skip to content

Online Schema Changes

Postgres DDL is transactional, but many ALTER TABLE forms take ACCESS EXCLUSIVE or rewrite the table, and a waiting DDL blocks everyone behind it. Interviewers probe lock_timeout and safe migrations.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

During a PostgreSQL release, ALTER TABLE accounts ADD COLUMN waits for a lock and every query on accounts stalls behind it; why, and how do lock_timeout and retries prevent it?

level: seniorimportance: must knowfreq 66%

answer

  1. a waiter blocks later arrivals
  2. ACCESS EXCLUSIVE conflicts with ACCESS SHARE
  3. idle in transaction holder
  4. SET lock_timeout, then retry
  5. SQLSTATE 55P03 lock_not_available

basics

~20 s

The ALTER waits for ACCESS EXCLUSIVE behind a long transaction, and PostgreSQL queues every later conflicting request, even plain SELECTs, behind that waiter. A lock_timeout of a few seconds makes the DDL give up with 55P03 and retry later.

solid answer

~50 s

`ADD COLUMN` needs `ACCESS EXCLUSIVE`, which conflicts with the `ACCESS SHARE` lock that any open transaction which read `accounts` still holds, for example a long report or a session left idle in transaction. So the `ALTER` waits. PostgreSQL's lock manager then makes every new request that conflicts with a waiter's requested mode join the queue too, so each new `SELECT` on `accounts` lines up behind the `ALTER`, and the table is effectively down although the `ALTER` itself would take milliseconds. The fix is to bound the wait: `SET lock_timeout = '3s'` before the DDL, so it fails with `canceling statement due to lock timeout` (SQLSTATE `55P03`), the queue drains at once, and the runner retries with backoff. Set it per session or transaction, never in `postgresql.conf`, and also find and end the transaction that held the lock.

code

bash · 10 lines
bash
for attempt in 1 2 3 4 5; do
  if psql -X -v ON_ERROR_STOP=1 -d social \
       -c "SET lock_timeout = '3s'" \
       -c "ALTER TABLE accounts ADD COLUMN IF NOT EXISTS locale text NOT NULL DEFAULT 'en'"; then
    exit 0
  fi
  sleep $((attempt * 5 + RANDOM % 5))
done
echo "gave up: accounts lock not available" >&2
exit 1

go deeper

for a junior

Remember that DDL on a busy table can wait for a lock and that lock_timeout limits that wait.

for a middle

Explain that ACCESS EXCLUSIVE conflicts with every read and that a waiting request blocks later conflicting requests. Name the 55P03 error a lock timeout raises.

for a senior

Show the full playbook: per-step lock_timeout, short transactions, idempotent retries with backoff, catalog-only forms, and finding the long transaction or anti-wraparound vacuum that blocked the release.

for a principal

Turn it into team policy: a migration runner that sets lock_timeout and retries by default, release windows for the busiest tables, and alerts on long-open transactions.

