Skip to main content

Which ALTER TABLE statements lock a PostgreSQL table?

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 are metadata-only and release the lock in milliseconds. The dangerous ones rewrite the whole table — some type changes, and adding a column with a volatile default — holding that lock for the entire rewrite. The other trap is the lock queue: a blocked ALTER makes every query behind it wait too. Guard migrations with a short lock_timeout and retry.

Version-sensitive

The exact behavior of several of these operations changed across PostgreSQL versions. The notes below call out the version; always confirm against the major version you actually run.

The lock most ALTER TABLE statements take​

Most ALTER TABLE subcommands acquire an ACCESS EXCLUSIVE lock — the strongest lock, which blocks reads and writes on the table. What differs is how long it's held:

  • Metadata-only changes update the catalog and release the lock almost immediately.
  • Table-rewrite changes hold that lock while every row is rewritten — which on a large table can mean minutes of total unavailability.

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)
Widening varchar(n) → larger varchar or textBinary-coercible — no rewrite

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

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;

Not everything takes ACCESS EXCLUSIVE​

A few forms take weaker locks, which is worth knowing before you schedule a maintenance window you don't need:

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, so on a big table use NOT VALID first and VALIDATE CONSTRAINT after — that moves the scan under the weakest lock of the three.

Always set a lock_timeout for migrations. This is the single most important habit. Without it, a migration blocked behind a long query will itself block all new traffic:

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

Wrap it in a retry loop in your migration tooling: on lock_not_available, wait and try again, so a transient long query doesn't turn into an outage.

Why does AI-generated migration code get this wrong?​

Your AI coding agent (Claude Code, Cursor) writes the ALTER TABLE that matches the request — "add a status column with a default", "change id to bigint" — and it's syntactically perfect. What it can't see is that orders has 200 million rows, that id → bigint triggers a full rewrite, or that a reporting query holds a lock right now. Whether a statement is metadata-only or a multi-minute table rewrite depends on your data volume and your live workload, neither of which is in the source it reasons over.

How do I ship schema changes safely?​

  • Know which category each statement falls in before running it in production.
  • Prefer NOT VALID + VALIDATE for constraints and foreign keys.
  • 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.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the real context around a migration — the table's row count, its size, and what's currently holding locks — so it can warn that an ALTER will rewrite a huge table or block behind live traffic before you run it. It surfaces and explains; it does not execute your migrations. Get started free →

FAQ​

Does adding a column with a default rewrite the table? Since PostgreSQL 11, a constant (non-volatile) default is metadata-only. A volatile default (e.g. random()) 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 query holds a weaker lock, the ALTER waits — and every new query queues behind its pending lock, so one slow query plus one migration stalls the table.

How do I make a migration fail fast instead of blocking? SET lock_timeout (e.g. '3s') before the ALTER, so it errors instead of queuing when it can't get the lock, and retry in a loop.

Does changing a column type always rewrite the table? No. Binary-coercible changes (widening varchar, or to text) are metadata-only. Conversions like integer → bigint rewrite the whole table.