PostgreSQL vs SQLite : recherche en texte intégral — quand SQLite vous surprend (et quand il ne le fait pas)

Le cadre de décision : ce que vous choisissez réellement

« PostgreSQL vs SQLite » n’est pas la bonne question. La question est :
où voulez-vous que la complexité vive ?
SQLite place la complexité dans le processus applicatif et le système de fichiers. PostgreSQL la place dans un serveur avec des réglages, de l’introspection et des frontières nettes.

Pour la recherche en texte intégral (FTS), la décision réelle dépend de :

• Modèle de concurrence : SQLite est excellent tant que la contention d’écriture n’est pas votre personnalité. PostgreSQL est conçu pour les écritures concurrentes.
• Classement et linguistique : les deux peuvent être « assez bons », mais PostgreSQL vous donne plus de contrôle structuré ; SQLite vous donne de la rapidité et de la portabilité.
• Visibilité opérationnelle : Postgres dispose de statistiques mûres, de plans de requête et d’instrumentation serveur. SQLite vous oblige à être votre propre DBA.
• Cycle de vie des données : sauvegardes, réplication, gestion de la corruption et évolution de schéma diffèrent quand la base est un fichier.
• Confinement des pannes : un processus SQLite bloqué en écriture peut paralyser votre appli. Une requête Postgres bloquée peut être tuée sans tuer tout le processus.

Conseil orienté :
Si votre appli est mononœud, peu écrite et que vous voulez la simplicité embarquée, SQLite FTS5 est un choix légitime.
Si vous avez besoin de concurrence multi‑écrivains, de maintenance en ligne et d’introspection robuste, utilisez PostgreSQL et n’en soyez pas gêné.

Une citation qui reste dans l’esprit des ops depuis des décennies, et qui s’applique ici :
« L’espoir n’est pas une stratégie. » — Vince Lombardi

Faits et histoire qui comptent plus que les benchs

Les benchmarks sont amusants jusqu’à ce qu’ils deviennent de la passation de marché. Voici des points concrets qui affectent les systèmes réels.
(C’est la partie où quelqu’un dit « mais mon test sur portable… » et vous fermez l’onglet en silence.)

1. SQLite est plus ancien que beaucoup de stacks « entreprise ». Il a démarré autour de 2000, conçu pour la fiabilité et l’intégration sans administration.
2. FTS de SQLite n’est pas une seule chose. FTS3/4 sont venus plus tôt ; FTS5 est plus récent et propose plus d’extensibilité et des fonctions comme le classement BM25 intégré.
3. La recherche en texte intégral de PostgreSQL a été disponible bien avant d’être à la mode. tsearch2 existait comme module externe au début des années 2000 ; il a été intégré au noyau PostgreSQL plus tard.
4. La contrainte de concurrence de SQLite est architecturale. Ce n’est pas que c’est « lent » ; c’est qu’un fichier de base unique a une sémantique de verrouillage qui peut goulotter les écrivains.
5. Le mode WAL a été un grand pas pour SQLite. Il a amélioré la concurrence lecture/écriture en séparant le journal d’écriture du fichier principal de la base.
6. L’index GIN de PostgreSQL a rendu la recherche textuelle pratique à grande échelle. GIN est conçu pour les types de données « contiennent beaucoup de clés », comme les termes tsvector.
7. Les deux systèmes tokenisent ; aucun n’est « Google ». Vous gérerez le stemming, les stopwords et les synonymes vous‑même, et on vous en tiendra responsable quoi qu’il en soit.
8. SQLite est célèbre pour ses tests. Sa suite de tests est intense et de longue durée ; c’est une des raisons pour lesquelles on lui fait confiance en embarqué.
9. PostgreSQL est une plateforme, pas seulement une base. Extensions, dictionnaires personnalisés et tâches côté serveur changent ce que peut être la « recherche en texte intégral ».

Blague #1 (courte, pertinente) : La recherche en texte intégral est l’endroit où le produit découvre que le langage est ambigu, et où l’ingénierie découvre que le produit l’est aussi.

Comment fonctionnent réellement SQLite FTS5 et PostgreSQL tsearch

SQLite FTS5 : un index inversé vivant dans un fichier

FTS5 stocke un index inversé : termes → listes d’IDs de lignes (et positions, selon les options).
La « table » que vous interrogez n’est pas une table normale ; c’est une table virtuelle soutenue par des structures d’index FTS.
Vous écrivez des lignes, elle tokenize le texte avec un tokenizer (comme unicode61), et met à jour l’index.

Le côté agréable : c’est rapide, portable et embarqué avec votre appli.
Le côté piquant : la maintenance de l’index peut être intensive en écritures, et si vous mal configurez le journal ou le checkpointing, vos latences de queue apparaissent à l’endroit le plus problématique : les requêtes utilisateur.

PostgreSQL tsearch : lexèmes, dictionnaires et index

