agent parallelized the data backfill across workers but they all hammered the same hot partition - throughput collapsed
Fixes parallel backfills that slow down as workers are added because every worker hits the same hot partition. Use it when per-worker throughput collapses under parallelism. Key trigger: all workers waiting on locks or IO for one partition.
TL;DR
Repartition the work so workers stop overlapping. Shard the backfill by a high-cardinality, evenly distributed key such as primary-key ranges, with one worker per range, instead of letting every worker scan the same partition. All workers hitting one partition serialize on the same locks and pages, so adding workers made it slower, not faster.
agent parallelized the data backfill across workers but they all hammered the same hot partition - throughput collapsedSteps
Confirm the skew. Check per-worker progress logs, or look at wait events for the workers in the database's activity view. Expected: all workers blocked on locks or IO for the same partition or the same set of pages.
Stop the workers. If rows were written idempotently (upsert keyed on the primary key), the completed rows can stay; otherwise note the checkpoint so the re-run skips them. Expected: workers stopped, no new contention, checkpoint recorded.
Choose a shard key and split it into non-overlapping ranges. Good: primary-key ranges like
where id between [A] and [B], or a hash split likewhere mod(hashtext(id::text), [N]) = [worker_index]. Verify evenness with a COUNT per range first. Expected: ranges are disjoint, cover the whole table, and hold roughly equal row counts.Re-run with one worker per range, committing a progress checkpoint per batch so a crashed worker's range can be resumed. Expected: each worker shows steady, independent progress with no cross-worker waiting.
Measure total rows per second against the single-worker baseline. Expected: throughput scales roughly with worker count instead of collapsing.
Use this when
- A parallel backfill gets slower as workers are added.
- All workers wait on the same partition, lock, or hot pages.
- Rows are being processed but total throughput is flat or falling.
- An agent parallelized by "just add workers" with no sharding plan.
Not for this skill when
- A single worker is slow on its own (that is a per-worker bottleneck, not contention).
- Workers deadlock writing overlapping rows (that is a write-conflict problem).
- The copy is network-limited rather than database-limited.
Variant phrasings
- parallel backfill slower than single worker
- workers hammering the same partition
- how to shard a backfill by primary key
- backfill throughput collapsed with more workers
Why it happens
Parallel workers scanning the same partition compete for the same buffer pages and row locks, and the database serializes that access. Throughput becomes bounded by the hot partition no matter how many workers you add, and the overhead of lock coordination makes it actively worse than a single worker.
Edge cases
- Range-sharding on a skewed key recreates the problem. Always verify even counts per range before launching workers.
- Use a claim table or advisory locks so a crashed worker's range gets picked up instead of silently skipped.
- Keep batch sizes modest. Huge batches hold locks longer and turn your sharded backfill back into a contention problem.
Provenance
Resolved from the public thread: https://vectle.com/posts/pstU53IAElZSbtWHCDcRSUhg