Zero-Downtime Database Migrations: What Actually Works for Small Teams

Key Takeaways

Zero-downtime migration depends on compatibility, sequencing, observability, rollback, and recovery. Expand-contract is the safest default for schema changes; live database moves need explicit replication, cutover, and failback planning.

Serdar Tekin
Serdar Tekin
Co-Founder & CTO
Updated 16 min read
Written for: Developers, database operators, founders, and small platform teams planning production schema changes or live database cutovers.
Database MigrationPostgreSQLMySQLManaged DatabasesDevOpsReliability
Last reviewed:
Guide
Zero-Downtime Database Migrations: What Actually Works for Small Teams
rafftechnologies.com/learn
On this page

Run your migrations against managed PostgreSQL or MySQL, from $7.99 a month.

PostgreSQL, MySQL, Valkey, ClickHouse and Kafka, with backups included and high availability when you need it.

A zero-downtime database migration is a staged database change or database move that keeps the application available while old and new application versions, schemas, or database endpoints remain compatible during the transition.

For small teams, the safest default is still expand and contract: add the new structure without removing the old one, deploy code that tolerates both states, backfill data in controlled batches, switch reads and writes deliberately, then remove the legacy path only after the new path is proven.

Raff Technologies supports both self-managed database workloads on cloud VMs and Managed Databases. That creates two different migration problems that are often confused:

  1. Schema migration — changing tables, columns, indexes, constraints, or data shape while the database stays in place.
  2. Database cutover — moving the system of record from one database host or provider to another while application traffic continues.

Both can be designed for little or no user-visible downtime, but neither is safe merely because a tool calls the operation “online.” The migration is safe only when application compatibility, data movement, cutover, rollback, and recovery are planned separately.

Zero downtime is an application-and-data compatibility property, not a migration-tool feature.

Zero-downtime database migration: quick decision table

Migration patternBest fitMain advantageMain risk
Additive DDLSmall backward-compatible schema changesSimpleLock/rewrite impact can be underestimated
Expand and contractMost production schema changesKeeps old/new app versions compatibleRequires multiple releases
Dual writeData-shape or system-of-record transitionGradual verificationDivergence after partial writes
Replication/change-data cutoverMoving database host/providerShort final cutoverLag, consistency, failback complexity
Maintenance windowSmall/internal or high-risk migrationsSimplest recovery modelPlanned downtime

Choose expand and contract when the database remains in place but application schema changes.

Choose replication or staged data transfer when moving a live database to a new host or managed service.

Choose a maintenance window when the complexity of online synchronization creates more risk than a short planned interruption.

The best migration is not the one with the lowest theoretical downtime. It is the one your team can observe, pause, verify, roll back, and recover under pressure.

Compatibility windows are the foundation of zero downtime

During rolling, blue-green, or overlapping deployments, old and new application versions may run at the same time. The database must support both versions until the transition is complete.

For the application release patterns that create those compatibility windows, see Production Deployment Strategies for SaaS Apps. This database guide remains the detailed migration-safety reference.

That means:

  • old code must still work after the schema expands;
  • new code must tolerate partially migrated data where applicable;
  • writers need an explicit temporary data contract;
  • rollback must remain possible after new code starts writing;
  • destructive cleanup must wait until old code is gone;
  • backups or database-native recovery must exist before risky changes begin.

A SQL statement succeeding in staging does not prove a production migration is safe. Real behavior also depends on:

  • table size;
  • write rate;
  • lock acquisition;
  • long-running transactions;
  • temporary disk usage;
  • WAL or binary-log growth;
  • replication lag;
  • connection-pool behavior;
  • engine/version behavior;
  • deployment order.

Expand and contract is the safest schema-migration default

Suppose users.full_name is being replaced with first_name and last_name.

A risky release is:

Text
Remove or rename full_name Deploy code that expects first_name and last_name

Any old instance still using full_name can fail immediately.

A safer sequence is:

1. Expand the schema

Add the new structure while leaving the old representation intact.

Sql
ALTER TABLE users ADD COLUMN first_name text; ALTER TABLE users ADD COLUMN last_name text;

Do not assume an additive change is automatically harmless. Whether DDL is metadata-only, blocking, or table-rewriting depends on the engine, version, table definition, and exact operation.

2. Deploy compatible application code

During the compatibility window, the application may:

  • keep reading the old representation;
  • dual-write old and new fields;
  • prefer the new representation when available;
  • fall back to the old representation;
  • use a feature flag for the new read path.

Every application version still in production needs a defined behavior.

3. Backfill existing rows

Run the data migration separately from schema deployment.

Sql
UPDATE users SET first_name = ..., last_name = ... WHERE id > :last_id AND id <= :next_id AND first_name IS NULL;

Use bounded, resumable batches instead of one long transaction.

4. Switch reads and writes

