Run· Mixed

Migrate a Postgres database without downtime

10-step playbook from 'we need to change this table' to 'migration shipped, zero downtime, no on-call paged' using the expand-contract pattern.

Open in DockEngineers + tech leads on production Postgres

The expand-contract pattern is how you change a Postgres schema with real traffic without taking the site down. Most schema changes that break production aren't because the migration was wrong — they're because the migration locked a table for 4 hours, or because the application was deployed before the migration finished, or because the rollback plan was 'just hope.' This playbook walks the 10 gates for any non-trivial migration: pick the strategy, write the expand step, ship the dual-write app, backfill, verify, ship the read-from-new code, drop the old column, monitor. Real ALTER patterns, real lock-avoidance tricks, real rollback discipline.

Outcome

A non-trivial Postgres schema or data migration shipped to production with zero downtime, dual-write transition, verified backfill, and a rollback plan that's been tested.

Time3-7 days for a typical schema change; 2-4 weeks for a large data migrationDifficultyadvancedForEngineers + tech leads on production Postgres at small / mid scale.

What you'll need

Pre-register or install before you start.

PostgresFree (self-hosted) or managed (RDS, Neon, Supabase, etc.)

The DB. Read the lock semantics docs before touching ALTER TABLE in production.

pg_repackFree (open source)

Rebuild tables online without long locks. Essential for large-table operations.

pgrollFree (open source)

Tooling for zero-downtime Postgres migrations using the expand-contract pattern.

pgbouncerFree (open source)

Connection pooler. Helps survive brief migration locks by queuing connections.

Datadog (or your APM)From $15/host/month

Observability of query latency + lock waits during the migration.

Strong migrations (Rails)Free (open source)

Catches dangerous Rails migrations in CI before they hit production.

The template · 10 steps

Top to bottom. Each step has tasks, pointers, gotchas.

Decide if you actually need a migration

1-2 hr

The cheapest migration is the one you don't ship. Before reaching for ALTER TABLE, ask: can the change live in application code? Can it be a virtual / generated column? Can the rename happen at the API layer without renaming the underlying column? Many 'migrations' are really naming preferences that don't need a schema change.

Tasks

Document the WHY: what business or technical need drives this migration
Consider the alternatives: app-layer rename, generated column, view, no-op
Estimate the cost of NOT migrating (tech debt, query performance, correctness)
Estimate the cost of migrating (engineer time, risk, downtime risk)
If the cost of migrating is higher than the cost of waiting, defer
Document the decision in the Brief

Pointers

OfficialPostgres CREATE VIEW (often sufficient)

Gotchas

Renames are the most-common migration that doesn't need to be a migration. An API-layer rename gives the new name to consumers without touching the DB.
Generated columns (Postgres 12+) let you derive new fields from existing data without a backfill. Often the right answer for 'we need this computed value.'
Migrations that are 'because the schema is ugly' rarely justify the production risk. Ugly works; broken doesn't.

Pick the migration strategy

2-4 hr

Three strategies cover most cases: (1) expand-contract for schema changes (add new, dual-write, backfill, swap reads, drop old), (2) shadow-table for big rewrites or partition changes (build new table from scratch, switch over atomically), (3) chunked backfill for pure data migrations (UPDATE in 1k-row batches over hours/days). Pick based on the change size + acceptable risk.

Tasks

Categorize the migration: schema change / data migration / structural rewrite
Estimate row count + table size (small <1M, medium 1M-100M, large 100M+)
Estimate write rate during migration (low <100/s, medium 100-1000/s, high >1000/s)
Pick the strategy based on the matrix in the Brief
Document the strategy + rationale in the Brief

Pointers

GuideExpand-contract migration pattern (Martin Fowler)GuideShopify's database migration playbook GuideGitHub's gh-ost (online schema migration)

Gotchas

