Which ALTER TABLE Operations Lock Your PostgreSQL Table?

ACCESS EXCLUSIVE Operations (Block Everything)

These ALTER TABLE operations acquire ACCESS EXCLUSIVE, which blocks all reads and writes on the table. On a busy production table, these are the operations most likely to cause outages.

ADD COLUMN with volatile DEFAULT

-- Rewrites the entire table (all PostgreSQL versions)
ALTER TABLE users ADD COLUMN request_id UUID DEFAULT gen_random_uuid();

-- Safe alternative: three-step pattern
ALTER TABLE users ADD COLUMN request_id UUID;
ALTER TABLE users ALTER COLUMN request_id SET DEFAULT gen_random_uuid();
-- Backfill in batches...

On PostgreSQL 11+, non-volatile defaults (constants like 'active', 0, false) are instant and do not rewrite the table. Volatile defaults (now(), gen_random_uuid(), random()) still rewrite the table.

ALTER COLUMN TYPE

-- Rewrites the entire table under ACCESS EXCLUSIVE
ALTER TABLE orders ALTER COLUMN amount TYPE BIGINT;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(100);

-- Safe alternative: expand-contract pattern
ALTER TABLE orders ADD COLUMN amount_v2 BIGINT;
-- Sync trigger + backfill + app code switch + drop old column

There are a few exceptions where ALTER COLUMN TYPE does not rewrite the table: changing VARCHAR(n) to VARCHAR(m) where m > n (widening), changing VARCHAR(n) to TEXT, and changing NUMERIC(p,s) to NUMERIC (removing precision constraint). These are metadata-only changes that still acquire ACCESS EXCLUSIVE but complete instantly.

SET NOT NULL

-- Scans entire table under ACCESS EXCLUSIVE to verify no NULLs exist
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Safe alternative: CHECK + VALIDATE pattern
ALTER TABLE users ADD CONSTRAINT chk_email_nn
  CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_email_nn;

-- PG 12+: After validation, SET NOT NULL is instant
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT chk_email_nn;

ADD CONSTRAINT (UNIQUE, PRIMARY KEY, EXCLUDE)

-- Scans table + builds index under ACCESS EXCLUSIVE
ALTER TABLE users ADD CONSTRAINT uniq_email UNIQUE (email);

-- Safe alternative: build index concurrently first
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users (email);
ALTER TABLE users ADD CONSTRAINT uniq_email UNIQUE USING INDEX idx_users_email;
-- The ADD CONSTRAINT USING INDEX is instant

SET LOGGED / SET UNLOGGED

-- Rewrites the entire table
ALTER TABLE events SET UNLOGGED;
ALTER TABLE events SET LOGGED;
-- No safe alternative — avoid in production

Other ACCESS EXCLUSIVE operations

• DROP COLUMN — instant but holds ACCESS EXCLUSIVE briefly
• RENAME COLUMN — instant but holds ACCESS EXCLUSIVE briefly
• RENAME TABLE — instant but holds ACCESS EXCLUSIVE briefly
• ADD COLUMN ... GENERATED ALWAYS AS (expr) STORED — rewrites table
• CLUSTER — rewrites table

SHARE ROW EXCLUSIVE Operations (Block Writes)

These operations block INSERT, UPDATE, DELETE but allow SELECT queries to continue.

ADD CONSTRAINT ... FOREIGN KEY (without NOT VALID)

-- Validates all existing rows — blocks writes during scan
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id);

-- Safe alternative: NOT VALID + VALIDATE
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
-- NOT VALID acquires SHARE ROW EXCLUSIVE but is instant

ALTER TABLE orders VALIDATE CONSTRAINT fk_user;
-- VALIDATE acquires SHARE UPDATE EXCLUSIVE (does not block writes)

Note that foreign keys lock both the child table (orders) and the parent table (users). The lock on the parent table is SHARE ROW EXCLUSIVE as well. This is one of the most overlooked aspects of FK constraints — your migration might not touch the parent table, but it still locks it.

CREATE TRIGGER

-- Acquires SHARE ROW EXCLUSIVE — blocks writes briefly
-- This is typically instant, so the lock duration is negligible
CREATE TRIGGER trg_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_modified_at();

SHARE UPDATE EXCLUSIVE Operations (Safe)

These operations do not block reads or writes. They only conflict with other SHARE UPDATE EXCLUSIVE operations and stronger locks.

• ALTER TABLE VALIDATE CONSTRAINT — validates CHECK or FK constraints
• ALTER TABLE SET STATISTICS — changes column statistics target
• ALTER TABLE SET (fillfactor = ...) — changes storage parameters
• ALTER TABLE SET (autovacuum_enabled = ...) — changes autovacuum settings

Quick Reference Table

The Key Insight: Lock Level vs Duration

A common misconception is that ACCESS EXCLUSIVE always means danger. That is not quite right. The danger comes from ACCESS EXCLUSIVE held for a long time.

Adding a column without a default acquires ACCESS EXCLUSIVE, but it completes in milliseconds. The lock is barely noticeable. ALTER COLUMN TYPE also acquires ACCESS EXCLUSIVE, but it rewrites the entire table, which can take minutes. Both are ACCESS EXCLUSIVE, but only one is dangerous.

The risk formula is: Lock Severity x Duration x Traffic. A brief ACCESS EXCLUSIVE on a low-traffic table is fine. A long-running SHARE lock on a high-traffic table can bring down your application.

Catch Lock Issues Before Production

Static analysis can catch most dangerous ALTER TABLE patterns before they reach production. MigrationPilot reports the exact lock type each ALTER TABLE sub-command acquires, flags operations that cause table rewrites or full scans, and suggests the safe alternative pattern. All 80 rules run in milliseconds without any database connection.

npx migrationpilot analyze migrations/005_alter_users.sql