La recherche textuelle de PostgreSQL tourne autour de tsvector (le document comme termes normalisés avec positions) et de tsquery (la requête).
Vous pouvez construire un tsvector à partir de colonnes à la volée, mais en production vous le stockez généralement (colonne générée ou trigger) et l’indexez avec GIN.

Postgres ajoute de la structure : configurations, dictionnaires, stopwords et pondérations.
Cette structure le rend plus contrôlable pour la recherche multilingue ou spécifique à un domaine, mais elle vous donne aussi plus de façons de mal le configurer.

Classement : BM25-ish vs ts_rank

SQLite FTS5 inclut une fonction de classement BM25 ; ce n’est pas magique, mais c’est raisonnablement aligné sur ce que les gens attendent d’un moteur de recherche.
PostgreSQL fournit ts_rank et apparentés ; avec des poids et une normalisation vous pouvez obtenir de bons résultats, mais vous devrez tuner.

Traduction : si vous voulez « un classement correct dès la sortie de la boîte », SQLite peut vous surprendre.
Si vous voulez « un classement que vous pouvez expliquer à votre équipe juridique », PostgreSQL vous donne plus de leviers.

Quand SQLite vous surprend (dans le bon sens)

1) Recherche embarquée avec faible charge opérationnelle

Vous déployez un binaire unique (ou un conteneur) et un seul fichier de base.
Vous n’avez pas besoin d’un serveur BD, d’un pool de connexions, de migrations entre nœuds ou de discovery de services.
Pour des outils internes, des applications de bureau, des appareils en périphérie et des déploiements mono‑locataire, SQLite FTS5 est un cadeau.

2) Charges majoritairement en lecture avec données locales

Si votre pattern d’accès est surtout des lectures et des écritures batch occasionnelles, SQLite en mode WAL peut être très rapide.
IO local, pas de saut réseau, pas de contention serveur et un moteur de requête mature.

3) Petits à moyens corpus avec requêtes raisonnables

Pour des dizaines de milliers à quelques millions de documents (selon la taille des documents et le matériel), SQLite FTS5 peut sembler « trop rapide pour être réel ».
C’est réel. C’est aussi facile à dépasser si vous ajoutez :
des mises à jour fréquentes, plusieurs écrivains ou une forte concurrence de requêtes sous un régime de verrouillage de fichier unique.

4) Indexation portable et environnements de test reproductibles

Livrer un jeu de données reproductible pour les tests d’intégration est plus simple quand c’est un fichier.
Si vous avez déjà essayé de déboguer des différences de classement entre staging et prod à cause d’une dérive de configuration Postgres, vous savez pourquoi cela compte.

Quand SQLite ne vous surprend pas (et fait mal de toute façon)

Concurrence : le goulot d’écrivain

SQLite peut faire des lectures concurrentes avec WAL, mais les écritures restent sérialisées.
Pour FTS, les écritures ne sont pas de « simples insertions » ; elles mettent aussi à jour l’index. Sous une charge d’écriture soutenue, vous verrez des attentes de verrou et des pics de latence tail.

Si vous avez besoin de plusieurs instances applicatives écrivant sur la même BD, vous allez rencontrer une rude discussion avec la physique.
Oui, vous pouvez placer le fichier sur un filesystem réseau. Non, vous ne devriez pas, sauf si vous aimez le comportement indéfini comme hobby.

Contrôle opérationnel et introspection

Postgres vous donne pg_stat_statements, des logs de requêtes lentes, EXPLAIN (ANALYZE, BUFFERS), le comportement de vacuum en arrière‑plan, et plus.
SQLite vous donne des pragmas et ce que vous branchez comme tracing. C’est acceptable — jusqu’à ce que votre commandant d’incident demande « qu’est‑ce qui a changé ? ».

Mises à jour points chauds et comportement de fusion

Les mises à jour fréquentes d’un même ensemble de documents peuvent fragmenter les structures d’index FTS et augmenter le travail de fusion.
Si vous ne planifiez pas la maintenance (ou si vous la planifiez en période de pointe), vous pouvez vous infliger un DoS auto‑infligé.

Pièges d’exactitude : tokenization et locale

« Pourquoi ‘résumé’ ne correspond‑il pas à ‘resume’ ? » n’est pas une question philosophique ; c’est une question de configuration du tokenizer.
Les tokenizers de SQLite sont configurables mais pas infinis. Les configurations de recherche textuelle de PostgreSQL peuvent être plus nuancées, surtout avec des dictionnaires personnalisés.

Blague #2 (courte, pertinente) : la concurrence SQLite, c’est comme un petit café : l’expresso est excellent, mais un seul barista peut faire mousser le lait à la fois.

Ce que PostgreSQL vous apporte que SQLite n’apportera pas

Concurrence multi‑utilisateurs robuste et isolation

