Schema and Index Migrations Without Downtime

The Rename That Cannot Be One Statement

The revenue system has enough capacity for its correction replay, but the next change appears much smaller. A payment row contains customer_id. The business now calls the same identity an account, and the service, workers, exports, and reconciliation queries must move to account_id. The new reconciliation read also needs an index on account and settlement date.

A direct column rename is logically tidy. In production it creates a moment when one name disappears and another appears. An old worker can still be writing customer_id while a new web process expects account_id. A rollback can put old code beside the new schema. A support export can depend on the old column without appearing in the service repository. The index build can compete with the correction replay for the I/O headroom established in the capacity test.

The migration therefore cannot be one statement. It is a period of controlled coexistence:

1. add the new structure without requiring it;
2. teach every live writer and reader to tolerate both structures;
3. move old rows in bounded work;
4. prove that both names still describe the same account;
5. shift serving behavior;
6. remove the old structure only after it has stopped carrying evidence or rollback value.

This is the expand-contract pattern. Its purpose is not ceremony. It turns one irreversible change into several observable decisions, each small enough to stop.

First, Prove the Coexistence States

Before changing the database, the team writes down the combinations it is about to create. The current application with the current schema is known production. The current application with the expanded schema must ignore account_id safely. The bridge release must tolerate the schema before and after expansion so that code rollback remains possible. The final application can require account_id only after the backfill, cutover, and rollback window are complete.

Those combinations are executable tests, not release prose. Start the old binary against the expanded schema. Start the bridge binary against the current schema if the deployment order permits it. Exercise web requests, background workers, scheduled jobs, CDC serialization, exports, and administrative queries. If the bridge binary cannot run against the current schema, the deployment needs a hard schema-first gate and a rollback plan that does not pretend otherwise.

Compatibility also has a semantic side. During expansion, null account_id means “not migrated yet,” not “this payment has no account.” A default value would erase that distinction. The bridge writes both identifiers from the same authoritative account value, keeps reads on customer_id, and records any attempted write in which the two differ.

Database engines and versions differ in how they lock, rewrite, or validate a change. The team tests the exact operation against production-shaped data and concurrent work. It records the lock mode and expected wait, whether existing rows are rewritten, temporary-space and log growth, cancellation behavior, and what remains after failure. “Online” or “instant” is a claim to verify for this operation, not a property to infer from syntax.

Expand Without Changing the Answer

The first production step adds nullable account_id. No request reads it yet. If the DDL waits longer than its small lock budget, the operator cancels it rather than allowing an unbounded queue of blocked work. A successful statement is followed by a schema inspection and a live latency check; execution success alone does not show what other sessions experienced.

The bridge release comes next. It writes customer_id and account_id in the same transaction whenever it creates or changes a payment. Reads continue to use customer_id. That order matters: the new path begins collecting evidence while the old path remains authoritative.

Dual write is deliberately temporary. It adds code, write work, and a new divergence mode. The bridge therefore exposes three signals: writes where only one field was populated, writes where the values differ, and records changed by an old writer after the bridge rollout. A clean application deploy is not enough if a month-end job or previous worker image still writes only the old shape.

Backfill as a Live Workload

Now the old rows need account_id. The backfill is not hidden maintenance; it is a new write workload competing with requests, replication, backups, index maintenance, and the revenue replay.

The team chunks by tenant and primary-key range. Tenant boundaries make customer impact visible; primary-key ranges make progress and retry precise. Each chunk updates only rows whose account_id is still null, derives it from the authoritative customer_id, and commits independently. Running a chunk twice produces the same state. A worker that crashes after commit can repeat the range without incrementing, duplicating, or overwriting a newer bridge write.

Progress is recorded durably by migration version, tenant, range, rows examined, rows changed, retry count, comparison result, start and finish time, and last error. This record answers “which facts have moved?” after a restart. A percentage cannot.

The throttle listens to the resource that live traffic needs. Lock waits, deadlocks, replica lag, p95 and p99 latency, error rate, log growth, disk headroom, and operator command can all slow or pause new chunks. The limits come from the capacity evidence, not from a convenient batch size. If stopping requires killing an unknown process and reconstructing its last range from logs, the backfill is not ready to run.

Verification Must Recognize the Same Fact

“No nulls remain” is necessary, but it does not prove the rename preserved meaning. The team verifies at several distances.

At the row level, it compares customer_id and account_id by tenant and range. At the business level, it checks that payments still group to the same account, settlement totals remain unchanged, and no payment crosses a tenant boundary. At the serving level, shadow reads execute the future account-based query while users still receive the old result. Mismatches retain the payment key, tenant, old value, new value, writer version, and last update time so that an operator can investigate rather than merely watch a counter rise.

The sample is biased toward records likely to expose a bad assumption: recently updated payments, the oldest retained rows, manually repaired records, null-heavy imports, large tenants, rare statuses, and rows touched while the backfill was active. Random sampling alone would favor ordinary data.