After verifying data quality:

  • move reads to the new fields;
  • measure null/mismatch rates;
  • verify no old application instance writes only the old representation;
  • keep fallback logic during an observation period.

5. Contract later

Remove dual writes, legacy fields, old indexes, and compatibility code only after rollback to the old application version is no longer required.

Temporary duplication is technical debt, but during a migration it is also recovery margin.

Backfills should be treated as production workloads

Large backfills can consume CPU, I/O, WAL or binary logs, connections, and replication capacity for hours even when the schema change itself is fast.

A production backfill should be:

  • batched so transactions remain bounded;
  • idempotent so retrying is safe;
  • throttled so customer traffic stays healthy;
  • observable with progress and error metrics;
  • interruptible without losing migration state;
  • verifiable against the old representation.

Monitor at minimum:

  • application latency/error rate;
  • database CPU and memory pressure;
  • storage latency and free space;
  • WAL/binlog generation;
  • lock waits;
  • transaction age;
  • replica lag;
  • connection saturation;
  • backfill throughput.

A migration that completes while causing sustained customer-visible degradation did not succeed operationally.

Dual writes need an authority and reconciliation plan

Dual writes are useful when old and new representations must coexist, but they can create divergence.

If write A succeeds and write B fails, the team needs to know:

  • which representation is authoritative;
  • how mismatches are detected;
  • whether retries are idempotent;
  • how failed writes are reconciled;
  • when dual writing can safely stop.

Use dual writes only when the compatibility window requires them. They should be a temporary migration mechanism, not an indefinite architecture by accident.

PostgreSQL zero-downtime migrations need PostgreSQL-specific planning

PostgreSQL provides several useful techniques for reducing blocking, but none removes the need to understand the exact operation.

Useful patterns include:

  • CREATE INDEX CONCURRENTLY where appropriate;
  • adding eligible constraints as NOT VALID and validating later;
  • creating a compatible unique index before attaching a constraint;
  • using deliberate lock_timeout and statement_timeout values;
  • separating schema change from bulk backfill.

For example:

Sql
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

CREATE INDEX CONCURRENTLY allows normal writes to continue during much of index creation, but it takes longer than a normal index build, cannot run inside a normal transaction block, can be delayed by old transactions, and may leave an invalid index if it fails.

Treat “concurrent” as reduced blocking, not “zero operational impact.”

MySQL zero-downtime migrations need operation-specific DDL checks

MySQL supports operation-specific online DDL behavior through mechanisms such as:

  • ALGORITHM=INSTANT;
  • ALGORITHM=INPLACE;
  • ALGORITHM=COPY;
  • LOCK=NONE.

The important point is that support varies by the exact operation and engine version. Some changes are metadata-only, some rebuild data while permitting concurrent DML, and others require stronger locking or copying.

Before production:

  • verify the exact ALTER TABLE operation against the deployed version;
  • request the intended algorithm/lock behavior where appropriate;
  • confirm what the server actually accepts;
  • monitor temporary space and binlog growth;
  • measure replica lag if replication is involved.

“Online DDL” means the operation may preserve more availability. It does not mean it is free of locks, resource use, or failure modes.

Moving databases is different from changing schema

A self-hosted-to-managed migration may keep the same schema while moving the system of record to a new endpoint.

A clean cutover separates the phases:

Text
Create target database ↓ Validate engine/version/extensions ↓ Load baseline data ↓ Synchronize ongoing writes ↓ Measure replication lag + consistency ↓ Final write/cutover decision ↓ Switch application connection target ↓ Validate business workflows ↓ Retain source for agreed rollback window

The exact mechanism depends on database engine, size, source configuration, supported replication method, write volume, and acceptable RPO.

Do not treat a provider migration as only a connection-string change. The application team still owns:

  • schema/application compatibility;
  • credential rollout;
  • connection-pool behavior;
  • cutover timing;
  • data verification;
  • failback decision;
  • application validation after cutover.

How Raff Managed Databases changes the operational boundary

Raff Managed Databases runs PostgreSQL and MySQL with built-in backup and recovery, private connectivity, monitoring, optional high availability, and a free, engineer-assisted migration.

The current migration flow advertised by Raff uses continuous replication so the target stays in sync while the source remains live, followed by a final cutover once lag reaches the agreed point.

That can reduce the final interruption for eligible migrations, but it does not remove workload-specific migration planning. Before committing to a cutover, verify:

  • source and target engine compatibility;
  • required extensions/features;
  • replication eligibility;
  • schema objects and privileges;
  • database size and sync duration;
  • write rate;
  • connection-string rollout;
  • acceptable lag;
  • rollback/failback window.

Use the live Managed PostgreSQL, Managed MySQL, and Managed Databases pages as the source of truth for current engine versions, migration eligibility, backup behavior, HA options, and pricing rather than embedding mutable limits into this guide.