PostgreSQL est conçu pour de nombreuses sessions effectuant des lectures et écritures simultanées, avec MVCC pour empêcher lecteurs et écrivains de se marcher sur les pieds.
Si vous exécutez la recherche dans une appli web avec plusieurs workers et jobs en arrière‑plan, cela compte tout de suite.

Options d’indexation et planification de requête prévisible

Avec PostgreSQL vous pouvez :

• Persister tsvector et l’indexer avec GIN.
• Utiliser des index partiels pour restreindre la recherche aux documents « actifs ».
• Utiliser des colonnes générées pour éviter les triggers.
• Régler GIN (comme fastupdate) et surveiller la fragmentation.

Meilleurs outils pour « le lendemain du lancement »

La recherche textuelle devient politique après le lancement.
Quelqu’un demandera « pourquoi ce résultat est au‑dessus de celui‑là ? » ou « peut‑on exclure les docs internes ? »
La visibilité de PostgreSQL sur les plans, les stats et la maintenance en arrière‑plan facilite le débogage sans deviner.

Mais : Postgres n’est pas non plus un moteur de recherche

Si vous avez besoin de correspondance approximative, tolérance aux fautes de frappe, synonymes à grande échelle, analytics à la volée ou indexation distribuée, vous vous dirigez vers un système de recherche dédié.
Utilisez SQLite/Postgres FTS quand vous voulez une recherche native en base, pas une deuxième pile d’infrastructure.

Tâches pratiques : commandes, sorties et décisions

Ce sont de vraies tâches « que fais‑je à 2h du matin ». Chacune inclut une commande, une sortie représentative, ce que cela signifie, et la décision à prendre.
Les commandes sont exécutables ; adaptez les chemins, noms de base et utilisateurs à votre environnement.

Tâche 1 : Confirmer le mode journal SQLite (WAL ou non)

cr0x@server:~$ sqlite3 app.db "PRAGMA journal_mode;"
wal

Ce que cela signifie : wal active une meilleure concurrence lecture/écriture que delete ou truncate.

Décision : Si ce n’est pas wal et que vous avez des lectures concurrentes pendant des écritures, passez en WAL et retestez la latence des queues.

Tâche 2 : Vérifier le busy timeout SQLite (éviter le « database is locked » immédiat)

cr0x@server:~$ sqlite3 app.db "PRAGMA busy_timeout;"
0

Ce que cela signifie : 0 signifie que les appels échouent immédiatement en cas de contention de verrou.

Décision : Définissez un timeout non nul dans la connexion applicative (ou PRAGMA) si une brève contention est attendue ; sinon corrigez la contention des écrivains à la source.

Tâche 3 : Vérifier que la table FTS5 existe et est bien FTS5

cr0x@server:~$ sqlite3 app.db ".schema docs_fts"
CREATE VIRTUAL TABLE docs_fts USING fts5(title, body, content='docs', content_rowid='id', tokenize = 'unicode61');

Ce que cela signifie : C’est une table virtuelle FTS5 avec contenu externe.

Décision : Si vous utilisez du contenu externe, assurez‑vous que les triggers ou processus de rebuild la tiennent synchronisée, sinon vous servirez des résultats périmés/vides.

Tâche 4 : Exécuter une vérification d’intégrité FTS5

cr0x@server:~$ sqlite3 app.db "INSERT INTO docs_fts(docs_fts) VALUES('integrity-check');"

Ce que cela signifie : L’absence de sortie signifie généralement succès. Les erreurs indiquent une corruption d’index ou un décalage.

Décision : Si cela échoue, planifiez une reconstruction (rebuild) et scrutez les problèmes de filesystem/IO et les terminaisons brutales de processus.

Tâche 5 : Rebuild d’un index FTS5 avec contenu externe (maintenance planifiée)

cr0x@server:~$ sqlite3 app.db "INSERT INTO docs_fts(docs_fts) VALUES('rebuild');"

Ce que cela signifie : FTS5 reconstruit l’index à partir de la table de contenu.

Décision : Planifiez cela en dehors des pics ; si le temps de rebuild est inacceptable, vous êtes probablement au point où Postgres (ou un moteur de recherche) devient plus raisonnable.

Tâche 6 : Vérifier si SQLite checkpointe le WAL agressivement (ou pas du tout)

cr0x@server:~$ sqlite3 app.db "PRAGMA wal_checkpoint(TRUNCATE);"
0|0|0

Ce que cela signifie : La sortie est busy|log|checkpointed. Tous zéros signifie qu’il n’y a pas de trames en attente.

Décision : Si log grossit énormément en production, ajoutez un checkpoint contrôlé (au niveau applicatif) et surveillez la latence IO.

Tâche 7 : Inspecter la taille de page SQLite et les hypothèses de cache size

cr0x@server:~$ sqlite3 app.db "PRAGMA page_size; PRAGMA cache_size;"
4096
-2000

