ALTER TABLE Locks in Postgres: The Reference I Wish I Had Five Years Ago

During my first production deploy of an ALTER TABLE, I assumed it was fast because it was just metadata. Forty seconds later, when the application was timing out, I learned what ACCESS EXCLUSIVE lock means in practice.

The Postgres docs do cover which operations take which locks. They cover it in seven different subsections of the documentation. Here is the consolidated cheat sheet I keep handy.

The lock levels that matter

Postgres has eight lock levels. For ALTER TABLE, three matter:

• ACCESS EXCLUSIVE — blocks every other operation, including reads. The most aggressive lock.
• SHARE UPDATE EXCLUSIVE — blocks DDL and vacuum, but not normal reads or writes. Allows concurrent traffic.
• SHARE ROW EXCLUSIVE — blocks writes, allows reads.

The operations break down by which lock they take. Some are instant; some hold the lock for the full duration of a table rewrite.

Operations that are FAST (metadata only, ACCESS EXCLUSIVE briefly)

These take ACCESS EXCLUSIVE but only for milliseconds. Safe on busy tables most of the time:

• RENAME COLUMN
• RENAME TABLE
• ALTER COLUMN ... DROP DEFAULT
• ALTER COLUMN ... SET STATISTICS
• ADD COLUMN ... NOT NULL DEFAULT <constant> (Postgres 11+)
• ALTER COLUMN ... DROP NOT NULL
• DROP CONSTRAINT (most cases)

The brief ACCESS EXCLUSIVE can still queue up queries. On a heavily-loaded busy table, even a 100ms lock window can cause a request spike. But the operation itself is fast.

Operations that are SLOW (full table rewrite, ACCESS EXCLUSIVE)

These rewrite every row in the table. They take ACCESS EXCLUSIVE for the duration. On a 100GB table, that is many minutes of total write/read blocking:

• ALTER COLUMN ... TYPE ... when the new type requires conversion (most type changes)
• ADD COLUMN ... DEFAULT <volatile> (e.g., DEFAULT now()) — the rewrite is needed to fill in per-row values
• SET WITHOUT CLUSTER does not need it, but CLUSTER itself does

For these, never run on a busy table directly. Use the expand-and-contract pattern: add new column, backfill, swap, drop old.

Operations that VALIDATE (table scan under SHARE UPDATE EXCLUSIVE)

These scan the table but allow concurrent reads and writes:

• ADD CONSTRAINT ... CHECK ... NOT VALID followed by VALIDATE CONSTRAINT
• ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID followed by VALIDATE CONSTRAINT

The two-step pattern is the right approach for adding constraints to busy tables. NOT VALID adds the constraint immediately (no scan, fast), and VALIDATE does the table scan in a non-blocking way:

-- Step 1: add constraint (fast, no scan)
ALTER TABLE orders
  ADD CONSTRAINT orders_total_positive
  CHECK (total_cents >= 0) NOT VALID;

-- Step 2: validate existing rows (slow, but no exclusive lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;

The CONCURRENTLY family

For indexes, CONCURRENTLY provides a non-blocking alternative:

• CREATE INDEX CONCURRENTLY — builds the index in the background, no exclusive lock.
• DROP INDEX CONCURRENTLY — drops without blocking.
• REINDEX CONCURRENTLY — rebuilds without blocking.

The trade-off: these run more slowly than the non-concurrent variants and can fail partway through (leaving an INVALID index that you have to clean up). For production tables, the trade-off is almost always worth it.

Note: CREATE INDEX CONCURRENTLY cannot run inside a transaction. Many migration tools wrap migrations in transactions by default; you need a way to opt out.

Operations that quietly take the strongest lock

Watch out for these because they look harmless:

• ALTER TABLE ... ENABLE ROW LEVEL SECURITY — ACCESS EXCLUSIVE, brief.
• ALTER TABLE ... ATTACH PARTITION (in older Postgres) — could be expensive. Postgres 14+ improved this.
• ADD UNIQUE (without USING INDEX) — builds the unique index under exclusive lock. Use CREATE UNIQUE INDEX CONCURRENTLY first, then promote.

The last one is the most common surprise. Adding a UNIQUE constraint on a large table directly is an outage-shaped operation. The right pattern:

-- Build the index without locks
CREATE UNIQUE INDEX CONCURRENTLY orders_email_unique
  ON orders (email);

-- Promote it to a constraint (fast)
ALTER TABLE orders
  ADD CONSTRAINT orders_email_unique UNIQUE USING INDEX orders_email_unique;

A sanity-check script

Before running any migration on a busy table, this query tells you what is currently active:

SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND backend_type = 'client backend';

If there is a long-running query, hold the migration until it finishes. The migration will get queued behind the existing query, and any other queries that arrive in the meantime will queue behind your migration.

For the cautious version, set a lock_timeout so the migration fails fast instead of waiting forever:

SET lock_timeout = '5s';
ALTER TABLE ...;

If the lock cannot be acquired in 5 seconds, the migration aborts with a clear error. Better than blocking traffic for an hour.

What I do for every migration on a busy table

• Check the operation against this list. If it takes ACCESS EXCLUSIVE for a long duration, rewrite it as expand-and-contract.
• Use CONCURRENTLY for index work.
• Use NOT VALID + VALIDATE for constraints.
• Set lock_timeout to fail fast if the lock is contended.
• Run during low-traffic windows when possible.

These five rules cover the majority of "why did the migration cause an outage" stories. The Postgres docs technically tell you everything; they just do not collect it in one place. This list is what I would have wanted, five years ago.