Reference

Postgres lock mode reference

68 DDL patterns, one page per statement. Each one has the exact PostgreSQL lock mode it takes, what it blocks, pgfence's risk level, and a safe rewrite when one exists. For the narrative version of this table, see the lock mode cheat sheet and the DDL lock cheat sheet.

68 patterns

Columns

HIGH ACCESS EXCLUSIVE

ADD COLUMN ... NOT NULL without DEFAULT

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD COLUMN ... DEFAULT <volatile expression>

Blocks reads, writes, DDL
LOW ACCESS EXCLUSIVE

ADD COLUMN ... DEFAULT <constant>

Blocks reads, writes, DDL
LOW ACCESS EXCLUSIVE

ADD COLUMN ... DEFAULT now() (stable/current-value functions)

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD COLUMN ... DEFAULT on PostgreSQL < 11

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD COLUMN ... GENERATED ALWAYS AS (...) STORED

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD COLUMN ... GENERATED ALWAYS AS IDENTITY

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD COLUMN ... REFERENCES (inline foreign key)

Blocks reads, writes, DDL
LOW ACCESS EXCLUSIVE

ADD COLUMN ... REFERENCES ... NOT VALID (inline)

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD COLUMN using a constrained domain type

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER COLUMN ... TYPE

Blocks reads, writes, DDL
MEDIUM ACCESS EXCLUSIVE

ALTER COLUMN ... SET NOT NULL

Blocks reads, writes, DDL
MEDIUM ACCESS EXCLUSIVE

ALTER COLUMN ... DROP NOT NULL

Blocks reads, writes, DDL

Renaming

LOW ACCESS EXCLUSIVE

ALTER TABLE ... RENAME COLUMN

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... RENAME TO

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER SCHEMA ... RENAME TO

Blocks reads, writes, DDL

Indexes

MEDIUM SHARE

CREATE INDEX without CONCURRENTLY

Blocks writes, DDL
MEDIUM ACCESS EXCLUSIVE

DROP INDEX without CONCURRENTLY

Blocks reads, writes, DDL
HIGH SHARE

REINDEX TABLE without CONCURRENTLY

Blocks writes, DDL
HIGH ACCESS EXCLUSIVE

REINDEX INDEX without CONCURRENTLY

Blocks reads, writes, DDL
CRITICAL ACCESS EXCLUSIVE

REINDEX SCHEMA or REINDEX DATABASE without CONCURRENTLY

Blocks reads, writes, DDL

Constraints

HIGH SHARE ROW EXCLUSIVE

ADD CONSTRAINT ... FOREIGN KEY without NOT VALID

Blocks writes, DDL
MEDIUM ACCESS EXCLUSIVE

ADD CONSTRAINT ... CHECK without NOT VALID

Blocks reads, writes, DDL
LOW ACCESS EXCLUSIVE

ADD CONSTRAINT ... CHECK ... NOT VALID

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD CONSTRAINT ... UNIQUE

Blocks reads, writes, DDL
LOW ACCESS EXCLUSIVE

ADD CONSTRAINT ... UNIQUE USING INDEX

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD CONSTRAINT ... PRIMARY KEY without USING INDEX

Blocks reads, writes, DDL
MEDIUM ACCESS EXCLUSIVE

ADD CONSTRAINT ... PRIMARY KEY USING INDEX

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ADD CONSTRAINT ... EXCLUDE

Blocks reads, writes, DDL
LOW SHARE ROW EXCLUSIVE

CREATE TABLE ... with an inline EXCLUDE constraint

Blocks writes, DDL
LOW SHARE UPDATE EXCLUSIVE

VALIDATE CONSTRAINT (standalone)

Blocks DDL

Domains

HIGH SHARE

ALTER DOMAIN ... ADD CONSTRAINT

Blocks writes, DDL
LOW ACCESS SHARE

CREATE DOMAIN ... with a constraint

Blocks DDL

Destructive Operations

CRITICAL ACCESS EXCLUSIVE

DROP TABLE

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... DROP COLUMN

