# bench-scale results

Measured on the throwaway bench DB (`seo_insight_bench`) on 2026-06-22T06:51:33.183Z.
DuckDB `@duckdb/node-api` 1.5.3-r.3 (postgres ext, `pg_use_binary_copy=true`,
`pg_connection_limit=4`). Deterministic seeded data (LCG), warm-up discarded,
Phase 1 = 5 measured runs, Phase 2 = 3 runs (median reported).

Hypothesis: 1000 NEW clients = mostly first-imports = all-INSERT, so a
COPY/INSERT fast path WITHOUT `ON CONFLICT DO UPDATE` should beat the current
upsert, and concurrency (N workers) is the real 1K-client scaling axis.

## Phase 1 — write-cost per strategy (single site, empty → first-import)

Each run TRUNCATEs the site's keywords first → every run is a clean first-import.

| N | strategy | median ms | min | max | rows/s | speedup vs A |
|---|----------|-----------|-----|-----|--------|--------------|
| 10,000 | A — upsert | 659 | 639 | 750 | 15,182 | 1.00× |
| 10,000 | B — insert (no conflict) | 582 | 561 | 592 | 17,175 | 1.13× |
| 10,000 | C — DuckDB COPY | 377 | 353 | 396 | 26,520 | 1.75× |
| 50,000 | A — upsert | 3366 | 3263 | 3415 | 14,856 | 1.00× |
| 50,000 | B — insert (no conflict) | 3017 | 2796 | 3099 | 16,572 | 1.12× |
| 50,000 | C — DuckDB COPY | 1842 | 1826 | 2404 | 27,146 | 1.83× |
| 100,000 | A — upsert | 6920 | 6682 | 6952 | 14,450 | 1.00× |
| 100,000 | B — insert (no conflict) | 5984 | 5898 | 6387 | 16,712 | 1.16× |
| 100,000 | C — DuckDB COPY | 4321 | 3821 | 5504 | 23,143 | 1.60× |

**Fast-path speedup vs A (upsert) at N=100,000:** B (plain INSERT) = 1.16×, C (DuckDB COPY) = 1.60×.
Best single-site fast primitive = **C (DuckDB COPY)** (1.60× vs upsert).

Reading: dropping `ON CONFLICT DO UPDATE` on a first-import removes the merge's
read-modify-write + array-union/jsonb-concat subqueries. The delta between A and
B isolates exactly that merge cost; C adds the DuckDB binary-COPY transport on
top of a plain insert.

## Phase 2 — concurrency curve (M sites × 2,000 rows, drained by N workers)

Fast path = strategy B (plain INSERT, no conflict) vs upsert (A). Each worker
loads one site at a time; rows pre-generated (generation excluded from wall-clock).

| M sites | total rows | path | workers | wall ms | rows/s | conn peak | lock-wait peak | deadlocks |
|---------|-----------|------|---------|---------|--------|-----------|----------------|-----------|
| 50 | 100,000 | fast (B insert) | 1 | 6318 | 15,827 | 4 | 0 | 0 |
| 50 | 100,000 | fast (B insert) | 2 | 4796 | 20,852 | 5 | 0 | 0 |
| 50 | 100,000 | fast (B insert) | 4 | 3552 | 28,151 | 7 | 3 | 0 |
| 50 | 100,000 | fast (B insert) | 8 | 3454 | 28,948 | 11 | 5 | 0 |
| 50 | 100,000 | fast (B insert) | 16 | 3825 | 26,144 | 11 | 4 | 0 |
| 50 | 100,000 | upsert (A) | 1 | 7109 | 14,067 | 11 | 0 | 0 |
| 50 | 100,000 | upsert (A) | 2 | 4942 | 20,236 | 11 | 1 | 0 |
| 50 | 100,000 | upsert (A) | 4 | 3339 | 29,951 | 11 | 0 | 0 |
| 50 | 100,000 | upsert (A) | 8 | 3649 | 27,408 | 11 | 0 | 0 |
| 50 | 100,000 | upsert (A) | 16 | 3499 | 28,578 | 11 | 0 | 0 |
| 100 | 200,000 | fast (B insert) | 1 | 12663 | 15,794 | 12 | 0 | 0 |
| 100 | 200,000 | fast (B insert) | 2 | 9205 | 21,729 | 12 | 0 | 0 |
| 100 | 200,000 | fast (B insert) | 4 | 6435 | 31,082 | 11 | 0 | 0 |
| 100 | 200,000 | fast (B insert) | 8 | 7453 | 26,836 | 11 | 4 | 0 |
| 100 | 200,000 | fast (B insert) | 16 | 7498 | 26,674 | 11 | 0 | 0 |
| 100 | 200,000 | upsert (A) | 1 | 14919 | 13,406 | 12 | 0 | 0 |
| 100 | 200,000 | upsert (A) | 2 | 10156 | 19,693 | 11 | 0 | 0 |
| 100 | 200,000 | upsert (A) | 4 | 6618 | 30,221 | 11 | 0 | 0 |
| 100 | 200,000 | upsert (A) | 8 | 6656 | 30,050 | 11 | 0 | 0 |
| 100 | 200,000 | upsert (A) | 16 | 6564 | 30,471 | 12 | 1 | 0 |
| 250 | 500,000 | fast (B insert) | 1 | 34008 | 14,703 | 12 | 0 | 0 |
| 250 | 500,000 | fast (B insert) | 2 | 25589 | 19,540 | 12 | 0 | 0 |
| 250 | 500,000 | fast (B insert) | 4 | 16610 | 30,103 | 11 | 0 | 0 |
| 250 | 500,000 | fast (B insert) | 8 | 17095 | 29,248 | 12 | 5 | 0 |
| 250 | 500,000 | fast (B insert) | 16 | 16827 | 29,714 | 12 | 0 | 0 |
| 250 | 500,000 | upsert (A) | 1 | 39016 | 12,815 | 12 | 0 | 0 |
| 250 | 500,000 | upsert (A) | 2 | 26280 | 19,026 | 12 | 0 | 0 |
| 250 | 500,000 | upsert (A) | 4 | 17117 | 29,211 | 11 | 2 | 0 |
| 250 | 500,000 | upsert (A) | 8 | 17160 | 29,137 | 12 | 7 | 0 |
| 250 | 500,000 | upsert (A) | 16 | 19243 | 25,984 | 12 | 0 | 0 |

