The Postgres Lock Mode Cheat Sheet Nobody Gave You

The Postgres documentation on lock modes is thorough and precise. It is also 4,000 words long and assumes you already understand it.

Here’s the version I wish someone had given me two years ago.

The eight lock modes, ranked

Postgres has eight table-level lock modes. They’re ordered from weakest to strongest. A stronger lock conflicts with more things.

Lock Mode	What takes it	What it blocks
ACCESS SHARE	SELECT	Almost nothing
ROW SHARE	SELECT FOR UPDATE	EXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVE	INSERT, UPDATE, DELETE	SHARE and above
SHARE UPDATE EXCLUSIVE	VACUUM, CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE and above
SHARE	CREATE INDEX (non-concurrent)	ROW EXCLUSIVE and above (blocks writes)
SHARE ROW EXCLUSIVE	CREATE TRIGGER, ADD CONSTRAINT (FK, CHECK, UNIQUE, EXCLUDE)	ROW EXCLUSIVE and above
EXCLUSIVE	REFRESH MATERIALIZED VIEW CONCURRENTLY	ROW SHARE and above
ACCESS EXCLUSIVE	ALTER TABLE (most), DROP TABLE, TRUNCATE	Everything

The one that ruins your day is ACCESS EXCLUSIVE. It conflicts with every other lock mode, including ACCESS SHARE (which is what SELECT takes). That means while an ACCESS EXCLUSIVE lock is held, nobody can even read the table.

DDL operations and their lock modes

This is the mapping that matters for migrations. Every DDL statement acquires a specific lock mode, and it holds that lock for the duration of the operation.

ACCESS EXCLUSIVE (blocks reads + writes)

These are the dangerous ones. They lock the entire table, including reads:

• ALTER TABLE ... ADD COLUMN ... NOT NULL (without pre-existing constraint)
• ALTER TABLE ... ALTER COLUMN TYPE (rewrites entire table)
• ALTER TABLE ... SET NOT NULL (validates all existing rows)
• DROP TABLE
• TRUNCATE

SHARE ROW EXCLUSIVE (blocks writes, allows reads)

These hold a strong lock that blocks all writes but still allows reads:

• ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY (on both source and referenced table)
• ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (builds unique index inline)
• ALTER TABLE ... ADD CONSTRAINT ... EXCLUDE (builds exclusion index inline)
• ALTER TABLE ... ADD CONSTRAINT ... CHECK
• CREATE TRIGGER

SHARE (blocks writes but allows reads)

• CREATE INDEX (non-concurrent): blocks inserts/updates/deletes while the index is being built
• REINDEX TABLE (non-concurrent): locks out writes on the parent table while each index is rebuilt

SHARE UPDATE EXCLUSIVE (blocks DDL but allows reads + writes)

• CREATE INDEX CONCURRENTLY: the safe version
• VALIDATE CONSTRAINT: the second step of the NOT VALID pattern
• VACUUM (non-full)

(SHARE ROW EXCLUSIVE details above)

• ALTER TABLE ... ENABLE/DISABLE TRIGGER (SHARE ROW EXCLUSIVE)

The lock queue problem

Here’s the part most people miss. Locks don’t just block, they queue.

Say you run ALTER TABLE users ADD COLUMN ... (ACCESS EXCLUSIVE). It can’t acquire the lock immediately because there are active SELECT queries on the table. So it waits. But here’s the problem: while it’s waiting, every new query that needs any lock on that table also waits behind it.

Active queries:  [SELECT (ACCESS SHARE)]  ← running
Lock queue:      [ALTER TABLE (ACCESS EXCLUSIVE)]  ← waiting
                 [SELECT (ACCESS SHARE)]  ← blocked by ALTER TABLE in queue
                 [SELECT (ACCESS SHARE)]  ← also blocked
                 [INSERT (ROW EXCLUSIVE)]  ← also blocked

Your ALTER TABLE is waiting for existing queries to finish. But new queries are piling up behind it. If the existing queries take a while (maybe a complex report or a slow analytical query), your entire table becomes unreachable for the duration.

This is why SET lock_timeout is not optional. Without it:

-- This might wait forever if a long-running query is active
ALTER TABLE users ADD COLUMN email_verified boolean;

With it:

SET lock_timeout = '2s';
-- If the lock isn't acquired within 2 seconds, the statement fails
-- instead of blocking your entire table indefinitely
ALTER TABLE users ADD COLUMN email_verified boolean;

If the migration fails, you retry during a quieter window. Much better than blocking all traffic while you wait.

Safe alternatives for common operations

Dangerous Pattern	Safe Alternative	Lock Difference
ADD COLUMN ... NOT NULL DEFAULT	Add nullable → backfill → CHECK NOT VALID → VALIDATE → SET NOT NULL	Long ACCESS EXCLUSIVE rewrite → brief metadata locks plus SHARE UPDATE EXCLUSIVE validation
CREATE INDEX	CREATE INDEX CONCURRENTLY	SHARE → SHARE UPDATE EXCLUSIVE
ADD CONSTRAINT ... FOREIGN KEY	ADD CONSTRAINT ... NOT VALID + VALIDATE CONSTRAINT	Long SHARE ROW EXCLUSIVE scan → brief metadata lock plus SHARE UPDATE EXCLUSIVE validation
ADD CONSTRAINT ... UNIQUE	CREATE UNIQUE INDEX CONCURRENTLY + ADD CONSTRAINT ... USING INDEX	Long SHARE ROW EXCLUSIVE index build → SHARE UPDATE EXCLUSIVE plus brief attach
ALTER COLUMN TYPE	Add new column → backfill → swap → drop old	ACCESS EXCLUSIVE long rewrite → brief metadata locks plus batched ROW EXCLUSIVE work

Use this as a CI check

You don’t need to memorize this table. pgfence does the lookup for you:

npx @flvmnt/pgfence analyze migrations/*.sql

It maps every statement to the correct lock mode and tells you what’s blocked. If you want a stricter CI gate than the default:

npx @flvmnt/pgfence analyze --ci --max-risk low migrations/*.sql

The lock mode matrix is the foundation. Everything else, risk levels, safe rewrites, policy checks, is built on top of it.