Change a busy Postgres table without stalling every query

Before you start

• A Postgres database and a psql session with rights to change the schema.
• A staging copy to rehearse on, ideally with the same traffic pattern.

The lock queue

Most forms of ALTER TABLE ask for an ACCESS EXCLUSIVE lock, the only mode that conflicts with a plain SELECT. If any transaction has touched the table and not finished, even one sitting idle in transaction, the ALTER waits for it. Every read and write that arrives after the ALTER then queues behind it, because Postgres grants locks in order. The statement itself is cheap. The wait is not.

Make the change safely

1. Step 1Check for old and idle transactions

   The oldest open transaction decides how long a waiting ALTER stalls the table. Look for long running and idle in transaction sessions before you start, and wait or end them first.

   SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
   FROM pg_stat_activity
   WHERE xact_start IS NOT NULL
     AND pid <> pg_backend_pid()
   ORDER BY xact_start
   LIMIT 10;

2. Step 2Set lock_timeout for the migration session

   lock_timeout aborts a statement that waits longer than the limit to get a lock, so an unlucky wait costs about the timeout instead of the length of someone's report query. Set it in the migration session only: the Postgres docs advise against a global value, which would also abort ordinary application statements.

   SET lock_timeout = '1s';

3. Step 3Retry with a pause and a limit

   Postgres does not retry a lock timeout, so your runner must. Retry only on the lock timeout error, pause between attempts so the queued readers drain, and stop loudly after a fixed number of tries. A migration that gives up leaves the schema unchanged.

   MAX_ATTEMPTS=20
   for attempt in $(seq 1 "$MAX_ATTEMPTS"); do
     if err=$(psql -v ON_ERROR_STOP=1 \
         -c "SET lock_timeout = '1s';" \
         -c "ALTER TABLE accounts ADD COLUMN note text;" 2>&1 >/dev/null); then
       echo "applied on attempt $attempt"
       exit 0
     fi
     echo "$err" | grep -q "lock timeout" || { echo "$err" >&2; exit 2; }
     sleep "$attempt"
   done
   echo "gave up after $MAX_ATTEMPTS attempts" >&2
   exit 1

4. Step 4Build indexes concurrently

   A plain CREATE INDEX blocks writes until it finishes. CREATE INDEX CONCURRENTLY takes a weaker lock and lets writes continue. It cannot run inside a transaction block, it waits for older transactions, and a failed build leaves an INVALID index behind that you drop and rebuild.

   CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);
   
   -- After a failure, find leftovers to drop with DROP INDEX CONCURRENTLY
   SELECT indexrelid::regclass AS index FROM pg_index WHERE NOT indisvalid;

5. Step 5Add constraints as NOT VALID, then validate

   Adding a foreign key or CHECK the plain way scans the table under a lock that blocks writes. Add it NOT VALID first, which commits at once and already rejects new bad rows, then run VALIDATE CONSTRAINT, which takes a lock that lets normal writes continue.

   ALTER TABLE orders ADD CONSTRAINT fk_orders_user
     FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
   ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

6. Step 6Get SET NOT NULL cheaply

   SET NOT NULL normally scans the table under the strong lock. It skips the scan when a valid CHECK constraint already proves the column has no nulls, so validate one first and drop it afterwards.

   ALTER TABLE orders ADD CONSTRAINT orders_user_not_null
     CHECK (user_id IS NOT NULL) NOT VALID;
   ALTER TABLE orders VALIDATE CONSTRAINT orders_user_not_null;
   ALTER TABLE orders ALTER COLUMN user_id SET NOT NULL;
   ALTER TABLE orders DROP CONSTRAINT orders_user_not_null;

Renames and type changes: expand, then contract

A rename in one statement breaks every running copy of the old app version the moment it commits. Ship it as several small releases instead, with the schema always compatible with both versions until the last step.

1. Expand: add the new column, plus a trigger that keeps old and new columns equal.
2. Backfill the new column in short batches, each in its own transaction, outside the schema migration.
3. Deploy the app version that reads and writes the new column, draining each instance before it stops.
4. Wait until you would no longer roll back to the old version.
5. Contract: drop the trigger and the old column, with the same lock timeout and retry as before.

During a stall, find the head of the queue

pg_blocking_pids lists the sessions in front of a waiting one. A reader's list names the ALTER, and the ALTER's list names the session holding the conflicting lock. Cancel the ALTER to release the readers, or end the holder to let the ALTER finish. The function takes lock manager locks, so use it to diagnose, not in a tight monitoring loop.

SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';