The migration that looked instant in staging
A schema change runs in 40ms against your staging database and locks a production table for eight minutes. The difference isn't the SQL — it's the row count and the traffic you weren't simulating.
Hammad Iqbal
Software Engineer
Every painful migration I've shipped passed review, ran clean in CI, and applied in well under a second against staging. Staging had ten thousand rows and no concurrent writes. Production had forty million rows and a checkout flow hitting the same table every second, and the `ALTER TABLE` that took 40ms locally held a lock for the better part of a coffee break.
- Migration speed scales with table size and write traffic, neither of which your staging database has.
- Adding a column with a default, or an index without `CONCURRENTLY`, takes a lock that blocks every write for the duration.
- The safe version of a schema change is almost always two or three small deploys, not one clever one.
Staging lies about the two things that matter
A migration's cost is a function of how many rows it rewrites and how many other transactions are fighting it for a lock. Staging typically has a seed script's worth of data and exactly one connection — yours. Production has the real table and a live workload. The SQL is identical; the behaviour isn't remotely comparable, and the number CI reports back to you is close to meaningless as a predictor.
The operations that quietly take a full table lock
Postgres has gotten better at this — adding a nullable column is instant now, and a column with a constant default no longer rewrites the table. But plenty of everyday changes still take a lock that blocks reads or writes until they finish:
- Creating an index without `CONCURRENTLY` — blocks writes for the whole build
- Adding a `NOT NULL` constraint to an existing column — scans every row while holding a lock
- Changing a column's type — rewrites the entire table
- Adding a foreign key without `NOT VALID` — checks every existing row before the deploy can proceed
Boring migrations are the fast ones
The instinct is to make the schema change in one atomic deploy so the code and the database are never out of sync. In practice the safe path is the opposite: add the nullable column in one deploy, backfill it in batches out of band, add the constraint `NOT VALID` and validate it separately, then flip the code over. Each step is individually reversible and none of them holds a lock long enough to notice. It's more deploys and less drama.
Assume every schema change against a large table is dangerous until you've checked what lock it takes and how long it holds it. The one-deploy version is for small tables and things nobody is writing to.
Have a project this kind of thinking applies to?
Tell me what you're building — I read every message myself.