Wrapping PostgreSQL DDL in a Transaction Safely

You wrapped four ALTER TABLE statements in a single BEGIN ... COMMIT, trusting PostgreSQL’s transactional DDL to give you all-or-nothing safety — and then the deploy hung, the connection pool drained, and the on-call channel filled with canceling statement due to lock timeout. Or worse: the migration “succeeded” in staging but wedged production for ninety seconds because one of those statements quietly demanded an ACCESS EXCLUSIVE lock behind a long-running report. PostgreSQL genuinely does wrap DDL in transactions, which is its great advantage over engines that implicitly commit — but atomicity is not the same as safety. A transaction that is atomic can still hold a table-wide lock for its entire duration, and a single non-transactional statement dropped into the block can break the atomicity you were counting on. This page is the runbook for keeping a multi-statement PostgreSQL migration both atomic and lock-safe. It sits under the transactional vs non-transactional databases topic of the migration fundamentals guide.

Symptom / Error Signatures

You are on the right page if a wrapped PostgreSQL migration produced one of these signals:

• ERROR: canceling statement due to lock timeout
• ERROR: canceling statement due to statement timeout
• ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
• ERROR: VACUUM cannot run inside a transaction block
• ERROR: ALTER TYPE ... ADD cannot run inside a transaction block (adding an enum value before PostgreSQL 12)
• ERROR: deadlock detected when two migrations touch the same tables in different order
• Migration runners (Flyway, Liquibase, Django, Rails) stalling at LOG: process ... still waiting for AccessExclusiveLock on relation
• The deploy “hangs” while pg_stat_activity shows your ALTER in state active and a fleet of application queries in Lock wait

The fastest confirmation is to look at what your migration is blocked on, and who is blocking it, from a second session:

-- PostgreSQL · read-only diagnostic · run as a monitoring role from a separate session
SELECT blocked.pid          AS blocked_pid,
       blocked.query        AS blocked_query,
       blocking.pid         AS blocking_pid,
       blocking.query       AS blocking_query,
       blocking.state,
       now() - blocking.query_start AS blocking_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Root Cause Analysis

PostgreSQL runs almost all DDL inside the transaction that issues it: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX (non-concurrent), ADD CONSTRAINT, RENAME, and comment or default changes all participate in BEGIN ... COMMIT and roll back cleanly on abort. The catalog rows that describe your schema are themselves MVCC-versioned, which is what makes transactional DDL possible. That is the strength the whole transactional vs non-transactional databases split turns on.

Two facts, however, undermine the naive assumption that “wrapped in a transaction” equals “safe.”

First, atomicity does not release locks early. Every lock a DDL statement acquires is held until the transaction commits or rolls back. If your BEGIN block runs five ALTER TABLE statements that each take ACCESS EXCLUSIVE, the first lock is held for the duration of all five statements plus commit. The longer the block, the longer every reader and writer of those tables is queued behind it — and because a pending ACCESS EXCLUSIVE request also blocks new ACCESS SHARE grants, a single slow migration can freeze a table that is otherwise idle.

Second, some statements cannot run in a transaction at all. CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VACUUM, ALTER SYSTEM, and (before PostgreSQL 12) ALTER TYPE ... ADD VALUE commit incrementally by design. Drop one into your atomic block and the whole migration aborts with a cannot run inside a transaction block error — or your runner silently splits it out and you lose the atomicity guarantee you thought you had.

The practical question is therefore which lock does each statement take, because that determines whether the statement is safe to co-locate in an atomic block during traffic. The heaviest offenders queue every reader:

The pattern is clear: metadata-only changes (ADD COLUMN without a rewrite, DROP COLUMN, NOT VALID constraints) are cheap and belong together in one atomic block; anything that rewrites the table or builds an index should be isolated. This lock classification is the same discipline that idempotent script design leans on so a retry after a cancelled statement converges cleanly.

Immediate Mitigation

When a wrapped migration is stalling traffic right now, the goal is to make it fail fast instead of holding a queue, then re-run it correctly.

1. Cancel the stuck migration and free the queue. Use the blocking_pid/blocked_pid from the diagnostic above. Prefer pg_cancel_backend (cancels the current statement) over pg_terminate_backend (drops the whole connection):

-- PostgreSQL · run as a role with pg_signal_backend or superuser · cancels the migration statement
SELECT pg_cancel_backend(<migration_pid>);

2. Bound the lock wait so the next attempt cannot hang. Set lock_timeout for the migration session so a statement that cannot get its lock within a few seconds aborts instead of parking the pool. SET LOCAL scopes it to the current transaction:

-- PostgreSQL · migration role · run at the top of the wrapped block; fails fast instead of queuing
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';
-- ... your DDL ...
COMMIT;

3. Split the non-transactional statements out. If the failure was cannot run inside a transaction block, move that statement into its own migration file marked non-transactional (Flyway -- executeInTransaction=false, Liquibase runInTransaction:false, Rails disable_ddl_transaction!). Run it standalone:

-- PostgreSQL · migration role · MUST run outside any transaction block; online but slow
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON orders (status);