Blocks reads, writes, DDL
MEDIUM ACCESS EXCLUSIVE

ALTER TABLE ... DROP CONSTRAINT

Blocks reads, writes, DDL
CRITICAL ACCESS EXCLUSIVE

DROP SCHEMA

Blocks reads, writes, DDL
CRITICAL ACCESS EXCLUSIVE

DROP SCHEMA ... CASCADE

Blocks reads, writes, DDL
CRITICAL ACCESS EXCLUSIVE

TRUNCATE

Blocks reads, writes, DDL
CRITICAL ACCESS EXCLUSIVE

TRUNCATE ... CASCADE

Blocks reads, writes, DDL
HIGH ROW EXCLUSIVE

DELETE without WHERE

Blocks DDL
CRITICAL ACCESS EXCLUSIVE

DROP DATABASE

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

VACUUM FULL

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... SET LOGGED / SET UNLOGGED

Blocks reads, writes, DDL

Partitions

HIGH SHARE UPDATE EXCLUSIVE

ALTER TABLE ... ATTACH PARTITION

Blocks DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... ATTACH PARTITION on PostgreSQL 11 and earlier

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... DETACH PARTITION

Blocks reads, writes, DDL
LOW SHARE UPDATE EXCLUSIVE

ALTER TABLE ... DETACH PARTITION CONCURRENTLY

Blocks DDL

Materialized Views

HIGH ACCESS EXCLUSIVE

REFRESH MATERIALIZED VIEW (non-concurrent)

Blocks reads, writes, DDL
MEDIUM ACCESS EXCLUSIVE

REFRESH MATERIALIZED VIEW ... WITH NO DATA

Blocks reads, writes, DDL
LOW EXCLUSIVE

REFRESH MATERIALIZED VIEW CONCURRENTLY

Blocks writes, DDL

Triggers

MEDIUM SHARE ROW EXCLUSIVE

CREATE TRIGGER

Blocks writes, DDL
MEDIUM ACCESS EXCLUSIVE

DROP TRIGGER

Blocks reads, writes, DDL
LOW SHARE ROW EXCLUSIVE

ALTER TABLE ... ENABLE/DISABLE TRIGGER

Blocks writes, DDL

Enums

LOW EXCLUSIVE

ALTER TYPE ... ADD VALUE

Blocks writes, DDL
MEDIUM ACCESS EXCLUSIVE

ALTER TYPE ... ADD VALUE on PostgreSQL 11 and earlier

Blocks reads, writes, DDL
LOW ACCESS SHARE

CREATE TYPE ... AS ENUM

Blocks DDL

Row-Level Security

HIGH ACCESS EXCLUSIVE

ALTER TABLE ... ENABLE ROW LEVEL SECURITY

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... DISABLE ROW LEVEL SECURITY

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... FORCE ROW LEVEL SECURITY

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... NO FORCE ROW LEVEL SECURITY

Blocks reads, writes, DDL
MEDIUM ACCESS EXCLUSIVE

CREATE POLICY

Blocks reads, writes, DDL
MEDIUM ACCESS EXCLUSIVE

ALTER POLICY

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

DROP POLICY

Blocks reads, writes, DDL

Maintenance

HIGH ACCESS EXCLUSIVE

CLUSTER

Blocks reads, writes, DDL

Replication

HIGH ACCESS EXCLUSIVE

ALTER TABLE ... REPLICA IDENTITY FULL

Blocks reads, writes, DDL

Inheritance

HIGH ACCESS EXCLUSIVE

ALTER TABLE ... INHERIT

Blocks reads, writes, DDL
HIGH ACCESS EXCLUSIVE

ALTER TABLE ... NO INHERIT

Blocks reads, writes, DDL

How this reference is kept accurate

Every entry here is derived directly from pgfence's own rule implementations (src/rules/*.ts) and the LockMode / RiskLevel definitions in src/types.ts, not typed up separately and left to drift. Run the same checks against your own migrations: 

npx @flvmnt/pgfence analyze migrations/*.sql

Get started Try it in the browser →