rails db:migrate on a forty-row development database takes eleven milliseconds. The same migration against a forty-million-row production table takes an ACCESS EXCLUSIVE lock, queues every query behind it, exhausts the connection pool, and takes the site down for six minutes. The migration itself was correct. What was missing was any consideration of what the database does while it runs.

The Real Enemy Is Lock Duration

Almost every migration outage comes down to one mechanism: a DDL statement acquires a strong lock, and every other query on that table waits behind it. Worse, PostgreSQL’s lock queue is ordered — a blocked ALTER TABLE blocks every subsequent SELECT, even ones that would have been compatible with the running query.

So a migration that needs an exclusive lock for two seconds can cause a two-minute outage if it has to wait behind a long-running read first.

Two settings turn that failure mode into a harmless retry:

Example:

class AddColumnToOrders < ActiveRecord::Migration[7.1]
  def change
    execute "SET LOCAL lock_timeout = '5s'"
    execute "SET LOCAL statement_timeout = '15s'"

    add_column :orders, :fulfilled_at, :datetime
  end
end

With lock_timeout, the migration gives up after five seconds rather than piling up a queue. It fails, you retry during a quieter moment, and nothing goes down. Set these globally for the migration role rather than per-migration:

ALTER ROLE migrator SET lock_timeout = '5s';
ALTER ROLE migrator SET statement_timeout = '1h';

Operations That Are Safe

On modern PostgreSQL (11+), these take a brief exclusive lock and complete in constant time regardless of table size:

• ADD COLUMN with a constant default — no table rewrite since PG 11
• ADD COLUMN nullable with no default
• DROP COLUMN — marks it dropped, doesn’t rewrite
• Renaming a table or index
• Adding an index with CONCURRENTLY
• Adding a NOT VALID constraint

The nuance on defaults: a constant default is safe. A volatile one is not.

add_column :orders, :status, :string, default: "pending"   # safe, PG 11+
add_column :orders, :token, :uuid, default: -> { "gen_random_uuid()" }  # rewrites the table

The volatile function has to be evaluated per row, so PostgreSQL rewrites. Add the column without a default, backfill in batches, then set the default.

Operations That Will Hurt You

Adding an index without CONCURRENTLY

class AddIndex < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

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

disable_ddl_transaction! is mandatory — CONCURRENTLY cannot run inside a transaction. Concurrent builds can fail and leave an invalid index; check afterwards:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Adding a NOT NULL constraint

The naive form scans the whole table under an exclusive lock. The two-step version doesn’t:

# Step 1 — add as NOT VALID (instant, no scan)
class AddNotNullToOrdersEmail < ActiveRecord::Migration[7.1]
  def up
    execute <<~SQL
      ALTER TABLE orders
      ADD CONSTRAINT orders_email_not_null
      CHECK (email IS NOT NULL) NOT VALID
    SQL
  end

  def down
    execute "ALTER TABLE orders DROP CONSTRAINT orders_email_not_null"
  end
end

# Step 2 — validate (scans, but takes only a SHARE UPDATE EXCLUSIVE lock;
# reads and writes continue)
class ValidateOrdersEmailNotNull < ActiveRecord::Migration[7.1]
  def up
    execute "ALTER TABLE orders VALIDATE CONSTRAINT orders_email_not_null"
  end
end

Same two-step pattern for foreign keys — Rails supports it directly:

add_foreign_key :orders, :accounts, validate: false   # instant
# separate migration:
validate_foreign_key :orders, :accounts               # scans without blocking writes

Changing a column type

Almost always a full rewrite. varchar(50) → varchar(100) is free; integer → bigint is not. For anything that rewrites, use the shadow-column dance below.

Backfills: Batch, Throttle, Don’t Block

update_all on forty million rows is a single transaction holding row locks on everything, generating enormous WAL, and blocking vacuum.

Example:

class BackfillOrderFulfilledAt < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  BATCH_SIZE = 5_000

  def up
    Order.unscoped.in_batches(of: BATCH_SIZE) do |batch|
      batch.where(fulfilled_at: nil)
           .update_all("fulfilled_at = shipped_at")
      sleep 0.1
    end
  end

  def down
    # no-op
  end
end

Three details that matter:

