Changing a live database: online indexes and schema changes

The problem

Tables grow, queries change, and sooner or later you must add an index or change a column on a table that is serving live traffic. A naive change can lock the table for minutes or hours. For a 2 billion row table that is an outage.

The goal of an online change: keep reads and writes working, limit extra load, and be able to back out.

Pattern 1: build the index concurrently

Some databases can build an index while writes continue. PostgreSQL's CREATE INDEX CONCURRENTLY scans the table twice and waits for existing transactions, so it does not block writes.

Trade-offs: it takes longer and uses more I/O; it cannot run inside a transaction block; if it fails it can leave an invalid index that you must drop before retrying. Watch for long-running transactions that make it wait.

Pattern 2: shadow table plus change capture

For schema changes that rewrite the table, many teams use a copy approach, used by tools such as gh-ost and pt-online-schema-change:

1. Create a shadow table with the new structure.
2. Backfill by copying rows in small chunks.
3. Meanwhile capture every change to the original (from the replication log or with triggers) and apply it to the shadow table.
4. When the copy has caught up, cut over: a brief swap that renames the shadow table into place.
5. Keep the old table for a while for rollback.

100%

Figure SDA-SQR-02-1 · Online schema change with a shadow table · © forcsguy.com

Safety checklist

• Throttle by replica lag, CPU and connection counts; the migration must back off when production suffers.
• Test on a copy of production-sized data first; time and I/O surprise people.
• Verify with row counts and sampled checksums before cut-over.
• Plan the cut-over window for low traffic; make it short and reversible.
• Keep the old table for rollback until you are confident.
• Watch replicas: large changes can cause replication lag, which in turn breaks read-after-write assumptions.

Backward-compatible steps (expand and contract)

Schema and code must change in an order that never breaks either:

1. Expand: add the new column or index; old code still works.
2. Deploy code that writes both shapes and reads the new one when available.
3. Backfill old rows in batches.
4. Switch reads fully to the new shape.
5. Contract: remove the old column in a later release, once nothing reads it.

100%

Figure SDA-SQR-02-2 · Expand and contract · © forcsguy.com

Real-world reading