## 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

```text
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

```sql
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

```python
# 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