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

Zero-downtime database migration is a staged database change that keeps the application available while old and new application versions remain compatible with the database 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 old path only after the new one is proven.

Raff Technologies supports more than 3,000 customers and 15,000 VMs, and Raff now provides both self-hosted database infrastructure and live Managed PostgreSQL and Managed MySQL. That adds a second migration case to the usual schema-change problem: teams may also move a database from a self-hosted VM to a managed service. The same principle applies in both cases—the migration is safe only when data movement, application compatibility, cutover, rollback, and recovery are planned as separate concerns.

At Raff, the recurring migration failure pattern is not the SQL command itself. It is collapsing schema change, backfill, application deployment, and cleanup into one irreversible release.

Zero-downtime migrations depend on compatibility windows

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

That means:

• old code must continue working after the schema expands;
• new code must tolerate partially migrated data where applicable;
• writers need a defined temporary data contract;
• rollback must remain possible after new application versions begin writing;
• destructive cleanup must wait until old code is gone;
• recovery protection must exist before the risky phase starts.

A migration is not safe merely because the SQL statement succeeds in development. Production behavior also depends on table size, write rate, locks, transaction age, storage headroom, replication lag, connection pools, database version, and application deployment ordering.

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

The decision framework separates five migration patterns

Different migrations need different operating models.

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

Choose replication-based or staged data transfer when moving a database between hosts or operating models and the final interruption must be short.

Choose a maintenance window when the complexity of online migration creates more operational risk than a brief planned interruption.

The strongest migration plan is not the one with the least theoretical downtime. It is the one the team can observe, stop, recover, and explain under pressure.

Expand and contract is the safest schema-change default

Suppose a users.full_name field is being replaced by first_name and last_name.

A risky sequence is:

Rename or remove full_name
Deploy code that expects first_name and last_name

Any old application instance still using full_name can fail immediately.

A safer sequence is:

Expand the schema

Add new nullable fields while leaving the old field available.

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

Whether DDL is fast or blocking depends on the engine, version, table, and exact operation. Rehearse the real command on representative data.

Deploy compatible application code

The application can temporarily:

• keep reading the legacy field;
• write both old and new fields;
• prefer new data when present and fall back to old data;
• place the new read path behind a feature flag.

The transition rule must be explicit. Every running application version should know how to behave during the compatibility window.

Backfill historical data

Backfill in bounded, resumable batches rather than one unbounded transaction.

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

Track progress with a durable cursor or idempotent selection rule so the job can stop and resume safely.

Switch reads and writes

After verifying backfill quality:

• move reads to the new representation;
• measure null or mismatch rates;
• confirm no old application version writes only the legacy field;
• keep fallback logic for an observation period.

Contract in a later release

Remove dual writes, old fields, old indexes, and compatibility code only after the new path is stable.

Cleanup is technical debt for a short period, but it is also recovery margin.

Backfills should run as operational workloads

Schema changes and data migration are different workloads. Adding a field may be quick while updating millions of rows can consume CPU, I/O, WAL or binary logs, connections, and replication capacity for hours.

A production backfill should be:

• batched so each transaction is bounded;
• idempotent so retries are safe;
• throttled so application traffic remains healthy;
• observable with progress and failure metrics;
• interruptible without corrupting migration state;
• verifiable by comparing old and new representations.

Monitor at minimum:

• application latency and error rate;
• database CPU and memory pressure;
• storage latency and free capacity;
• WAL or binary-log generation;
• lock waits and transaction age;
• replica lag where replication exists;
• connection saturation;
• backfill throughput and estimated completion time.

A migration that finishes while degrading customer traffic has not succeeded operationally.

Dual writes deserve particular caution. If the old representation succeeds and the new one fails—or the reverse—the team needs an authoritative source, mismatch detection, idempotent retries, and reconciliation logic.

Use dual writes only when the compatibility window requires them.

PostgreSQL and MySQL need engine-specific migration plans

Online migration techniques are not portable abstractions. PostgreSQL and MySQL expose different DDL behavior, locking rules, and operational trade-offs.

PostgreSQL can stage several high-risk changes

Useful PostgreSQL techniques include:

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

For example:

CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders (customer_id);

CREATE INDEX CONCURRENTLY allows normal writes to continue, but it takes longer, cannot run inside a transaction block, can be delayed by old transactions, and can leave an invalid index after failure.

A migration runbook should therefore include inspection of long-running transactions and invalid indexes rather than treating “concurrent” as “zero impact.”

MySQL online DDL uses different algorithms and lock modes

MySQL exposes operation-specific behavior such as:

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

Support varies by operation and MySQL version. Some changes remain online for most of their execution but still require metadata locks or temporary storage. Others rebuild the table or fall back to a different algorithm.

For MySQL migrations:

• check the exact operation against the deployed MySQL version;
• request the intended algorithm and lock mode explicitly where appropriate;
• verify whether the server accepts or falls back from that request;
• monitor temporary space and binary-log growth;
• watch replication or managed-service lag during large changes.

“Online DDL” means reduced blocking, not zero resource impact.

Moving to a managed database adds a data-cutover problem

