DDL semantics¶

PostgreSQL DDL statements have runtime behaviors that affect migration safety: lock modes, table rewrites, transaction boundaries, and version-gated optimizations. Understanding these prevents production outages caused by migrations that are syntactically correct but operationally dangerous.

DDL locking fundamentals¶

The three lock modes that matter¶

PostgreSQL has eight table-level lock modes. For schema migrations, three dominate:

ACCESS EXCLUSIVE conflicts with every other lock mode, including ACCESS SHARE. This means a single ALTER TABLE blocks all SELECT statements on that table.

The lock queue pile-up¶

A blocked ALTER TABLE does not wait politely to the side. It takes the head of the lock queue, and everything else lines up behind it:

1. Reporting query starts. Holds ACCESS SHARE on users. Runs for 40 seconds.
2. Migration: ALTER TABLE users ADD COLUMN age int. Wants ACCESS EXCLUSIVE.
   Conflicts with the reporting query. Waits.
3. Normal SELECT from the app arrives. Wants ACCESS SHARE.
   Does NOT conflict with the reporting query, but DOES conflict with
   the queued ACCESS EXCLUSIVE ahead of it. Waits.
4. Every subsequent SELECT piles up behind step 3.

The table is dark for the duration of the slowest query that was running when the migration started. The ALTER TABLE itself was never slow.

lock_timeout¶

lock_timeout bounds the acquisition phase. If the lock cannot be acquired within the timeout, the statement errors with 55P03 lock_not_available, releases its queue position, and the backlog drains:

SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN age int;
-- If lock not acquired in 2s: ERROR, backlog drains, SELECTs resume

Without lock_timeout, a migration waiting behind a 40-minute reporting query holds the queue for 40 minutes. With lock_timeout = 2s, it fails in 2 seconds and can be retried.

Recommended values:

Hold-time: locks held until transaction end¶

PostgreSQL holds table-level locks until the end of the transaction, not until the statement finishes. A 6ms ALTER TABLE inside a 5-minute transaction holds ACCESS EXCLUSIVE for the full 5 minutes:

BEGIN;
ALTER TABLE orders ADD COLUMN note3 text;  -- 6ms work, but...
UPDATE orders SET note3 = compute(note3);  -- 5 minutes of backfill
COMMIT;  -- ACCESS EXCLUSIVE released here, 5 minutes later

The fix: separate DDL from backfill. Run DDL in its own short transaction, then backfill in batches:

-- Migration: DDL only. Brief lock.
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note3 text;
COMMIT;

-- Separate: backfill in batches, each its own short transaction
UPDATE orders SET note3 = compute(note3) WHERE id BETWEEN 1 AND 5000;
UPDATE orders SET note3 = compute(note3) WHERE id BETWEEN 5001 AND 10000;
-- ...

Operation lock reference¶

Complete reference of every ALTER TABLE subform dbwarden generates, its lock mode, rewrite behavior, and zero-downtime safety.

Column operations¶

Table operations¶

Index operations¶

Constraint operations¶

Comment operations¶

CREATE INDEX CONCURRENTLY¶

Why it cannot run inside a transaction¶

CREATE INDEX CONCURRENTLY splits its work across multiple internal transactions: build against a snapshot, wait for older transactions to drain, scan again to catch changes, mark valid. Waiting for other transactions to finish is impossible from inside a transaction that is itself still open. PostgreSQL enforces this:

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

This is a permanent server-side constraint. No tool can work around it.

Three-transaction lifecycle¶

1. Transaction 1: Index entered as INVALID in system catalogs
2. Transaction 2: First table scan (builds index against snapshot)
3. Transaction 3: Second table scan (catches changes since first scan)
4. Mark valid: Index becomes usable for queries

On failure: invalid index left behind¶

If the build fails (deadlock, uniqueness violation, OOM), the index remains as INVALID:

Indexes:
    "idx" btree (col) INVALID

Invalid indexes are ignored for queries but still consume update overhead. The recovery path:

DROP INDEX CONCURRENTLY IF EXISTS idx;
CREATE INDEX CONCURRENTLY idx ON t (col);

dbwarden handles this automatically¶

dbwarden prefixes concurrent index creation with -- @dbwarden:autocommit, which executes the statement outside the migration transaction on a separate autocommit connection. The --no-concurrent flag disables this for development environments.

See DDL Behavior for the autocommit mechanism details.

ADD COLUMN fast path (PG11+)¶

Constant defaults: instant on any table size¶

PostgreSQL 11 changed ADD COLUMN with a constant default from a full table rewrite to a catalog-only operation. The default value is evaluated at ALTER TABLE time and stored in pg_attribute.atthasmissing. Existing rows receive the value at read time:

-- Instant, even on 100M rows (PG11+)
ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';

Constant defaults (fast path): string literals, integers, booleans, CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, NULL, ARRAY[], etc.

Volatile defaults (full rewrite): random(), gen_random_uuid(), clock_timestamp(), nextval(), any function marked VOLATILE.

When the old advice is still correct¶

Before PG11, the standard pattern was:

-- Old pattern (PG10 and below): add nullable, backfill, set NOT NULL
ALTER TABLE orders ADD COLUMN status TEXT;
UPDATE orders SET status = 'pending';
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

This is still necessary when:

• The default is volatile (e.g., DEFAULT now() or DEFAULT gen_random_uuid())
• The column uses a stored generated expression
• The column uses an identity column
• You are on PG 10 or below

