Skill · Database
Safe Data Backfill Planner
Plan and implement a resumable production data backfill with bounded batches, checkpoints, rate limits, correctness verification, observability, and a pause or rollback path. Use when populating a new column, repairing historical rows, re-keying records, migrating derived data, or updating millions of records without overwhelming the database or replicas.
User-invocablev1.0.0
Updated Aug 4, 2026npx agentscamp add skills/safe-data-backfill-plannerInstall to ~/.claude/skills/safe-data-backfill-planner/SKILL.md
Plans a large data backfill as an observable, resumable job rather than one unbounded UPDATE. It chooses a stable cursor, processes committed batches with rate and lock limits, records checkpoints, handles live-write races, verifies counts and sampled values, monitors replicas and application latency, and defines pause, retry, and cleanup behavior.
Design the backfill as a controlled production workload. Keep schema changes, application compatibility, and the data job as separate deployable phases.
Workflow
- Define source, target, and invariant. State how the target value is derived, which rows qualify, how live writes behave during the job, and what must be true when complete.
- Establish rollout order. Add the target shape first, deploy code that can read both shapes and writes the new shape, then backfill. Contract the old shape only after verification and an observation window.
- Choose a stable cursor. Prefer an indexed immutable key such as primary key or creation ID. Use keyset pagination (
WHERE id > checkpoint ORDER BY id LIMIT n), not offset pagination on a changing table. - Make each batch bounded. Limit rows, transaction time, lock wait, statement time, and concurrency. Commit after each batch. Add a small delay or adaptive rate control based on database load and replica lag.
- Make it resumable and idempotent. Persist the high-water mark and counters. Reprocessing a batch must be safe. Update only eligible rows with a predicate such as
target IS NULLor an expected version. - Protect live writes. Use compare-and-set conditions, version checks, or dual-write ordering so a batch computed from stale data cannot overwrite a newer application value.
- Instrument progress and impact. Record scanned, updated, skipped, failed, retry, and remaining estimates; batch duration; lock waits; database CPU; WAL; replica lag; and affected application latency.
- Verify independently. Compare eligible and completed counts, run invariant queries, sample records across the key range, and use checksums or aggregates when appropriate. Do not trust the job's own success counter alone.
- Define pause and recovery. State thresholds that stop the job, how to resume from the checkpoint, how poison rows are quarantined, and whether correction means reverse transformation, restore, or forward repair.
- Clean up deliberately. Remove dual-read or dual-write compatibility and old columns only after completion, verification, and a rollback-safe observation period.
WARNING
Never use OFFSET as the progress cursor for a large changing dataset. Deletes, inserts, and updates can cause skipped or duplicated rows, and later pages become increasingly expensive.
Output
Return:
- the expand/backfill/contract deployment sequence
- the job or migration code with stable cursor, batch transaction, checkpoint, and idempotent predicate
- batch size, concurrency, timeouts, and adaptive throttle rules
- metrics, dashboard queries, and automatic pause thresholds
- correctness queries and sampling plan
- crash recovery, poison-row handling, and rollback or forward-repair strategy
- completion and cleanup criteria
Frequently asked questions
- Why not run one SQL UPDATE for a backfill?
- One large transaction holds locks and old row versions, generates a burst of WAL, increases replica lag, is hard to pause, and rolls back all progress on failure. Bounded committed batches limit blast radius and make progress resumable.
- How should a backfill avoid overwriting live writes?
- Make the application write the new shape before the backfill, then update only rows still missing or matching the old expected value. Use compare-and-set conditions or version columns so a stale batch cannot overwrite newer application data.
Related
- 8 Best Claude Skills for Database WorkCompare Claude skills for safe migrations, data backfills, indexes, query plans, pooling, deadlocks, vector search, and SQL tuning.
- Migration WriterWrite a safe, reversible, zero-downtime database migration using expand-contract — add the new shape, backfill in batches, switch reads/writes, then drop the old — so every deploy stays compatible with the running app version. Use when adding or changing schema on a live system, renaming/dropping a column, adding NOT NULL or a foreign key on a large table, or when a migration risks locks, table rewrites, or an unrevertable step.
- Postgres Index StrategistRecommend the right Postgres index for a query or workload — choosing B-Tree vs. GIN vs. BRIN vs. partial/covering/expression, checking for redundant or unused indexes, and verifying the choice against the query plan. Use when a query needs an index, when deciding an index type for jsonb/array/full-text/time-series data, or when auditing an over-indexed table.
- Connection Pool TunerSize and tune a database connection pool from the real constraint — the database's shared max_connections and its core count — so total connections (per-instance pool × instance count) stay safely under the cap and a too-large pool stops adding latency. Use when the app throws 'too many connections' or pool-acquire timeouts, when the DB is saturated by connection count, or when deploying to serverless.
- Dashboard DesignerDesign a service dashboard that answers one question at a glance — is the service healthy, and if not, where's the problem? — by structuring panels around RED/USE instead of dumping every metric. Use when a service has no dashboard, when the existing one is an unreadable metric wall, or during incident-readiness prep.
- Deadlock DiagnoserDiagnose a database deadlock from the engine's own deadlock report, reconstruct the lock cycle (A holds 1 wants 2, B holds 2 wants 1), name the root cause — almost always two code paths locking the same rows in different orders — and fix it with consistent lock ordering, shorter transactions, and a retry-the-victim safeguard. Use when the DB logs deadlock errors, when transactions intermittently fail under load, or when queries mysteriously block each other.