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.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!