Ce que cela signifie : pages de 4KB ; cache_size négatif signifie unités en KB (ici ~2 000KB).

Décision : Si votre charge est fortement en lecture et que vous thrashz le disque, augmentez la taille du cache sur la connexion applicative, mais validez l’impact mémoire par processus.

Tâche 8 : Identifier les erreurs « database is locked » dans les logs applicatifs

cr0x@server:~$ journalctl -u app.service --since "1 hour ago" | grep -i "database is locked" | tail
Dec 30 01:12:40 host app[2219]: sqlite error: database is locked (SQLITE_BUSY) on INSERT INTO docs_fts ...
Dec 30 01:12:41 host app[2219]: sqlite error: database is locked (SQLITE_BUSY) on UPDATE docs SET ...

Ce que cela signifie : Vous avez de la contention d’écrivains ou des transactions longues qui gardent des verrous.

Décision : Raccourcissez les transactions, déplacez les mises à jour d’index vers une file d’un seul écrivain, ou cessez de prétendre que le fichier est un serveur de base de données multi‑écrivains.

Tâche 9 : Trouver de gros fichiers WAL (symptôme de checkpointing ou rafales d’écritures)

cr0x@server:~$ ls -lh app.db app.db-wal app.db-shm
-rw-r--r-- 1 app app  12G Dec 30 01:10 app.db
-rw-r--r-- 1 app app 3.8G Dec 30 01:12 app.db-wal
-rw-r--r-- 1 app app  32K Dec 30 00:55 app.db-shm

Ce que cela signifie : Le WAL est énorme ; le coût de recovery/checkpoint et la pression disque arrivent.

Décision : Enquêtez sur des lecteurs longs empêchant les checkpoints, ajoutez des checkpoints périodiques et vérifiez la marge disque ; sinon vous « découvrirez » ENOSPC en pleine pointe.

Tâche 10 : PostgreSQL : confirmer votre configuration de recherche textuelle

cr0x@server:~$ psql -d app -c "SHOW default_text_search_config;"
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

Ce que cela signifie : Votre parsing/stemming par défaut est en anglais.

Décision : Si vous avez du contenu multilingue, ne laissez pas cela par défaut et espérez. Choisissez une config par document ou stockez plusieurs vecteurs.

Tâche 11 : PostgreSQL : vérifier que vous avez le bon type d’index pour tsvector

cr0x@server:~$ psql -d app -c "\d+ docs"
                                                   Table "public.docs"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | bigint  |           | not null |         | plain    |             |              |
 title  | text    |           |          |         | extended |             |              |
 body   | text    |           |          |         | extended |             |              |
 tsv    | tsvector|           |          |         | extended |             |              |
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)
    "docs_tsv_gin" gin (tsv)

Ce que cela signifie : Vous avez un index GIN sur tsv. Bien.

Décision : Si cet index manque, ajoutez‑le avant de blâmer Postgres pour être « lent ».

Tâche 12 : PostgreSQL : inspecter le plan réel d’une requête de recherche

cr0x@server:~$ psql -d app -c "EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM docs WHERE tsv @@ plainto_tsquery('english','refund policy') ORDER BY ts_rank(tsv, plainto_tsquery('english','refund policy')) DESC LIMIT 20;"
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..35.71 rows=20 width=8) (actual time=4.112..4.168 rows=20 loops=1)
   Buffers: shared hit=812
   ->  Index Scan using docs_tsv_gin on docs  (cost=0.42..271.08 rows=152 width=8) (actual time=4.110..4.156 rows=20 loops=1)
         Index Cond: (tsv @@ plainto_tsquery('english'::regconfig, 'refund policy'::text))
 Planning Time: 0.214 ms
 Execution Time: 4.242 ms
(6 rows)

Ce que cela signifie : Il utilise l’index GIN ; les buffers sont en hit (en cache). 4ms est sain.

Décision : Si vous voyez un scan séquentiel, vous avez probablement oublié l’index, utilisé une fonction qui empêche l’utilisation de l’index, ou la requête a une très faible sélectivité.

Tâche 13 : PostgreSQL : vérifier si l’autovacuum suit la cadence (la bloat affecte aussi GIN)

cr0x@server:~$ psql -d app -c "SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
 relname | n_dead_tup |        last_autovacuum
---------+------------+-------------------------------
 docs    |     842113 | 2025-12-30 00:41:12+00
 events  |      12044 | 2025-12-29 23:58:06+00
(2 rows)

Ce que cela signifie : Beaucoup de tuples morts dans docs ; mises à jour/suppressions s’accumulent.

Décision : Réglez l’autovacuum pour cette table, réduisez le churn des updates, ou acceptez que les index de recherche gonflent et ralentissent avec le temps.

Tâche 14 : PostgreSQL : trouver les requêtes les plus lentes par temps total (si pg_stat_statements est activé)