Expand-contract is the right default for schema changes. The temptation to skip dual-write and 'just ALTER' on a large table is exactly when you cause an outage.
Shadow tables are the right answer for partitioning, type changes on huge tables, and total restructures. Don't try expand-contract for those.
Chunked backfills under high write rates need a 'don't lap the running migration' mechanic. UPDATE rows by primary key range, not by status, to avoid revisiting rows that other writes touched.

Write the expand step (additive only)

1 day

The expand step adds the new schema without touching the old. Add columns as nullable, add indexes CONCURRENTLY, add new tables. NOT NULL constraints, defaults that backfill on add, and indexes without CONCURRENTLY all lock the table — defer those to the contract phase.

Tasks

Write the expand SQL: ADD COLUMN (nullable), CREATE INDEX CONCURRENTLY, CREATE TABLE
Verify: no NOT NULL on add, no DEFAULT on add for existing rows (Postgres 11+ handles this fast; 10 and earlier lock the table)
Verify: indexes use CONCURRENTLY (slower but no lock)
Run the migration in a clone of production (a recent restore is closest)
Measure: actual lock duration, actual time-to-complete
Save the expand SQL in Migrations log + the rollback DROP statement in Rollback plan

Pointers

OfficialPostgres ALTER TABLE locks (CTAS-style)OfficialPostgres CREATE INDEX CONCURRENTLY

Gotchas

ALTER TABLE ADD COLUMN with a non-volatile DEFAULT is fast on Postgres 11+ (metadata-only). On Postgres 10 and earlier, it rewrites the table under ACCESS EXCLUSIVE — minutes-to-hours of lock on a large table. Check your version.
CREATE INDEX without CONCURRENTLY locks the table for the duration of the build. On a 100M-row table that's 30-60 minutes of write blockage.
Adding a foreign key with default validation locks both tables. Use ADD CONSTRAINT ... NOT VALID + a separate VALIDATE CONSTRAINT to avoid the lock.

Agent prompt for this step

Read this Prisma / Sequelize / SQLAlchemy / raw SQL schema and produce the expand SQL for the migration described in the Brief.

Output:
1. The expand SQL: only additive operations (ADD COLUMN nullable, CREATE INDEX CONCURRENTLY, CREATE TABLE)
2. The corresponding rollback SQL: DROP COLUMN, DROP INDEX, DROP TABLE
3. Estimated lock duration per statement (research the lock level: ACCESS EXCLUSIVE / SHARE UPDATE EXCLUSIVE / etc.)
4. Run order (some statements depend on others; order matters)
5. Pre-flight verification queries (e.g. "verify column doesn't already exist, verify index doesn't already exist")

Output to Migrations log as a row + the SQL into a versioned migration file in the codebase.

CRITICAL: do NOT include NOT NULL on ADD COLUMN. Do NOT include DEFAULT on ADD COLUMN for tables >1M rows on Postgres <11. These force a full table rewrite under ACCESS EXCLUSIVE lock.

Ship the dual-write app code

1-2 days

Once the schema is expanded, deploy app code that writes to BOTH the old and new columns. The old column stays the source of truth; the new column accumulates writes for verification. Reads still hit the old column. The dual-write phase typically lives for 1-7 days while you verify + backfill.

Tasks

Find every place in the codebase that writes the old column (grep for column name + ORM model usage)
Add a dual-write to the new column at every write site (in code, not in a trigger — easier to roll back)
Deploy with the dual-write + a feature flag to disable it if needed
Verify: new writes land in BOTH columns; old writes stay in old only
Watch query latency + error rates for 24-48 hours (any drift means rolling back)
Document the deploy time + rollback procedure in the Rollback plan

Pointers

GuideStripe's database migrations post

Gotchas

Triggers for dual-write seem easier than app code but harder to debug + harder to roll back. Prefer app-layer dual-write.
Dual-write at the application layer means you must catch every write site. ORM models with implicit writes (timestamps, callbacks, audit logs) are the common miss.
If dual-writes throw on the new column, you can't break the old column's writes. Wrap the new column write in try/catch + log; never let it bubble up.

