Database Migration

Coverage

• Migration file conventions: chronological filenames, headers documenting purpose and rollback, BEGIN/COMMIT framing, when DDL must escape the transaction
• Connection requirements: why DDL needs an unpooled connection (PgBouncer transaction-mode rejects DDL session state), and the two-URL pattern for live applications
• Branched-database workflow: create a branch, apply migration, schema-diff against parent, apply to main, prune the branch — with a vendor-capability matrix
• Common DDL patterns: nullable column, column with constant default, column with non-constant default on a large table (batched backfill), zero-downtime rename via expand / contract, type change via shadow column, concurrent index creation, low-lock foreign key (NOT VALID + VALIDATE)
• Tenant-scoped schema additions: how to keep multi-tenant isolation safe across migrations (RLS-policy-in-migration discipline), without owning the policy design itself
• Zero-downtime table rename: compatibility view pattern that lets old code keep reading while the new name takes over
• Rollback strategy: transactional rollback for structural changes, DOWN paths for non-transactional ones, point-in-time restore as the last resort
• Pre-production checklist: schema diff, branch test run, NOT VALID gating, CONCURRENTLY gating, RLS gating, rollback documented in header

Philosophy

A migration is the only operation in the application stack that is both shipped as code and irreversible by default. Application code can be reverted by re-deploying yesterday's commit; a DROP COLUMN cannot. Treat every migration as a one-way door unless you have explicitly designed the reverse door alongside it.

The dominant failure mode is the plausible-looking single-statement migration: ALTER TABLE orders ADD COLUMN sync_version INTEGER NOT NULL DEFAULT 0; looks fine on a small dev database and locks the table for minutes on a 50M-row production table. Zero-downtime migration is a discipline of splitting the apparent single change into a sequence of low-lock steps that each remain compatible with the running application. The split is not theoretical — it is the difference between a deploy and an outage.