Database Migration Cheatsheet: Zero-Downtime Cutover Patterns

For: A CTO at a 50–200 person B2B SaaS company who has scheduled a production database migration — schema change, engine swap, or cloud provider move — and is staring at a weekend maintenance window they know is going to slip

Pick your cutover pattern based on write-to-read ratio and acceptable replication lag — not your database engine, and not what your team used last time. Expand-contract for schema changes on the same engine. Logical replication with lag monitoring for engine or cloud moves where reads dominate. Dual-write with reconciliation when you need bidirectional safety during a long transition. Shadow reads when correctness must be proven before traffic shifts. The rest of this post is the reference you can keep open during the migration.

Step 1: Classify the migration

Every zero-downtime cutover falls into one of three buckets. The bucket determines which patterns are even viable.

Step 2: Pick the pattern

Use write-to-read ratio and lag tolerance as the two decision axes. Everything else is a distractor.

Step 3: The sequencing playbooks

Expand-contract (schema change)

1. Expand: Add new column/table. Make it nullable or default-valued. Deploy.
2. Backfill: Write a batched, resumable job. Chunk by PK range. Rate-limit against replica lag.
3. Dual-write: App writes to both old and new columns. Reads still hit old.
4. Flip reads: Switch reads to new column behind a feature flag. Roll gradually.
5. Contract: Stop writing to old. Wait a full backup cycle. Drop.

Failure mode to watch: the backfill job holding long transactions that block DDL or cause replica lag. Break it into smaller chunks than you think you need.

Logical replication cutover (engine swap or cloud move)

1. Provision target. Match version, extensions, encoding.
2. Snapshot source → restore to target. Note the LSN/GTID/binlog position.
3. Start logical replication (Postgres pglogical / native pub-sub, MySQL binlog, or a CDC tool like Debezium/AWS DMS) from that position.
4. Monitor lag until it stabilizes at sub-second.
5. Route read traffic to target first. Verify.
6. Cutover: pause writes at source (application-level or via read-only mode), wait for lag = 0, promote target, flip writes.
7. Keep source running read-only for at least 24 hours as rollback safety.

Failure mode: unreplicated objects. Sequences, large objects, and DDL don’t always replicate. Audit them before cutover, not during.

Dual-write with reconciliation (cross-engine, write-heavy)

1. Deploy app that writes to both DBs synchronously. Old is source of truth.
2. Backfill historical data via ETL. Record the cutoff timestamp.
3. Run a reconciler continuously: sample rows, diff, alert on drift.
4. When drift rate is stable and near zero for several days, flip reads to new.
5. Make new the source of truth. Keep dual-write on for rollback.
6. Remove dual-write after a full week of clean reconciliation.

Failure mode: partial write failures. Decide upfront: does a failed write to the new DB fail the request or just log? Both are defensible. Pick before you ship.

Shadow read (verification, not cutover)

1. App issues the same read to both DBs. Return old’s result to user.
2. Async diff the results. Log mismatches with query fingerprint.
3. Fix mismatches until diff rate is acceptable.
4. Only then move to a real cutover pattern above.

Step 4: The cutover moment itself

This is the consistency boundary. Every zero-downtime plan lives or dies here.

Step 5: Pre-flight checklist

• Every table has a primary key. (CDC tools silently skip or misbehave without one.)
• Long-running transactions on source identified and killed before snapshot.
• Extensions, stored procedures, triggers audited on target.
• Character set and collation match — especially for MySQL utf8 vs utf8mb4.
• Replication lag alerting wired to the same channel humans watch.
• Runbook has a named decision-maker for the go/no-go call.
• Rollback rehearsed on staging with production-scale data.

Anti-patterns

• “We’ll do it during the maintenance window.” Windows slip. Design for zero downtime and you won’t need one.
• Dumping and restoring a large DB in one shot. Fine for <50 GB. Painful above.
• Trusting pg_dump to preserve sequences correctly across versions. It doesn’t always. Verify.
• Testing the migration once, in staging, with 1% of production data. The failure modes only appear at scale.
• Cutting over on Friday. You know why.

When to bring in help

Engine swaps and cross-cloud moves on write-heavy production systems are where teams typically lose data — not from bad tooling, but from an unrehearsed cutover sequence. If the migration is coupled to a broader modernization effort — replatforming a monolith, extracting services, or moving off a legacy stack — the migration plan should be designed alongside the app changes, not after. That’s the kind of work our digital transformation team scopes for teams shipping under load.

Frequently Asked Questions

What is the safest zero-downtime pattern for a Postgres major-version upgrade?

Logical replication using pglogical or native logical replication (Postgres 10+). Snapshot to the new version, start replication from the correct LSN, verify lag is sub-second, then flip reads first and writes second behind a proxy. pg_upgrade is faster but requires downtime.

How do I migrate Postgres without downtime when my workload is write-heavy?

Logical replication will struggle because lag grows with write volume. Use dual-write with reconciliation: the app writes to both databases, a background job continuously diffs samples, and you only cut over reads once drift is stable near zero. It’s more application code but survives write pressure.

Do I need dual-write if I’m using AWS DMS or Debezium?

Usually no for a same-engine move. Yes, often, for cross-engine moves where type mappings, JSON semantics, or transaction boundaries differ. Run a shadow-read verification phase first to catch semantic mismatches before you rely on CDC alone.

How long should I keep the old database running after cutover?

At least one full backup cycle plus one business week, in read-only mode. Rollback pressure usually appears within 48 hours, but subtle data issues (a reporting job, a monthly batch) show up later. Storage is cheaper than a second migration.

How do we estimate effort and risk for our specific migration?

The variables that matter are data volume, write-to-read ratio, engine change vs. same-engine, and coupling to application code. For a scoped assessment against your actual workload, contact CodeNicely for a personalized review.