Zero-downtime migrations on a live store

On a hobby project a migration is harmless: a moment’s downtime while the schema changes, nobody notices. On a live store doing transactions every second, the same innocent-looking migration can lock a table and take the entire site down for the duration — and some migrations, run naively, lock a busy table for minutes. The painful part is that these migrations run fine in development against an empty database and only bite in production against a large, busy table. Here is how to change schemas on a running store without anyone noticing.

The enemy: locks held too long

Almost every zero-downtime problem comes down to one thing: a migration takes a lock on a table and holds it while doing slow work, and every query that needs that table queues behind the lock. Brief locks are fine; the killers are operations that hold a strong lock while rewriting a large table or building an index. The job is to know which operations do that and avoid the dangerous form.

A practical safety net first: set a short lock timeout in your migrations so a migration that would block waiting for a lock fails fast instead of piling up a queue of stalled queries behind it (which is how one slow migration cascades into a full outage):

# fail fast rather than queue the whole app behind a lock
execute "SET lock_timeout = '5s'"

And consider the strong_migrations gem, which catches dangerous migrations in development and tells you the safe alternative before you ship them — the cheapest insurance there is.

Adding a column: usually safe, with one trap

Adding a nullable column is fast and safe — it is a metadata change, no table rewrite:

add_column :products, :featured, :boolean        # fine

The historic trap was adding a column with a default value: on older Postgres this rewrote the entire table while holding a lock, which on a large table meant minutes of downtime. (Postgres 11 will make this instant for constant defaults, but in 2016 you must assume it rewrites.) The safe pattern is the expand-contract dance:

1. Add the column without a default (fast).
2. Set the default for future rows in a separate step.
3. Backfill existing rows in batches, so you never update millions of rows in one long-locking statement:

# backfill in batches, off the migration's critical path
Product.in_batches(of: 5_000) do |batch|
  batch.update_all(featured: false)
  sleep(0.1)   # breathe; don't saturate the DB
end

The principle generalises: never run a single statement that touches every row of a big table. Batch it, and let each small batch commit quickly so other queries get a turn.

Adding an index: concurrently, always

Building an index normally locks the table against writes for the whole build — fatal on a busy table. Postgres has a dedicated answer: CREATE INDEX CONCURRENTLY, which builds the index without blocking writes. Rails supports it with one caveat — it cannot run inside a transaction, so you disable the migration’s transaction:

class AddIndexToOrders < ActiveRecord::Migration[5.0]
  disable_ddl_transaction!         # required for CONCURRENTLY

  def change
    add_index :orders, :customer_id, algorithm: :concurrently
  end
end

This single change — algorithm: :concurrently plus disable_ddl_transaction! — is the difference between an index build that locks out checkout for two minutes and one nobody notices.

Renaming a column: never in one step

Renaming a column looks trivial and is one of the most dangerous things you can do to a live app — not because of locks, but because of deploy timing. Your code and your schema are deployed at slightly different moments, so there is always a window where old code runs against the new schema (or vice versa). Rename email to email_address in one migration, and during that window the running app is querying a column that no longer exists — errors everywhere.

The safe approach is the expand-contract (a.k.a. parallel-change) pattern, spread across multiple deploys:

1. Expand: add the new column email_address. Deploy code that writes to both columns and reads from the old one. Now both exist and stay in sync.
2. Backfill the new column from the old one, in batches.
3. Migrate reads: deploy code that reads from the new column. Both are still written.
4. Contract: once nothing references the old column, deploy code that stops writing it, then drop it in a final migration.

It is more steps, but each one is independently safe, and at no point is the running code out of step with the schema. The same pattern handles changing a column’s type, splitting a column, or extracting data to a new table. The mental model: the schema and the code must be compatible at every intermediate state, because there is always an intermediate state in a rolling deploy.

Adding NOT NULL and constraints safely

Adding a NOT NULL constraint validates every existing row while holding a lock — slow on a big table. The safe path in Postgres is to add the constraint as NOT VALID first (which skips the immediate full scan), then VALIDATE it in a separate step (which checks existing rows without the heavy lock). Foreign keys follow the same add-then-validate pattern. The recurring theme: split the fast “declare it” step from the slow “verify it” step, so the slow part does not hold a blocking lock.

Verdict

Zero-downtime migrations are not magic; they are a small set of disciplines applied consistently. Set a lock timeout so a migration fails fast instead of cascading. Add columns without defaults and backfill in batches. Build indexes CONCURRENTLY. Never touch every row in one statement. And above all, treat renames and type changes as multi-deploy expand-contract sequences, because in a rolling deploy the code and schema are never updated at exactly the same instant, and your job is to keep them compatible through every intermediate state. Adopt a tool like strong_migrations to catch the dangerous patterns before they ship, and schema changes on a busy store stop being a held breath and become routine — which, on something handling money every second, is exactly what they must be.