## The timeline of the outage The scenario is a zero-downtime release that adds a column to a busy social network's `accounts` table: 1. **Session A** began a transaction minutes ago and read `accounts`: a long analytics query, or an application connection that ran a `SELECT` and is now **idle in transaction**. It holds `ACCESS SHARE` on `accounts` until its transaction ends. 2. **The migration** runs `ALTER TABLE accounts ADD COLUMN locale text NOT NULL DEFAULT 'en'`. This form is catalog-only and would finish in milliseconds, but it needs `ACCESS EXCLUSIVE`, which conflicts with A's `ACCESS SHARE`. It waits. 3. **Sessions C, D, E...** arrive with ordinary `SELECT`s on `accounts`. Their `ACCESS SHARE` does not conflict with A, yet they wait, behind the migration. 4. Connection pools fill, request latency climbs, and to users the site is down for as long as A stays open. ## Why later readers queue behind the waiter PostgreSQL's lock manager checks a new request against two things: locks already **granted** and locks other sessions are **waiting for**. If the new request conflicts with a waiter's requested mode, it joins the wait queue even though it is compatible with every granted lock. `ACCESS EXCLUSIVE` conflicts with every mode, so once it is waiting, nothing new on that table is granted. The rule exists to stop **starvation**: without it, a steady stream of readers could keep the strong lock waiting forever. The statement that causes the outage is therefore not the slow one. The `ALTER` does no work while waiting; it is the place in the queue it holds that hurts. The same effect, in a milder form, follows any waiting DDL. An `ADD FOREIGN KEY` waiting for `SHARE ROW EXCLUSIVE` does not stop plain readers, but every `INSERT`, `UPDATE` and `DELETE` that arrives after it queues behind it, on both tables it locks. So the rule is not specific to `ACCESS EXCLUSIVE`: any strong DDL lock that has to wait turns into a queue for whatever it conflicts with. ## `lock_timeout`: fail fast instead of waiting `lock_timeout` aborts any statement that waits longer than the given time for one lock on a table, index, row or other object. - Default `0`, meaning no limit; a value without units is milliseconds. - Any user can set it, per session (`SET lock_timeout = '3s'`) or per transaction (`SET LOCAL`). - It bounds each lock acquisition separately, not the statement's total runtime. - When it fires, the statement fails with `ERROR: canceling statement due to lock timeout`, SQLSTATE `55P03` (`lock_not_available`). - The manual advises against setting it in `postgresql.conf`, because it would then apply to every session. When the `ALTER` gives up, it leaves the queue, and the waiting `SELECT`s are granted at once because they never conflicted with A. The stall lasts at most the timeout, which you choose to be shorter than your users' latency budget. `statement_timeout` is a separate backstop that limits total runtime, including the work done after the lock is granted. ## Retry with backoff A timeout only helps if the step is retried. The pattern: - Run each strong-lock DDL in its **own short transaction**, so a retry repeats one step, not the release. - Retry on `55P03` with growing, jittered pauses, a fixed number of times, then stop and alert a human. - Make the step safe to repeat, for example `ADD COLUMN IF NOT EXISTS`. - Keep data backfills out of the strong-lock transaction, so that once the lock is granted it is released within milliseconds. ## Removing the blocker Retries succeed only when the holder goes away. Typical holders are long reports, sessions idle in transaction, and an autovacuum running to prevent transaction ID wraparound. An ordinary autovacuum is interrupted by a conflicting lock request; an anti-wraparound one is not, so the `ALTER` waits for it. Finding the blocking process and deciding whether to cancel or terminate it, and timeouts that end idle-in-transaction sessions automatically, are monitoring and connection topics; the migration's own job is to never wait unbounded. ## Checklist for a release | Step | Setting or action | |---|---| | Before the release | check for transactions open for more than a few seconds | | Each `ACCESS EXCLUSIVE` step | own transaction, `lock_timeout` of a few seconds | | On `55P03` | back off, retry a bounded number of times, then stop | | After the lock is granted | do only catalog work; batch any data changes separately |

  • Why does lock_timeout not protect you once a rewriting ALTER TABLE has acquired its lock in PostgreSQL?
    `lock_timeout` only limits waiting. Once the lock is granted, a rewrite holds `ACCESS EXCLUSIVE` for as long as the copy takes, and every other session waits for it. The protections there are choosing a catalog-only form, splitting the change into batched steps, or a `statement_timeout` that aborts the rewrite rather than letting it run unbounded.
  • Why can an anti-wraparound autovacuum cause this queue in PostgreSQL when an ordinary autovacuum does not?
    An ordinary autovacuum holds `SHARE UPDATE EXCLUSIVE`, and a conflicting lock request interrupts it, so the `ALTER` gets its lock almost at once. An autovacuum running to prevent wraparound, shown in `pg_stat_activity` with `(to prevent wraparound)`, is not interrupted, so the `ALTER` waits and the queue forms behind it.
  • How would you choose the lock_timeout value for a PostgreSQL migration step?
    Start from the stall users can tolerate, because every query on the table can wait up to that long per attempt. A few seconds is common; very small values can fail on every retry while normal short transactions hold the table, and zero means unbounded. Pair it with enough attempts and backoff to outlast typical transactions.

A narrow bridge that cars can cross side by side, but where a wide truck needs the whole span: once the truck (the ALTER) is waiting at the entrance for the bridge to empty, every car arriving behind it stops too, although those cars could have crossed alongside the ones already on it. lock_timeout tells the truck to pull aside after a few seconds and try later, and the cars flow again at once.

  • A waiting ALTER TABLE does not affect other sessions until it gets its lock.
  • Set lock_timeout in postgresql.conf so every session is protected.
  • lock_timeout limits how long the ALTER TABLE runs in total.
  • ADD COLUMN with a constant default is instant, so it cannot cause an outage.
  • Cancelling the waiting ALTER does not help; the readers still wait for the old transaction.
open as a page

In PostgreSQL, which ALTER TABLE forms rewrite the whole table and which only change the catalog, and how do you check before production?

level: seniorimportance: must knowfreq 58%

basics

~20 s

In PostgreSQL, ADD COLUMN with no or a non-volatile default, DROP COLUMN, SET DEFAULT, renames and varchar widening only change catalogs; volatile defaults, identity or stored generated columns and most type changes rewrite the table under a new relfilenode.

open as a page

In PostgreSQL, what does transactional DDL let a schema migration do, and which commands refuse to run inside a transaction block?

level: juniorimportance: should knowfreq 52%

basics

~20 s

PostgreSQL runs almost all DDL inside a transaction, so a migration that fails halfway rolls back completely. A few commands refuse a transaction block: CREATE INDEX CONCURRENTLY, VACUUM, CREATE or DROP DATABASE, CREATE TABLESPACE and ALTER SYSTEM.

open as a page

In PostgreSQL, which table lock do ADD COLUMN, ADD FOREIGN KEY, VALIDATE CONSTRAINT and RENAME COLUMN take, and what does each block?

level: middleimportance: should knowfreq 44%

basics

~20 s

In PostgreSQL, ADD COLUMN and RENAME COLUMN take ACCESS EXCLUSIVE, blocking everything; ADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables, blocking writes; VALIDATE CONSTRAINT takes SHARE UPDATE EXCLUSIVE, leaving reads and writes running.

open as a page

On a busy PostgreSQL 18 accounts table, how do you add a NOT NULL signup_country column that needs a backfill without a long ACCESS EXCLUSIVE scan?

level: seniorimportance: should knowfreq 37%

basics

~20 s

Add the column nullable and backfill in batches; then, in PostgreSQL 18, add the not-null constraint NOT VALID and VALIDATE it under SHARE UPDATE EXCLUSIVE. On 17, a validated CHECK (signup_country IS NOT NULL) lets SET NOT NULL skip its scan.

open as a page