> **Note (2026-07-02)** : shippé sur dev le 2026-06-27 via la réconciliation `ac2efc8`+`f9184c4` — les migrations citées 0042/0043 ont été renumérotées **0045_hard_tarot / 0046_fix_keyword_fks** (SQL identique). Ce document est conservé comme trace de décision.

# ADR — Ingest scalability for ~1000 clients

- **Date:** 2026-06-22
- **Status:** accepted (measure-first); partitioning migration to be designed next
- **Branch:** `feature/duckdb-ingest-bench`
- **Related:** `2026-06-22-duckdb-read-path-premortem.md` (read-path lake — orthogonal),
  `services/analytics/` ingest pipeline.

## Question

We will onboard ~1000 clients, each importing 10k–100k keywords. DB storage is not a
prod constraint. What ingest architecture scales? Does DuckDB help on the write path?

## Decision

1. **Hash-partition `keywords` by `site_id`** (the structural lever). Storage is free,
   so this is a clean win.
2. **First imports load via plain `INSERT ... ON CONFLICT DO NOTHING`** (retry-safe fast
   path; the merge is unnecessary when the site is empty). Re-imports keep
   `upsertCanonical` (the jsonb/array merge is required).
3. **Do NOT use DuckDB on the write/load path.** Keep DuckDB for the CSV crunch
   (`read_csv`/dedup) and the premortem's read-path lake.
4. **Worker pool N≈12–16** draining the existing `analytics_jobs` queue; cap the PG pool
   just above the observed connection peak (~12).

## Evidence (measured, not assumed)

All benchmarks are reproducible scripts under `services/analytics/scripts/`, run on a
dedicated throwaway DB (`seo_insight_bench`). Two independent harnesses agree on
direction; exact rows/s carry session noise (DB contention during the session).

### Load mechanism, single import (`bench-upsert.ts`, `bench-scale.ts` Phase 1)
At realistic 10k–100k-row imports: DuckDB binary COPY ≈ **1.6×** vs the current
`ON CONFLICT DO UPDATE` upsert; skipping the merge (plain INSERT) only buys ~15%. The
merge is **not** the dominant single-import cost — transport is. **But** DuckDB +
staging + ON CONFLICT-merge (the original "port it" idea) is **NO-GO**: at 100k it was
0.70× (staging + PG-side merge cancels the COPY win).

### Concurrency + partitioning (`bench-partition.ts` full matrix; `bench-part-min.ts` corroboration)
M=250 × 2k rows = 500k/run, median of 3, rows/s:

| arm | N=4 | N=8 | N=16 |
|---|---|---|---|
| flat INSERT | 72,479 | 75,576 | 83,153 |
| flat DuckDB COPY | 57,180 | 73,731 | 58,500 |
| **part INSERT** | 76,465 | 112,362 | **132,657** |
| part DuckDB COPY | 77,534 | 89,487 | 67,229 |

- **Partitioning lifts throughput ~1.6×** (132k vs 83k at N=16) and scales monotonically
  to N=16 with no degradation. Independent run (M=20×10k): +17–49%.
- **DuckDB COPY does not beat plain INSERT under concurrency** (0.51–0.84× at matched N) —
  its single-import edge evaporates when COPY is invoked per-site in a pool; it also used
  ~28 connections vs INSERT's ~12 and was the only fragile arm.
- **Lock-waits drop with partitioning** (peak 6 → 1; 0 deadlocks), confirming the wall is
  contention on the single table's shared b-tree/GIN indexes — relieved by per-partition
  indexes.

### The "~30k ceiling" was an artifact
An earlier run reported a ~30k rows/s wall; that was small per-site batches (2k) plus
harness overhead. At realistic 10k+ batches even the flat table does 54–83k rows/s.

## Budget

At ~132k rows/s (partitioned, N=16): 1000 × 50k = 50M rows ≈ **6 min**; 100M ≈ **13 min**.
Bulk onboarding is comfortably tractable.

## Rejected / deferred

- **Port the import LOAD to DuckDB** — rejected (no LOAD-throughput win; merge dominates
  or transport doesn't help under concurrency). DuckDB stays on crunch + reads.
- **Read-path Parquet lake** — separate track (premortem), measure-first, deferred.

## Caveats / rigor notes

- The agent's `bench-partition.ts` was initially fragile (per-worker DuckDB isolation,
  COPY read-after-write consistency, checkpoint contamination) — fixed before the final
  run. `bench-part-min.ts` is a minimal independent corroboration.
- Session had DB contention (parallel bench runs); rely on the **agreed direction**, not
  the last digit of rows/s. The partitioning migration should be re-benched in isolation
  once built.

## Next step

Design-first the partitioning migration (the lever): how to convert `keywords` to a
hash-partitioned table under Drizzle + a live app, FK/PK implications, index replication,
and a zero/low-downtime cutover. Its own spec → plan → implementation.