cr0x@server:~$ psql -d app -c "SELECT calls, total_exec_time::int AS total_ms, mean_exec_time::numeric(10,2) AS mean_ms, rows, left(query,80) AS q FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
 calls | total_ms | mean_ms | rows |                                        q
-------+----------+---------+------+--------------------------------------------------------------------------------
 10213 |   912345 |   89.33 |  0   | SELECT id FROM docs WHERE tsv @@ plainto_tsquery($1,$2) ORDER BY ts_rank...
  1400 |   221100 |  157.92 |  1   | UPDATE docs SET body = $1, tsv = to_tsvector($2, $1) WHERE id = $3
(2 rows)

Ce que cela signifie : Votre requête de recherche domine le temps total d’exécution ; le chemin de mise à jour est aussi lourd.

Décision : Décidez d’optimiser la requête/le classement, de cacher les résultats, de précalculer les vecteurs, ou de réduire la fréquence des mises à jour.

Tâche 15 : Vérifier la saturation IO sur Linux (c’est généralement le disque, pas votre tokenizer)

cr0x@server:~$ iostat -xz 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.41    0.00    6.02   21.15    0.00   60.42

Device            r/s     w/s   rkB/s   wkB/s  await  %util
nvme0n1        112.0   980.0  8200.0 65200.0  18.40  97.50

Ce que cela signifie : Le disque est presque saturé ; l’attente moyenne est élevée. La latence de recherche suivra cela.

Décision : Arrêtez de tuner les fonctions de classement et commencez à réparer l’IO : stockage plus rapide, réduire l’amplification d’écriture, regrouper les écritures, ou déplacer la recherche hors du disque chaud.

Tâche 16 : Vérifier la pression sur les descripteurs de fichiers (les applis en processus SQLite peuvent atteindre des limites)

cr0x@server:~$ cat /proc/$(pgrep -n app)/limits | grep "Max open files"
Max open files            1024                 1048576              files

Ce que cela signifie : La limite souple est 1024 ; votre appli peut l’atteindre avec logs, sockets et fichiers BD.

Décision : Relevez les limites si vous observez des erreurs IO intermittentes ; c’est ennuyeux, mais cela évite de faux « problèmes de base de données ».

Recette de diagnostic rapide

La recherche est lente ou il y a des timeouts. Vous n’avez pas le temps d’écrire une thèse.
Faites ceci dans l’ordre. Arrêtez quand vous trouvez le cratère fumant.

Première étape : décidez si vous êtes CPU‑bound, IO‑bound ou lock‑bound

• IO‑bound : haut %iowait, %util disque proche de 100%, WAL qui grossit, fsyncs lents.
• CPU‑bound : CPU utilisateur élevé, IO stable, tokenization/classement lourds, grands ensembles triés.
• Lock‑bound : « database is locked » (SQLite), transactions longues (Postgres), écrivains bloqués.

Deuxième étape : vérifiez que l’index est utilisé

• SQLite : confirmez que vous utilisez docs_fts MATCH ? et que vous ne faites pas LIKE '%term%' sur la table de contenu.
• Postgres : EXPLAIN (ANALYZE, BUFFERS) ; cherchez l’utilisation de l’index GIN, pas un seq scan.

Troisième étape : vérifiez l’amplification des écritures et la maintenance en arrière‑plan

• SQLite : taille du WAL, fréquence des checkpoints, comportement de rebuild/merge FTS.
• Postgres : retard d’autovacuum, tuples morts, comportement de la liste en attente GIN, bloat.

Quatrième étape : inspectez la forme de la requête et le classement

• Triez‑vous des milliers de correspondances pour n’en retourner que 20 ?
• Faites‑vous des requêtes préfixes partout ?
• Calculez‑vous to_tsvector à la volée au lieu d’un vecteur stocké ?

Cinquième étape : décidez si l’architecture est mauvaise

Si le goulot est « beaucoup d’écrivains » et que vous êtes sur SQLite, le tuning est un mécanisme d’adaptation.
Si le goulot est « la recherche est désormais une fonctionnalité produit avec des SLA », Postgres peut suffire, mais un moteur de recherche dédié peut devenir inévitable.

Erreurs courantes : symptôme → cause → correction

1) Symptomatique : pics « database is locked » en période de pointe

Cause racine : Plusieurs écrivains ou transactions longues dans SQLite ; les mises à jour d’index FTS amplifient les écritures.

Correction : Pattern d’un seul écrivain (file), raccourcir les transactions, définir busy_timeout, activer WAL, passer à Postgres si le multi‑writer est réel.

2) Symptomatique : résultats de recherche manquants pour des documents récemment mis à jour

Cause racine : table FTS à contenu externe désynchronisée (triggers manquants, échoués, ou désactivés pendant un bulk load).

Correction : Recréez les triggers, exécutez le rebuild FTS5, ajoutez des checks d’invariants en CI (comptage des mismatches), et alertez sur la dérive.

