Database Migrations at Scale Zero Downtime

By Khimananda Oli | Last reviewed: August 2026

Running database migrations at scale zero downtime requires decoupling schema changes from application deployments so neither blocks the other during peak traffic. Most outages I investigate stem from teams running blocking ALTER TABLE statements directly against production primaries or assuming their ORM handles concurrency safely. The solution is adopting an expand-contract workflow combined with online schema change tools that keep tables available while restructuring data in the background.

How do you implement the expand-contract pattern for database migrations at scale zero downtime?

The expand-contract pattern (also called parallel change) is the foundational technique for safe schema evolution in high-traffic environments. Instead of modifying a column in place—which locks the table and breaks running code—you introduce the new structure alongside the old one, migrate data gradually, and only remove the legacy structure after full verification. This approach ensures your application remains functional throughout every phase of the migration.

Step-by-step expand phase implementation

The expand phase must be completely non-blocking. In PostgreSQL, this means avoiding NOT NULL constraints without defaults and avoiding volatile default expressions that force a full table rewrite. For PostgreSQL administration essentials, always add columns as nullable first:

-- SAFE: Adds column instantly, no table lock beyond catalog update
ALTER TABLE orders ADD COLUMN total_cents BIGINT;

-- UNSAFE on large tables: Forces full rewrite in PG <11, still acquires AccessExclusiveLock
ALTER TABLE orders ADD COLUMN total_cents BIGINT NOT NULL DEFAULT 0;

In MySQL 8.0+, adding a nullable column is an instant metadata-only operation. Adding a NOT NULL DEFAULT column is also instant for most types, but adding a foreign key constraint or modifying an indexed column still requires a table rebuild. Always check INFORMATION_SCHEMA.INNODB_TRX before running DDL to ensure no long-running transactions will block your lock acquisition.

Dual-write application logic

Your application must write to both the old and new columns during the transition period. This is typically handled at the model or repository layer. The read path continues using the old column until backfill completes and verification passes. Here is a practical pattern:

• On INSERT/UPDATE: Set both total (old decimal) and total_cents (new integer)
• On READ: Continue selecting total; ignore total_cents until cutover
• Add a feature flag to toggle read source without redeployment
• Log discrepancies between old and new values during dual-write for audit

This dual-write period typically lasts days to weeks depending on table size and traffic. Do not rush to the next phase. I have seen teams skip verification and discover silent data corruption months later during compliance audits.

Which online schema change tools prevent locking during database migrations at scale zero downtime?

Even with expand-contract, some operations cannot be decomposed into safe additive steps. Renaming columns, changing primary keys, or adding indexes to massive tables require specialized tools that perform the work without holding exclusive locks. These tools create shadow tables, copy data incrementally, and swap atomically.

Using gh-ost for triggerless MySQL migrations

gh-ost is my default choice for MySQL tables over 50GB because it avoids triggers entirely. It reads the binary log to capture changes during copy, which eliminates the write amplification that plagues pt-osc under heavy load. A typical invocation for adding an index without downtime:

gh-ost \
  --host="db-primary.internal" \
  --database="ecommerce" \
  --table="orders" \
  --alter="ADD INDEX idx_created_at (created_at)" \
  --allow-on-master \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=100 \
  --chunk-size=1000 \
  --execute

The --max-load and --critical-load flags are non-negotiable in production. They throttle or pause the migration when the primary is busy, ensuring customer-facing queries never starve. Always test these thresholds in staging with realistic write patterns before production execution.

PostgreSQL concurrent index creation

For PostgreSQL, many operations now have native concurrent alternatives that eliminate the need for external tools. Creating an index without blocking writes has been stable since version 9.6:

-- Does NOT block INSERT/UPDATE/DELETE during build
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

-- Validate partial or corrupted indexes before making visible
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Note that CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Your migration framework must handle this as a separate statement outside the usual transaction wrapper. Tools like Flyway and Liquibase have specific configurations for this; failing to set them results in silent fallback to blocking mode.

How do you safely backfill data during database migrations at scale zero downtime?

