Which ALTER TABLE statements lock a PostgreSQL table?
Quick answer: Most
ALTER TABLEforms take anACCESS EXCLUSIVElock (a handful take weaker ones, notablyADD FOREIGN KEY, which needs onlySHARE 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 blockedALTERalso makes every query behind it wait. Guard migrations with a shortlock_timeoutand retry.
The lock most ALTER TABLE statements take
Section titled “The lock most ALTER TABLE statements take”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)”| Operation | Notes |
|---|---|
ADD COLUMN with no default | Always metadata-only (all versions). NULLs are supplied on readout |
ADD COLUMN with a constant default | Metadata-only since PostgreSQL 11 (the default is stored in the catalog) |
DROP COLUMN | Logical: the column is marked dropped; space is reclaimed later |
RENAME COLUMN / RENAME (table) | Catalog only |
ALTER COLUMN SET DEFAULT / DROP DEFAULT | Catalog only |
ADD CONSTRAINT ... CHECK (...) NOT VALID | Skips the validating scan; validate later (see below) |
ADD CONSTRAINT ... NOT NULL <col> NOT VALID | PostgreSQL 18+. Skips the validating scan; enforced on new rows immediately, validate later |
Widening varchar(n) → larger varchar or text | Binary-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)”| Operation | Notes |
|---|---|
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 NULL | Full-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 |
The safer patterns
Section titled “The safer patterns”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 changeALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null; -- SHARE UPDATE EXCLUSIVEThe 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.
Not everything takes ACCESS EXCLUSIVE
Section titled “Not everything takes ACCESS EXCLUSIVE”A few forms take weaker locks:
| Operation | Lock | Effect |
|---|---|---|
ADD FOREIGN KEY | SHARE ROW EXCLUSIVE, on both the referencing and referenced tables | Blocks writes, allows reads |
VALIDATE CONSTRAINT | SHARE UPDATE EXCLUSIVE | Allows reads and writes |
SET STATISTICS, CLUSTER ON, SET (attribute options) | SHARE UPDATE EXCLUSIVE | Allows 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 lockWrap 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.
How do I ship schema changes safely?
Section titled “How do I ship schema changes safely?”- Know which category each statement falls in before running it in production.
- Prefer
NOT VALID+VALIDATEfor check constraints, foreign keys, and, on PostgreSQL 18+,NOT NULL. - Set a short
lock_timeoutand 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.