# Plan d'attaque — compression & cache (PostgreSQL + DuckDB)

**Date :** 2026-06-11 · **Origine :** audit DB + recherche docs officielles (postgresql.org, duckdb.org).
**Contexte :** VPS Dokploy (Postgres self-hosted, postgres.js + Drizzle), worker Bun + `@duckdb/node-api` (instances `:memory:`, CSV one-shot depuis R2). Tables chaudes : `keywords` (jsonb lourds : `serp`, `briefs`, `metrics`, `gsc_by_country`, `trend`), `gsc_*_snapshots` (append-only quotidien), `domain_backlinks`.

## Actions retenues (classées)

| # | Action | Effort | Gain |
|---|--------|--------|------|
| 1 | **TOAST lz4** — `default_toast_compression='lz4'` + `ALTER COLUMN … SET COMPRESSION lz4` sur les 5 jsonb de `keywords` | XS (1 h) | Lectures/écritures jsonb nettement plus rapides, coût nul |
| 2 | **`wal_compression='lz4'`** + tuning `shared_buffers` (~25 % RAM) / `effective_cache_size` (~50-75 %) / `work_mem` (32 MB) | XS (30 min, 1 restart) | Moins d'I/O disque sur le VPS |
| 3 | **Hygiène worker DuckDB** — `SET memory_limit='1500MB'; SET threads=2; SET preserve_insertion_order=false;` | S (1 h) | L'import n'étrangle plus Postgres ; RAM bornée |
| 4 | **Gzipper les CSV à l'upload R2** (`.csv.gz` — httpfs/read_csv le lit nativement, et c'est *plus rapide* que le CSV nu : 107 s vs 121 s au microbenchmark officiel) | S (½ j) | Transfert R2 −70-80 % |
| 5 | **Cache applicatif des pages overview** — Next.js `use cache` + `revalidateTag` déclenché en fin d'import/sync — AVANT toute matview | S-M (½-1 j) | Pages agrégées instantanées, invalidation exacte |
| 6 | **Matviews + refresh schedulé depuis le worker Bun** — seulement si #5 ne suffit pas (agrégats > ~500 ms) ; pas de pg_cron (évite de customiser l'image Postgres) | M | Agrégats précalculés |
| 7 | **Partitionnement range mensuel `gsc_*_snapshots`** — différé ; déclencheur : > ~10-20 M lignes ou politique de rétention actée. Dès maintenant : garder `date` dans les PK/uniques (déjà le cas) | M (le jour venu) | Rétention par DROP partition |
| 8 | **TimescaleDB** (compression time-series 90 %+, licence Community OK en self-host) ou **Parquet zstd sur R2 + DuckDB long-lived + `cache_httpfs`** pour les dashboards historiques — différé ; déclencheur : les snapshots dominent le stockage ou les dashboards rament | L | Compression massive / analytics découplé |

## DDL / config de référence

```sql
-- #1 (migration Drizzle custom ; ne réécrit pas l'existant — seules les
-- nouvelles écritures prennent lz4 ; les refreshs serp/metrics migrent naturellement)
ALTER TABLE keywords
  ALTER COLUMN serp SET COMPRESSION lz4,
  ALTER COLUMN briefs SET COMPRESSION lz4,
  ALTER COLUMN metrics SET COMPRESSION lz4,
  ALTER COLUMN gsc_by_country SET COMPRESSION lz4,
  ALTER COLUMN trend SET COMPRESSION lz4;

-- audit : SELECT pg_column_compression(serp), count(*) FROM keywords GROUP BY 1;

-- #2 (ALTER SYSTEM, côté Dokploy ; restart requis pour shared_buffers)
ALTER SYSTEM SET default_toast_compression = 'lz4';
ALTER SYSTEM SET wal_compression = 'lz4';
ALTER SYSTEM SET shared_buffers = '2GB';          -- ~25 % d'un VPS 8 GB
ALTER SYSTEM SET effective_cache_size = '4GB';
ALTER SYSTEM SET work_mem = '32MB';
```

## Écarté sans regret (et pourquoi)

- **Columnar Postgres (Citus/Hydra)** : read-only de fait, communautés étroites, nos volumes ne le justifient pas — DuckDB le fait mieux sans toucher Postgres.
- **« Plugins de cache » Postgres** (pgpool query cache & co) : invalidation grossière, fragile ; le cache applicatif Next.js (invalidation par tag au moment où NOUS écrivons) est strictement supérieur.
- **Parquet pour les imports one-shot** : convertir = lire tout le CSV une fois de plus ; ne paie que si relectures/colonnes partielles. Le vrai quick win est le gzip (#4).
- **`cache_httpfs` / external file cache DuckDB** : zéro hit tant que le pattern est « 1 CSV lu 1 fois puis jeté » ; à ressortir avec l'option dashboards (#8).
- **MotherDuck** : coût récurrent + lock-in, contraire à la posture self-host.

## Notes de doc (vérifiées)

- lz4 dispo PG ≥ 14, images Docker officielles compilées `--with-lz4` ; décompression quasi gratuite vs pglz, et sur du jsonb réel lz4 sort parfois même plus petit.
- `SET COMPRESSION` ne recompresse pas l'existant (doc ALTER TABLE) ; `VACUUM FULL` non plus. Forçage éventuel : `UPDATE … SET col = col::text::jsonb` par batch.
- DuckDB ne compresse pas les instances `:memory:` — la compression native ne concerne que les fichiers `.duckdb`.
- Défauts DuckDB (`memory_limit` 80 % RAM, `threads` = tous les cœurs) pensés pour machine dédiée — dangereux sur VPS partagé, d'où #3.
- Partitionnement : seuil doc « table > RAM du serveur » ; on en est loin.