Backfilling is where most migrations fail silently. Copying millions of rows in a single transaction exhausts undo logs, bloats replication lag, and risks hours of lost work if interrupted. Safe backfilling requires batching, idempotency, and continuous verification.

Keyset pagination over OFFSET

Never use OFFSET for backfill queries on tables exceeding 100K rows. Offset performance degrades quadratically because the database must scan and discard all preceding rows. Keyset pagination using the primary key is consistently O(1):

-- BAD: Slows to crawl after 1M rows
UPDATE orders SET total_cents = (total * 100)::BIGINT 
WHERE id IN (SELECT id FROM orders ORDER BY id LIMIT 1000 OFFSET 5000000);

-- GOOD: Constant time regardless of position
UPDATE orders SET total_cents = (total * 100)::BIGINT
WHERE id > :last_processed_id
ORDER BY id ASC
LIMIT 1000;

Store :last_processed_id in a dedicated tracking table or Redis key. This makes the backfill resumable after crashes, network partitions, or planned maintenance windows. Without checkpoints, a failure at row 9 million of 10 million means restarting from zero.

Throttling and replication awareness

Backfill jobs must be polite tenants. Unthrottled updates saturate disk I/O, inflate WAL/binlog volume, and cause replication lag that breaks read replicas and CDC pipelines. Implement adaptive throttling:

1. Measure batch execution time; target under 500ms per batch
2. Query pg_stat_replication.lag (PostgreSQL) or SHOW REPLICA STATUS (MySQL) before each batch
3. If lag exceeds threshold (typically 2-5 seconds), sleep exponentially up to 30 seconds
4. Reduce batch size dynamically if latency spikes persist
5. Emit metrics to Prometheus/Grafana for real-time visibility during execution

For teams managing MySQL performance tuning, remember that bulk updates generate significant redo log volume. Ensure your innodb_log_file_size accommodates sustained backfill throughput without frequent checkpoints that stall writes.

How do you verify integrity and roll back database migrations at scale zero downtime safely?

Verification is not optional—it is the gate between dual-write and contract phases. Skipping it turns migrations into gambling. Every backfill must produce cryptographic or statistical proof that source and target data match before any code stops reading the old column.

Checksum and count validation

Run independent verification queries outside the backfill job. Compare row counts first as a fast sanity check, then sample checksums for deeper assurance:

-- Fast count comparison
SELECT 
  COUNT(*) AS total_rows,
  COUNT(total_cents) AS populated_rows,
  COUNT(*) - COUNT(total_cents) AS null_count
FROM orders;

-- Sampled checksum for 1M+ row tables (full hash too expensive)
SELECT 
  COUNT(*) AS sample_size,
  SUM(hashtext(total::text || '|' || total_cents::text)) AS checksum
FROM orders
WHERE id % 100 = 0;  -- 1% sample, deterministic selection

If null_count is non-zero after backfill completion, your dual-write logic has gaps. Investigate application logs for failed writes during the transition window before proceeding to contract.

Safe rollback strategy

Rollback capability must exist at every phase. During expand, dropping the new column is safe because no code reads it. During dual-write, reverting the application deployment restores single-write behavior. During contract, you cannot simply re-add the dropped column—data is gone. This is why contract happens in a subsequent release, only after extended monitoring confirms stability.

Maintain a rollback runbook for each migration phase. For teams following blue-green and canary deployment strategies, tie migration phase transitions to deployment pipeline gates rather than manual coordination. Automated verification checks in CI prevent premature promotion.

Conclusion

Executing database migrations at scale zero downtime is a discipline, not a feature. It demands treating schema changes as multi-phase deployments with their own testing, monitoring, and rollback procedures. The expand-contract pattern, combined with appropriate online schema change tools and rigorous backfill verification, eliminates the false choice between progress and availability. Start small: practice the workflow on non-critical tables before attempting it on your core transactional data. If your team needs help designing migration strategies for high-traffic PostgreSQL or MySQL systems, reach out to discuss your specific architecture.