A migration from self-hosted PostgreSQL or MySQL to a managed service is different from a schema migration because the application may keep the same schema while the system of record moves to a new database endpoint.

Raff currently provides live:

• Managed PostgreSQL 14–16 with backups and point-in-time recovery, monitoring, connection pooling, private connectivity, storage expansion, and optional high availability;
• Managed MySQL 8.0 with managed backups, binary-log-based recovery workflows, monitoring and slow-query visibility, TLS, IP allowlists, private networking, storage expansion, maintenance workflows, and optional high availability.

The Raff database console also provides free managed-database entry points. Current engine eligibility and capacity should be checked in the live console because those limits can change independently of article copy.

A self-hosted-to-managed cutover should separate these stages:

Create target managed database
  ↓
Validate engine/version/extensions and schema compatibility
  ↓
Load baseline data
  ↓
Synchronize ongoing writes where required
  ↓
Measure lag and consistency
  ↓
Pause or redirect writes for final cutover
  ↓
Switch application connection target
  ↓
Validate application behavior
  ↓
Keep source available for rollback window

The exact data-transfer mechanism depends on the engine, database size, required RPO, supported replication methods, and source/target configuration.

Do not assume that moving to a managed service makes the migration provider-owned. The managed platform can operate backups, monitoring, maintenance, and optional HA after cutover, while the application team still owns schema compatibility, credentials, cutover timing, data validation, and application reconnect behavior.

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

Rollback and restore solve different failures

Rollback returns application behavior or traffic to the previous path while preserving acceptable data consistency.

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

A migration rollback plan should answer:

• can old code read data written by the new code?
• which database or representation is authoritative after cutover begins?
• can writes be safely redirected back?
• have irreversible external side effects occurred?
• will reverting the application require reverting the schema?
• how will data written during the failed release be reconciled?

A database backup is required protection, but restore is usually too disruptive to be the primary application rollback mechanism.

Before risky self-hosted changes, Raff Data Protection can provide VM-level backup and snapshot recovery points alongside database-native recovery. For Managed PostgreSQL and MySQL, use the managed backup and recovery controls within the service scope while still validating application behavior after recovery.

Rollback protects the release path. Restore protects the data state. A production migration needs both concepts.

Rehearsal and stop conditions make the migration operable

A useful rehearsal environment should match the production characteristics that affect migration behavior:

• row count and table size;
• indexes and constraints;
• data distribution;
• database engine and version;
• write rate and concurrency;
• connection-pool behavior;
• replication or synchronization method;
• realistic storage headroom.

Measure:

• lock acquisition time;
• total DDL duration;
• backfill throughput;
• temporary disk growth;
• WAL or binary-log growth;
• synchronization lag;
• application latency;
• rollback duration;
• final cutover duration.

Define stop conditions before production work begins. Examples include:

• lock wait exceeds the planned threshold;
• application errors cross an agreed limit;
• database CPU or I/O remains saturated;
• synchronization lag exceeds the cutover target;
• free storage falls below the safety margin;
• mismatch checks fail;
• backfill throughput indicates the window will be missed.

At Raff, we treat a written stop condition as part of the migration design, not as an emergency improvisation. Operators make better decisions when the threshold was agreed before the incident pressure appears.

The migration runbook should separate every irreversible step

A compact production runbook can use three phases.

Before the change

• define the compatibility window;
• identify old and new application versions that may overlap;
• document schema and data stages;
• verify backups, PITR, or restore procedures;
• estimate temporary storage requirements;
• inspect long-running transactions;
• validate target engine/version compatibility;
• rehearse on representative data;
• define stop and rollback conditions;
• assign one cutover decision owner.

During the change

• record start time and migration version;
• monitor locks, transactions, database load, and application latency;
• track backfill or synchronization progress;
• throttle when agreed limits are crossed;
• verify data before switching reads or writes;
• switch endpoints or features deliberately;
• retain the previous path during the observation window.

After the switch

• verify application errors and latency;
• validate key records and business workflows;
• confirm workers, jobs, and reporting paths;
• monitor mismatch, null, fallback, or synchronization metrics;
• retain rollback capability for the agreed period;
• schedule contract cleanup separately;
• remove temporary flags and compatibility code only after the new path is stable.

A controlled maintenance window remains a valid result of this decision process. If the team cannot safely operate dual writes, replication, backfills, and rollback, a short planned interruption can be the more reliable production choice.

:::cluster

Conclusion

Zero-downtime database migration is disciplined sequencing rather than clever SQL. The database, application, data movement, and recovery layers must remain compatible through a controlled transition.

Use expand and contract for most schema changes. Treat backfills as observable workloads. Plan PostgreSQL and MySQL operations against their actual engine behavior. When moving from self-hosted infrastructure to Raff Managed PostgreSQL or MySQL, treat the move as a data cutover with validation and rollback—not simply a connection-string change.

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

Sources

• PostgreSQL: CREATE INDEX
• PostgreSQL: ALTER TABLE
• MySQL 8.4: Online DDL Operations
• MySQL 8.4: Online DDL Performance and Concurrency
• Raff Managed PostgreSQL
• Raff Managed MySQL
• Raff Data Protection
• Product details reviewed: 2026-08-11