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:
- Schema migration — changing tables, columns, indexes, constraints, or data shape while the database stays in place.
- 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 pattern | Best fit | Main advantage | Main risk |
|---|---|---|---|
| Additive DDL | Small backward-compatible schema changes | Simple | Lock/rewrite impact can be underestimated |
| Expand and contract | Most production schema changes | Keeps old/new app versions compatible | Requires multiple releases |
| Dual write | Data-shape or system-of-record transition | Gradual verification | Divergence after partial writes |
| Replication/change-data cutover | Moving database host/provider | Short final cutover | Lag, consistency, failback complexity |
| Maintenance window | Small/internal or high-risk migrations | Simplest recovery model | Planned 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.
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:
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.
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.
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 CONCURRENTLYwhere appropriate;- adding eligible constraints as
NOT VALIDand validating later; - creating a compatible unique index before attaching a constraint;
- using deliberate
lock_timeoutandstatement_timeoutvalues; - separating schema change from bulk backfill.
For example:
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 TABLEoperation 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:
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
The live Raff Managed Databases pages currently describe managed PostgreSQL and MySQL with built-in backup/recovery, private connectivity, monitoring, optional high availability, and engineer-assisted migration paths.
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.