For the operating-model decision, see Managed Database vs Self-Hosted. For a production move, use Managed Database Migration Checklist.

Compare the current managed PostgreSQL and MySQL migration path, recovery model, and database options before planning your cutover.

Rollback and restore solve different failures

Rollback returns application behavior or traffic to a previous compatible path.

Restore reconstructs database state from a backup, snapshot, or point-in-time recovery mechanism.

A rollback plan should answer:

  • can old code read data written by new code?
  • which representation/database is authoritative after cutover starts?
  • can writes safely return to the old target?
  • did any irreversible external side effects occur?
  • does app rollback require schema rollback?
  • how will writes during the failed release be reconciled?

A backup is essential protection, but restore is usually too disruptive to be the first-line rollback mechanism for an ordinary application release.

For self-managed databases on Raff VMs, Data Protection can complement database-native backups with VM-level recovery. Managed database workloads should use the service's current database-native recovery controls and still test application recovery behavior.

Rollback protects the release path. Restore protects data state. Production migration planning needs both.

Zero downtime can fail because of old transactions and connection pools

Teams often focus on migration SQL while ignoring application connections.

Long-running transactions can delay DDL, index builds, cleanup, and replication progress. Persistent connection pools can also keep application instances attached to an old database endpoint after cutover.

Before the change:

  • inspect long-running transactions;
  • define connection-pool recycle behavior;
  • lower or control DNS TTL where DNS is part of cutover;
  • document how workers and scheduled jobs reconnect;
  • identify clients that cache credentials/endpoints;
  • verify the rollback path uses compatible credentials.

The database may be healthy while part of the application still talks to the wrong endpoint.

Rehearse with production-like data, not an empty staging database

A useful rehearsal should approximate the characteristics that affect migration behavior:

  • row count and table size;
  • index/constraint layout;
  • data distribution;
  • engine/version;
  • write rate and concurrency;
  • connection-pool behavior;
  • replication/synchronization method;
  • available storage headroom.

Measure:

  • lock acquisition time;
  • DDL duration;
  • backfill throughput;
  • temporary storage growth;
  • WAL/binlog growth;
  • replication lag;
  • application latency;
  • rollback duration;
  • cutover duration.

Testing the same SQL against a tiny development database is not a production migration rehearsal.

Define stop conditions before the change

A migration runbook should say when to stop before operators are under pressure.

Examples:

  • lock wait exceeds the planned threshold;
  • application error rate crosses the agreed limit;
  • database CPU/I/O remains saturated;
  • replication lag exceeds the cutover target;
  • free storage falls below the safety margin;
  • data-verification checks fail;
  • backfill throughput indicates the migration window will be missed.

A written stop condition turns “should we continue?” into an operational decision instead of an incident-room debate.

Migration runbook for small teams

Before the migration

  • Classify the change: schema migration, data backfill, or database-host cutover.
  • Define the compatibility window.
  • Identify old/new application versions that may overlap.
  • Verify backups/PITR/restore procedures.
  • Estimate temporary storage requirements.
  • Inspect long-running transactions.
  • Validate engine/version/extension compatibility.
  • Rehearse with representative data.
  • Define stop conditions.
  • Define rollback/failback conditions.
  • Assign one cutover decision owner.

During the migration

  • Record start time and migration version.
  • Monitor locks, transactions, DB load, latency, and errors.
  • Track backfill or replication progress.
  • Throttle when agreed limits are crossed.
  • Verify data before switching reads/writes.
  • Switch endpoints or feature flags deliberately.
  • Confirm workers/jobs reconnect correctly.
  • Retain the previous path during the rollback window.

After cutover

  • Verify application errors and latency.
  • Validate business-critical records and workflows.
  • Confirm background workers and reporting paths.
  • Monitor mismatch/null/fallback metrics.
  • Verify backup/recovery on the new database.
  • Keep rollback capability for the agreed period.
  • Schedule contract cleanup separately.
  • Remove legacy compatibility code only when rollback is no longer required.

When a maintenance window is the better choice

Zero downtime should not become an architectural vanity metric.

A short planned maintenance window can be safer when:

  • the workload is internal or low traffic;
  • replication setup is more complex than the business impact of downtime;
  • the team cannot safely operate dual writes;
  • the schema change is fundamentally incompatible;
  • rollback after online migration would be unclear;
  • the application has hard-to-control external clients;
  • the migration cannot be rehearsed confidently.

If five minutes of planned downtime is cheaper and safer than maintaining weeks of transitional logic, choose the maintenance window.

Frequently asked questions

What is a zero-downtime database migration?

A zero-downtime database migration is a staged schema or database-host change designed to keep the application serving users while old and new application/database states remain compatible during the transition.

What is the expand-and-contract migration pattern?

Expand adds new schema without removing the old structure. Compatible application code is deployed, data is backfilled, reads/writes move to the new representation, and legacy schema is removed in a later release.

