Backfill a Large Table Safely
Backfill data in small idempotent batches, track a watermark, throttle writes, verify coverage, and avoid long locks or replication lag.
Objective
Populate a new column or table for existing rows without blocking production traffic or overloading replicas.
Environment
- A database with an indexed primary key or stable ordering column
- A way to pause and resume the job
- Monitoring for locks, replication lag, and database CPU
- A dry-run or count query for the affected rows
Steps
1. Estimate the work
Count affected rows, measure a small batch, and estimate total duration and write volume.
Expected state: The backfill plan has a realistic duration and load estimate.
2. Write an idempotent batch job
Select a bounded set by primary key, update only rows that still need the new value, and record the last processed key.
Expected state: Re-running a batch does not double-apply changes.
3. Throttle and monitor
Add a delay between batches, watch replication lag and lock waits, and pause automatically if thresholds are exceeded.
Expected state: Production latency and replica lag remain within budget.
4. Verify coverage
Compare counts of rows missing the new value before and after, and spot-check transformed values.
Expected state: No eligible row remains unbackfilled.
5. Clean up
Remove the temporary progress table or job flags, and keep the backfill script for audit or re-runs.
Expected state: The job is stopped and the schema is ready for the next migration phase.
Decision points
Should the backfill run online or in a maintenance window?
- Online with throttling
- In a maintenance window
Prefer online batches; use a window only when the transformation cannot be made idempotent or low-impact.
What is the batch size?
- A fixed large batch for speed
- A small batch sized to the latency budget
Start small and increase only after measuring lock time and replication lag.
Expected outcome
Existing rows have the new value, production stayed within its latency budget, and the job can be safely re-run.
Verification
Run count queries for missing values, compare a sample against the transformation rule, and review replica lag and lock wait metrics for the job window.
Cleanup
Archive the batch log and counts, remove temporary indexes or tables, and document the final watermark.
Small batches, stable progress
A single UPDATE over millions of rows holds locks, generates a large transaction, and can overwhelm replicas. A backfill processes a bounded range, commits, records the watermark, and continues. The job is restartable and idempotent: rows already backfilled are skipped.
UPDATE orders
SET shipping_zone = 'standard'
WHERE id IN (
SELECT id FROM orders
WHERE shipping_zone IS NULL
ORDER BY id
LIMIT 1000
)
RETURNING id;Throttle to the system’s budget
A backfill is a background workload competing with users. Measure lock wait time, database CPU, and replication lag, and pause when a threshold is exceeded. A delay between batches is cheaper than an outage. Keep the batch size configurable and start smaller than you think necessary.
Verify coverage
Count rows that still need the value and compare before and after. Spot-check a sample of transformed rows against the rule. If the transformation is not idempotent, fix it before running at scale; re-running a partial batch should not corrupt data. Keep the progress table until the verification is complete.