# seoinsight v2 — Données à grande échelle : stockage, recherche, analytique, vecteurs Date : 2026-08-28 Statut : validé en brainstorming (combinaison A, Dedibox Pro-11-XL-64G). Amende : `2026-08-26-seoinsight-v2-architecture-design.md` §8 (données) et §11 (points ouverts). ## 1. Le problème seoinsight v2 est un outil « all-in-one keywords » : imports massifs de mots-clés (Mangools, Semrush, SE Ranking, Ahrefs), snapshots Google Search Console quotidiens, crawls complets de sites concurrents (dizaines de milliers de pages), et à terme du regroupement sémantique, de la comparaison de contenus et un assistant IA. Les volumes à 12 mois sont **inconnus** ; il faut une architecture qui ne préjuge de rien et monte d'un cran sans refonte. ## 2. Faits dimensionnants (sources dans les recherches du 2026-08-28) | Donnée | Ordre de grandeur | Conséquence | |---|---|---| | Imports mots-clés | Exports plafonnés (Semrush 10–50 k lignes/export, Ahrefs 500 k lignes/mois, SE Ranking API 5 k/requête, Mangools 700/requête) → **1 à 20 M lignes en année 1**, 100 M seulement avec budgets API | Partitionner par `org_id` n'est pas nécessaire au départ ; index composites + `tsvector` suffisent. | | GSC | 50–250 k lignes/jour pour un site de 5 000 pages ; plafond API 50 k lignes/jour/type → segmentation pays/device | **Dizaines de millions de lignes/an** : partitionnement mensuel + BRIN sur la date, comme prévu. | | Crawl | HTML médian 22 Ko → 100 k pages ≈ 2,2 Go brut, ~0,5 Go compressé ; texte extrait quelques Ko/page | Le HTML brut **ne va pas dans Postgres**. | | Vecteurs (pgvector HNSW) | 10 M × 1 024 dims float32 = 40–80 Go RAM ; `halfvec` divise par 2 ; 1 M pages × 1 024 halfvec ≈ 2 Go + index | Vectoriser les **jeux par org/site**, jamais le réservoir global. | | Embeddings API | 50 k pages ≈ 2 $ (OpenAI `text-embedding-3-small`) à 18 $ ; 10 M mots-clés ≈ 1–6 $ | Coût négligeable ; le self-host CPU (bge-m3 / e5, ONNX int8) est une optimisation ultérieure. | | Dedibox retenue | Pro-11-XL-64G : EPYC 4545P 16C/32T, **64 Go**, 2 × 4 To NVMe, 3 Gbps | Postgres `shared_buffers` 16 Go, cache OS ~40 Go, **~5 M vecteurs halfvec 1024d** en RAM. | ## 3. Combinaison retenue : A — Postgres 18 « tout-en-un » + DuckDB embarqué + fichiers ``` ┌──────────────────────── Dedibox Pro-11-XL-64G ────────────────────────┐ │ FastAPI │ │ ├─ SQLAlchemy async ──▶ Postgres 18 (DATA) │ │ │ tables métier partitionnées, tsvector + pg_trgm, │ │ │ pgvector halfvec (colonne nullable, index à l'activation) │ │ ├─ DuckDB (librairie) ──▶ import CSV, agrégations sur Parquet, │ │ │ ATTACH Postgres pour lire/écrire │ │ └─ fichiers /var/lib/seoinsight/ │ │ crawls///pages/*.html.zst (HTML brut) │ │ parquet//… (analytique) │ └───────────────────────────────────────────────────────────────────────┘ ``` ### Postgres 18 (source de vérité) - **Mots-clés** : table `keywords` (par org : `org_id`, `keyword`, `locale`, `volume`, `kd`, `cpc`, `intent`, `serp_features`, `trend` (12 smallint), `source`, `imported_at`) avec `tsvector` généré (`to_tsvector('simple', keyword)`) + GIN, et `pg_trgm` GIN pour le flou ; unique `(org_id, locale, keyword)`. Pas de partitionnement tant que < 50 M lignes ; clé de partition prévue : `org_id` (hash) si dépassement. - **GSC** : `gsc_query_snapshots`, `gsc_page_snapshots` **partitionnées par mois** (`RANGE (date)`), BRIN sur `date`, B-tree `(site_id, date)`. Partitions créées 3 mois à l'avance par un cron (SQL, pas `pg_partman`). - **Crawl** : `crawl_runs`, `crawl_pages` (champs SEO structurés : url, status, title, h1, meta, canonical, word_count, liens internes/externes en `int`, `text_hash`, `content_path`) ; le texte extrait (trafilatura) en `text` compressé par TOAST (`lz4`) ; le HTML brut sur disque. - **Vecteurs** : colonnes `embedding halfvec(1024)` **nullables** sur `keywords` et `crawl_pages`, **sans index** tant que la feature n'est pas livrée ; index HNSW (`halfvec_cosine_ops`, `m=16`, `ef_construction=64`) créé `CONCURRENTLY` à l'activation, `maintenance_work_mem` ≥ 2 Go. Filtrage par `org_id`/`site_id` en `WHERE` (index partiels par org si besoin). - **Recherche texte** : `tsvector` pour « contient ces mots », `pg_trgm` pour « ressemble à ». Seuil : si p95 > 200 ms au-delà de 10 M mots-clés, évaluer `pg_search` (BM25) ou `pg_textsearch` avant tout moteur externe. ### DuckDB (librairie Python, jamais un serveur) - **Imports CSV** : `read_csv` (5 M lignes ≈ 3 s) → normalisation (colonnes hétérogènes des 4 outils vers le schéma `keywords`) → `COPY` vers Postgres via `ATTACH` (extension `postgres`) ou `COPY FROM STDIN` asyncpg. Tout se passe dans le worker de jobs, jamais dans une requête HTTP. - **Analytique** : agrégations lourdes (tendances multi-mois GSC, comparatifs de corpus) sur des **exports Parquet** produits par le worker (`parquet/gsc//.parquet`), lus par DuckDB ; résultats matérialisés dans Postgres (tables d'agrégats) pour l'API. Pas de `duckdb_engine` SQLAlchemy (pas d'async) : DuckDB est appelé directement dans le worker, en thread. - **Exclus** : MotherDuck (cloud payant), `vss` (index non persistants), `pg_duckdb` (à réévaluer quand stable en prod, pas avant). ### Fichiers - `/var/lib/seoinsight/crawls///.html.zst` (zstd niveau 3) ; rétention : N derniers runs par site (paramètre org) ; sauvegarde avec le reste de la Dedibox. - Parquet uniquement pour l'analytique, régénérable depuis Postgres : jamais source de vérité. ### Embeddings - Au départ : API (`text-embedding-3-small`, 1 536 → tronqué à 1 024 dims via Matryoshka, ou `voyage-3.5`) appelée par le worker, par lots, avec cache par `text_hash`. Clé en env, coût suivi par org. - Plus tard, si volume ou confidentialité l'exigent : `bge-m3` / `multilingual-e5-large` ONNX int8 sur la Dedibox (AVX-512), même interface `embed(texts) -> list[halfvec]`. ## 4. Seuils de bascule (écrits pour ne pas être devinés plus tard) | Signal mesuré | Action | |---|---| | > 3 M vecteurs indexés ou index HNSW > 20 Go | Passer à **VectorChord** (RaBitQ) ou réduire les dims (jina v3 256) ; Qdrant seulement si p95 > 100 ms persiste après ça. | | Recherche mots-clés p95 > 200 ms à > 10 M lignes | `pg_search` / `pg_textsearch` (BM25 dans Postgres) avant Meilisearch/Typesense. | | `keywords` > 50 M lignes ou > 100 Go | Partitionnement hash par `org_id`. | | Agrégation GSC > 30 s en SQL malgré index/partitions, ou GSC > ~100 M lignes | D'abord table d'agrégats (matérialisée par le worker) ; si insuffisant, rouvrir la question analytique avec les mesures (§8 : ClickHouse serveur serait le premier candidat). | | Import CSV > 10 min | Paralléliser par fichier dans le worker, pas de nouveau composant. | | RAM Postgres saturée | Variante 128 Go du même châssis avant tout second serveur. | ## 5. Ce que cela impose aux specs de modules - **Sites** : rien de nouveau ; `site_id` est la clé de tout le reste. - **Keywords** : schéma ci-dessus, import via worker + DuckDB, recherche `tsvector` + `pg_trgm`, colonne `embedding` nullable. - **GSC** : partitions mensuelles + cron de création, BRIN, segmentation pays/device pour rester sous 50 k lignes/jour/type. - **Pages / crawl** : crawler HTTP-first (Crawlee Python ou spider-rs si un crate Rust est repris), respect `robots.txt`, HTML zstd sur disque, trafilatura pour le texte, champs SEO en Postgres, `embedding` nullable. - **Jobs** : table `jobs` + worker (spec architecture §7) — c'est lui qui porte imports, crawls, embeddings, exports Parquet. ## 6. Hors périmètre / écarté - Qdrant, Milvus, Weaviate, LanceDB au départ (B) — réévalués sur seuil. - Lakehouse DuckDB/Parquet comme source de vérité (C) — pas d'index secondaires ni de concurrence pour une application. - MotherDuck — cloud payant. - Elasticsearch / OpenSearch — surdimensionnés pour la recherche de mots-clés. - chDB (ClickHouse embarqué) — pas d'async, mono-process ; n'apporte rien de plus que DuckDB pour l'import. - ClickHouse — analysé et non retenu (§8) : surdimensionné pour une boîte à outils mots-clés ; réévaluable uniquement sur mesure. ## 7. Ce qui compte vraiment : modèle canonique, adaptateurs, une seule API Le produit est une **boîte à outils mots-clés** : importer (CSV énormes, APIs), organiser, filtrer, comparer, exposer. Les moteurs sont secondaires ; la structure qui doit être juste dès le départ est celle-ci. - **Modèle canonique** (Postgres) : `keywords` = une ligne par `(org_id, locale, keyword)` avec les attributs stables ; `keyword_metrics` = une ligne par `(keyword_id, source, fetched_at)` avec les métriques **telles que fournies par la source** (`volume`, `kd`, `cpc`, `intent`, `serp_features`, `trend`, `raw jsonb`). On ne fusionne jamais deux sources en écrasant : on garde la provenance, et une vue « consolidée » choisit la valeur affichée (règle par org : source préférée, plus récente…). Organisation : `keyword_lists` (listes/sets par org), `keyword_list_items`, `tags`. Comparaison = requêtes SQL sur ce modèle, pas une brique dédiée. - **Adaptateurs de sources** (`api/app/keywords/sources/.py`) : un module par source — Mangools, SEObserver, Keysearch, SE Ranking, Keywords Everywhere, LowFruits, DataForSEO, SerpApi, plus les CSV Semrush/Ahrefs — avec une seule interface : `fetch(...) -> Iterable[CanonicalKeywordRow]` ou `parse_csv(path) -> Iterable[CanonicalKeywordRow]`. Chaque adaptateur porte ses quotas, son mapping de colonnes et ses tests sur un fichier d'exemple. Ajouter une source = ajouter un fichier. - **Pipeline d'import** = un job (table `jobs` + worker) : lecture (DuckDB pour les CSV, `httpx` pour les APIs) → normalisation → `COPY` par lots → rapport (lignes lues, insérées, ignorées, erreurs). Idempotent par `(source, fetched_at)`. - **Une seule API** (FastAPI) consommée par le dashboard Next, par les MCP et par le SDK IA du dashboard (v2–v3) : mêmes endpoints, mêmes filtres (`?list=`, `?source=`, `?volume_min=`, `?q=` texte), même pagination par curseur, même `Principal` (JWT ou clé API). Aucun chemin « spécial IA » : le MCP et le SDK IA sont des clients comme les autres. - **Volumes** : ce modèle tient des dizaines de millions de lignes en Postgres avec les index du §3 ; les seuils du §4 restent des garde-fous, pas des étapes prévues. ## 8. ClickHouse — analysé (2026-08-28), non retenu Faits (ClickHouse 26.8 LTS, Apache 2.0 complet en self-host) : agrégations à 100 M lignes en 10–100 ms contre 10–30 s en Postgres ; compression 5–10× (30×+ sur colonnes basse cardinalité, DoubleDelta + ZSTD sur les séries temporelles) ; `UPDATE` légers 1 000× plus rapides qu'avant (patchs), lightweight `DELETE`, mais toujours **ni unicité, ni transactions, ni FK** ; full-text GA (mars 2026) explicitement « pas un moteur de pertinence » (ni BM25 ni TF-IDF) ; HNSW (usearch) hors expérimental depuis 26.4, index en RAM reconstruit depuis les parts, sans benchmark officiel ; single-node à 8–16 Go RAM plafonnable (`max_server_memory_usage`) ; `BACKUP … TO Disk` incrémental, TTL natif, `/metrics` Prometheus ; Python : `clickhouse-connect` officiel avec `AsyncClient`, table function `postgresql()` (SELECT/INSERT) pour lire Postgres, `pg_clickhouse` (FDW) dans l'autre sens. Verdict : **surdimensionné pour ce produit**. Le besoin (importer, organiser, filtrer, comparer des listes de mots-clés ; snapshots GSC par site) est servi par Postgres jusqu'à des dizaines de millions de lignes avec des index corrects. ClickHouse n'apporterait que de l'analytique inter-sites à très grande échelle, au prix d'un second serveur (8–16 Go de RAM, sauvegardes, merges en tâche de fond) et d'un second modèle de données. Il n'est donc **ni planifié ni « successeur désigné »** : si un jour une mesure montre qu'une agrégation Postgres bien indexée dépasse les seuils du §4, on rouvrira la question avec les chiffres — ClickHouse serveur sera alors le premier candidat, jamais chDB (pas d'async) et jamais pour les vecteurs ou la recherche mots-clés.