# ADR / Premortem — DuckDB en lecture pour décharger Postgres

> **Date** : 2026-06-22 · **Auteur** : architecte · **Statut** : proposé (décision phasée, mesure-d'abord)
> **Type** : premortem + ADR + plan d'exécution
> **Contexte produit** : `docs/project/overview.md` (cible Hono · workers Rust · SurrealDB · MCP).
> Service existant : `services/analytics/` (Bun + Hono + DuckDB, R2 câblé).

---

## 1. Décision demandée

Faut-il introduire **DuckDB sur le chemin de lecture** (servir des données à l'API / Next.js)
pour anticiper le moment où **PostgreSQL atteindra ses limites** ? Si oui, comment, et avec
quels gains ?

**Décision** : oui, mais **phasé et mesure-d'abord**. DuckDB en lecture est le bon outil pour
l'**analytique** ; il ne remplace pas Postgres sur l'OLTP. On ne migre rien tant que la
contention réelle n'est pas chiffrée (Phase 0).

---

## 2. Premortem — « Postgres va vite atteindre ses limites »

### 2.1 Challenge de la prémisse

« Vite » est probablement faux sur le **volume brut**. Postgres encaisse des centaines de
millions de lignes correctement indexées. Sur les vraies tables de croissance du projet :

- `gsc_query_snapshots` (PK `site_id,date,query`) et `gsc_page_snapshots`
  (PK `site_id,date,page`) — **time-series append-only**, écrites quotidiennement par le
  worker Rust (`crates/gsc-worker`). Ordre de grandeur : ~1 000 requêtes/jour/site ⇒
  ~365 k lignes/an/site ⇒ ~36 M lignes/an pour 100 sites. **Gérable** si partitionné/indexé.

Le mur n'est donc **pas** le nombre de lignes.

### 2.2 Les vrais points de rupture (par ordre d'apparition probable)

1. **Contention OLTP + OLAP sur une seule instance contrainte.**
   Le transactionnel (CRUD réservoir, promotions, outreach, invitations) **et** l'analytique
   lourd (`src/lib/content/stats.ts` : `sum(...) filter(...)`, position pondérée par
   impressions, période courante vs précédente, group-by `page` sur tout l'historique)
   tapent le **même** Postgres, avec un **pool ~10 connexions** (cf. [[ELEVA-42]] : fuite de
   connexions déjà subie) sur un **VPS Dokploy modeste** (deploy manuel sudo-gated). À mesure
   que les snapshots grossissent, les agrégats balayent plus de lignes et entrent en
   compétition avec les écritures. **C'est le premier point de rupture.**

2. **Croissance non bornée des snapshots GSC.** Append-only sans partitionnement ⇒ scans par
   période linéairement plus chers, pression autovacuum, index qui gonflent.

3. **Lectures jsonb TOAST sur `keywords`** (metrics / trend / trendBySource / serp /
   gscByCountry / briefs). Déjà partiellement traité : lz4 ([[Batch E]] / ELEVA-78), `serp`
   retiré des reads ([[Batch C]] / ELEVA-74), cache overview 30 s. Borné par site, mais les
   scans cross-keywords (consensus médian, histogrammes) restent row-store.

### 2.3 Conclusion du premortem

Ce qui casse en premier, c'est le **mélange OLAP/OLTP sur une instance contrainte** + les
**time-series non bornées** — pas un plafond Postgres. C'est précisément le cas d'usage où
**DuckDB en lecture** (moteur columnar) excelle.

---

## 3. Périmètre — où DuckDB aide (et où non)

| Charge | DuckDB en lecture ? |
|---|---|
| `stats.ts` (agrégats période, position pondérée, prev-period) | ✅ sweet spot columnar |
| Overview hubs, trends / sparklines, consensus médian cross-source | ✅ |
| Backlink overview (histogrammes DR, buckets, cadence) | ✅ |
| CRUD réservoir, statut, promotion, outreach, invitations | ❌ reste Postgres (OLTP) |
| Point-reads par id (sheet keyword) | ❌ Postgres |

**Atout de départ** : DuckDB est **déjà embarqué et opérationnel** dans `services/analytics/`
(R2 câblé, `ANALYTICS_API_KEY`, hygiène `memory_limit`/`threads` via [[Batch E]],
`withConnection` + close en `finally` via [[Batch D]]). On **étend un service existant**.

---

## 4. Options considérées

### Option A — Endpoints de lecture DuckDB sur Parquet/R2 — **retenue (cible)**
Le worker exporte des snapshots **Parquet** des time-series sur R2 (partition Hive
`site_id/month`). Le service analytics expose des routes Hono lues par DuckDB
(`read_parquet('r2://…')`). Next appelle l'API analytics pour les reads lourds. Postgres reste
source de vérité + OLTP.
- ➕ Offload OLAP **total** de Postgres ; columnar 10–100× sur agrégats ; réutilise R2 + le
  service ; fraîcheur J-1 = déjà la réalité GSC.
- ➖ Pipeline d'export à bâtir ; deux chemins (Drizzle OLTP / DuckDB SQL OLAP) ; gérer la
  concurrence de lecture (handle DB caché, `ATTACH` read-only, partition-pruning).

### Option B — DuckDB `postgres_scanner` en direct sur le chemin de requête — **rejetée comme service**
DuckDB attache Postgres et agrège en lisant les lignes à la volée.
- La doc officielle (`core_extensions/postgres`) confirme : **scan parallèle par ctid**
  (`pg_use_ctid_scan=true`, `pg_pages_per_task=1000`) ⇒ **ouvre N connexions Postgres**
  (`pg_connection_limit=64` par défaut). Sur un pool ~10, cela **aggrave** la contention
  qu'on veut soulager.
- La doc recommande explicitement de **matérialiser une copie** :
  > *"It might be desirable to create a copy of the PostgreSQL databases in DuckDB to prevent
  > the system from re-reading the tables from PostgreSQL continuously, particularly for large
  > tables."*
- **MAIS** le scanner est **le bon outil pour l'ETL** (lecture read-only → Parquet, voir §6).
  Filter pushdown dispo mais **expérimental** (`pg_experimental_filter_pushdown=true`).

### Option C — DuckDB embarqué dans Next.js — **rejetée**
Addon natif + Parquet/fichiers dans le tier web ⇒ cold starts, mémoire, casse la séparation
micro-module déjà choisie.

### Option D — Pas de DuckDB : matviews/rollups + partitionnement Postgres — **retenue pour Phase 0**
Partitionner `gsc_*_snapshots` par mois (+ index BRIN sur `date`), pré-agréger les stats.
- ➕ Zéro nouvelle techno, résout ~80 % du coût de lecture, gros répit immédiat.
- ➖ Pas d'analytique columnar ad-hoc ; maintenance partitions/refresh. **Probablement
  suffisant à l'échelle actuelle.**

### Option E — Lac Parquet/DuckDB complet (north-star) — **différée**
Version « go big » de A, alimentant aussi les outils MCP/AI (cap Agents-Ready). Prématuré.

---

## 5. Garde-fous premortem (à ne pas découvrir en prod)

- **Ne jamais** brancher Next/l'API directement sur `postgres_scanner` pour servir du trafic :
  le scan parallèle multi-connexions cannibalise le pool. Le scanner reste **off-peak, côté
  ETL uniquement**.
- **Concurrence DuckDB** : single-process ⇒ cache le handle DB, `ATTACH … (READ_ONLY)`, une
  connexion read/requête (réutiliser le pattern `withConnection`).
- **Latence R2/httpfs** : impérativement du **partition pruning** (`site_id`/`month`) +
  envisager un cache SSD local des partitions chaudes, sinon explosion des **Class-B ops** R2.
- **Cohérence** : le Parquet est une **projection dérivée, jamais source de vérité** ; export
  idempotent, reconstructible depuis Postgres à tout moment.
- **Tenancy** : l'API read scope `site_id`/org comme aujourd'hui (bearer `ANALYTICS_API_KEY`) ;
  le chemin de partition encode `site_id` et l'API le valide — **pas de lecture Parquet
  cross-site**. (Cohérent avec [[ELEVA-72]]/[[ELEVA-45]].)
- **Cache de schéma DuckDB** : `pg_clear_cache()` après toute migration de schéma.
- **Ne pas confondre deux migrations** : DuckDB-en-lecture = accélérateur analytique,
  **orthogonal** au remplacement éventuel du store OLTP (cible cahier : SurrealDB). Il
  décharge Postgres, il ne le remplace pas.

---

## 6. Plan d'exécution

### Phase 0 — Postgres natif (maintenant, cheap, haute ROI)
But : retirer le risque court terme **sans nouvelle infra** et **mesurer** si DuckDB est même
nécessaire.

1. **Instrumenter** `stats.ts` + overview : logguer durée requête + lignes scannées (mesure de
   la contention réelle avant d'investir).
2. **Partitionner** `gsc_query_snapshots` / `gsc_page_snapshots` par mois (`RANGE (date)`),
   migration Drizzle (`generate` + `migrate`, jamais `db:push`). Index **BRIN** sur `date` par
   partition.

   ```sql
   -- esquisse (à générer proprement via Drizzle / migration manuelle partitionnée)
   -- table partitionnée par mois + BRIN date ; partitions roulantes créées par le worker.
   CREATE INDEX gsc_page_snap_date_brin ON gsc_page_snapshots USING brin (date);
   ```
3. **Rollup / cache** des agrégats stats (matview mensuelle ou table de rollup rafraîchie par
   le worker, ou extension du `unstable_cache` + tag existant).
4. **Décider** sur données : si la contention reste sous seuil → on s'arrête là.

### Phase 1 — DuckDB read-path (quand la contention est mesurée)
Réutilise `services/analytics/`.

1. **ETL Parquet** (off-peak, worker/cron) via le `postgres_scanner` read-only :

   ```sql
   INSTALL postgres; LOAD postgres; INSTALL httpfs; LOAD httpfs;
   ATTACH 'dbname=seo_insight host=… user=… password=…' AS pg (TYPE postgres, READ_ONLY);
   SET pg_experimental_filter_pushdown = true;   -- pushdown du WHERE date/site_id

   COPY (
     SELECT site_id, date, page, clicks, impressions, ctr, position
     FROM pg.gsc_page_snapshots
     WHERE date >= DATE '2026-06-01'             -- export incrémental par mois
   ) TO 'r2://seo-exports/lake/gsc_page/site_id=…/month=2026-06.parquet'
     (FORMAT parquet, COMPRESSION zstd);
   ```
   Idempotent (réécrit le Parquet du mois courant), reconstructible intégralement.

2. **Routes de lecture** Hono → DuckDB sur Parquet :

   ```sql
   SELECT page,
          sum(clicks)      FILTER (WHERE date >= $cur_start) AS clicks,
          sum(impressions) FILTER (WHERE date >= $cur_start) AS impressions,
          sum(position*impressions) FILTER (WHERE date >= $cur_start) AS pos_weighted
   FROM read_parquet('r2://seo-exports/lake/gsc_page/site_id=$site/month=*.parquet')
   WHERE date >= $prev_start
   GROUP BY page;
   ```
   Partition-pruning par `site_id`/`month`. Handle DB caché, connexion read-only/requête.

3. **Bascule Next** : `stats.ts` / overview / trends appellent l'API analytics
   (typed RPC `hc<AppType>`) au lieu de Postgres. OLTP inchangé.

### Phase 2 — Lac pour MCP/AI (north-star)
Étendre le lac Parquet pour alimenter les outils MCP de lecture + agents AI (cap
Agents-Ready). Gouvernance/lifecycle R2, partitions roulantes.

---

## 7. Conséquences

- ➕ Postgres recentré sur l'OLTP (sa force) ; analytique sur un moteur columnar adapté.
- ➕ Réutilisation maximale de l'existant (R2, `services/analytics/`, DuckDB déjà en prod).
- ➕ Chemin incrémental, réversible (Parquet = projection jetable).
- ➖ Deux chemins de données (OLTP Drizzle / OLAP DuckDB) à maintenir et tester.
- ➖ Fraîcheur analytique = J-1 (acceptable : GSC est déjà J-1).
- ⚠️ À surveiller : coûts R2 Class-B sans pruning ; concurrence DuckDB single-process ;
  tenancy stricte sur le chemin de partition.

---

## 8. Références
- Doc DuckDB Postgres extension : `core_extensions/postgres` (READ_ONLY, `pg_use_ctid_scan`,
  `pg_pages_per_task`, `pg_connection_limit`, `pg_experimental_filter_pushdown`,
  `pg_use_binary_copy`, recommandation « create a copy … for large tables », `pg_clear_cache`).
- `services/analytics/README.md` — service DuckDB existant (ingest path).
- `src/lib/content/stats.ts` — requête analytique de référence (candidate Phase 1).
- `src/lib/db/domain-schema.ts` — `gscQuerySnapshots` / `gscPageSnapshots` / `keywords`.
- Roadmap : [[Batch C]] (ELEVA-74 index), [[Batch D]] (ELEVA-76 robustesse DuckDB),
  [[Batch E]] (ELEVA-78 compression/cache), [[ELEVA-42]] (pool connexions).