Table-level locks in PostgreSQL: which DDL blocks even SELECT on a live table Intermediate

Problem

A shop has an orders table with 100,000 rows, receiving continuous INSERTs and UPDATEs from checkout. The team needs to add a payment_status column, an index on status, and a total_cents > 0 constraint.

A row-level lock only locks the rows being written. DDL locks the whole table, in one of eight modes. Which mode does each DDL take, whom does it block, and how do you rewrite it to lock less?

What to choose (summary first)

Situation on orders	Choose	Condition
Add a column	ADD COLUMN nullable or with a constant DEFAULT	Metadata only; set lock_timeout
Add an index	CREATE INDEX CONCURRENTLY	Outside a transaction block; check for an INVALID index
Add a CHECK or foreign key	ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT	Two statements, two transactions
Change a column type that rewrites	New column + batched backfill (expand–contract)	Avoid copying the table under a strong lock

Core concepts

A table-level lock is a lock on a relation (table, index), shown in pg_locks with locktype = 'relation'. Each statement takes the mode it needs automatically and holds it until the end of the transaction. Names containing “ROW” are still table-level locks.

Eight modes and typical statements

In pg_locks.mode the names are written in CamelCase: AccessShareLock, RowExclusiveLock…

#	Mode	Typical statements
1	ACCESS SHARE	SELECT
2	ROW SHARE	SELECT ... FOR UPDATE/SHARE
3	ROW EXCLUSIVE	INSERT, UPDATE, DELETE, MERGE
4	SHARE UPDATE EXCLUSIVE	VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VALIDATE CONSTRAINT
5	SHARE	CREATE INDEX
6	SHARE ROW EXCLUSIVE	CREATE TRIGGER, ADD FOREIGN KEY
7	EXCLUSIVE	REFRESH MATERIALIZED VIEW CONCURRENTLY
8	ACCESS EXCLUSIVE	Most ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL, CLUSTER, LOCK TABLE

Conflict matrix

X = two different transactions cannot hold both. Bold cells are the usual sources of migration incidents.

Held \ Requested	ACC. SHARE	ROW SHARE	ROW EXCL.	SHARE UPD. EXCL.	SHARE	SHARE ROW EXCL.	EXCL.	ACC. EXCL.
ACC. SHARE								X
ROW SHARE							X	X
ROW EXCL.					X	X	X	X
SHARE UPD. EXCL.				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCL.			X	X	X	X	X	X
EXCL.		X	X	X	X	X	X	X
ACC. EXCL.	X	X	X	X	X	X	X	X

Three takeaways:

• Only ACCESS EXCLUSIVE blocks a plain SELECT: DDL taking this mode stops both reads and writes.
• SHARE (plain CREATE INDEX) allows reads but blocks every write.
• SHARE UPDATE EXCLUSIVE blocks neither reads nor writes but conflicts with itself (VACUUM waits for CREATE INDEX CONCURRENTLY).

How it works

A migration runs CREATE INDEX then ALTER TABLE on orders while the API reads and writes:

CREATE INDEX blocks only INSERT; ALTER TABLE blocks both SELECT and INSERT until commit

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

The migration runs CREATE INDEX inside a transaction and holds SHARE on orders.

1. The migration holds SHARE: SELECT (ACCESS SHARE) runs, INSERT (ROW EXCLUSIVE) waits.
2. The migration commits and releases SHARE; the INSERT runs.
3. The migration holds ACCESS EXCLUSIVE: both SELECT and INSERT wait.
4. The migration commits; both requests continue.

The blocking time equals the time the lock is held plus the time spent waiting to acquire it. If the DDL has to wait for a long transaction, queries arriving later queue behind it: see lock queue.

Operational details

Common DDL → lock mode

ALTER TABLE takes ACCESS EXCLUSIVE by default, except for subforms noted otherwise. Checked against the PostgreSQL 17 docs and the demo:

Statement on orders	Lock on the table	Time-consuming part
ADD COLUMN nullable / non-volatile DEFAULT	ACCESS EXCLUSIVE	None; metadata only
ADD COLUMN ... DEFAULT gen_random_uuid()	ACCESS EXCLUSIVE	Rewrites the table and indexes
ALTER COLUMN TYPE bigint → numeric	ACCESS EXCLUSIVE	Rewrite (varchar(50) → varchar(100) does not)
SET NOT NULL	ACCESS EXCLUSIVE	Scan, unless a valid CHECK exists
ADD CONSTRAINT CHECK	ACCESS EXCLUSIVE	Scan; NOT VALID skips the scan
ADD FOREIGN KEY	SHARE ROW EXCLUSIVE (both tables)	Scan; NOT VALID skips the scan
VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	Scan, does not block reads or writes
CREATE INDEX	SHARE	Builds the index, blocks writes
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	Two scans, waits for old transactions
REINDEX TABLE	SHARE, plus ACCESS EXCLUSIVE on each index	Rebuilds indexes
DROP TABLE, TRUNCATE, DROP COLUMN, RENAME	ACCESS EXCLUSIVE	Short
VACUUM FULL, CLUSTER	ACCESS EXCLUSIVE	Copies the entire table

An ALTER TABLE with several subcommands takes the strongest lock among them.

Turning heavy DDL into light DDL

Loading diagram…

Principle: ACCESS EXCLUSIVE should only be held while changing metadata. Scans, index builds, and data copies move to SHARE UPDATE EXCLUSIVE statements or to batched code.

Real-world example

Three changes on an orders table that is taking checkouts

The three changes from the Problem section, rewritten as four autocommit statements (see the mistakes section): only ADD COLUMN and ADD CONSTRAINT ... NOT VALID take ACCESS EXCLUSIVE, and neither scans. The index and validation run under SHARE UPDATE EXCLUSIVE, so checkout can keep writing.