Should database backfills run inside schema migrations?

Usually not for large datasets. Backfills are easier to batch, throttle, pause, retry, observe, and verify when they run as a separate operational workload.

Can PostgreSQL and MySQL use the same zero-downtime migration plan?

The overall sequencing principles are similar, but DDL algorithms, locking behavior, replication, and engine-specific controls differ. Verify every high-risk operation against the deployed engine/version.

Does online DDL guarantee zero downtime?

No. Online or concurrent DDL can reduce blocking, but it can still consume CPU, I/O, temporary storage, locks, and replication capacity and can fail in workload-specific ways.

How do I move a live database to a managed service?

Create and validate the target, load baseline data, synchronize ongoing changes when supported, measure lag and consistency, perform a controlled endpoint cutover, validate application behavior, and keep a defined rollback/failback window.

Can Raff migrate a live PostgreSQL or MySQL database?

Yes, free: a Raff engineer moves the database with you, and for PostgreSQL and MySQL Raff keeps a live copy in sync while the source keeps running, until the final cutover. Plan the cutover window, client reconnects and rollback with the engineer.

What is the difference between migration rollback and database restore?

Rollback moves application behavior back to a previous compatible path. Restore reconstructs database state from backups or point-in-time recovery. They protect different failure modes.

Is a maintenance window acceptable for a database migration?

Yes. A short planned interruption can be safer when online replication, dual writes, compatibility windows, or failback would add more operational risk than the downtime itself.

What should I monitor during a zero-downtime migration?

Monitor application error rate and latency, database CPU and I/O, lock waits, transaction age, storage headroom, WAL/binlog growth, replication lag, connection saturation, and migration progress.

Conclusion

Zero-downtime database migration is disciplined sequencing, not clever SQL.

Use expand and contract for most production schema changes. Treat backfills as observable operational workloads. Plan PostgreSQL and MySQL against their actual engine behavior. For host/provider migrations, separate baseline transfer, replication, cutover, verification, failback, and recovery into explicit steps.

Raff Managed Databases can reduce the operational work around backups, monitoring, high availability, and supported migration workflows, but the application team still owns compatibility and cutover correctness.

The goal is not “zero seconds of downtime at any cost.” The goal is a migration your team can stop, verify, roll back, and recover without losing control of production data.

Sources

Was this article helpful?
Serdar Tekin
Serdar Tekin
Co-Founder & CTO

Co-Founder & CTO at Raff, focused on cloud infrastructure, systems architecture, security, reliability, and production operations.

Run your migrations against managed PostgreSQL or MySQL, from $7.99 a month.

PostgreSQL, MySQL, Valkey, ClickHouse and Kafka, with backups included and high availability when you need it.

14-day money-back guarantee

Frequently asked questions

What is a zero-downtime database migration?
A zero-downtime database migration is a staged schema or database-host change designed to keep the application serving users while old and new application/database states remain compatible during the transition.
What is the expand-and-contract migration pattern?
Expand adds new schema without removing the old structure. Compatible application code is deployed, data is backfilled, reads/writes move to the new representation, and legacy schema is removed in a later release.
Should database backfills run inside schema migrations?
Usually not for large datasets. Backfills are easier to batch, throttle, pause, retry, observe, and verify when they run as a separate operational workload.
Can PostgreSQL and MySQL use the same zero-downtime migration plan?
The overall sequencing principles are similar, but DDL algorithms, locking behavior, replication, and engine-specific controls differ. Verify every high-risk operation against the deployed engine/version.
Does online DDL guarantee zero downtime?
No. Online or concurrent DDL can reduce blocking, but it can still consume CPU, I/O, temporary storage, locks, and replication capacity and can fail in workload-specific ways.
How do I move a live database to a managed service?
Create and validate the target, load baseline data, synchronize ongoing changes when supported, measure lag and consistency, perform a controlled endpoint cutover, validate application behavior, and keep a defined rollback/failback window.
Can Raff migrate a live PostgreSQL or MySQL database?
Yes, free: a Raff engineer moves the database with you, and for PostgreSQL and MySQL Raff keeps a live copy in sync while the source keeps running, until the final cutover. Plan the cutover window, client reconnects and rollback with the engineer.
What is the difference between migration rollback and database restore?
Rollback moves application behavior back to a previous compatible path. Restore reconstructs database state from backups or point-in-time recovery. They protect different failure modes.
Is a maintenance window acceptable for a database migration?
Yes. A short planned interruption can be safer when online replication, dual writes, compatibility windows, or failback would add more operational risk than the downtime itself.
What should I monitor during a zero-downtime migration?
Monitor application error rate and latency, database CPU and I/O, lock waits, transaction age, storage headroom, WAL/binlog growth, replication lag, connection saturation, and migration progress.