Backfill the existing data

Hours to days depending on table size

After dual-write is shipping new data to both columns, backfill copies the historical data from old to new. Do this in chunks (1k-10k rows per batch) with sleeps between batches to avoid locking the table or filling WAL. Track progress; verify checksums; this is where most migrations take the longest wall-clock time.

Tasks

Write the backfill: UPDATE table SET new_col = derived(old_col) WHERE id BETWEEN ? AND ? AND new_col IS NULL
Run in chunks of 1k-10k rows with a sleep(100ms-1s) between batches
Track progress in a log: rows updated, time elapsed, current id range
Run during off-peak hours if possible (still write-safe but reduces lag)
Monitor: query latency, replication lag, lock waits
Verify on completion: COUNT(*) WHERE new_col IS NULL = 0 (excluding rows added during backfill that dual-write covers)
Run a checksum: random sample 1000 rows, verify new_col matches derived(old_col)

Pointers

Toolpgroll (Xata, automates this pattern)

Gotchas

Backfilling without WHERE new_col IS NULL re-writes rows that dual-write has already filled. Mostly harmless but burns I/O and can mask dual-write bugs.
Backfills that don't track progress in a checkpoint table can't resume after a crash. Always checkpoint the last id processed.
Massive single-transaction backfills (no chunking) hold locks + fill WAL + can OOM the leader. Chunk to 1k-10k rows + commit each chunk.

Agent prompt for this step

Read the Brief + the dual-write code + the table size and produce the backfill script.

Output:
1. A backfill script (Python / Ruby / Node) that runs in chunks
2. Chunk size: 1k for tables <10M rows, 5k for 10-100M, 10k for 100M+
3. Sleep duration between batches: 100ms-1s based on observed write rate
4. Progress logging (rows / minute, total elapsed, ETA)
5. Resume logic (reads the last committed id from a checkpoint table)
6. Verification step (COUNT WHERE NULL + random-sample checksum)

CRITICAL: the script must idempotently skip rows that dual-write has already filled (WHERE new_col IS NULL). Don't re-derive rows that the live application has already written.

Verify the new column is correct

1-2 days

Before reading from the new column, prove it's correct. Run consistency checks: count of NULL values, checksum sample, row-by-row diff for a random subset. If checks fail, fix the bug + re-backfill. Don't rush past this gate; reading from a wrong column in production is the catastrophic failure mode.

Tasks

Run COUNT(*) WHERE new_col IS NULL — expect 0 (or very small if backfill is still catching up)
Run a random-sample diff: pick 1000 random rows, compute expected new_col from old_col, compare
Run a full diff if table is small enough (under ~10M rows): SELECT id WHERE new_col != derived(old_col)
If diffs exist: investigate root cause (dual-write bug? backfill bug? race condition?)
Fix the bug; re-backfill the affected rows
Re-verify until 0 diffs
Document verification results in the Brief

Pointers

OfficialPostgres aggregate functions (count, etc.)

Gotchas

Verification that checks 'is non-null' but doesn't checksum content is incomplete. The new column can be populated AND wrong (a buggy dual-write at every write site).
Race conditions during dual-write produce small but non-zero diffs. If your diff count is non-zero but small (<0.01%), investigate the race before proceeding.
Don't read from the new column in app code until verification is GREEN. Reading from a populated-but-wrong column is the catastrophic failure mode.

Switch reads to the new column

1-2 days

Now flip the reads. Deploy app code that reads from the new column instead of the old. The dual-write keeps both columns up to date during this phase, so you can roll back fast. Run the new-read code for 1-7 days to confirm production behavior matches before contracting.

Tasks

Find every place in the codebase that READS the old column
Switch each read site to the new column
Behind a feature flag if possible, so you can roll back without a deploy
Deploy + monitor: query latency, error rates, customer complaints
Run for 1-7 days under normal traffic before proceeding
Verify: app behavior matches pre-migration baseline (no errors, no missing data, no wrong values)