For PG11+ with constant defaults, the direct approach is safe and fast:

-- PG11+: direct is fine for constant defaults
ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';

NOT NULL via CHECK + VALIDATE¶

The problem¶

SET NOT NULL scans the entire table under ACCESS EXCLUSIVE to verify no NULLs exist. On a large table, this holds the lock for the duration of the scan:

-- Dangerous on large tables: full scan under ACCESS EXCLUSIVE
ALTER TABLE orders ALTER COLUMN customer_ref SET NOT NULL;

The four-step recipe¶

PostgreSQL 12+ can skip the scan if a validated CHECK constraint already proves the column contains no NULLs:

-- Step 1: Add CHECK NOT VALID (ACCESS EXCLUSIVE, instant, catalog only)
ALTER TABLE orders
    ADD CONSTRAINT orders_ref_notnull CHECK (customer_ref IS NOT NULL) NOT VALID;

-- Step 2: Validate (SHARE UPDATE EXCLUSIVE, full scan, reads+writes continue)
ALTER TABLE orders VALIDATE CONSTRAINT orders_ref_notnull;

-- Step 3: SET NOT NULL (ACCESS EXCLUSIVE, instant, scan skipped)
ALTER TABLE orders ALTER COLUMN customer_ref SET NOT NULL;

-- Step 4: Cleanup (optional, removes redundant CHECK)
ALTER TABLE orders DROP CONSTRAINT orders_ref_notnull;

Step 2 takes SHARE UPDATE EXCLUSIVE which allows concurrent reads and writes. Step 3 is instant because PG12+ recognizes the validated CHECK and skips the scan.

dbwarden support¶

dbwarden supports NOT VALID / VALIDATE CONSTRAINT via the constraint handler. The VALIDATE step is marked with -- @dbwarden:autocommit to execute outside the migration transaction.

See Constraints for the full NOT VALID / VALIDATE documentation.

Advisory lock operational details¶

dbwarden uses PostgreSQL advisory locks (pg_try_advisory_lock) for migration coordination. These have several operational subtleties.

Session-scoped¶

Advisory locks are session-scoped: they release when the connection closes. This means:

• If the migration process crashes, the lock is released automatically
• If the connection is closed by a pooler, the lock is released
• There is no "dead lock" scenario where a stale lock persists

PgBouncer detection¶

PgBouncer in transaction-pooling mode assigns different backend connections per transaction, making session-level advisory locks meaningless. dbwarden detects this by calling pg_backend_pid() twice and comparing results:

# If the PID changes between calls, we're behind a pooler
pid1 = conn.execute(text("SELECT pg_backend_pid()")).scalar()
pid2 = conn.execute(text("SELECT pg_backend_pid()")).scalar()
if pid1 != pid2:
    raise LockError("Transaction-pooling proxy detected")

Replica detection¶

dbwarden refuses to run migrations on read replicas (detected via pg_is_in_recovery()). Migrations must target the primary.

Prepared transactions prohibition¶

Prepared transactions (2PC) interact badly with advisory locks. If a prepared transaction is open, dbwarden refuses to acquire the lock.

Re-entrancy protection¶

If the same namespace is already locked on the same connection, dbwarden refuses to acquire a nested lock. This prevents deadlocks from nested migration calls.

Canonicalization behaviors¶

dbwarden normalizes several PostgreSQL constructs during diffing to avoid spurious changes. Understanding these prevents confusion when the generated DDL differs from what you wrote.

CHECK expression cast stripping¶

PostgreSQL stores CHECK constraints with type casts added by the parser:

-- You wrote:
CHECK (email <> '')
-- PostgreSQL stores:
CHECK (email <> ''::text)

Without normalization, every unnamed CHECK constraint would appear changed on every migration run. dbwarden strips ::type suffixes and normalizes whitespace before comparison.

FK action normalization¶

PostgreSQL reports NO ACTION for an unspecified FK action. dbwarden normalizes absent values to NO ACTION and uppercases the result:

# Model omits on_delete → stored as None → normalized to "NO ACTION"
# Snapshot has "No Action" → normalized to "NO ACTION"
# Both match, no spurious change

Index null ordering¶

PostgreSQL's default null ordering follows sort direction: NULLS LAST is implied by ASC, NULLS FIRST by DESC. Recording the implied value would cause every index to differ from a model that says nothing about sorting, producing spurious drop+recreate cycles.

dbwarden only emits null ordering when it contradicts the PG default.

nextval() suppression for autoincrement¶

When a column is marked as autoincrement, the nextval(...) default in the snapshot is set to None during diffing. Without this, every autoincrementing column would appear to have a default change.

Physical storage tuning preservation¶

If the model does not mention pg_storage or pg_compression for a column, dbwarden will not emit SET STORAGE or SET COMPRESSION to reset the column to its type default. This preserves deliberate DBA tuning (e.g., a column set to MAIN storage for compression).

To reset storage to the type default, explicitly set pg_storage or pg_compression in the model.

Version-gated features¶

Known limitations¶

dbwarden does not currently support:

Related¶

• DDL Behavior : Transactional DDL, index creation, column type changes, lock levels
• Indexes : Index types, PgIndexSpec, CONCURRENTLY field
• Constraints : NOT VALID / VALIDATE, deferrable, NO INHERIT
• Tables and Columns : Column lifecycle, table properties
• Migration Locking : Advisory lock implementation, PgBouncer detection, heartbeats
• Migration Safety : SAFE/WARN/CRITICAL classification per object type