Backfills, Validation, and Zero-Downtime Changes

Learning outcomes

A schema change that is trivial on 10,000 rows may be dangerous on 5 billion. Large migrations must control locks, transaction size, replication lag, I/O, write amplification, and failure recovery. This lesson turns migration into an operational workflow.

Why giant updates are risky

UPDATE work_orderSET priority_code = 'normal'WHERE priority_code IS NULL;

On hundreds of millions of rows, one transaction may create huge logs, long locks, replication lag, cache churn, and difficult rollback.

Batch backfill

UPDATE work_orderSET priority_code = 'normal'WHERE work_order_id > :last_id  AND work_order_id <= :next_id  AND priority_code IS NULL;

Commit each bounded batch.

Batch by stable key

Prefer a stable indexed key such as primary key or partition key. OFFSET-based batching can become slow and unstable on changing data.

Throttle

Backfill worker can pause between batches based on:

• primary CPU;
• I/O latency;
• replication lag;
• lock waits;
• application p95 latency.

Pause and resume

Store progress:

migration_namelast_processed_idrows_updatedstarted_atupdated_atstatus

A production backfill should survive process restart.

Idempotent backfills

Write conditions so rerunning is safe:

WHERE new_column IS NULL

or compare source version/checkpoint before applying.

Backfill while live writes continue

New application code should start writing the new field before or during the backfill. Otherwise the backfill may never catch up because new rows continue arriving without the field.

Validation query

SELECT COUNT(*)FROM work_orderWHERE service_region_id IS NULL;

For huge tables, full scans may be expensive; use indexed predicates, partitions, samples, or database validation features where appropriate.

Checksums and reconciliation

For transformed columns:

old_price_centsnew_price_decimal

validate deterministic equivalence across partitions or samples before dropping the old representation.

Online index creation

Many DBMSs provide a concurrent/online index-build mode that reduces blocking. It may take longer and use more resources, but is often safer for live systems.

Build index before constraint

For large uniqueness changes, you may be able to build a unique index online first, then attach a constraint using the existing index. Exact capabilities vary by DBMS.

Foreign-key validation in phases

A large FK can be introduced by:

1. ensuring child-side index;
2. cleaning orphan rows;
3. adding constraint in non-blocking/unvalidated form if supported;
4. validating existing data separately.

NOT NULL migration

Safe sequence:

1. add nullable column;
2. deploy writers;
3. backfill;
4. validate zero NULLs;
5. add NOT NULL.

Default rewrite hazards

Some DBMS/version combinations rewrite entire tables when adding columns with defaults; modern engines may optimize this. Know the exact behavior of your DBMS before running DDL on large tables.

Lock-time budget

Even “online” DDL may need brief metadata locks at start or end. Use lock timeouts so a migration fails quickly rather than blocking production unexpectedly.

Replication considerations

Large backfills generate replication traffic. Monitor replicas because a migration that is safe on the primary can make replicas hours behind.

Partitioned migrations

Large partitioned tables can sometimes be migrated one partition at a time, reducing blast radius and allowing incremental validation.

Zero downtime means compatible states

Zero downtime does not mean “the migration takes zero time.” It means application availability continues while the system passes through compatible intermediate states.

Observability

WorkshopHub example

Goal:add tenant_id to 400M PartUsage rows1. add nullable tenant_id2. deploy new writes with tenant_id3. create supporting index online4. backfill in PK ranges5. monitor replication/latency6. reconcile NULL count + ownership mapping7. validate FK to Tenant8. add NOT NULL9. update composite uniqueness/indexes10. remove compatibility code

Practice: backfill failure

Summary and next lesson

Large migrations are operational systems: they need batching, throttling, progress, validation, observability, and safe online DDL. The final lesson covers versioning these changes in Git and planning rollback or roll-forward when migrations fail.

References

• Database vendor documentation for online/concurrent DDL.
• Pramod Sadalage and Scott Ambler, Refactoring Databases.
• Martin Kleppmann, Designing Data-Intensive Applications.