Postgres: Column Change Migrations with Zero Downtime In Rails

Since online shoppers are known to abandon carts at all hours of the day, we do our best to make sure Jilt is up and running 24/7. However, every developer knows altering a large database without any downtime can get pretty tricky.

A few weeks ago, we had several columns that needed to be changed from an int to a bigint. At first glance, this seemed like a straight forward change, we could use the rails change_column method and be off to the races.

change_column :orders, :price, :bigint

Not so fast. Before deploying this to our production app we ran a quick test on a copy of our production database. The result? It took an hour and a half.

Eep. Not willing to put our app into maintenance mode for an hour and a half, we set out to find a workaround. Taking inspiration from this Braintree post, our solution involved two parts. The first to add temporary columns and backfill them, and the second to rename the temporary columns and remove the old ones.

Part I

In part I of our migration we needed to accomplish three things

Add temporary columns of the proper type

Add a callback to save data to both our old column and our new column

Add a rake task to backfill our new temporary columns

First, we added our temporary columns.

add_column :orders, :temp_price, :bigint

Then, we added a callback to our model to make sure that when a record was updated, our data would write to both columns.

Each part of this migration was written on a different branch and deployed one after the other.

Part I of our migration took 0.0279s, while Part II took 1.3389s, saving us an hour and a half of potential downtime. This meant we were able to keep capturing abandoned carts without any interruptions.

Post navigation

One comment

I think you have the potential for triggering some errors during the phase 2 deployment. Since your deployment will first run your migration there will be some old web servers still running the old code after the migration has been deployed. Since ActiveRecord caches the columns and uses that cache to build INSERT and UPDATE statements, the old web servers will try to write to the old column and blow up.

The most common way to avoid that is to do this in 3 steps, and in step 2 you would tell ActiveRecord to ignore the old column by doing