3) Symptomatique : requête Postgres de recherche utilise un seq scan

Cause racine : Pas d’index GIN sur tsvector, ou la requête enveloppe tsvector dans une fonction, ou termes à faible sélectivité.

Correction : Persistez le tsvector, ajoutez un index GIN, utilisez les bons opérateurs (@@), envisagez des index partiels, ajustez la config/stopwords.

4) Symptomatique : le classement semble aléatoire ou « pire qu’avant »

Cause racine : changements de tokenizer/dictionnaire, modification de la liste de stopwords, mélange de langues, ou passage de requêtes de phrase à sac de mots.

Correction : Versionnez votre configuration de recherche, écrivez des tests de régression avec requêtes goldens, et traitez le classement comme un contrat d’API.

5) Symptomatique : l’usage disque augmente régulièrement et les performances se dégradent

Cause racine : bloat Postgres (tuples morts), bloat GIN, WAL SQLite non checkpointé, mises à jour fréquentes de gros champs texte.

Correction : Ajustez l’autovacuum ; réduisez le churn des mises à jour ; pour SQLite, gérez les checkpoints ; pour Postgres, envisagez REINDEX/ventes de maintenance périodiques si nécessaire.

6) Symptomatique : « ça marche sur ma machine » mais échoue en prod avec du texte non ASCII

Cause racine : règles de tokenization, collations ou configurations de recherche textuelle différentes entre environnements.

Correction : Figez explicitement le tokenizer/la configuration ; créez des cas de test pour l’Unicode et la ponctuation ; arrêtez de compter sur les valeurs par défaut.

7) Symptomatique : pics de latence toutes les quelques minutes

Cause racine : checkpointing SQLite ou rafales de maintenance FTS ; autovacuum Postgres ou pression IO due à des workloads non liés.

Correction : Corrélez avec logs/métriques ; planifiez la maintenance ; isolez le stockage ; ajoutez du jitter/backoff aux écrivains par lots.

8) Symptomatique : la recherche renvoie trop de correspondances non pertinentes

Cause racine : construction de requête trop permissive (OR omniprésent), pas de pondération de champs, pas de recherche de phrase, stopwords trop agressifs.

Correction : Pondérez titre vs corps (pondérations Postgres ; colonnes séparées et ajustements de classement SQLite), ajoutez des requêtes de phrase pour les motifs courants, peaufinez les stopwords.

Listes de contrôle / plan étape par étape

Choisir SQLite FTS5 : la checklist « ne pas être appelé »

1. Activez le mode WAL et vérifiez qu’il reste activé lors des déploiements.
2. Définissez busy_timeout (ou retry applicatif avec jitter) délibérément.
3. Utilisez un pattern d’un seul écrivain si vous avez des écritures fréquentes ; traitez les mises à jour FTS comme lourdes en écriture.
4. Définissez une politique de checkpoint ; surveillez la taille du WAL et la marge disque.
5. Si vous utilisez du contenu externe, maintenez les triggers et ajoutez une vérification d’intégrité périodique.
6. Figez la configuration du tokenizer et testez les cas Unicode.
7. Ayez un plan de rebuild (créez‑l et mesurez le temps) ; ne découvrez pas que la reconstruction prend des heures pendant un incident.
8. Sauvegardes : snapshottez le fichier DB en sécurité (et comprenez ce que « en sécurité » signifie en mode WAL).

Choisir PostgreSQL tsearch : la checklist « rendre ça ennuyeux »

1. Persistez tsvector (colonne générée ou trigger) et ajoutez un index GIN.
2. Choisissez default_text_search_config intentionnellement ; ne laissez pas le contenu multilingue au hasard.
3. Utilisez EXPLAIN (ANALYZE, BUFFERS) pour confirmer l’utilisation d’index avant de tuner quoi que ce soit.
4. Activez pg_stat_statements pour identifier les vraies requêtes coûteuses.
5. Réglez l’autovacuum pour les tables qui tournent ; surveillez tuples morts et bloat.
6. Décidez de votre fonction de classement et figez‑la derrière une interface stable.
7. Planifiez les migrations (changements de config, dictionnaire) comme des rollouts versionnés.

Migrer de SQLite FTS vers PostgreSQL tsearch : étape par étape

1. Inventoriez le comportement de tokenization (ce qui correspond aujourd’hui) et écrivez une suite de régression de requêtes et de résultats attendus.
2. Exportez les documents et IDs ; conservez des identifiants stables pour comparer les résultats entre systèmes.
3. Dans Postgres, créez le tsvector avec la config la plus proche ; indexez‑le avec GIN.
4. Exécutez une comparaison hors ligne : rappel/précision pour vos requêtes de régression, pas des benchmarks synthétiques.
5. Déployez l’écriture double ou l’indexation asynchrone ; ne bloquez pas les écritures utilisateurs sur l’indexation pendant la migration.
6. Basculez les lectures progressivement ; surveillez la latence et les plaintes de pertinence séparément.
7. Retirez la recherche SQLite seulement lorsque vous pouvez reconstruire les index Postgres et récupérer rapidement des pannes.