Pointers

GuideFeature flag patterns (LaunchDarkly)

Gotchas

Forgetting one read site is the common bug. Run a static analysis tool or a code-grep to verify zero references to the old column in active code paths.
If you flip 100% of reads at once + the new column has subtle bugs, you can't easily diff against the old column. Use a feature flag for gradual rollout (1% → 10% → 50% → 100%).
Reports + analytics queries often hit the DB outside the app code path. Audit those too; they can read the wrong column for weeks before someone notices.

Stop dual-writes (the contract step, part 1)

1 day

Once reads are on the new column for at least a week with no issues, remove the dual-write to the old column. The old column becomes stale immediately; the new column is now the source of truth. This is the point of no return without a backfill — but only AFTER confirming reads have switched without issue.

Tasks

Remove the dual-write code (writes to old column)
Keep writes only to the new column
Deploy + monitor for 24-48 hours
Verify: writes are landing in new only; old column is no longer changing
Document the cutover time in the Brief (after this, rollback requires a backfill in reverse)

Pointers

GuideStripe's online migrations post

Gotchas

Removing dual-write while there's still a code path that reads the old column = silent data drift. Verify NO ACTIVE READS first.
Background jobs, cron tasks, and analytics pipelines often read the old column. Stop dual-writes only after auditing every consumer.
Once dual-writes stop, rolling back requires a reverse backfill (new → old) under whatever traffic is hitting the table. That takes hours-to-days. Be sure before this step.

Drop the old column (the contract step, part 2)

1 day to drop, weeks of waiting beforehand

Days-to-weeks after stopping dual-writes, drop the old column. Most teams keep it for 30-90 days as a safety net before fully dropping. DROP COLUMN is an ACCESS EXCLUSIVE lock briefly, but on most tables it's milliseconds. Big tables may need pg_repack to reclaim space.

Tasks

