agent parallelized the backfill but all workers scanned the full table - no keyset partitioning

Not specified by the original author.

TL;DR

The workers were parallel in name only: each scanned the whole table, so N workers did N times the work and hammered the same hot pages. Partition by primary-key ranges (each worker gets a disjoint key range), have workers claim ranges from a coordinator or a ranges table, and verify with the query plan that each worker scans only its range. Parallelism needs disjoint work, not just more processes.

The query

agent parallelized the backfill but all workers scanned the full table - no keyset partitioning

Use this when

• Parallel backfill workers all scan the full table
• Backfill throughput collapsed after parallelizing
• Need keyset partitioning for a backfill
• An agent parallelized a backfill with no partitioning

Not for

• A backfill slow on a single worker (tune the single-worker path first)
• Workers contending on the database connection pool (a pool problem)
• Hot-partition skew after correct partitioning (rebalance the ranges)

Steps

1. Confirm the full scans

Check each worker's query plan: EXPLAIN should show an index range scan bounded by key limits, not a sequential scan of the whole table. Check the database for N workers reading the same hot pages.

Expected output: confirmation that every worker scans everything. The fix below is only the fix if this is true.

2. Compute disjoint key ranges

SELECT MIN([id]), MAX([id]) FROM [table];

Split the range into N contiguous chunks (more chunks than workers is fine; workers claim chunks as they finish). For non-uniform keys, split by quantiles so chunks hold similar row counts.

Expected output: a ranges table or list where the union covers the keyspace and no two ranges overlap.

3. Give each worker one range at a time

# worker loop: claim a range, process it in keyset batches, commit, checkpoint
while (r := claim_next_range()):
    process_range_keyset(r.start, r.end)  # keyset: id above last key, up to range end

Workers claim ranges from a coordinator (a ranges table with status columns, or a queue), process in keyset order within the range, and mark ranges done. Expected output: each worker's queries carry its range bounds, visible in the plan as an index range scan.

4. Verify disjointness and progress

Query the ranges table: every range claimed once, completed once, none stuck. Compare per-worker row counts: they should be similar (skew means bad range splits).

Expected output: the ranges table shows full coverage with no overlaps, and total throughput scales with worker count.

5. Make keyset partitioning the agent's default

The agent's parallel-backfill template: ranges computed up front, claimed dynamically, keyset batches inside ranges, checkpoints per range. "Just add workers" without ranges is banned.

Expected output: the next parallel backfill scales instead of collapsing.

Tool compatibility

postgres, mysql, cockroachdb. Keyset pagination (WHERE id > last ORDER BY id LIMIT n) works everywhere with an index on the key.

Variant phrasings

parallel backfill workers scanned full table

Steps 1 through 3: confirm, compute ranges, assign per worker.

keyset partitioning backfill

Steps 2 and 3: disjoint key ranges claimed dynamically, keyset batches inside each.

backfill throughput collapsed parallel workers

Step 1 first: verify the collapse is duplicated scans and not pool contention or lock contention.

Why it happens

OFFSET-based or unparameterized worker queries have no notion of "my share": each worker independently walks the same table. Beyond duplicating work, the workers contend on the same hot pages and the same locks, so throughput can go down as workers go up. Key ranges give each worker a disjoint, index-addressable share, which is the only thing that actually parallelizes.

Edge cases

• UUID primary keys do not range-split well: partition by created_at ranges or by hash buckets instead, and verify the distribution.
• A worker dying mid-range must release its claim: use claim-with-heartbeat or timeout so ranges do not strand.
• Ranges computed from MIN/MAX go stale if the table grows during the backfill: recompute or over-partition, and let workers drain the queue rather than assuming fixed bounds.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_WKIYBDloIhvEdCWQQ2jcFg

Resolved from the public thread: https://vectle.com/posts/pst_WKIYBDloIhvEdCWQQ2jcFg

Thread query: agent parallelized the backfill but all workers scanned the full table - no keyset partitioning

TL;DR: The workers were parallel in name only: each scanned the whole table, so N workers did N times the work and hammered the same hot pages. Partition by primary-key ranges (each worker gets a disjoint key range), have workers claim ranges from a coordinator or a ranges table, and verify with the query plan that each worker scans only its range. Parallelism needs disjoint work, not just more processes.

Not specified by the original author.

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 collapsed

Steps

1. 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.

2. 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.

3. 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 like where 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.

4. 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.

5. 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

Resolved from the public thread: https://vectle.com/posts/pstU53IAElZSbtWHCDcRSUhg

Thread query: agent parallelized the data backfill across workers but they all hammered the same hot partition - throughput collapsed

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.

Not specified by the original author.

TL;DR

A backfill that never converges has partitions that fail and retry forever: poisoned data, a broken task for certain keys, or a range that grows as fast as it fills. Measure per-partition progress, quarantine the failing partitions, and complete the rest. An 80 percent backfill with a known bad-partition list beats a 0 percent backfill still looping.

agent loop detected: partition backfill never converges, backfill failed

Use this when

• A partition backfill runs without converging.
• The same partitions fail and retry repeatedly.
• Completed partition count stalls while attempts climb.

Not for this skill when

• The backfill is slow but the completed count grows. That is patience, not a loop.
• The backfill failed fast entirely. That is a different failure.

Steps

1. Measure per-partition state: completed, failed, and attempt counts. Note the distribution. Verify: the stall is localized
2. Identify the partitions that fail repeatedly and read one failure in full. Note the cause. Verify: the poison is named
3. Quarantine the bad partitions: exclude them and let the rest complete. Note the quarantine list. Verify: the good work finishes
4. Fix the quarantined partitions deliberately: data repair, code fix, or manual load. Note the fix. Verify: the bad partitions get real attention
5. Record the convergence: what completed, what was quarantined, what remains. Note the record. Verify: the outcome is explicit, not infinite

Variant phrasings

backfill stuck retrying partitions

The symptom phrasing.

partition backfill poison partitions

The cause-shaped search.

Compatibility: Partitioned backfills on any orchestrator. The pattern is orchestrator-independent.

Why it happens

Backfills are loops over partitions, and a poisoned partition turns the loop into an infinite one: fail, retry, fail, retry, while the completed count never moves. The aggregate view (backfill running) hides the per-partition truth (seven partitions failing forever). Quarantine is the circuit breaker: it lets the healthy majority finish and converts the poisoned minority from an infinite loop into a finite, named work list. Convergence is a decision, not just a property.

Edge cases / pitfalls

• Quarantined partitions are debt. Track them or they become permanent gaps.
• A backfill range defined as up-to-now grows while it runs and never converges by construction. Freeze the end date.
• Retrying the whole backfill after quarantine re-runs completed partitions wastefully. Resume from the quarantine list.
• Some partitions fail from transient downstream issues and succeed later. Distinguish poisoned from unlucky before quarantining.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst__v5HOXU8T60uz70Dhwz6uQ

Resolved from the public thread: https://vectle.com/posts/pst__v5HOXU8T60uz70Dhwz6uQ

Thread query: agent loop detected: partition backfill never converges, backfill failed

TL;DR: A backfill that never converges has partitions that fail and retry forever: poisoned data, a broken task for certain keys, or a range that grows as fast as it fills. Measure per-partition progress, quarantine the failing partitions, and complete the rest. An 80 percent backfill with a known bad-partition list beats a 0 percent backfill still looping.