Safe PostgreSQL schema migrations

Zero downtime is not an intrinsic property of one DDL statement. It depends on whether old and new application versions can operate together during the migration window. Safe migration needs compatibility phases, a lock budget, representative-scale tests, observable execution, and explicit rollback boundaries.

Default to expand-and-contract

For a renamed or replaced field on a high-traffic table:

1. Expand: add the new column or table without removing the old structure; prefer short metadata operations.
2. Dual compatible: make the application read both structures and dual-write when required; writes must be idempotent.
3. Backfill: use small primary-key or time ranges and bound transaction duration, WAL, and replica lag.
4. Switch: change reads first, then stop old writes; verify with business signals and consistency queries.
5. Contract: remove the old structure only after a full rollback-compatible release window.

Putting “add, backfill, set NOT NULL, and drop old” in one long transaction often amplifies lock, WAL, rollback, and replication-lag risk.

Bound lock waits

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '15min';

ALTER TABLE app.orders
  ADD COLUMN IF NOT EXISTS fulfillment_state text;

COMMIT;

These timeouts are examples, not universal defaults. lock_timeout prevents a migration from waiting indefinitely and acquiring a strong lock at an uncontrolled moment; statement_timeout bounds execution. On failure, exit and investigate the blocker instead of retrying forever.

Constraint scanning can be separated from the short-lock phase:

ALTER TABLE app.orders
  ADD CONSTRAINT orders_total_nonnegative
  CHECK (total_cents >= 0) NOT VALID;

ALTER TABLE app.orders
  VALIDATE CONSTRAINT orders_total_nonnegative;

Check the exact DDL lock level on the target PostgreSQL version and representative data. CREATE INDEX CONCURRENTLY still consumes I/O, WAL, and time, and a failure can leave an invalid index that must be detected.

Recommended CI pipeline

Schema / reviewed SQL
        ↓
Migration generation
        ↓
Squawk static checks
        ↓
Disposable PostgreSQL 18.4
        ↓
Apply every migration from empty and upgraded states
        ↓
pgTAP + application integration + RLS negative tests
        ↓
PostgreSQL 19 Beta compatibility lane
        ↓
Representative-data rehearsal → staging → production

• Squawk detects common hazards such as non-concurrent indexes, constraints added without NOT VALID, and selected lock risks; it is not proof of zero downtime.
• Testcontainers for Node.js starts real PostgreSQL in CI for transactions, locks, RLS, JSONB, extensions, and driver behavior.
• pgTAP tests functions, triggers, constraints, and policies inside PostgreSQL.

With Drizzle ORM, Drizzle Kit can generate ordinary schema changes before Squawk and human review. Use reviewed native SQL for complex indexes, policies, functions, extensions, and PostgreSQL 19 syntax. An ORM's inability to express a feature does not make the database feature inappropriate.

Test the upgrade path, not only an empty database

CI needs at least two starting states:

Split version tests into two lanes:

• Production gate: the current production major/minor, such as PostgreSQL 18.4; failures block release.
• Forward compatibility: PostgreSQL 19 Beta 2; failures may initially be allowed but must be classified, tracked, and cleared before GA adoption.

Beta testing does not replace the stable-version gate. Follow version status on the PostgreSQL 19 topic page.

RLS and security objects need negative tests

A successful migration does not prove correct authorization. For every tenant and role, verify that allowed SELECT/INSERT/UPDATE/DELETE operations succeed and forbidden cross-tenant reads and writes fail. The runtime role should not own tables; use FORCE ROW LEVEL SECURITY where required. See the Security baseline.

When heavier tooling is justified

Small teams should first make expand-and-contract, real PostgreSQL tests, lock observation, and recovery drills routine before adding a control plane.