Table-Level Lock Blocking DDL (ALTER TABLE)

Scenario

A DBA runs ALTER TABLE orders ADD COLUMN delivery_eta TIMESTAMPTZ during business hours. The command hangs for 30 minutes. Meanwhile, the application’s connection pool fills to capacity because every query against the orders table — SELECTs, INSERTs, UPDATEs — is queued behind the DDL waiting for an ACCESS EXCLUSIVE lock. The table itself is locked because an open read transaction, started before the ALTER, is still active.

How to Identify

Conditions:

• ALTER TABLE or other DDL command appears in pg_stat_activity with state = 'active' but no progress for minutes
• pg_locks shows the DDL session waiting for AccessExclusiveLock with granted = false
• A large number of subsequent sessions are stacked behind the DDL, also blocked
• Application connection count spikes; pool exhaustion errors appear in application logs
• The root blocker may be an innocuous long-running SELECT or an idle in transaction session

Analysis Steps

-- 1. Find all sessions blocked on lock waits (includes DDL and its followers)
SELECT
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    now() - query_start   AS wait_duration,
    left(query, 100)      AS query
FROM   pg_stat_activity
WHERE  wait_event_type = 'Lock'
ORDER  BY wait_duration DESC;

-- 2. Identify blocker → blocked chains
SELECT
    blocking.pid                       AS blocking_pid,
    blocking.state                     AS blocking_state,
    now() - blocking.xact_start        AS blocking_xact_age,
    left(blocking.query, 80)           AS blocking_query,
    blocked.pid                        AS blocked_pid,
    left(blocked.query, 80)            AS blocked_query
FROM   pg_stat_activity blocked
JOIN   pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE  cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- 3. Check AccessExclusiveLock waits specifically (DDL indicator)
SELECT
    l.pid,
    l.relation::regclass    AS table_name,
    l.mode,
    l.granted,
    a.state,
    left(a.query, 80)       AS query
FROM   pg_locks l
JOIN   pg_stat_activity a ON a.pid = l.pid
WHERE  l.mode = 'AccessExclusiveLock'
ORDER  BY l.granted DESC;

-- 4. Count how many sessions are blocked (connection pile-up indicator)
SELECT count(*) AS queued_sessions
FROM   pg_stat_activity
WHERE  wait_event_type = 'Lock';

Pitfalls

• DDL in production without a maintenance window or lock_timeout will cause an outage if any long transaction is active — even a read-only one.
• Do not run ALTER TABLE without first checking for long-running transactions on the target table; a single open SELECT can block the entire table.
• lock_timeout is critical for DDL scripts — without it, the DDL waits indefinitely and queues all subsequent queries behind it, amplifying the outage.
• Once the DDL acquires its position in the lock queue, all subsequent queries to the same table also queue behind it, even if the DDL itself is instantaneous once it starts.
• Avoid pg_cancel_backend() on the DDL itself unless intentional — cancelling it releases the queue position, but blocked sessions are already waiting and need to be re-dispatched by the application.

Resolution Approach

Immediately cancel or terminate the root blocker (the session holding the conflicting lock), then cancel the DDL itself if it is safe to do so. Going forward, always set lock_timeout before DDL so it fails fast instead of queuing. For large tables, prefer CREATE INDEX CONCURRENTLY or pg_repack for schema changes that need to avoid table locks entirely.