Understanding PostgreSQL Table Locks Before a Migration Bites You

PostgreSQL lock behavior usually becomes interesting at exactly the wrong moment: during a schema change on a busy table. Everything looks harmless in staging, the migration appears simple, and then production traffic starts backing up because one statement is waiting on a lock it cannot get. 🚨

The official documentation covers this topic correctly, but it is dense and not especially friendly when you need a quick operational answer. What matters in day-to-day work is much simpler: which table lock does a statement take, what conflicts with it, and whether it will stop reads, writes, or both.

This article is that practical version. It walks through the eight table-level lock modes in PostgreSQL, explains which DDL commands commonly take them, and highlights the queueing behavior that causes real incidents. 📌 If you work on migrations, deployment safety, or database-heavy systems, this is the mental model you want ready before you ship.

PostgreSQL defines eight table-level lock modes. They go from least restrictive to most restrictive. As you move upward, each mode clashes with more operations.

Here is the useful ranking:

• ACCESS SHARE

- Usually taken by SELECT - Blocks almost nothing

• ROW SHARE

- Usually taken by SELECT FOR UPDATE - Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE

• ROW EXCLUSIVE

- Usually taken by INSERT, UPDATE, DELETE - Conflicts with SHARE and anything stronger

• SHARE UPDATE EXCLUSIVE

- Usually taken by VACUUM and CREATE INDEX CONCURRENTLY - Conflicts with SHARE UPDATE EXCLUSIVE and stronger modes

• SHARE

- Usually taken by CREATE INDEX without CONCURRENTLY - Blocks write activity such as INSERT, UPDATE, and DELETE

• SHARE ROW EXCLUSIVE

- Usually taken by CREATE TRIGGER and ADD CONSTRAINT ... FOREIGN KEY - Conflicts with ROW EXCLUSIVE and stronger modes

• EXCLUSIVE

- Usually taken by REFRESH MATERIALIZED VIEW CONCURRENTLY - Conflicts with ROW SHARE and stronger modes

• ACCESS EXCLUSIVE

- Usually taken by many forms of ALTER TABLE, plus DROP TABLE and TRUNCATE - Conflicts with everything

The one to fear is ACCESS EXCLUSIVE. ⚠️ It conflicts even with ACCESS SHARE, and ACCESS SHARE is what plain SELECT uses. In other words, once an ACCESS EXCLUSIVE lock is held, the table is not readable at all.

For migrations, the lock map is the important part. Every DDL operation grabs a specific lock mode and keeps it for the whole operation.

These are the highest-risk operations because they fully lock the table:

• ALTER TABLE ... ADD COLUMN ... NOT NULL when there is no pre-existing constraint

• ALTER TABLE ... ALTER COLUMN TYPE when the table must be rewritten

• ALTER TABLE ... SET NOT NULL because existing rows must be checked

• ALTER TABLE ... ADD CONSTRAINT ... CHECK

• ALTER TABLE ... ADD CONSTRAINT ... UNIQUE

• ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY

• ALTER TABLE ... ADD CONSTRAINT ... EXCLUDE

• ALTER TABLE ... ADD CONSTRAINT ... USING INDEX

• DROP TABLE

• TRUNCATE

If one of these runs on a hot table, it can block both readers and writers. That is why these statements deserve extra planning. 🚧

These still permit reads, but they stop writes:

• ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY

- This applies to both the source table and the referenced table

• CREATE TRIGGER

This lock is strong enough to interfere with normal application traffic, even if dashboards and read-only queries continue to work.

This is the lock commonly associated with non-concurrent index work:

• CREATE INDEX without CONCURRENTLY

• REINDEX TABLE without concurrent behavior

- The parent table is protected from writes while each index rebuild happens

This is why a regular index build on a large production table can be disruptive even though reads still succeed.

This mode is usually the safer option for online maintenance:

• CREATE INDEX CONCURRENTLY

• VALIDATE CONSTRAINT

• VACUUM that is not FULL

It also matters indirectly in multi-step migration patterns, because validation phases often use this lock instead of something stronger. That difference is a big deal in production. 🚀

The subtle part is not just lock conflict. It is lock queueing.

Imagine this situation:

• A SELECT is already running on users

• You start ALTER TABLE users ADD COLUMN ...

• That ALTER TABLE needs ACCESS EXCLUSIVE

• It cannot get the lock yet, so it waits