4. Convert heavy validation into a two-phase, low-lock pattern. Add the constraint as NOT VALID (metadata-only, ACCESS EXCLUSIVE but instant), then validate it separately under the lighter SHARE UPDATE EXCLUSIVE lock that does not block reads or writes:

-- PostgreSQL · migration role · phase 1 is instant, phase 2 is a separate non-blocking transaction
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT orders_total_positive CHECK (total >= 0) NOT VALID;
COMMIT;

-- Phase 2, its own transaction — scans the table but does not block traffic.
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;

5. Confirm nothing was left half-applied. Because the block is atomic, a clean cancel leaves the schema untouched — but a concurrently-built index that was cancelled can leave an INVALID index behind:

-- PostgreSQL · read-only · find any invalid index from a cancelled concurrent build
SELECT indexrelid::regclass AS invalid_index
FROM pg_index WHERE NOT indisvalid;

Permanent Fix / Long-Term Pattern

The durable pattern has three rules that keep a multi-statement migration both atomic and lock-safe.

Keep the atomic block short and metadata-only. Group the cheap, related metadata changes — add nullable columns, drop columns, add NOT VALID constraints, rename — into one wrapped transaction guarded by lock_timeout, so they succeed or roll back together in milliseconds. A short block means the ACCESS EXCLUSIVE lock is held only briefly, so even under load the migration slips between queries rather than damming them.

-- PostgreSQL · migration role · atomic and fast; every statement here is metadata-only
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN IF NOT EXISTS fulfilled_at timestamptz NULL;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS carrier_code text NULL;
ALTER TABLE orders ADD CONSTRAINT orders_carrier_fk
  FOREIGN KEY (carrier_code) REFERENCES carriers (code) NOT VALID;
COMMIT;

Isolate every rewrite, index build, and validation into its own step. CREATE INDEX CONCURRENTLY, table-rewriting type changes, and VALIDATE CONSTRAINT each run in a separate transaction (or non-transactionally, for the concurrent forms), so a slow scan never extends the lock hold of the metadata block. This is the transactional-engine equivalent of the decomposition discipline that non-transactional engines force on you — the same principle as handling non-transactional DDL in MySQL migrations, applied here by choice rather than necessity.

Backfill volatile data separately, never via a defaulted rewrite. Adding a column with a volatile or computed default rewrites every row under an ACCESS EXCLUSIVE lock. Add the column nullable in the atomic block, then backfill in batches outside any long transaction, keeping each batch idempotent so a retry converges — the pattern documented in idempotent script design.

-- PostgreSQL · application loop · run in primary-key batches during a low-write window; safe to re-run
UPDATE orders SET carrier_code = 'UNSET'
WHERE carrier_code IS NULL AND id BETWEEN :lo AND :hi;

Finally, make the ordering deterministic. Two migrations that lock the same tables in opposite orders will eventually deadlock; always acquire locks on tables in a consistent, documented order across every migration. For a fuller treatment of why this contract differs so sharply between engines, return to the transactional vs non-transactional databases topic, and see avoiding implicit commits in MySQL DDL migrations for the mirror-image trap on the non-transactional side.

Verification Checklist

• Every wrapped block opens with SET LOCAL lock_timeout (and usually statement_timeout) so a blocked statement fails fast instead of queuing traffic
• The atomic transaction contains only metadata-only DDL; no table rewrites, plain CREATE INDEX, or VALIDATE CONSTRAINT inside it
• Every CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY / VACUUM lives in its own step flagged non-transactional in the runner
• New columns are added nullable, with any volatile backfill done in idempotent batches outside the atomic block
• Constraints are added NOT VALID first, then VALIDATE CONSTRAINT runs in a separate transaction under SHARE UPDATE EXCLUSIVE
• pg_index shows no indisvalid = false rows and pg_stat_activity shows no session still blocked on a lock after the deploy

Return to the parent topic: Transactional vs Non-Transactional Databases.

Frequently Asked Questions

Is all PostgreSQL DDL transactional? Almost all of it is — CREATE TABLE, ALTER TABLE, DROP, RENAME, plain CREATE INDEX, and ADD CONSTRAINT all participate in the enclosing transaction and roll back cleanly. The exceptions commit incrementally and cannot run in a transaction block at all: CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY, REINDEX CONCURRENTLY, VACUUM, ALTER SYSTEM, and, before PostgreSQL 12, ALTER TYPE ... ADD VALUE. Those must be isolated into their own non-transactional migration steps.

Why does my wrapped migration still block traffic if it is atomic? Atomicity governs what state you end in, not how long locks are held. Every lock acquired inside a transaction is held until commit, so a long block keeps its ACCESS EXCLUSIVE lock for the whole duration, and because a pending exclusive request also blocks new shared-lock grants, even reads queue behind it. Keep the block short, set lock_timeout, and move any slow scan or rewrite into a separate step.

How do I add an index without breaking my transactional migration? Use CREATE INDEX CONCURRENTLY, but it cannot run inside a transaction block, so put it in its own migration marked non-transactional (executeInTransaction=false, disable_ddl_transaction!, or the runner’s equivalent). It takes only a SHARE UPDATE EXCLUSIVE lock and does not block reads or writes; if a build is interrupted it leaves an INVALID index, which you drop with DROP INDEX CONCURRENTLY before retrying.