Trois mini‑récits terrain d’entreprises

Incident : la mauvaise hypothèse (« SQLite n’est qu’un fichier, donc ce sera plus rapide »)

Une équipe SaaS de taille moyenne a livré une fonctionnalité de recherche de documents embarquée dans le processus principal de l’appli en utilisant SQLite FTS5.
C’était conçu pour réduire les dépendances : pas de cluster Postgres, pas de service de recherche, pas de nouvelle rotation d’astreinte.
Le premier mois s’est bien passé. Le jeu de données était modeste et la plupart des écritures venaient d’un import nocturne.

Puis ils ont ajouté des notes générées par les utilisateurs avec autosave. Les écritures sont devenues constantes.
Ils ont supposé que le mode WAL signifiait « essentiellement concurrent », et ont mis l’appli à l’échelle horizontalement. Chaque instance écrivait dans le même fichier de base sur un stockage partagé.
Les symptômes étaient classiques : timeouts aléatoires, « database is locked » occasionnels, et des graphes CPU calmes pendant que les utilisateurs hurlaient.

L’incident n’a pas été un crash unique. Ce fut un effondrement en slow‑motion.
Un écrivain retenait un verrou plus longtemps que prévu, les autres faisaient la queue, les threads de requêtes s’accumulaient, et l’appli a cessé de servir le trafic bien avant que la base « ne casse ».
Ils ont appris à la dure qu’une frontière de verrou de fichier unique n’est pas un modèle de concurrence distribué.

La correction n’a rien d’ingénieux. Ils ont déplacé la recherche vers Postgres sur une instance dédiée, mis en file le travail d’indexation, et gardé SQLite pour le dev local et les tests.
Le changement le plus précieux fut social : ils ont écrit les hypothèses de concurrence dans le design doc pour que ça ne se reproduise pas.

Optimisation qui a échoué : « On va reconstruire l’index chaque nuit pour le garder rapide »

Une autre équipe avait un grand index SQLite FTS5 et a remarqué une montée lente de la latence des requêtes.
Quelqu’un a proposé une reconstruction complète nocturne de la table FTS. Ça fonctionnait en staging, c’était facile à automatiser, et cela embellissait les graphiques pour exactement un jour.
Ils l’ont déployée avec confiance et une invitation calendrier.

La reconstruction s’est exécutée sur le même hôte que l’appli. Elle a saturé l’IO disque pendant une heure, puis deux.
Les requêtes de lecture ont ralenti pendant la reconstruction, donc les utilisateurs ont relancé, créant plus de charge. Le job a chevauché le trafic du matin à cause des fuseaux horaires.
Le support a ouvert des tickets. L’ingénierie a blâmé le réseau. L’équipe réseau a blâmé le DNS, comme tradition l’exige.

L’effet boomerang n’était pas que reconstruire soit « mauvais ». C’était que la reconstruction est un événement d’IO.
L’équipe traitait le fichier de base comme un cache interne, mais il était devenu une fonctionnalité principale orientée utilisateur.
Ils avaient besoin d’une maintenance contrôlée, pas d’un reset brutal.

La solution finale : ils ont arrêté les reconstructions systématiques et ont géré les patterns d’écriture, le checkpointing et la santé des index.
Pour un churn plus important, ils sont passés à Postgres, où ils pouvaient reindexer de façon plus isolée et programmer des fenêtres de maintenance.
La leçon était ennuyeuse : ne programmez pas une tempête disque à côté de vos clients.

Pratique ennuyeuse mais correcte : « Nous testons la pertinence comme nos API »

Une troisième entreprise utilisait la recherche Postgres pour la base de connaissances interne.
Rien de sophistiqué : tsvector, index GIN, et quelques ajustements de classement.
Leur arme secrète n’était pas la technologie. C’était la discipline.

Ils gardaient un petit corpus de « requêtes goldens » avec les meilleurs résultats attendus.
Tout changement de dictionnaires, configs ou poids de classement passait par la CI et produisait un rapport de diff.
Les ingénieurs ont vite compris qu’un « tweak mineur » peut signifier « avalanche de tickets support ».

Un jour, une mise à jour de paquet OS a changé un comportement lié à la collation en staging, ce qui a modifié subtilement la tokenization.
La suite de régression l’a détecté. Ils ont bloqué le déploiement et enquêté.
Ce n’était pas une panne dramatique. Ce fut un non‑événement silencieux, le meilleur type.

Quand il a fallu ajouter une autre langue, ils n’ont pas argumenté sur des ressentis. Ils ont ajouté des vecteurs par langue et élargi le corpus golden.
Ça leur a coûté du stockage et du temps.
Ça les a sauvés de semaines de réunions « pourquoi la recherche est pire » où tout le monde a raison et personne n’est utile.