• unscoped — a default_scope on the model will silently skip rows
• sleep — gives replicas a chance to catch up and vacuum a chance to run; without it, replication lag climbs and read replicas serve stale data
• down is a no-op — you can’t un-backfill, and pretending otherwise makes rollbacks fail

Better still: don’t put backfills in migrations at all. A migration that takes two hours blocks your deploy pipeline for two hours and can’t be safely retried from the middle. Put the backfill in a rake task or a job, run it separately, and keep migrations to schema changes that complete in seconds.

The Multi-Deploy Dance

The changes that genuinely can’t be done in one step need application code and schema to change in a specific order, across separate deploys.

Renaming a column

Never rename in place. Old code running against the new schema will crash during the deploy window when both versions are live.

Deploy 1: add new column, write to both, read from old
Deploy 2: backfill new column from old
Deploy 3: read from new, still write to both
Deploy 4: stop writing to old
Deploy 5: drop old column

Example (deploy 1 model code):

class Order < ApplicationRecord
  def recipient_email = self[:email]

  def recipient_email=(value)
    self[:email] = value
    self[:recipient_email] = value
  end
end

Five deploys for a rename feels absurd until the first time a one-step rename takes down checkout.

Changing a column type

Same shape: add a shadow column, dual-write, backfill, switch reads, drop the original.

Removing a column

Deploy code that stops referencing it first, then drop it. And tell ActiveRecord to forget it, because the column cache means old processes will still SELECT * and try to instantiate an attribute that no longer exists:

class Order < ApplicationRecord
  self.ignored_columns += ["legacy_status"]
end

Ship ignored_columns in one deploy, drop the column in the next.

Pro-Tip: Install strong_migrations before you need it. It’s a development-time gem that fails the migration with an explanation and a safe rewrite whenever you write something dangerous — adding an index without CONCURRENTLY, setting NOT NULL on an existing column, renaming, changing a type, backfilling in a transaction. The message includes the corrected migration you can paste in. What makes it worth adding on day one rather than after an incident is that it teaches the whole team the rules through code review rather than through postmortems, and it has a safety_assured { } escape hatch for the cases where you genuinely know the table has four hundred rows. Pair it with start_after set to your current schema version so it only polices new migrations.

Verify Before You Ship

Two habits that catch most of the rest:

Read the SQL. bin/rails db:migrate:status tells you what will run; ActiveRecord::Base.connection.execute with EXPLAIN on your backfill query tells you whether it’s using an index. A backfill doing a sequential scan per batch is quadratic.

Know the row count. Every migration review should start with “how many rows?” — the same add_index is trivial at 10k and an outage at 40M. SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders'; gives an instant estimate without counting.

Conclusion

Zero-downtime migration is mostly a matter of knowing which operations rewrite the table and which don’t, then splitting the ones that do across multiple deploys. Set lock_timeout so a blocked migration fails fast instead of queueing the world behind it. Add indexes concurrently, add constraints NOT VALID and validate separately, and keep backfills out of migrations entirely. Use ignored_columns before dropping, dual-write before renaming, and install strong_migrations so the rules are enforced in review rather than remembered under pressure.

FAQs

Q1: Does MySQL have the same constraints?
Different ones. MySQL 8 supports online DDL for many operations with ALGORITHM=INPLACE, LOCK=NONE, but the safe set differs from PostgreSQL’s — adding a column is online, changing a type usually isn’t. Check the specific operation against your version’s documentation.

Q2: Why does my concurrent index build keep failing?
CREATE INDEX CONCURRENTLY waits for all transactions that predate it to finish. A long-running query or an idle-in-transaction connection blocks it indefinitely. Find them in pg_stat_activity before retrying.

Q3: Can I run migrations while the app is deploying?
Yes, and that’s the point of the multi-deploy dance — during a rolling deploy, old and new code run simultaneously, so the schema must be compatible with both. Every migration should be safe against the previously deployed application version.

Q4: How large is “large” for these concerns?
Roughly a million rows is where a full-table rewrite becomes noticeable, and ten million is where it becomes an outage. Below a hundred thousand, most of this is unnecessary caution — but write it safely anyway, because tables grow and migrations get copy-pasted.

Q5: What about rollback for these multi-step migrations?
Most aren’t reversible in any meaningful sense — you can’t un-backfill data. Write down as a no-op or raise ActiveRecord::IrreversibleMigration rather than a fiction, and rely on forward-only fixes plus database backups for genuine recovery.