Safe Database Migrations on Terabyte-Scale Tables

Running schema migrations on terabyte-scale databases introduces severe risks of table locks, replication lag, and application downtime. To safely modify large datasets without impacting production traffic, web development teams rely on specialized architectural strategies, including the expand-contract pattern, ghost-table tooling, chunked data backfills, and non-blocking native engine capabilities. This article breaks down these core techniques to provide a practical guide for executing zero-downtime database updates at scale.

The Expand and Contract Pattern

The expand and contract pattern (also known as parallel run) decouples database changes from application releases into phased, non-breaking steps:

  1. Expand: Add new columns or tables alongside the old schema without modifying or dropping existing structures. New columns must be nullable or possess default values so existing queries do not fail.
  2. Dual-Write: Update the application to write to both the old and new locations simultaneously, while still reading exclusively from the old schema.
  3. Backfill: Populate the new columns or tables with historical data using background workers.
  4. Switch Reads: Update the application to read from the new schema.
  5. Contract: Stop writing to the old schema and drop the deprecated columns or tables in a subsequent release.

This approach eliminates the need for all-or-nothing cutovers and provides a seamless fallback mechanism at any stage.

Online Schema Change (OSC) Tools

Standard DDL (Data Definition Language) queries often lock tables, making direct ALTER TABLE commands hazardous on large datasets. Online Schema Change tools circumvent locks by operating on auxiliary tables:

  • Shadow Table Creation: The tool creates an empty "ghost" or "shadow" table with the desired target schema.
  • Continuous Synchronization: The tool streams live data modifications from the active table to the ghost table. Tools like Percona’s pt-online-schema-change use database triggers, whereas GitHub’s gh-ost reads the binary log directly to reduce database overhead.
  • Bulk Copy: Historical data is copied in small, configurable batches from the original table to the ghost table.
  • Atomic Swap: Once the ghost table is fully caught up, an atomic metadata lock swaps the names of the two tables in milliseconds, instantly applying the new schema.

Throttled Batch Processing for Historical Data

Backfilling historical data on massive tables in a single transaction will exhaust connection pools, overwhelm undo logs, and inflate replication lag.

Safe migrations divide data modifications into discrete, indexed ranges:

  • Indexed Iteration: Batch updates iterate strictly over primary keys or unique clustered indexes (e.g., WHERE id >= ? AND id < ?) rather than utilizing expensive offset scans.
  • Adaptive Batch Sizing: Batch sizes adapt dynamically based on execution time and server load, typically processing between 1,000 and 10,000 rows at a time.
  • Throttling and Sleep Intervals: Inserting short sleep intervals (e.g., 50–200 milliseconds) between batches gives read replicas time to process changes and reduces I/O pressure.
  • Lag Monitoring: Automation scripts monitor replication lag and CPU utilization, automatically pausing the backfill if lag crosses predetermined thresholds.

Native Non-Blocking Operations

Modern database engines offer native capabilities designed to minimize downtime for common operations:

  • PostgreSQL: Creating indexes using CREATE INDEX CONCURRENTLY avoids locking the table against writes. PostgreSQL builds the index across two scans without acquiring an ACCESS EXCLUSIVE lock. Adding nullable columns or columns with constant defaults in recent PostgreSQL versions modifies metadata only, executing near-instantly.
  • MySQL: InnoDB’s Online DDL engine supports ALGORITHM=INPLACE and ALGORITHM=INSTANT for operations like column additions, renaming, and index operations, allowing concurrent DML (SELECT, INSERT, UPDATE, DELETE) during the migration process.

Teams should explicitly define these native algorithms in migration scripts to fail fast if an operation requires an unexpected exclusive lock.

Deployment Gating and Rollback Readiness

Safely managing terabyte-scale migrations requires structural discipline across the entire delivery pipeline:

  • Decoupled Deployments: Database migrations are never bundled directly with application deployments; they execute as independent pipeline steps.
  • Feature Flags: Application logic transitions—such as dual-writing and swapping read paths—are wrapped in dynamic feature flags, allowing teams to reverse traffic instantly without rolling back deployments.
  • Safe Rollbacks: Because dropping a large table or column can lock an I/O subsystem, deletions should use hard links or deferred drops rather than immediate, blocking deletion commands.