What lock does each DDL statement actually take? A cheat sheet verified against the PostgreSQL source
Every Postgres DDL statement takes a lock. Most cheat sheets on the internet get at least one wrong. This one is verified line by line against tablecmds.c, lockcmds.c, and indexcmds.c in PostgreSQL 17.
Every cheat sheet for Postgres DDL lock modes that I have seen on the internet has at least one error. The most common mistakes:
- Treating every
ADD CONSTRAINTform as one lock mode. Foreign keys useSHARE ROW EXCLUSIVE; CHECK, UNIQUE, PRIMARY KEY, EXCLUDE, andUSING INDEXforms useACCESS EXCLUSIVEon the target table. - Listing
REFRESH MATERIALIZED VIEW CONCURRENTLYasSHARE UPDATE EXCLUSIVE(it isEXCLUSIVE) - Listing
ALTER TYPE ADD VALUEasSHARE UPDATE EXCLUSIVEon PG12+ (the type-object lock isEXCLUSIVE, not a table lock at all) - Listing
REINDEX TABLEasACCESS EXCLUSIVE(it isSHAREon the table,ACCESS EXCLUSIVEonly on the index) - Listing
ATTACH PARTITIONasACCESS EXCLUSIVEon PG12+ (it isSHARE UPDATE EXCLUSIVEon the parent)
This post is the cheat sheet we use inside pgfence v0.6, with the source file and line of code that gives each lock its level. If you find a row that disagrees with the Postgres source, please open an issue and we will fix it.
The eight lock modes, ranked
From src/include/storage/lockdefs.h:
| # | Lock mode | Blocks |
|---|---|---|
| 1 | ACCESS SHARE | Only ACCESS EXCLUSIVE |
| 2 | ROW SHARE | EXCLUSIVE, ACCESS EXCLUSIVE |
| 3 | ROW EXCLUSIVE | SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
| 4 | SHARE UPDATE EXCLUSIVE | SHARE UPDATE EXCLUSIVE and above |
| 5 | SHARE | ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |
| 6 | SHARE ROW EXCLUSIVE | ROW EXCLUSIVE and above |
| 7 | EXCLUSIVE | Everything except ACCESS SHARE |
| 8 | ACCESS EXCLUSIVE | Everything |
The colloquial way to read this: anything at level 5 or above blocks writes, only level 8 blocks reads.
ALTER TABLE subcommands
Source: AlterTableGetLockLevel() in src/backend/commands/tablecmds.c.
| Subcommand | Lock mode | Blocks reads | Blocks writes |
|---|---|---|---|
ADD COLUMN (no default) | ACCESS EXCLUSIVE | Yes | Yes |
ADD COLUMN ... DEFAULT <constant> (PG 11+) | ACCESS EXCLUSIVE (metadata only, fast) | Yes (briefly) | Yes (briefly) |
ADD COLUMN ... DEFAULT <volatile> | ACCESS EXCLUSIVE (full rewrite) | Yes | Yes |
ADD COLUMN ... NOT NULL (no default) | ACCESS EXCLUSIVE (full scan) | Yes | Yes |
DROP COLUMN | ACCESS EXCLUSIVE | Yes | Yes |
ALTER COLUMN TYPE (binary-coercible) | ACCESS EXCLUSIVE (fast) | Yes (briefly) | Yes (briefly) |
ALTER COLUMN TYPE (rewrite required) | ACCESS EXCLUSIVE (slow) | Yes | Yes |
ALTER COLUMN SET NOT NULL | ACCESS EXCLUSIVE | Yes | Yes |
ALTER COLUMN DROP NOT NULL | ACCESS EXCLUSIVE | Yes | Yes |
ALTER COLUMN SET DEFAULT | ACCESS EXCLUSIVE (metadata) | Yes (briefly) | Yes (briefly) |
ALTER COLUMN DROP DEFAULT | ACCESS EXCLUSIVE (metadata) | Yes (briefly) | Yes (briefly) |
ADD CONSTRAINT ... CHECK | ACCESS EXCLUSIVE | Yes | Yes |
ADD CONSTRAINT ... FOREIGN KEY | SHARE ROW EXCLUSIVE (both tables) | No | Yes (both tables) |
ADD CONSTRAINT ... UNIQUE (without USING INDEX) | ACCESS EXCLUSIVE | Yes | Yes |
ADD CONSTRAINT ... EXCLUDE | ACCESS EXCLUSIVE | Yes | Yes |
ADD CONSTRAINT ... PRIMARY KEY (without USING INDEX) | ACCESS EXCLUSIVE | Yes | Yes |
ADD CONSTRAINT ... USING INDEX (UNIQUE or PK) | ACCESS EXCLUSIVE | Yes (briefly) | Yes (briefly) |
VALIDATE CONSTRAINT | SHARE UPDATE EXCLUSIVE | No | No |
DROP CONSTRAINT | ACCESS EXCLUSIVE | Yes | Yes |
RENAME COLUMN | ACCESS EXCLUSIVE (instant on PG14+) | Yes (briefly) | Yes (briefly) |
RENAME (table) | ACCESS EXCLUSIVE | Yes | Yes |
SET TABLESPACE | ACCESS EXCLUSIVE | Yes | Yes |
SET LOGGED / SET UNLOGGED | ACCESS EXCLUSIVE | Yes | Yes |
SET STATISTICS | SHARE UPDATE EXCLUSIVE | No | No |
ATTACH PARTITION (PG12+) | SHARE UPDATE EXCLUSIVE (parent) | No | No |
DETACH PARTITION | ACCESS EXCLUSIVE | Yes | Yes |
DETACH PARTITION CONCURRENTLY (PG14+) | SHARE UPDATE EXCLUSIVE | No | No |
REPLICA IDENTITY (any form) | ACCESS EXCLUSIVE | Yes (briefly) | Yes (briefly) |
Index operations
Source: src/backend/commands/indexcmds.c and src/backend/commands/cluster.c.
| Statement | Lock on table | Lock on index | Notes |
|---|---|---|---|
CREATE INDEX | SHARE | ACCESS EXCLUSIVE | Blocks writes for the whole build |
CREATE INDEX CONCURRENTLY | SHARE UPDATE EXCLUSIVE | SHARE | Allows reads and writes |
DROP INDEX | ACCESS EXCLUSIVE (table) | ACCESS EXCLUSIVE (index) | Blocks everything |
DROP INDEX CONCURRENTLY | SHARE UPDATE EXCLUSIVE | ACCESS EXCLUSIVE (briefly) | Allows reads and writes |
REINDEX INDEX | n/a | ACCESS EXCLUSIVE | Table is implicit SHARE |
REINDEX TABLE | SHARE | ACCESS EXCLUSIVE (per index) | Reads OK, writes blocked |
REINDEX TABLE CONCURRENTLY | SHARE UPDATE EXCLUSIVE | varies | Allows reads and writes |
REINDEX SCHEMA | per-table SHARE | per-index ACCESS EXCLUSIVE | Long held; avoid in tx |
CLUSTER table | ACCESS EXCLUSIVE | ACCESS EXCLUSIVE | No CONCURRENTLY form. Use pg_repack. |
Type, sequence, and view operations
Source: src/backend/commands/typecmds.c, src/backend/commands/sequence.c, src/backend/commands/matview.c.
| Statement | Lock | Object | Notes |
|---|---|---|---|
ALTER TYPE ADD VALUE (PG12+) | EXCLUSIVE | type object | Not a table lock. Instant. |
ALTER TYPE ADD VALUE (PG11 and earlier) | ACCESS EXCLUSIVE | type object | Cannot run inside a transaction |
ALTER TYPE RENAME VALUE | ACCESS EXCLUSIVE | type object | |
CREATE SEQUENCE | ACCESS EXCLUSIVE | new object | |
ALTER SEQUENCE | ACCESS EXCLUSIVE | sequence | |
CREATE MATERIALIZED VIEW | ACCESS EXCLUSIVE | new object | |
REFRESH MATERIALIZED VIEW | ACCESS EXCLUSIVE | matview | Blocks reads |
REFRESH MATERIALIZED VIEW CONCURRENTLY | EXCLUSIVE | matview | Allows reads but blocks other refreshes |
The last row is worth highlighting. Many cheat sheets list this as SHARE UPDATE EXCLUSIVE. From matview.c:
/* Concurrent refresh takes EXCLUSIVE lock to serialize against other refreshes */
matviewOid = RangeVarGetRelidExtended(stmt->relation, ExclusiveLock, 0, ...);It is EXCLUSIVE, not SHARE UPDATE EXCLUSIVE. Reads still work because EXCLUSIVE only blocks ROW SHARE and above, and plain SELECT takes ACCESS SHARE.
Schema, role, and database operations
| Statement | Lock | Object | Notes |
|---|---|---|---|
CREATE SCHEMA | ACCESS EXCLUSIVE | namespace | |
DROP SCHEMA ... CASCADE | ACCESS EXCLUSIVE | every dropped object | Walks deps. Catastrophic if wrong. |
DROP TABLE | ACCESS EXCLUSIVE | table | |
TRUNCATE | ACCESS EXCLUSIVE | table | Use batched DELETE instead |
CREATE TRIGGER | SHARE ROW EXCLUSIVE | table | (Corrected in pgfence v0.5) |
DROP TRIGGER | ACCESS EXCLUSIVE | table | |
VACUUM | SHARE UPDATE EXCLUSIVE | table | |
VACUUM FULL | ACCESS EXCLUSIVE | table | Use pg_repack for large tables |
How pgfence keeps this honest
This table lives in pgfence’s source as src/rules/lock-modes.ts. Every entry has at least one test that:
- Statically maps the AST node to the predicted lock mode
- Executes the statement against a real Postgres container in trace mode
- Polls
pg_locksduring execution and asserts the observed lock matches the prediction
If you upgrade Postgres and a lock mode changes (it has happened: see ATTACH PARTITION between PG11 and PG12), the trace tests will fail and we fix the table. Trace mode is built into the CLI, so you can verify any statement against your own Postgres version:
npx @flvmnt/pgfence trace migrations/your_migration.sqlThe output includes a “Verified” column for every statement: Confirmed, Mismatch, or Trace-only. A mismatch is a bug we want to know about.
Bookmark and share
This page is the cheat sheet I wish I had had three years ago. It is also a living document: PostgreSQL 18 will tweak two of these lock levels (we are tracking the patches), and pgfence’s source will update the day they land in tablecmds.c.
If you spot a row that disagrees with the Postgres source, please open an issue. Verified corrections only, with a source link.