Migrations that lock, and the ones that do not

Adding a nullable column with no default is instant: it changes the catalog and touches no rows. Adding one with a volatile default rewrites the whole table. In a migration file the two lines are almost identical, and one of them takes an ACCESS EXCLUSIVE lock on your largest table at whatever hour the deploy runs.

• ADD COLUMN nullable, no default — instant, catalog only.
• ADD COLUMN with a constant default — instant in Postgres 11 and later, a full rewrite before it.
• CREATE INDEX — blocks writes for its whole duration. CREATE INDEX CONCURRENTLY does not, and takes roughly twice as long.
• ALTER COLUMN … TYPE — a full rewrite, and an ACCESS EXCLUSIVE lock for all of it.
• ADD CONSTRAINT … CHECK — scans the whole table. NOT VALID then VALIDATE CONSTRAINT does it in two cheap steps.

Building an index over a table this size is not instant even here, on a few thousand rows in a browser tab. On a hundred million rows on a production disk it is minutes to hours — and for all of them, without CONCURRENTLY, nothing can write.

SELECT count(*) AS rows_to_rewrite FROM events

The practical defence is a short lock_timeout on migrations and a retry. Failing fast and trying again in ten seconds is almost always better than waiting, because waiting is what turns one slow query into an outage.