Postgres field notes: why instant ALTER TABLE statements still cause outages

A one-millisecond migration that took down an API

A field-notes post on dev.to walks through a production failure familiar to many teams: a migration adding a single nullable column ran in no time in staging, then sat frozen in production while the API stopped responding. The author's core point is that the column itself was never the problem — everything queued behind it was.

According to the post, ALTER TABLE requires an ACCESS EXCLUSIVE lock, the most restrictive mode Postgres offers, and it is incompatible with every other lock, including the ACCESS SHARE lock taken by a plain SELECT. Lock requests are granted in arrival order, and weaker requests arriving later cannot overtake a stronger one already waiting. So if an ALTER TABLE is stuck waiting for a long-running transaction to finish, every subsequent read and write on that table lines up behind it. A catalog update that takes milliseconds turns into a stall that persists for exactly as long as whatever was already holding the table.

The post names three repeat offenders: a long-running analytics query, a web request that opened a transaction and never committed it, and autovacuum in anti-wraparound mode, which does not back off the way ordinary autovacuum does. For diagnosis, the author suggests joining pg_stat_activity with pg_blocking_pids(); any row with a non-empty blocking list is waiting, and the PID inside that list identifies the session you actually need to resolve.

Fail fast with lock_timeout

The central recommendation is to set a short lock_timeout before any DDL — a few seconds. lock_timeout bounds how long a statement waits to acquire a lock, while statement_timeout bounds how long it runs after acquiring one. With a three-second cap, a migration that cannot obtain its lock aborts with SQLSTATE 55P03 and releases the queue, so the queue clears within three seconds at most. Aborting is fine: retrying is the right move, and an attempt a few moments later usually finds a window between long queries. The post also advises running migrations as a separate pipeline step rather than bundling them into deploys.

Expand, backfill, contract

Because rolling and serverless deploys run the old and new application versions against one database at the same time, the schema must stay compatible with both. Expand/contract achieves that across three deploys: add the new structure alongside the old and dual-write; backfill existing rows in batches and switch reads over; finally stop writing the old column and only then drop it. A column rename illustrates the trap — RENAME COLUMN is instant, metadata-only work, yet every old instance still selecting the old name starts erroring the moment it commits. The author's rule is that each deploy should do one of two things — add something optional, or remove something no running instance depends on — but never both at once. Dropping a column is instant yet effectively irreversible, so it belongs at the end of the sequence, on its own.

Cheap DDL versus full table rewrites

A rewrite copies every row into new files while holding ACCESS EXCLUSIVE — on a large table that is a stall with no upper bound. Since Postgres 11, ADD COLUMN with a NOT NULL constant default is catalog-only, but a volatile default such as gen_random_uuid() rewrites every row. Widening varchar to text is metadata-only; widening int to bigint is a full rewrite. CREATE INDEX blocks writes for the entire build, so the post recommends CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block and therefore has to run as its own step — and a failed attempt leaves behind an INVALID index that must be dropped before trying again.

For constraints, the author leans on a two-step approach: adding a foreign key or CHECK constraint as NOT VALID is instant and verifies only new writes, and a follow-up VALIDATE CONSTRAINT scans existing rows under the weaker SHARE UPDATE EXCLUSIVE lock, which blocks neither reads nor writes. The same shape makes a column NOT NULL without a blocking scan — since Postgres 12, a validated CHECK constraint serves as proof, letting SET NOT NULL skip its own scan.

Why it matters

Most migration downtime is not caused by slow DDL; it is caused by an instant statement getting stuck at the front of the lock queue. That failure mode is cheap to prevent. A short lock_timeout with retries converts an unbounded outage into a few failed attempts, and expand/contract keeps schema changes compatible with rolling deploys. These are process changes rather than infrastructure investments, and they apply to any team running Postgres behind a live API.