I recently had to add a new column with a default value to an existing table in a Rails application.
The Rails application was backed by PostgreSQL and the table that we were adding this new column to had over 2 million rows of data. Now, adding a new column with a default value in Rails is a simple task that involves writing a migration that looks like
|
1 2 3 4 5 |
class AddNewColumnToTable < ActiveRecord::Migration[5.0] def change add_column :table_name, :column_name, :data_type, default: 'blah' end end |
In most cases, the above will work. However, if you have really big table with large amounts of data, which in this specific case there were over 2 million rows of data, the above migration will take an eternity to run. This is because adding a default value for a column in a table will get Postgres to go over every row and update the default values for the said column. If you have a large table, this operation can take some time. What’s worse is that because the above migration is locked in a transaction, the table in question becomes locked during the migration and your application will grind to a halt since your database table won’t be functional.
So… how do we solve this? Let’s first list our problems in the situation where we’re adding a new default column to a really large table.
Problems
- Column creation (or simply adding default value to an existing column) causes all rows in that table to be updated at the same time, which can be time consuming in really large tables
- Updates are slow in Postgres since it has to guarantee consistency
- During the update, the table that’s in operation is locked, causing the application to grind to a halt until the update finishes
To solve this problem, we need to work around the above facts so that we can add the default column and allow all of the rows in the large table to be updated while keeping the application running. How do we do this? Well… we can do the following.
Solution
- Adding a column to a table without default values in Postgres is fast. Thus, we should add the new column with a default value of null first to get the creation of the new column out of the way.
- Rather than updating every row in the table all at once, we can split up the number of records and update them in batches. This makes the work more manageable for the database.
- If we make the updates of the rows in the table with default values more manageable, the table won’t be locked during the operations and the application can keep running during the migration.
So, let’s do this. To do this in a Rails migration, we need to recognize a few more things about how Rails handles migrations. Rails migrations are done in a transaction. Meaning anything in that def change are being ran in a transaction to allow for rollbacks. If we do any data updates there, it means that all of those updates will be wrapped in a transaction, locking the entire table. This means that we’ll need a way to disable this auto-transaction mechanism of Rails migrations and handle our transactions manually, picking and choosing where we want to wrap our database operations in a transactions and where we want to disable wrapping our database operations in transactions. Thankfully, Rails comes with a method called disable_ddl_transaction! that we can use in our migration files to disable the default transaction locking behavior in our migrations. Also, we want to replace the def change method with the def up and def down so that we can manually define our rollback strategy just in case we want to rollback our migration. Below is my attempt at utilizing this feature of Rails and handling the transactions on my own.
For the sake of demonstration, let’s say that we’re adding a integer column named number_of_retries with a default value of 0 to a table called users.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
class AddNumberOfRetriesToUsers < ActiveRecord::Migration[5.0] disable_ddl_transaction! def up ActiveRecord::Base.transaction do add_column :users, :number_of_retries, :integer, default: nil execute <<~SQL ALTER TABLE users ALTER COLUMN number_of_retries SET DEFAULT 0; SQL end User.find_in_batches.with_index do |users, batch| puts "Processing batch number ##{batch + 1}" User.where(id: users.map(&:id)).update_all number_of_retries: 0 end ActiveRecord::Base.transaction do execute <<~SQL ALTER TABLE users ALTER COLUMN number_of_retries SET NOT NULL; SQL end end def down ActiveRecord::Base.transaction do remove_column :users, :number_of_retries end end end |
You can see that I wrap all database operations (adding a column, altering columns with default values, and etc.) in transactions, while I do not wrap the part where I update the default user values in transactions. The find_in_batches has a default batch_size of 1,000, but you can always increase that to a larger number for faster migration speed if you want. I just found that 1,000 to be a good number for stable migrations. I’ve had situations where the migration timed out when I set the default batch_size to a higher number like 10,000. Also, as you can see, having a separate def up and def down methods in the migration allows us a way to safely rollback the migration if needed.
The above migration will work. Granted, it won’t be an instant migration. You’ll still have to wait for the migration to run. However, it’ll prevent locking of the table which grinds the application to a halt, which ends up in bad experience for your users. Unfortunately as of now, there are no good simple ways to add new columns with default values quickly to an existing large table. The above is a workaround that utilizes certain principles to add new columns with default values on really large tables.
I hope that this post is helpful for those looking to solve similar problems on their production Rails applications.
Subscribe to the Newsletter!
Sign up for my email newsletter to get updates on remote work, Android development, and etc.
