The Complete Guide to PostgreSQL Lock Types for Schema Changes

If you run DDL against a production PostgreSQL database without understanding locks, you will cause an outage. That is not a maybe. This guide covers every lock level, which statements acquire which locks, and what that means for your running application. PostgreSQL Lock Levels: The Full Hierarchy PostgreSQL has eight table-level lock modes, ordered from least restrictive to most restrictive. Two locks conflict when one would violate the guarantees of the other. Understanding this hierarchy is the foundation of safe schema changes. Lock Level Blocks Reads? Blocks Writes? Acquired By ACCESS SHARE No No SELECT ROW SHARE No No SELECT FOR UPDATE/SHARE ROW EXCLUSIVE No No INSERT, UPDATE, DELETE SHARE UPDATE EXCLUSIVE No No VACUUM, CREATE INDEX CONCURRENTLY, ALTER TABLE (some) SHARE No Yes CREATE INDEX (without CONCURRENTLY) SHARE ROW EXCLUSIVE No Yes CREATE TRIGGER, some ALTER TABLE EXCLUSIVE No Yes REFRESH MATERIALIZED VIEW CONCURRENTLY ACCESS EXCLUSIVE Yes Yes DROP, TRUNCATE, most ALTER T