Skip to content

Which ALTER TABLE statements lock a PostgreSQL table?

Last updated

View as Markdown

Quick answer: Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock (a handful take weaker ones, notably ADD FOREIGN KEY, which needs only SHARE ROW EXCLUSIVE), but most operations only change metadata and release the lock in milliseconds. Operations that rewrite the whole table, such as some type changes and adding a column with a volatile default, hold the lock for the entire rewrite. A blocked ALTER also makes every query behind it wait. Guard migrations with a short lock_timeout and retry.

Most ALTER TABLE subcommands acquire an ACCESS EXCLUSIVE lock, the strongest lock. It blocks reads and writes on the table. The difference is how long the lock remains held:

  • Metadata-only changes update the catalog and release the lock almost immediately.
  • Table-rewrite changes hold the lock while every row is rewritten. On a large table, this can cause minutes of unavailability.

Metadata-only (fast, catalog change, no rewrite)

Section titled “Metadata-only (fast, catalog change, no rewrite)”
OperationNotes
ADD COLUMN with no defaultAlways metadata-only (all versions). NULLs are supplied on readout
ADD COLUMN with a constant defaultMetadata-only since PostgreSQL 11 (the default is stored in the catalog)
DROP COLUMNLogical: the column is marked dropped; space is reclaimed later
RENAME COLUMN / RENAME (table)Catalog only
ALTER COLUMN SET DEFAULT / DROP DEFAULTCatalog only
ADD CONSTRAINT ... CHECK (...) NOT VALIDSkips the validating scan; validate later (see below)
ADD CONSTRAINT ... NOT NULL <col> NOT VALIDPostgreSQL 18+. Skips the validating scan; enforced on new rows immediately, validate later
Widening varchar(n) → larger varchar or textBinary-coercible, no rewrite

Table-rewrite or full scan (slow, holds ACCESS EXCLUSIVE for the whole operation)

Section titled “Table-rewrite or full scan (slow, holds ACCESS EXCLUSIVE for the whole operation)”
OperationNotes
ALTER COLUMN ... TYPE requiring conversion (e.g. integer → bigint)Rewrites every row
ADD COLUMN with a volatile default (e.g. DEFAULT random())Rewrites every row
SET NOT NULLFull-table scan (not a heap rewrite) under ACCESS EXCLUSIVE. PostgreSQL 12+ can skip the scan entirely (an instant metadata change) if a validated CHECK (col IS NOT NULL) constraint already exists. On PostgreSQL 18+ you can avoid SET NOT NULL entirely: add the constraint NOT NULL <col> NOT VALID (instant) and then VALIDATE CONSTRAINT, which scans under SHARE UPDATE EXCLUSIVE instead

Validate constraints in two steps. Add the constraint NOT VALID (a quick metadata change), then validate it separately. VALIDATE CONSTRAINT takes a weaker SHARE UPDATE EXCLUSIVE lock that allows reads and writes:

ALTER TABLE orders ADD CONSTRAINT orders_total_positive
CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;

On PostgreSQL 18+ the same two-step pattern works for NOT NULL, because not-null constraints are now stored in pg_constraint like any other constraint:

{/* sql-check: pg 18+ */}

ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
NOT NULL region NOT VALID; -- instant catalog change
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null; -- SHARE UPDATE EXCLUSIVE

The column is enforced against new inserts and updates as soon as the NOT VALID constraint is added. VALIDATE CONSTRAINT checks only the pre-existing rows. On PostgreSQL 17 and earlier there is no unvalidated not-null constraint, so you still need the longer sequence: CHECK (col IS NOT NULL) NOT VALID → VALIDATE CONSTRAINT → SET NOT NULL → drop the now-redundant check.

A few forms take weaker locks:

OperationLockEffect
ADD FOREIGN KEYSHARE ROW EXCLUSIVE, on both the referencing and referenced tablesBlocks writes, allows reads
VALIDATE CONSTRAINTSHARE UPDATE EXCLUSIVEAllows reads and writes
SET STATISTICS, CLUSTER ON, SET (attribute options)SHARE UPDATE EXCLUSIVEAllows reads and writes

Adding a foreign key still scans the referencing table to verify existing rows. On a big table, use NOT VALID first and run VALIDATE CONSTRAINT afterward. This moves the scan under the weakest lock of the three.

Always set a lock_timeout for migrations. Without it, a migration blocked behind a long query will block later traffic:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN notes text; -- fails fast if it cannot get the lock

Wrap it in a retry loop in your migration tooling: on lock_not_available, wait and try again. This prevents a transient long query from causing an outage.

Why does AI-generated migration code get this wrong?

Section titled “Why does AI-generated migration code get this wrong?”

Your AI coding agent writes the ALTER TABLE that matches the request (“add a status column with a default”, “change id to bigint”), and the SQL is syntactically correct. It cannot see that orders has 200 million rows, that id → bigint triggers a full rewrite, or that a reporting query currently holds a lock. Whether a statement is metadata-only or a multi-minute table rewrite depends on your data volume and live workload, neither of which appears in the source it reasons over.

  • Know which category each statement falls in before running it in production.
  • Prefer NOT VALID + VALIDATE for check constraints, foreign keys, and, on PostgreSQL 18+, NOT NULL.
  • Set a short lock_timeout and retry on every migration.
  • For a rewriting type change on a big table, consider the add-new-column / backfill / swap approach instead of an in-place ALTER COLUMN TYPE.
  • Does adding a column with a default rewrite the table in PostgreSQL?

    Since PostgreSQL 11, adding a column with a constant (non-volatile) default is metadata-only and does not rewrite the table. Adding a column with a volatile default, such as one calling random() or a volatile function, still rewrites every row. On PostgreSQL 10 and earlier, any default rewrote the table.

  • Why did a quick ALTER TABLE freeze my whole application?

    ALTER TABLE needs an ACCESS EXCLUSIVE lock. If a long-running query holds a weaker lock on the table, the ALTER waits, and while it waits, every new query queues behind its pending ACCESS EXCLUSIVE lock. One slow query plus one migration can stall all traffic to the table.

  • How do I make a migration fail fast instead of blocking traffic?

    Set a short lock_timeout before the ALTER (for example SET lock_timeout = 3000). If the lock cannot be acquired in that window the statement errors instead of queuing, and you retry in a loop. This keeps a blocked migration from stalling the table.

  • Does changing a column type always rewrite the table?

    No. Binary-coercible changes such as widening varchar(10) to varchar(20) or to text are metadata-only. Type changes that require converting the stored value, for example integer to bigint, rewrite the whole table under an ACCESS EXCLUSIVE lock.