A single long SELECT stretches an instant ALTER TABLE into minutes of Postgres downtime

The post's author wrote of the production incident: "It was instant in staging. In production it hung, and the API stopped answering." The DDL itself was fast. ACCESS EXCLUSIVE is the strongest lock level in Postgres and conflicts with every other lock, including the ACCESS SHARE that a plain SELECT takes ^[2]. Weaker requests arriving later do not get to jump ahead of it ^[3].

Three holders cause it, according to the post: a long analytics SELECT, an idle in transaction session that a web request opened and never committed, and autovacuum running in anti-wraparound mode, which does not yield the way ordinary autovacuum does ^[5]. During the incident, joining pg_stat_activity against pg_blocking_pids() shows which is which. Any row with a non-empty blocked_by array is waiting, and the PID inside that array is the transaction to deal with ^[6].

Set the wait cap to a few seconds and retry. The worst case for live traffic becomes those few seconds plus the DDL's own execution, instead of however much of the blocking transaction is left to run ^[8]. The post also runs migrations as their own pipeline step ^[18].

The rename sequence shows what the discipline costs in releases. Add display_name as a nullable column and deploy code that writes both columns while still reading full_name. Copy existing rows in batches, flip reads to the new column and deploy again. Stop writing full_name, deploy, and only then run DROP COLUMN ^[12]. Dropping a column is instant and irreversible in practice, so it goes last and alone ^[14]. The rule the author follows, as written: "a deploy may add optional things or remove unused things, never both, and never anything a currently-running instance depends on" ^[13].

Before writing any of it, check whether the statement rewrites the table. Since Postgres 11, ADD COLUMN ... NOT NULL DEFAULT with a constant stores the default in the catalog and does not rewrite ^[15]. A volatile default such as gen_random_uuid() still rewrites every row ^[15]. The distinction is the word constant. A rewrite copies every row into new files while holding ACCESS EXCLUSIVE. On a large table that is an outage of unbounded length ^[17].

This is one engineer's field notes on dev.to, and the post does not report how long the production API was unavailable. I would adopt the timeout first, because it is one setting in the migration session and it needs no application change. And if you are sizing a migration window, how long the ALTER TABLE itself runs is the wrong thing to measure ^[20].