# bench-partition results

Measured on the throwaway bench DB (`seo_insight_bench`) on 2026-06-22T07:41:55.979Z.
DuckDB `@duckdb/node-api` 1.5.3-r.3 (postgres ext, `pg_use_binary_copy=true`,
`pg_connection_limit=1` per DuckDB instance; one DuckDB instance + one PG
attachment per worker; the staging→target cast-insert runs via DuckDB's own
`postgres_execute` for read-after-write consistency). Deterministic seeded data
(LCG, identical to bench-scale). M=250 sites × K=2,000 rows = 500,000 total per run,
N∈{2, 4, 8, 16} workers, 3 runs/combo (median reported); run resumably
in per-N chunks via a JSON checkpoint. TRUNCATE between every run.

## Question

bench-scale found ingest into the single shared `keywords` table saturates at
~30k rows/s around N=4 workers, with lock-waits rising as N grows DESPITE
per-site isolation → hypothesized wall = contention on the SHARED b-tree/GIN
indexes of one flat table. This probe asks:
1. Does hash-partitioning `keywords` by site_id (16 partitions, each with its
   own (site_id,term) / GIN(stages) / (site_id) index) break the ~30k ceiling?
2. Does DuckDB binary COPY beat plain INSERT under CONCURRENCY (flat AND
   partitioned)?

`keywords_part` = `CREATE TABLE keywords_part (LIKE keywords INCLUDING DEFAULTS)
PARTITION BY HASH (site_id)`, 16 partitions `keywords_part_0..15`,
PRIMARY KEY (id, site_id), 3 contended indexes replicated on the parent.

## 4-arm × N matrix

| arm | table | mechanism | workers | wall ms | rows/s | conn peak | lock-wait peak | deadlocks |
|-----|-------|-----------|---------|---------|--------|-----------|----------------|-----------|
| 1 flat + INSERT (B) | keywords | INSERT (B) | 16 | 6013 | 83,153 | 11 | 6 | 0 |
| 2 flat + DuckCOPY (C) | keywords | DuckDB COPY (C) | 16 | 8547 | 58,500 | 28 | 6 | 0 |
| 3 part + INSERT (B) | keywords_part | INSERT (B) | 16 | 3769 | 132,657 | 12 | 1 | 0 |
| 4 part + DuckCOPY (C) | keywords_part | DuckDB COPY (C) | 16 | 7437 | 67,229 | 29 | 1 | 0 |
| 1 flat + INSERT (B) | keywords | INSERT (B) | 8 | 6616 | 75,576 | 10 | 5 | 0 |
| 2 flat + DuckCOPY (C) | keywords | DuckDB COPY (C) | 8 | 6781 | 73,731 | 18 | 6 | 0 |
| 3 part + INSERT (B) | keywords_part | INSERT (B) | 8 | 4450 | 112,362 | 11 | 0 | 0 |
| 4 part + DuckCOPY (C) | keywords_part | DuckDB COPY (C) | 8 | 5587 | 89,487 | 18 | 1 | 0 |
| 1 flat + INSERT (B) | keywords | INSERT (B) | 4 | 6899 | 72,479 | 7 | 3 | 0 |
| 2 flat + DuckCOPY (C) | keywords | DuckDB COPY (C) | 4 | 8744 | 57,180 | 10 | 2 | 0 |
| 3 part + INSERT (B) | keywords_part | INSERT (B) | 4 | 6539 | 76,465 | 6 | 0 | 0 |
| 4 part + DuckCOPY (C) | keywords_part | DuckDB COPY (C) | 4 | 6449 | 77,534 | 10 | 0 | 0 |
| 1 flat + INSERT (B) | keywords | INSERT (B) | 2 | 11781 | 42,442 | 4 | 0 | 0 |
| 2 flat + DuckCOPY (C) | keywords | DuckDB COPY (C) | 2 | 13980 | 35,766 | 6 | 0 | 0 |
| 3 part + INSERT (B) | keywords_part | INSERT (B) | 2 | 7431 | 67,286 | 5 | 0 | 0 |
| 4 part + DuckCOPY (C) | keywords_part | DuckDB COPY (C) | 2 | 9521 | 52,517 | 6 | 0 | 0 |

## Verdict

### 1. Does partitioning raise the ceiling above ~30k?

**YES** — partitioning raised the ceiling: best partitioned arm hit 132,657 rows/s vs the flat baseline 83,153 (1.60×).

- Flat baseline (arm 1, best N): **83,153 rows/s** @ N=16.
- Partitioned + INSERT (arm 3, best N): **132,657 rows/s** @ N=16 (1.60× vs flat baseline).
- Partitioned + DuckCOPY (arm 4, best N): **89,487 rows/s** @ N=8 (1.08× vs flat baseline).

### 2. Does DuckDB COPY beat plain INSERT under concurrency?

  - **flat N=2:** INSERT 42,442 vs COPY 35,766 rows/s → COPY 0.84×
  - **part N=2:** INSERT 67,286 vs COPY 52,517 rows/s → COPY 0.78×

- Best flat COPY (arm 2): 73,731 rows/s @ N=8.
- Best partitioned COPY (arm 4): 89,487 rows/s @ N=8.

### 3. Best overall arm

**3 part + INSERT (B)** @ N=16 → **132,657 rows/s** (conn peak 12, lock-wait peak 1, deadlocks 0).

### 4. Do lock-waits drop with partitioning?

Lock-waits DROPPED with partitioning (flat peak 6 → partitioned peak 1), consistent with the shared-index-contention diagnosis.

- Flat: lock-wait peak 6, deadlocks 0.
- Partitioned: lock-wait peak 1, deadlocks 0.

## Recommendation for ingesting 1000 clients

- **Table layout:** hash-partition `keywords` by site_id (16 partitions).
- **Load mechanism:** plain no-conflict INSERT (the merge/upsert is unnecessary for first-imports).
- **Worker N:** 16 concurrent workers (best observed point; cap the PG pool just above the conn peak of 12).
- **Best sustained throughput:** 132,657 rows/s → ~3,980 sites/min at 2k rows/site.