The Migration That Locked the Table (and How to Never Write One)

At 9:14 on a Tuesday morning, a deploy went out containing a one-line migration: add a column to the events table. At 9:15, every request touching events was hanging. At 9:26, someone killed the migration and the site came back. The postmortem's most quoted line: "the migration was correct." It was! It just wasn't safe — and on a table with 400 million rows and constant traffic, the difference between those two words is eleven minutes of downtime.

I've reviewed thousands of migrations, and the dangerous ones almost never look dangerous. They look like the tutorials. So let's build the mental model that separates "correct" from "safe," because it's a model you can apply in thirty seconds during code review — which is exactly where this incident should have been caught.

The mental model: locks and the queue behind them

Most schema changes take an ACCESS EXCLUSIVE lock on the table — the strongest lock there is. While it's held, nothing else can touch the table. Not reads. Not writes. Nothing.

That alone isn't the disaster. The disaster is the queue. Your migration asks for the exclusive lock while some long-running query — a report, an analytics scan, an idle-in-transaction session — still holds a read lock. So the migration waits. And here's the cruel part: everything that arrives after the migration waits behind it, including plain SELECTs that would normally sail through. One waiting migration converts a busy table into a parking lot. The 9:15 hang wasn't the migration running; it was the migration waiting, with all of production queued up behind it.

Two consequences fall out of this model:

1. What matters is how long the lock is held or awaited, not how "big" the change is. Some expensive-sounding changes hold the lock for milliseconds; some innocent-looking ones hold it for the length of a full table rewrite.
2. Always set a lock timeout. If the migration can't get its lock quickly, it should fail and retry later — a failed migration is an inconvenience; a queued one is an outage.

class AddStatusToEvents < ActiveRecord::Migration[8.0]
  def change
    execute "SET lock_timeout = '5s'"   # fail fast instead of queueing production
    add_column :events, :status, :string
  end
end

(Libraries like strong_migrations automate this and will flag most of what follows. I consider it table stakes on any Rails app with a table over a few million rows.)

Knowing which changes are which

The safety of a change depends on your database and version, but for modern Postgres the map looks like this:

Fast (brief lock, fine on big tables): adding a nullable column; adding a column with a constant default (Postgres 11+ stores the default in metadata — no rewrite); dropping a column (it's marked dead, not rewritten).

Slow while holding the lock (the outage makers): CREATE INDEX without CONCURRENTLY — blocks all writes for the whole build; changing a column's type (usually a full table rewrite); adding NOT NULL or a CHECK/foreign key constraint the naive way — full table scan under lock.

Safe versions of the slow ones exist, and they share one idea: split "declare the rule" from "verify the rule."

-- Index: build without blocking writes (can't run inside a transaction)
CREATE INDEX CONCURRENTLY idx_events_status ON events (status);

-- Constraint: declare instantly, verify later with only a light lock
ALTER TABLE events
  ADD CONSTRAINT status_present CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE events VALIDATE CONSTRAINT status_present;

The NOT VALID / VALIDATE two-step is the single most useful trick in this whole area: new writes are checked immediately, existing rows are verified in the background, and the exclusive lock lasts milliseconds.

Backfills are deploys, not migrations

The second habit that causes 9:15 incidents: putting data changes inside schema migrations. The tempting version looks like this — and this is also, notably, the version AI assistants love to produce, because it's the tidy self-contained answer to "add a column with a value for existing rows":

def change
  add_column :users, :timezone, :string
  User.update_all(timezone: "UTC")   # one statement, 80M rows, one giant
end                                   # transaction, replication lag for everyone

A single UPDATE across a huge table holds row locks on everything it touches, bloats the table (every update writes a new row version), hammers replication, and — since migrations run during deploy — puts all of that in the critical path of shipping code. If it fails at row 79 million, the whole thing rolls back and your deploy is wedged.

The disciplined pattern splits one change into three independently shippable, independently revertible steps:

1. Deploy 1 — schema: add the column, nullable, no default logic. Fast lock, done.
2. Deploy 2 — backfill: a background job or rake task that updates in batches (a few thousand rows), sleeping between batches, resumable from where it stopped. It runs for hours or days, and nobody notices — which is the point.
3. Deploy 3 — constraint: once the backfill is verified complete, add NOT NULL via the NOT VALID/VALIDATE dance, and only then ship code that relies on it.

Yes, it's three PRs where the tutorial shows one. The tutorial's table has nine rows. Yours doesn't. When you review a migration that mixes DDL with an update_all, the kindest comment you can leave is: "let's make this three PRs — here's why." Reviewers who explain the lock-queue model once tend not to see the pattern again from that author, human or otherwise.

A reviewer's thirty-second checklist

For any migration touching a table you'd describe as "big" (my threshold: would a full scan take longer than my lock timeout?):

• Is there a lock_timeout?
• Any index creation? Must be CONCURRENTLY (and disable_ddl_transaction! in Rails).
• Any NOT NULL, CHECK, or FK on existing data? Must be NOT VALID then VALIDATE.
• Any type change? That's a new-column-plus-backfill project wearing a one-liner costume.
• Any data updates in the migration file? Move them to a batched, resumable backfill.
• Can the app run correctly between each step? (Old code + new schema must coexist.)

What I want you to remember

• The outage isn't the lock; it's the queue behind the lock. lock_timeout turns outages into retries.
• "Correct" and "safe" are different reviews. Do both.
• Split declare from verify: CONCURRENTLY for indexes, NOT VALID/VALIDATE for constraints.
• Backfills are their own deploys — batched, resumable, boring. One-statement backfills of big tables belong in postmortems, and regularly appear there.
• Big-table migrations are choreography: nullable column, backfill, constraint, then dependent code. Three small dances beat one dramatic one.

The best migrations at scale are the ones nobody can find in the graphs afterward. Aim for invisible.