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 is 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)

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)

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

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:

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 the moment the NOT VALID constraint lands.VALIDATE CONSTRAINT only checks the pre-existing rows. On PostgreSQL 17 and earlier there is no unvalidated not-null, 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

A few forms take weaker locks, which is worth knowing before you schedule a maintenance window you do not need:

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, 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 cannot 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 does not 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 is syntactically perfect. What it cannot 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 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.

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 is 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 cannot 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.

Related

• How to add a PostgreSQL index without downtime
• Why did my PostgreSQL query suddenly get slow?
• How to read a PostgreSQL EXPLAIN ANALYZE plan
• Product: DBGorilla docs