# bench-upsert results

Measured on the test DB (`seo_insight_test`) on 2026-06-22T05:25:05.427Z.
DuckDB `@duckdb/node-api` 1.5.3-r.3, postgres extension, `pg_use_binary_copy=true`,
`pg_connection_limit=4`. Normalization held constant (identical pre-built rows
for Path 1 and Path 2-fromJS; Path 2-native generates the same shape in-SQL).

## Results

| N | path | pass | ms | rows/s | PG conn peak |
|---|------|------|----|--------|--------------|
| 10,000 | Path 1 (upsertCanonical) | insert | 799 | 12,522 | 2 |
| 10,000 | Path 1 (upsertCanonical) | update | 795 | 12,572 | 2 |
| 10,000 | Path 2-fromJS | insert | 604 | 16,556 | 3 |
| 10,000 | Path 2-fromJS | update | 634 | 15,771 | 3 |
| 10,000 | Path 2-native | insert | 457 | 21,872 | 3 |
| 10,000 | Path 2-native | update | 445 | 22,464 | 3 |
| 50,000 | Path 1 (upsertCanonical) | insert | 3690 | 13,549 | 2 |
| 50,000 | Path 1 (upsertCanonical) | update | 4112 | 12,159 | 2 |
| 50,000 | Path 2-fromJS | insert | 2913 | 17,165 | 3 |
| 50,000 | Path 2-fromJS | update | 2923 | 17,107 | 3 |
| 50,000 | Path 2-native | insert | 2753 | 18,159 | 3 |
| 50,000 | Path 2-native | update | 3186 | 15,692 | 3 |
| 100,000 | Path 1 (upsertCanonical) | insert | 8586 | 11,647 | 3 |
| 100,000 | Path 1 (upsertCanonical) | update | 10278 | 9,730 | 3 |
| 100,000 | Path 2-fromJS | insert | 12195 | 8,200 | 3 |
| 100,000 | Path 2-fromJS | update | 7506 | 13,322 | 3 |
| 100,000 | Path 2-native | insert | 8151 | 12,269 | 3 |
| 100,000 | Path 2-native | update | 7206 | 13,878 | 3 |

## Interpretation

- **Path 2-fromJS / Path 1 speedup at 100k (insert):** 0.70× (>1 = DuckDB faster).
- **Path 2-native / Path 1 speedup at 100k (insert):** 1.05× (best case, no JS marshalling).

**NO-GO** — neither DuckDB path is ≥ ~2× faster than Path 1 at 100k. The staging + PG-side upsert overhead (extra COPY + extra full-table merge) cancels the LOAD win. Keep the current chunked Drizzle upsert.

The "fromJS" path carries the same JS materialization cost as Path 1 PLUS the
Appender marshalling, the binary COPY into staging, AND a second full-table
PG-side merge — so it only wins if Postgres-side insert is the bottleneck. The
"native" path is the realistic ceiling for an eventual pipeline where
normalization runs inside DuckDB and JS never sees the rows.

## Reproducibility — staging DDL

```sql
CREATE TABLE IF NOT EXISTS "bench_stg_7eec2d43" (
  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
)
```

(The staging table is intentionally all `text`/`text[]`: DuckDB's postgres
writer does not emit `jsonb` cleanly, so `metrics`/`trend` are carried as
`text` and cast PG-side — see the upsert below. **Type-bridging compromise:**
`metrics_json`/`trend_json` are JSON strings cast via `::jsonb`; the `text[]`
arrays (intent/sources/stages/serp_features) cross natively.)

## Reproducibility — PG-side upsert (replicates upsertCanonical's merge)

```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_7eec2d43"
ON CONFLICT (id) DO UPDATE SET
  term          = excluded.term,
  lang          = excluded.lang,
  country       = excluded.country,
  intent        = excluded.intent,
  primary_source = excluded.primary_source,
  serp_features = excluded.serp_features,
  sources = (SELECT array_agg(DISTINCT s)
             FROM unnest(keywords.sources || excluded.sources) AS s),
  stages  = (SELECT coalesce(array_agg(DISTINCT s), '{}')
             FROM unnest(keywords.stages || excluded.stages) AS s),
  metrics = keywords.metrics || excluded.metrics
```