DEV Community

Bedirhan Elibol
Bedirhan Elibol

Posted on

What's the worst deploy-time lock you've hit?

I've seen a two-second migration freeze a checkout page because a plain CREATE INDEX held a lock on a table with a few million rows. Staging never showed it, because staging had a few hundred rows.

What was yours? Curious which statements bit people most: CREATE INDEX, SET NOT NULL, adding a column with a volatile default, something with foreign keys?

I wrote up the three I see most often with the safe versions, if useful: https://zelsis.com/guides/postgres-migration-locks

Top comments (5)

Collapse
 
alexcodebytes profile image
Oleksandr •

The CREATE INDEX without CONCURRENTLY is the classic trap. Staging with a few thousand rows takes 50ms, so everyone thinks it's fine, and then it takes 20 minutes of table-locking on prod.

The one that bit me the hardest was ALTER TABLE ... ADD COLUMN ... DEFAULT now() before Postgres 11. It rewrote the entire multi-million row table, locking it for what felt like an eternity. (Thankfully, since PG 11, non-volatile defaults are metadata-only and instant).

Also, SET NOT NULL is sneaky. It does a full table scan to verify the constraint, which blocks writes if you just run it directly. Using the NOT VALID constraint first, then VALIDATE CONSTRAINT in a separate transaction, is the only safe way to do it without downtime.

Collapse
 
bedirhandev profile image
Bedirhan Elibol •

Good list. One detail on SET NOT NULL that might save someone a surprise: the NOT VALID / VALIDATE pair applies to a CHECK constraint, since a plain NOT NULL can't be added as NOT VALID on most versions. The sequence that avoids the long ACCESS EXCLUSIVE scan on PG12+ is:

ALTER TABLE t ADD CONSTRAINT t_col_nn CHECK (col IS NOT NULL) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT t_col_nn;  -- only SHARE UPDATE EXCLUSIVE, writes keep going
ALTER TABLE t ALTER COLUMN col SET NOT NULL; -- skips the scan because the validated CHECK proves it
ALTER TABLE t DROP CONSTRAINT t_col_nn;
Enter fullscreen mode Exit fullscreen mode

Even the fast steps need a brief ACCESS EXCLUSIVE lock, so I also set lock_timeout (say 2s) and retry. Otherwise a long-running transaction ahead of you makes the ALTER wait, and every query behind it queues up. And since CREATE INDEX CONCURRENTLY can't run inside a transaction block, some migration tools need a flag to run that file outside one.

Collapse
 
alexcodebytes profile image
Oleksandr •

This is a great PG12+ pattern. The NOT VALID / VALIDATE trick on a CHECK constraint is a lifesaver for large tables.
The point about lock_timeout is crucial — without it, a simple ALTER waiting for a lock will block every subsequent SELECT (the classic lock queue issue). I usually set it to 2s and retry. Also, completely agree on CREATE INDEX CONCURRENTLY — most ORMs (like Django or Alembic) default to wrapping migrations in a transaction, which is a trap. You have to explicitly disable it for that specific file.

Thread Thread
 
bedirhandev profile image
Bedirhan Elibol •

2s with a retry is what I'd pick too. Good point on the transaction wrapping: in Django it's atomic = False on that one migration, and it's easy to forget because everything else in the file still looks normal.

Thread Thread
 
alexcodebytes profile image
Oleksandr •

Exactly. atomic = False is the classic trap — the rest of the migration looks perfectly normal, so it's easy to miss until it causes a long lock in production. Good catch on keeping that specific detail in mind.