A Database Migration That Locks the Table Is an Outage With Extra Steps
Key takeaway: Not all schema changes are equal, and the difference between an instant metadata-only change and a full table rewrite is not obvious from reading the migration statement alone — it depends on the specific database engine’s internal implementation of that particular change.
Why Some Migrations Are Instant and Others Are Not
Adding a nullable column with no default is, on most modern database engines, a fast metadata-only operation, because the engine can record the new column definition without touching every existing row — a row simply returns null for a column it predates, computed at read time rather than stored.
Adding a column with a non-null default historically required rewriting every existing row to populate that default value, holding a lock on the table for the entire duration of that rewrite — for a table with millions of rows, this can take minutes, and during those minutes the table is effectively unavailable for the writes, and sometimes reads, that the lock blocks.
| Migration type | Typical impact on a large table |
|---|---|
| Add nullable column, no default | Instant, metadata only |
| Add column with non-null default | Historically a full rewrite and lock; improved in recent engine versions |
Add an index without CONCURRENTLY |
Locks writes for the full build duration |
Add an index with CONCURRENTLY |
No write lock, takes longer, can fail and needs cleanup |
| Change a column’s type | Frequently a full rewrite regardless of size |
| Add a foreign key constraint | May need to scan the full table to validate existing data |
Index creation without an explicit non-locking option is a particularly common and avoidable cause of production incidents, because building an index the ordinary way holds a lock preventing writes to the table for the entire build duration, while a non-locking build option — available in most modern engines — takes measurably longer to complete but never blocks concurrent writes, which is almost always the correct trade for a table receiving live traffic.
The Practical Discipline
Test every migration against a copy of production-scale data, not against an empty or small development database, because the locking behaviour and duration that actually matters only becomes visible at a size and row count resembling the real table — a migration that completes instantly on a thousand-row development table can lock a hundred-million-row production table for an unacceptable duration, and nothing about running it against the small table would have revealed that difference.
Prefer the non-locking variant of any operation that has one, even at the cost of a longer total migration time, since a slower migration that never blocks production traffic is a better trade in almost every case than a faster one that does.
Split unavoidably risky changes into separate, sequenced steps rather than one migration attempting everything at once — adding a nullable column, backfilling it in controlled batches, then adding the constraint that requires it to be non-null as a separate later step, rather than adding the column with the constraint already attached in one operation that would require the full rewrite immediately.
Backfilling Large Tables Safely
Backfilling data into an existing large table should generally happen in small batches with a brief pause between them, rather than as a single large update statement — a single massive update transaction can itself hold locks and generate an amount of write-ahead log activity that causes its own operational problems, distinct from the schema change that motivated the backfill in the first place.
Verifying Before Running in Production
Check your specific database engine’s actual documented behaviour for the exact operation you are running, since locking behaviour varies meaningfully between database engines and has also changed across versions of the same engine — a rule that was true several versions ago may no longer hold, and assuming behaviour from general knowledge rather than checking the current specific documentation is a common source of surprises.
The Bottom Line
Test migrations against production-scale data specifically to reveal locking duration, prefer non-locking variants for indexes and other operations that offer one even at the cost of additional time, and split genuinely risky changes into separate sequenced steps rather than one large operation. Verify the actual locking behaviour for your specific database engine and version rather than assuming it from general or outdated knowledge.
