Numeric data types in Rails migrations. Use of optional flags to improve storage and performance

Photo by Nick Hillier on Unsplash

Numeric data types play a crucial role in defining the structure of numeric data in a database through Rails migrations. In this article, we will explore various numeric types that can be used in Rails migrations and will dive into detailed use case for unsigned and limit optional flags.

Integer The integer type is used to represent whole numbers: both positive and negative values. The size of an integer can be specified by passing a limit parameter when defining the column in the migration file. For example, to create an integer column with a maximum value of 2 billion, you can define the migration as follows:

class CreateProducts < ActiveRecord::Migration[7.0]
  def change
    create_table :products do |t|
      t.integer :identifier, limit: 8
      t.timestamps
    end
  end
end

In this example, we have specified a limit of 8 bytes for the identifier column, which allows us to store values up to 2⁶³ — 1.

Float The float type is used to represent decimal numbers with a floating point with support for both positive and negative values. The precision and scale of a float can be specified by passing parameters when defining the column. For example, to create a float column with a precision of 10 and a scale of 2, you can define the migration as follows:

class CreateProducts < ActiveRecord::Migration[7.0]
  def change
    create_table :products do |t|
      t.float :price, precision: 10, scale: 2
      t.timestamps
    end
  end
end

In this example, we have specified a precision of 10 digits and a scale of 2 decimal places for the price column.

Decimal Similar to the float, the decimal type is also used to represent decimal numbers but with a fixed precision and scale, most commonly it is used for financial and other calculations where accuracy is of outmost importancy. Precision and scale of a decimal can be specified by passing parameters when defining the column. For example, to create a decimal column with a precision of 15 and a scale of 2, you can define the migration as follows:

class CreateOrders < ActiveRecord::Migration[7.0]
  def change
    create_table :orders do |t|
      t.decimal :total_amount, precision: 15, scale: 2
      t.timestamps
    end
  end
end

In this example, we have specified a precision of 15 digits and a scale of 2 decimal places for the total_amount column.

Biginteger The big integer type is used to represent large whole numbers and it supports both positive and negative values. Size of a big integer can be specified by passing a limit parameter when defining the column. For example, to create a big integer column, which can hold a maximum value of 10³⁰, you can define the migration as follows:

class CreateAccounts < ActiveRecord::Migration[7.0]
  def change
    create_table :accounts do |t|
      t.bigint :balance, limit: 30
      t.timestamps
    end
  end
end

In this example, we have specified a limit of 30 bytes for the balance column, which allows us to store values up to 10³⁰ — 1.

As you may have noticed from the examples above, we can use several optional flags to customize the behavior and storage of the columns in the database.

default The default flag can be used to specify a default value for a numeric column. This value will be used if no other value is specified when creating a new record. Here’s an example:

class CreateProducts < ActiveRecord::Migration[7.0]
  def change
    create_table :products do |t|
      t.decimal :price, precision: 10, scale: 2, default: 0.0
      t.timestamps
    end
  end
end

In this example, we’ve added a default value of 0.0 to the price column. If no price is specified when creating a new product, it will default to 0.0.

null The null flag can be used to specify whether a numeric column can be null (i.e., have no value). By default, all numeric columns are nullable, but you can use this flag to enforce no null values. Here’s an example:

class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.integer :age, limit: 2, null: false
      t.timestamps
    end
  end
end

In this example, we’ve added the null: false flag to the age column, which means it cannot be null.

precision & scale We’ve already covered precision and scale when defining decimal columns above but I would like to note that they can also be used as optional flags when altering an existing column. Here’s an example:

class ChangePricePrecision < ActiveRecord::Migration[7.0]
  def change
    change_column :products, :price, :decimal, precision: 12, scale: 4
  end
end

In this example, we’re altering the price column in the products table to have a precision of 12 digits and a scale of 4 decimal places.

unsigned The unsigned flag can be used with integer and big integer columns to specify that only positive values should be allowed. Most commonly it is used for columns like ID’s or counts where negative values are practically not possible. Here’s an example:

class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.integer :age, limit: 1, unsigned: true
      t.timestamps
    end
  end
end

In this example, we’ve added the unsigned flag to the age column, which means it can only store positive values up to a limit of 1 byte. Since the age cannot be a negative number we can use the unsigned flag here.

limit This option flag that can be used with integer and big integer columns to specify the maximum number of bytes that should be used to store the value in the database. Here’s an example:

class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.integer :age, limit: 2
      t.bigint :salary, limit: 8
      t.timestamps
    end
  end
end

In this example, we’re creating an age column with a limit of 2 bytes and a salary column with a limit of 8 bytes. Please note that the limit only affects the storage size, and not the range of values that can be stored. The range of values is determined by the signed or unsigned nature of the column and the number of bytes or digits used.

It is also worth noting that if you don’t specify a limit option, Rails will resort to a default limit based on the data type and database adapter being used. For example, the default limit for an integer column in MySQL is 4 bytes, while the default limit for an integer column in PostgreSQL is 4 bytes for smallint, 2 bytes for integer and 8 bytes for bigint.

For reference here is a table of integer size limits for PostgreSQL and MySQL:

These size limit apply to signed integers, so in case of the unsigned integers the maximum value will be doubled, but the maximum number of bytes will remain the same.

And here is table of integer value ranges that can be stored in PostgreSQL and MySQL:

In general, it is a good idea to use the limit option to specify the exact storage size for the numeric columns, instead of relying on defaults. This can help to optimize storage space and performance, especially if you’re working with large datasets or limited resources.

But it also worth noting that if you exceed the specified maximum size, you may encounter errors or unexpected behavior when trying to insert or update data. Therefore, it’s important to choose an appropriate size limit based on your needs and the capabilities of your database.