### Concurrency ceiling (fast path)

- **M=50 sites**: throughput peaks at **N=8** (28,948 rows/s, conn peak 11, lock-wait peak 5) (no clear degradation across tested N up to 16).
- **M=100 sites**: throughput peaks at **N=4** (31,082 rows/s, conn peak 11, lock-wait peak 0), then degrades by N=8 (26,836 rows/s).
- **M=250 sites**: throughput peaks at **N=4** (30,103 rows/s, conn peak 11, lock-wait peak 0) (no clear degradation across tested N up to 16).

- **Global best fast-path point:** N=4 @ M=100 → 31,082 rows/s.
- **PG connection peak (any run):** 12.
- **Lock waits / deadlocks:** peak lock-wait 7, max deadlocks 0.

## Recommendation for 1000 clients

1. **First-import fast path is worth it:** strategy C (DuckDB COPY) loads a fresh site
   1.60× faster than the upsert at N=100,000. For 1000 NEW clients
   (all first-imports, zero conflicts) route ingestion through the no-conflict
   INSERT and skip the merge entirely. Reserve `upsertCanonical` for re-imports
   into an already-populated site (where conflicts genuinely exist).

2. **Worker concurrency:** throughput maximizes around **N=4 concurrent workers**
   (best observed 31,082 rows/s); see the per-M ceiling notes for where
   adding workers stops helping. Size the ingest worker pool to that N and cap the
   PG connection pool just above it (observed conn peak 12).

3. **Partition keywords by site_id?** Lock waits / deadlocks WERE observed (lock-wait peak 7, deadlocks 0) as N rose → **partitioning `keywords` by site_id is worth evaluating** to cut cross-site index/heap contention.

## Reproducibility — DDL & SQL

### Strategy B (plain INSERT, no conflict) — chunked, ≤1000 rows/statement, one txn
(Drizzle `db.insert(keywords).values(slice)` per chunk, no `.onConflictDoUpdate`.)

### Strategy C — DuckDB staging DDL (all text / text[])
```sql
CREATE TABLE "bench_stg_<run>" (
  id             text PRIMARY KEY,
  site_id        text NOT NULL,
  term           text NOT NULL,
  lang           text NOT NULL,
  country        text NOT NULL,
  primary_source text NOT NULL,
  status         text NOT NULL,
  intent         text[] NOT NULL,
  sources        text[] NOT NULL,
  stages         text[] NOT NULL,
  serp_features  text[],
  metrics_json   text NOT NULL,
  trend_json     text NOT NULL
)
```

### Strategy C — PG-side plain INSERT … SELECT (no conflict, text→jsonb cast)
```sql
INSERT INTO keywords
  (id, site_id, term, lang, country, status, intent,
   primary_source, sources, stages, serp_features, metrics, trend)
SELECT
  id, site_id, term, lang, country, status, intent,
  primary_source, sources, stages, serp_features,
  metrics_json::jsonb, trend_json::jsonb
FROM "bench_stg_<run>"
```