Demo: seeing lock modes through pg_locks

PostgreSQL 17.11, schema demo_table_level_locks. Each DDL runs in an open transaction, reads the session’s own locks, then ROLLBACKs.

CREATE VIEW my_locks AS
  SELECT c.relname, l.mode, l.granted
  FROM pg_locks l JOIN pg_class c ON c.oid = l.relation
  WHERE l.pid = pg_backend_pid() AND l.locktype = 'relation'
    AND c.relname IN ('orders', 'orders_pkey', 'customers');

BEGIN;
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers(id) NOT VALID;
SELECT * FROM my_locks;
ROLLBACK;

  relname  |         mode          | granted
-----------+-----------------------+---------
 orders    | AccessShareLock       | t
 orders    | ShareRowExclusiveLock | t
 customers | AccessShareLock       | t
 customers | ShareRowExclusiveLock | t

Summary of the other runs (locks on orders; rewritten compares pg_relation_filenode before and after):

ADD COLUMN ... DEFAULT gen_random_uuid() AccessExclusiveLock, rewritten = t
ADD COLUMN ... NOT NULL DEFAULT 'web'   rewritten = f
ALTER COLUMN total_cents TYPE numeric   AccessExclusiveLock, rewritten = t
VALIDATE CONSTRAINT                     ShareUpdateExclusiveLock
CREATE INDEX                            ShareLock
REINDEX TABLE                           ShareLock; orders_pkey: AccessExclusiveLock

CREATE INDEX CONCURRENTLY observed from another session while the writing transaction long_tx is still open:

   app   |           mode           | granted | wait_type | wait_event
---------+--------------------------+---------+-----------+------------
 cic     | ShareUpdateExclusiveLock | t       | Lock      | virtualxid
 long_tx | RowExclusiveLock         | t       | Timeout   | PgSleep

The statement already has its lock but waits for long_tx to finish; new INSERTs still complete. The diagram’s scenario:

    app    |        mode         | granted | blocked_by_pid
-----------+---------------------+---------+----------------
 api_read  | AccessShareLock     | f       | {16832}
 api_write | RowExclusiveLock    | f       | {16832}
 migration | AccessExclusiveLock | t       | {}

The pid changes on every run.

Trade-offs

Pros

• CONCURRENTLY and NOT VALID keep the table serving reads and writes while the heavy part runs.

• Each small step can be retried on its own under lock_timeout.

< slot="cons">

• The migration has many steps and is no longer atomic.

• CONCURRENTLY is slower and can leave an INVALID index behind.

Cons

🎮 Try it

• A migration that locks the whole table: which DDL takes which lock, lock_timeout, and NOT VALID.

Production checklist

1. Note the lock mode of each statement when reviewing a migration; ACCESS EXCLUSIVE statements must neither rewrite nor scan.
2. SET lock_timeout for the migration session, and retry with backoff.
3. Do not bundle DDL into one transaction; turn off the tool’s automatic transaction for CONCURRENTLY.
4. After CREATE INDEX CONCURRENTLY, check pg_index.indisvalid.
5. Add CHECKs and foreign keys with NOT VALID; validate in a separate step.
6. Before running, check pg_stat_activity for long transactions on the table.

Common mistakes

A short DDL still holds its lock for long if the transaction is long:

Bundling every change into one transaction

✕ Common

BEGIN;
ALTER TABLE orders ADD COLUMN payment_status text;
CREATE INDEX orders_status_idx ON orders (status);
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total_cents > 0);
COMMIT;

✓ Prefer

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN payment_status text;
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total_cents > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT total_positive;

Why

The first version holds ACCESS EXCLUSIVE from the first statement until COMMIT. The second runs in autocommit: each statement releases its lock when done.

Another misunderstanding:

REINDEX TABLE still blocks SELECT

REINDEX only takes SHARE on the table, but takes ACCESS EXCLUSIVE on the indexes. The planner takes ACCESS SHARE on every index of the table while planning, so nearly every query waits.

Demo: a SELECT forced into a seq scan still waits on orders_pkey. On a live table, use REINDEX ... CONCURRENTLY.

Misconception	Consequence	What to do
ADD COLUMN is fast so it needs no lock_timeout	The DDL waits for a long transaction and later queries queue up	Always set lock_timeout
NOT VALID means skipping the check	Old violating data goes undetected	Run VALIDATE

Self-check

1. Why does a plain CREATE INDEX let SELECT run but block INSERT, while ADD COLUMN blocks both?
2. DEFAULT 'web' and DEFAULT gen_random_uuid() both take ACCESS EXCLUSIVE. Why do their lock hold times differ?
3. CREATE INDEX CONCURRENTLY is already granted = t but not finished. What is it waiting for, and are new INSERTs blocked?

Summary

Key takeaways

• Eight table lock modes, held until the end of the transaction; only ACCESS EXCLUSIVE blocks a plain SELECT.
• Most ALTER TABLE, plus DROP, TRUNCATE, VACUUM FULL, take ACCESS EXCLUSIVE; CREATE INDEX takes SHARE, blocking writes.
• The risk lies in hold time: rewrites and scans under ACCESS EXCLUSIVE.
• Turn heavy DDL into light DDL: CONCURRENTLY, NOT VALID + VALIDATE, new column + backfill.

References

• PostgreSQL 17 — Table-Level Locks
• PostgreSQL 17 — ALTER TABLE
• PostgreSQL 17 — Building Indexes Concurrently
• PostgreSQL 17 — Rebuilding Indexes Concurrently