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

Hotfix migration process?

Same PR + pipeline urgency path; never psql hotfix on primary without backfill into git migration immediately after.

Squash old migrations?

Only via baseline ADR on greenfield clones - never rewrite history prod already applied.

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

Official PostgreSQL Documentation - Central hub for all manuals.
PostgreSQL Wiki - Community FAQ, howtos, and technical notes.
Awesome Postgres - Curated collection of tools, libraries, extensions, and resources.
PostgreSQL 16 Administration Cookbook - Ciolli, Riggs et al.
PostgreSQL DBA (2025 2nd Edition) - Tony CL Chan.

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+.

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

Hotfix migration process?

Same PR + pipeline urgency path; never psql hotfix on primary without backfill into git migration immediately after.

Squash old migrations?

Only via baseline ADR on greenfield clones - never rewrite history prod already applied.

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

Official PostgreSQL Documentation - Central hub for all manuals.
PostgreSQL Wiki - Community FAQ, howtos, and technical notes.
Awesome Postgres - Curated collection of tools, libraries, extensions, and resources.
PostgreSQL 16 Administration Cookbook - Ciolli, Riggs et al.
PostgreSQL DBA (2025 2nd Edition) - Tony CL Chan.

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+.

Migrations Best Practices

Search Documentation

Migrations Best Practices