Writing a Rails migration for a potentially large table

Some tables have a bigger chance to become quite large. For example, tables for storing object changes history.

Here I show some tips when creating a new potentially large table, but it is possible to use them to optimise existing tables.

Bigint as id

If this table grows really fast, it is possible that you will run into the limits of integer data type (which is 2147483647 for PostgreSQL/MySQL). Fortunately, from Rails 5.1, ids in new tables are bigint by default. If your project uses an older version, check this stackoverflow answer for details how to change id type from integer to bigint.

Remove indexes that you don’t need

Big tables, like for storing changes history, are usually updated very often, that means that it is critical to make INSERT faster. And what makes it slower is indexes. Check if you really need all these indexes you have on this table. For new tables, remember, that from Rails 5 when you use t.belongs_to or t.references Rails will automatically create indexes for these associations. If you don’t need them, you can add an option index: false to this methods:

t.belongs_to :user, index: false

Replace strings with integers

If you need to store some text about a status or an action, use integers and then map them to text in your model. Why? Because integers use significantly less disk space than strings. For PostgreSQL you can add an option to use smallint datatype which uses only 2 bytes instead of 4 bytes for integer and accepts integers in range approximately -32000 to +32000.

t.integer :action, limit: 2

Give up updated_at column

If you plan to store changes history or an events log, you don’t really need this column. And to make a table smaller it is essential to remove columns that are not required. If you used to write t.timestamps, don’t forget that Rails make them ‘not null’ by default, so should you with ‘created_at’:

t.timestamp :created_at, null: false

These are all the tips I have for you. If you have any suggestions, you can add them in comments sections. Have a nice day!