Verification continues while the bridge is writing. A clean backfill followed by one old worker writing only customer_id is not a clean migration. The gate stays closed until the backfill is complete, divergence remains at zero for the agreed observation window, shadow reads agree, business invariants hold, and every known writer is on a compatible version.

The Index Has Its Own Migration

The account-and-settlement-date index supports the new reconciliation read, but it does not ride for free with the column migration. Building it consumes CPU, I/O, temporary space, log bandwidth, and replica capacity. An online or concurrent build may reduce blocking while still exhausting the margin that keeps live reads healthy.

The index plan names the exact build mode for the database version, expected duration and space, lock behavior at start and finish, cancellation and retry behavior, and the signals that pause competing work. The team does not overlap the build with the revenue replay simply because both fit when tested alone.

After the build, existence is the first check. Critical query plans are compared before and after, using representative account sizes and settlement ranges. The new access path can improve its target query while changing planner choices elsewhere. Query latency, write cost, cache behavior, and replica lag remain under observation after statistics change and real traffic reaches the index.

If the new index supersedes an existing one, removing the old index is another compatibility decision. Query logs and plan samples must show that old code, reports, and emergency scripts no longer depend on it. If the platform supports a reversible unused or invisible state, that observation window is safer than immediate removal. Otherwise the rollback window and evidence bar must carry the risk.

Cut Over, Then Earn Cleanup

Cutover begins by making reads prefer account_id while retaining a fallback to customer_id. The change rolls out to a small scope, then to larger tenants, workers, exports, and reconciliation jobs. Correctness and latency are watched together. A fast result can still attach a payment to the wrong account.

Once all readers use the new shape and the observation window is clean, the service stops writing customer_id. The old column remains present while logs, dependency searches, and database access evidence look for a forgotten consumer. Only then may the team tighten a non-null constraint on account_id, using a separately tested validation path with its own lock and load budget.

Cleanup removes the fallback read, dual-write code, old column, any superseded index, temporary progress records subject to retention policy, migration-only dashboards, and alerts. These removals need not happen in one release. Old structure is cheap compared with destroying the last working rollback path.

Rollback has several meanings along this timeline. Before cutover, the team can pause backfill and keep serving the old column. During the fallback window, it can route reads back without discarding new writes. After the old column is dropped, code rollback no longer restores the old data path. The closer the migration moves to cleanup, the more its safety comes from accumulated evidence rather than reversibility.

The Operator Record

The migration runbook should make one person’s next decision obvious. For this change, the compact record reads:

Goal and invariant:
  Rename customer_id to account_id without changing payment ownership.
  Every payment remains attached to the same account and tenant.

Compatibility:
  Old code ignores nullable account_id. The bridge release tolerates both
  schemas, writes both fields in one transaction, and reads customer_id.
  The final release does not require account_id until the gate is clean.

Expand:
  Test the exact DDL lock, rewrite, log, space, cancellation, and failure
  behavior. Add nullable account_id inside the lock budget.

Backfill:
  Process tenant and primary-key chunks idempotently. Record durable range
  progress. Throttle on live latency, locks, replica lag, errors, and disk.

Index:
  Build the account-and-settlement-date index in the verified online mode.
  Reserve capacity, compare critical plans, and retain any superseded index
  through its observation window.

Verification gate:
  No unmigrated rows; old and new identifiers agree; payment totals and tenant
  ownership hold; shadow reads match; every writer is compatible; the
  observation window has no divergence.

Cutover and rollback:
  Prefer account_id for a small scope, keep customer_id fallback, then expand.
  Route reads back and pause work on any correctness or production-budget
  breach.

Cleanup:
  Stop old writes, observe for hidden consumers, validate the new constraint,
  then remove old code, column, index, and migration controls separately.

Authority:
  One named operator may pause chunks, cancel the index build, halt rollout,
  route reads back, or abandon cleanup.

The actual runbook supplies measured thresholds, commands, owners, expected durations, verification queries, and links to dashboards. Its job is not to certify that planning occurred. It binds each action to evidence, a stop condition, and an available response.

Rehearse the Moment That Resists the Plan

Design a required-field migration for one large, frequently written table. Write the coexistence tests for old and bridge application versions, choose the temporary unknown state, and identify the exact operation whose lock or rewrite behavior must be verified. Then specify the chunk key, idempotency rule, durable progress record, verification queries, cutover gate, rollback window, and cleanup evidence.

Add one index required by the new read path. Predict its resource cost, name the query plans that might change, and decide which concurrent job will be excluded from the build window.

Finally, introduce an old worker that writes one row after the backfill has passed. The plan is ready only when it detects that write before cutover, preserves enough evidence to identify the writer, and gives the operator a safe next action.

This migration changed a schema and its access path while the source of truth stayed in place. Moving facts to another store, shard, region, or model adds a snapshot boundary, change capture, routing, reconciliation, and decommissioning. That is a larger control problem, and it begins in the next chapter.