Distributed Schema Migrations

In this lesson, you will learn how to execute non-breaking schema changes across distributed systems using the "Expand-Contract" pattern via ghost tables and dual-writes.

When working with large-scale distributed databases, a simple ALTER TABLE command can lock shards for long periods, causing downtime or cascading timeouts. To avoid this, we perform migrations in stages to ensure the application remains functional throughout the transition.

The Ghost Table Strategy

Rather than modifying a live table directly, we create a "ghost" table (a shadow copy) that mirrors the desired end-state schema. While the application continues to write to the original table, a background process or a trigger-based replication mechanism copies the data into the ghost table.

The Dual-Write Strategy

To keep the ghost table in sync with real-time traffic, the application implements Dual-Writes. During the migration window:

1. The application writes to the Source Table.
2. The application simultaneously writes to the Ghost Table.

-- Application logic during migration
BEGIN;
  INSERT INTO orders_v1 (id, status) VALUES (101, 'shipped'); -- Source
  INSERT INTO orders_v2 (id, status, migrated_at) VALUES (101, 'shipped', NOW()); -- Ghost
COMMIT;

The Transition Phase

Once the ghost table has caught up on historical data (often via a batch backfill) and is successfully receiving dual-writes, we perform a "cutover." This involves updating the application configuration to read from the ghost table.

Because we kept both tables in sync, the transition is near-instantaneous. If a bug appears, we can roll back by simply pointing the application back to the original table. Once the system is stable, we drop the old table. This approach prevents schema locks, maintains high availability, and allows for thorough verification before fully committing to the new structure.

By treating the schema change as a multi-step deployment rather than a single atomic operation, you decouple the database structural change from the application deployment cycle, effectively mitigating the risks inherent in large-scale distributed architectures.

Key Takeaway: Use ghost tables and dual-writes to perform schema migrations as a multi-step background process, ensuring zero-downtime and easy rollback paths.