So far, that sounds manageable. The trap is what happens next: new queries line up behind the waiting DDL statement.

The flow looks like this:

• Active work: [SELECT (ACCESS SHARE)] is currently running

• Waiting in queue: [ALTER TABLE (ACCESS EXCLUSIVE)]

• New arrivals:

- [SELECT (ACCESS SHARE)] waits behind the queued ALTER TABLE - another [SELECT (ACCESS SHARE)] also waits - [INSERT (ROW EXCLUSIVE)] waits too

That is the operational hazard. 🧨 The ALTER TABLE is blocked by an existing query, but while it waits, fresh traffic accumulates behind it. If the original query is long-lived, maybe an analytical report or a heavy dashboard query, the table can effectively become unavailable while the queue grows.

This is why lock_timeout is not optional.

Without a timeout:

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

With a timeout:

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 cannot get the lock in time, it fails fast and you retry later during a quieter period. That outcome is far better than letting a blocked DDL statement stall all incoming traffic. ✅

A lot of risky DDL has a safer alternative. The core strategy is to replace long, blocking operations with shorter metadata changes plus background-compatible steps.

Risky pattern:

• ADD COLUMN ... NOT NULL DEFAULT

Safer approach:

• Add the column as nullable

• Backfill data separately

• Add CHECK ... NOT VALID

• Run VALIDATE CONSTRAINT

• Finish with SET NOT NULL

The improvement is important: instead of a long ACCESS EXCLUSIVE rewrite, you get short metadata locks and a validation step that uses SHARE UPDATE EXCLUSIVE. 📌

Risky pattern:

• CREATE INDEX

Safer approach:

• CREATE INDEX CONCURRENTLY

This changes the lock profile from SHARE to SHARE UPDATE EXCLUSIVE, which means writes can continue.

Risky pattern:

• ADD CONSTRAINT ... FOREIGN KEY

Safer approach:

• ADD CONSTRAINT ... NOT VALID

• then VALIDATE CONSTRAINT

That swap avoids a long validation scan under SHARE ROW EXCLUSIVE and replaces it with a brief metadata phase followed by SHARE UPDATE EXCLUSIVE validation. 🛠️

Risky pattern:

• ADD CONSTRAINT ... UNIQUE

Safer approach:

• CREATE UNIQUE INDEX CONCURRENTLY

• then ADD CONSTRAINT ... USING INDEX

This turns a long inline build that needs ACCESS EXCLUSIVE into a concurrent index creation plus a brief attachment step.

Risky pattern:

• ALTER COLUMN TYPE

Safer approach:

• Create a new column

• Backfill in batches

• Swap usage

• Remove the old column later

That avoids a long rewrite under ACCESS EXCLUSIVE and replaces it with short metadata operations plus batched work that relies on ROW EXCLUSIVE.

You do not need to memorize every lock combination. The better move is to automate the check in your migration workflow.

A simple analysis command can inspect your SQL files and map each statement to the lock mode it requires:

npx @flvmnt/pgfence analyze migrations/*.sql

If you want a stricter CI policy, use the stricter mode:

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

The lock matrix is the base layer. Once that mapping exists, higher-level checks become possible: risk scoring, safer rewrite suggestions, and policy enforcement in CI/CD. 🚀

The lesson is simple but important: DDL risk is not only about the lock a statement eventually holds. It is also about the queue it creates while waiting. That is the part teams often overlook.

If you remember only one rule, make it this one: a migration that wants ACCESS EXCLUSIVE should be treated as dangerous until proven otherwise. Add lock_timeout, prefer concurrent or multi-step patterns, and assume busy production tables will expose every weakness in a naive migration plan. ⚠️

🔍 TL;DR Summary

• 📌 PostgreSQL has eight table-level lock modes, ordered from ACCESS SHARE up to ACCESS EXCLUSIVE.

• 🚨 ACCESS EXCLUSIVE is the most disruptive lock because it blocks both reads and writes.

• ⏳ The real production hazard is lock queueing: a waiting DDL statement can cause later queries to pile up behind it.

• 🛠️ SET lock_timeout = '2s' is a practical safety measure to avoid indefinite waiting and traffic stalls.

• ✅ Safer migration patterns usually replace long blocking operations with phased changes, concurrent index creation, and NOT VALID plus VALIDATE CONSTRAINT.

• 🤖 Automating lock analysis in CI helps catch dangerous migration statements before they reach production.