FAQ

1) SQLite FTS5 est‑il « suffisant » pour la production ?

Oui, si vous êtes mono‑nœud (ou un seul écrivain), majoritairement en lecture, et que vous traitez la maintenance (WAL, checkpoints, rebuilds) comme une préoccupation opérationnelle réelle.
Il échoue de manière prédictible quand vous violez ses hypothèses de concurrence.

2) La recherche PostgreSQL est‑elle plus lente que SQLite ?

Pas intrinsèquement. SQLite peut sembler plus rapide parce qu’il est embarqué et évite le saut réseau.
Postgres peut être extrêmement rapide avec un tsvector stocké + GIN, mais vous devez confirmer l’utilisation de l’index et garder l’autovacuum sain.

3) Dois‑je calculer to_tsvector au moment de la requête dans Postgres ?

Presque jamais pour une charge réelle. Persistez le vecteur (colonne générée ou trigger), indexez‑le et gardez les requêtes assez simples pour que le planner utilise l’index.

4) SQLite peut‑il gérer plusieurs instances applicatives lisant et écrivant ?

Lectures : oui, surtout avec WAL. Écritures : elles se sérialisent. Si vous avez des écritures fréquentes depuis plusieurs instances, vous aurez de la contention de verrous et des pics de latence.
Utilisez une file d’un seul écrivain ou passez à une base serveur.

5) Pourquoi j’ai des résultats différents entre environnements ?

Dérive de tokenizer/config. Les tokenizers de SQLite et les configurations de recherche Postgres sont des valeurs par défaut… jusqu’à ce qu’elles changent.
Figez les configs, versionnez‑les et testez explicitement l’Unicode et la ponctuation.

6) Comment améliorer la pertinence sans construire toute une pile de recherche ?

Commencez par la pondération des champs (titre > corps), des requêtes de phrase pour les motifs courants, et l’ajustement des stopwords.
Ensuite ajoutez une suite de régression de requêtes goldens pour que la pertinence ne régresse pas silencieusement.

7) Quand dois‑je arrêter de tuner et passer de SQLite à Postgres ?

Quand le goulot est la concurrence (écrivains), la visibilité opérationnelle, ou que les fenêtres de rebuild/maintenance deviennent inacceptables.
Si la solution consiste à « faire attendre moins les utilisateurs en espérant », c’est terminé.

8) Quand dois‑je arrêter d’utiliser Postgres FTS et adopter un moteur de recherche ?

Quand vous avez besoin de tolérance aux fautes de frappe, de synonymes, de scoring complexe, de faceting/agrégations à grande échelle ou d’indexation distribuée.
Aussi quand la recherche devient une fonctionnalité produit centrale avec ses propres SLA et que vous avez besoin d’outils spécialisés.

9) Le mode WAL de SQLite rend‑il les écritures concurrentes ?

Il améliore la concurrence lecture/écriture, pas la concurrence écriture/écriture. Un seul écrivain reste maître à la fois.
WAL empêche surtout les lecteurs d’être bloqués par les écrivains.

10) GIN est‑il toujours l’index adapté pour la recherche texte Postgres ?

Habituellement, oui. GiST existe et peut être utile dans certains cas, mais GIN est le choix standard pour les requêtes de containment sur tsvector.
Validez avec EXPLAIN, pas avec la superstition.

Étapes pratiques suivantes

Si vous décidez aujourd’hui :

• Choisissez SQLite FTS5 si vous pouvez vous engager sur un modèle à un seul écrivain, WAL, checkpointing explicite et discipline de sauvegarde sur fichier.
• Choisissez PostgreSQL tsearch si vous avez besoin de concurrence, d’introspection, d’opérations prévisibles et que vous êtes prêt à gérer un serveur de base de données.

Si vous souffrez déjà :

1. Exécutez la recette de diagnostic rapide. Identifiez si vous êtes lock‑, IO‑ ou CPU‑bound.
2. Prouvez l’utilisation de l’index (SQLite : MATCH ; Postgres : EXPLAIN (ANALYZE, BUFFERS)).
3. Stabilisez la maintenance : checkpoints WAL (SQLite), autovacuum/bloat (Postgres).
4. Verrouillez les configs et ajoutez des tests de régression par requêtes goldens. La pertinence est un contrat, pas une impression.
5. Si la concurrence est le facteur limitant, cessez de négocier avec la physique du verrouillage de fichier et déplacez la charge vers Postgres.

La version adulte du « choix de base de données » est de choisir quels incidents vous voulez déboguer.
SQLite vous surprend par la distance qu’il peut couvrir. PostgreSQL vous surprend par la quantité de contrôle qu’il offre.
Choisissez selon les surprises que vous pouvez vous permettre.