Migrations Best Practices

Rules for versioned schema change that survives continuous deploy, large tables, and 3 AM rollbacks.

How to Use This List

• Gate every migration PR with sections A, B, and C.
• Require DBA review for D items on tables over 10M rows or SLA-critical paths.
• Revisit E after every migration-related incident.

A - Process & Git

• One ordered migration history in git. No manual prod DDL outside the pipeline.
• Review migrations like application code. Two pairs of eyes minimum.
• Never edit applied migration files. Forward-fix with new version only.
• CI applies full chain to empty database on every PR. Catches ordering and grant bugs.
• Match PostgreSQL major in CI to production target. Extension and DDL behavior differs.

B - Roles & Safety

• Migrations run as app_migrator, objects owned by app_owner. Runtime role has no DDL.
• Set lock_timeout in migration SQL. Fail fast instead of blocking app traffic.
• Set sensible statement_timeout per statement type. High for CONCURRENTLY, low for trial DDL.
• Grant privileges in migration or default privileges. Deploy success without runtime permission errors.
• Document non-transactional migrations. CREATE INDEX CONCURRENTLY in separate file with runner txn disabled.

C - Zero-Downtime Design

• Expand before contract; nullable before NOT NULL. Multi-release breaking changes.
• Use CREATE INDEX CONCURRENTLY on large tables. Verify indisvalid after run.
• ADD CONSTRAINT NOT VALID then VALIDATE separately. Avoid long ACCESS EXCLUSIVE scans.
• Backfill in chunks with replication lag monitoring. No single billion-row UPDATE transaction.
• Analyze tables after large data changes. Planner stats stale after backfill.

D - Rollback & Recovery

• ADR: forward-only schema in production. App rolls back; schema advances with fix migration.
• Test app rollback compatibility with expand-phase schema. Old binary must run on new nullable columns.
• Backup or PITR checkpoint before contract migrations. DROP COLUMN is irreversible without restore.
• Liquibase/Flyway rollback scripts drill-only on clones. Not auto-run in prod.
• Feature flags coordinate schema phases. Kill switch without contract migration.

E - Observability

• Log migration duration and lock waits in deploy pipeline. Trend long runners before they block prod.
• Alert on invalid indexes after deploy. Failed CONCURRENTLY leaves landmines.
• Post-deploy smoke queries in CI/CD. psql -f assert_schema.sql with ON_ERROR_STOP.
• Track migration ownership in CODEOWNERS. Data team reviews db/migration/**.
• Include row count estimates in PR description for large DDL. Sets reviewer expectations.

FAQs

Related

• Migrations Basics - fundamentals
• Zero-Downtime DDL - concurrent patterns
• Migration Safety Rules - rule list

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.