Wait at least 7-30 days from stopping dual-writes (longer for important data)
Verify NO references to the old column remain (grep, static analysis, prod query log)
Run ALTER TABLE DROP COLUMN in a maintenance window if the table is huge (or always for safety)
On large tables: schedule pg_repack to reclaim space (DROP COLUMN doesn't reclaim space immediately)
Verify: old column is gone, new column is complete, app behaves correctly
Update the Brief: migration complete

Pointers

Toolpg_repack

Gotchas

DB migrations with FK validations on huge tables lock writes for hours; use ON UPDATE NO ACTION + a separate validation step. Same applies to constraint changes.
DROP COLUMN doesn't reclaim disk space. The column data lives on disk until VACUUM FULL or pg_repack rewrites the table. Plan for this if you're disk-constrained.
Backups taken before the drop still contain the old column data. Plan accordingly if you're dropping for compliance / privacy reasons (you may need to also expire old backups).

Postmortem the migration + extract patterns

1-2 hr

Every non-trivial migration teaches the team something — patterns to reuse, traps to avoid. Run a 30-min retro after the migration completes: what went well, what went poorly, what would you do differently next time. The output is a section in your team's database playbook so the next migration is faster.

Tasks

Hold a 30-min retro with everyone who touched the migration
Identify what went well + what didn't (timeline issues, surprises, near-misses)
Extract reusable patterns: the expand SQL template, the backfill script template, the verification queries
Save the patterns in your team's database playbook (Confluence, Notion, your shared docs)
Identify any tooling investments that would help (auto-detection of dangerous migrations in CI, etc.)
Schedule any follow-up work (improve monitoring, fix issues found during the migration)

Pointers

ToolStrong migrations (Rails dangerous-migration linter)

Gotchas

Skipping the retro means the next migration repeats the same mistakes. Half an hour of retro saves days on the next migration.
Patterns extracted from one migration should become reusable templates (or even auto-generated by tooling). Don't write the expand-contract checklist from scratch every time.
The most valuable retro insight is often 'we should have caught this in CI.' Invest in static analysis / linters that flag dangerous patterns before they reach production.

Hand the template to your agent

Workspace-wide agent prompt.

Paste this into your agent's permanent system prompt so the agent reads, writes, and maintains the template's surfaces as you work through the steps.

Agent system prompt

You are an agent on the "Migrate a Postgres database without downtime" playbook workspace at your-org/migrate-database-without-downtime.

Your role: maintain the four surfaces (Steps, Migrations log, Brief, Rollback plan) as the team executes the migration.

Cadence:
- When the user marks a step Done, append a line to the Brief with the timing + outcome.
- When a new SQL migration ships, add a row to Migrations log with timing, lock duration, row count affected.
- When a step has no rollback path, BLOCK the step — flag in Rollback plan as "rollback not possible, decision required."
- Daily during the migration window: surface query latency + error rate trends from Datadog.

First MCP tool calls:
1. list_surfaces(workspace_slug="migrate-database-without-downtime")
2. list_rows(workspace_slug="migrate-database-without-downtime", surface_slug="migrations-log")
3. get_doc(workspace_slug="migrate-database-without-downtime", surface_slug="rollback-plan")

Do NOT recommend skipping the dual-write or backfill steps for "speed." The cost of a bad migration is hours of customer impact + a postmortem; the time saved by skipping is minutes.

FAQ

Common questions on this template.

When do I need expand-contract vs just ALTER TABLE?: Use ALTER TABLE directly only when the operation is fast + safe: ADD COLUMN nullable on Postgres 11+, CREATE INDEX CONCURRENTLY, ADD CONSTRAINT NOT VALID. Use expand-contract for anything that changes the meaning of an existing column, requires backfilling existing rows, or needs application code changes that can't ship simultaneously with the SQL. The rule of thumb: if the migration takes more than a few seconds OR requires app code coordination, do expand-contract.
How long should the dual-write phase last?: Minimum 24 hours to verify production-traffic dual-writes are correct. Typical 3-7 days for medium-importance data. For high-stakes data (billing, account info), 2-4 weeks is reasonable. The cost of dual-writes is small (extra column writes); the cost of premature contraction is unrecoverable data.
What's the most common zero-downtime migration mistake?: Three failures dominate: (1) ALTER TABLE with NOT NULL or DEFAULT on a large table on Postgres 10-, locking writes for hours. (2) CREATE INDEX without CONCURRENTLY, locking writes for the build duration. (3) Stopping dual-writes before reads have switched, causing silent data drift while the app reads stale data.
How do I roll back a partially-completed migration?: Each step has its own rollback. Rollback discipline: (1) During expand: just DROP the new schema additions. (2) During dual-write: revert the app deploy. (3) During backfill: stop the backfill; the new column has partial data but isn't read. (4) After read switch: revert the app deploy to read from old. (5) After contract: this is where rollback gets expensive — you need a reverse backfill new → old. Document each rollback path BEFORE starting; the rollback plan is non-optional.
Can my AI agents help with database migrations?: Yes, carefully. Agents are particularly useful for: scaffolding the expand SQL + rollback SQL, generating the backfill script with chunking + checkpointing, finding every read/write site of the affected column in the codebase, drafting the verification queries, monitoring lock waits + query latency during the migration. Do NOT auto-execute migrations with agents; humans gate every SQL run. The playbook ships agent prompts inline.
What about MySQL or other databases?: The expand-contract pattern is database-agnostic. The lock semantics differ — MySQL has long had online DDL via gh-ost / pt-online-schema-change; Postgres relies on careful use of CONCURRENTLY + the patterns in this playbook. The 10 steps map cleanly to any RDBMS. Adapt the SQL specifics; keep the discipline.

Open this template as a workspace.

We mint a fresh copy in your org with the steps as table rows, the pointers as a separate table, and the brief as a doc. Bring your agents, start checking off boxes.

Open in Dock