PostgreSQL vs SQLite : Fiabilité vs Simplicité — Qu’est-ce qui casse en premier

novembre 6, 2025 • février 3, 2026 • Lecture : 30 min • Views: 5

Cet article vous a aidé ?

Le choix de base de données qui fait le plus mal est celui que vous n’avez pas réalisé avoir fait. Vous livrez une application bien tenue,
elle fonctionne sur votre portable, elle fonctionne en staging, puis elle rencontre le trafic du vendredi, un voisin bruyant ou un système de fichiers un peu « créatif ». Maintenant vous découvrez les verrous, fsync et sauvegardes à la dure.

PostgreSQL et SQLite sont tous deux excellents. Ils échouent cependant différemment. Voici la carte pratique montrant où chacun casse en premier,
pourquoi, et comment le diagnostiquer rapidement quand votre pager commence à s’agiter.

La lentille décisionnelle : qu’est-ce que vous optimisez ?

« Fiabilité vs simplicité » sonne comme un débat philosophique jusqu’à ce que vous ayez été d’astreinte pour les deux.
En pratique, c’est un compromis entre où la complexité réside et quelle défaillance vous pouvez tolérer.

SQLite optimise pour une correction sans friction… jusqu’à ce que vous scaliez le mauvais axe

SQLite est une bibliothèque. Il n’y a pas de serveur. Ce n’est pas un détail anecdotique ; cela change toute votre surface de défaillance.
Moins de pièces en mouvement, moins de ports, moins de daemons, moins d’identifiants, moins de runbooks.
Votre processus applicatif est le processus base de données.

La première chose qui casse n’est généralement pas « la disponibilité ». C’est souvent la concurrence d’écriture ou les hypothèses opérationnelles :
« On peut mettre la BDD sur un NFS », ou « on peut avoir 20 workers écrivant constamment », ou « on n’a pas besoin de sauvegarde parce que c’est un fichier ».

PostgreSQL optimise pour un comportement prévisible sous charge… et vous demande de payer la taxe opérateur

PostgreSQL est un serveur de base de données. Il est conçu pour être partagé par de nombreux clients faisant beaucoup de choses en même temps, et il est très bon pour cela.
Mais vous héritez des tâches opérationnelles classiques : dimensionnement mémoire, gestion des connexions, WAL, réplication, vacuum, upgrades, monitoring,
et l’occasionnel « pourquoi cette requête est soudainement un crime ? »

Ce qui casse en premier sur PostgreSQL n’est rarement le fichier de base de données. C’est habituellement la latence (requêtes lentes, stalls IO),
la dérive opérationnelle (autovacuum mal réglé, stockage presque plein) ou l’erreur humaine (le mauvais paramètre en prod).

Mon biais : si votre application est mono-nœud, avec un volume d’écritures modeste, et que vous pouvez accepter « monter en puissance ou refactorer plus tard », SQLite est un cadeau.
Si vous avez besoin de débit multi-auteurs, d’accès distant, d’isolation multi-tenant ou de haute disponibilité sérieuse, PostgreSQL est l’adulte dans la pièce.
Vous pouvez vous blesser avec les deux ; PostgreSQL vous donne juste plus de façons de le faire à budgets supérieurs.

Faits intéressants et contexte historique

SQLite a été créé en 2000 par D. Richard Hipp pour soutenir un contrat de la marine américaine ; il a été conçu pour être petit, fiable et autonome.
SQLite est célèbre pour être « sans serveur », ce qui signifie pas de démon séparé ; c’est une bibliothèque liée à votre processus, changeant les domaines de défaillance et les modèles de déploiement.
SQLite vise un format de fichier stable entre les versions. Vous pouvez souvent déplacer un fichier de base de données entre machines avec peu de drame — si vous respectez les règles du système de fichiers.
PostgreSQL descend de POSTGRES à l’UC Berkeley (années 1980). La partie « SQL » est arrivée plus tard ; la culture de la justesse est restée.
PostgreSQL a introduit MVCC tôt comme modèle de concurrence central, ce qui explique pourquoi les lecteurs ne bloquent pas les écrivains comme dans des systèmes de verrouillage plus simples.
Le mode WAL de SQLite (write-ahead logging) est devenu largement utilisé pour améliorer la concurrence et les performances en séparant les lectures des écritures.
Le WAL de PostgreSQL n’est pas seulement pour la durabilité ; c’est l’épine dorsale de la réplication et de la restauration point-in-time.
SQLite est omniprésent — téléphones, navigateurs, appareils embarqués — parce que « c’est juste un fichier » est exactement l’histoire de déploiement que veulent les fabricants de matériel.
La lignée VACUUM de PostgreSQL est le coût opérationnel du MVCC : les tuples morts ne disparaissent pas à moins que le système ne les nettoie.

Ce qui casse en premier : PostgreSQL vs SQLite

SQLite : la première défaillance est généralement la contention ou le système de fichiers

La douleur de SQLite est peu romantique. Ce n’est pas une « panne de base de données », c’est « pourquoi les requêtes sont bloquées », ou « pourquoi avons-nous eu ‘database is locked’ »,
ou « pourquoi ce fichier est corrompu après un reboot ».

Échecs « premiers » courants pour SQLite en production :

Contention d’écriture : trop d’écrivains concurrents, transactions longues, ou un cycle de checkpoint chargé.
Mauvais emplacement : fichier de base de données sur systèmes de fichiers réseau ou stockage instable ; les verrous de fichiers et garanties de durabilité deviennent étranges.
Durabilité mal configurée : réglages PRAGMA choisis pour la vitesse sans comprendre le modèle de crash.
Sauvegarde par copie du fichier à chaud : vous obtenez un fichier qui a l’air propre mais qui est logiquement incohérent (ou simplement corrompu).
Domaine de défaillance au niveau processus : corruption mémoire, SIGKILL, ou éviction de conteneur qui tue à la fois l’app et la BDD.

Blague #1 : SQLite est comme un vélo — fiable, élégant, et silencieusement jugeant quand vous essayez d’y transporter un réfrigérateur.

PostgreSQL : la première défaillance est généralement la dérive ops ou la pression IO

Les défaillances initiales de PostgreSQL concernent moins la correction et plus l’entropie opérationnelle. Il est extrêmement bon pour ne pas corrompre les données.
Il est moins bon pour vous empêcher de le faire foncer contre un mur avec vos propres choix.

Échecs « premiers » courants pour PostgreSQL en production :

Orages de connexions : trop de connexions clientes, surcharge mémoire, commutations de contexte, et contention de verrous.
Stalls IO : stockage lent, WAL mal dimensionné, pics de checkpoint, ou disques saturés.
Retard d’autovacuum : bloat, croissance table/index, risque de wraparound des transaction ID, et dégradation des plans de requête.
Surprises de réplication : lag, slots retenant le WAL indéfiniment, scripts de basculement qui fonctionnent jusqu’à ce qu’ils ne fonctionnent plus.
Mauvais plans de requêtes : index manquants, statistiques obsolètes, et l’occasionnel « nous avons désactivé nested loops globalement parce qu’un billet de blog le disait ».

Durabilité et sécurité au crash : ce que « validé » signifie vraiment

La fiabilité commence à la frontière du commit. Quand votre code renvoie « OK », qu’avez-vous réellement acheté ?
Pas « probablement ». Pas « en cache ». Pas « peut-être après que le noyau ait décidé ». Qu’avez-vous réellement garanti ?

La durabilité de SQLite est configurable — et c’est à la fois un pouvoir et un piège

SQLite vous donne des pragmas qui changent les caractéristiques de durabilité. Si vous ne les réglez pas, vous héritez de valeurs par défaut généralement sûres,
mais les équipes sensibles à la performance partent souvent en chasse de la vitesse et marchent accidentellement sur la durabilité en cas de crash.

Leviers clés de SQLite :

journal_mode : DELETE, TRUNCATE, PERSIST, MEMORY, WAL, OFF. WAL est habituellement le meilleur compromis pratique.
synchronous : OFF, NORMAL, FULL, EXTRA. Plus bas = plus rapide et plus risqué en cas de coupure/panne de courant.
locking_mode : NORMAL vs EXCLUSIVE ; EXCLUSIVE peut améliorer la vitesse mais surprendre d’autres processus.
temp_store : affecte les objets temporaires ; peut déplacer les schémas d’E/S.

La dure réalité : SQLite peut être extrêmement sûr en cas de crash sur un disque local avec des réglages corrects. Mais si vous l’exécutez sur un système de fichiers qui ment
sur fsync, ou sur un partage réseau avec des verrous d’avis se comportant différemment, votre histoire de durabilité devient « basée sur l’espoir ».

La durabilité de PostgreSQL est plus rigide — et plus facile à raisonner

PostgreSQL a aussi des boutons de durabilité, mais la culture et les valeurs par défaut penchent vers la correction. Le modèle central :
un commit est durable lorsque l’enregistrement WAL est correctement persisté (sous réserve de votre réglage synchronous_commit).

Le WAL de PostgreSQL est une piste d’audit de qualité professionnelle : il sert à la récupération après crash, à la réplication, et à la restauration point-in-time. C’est pourquoi
Postgres peut survivre à un crash et revenir avec les données intactes — à condition que le stockage respecte les sémantiques de durabilité.

La couche de stockage décide qui gagne l’argument

Les deux bases dépendent du système de fichiers et du stockage sous-jacent pour honorer les écritures et vidages. Si la plateforme ment, la base perd.
Les caches avec alimentation, les barrières d’écriture et les options de montage correctes comptent. Il en va de même pour ne pas utiliser une combinaison filesystem+transport qui « marche à peu près ».

Une idée paraphrasée souvent attribuée à Werner Vogels (CTO d’Amazon) : la fiabilité vient de construire des systèmes qui supposent la défaillance et récupèrent rapidement plutôt que de prétendre qu’une défaillance n’arrivera pas.

Concurrence : verrous, contention et forme de la douleur

Concurrence SQLite : un écrivain (principalement), plusieurs lecteurs (généralement)

SQLite permet plusieurs lecteurs, et avec WAL il peut maintenir les lecteurs pendant qu’un écrivain ajoute au WAL. Mais il a toujours une contrainte centrale :
les transactions d’écriture se sérialisent. Vous pouvez avoir des écrivains concurrents dans le sens où plusieurs threads essaient, mais ils feront la queue et expireront si vous
concevez l’application comme un système OLTP bavard.

Ce que cela signifie en pratique :

Les transactions courtes sont une question de survie. Les transactions longues sont de l’automutilation.
Les busy timeouts ne sont pas une solution ; ils masquent la mise en file d’attente jusqu’à ce que la latence devienne visible par l’utilisateur.
Le comportement des checkpoints WAL peut créer des arrêts périodiques s’il n’est pas géré (surtout sur des disques lents).

Concurrence PostgreSQL : MVCC plus verrous, ce qui est à la fois meilleur et plus compliqué

PostgreSQL brille sous une concurrence mixte lecture/écriture parce que les lecteurs ne bloquent pas les écrivains de façon naïve. Mais ne confondez pas « MVCC » avec « pas de verrous ».
Postgres a plein de verrous, plus des verrous lourds, plus des verrous légers, plus des points de contention internes.

La forme de douleur typique de Postgres :

Une requête lente bloque les autres en gardant des verrous plus longtemps que prévu.
Une migration modifie une table et provoque des files d’attente et des expirations de verrous.
Trop de connexions brûlent la mémoire et saturent le CPU par commutation de contexte.

Blague #2 : PostgreSQL vous laissera faire presque n’importe quoi — parfois y compris des choses que vous n’admettrez jamais avoir faites lors du postmortem.

Sauvegardes et restaurations : simplicité vs garanties

Les sauvegardes SQLite sont simples seulement si vous les faites correctement

SQLite semble amical pour les sauvegardes parce que c’est un fichier unique. Cela séduit les gens qui finissent par le copier pendant que l’application écrit.
Parfois vous vous en sortez. Puis un jour vous ne vous en sortez pas, et vous découvrez que votre stratégie de sauvegarde était basée sur « l’ambiance ».

Schémas sûrs :

Utilisez l’API de sauvegarde SQLite (via sqlite3 .backup ou intégration applicative).
Si vous utilisez WAL : capturez la base et l’état WAL de manière cohérente (ou faites un checkpoint avant de copier).
Validez les restaurations régulièrement. Une sauvegarde que vous n’avez pas restaurée n’est qu’une rumeur.

Les sauvegardes PostgreSQL sont plus complexes — et bien plus flexibles

Postgres dispose d’outils matures pour les sauvegardes logiques (pg_dump), les sauvegardes physiques (pg_basebackup),
et la récupération point-in-time via l’archivage WAL. La complexité n’est pas optionnelle, mais elle vous procure un vrai pouvoir opérationnel :
restaurer à hier, cloner la production, récupérer d’erreurs humaines, et construire des réplicas de lecture.

Le mode d’échec typique n’est pas « l’outil de sauvegarde ne fonctionne pas ». C’est « nous n’avons jamais testé la restauration », ou « l’archivage WAL s’est cassé silencieusement » ,
ou « nous avons rempli le disque de WAL parce qu’un slot de réplication l’a bloqué ».

Réalité opérationnelle : monitoring, upgrades, migrations

Opérations SQLite : moins de boutons, mais vous gérez le cycle de vie du processus

SQLite n’a pas de serveur à surveiller, mais vous avez quand même besoin de discipline opérationnelle :
permissions de fichiers, espace disque, cohérence du système de fichiers, sauvegardes, et timeouts côté application.
Votre « upgrade de BDD » peut être une mise à jour de bibliothèque. Votre « redémarrage de BDD » est un redémarrage de l’app.

Si votre application tourne en flotte de conteneurs stateless, SQLite devient délicat à moins que chaque instance ait un stockage persistant local et que vous acceptiez
des bases par instance ou un modèle de synchronisation soigneux. SQLite est excellent en local. Il n’est pas excellent pour prétendre être partagé.

Opérations PostgreSQL : vous obtenez des outils puissants, et ces outils peuvent blesser

L’excellence opérationnelle PostgreSQL ressemble à une cohérence ennuyeuse :
surveillez le volume WAL, le lag de réplication, la santé du vacuum, les requêtes lentes, la croissance du disque, et la contention de verrous.
Les upgrades sont gérables, mais ce sont des choses à faire. Les migrations de schéma aussi.

L’avantage est que vous pouvez scaler les lectures avec des réplicas, augmenter les écritures avec une meilleure conception (ou sharder si nécessaire), et récupérer d’une plus large classe de désastres.

Trois mini-histoires d’entreprise tirées du terrain

Mini-histoire 1 : Incident causé par une mauvaise hypothèse

Une équipe produit a livré un petit outil interne avec SQLite parce que c’était « juste des métadonnées ». Il tournait sur une VM partagée, et le fichier de base de données résidait sur un partage réseau monté.
Ce choix n’a pas été débattu ; il était implicite. L’équipe voulait que le fichier survive aux rebuilds de VM, et le partage était « stockage fiable ».

Quelques semaines plus tard, un événement de maintenance a déclenché un bref hic réseau. L’app a continué à tourner. SQLite a continué à essayer.
Les logs montraient des erreurs intermittentes « database disk image is malformed » et « database is locked ». L’outil ne mourait pas complètement ; il devenait juste peu fiable.
Les utilisateurs retentaient. Les retentatives ont amplifié les écritures. Le fichier de base de données est devenu une scène de crime.

Le postmortem était gênant parce que personne n’avait fait un changement manifestement stupide. Ils avaient formulé une hypothèse à l’air raisonnable :
« Un fichier de base de données est un fichier, donc n’importe quel stockage qui stocke des fichiers convient. » Mais la correction de SQLite dépend de sémantiques spécifiques de verrouillage et de durabilité.
Le système de fichiers réseau fonctionnait la plupart du temps — jusqu’à ce qu’il ne fonctionne plus.

La correction a été ennuyeuse : déplacer le fichier SQLite sur un stockage local, activer WAL, et créer une vraie pipeline de sauvegarde en utilisant .backup.
Finalement, au fur et à mesure de la croissance d’usage, ils ont migré vers PostgreSQL afin que plusieurs services puissent écrire sans traiter un verrou de fichier comme un algorithme de consensus distribué.

Mini-histoire 2 : Une optimisation qui s’est retournée contre eux

Une autre équipe utilisait PostgreSQL pour une application client-facing avec un pattern OLTP normal : beaucoup de lectures, écritures régulières, pics occasionnels.
Un nouvel ingénieur a remarqué des pics de latence pendant les checkpoints. Il a lu quelques billets de tuning et a décidé que la correction était de « réduire le surcoût de flush disque ».
Il a changé des paramètres pour rendre Postgres moins prompt à fsync et a ajusté agressivement les paramètres de checkpoint.

Pendant une semaine, les graphes semblaient plus jolis. La latence s’est lissée. L’ingénieur a gagné une tranquille sensation de victoire.
Puis un reboot d’hôte est survenu lors d’un événement d’alimentation. Postgres a récupéré, mais les dernières minutes de transactions acquittées avaient disparu.
Les utilisateurs ont ouvert des tickets pour des mises à jour disparues. L’équipe applicative a d’abord soupçonné le cache. Ce n’était pas le cache.

L’optimisation a troqué la durabilité contre la performance sans décision formalisée de risque. Isolés, les paramètres étaient « valides ».
En réalité, l’exigence métier était « ne pas perdre les updates validés », et la sémantique du système avait été modifiée.

La remédiation n’a pas été juste de revenir en arrière. Ils ont mis en place une règle de gestion du changement : les paramètres affectant la durabilité requièrent une revue,
et tout test de performance doit inclure une injection de panne de type « couper l’alimentation » (ou le plus proche possible en lab sécurisé).

Mini-histoire 3 : Une pratique ennuyeuse mais correcte qui a sauvé la journée

Un groupe plateforme exécutait PostgreSQL avec réplication en streaming et une routine de sauvegarde conservatrice : backups de base nightly, archivage WAL continu,
et un drill de restauration mensuel. Ce n’était pas du travail glamour. Personne n’a été promu pour « restore drill #12 réussi ».

Un après-midi, un ingénieur a lancé un script de nettoyage de données contre le mauvais environnement. Ce n’était pas malveillant. C’était une erreur de mémoire musculaire :
onglet de terminal, autocomplétion, Entrée. Le script s’est exécuté rapidement et a fait exactement ce qu’on lui avait demandé.

L’équipe a détecté le problème en quelques minutes via le monitoring : une chute soudaine du nombre de lignes et un pic d’activité de suppression.
Ils ont déclaré un incident, gelé les écritures, et choisi un point de récupération juste avant l’exécution du script. Parce que l’archivage WAL était sain
et que les procédures de restauration étaient répétées, ils ont effectué une récupération point-in-time vers un nouveau cluster et redirigé le trafic.

Le résultat : un après-midi désagréable, mais pas de perte de données permanente. Le facteur salvateur n’a pas été de l’héroïsme. Ce furent les habitudes de pratiquer la restauration
et de vérifier que les archives WAL étaient réellement utilisables. Les pratiques ennuyeuses sont sous-estimées parce qu’elles ne ressemblent pas à de l’ingénierie — jusqu’à ce qu’elles le soient.

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

Ce sont les types de vérifications que vous exécutez quand vous décidez entre SQLite et Postgres, ou quand quelque chose est déjà en feu.
Chaque tâche inclut une commande, une sortie d’exemple, ce que cela signifie, et quelle décision prendre.

Tâche 1 (SQLite) : Identifier le mode de journaling et le niveau de durabilité

cr0x@server:~$ sqlite3 /var/lib/app/app.db "PRAGMA journal_mode; PRAGMA synchronous;"
wal
2

Ce que cela signifie : Le mode WAL est activé ; synchronous=2 correspond à FULL. Les commits sont plus durables, typiquement plus lents que NORMAL.

Décision : Pour la production sur SSD local, WAL + FULL est une base sûre. Si la latence est trop élevée, testez NORMAL, mais documentez le risque.

Tâche 2 (SQLite) : Vérifier les busy timeouts et les erreurs de lock immédiates

cr0x@server:~$ sqlite3 /var/lib/app/app.db "PRAGMA busy_timeout;"
5000

Ce que cela signifie : Le client attendra jusqu’à 5 secondes pour les verrous avant d’échouer.

Décision : Si vous voyez de la latence visible par l’utilisateur, réduisez la contention (transactions plus courtes, moins d’écrivains) au lieu d’augmenter indéfiniment ce délai.

Tâche 3 (SQLite) : Exécuter un contrôle d’intégrité après un crash ou un événement de stockage

cr0x@server:~$ sqlite3 /var/lib/app/app.db "PRAGMA integrity_check;"
ok

Ce que cela signifie : La structure de la base est cohérente.

Décision : Si la sortie n’est pas ok, arrêtez les écritures, prenez une copie pour analyse, et restaurez depuis une sauvegarde connue bonne.

Tâche 4 (SQLite) : Inspecter l’état WAL/checkpoint

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

Ce que cela signifie : Le checkpoint a réussi ; pas de frames restants dans le WAL ; le WAL a été tronqué.

Décision : Si vous voyez de grandes valeurs ou des échecs, enquêtez sur des lecteurs de longue durée ou des goulots IO. Envisagez de planifier les checkpoints en période de faible charge.

Tâche 5 (SQLite) : Effectuer une sauvegarde en ligne sûre

cr0x@server:~$ sqlite3 /var/lib/app/app.db ".backup '/var/backups/app-$(date +%F).db'"

Ce que cela signifie : Cela utilise le mécanisme de sauvegarde de SQLite, produisant un snapshot cohérent.

Décision : Préférez ceci à cp pour les bases actives. Ensuite, exécutez PRAGMA integrity_check sur la sauvegarde dans votre pipeline.

Tâche 6 (Linux) : Confirmer que la base n’est pas sur un système de fichiers réseau

cr0x@server:~$ df -T /var/lib/app/app.db
Filesystem     Type 1K-blocks     Used Available Use% Mounted on
/dev/nvme0n1p2 ext4 205113344 73214512 121345024  38% /

Ce que cela signifie : ext4 local, pas NFS/CIFS. Bon pour les hypothèses de durabilité SQLite.

Décision : Si vous voyez nfs ou cifs, reconsidérez SQLite pour un accès partagé ou déplacez le fichier sur disque local et répliquez à un niveau supérieur.

Tâche 7 (PostgreSQL) : Vérifier si le serveur est réellement en ligne et accepte les connexions

cr0x@server:~$ pg_isready -h 127.0.0.1 -p 5432
127.0.0.1:5432 - accepting connections

Ce que cela signifie : Postgres est en ligne et réactif au niveau TCP.

Décision : S’il est « rejecting » ou « no response », vérifiez les logs, les conditions de disque plein et le statut de récupération avant de blâmer l’app.

Tâche 8 (PostgreSQL) : Identifier la pression de connexions

cr0x@server:~$ psql -X -qAt -c "SELECT count(*) FROM pg_stat_activity;"
187

Ce que cela signifie : 187 sessions backend existent. Selon la taille de l’instance, cela peut être acceptable ou problématique.

Décision : Si élevé et en augmentation, implémentez un pooler de connexions et fixez des limites raisonnables dans l’application.

Tâche 9 (PostgreSQL) : Trouver rapidement les verrous bloquants

cr0x@server:~$ psql -X -qAt -c "SELECT blocked.pid, blocked.query, blocking.pid, blocking.query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid)) WHERE blocked.wait_event_type='Lock';"
4123|UPDATE orders SET status='paid' WHERE id=$1;|3999|ALTER TABLE orders ADD COLUMN note text;

Ce que cela signifie : Une migration bloque les écritures applicatives.

Décision : Arrêtez/rollbackez le DDL bloquant si possible, ou replanifiez les migrations en utilisant des patterns amicaux aux verrous (ex. ajouter une colonne sans default, backfill par lots).

Tâche 10 (PostgreSQL) : Mesurer le lag de réplication (si vous avez des réplicas)

cr0x@server:~$ psql -X -qAt -c "SELECT application_name, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
replica1|streaming|00:00:00.120|00:00:00.180|00:00:00.450

Ce que cela signifie : Lag sub-second ; sain pour beaucoup de charges de travail.

Décision : Si le lag est de secondes à minutes, enquêtez sur la saturation IO, des problèmes réseau, ou des transactions longues sur le replica.

Tâche 11 (PostgreSQL) : Vérifier si autovacuum est en retard

cr0x@server:~$ psql -X -qAt -c "SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"
events|983421|2025-12-30 08:12:41+00
orders|221904|2025-12-30 08:03:10+00
sessions|110992|2025-12-30 08:15:02+00

Ce que cela signifie : Les tuples morts s’accumulent ; autovacuum tourne mais peut ne pas suivre le churn d’écritures.

Décision : Réglez autovacuum pour les tables chaudes, ajoutez des index prudemment, et envisagez le partitionnement pour les tables d’événements à fort turnover.

Tâche 12 (PostgreSQL) : Identifier les requêtes à forte latence (nécessite pg_stat_statements)

cr0x@server:~$ psql -X -qAt -c "SELECT calls, mean_exec_time::numeric(10,2), left(query,80) FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;"
142|812.34|SELECT * FROM reports WHERE org_id = $1 ORDER BY created_at DESC LIMI
9211|203.11|UPDATE sessions SET last_seen = now() WHERE id = $1

Ce que cela signifie : Une requête est systématiquement lente ; une autre est modérément lente mais très appelée.

Décision : Corrigez d’abord la requête constamment lente si elle bloque des parcours utilisateurs ; corrigez les requêtes modérées à grand nombre d’appels si elles dominent CPU/IO.

Tâche 13 (OS) : Vérifier l’espace disque (parce que les bases détestent les surprises)

cr0x@server:~$ df -h /var/lib/postgresql
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p2  196G  189G  1.9G  99% /

Ce que cela signifie : Vous êtes à un fichier journal d’une mauvaise journée.

Décision : Libérez de l’espace immédiatement. Puis mettez en place des alertes à 80/90/95% et planifiez la croissance de capacité. Le disque plein n’est pas un « problème plus tard ».

Tâche 14 (PostgreSQL) : Inspecter la pression de croissance WAL

cr0x@server:~$ psql -X -qAt -c "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),'0/0')) AS wal_bytes_since_origin;"
1643 GB

Ce que cela signifie : C’est un indicateur grossier, mais il suggère une forte génération de WAL au fil du temps (ou juste que l’origine est zéro). Mieux : vérifiez la taille du répertoire pg_wal et les slots de réplication.

Décision : Si le répertoire WAL est volumineux, vérifiez les slots de réplication coincés ou l’archivage en échec avant de changer les paramètres de checkpoint.

Tâche 15 (PostgreSQL) : Vérifier des replication slots qui pourraient épingler le WAL

cr0x@server:~$ psql -X -qAt -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"
analytics_slot|f|1A/2F000000

Ce que cela signifie : Un slot existe et est inactif. Il peut retenir le WAL jusqu’à ce qu’un consommateur rattrape ou que le slot soit supprimé.

Décision : Si le consommateur est parti, supprimez le slot après vérification. S’il est nécessaire, réparez le consommateur et planifiez la rétention WAL.

Mode opératoire de diagnostic rapide

Quand la latence grimpe ou que les écritures échouent, vous n’avez pas le temps pour un débat philosophique. Vous avez besoin d’un chemin court vers « quel est le goulot ».
Ce playbook est volontairement opinionné.

Première étape : identifier la classe de défaillance

Hard down : impossible de se connecter / fichier ne s’ouvre pas / erreurs de corruption.
Soft down : les connexions fonctionnent mais les requêtes expirent.
Mauvais résultats : données manquantes, lectures incohérentes, ou mises à jour partielles.

Deuxième étape : vérifier le substrat (disque et système de fichiers) avant de blâmer le SQL

Le disque est-il presque plein ?
L’IO est-il saturé ou lent ?
La base est-elle sur un système de fichiers réseau (danger SQLite) ou un volume instable ?

Troisième étape : chercher des signaux de contention

SQLite : « database is locked », transactions longues, stalls de checkpoint WAL, busy timeouts.
PostgreSQL : attentes de verrou, compte de connexions, requêtes lentes, retard autovacuum, lag de réplication.

Quatrième étape : décider de la mitigation sûre la plus rapide

SQLite : réduire la concurrence des écrivains, raccourcir les transactions, activer WAL, ajouter busy_timeout (pansement), déplacer sur disque local.
Postgres : tuer la requête bloquante, mettre en pause une migration, ajouter un index prudemment, augmenter l’IO, activer le pooling, ou basculer si le primaire est malsain.

Cinquième étape : noter le mode de défaillance en une phrase

Si vous ne pouvez pas le décrire, vous ne pouvez pas le corriger de manière fiable. Exemples :
« Les écritures font la queue derrière une transaction longue qui détient un verrou » ou « Fichier SQLite sur NFS a perdu les sémantiques de verrou pendant un flapping réseau ».

Erreurs courantes : symptôme → cause racine → correction

Erreurs SQLite

Symptôme : erreurs fréquentes « database is locked » sous charge
Cause racine : trop d’écrivains concurrents ou transactions d’écriture longues ; le mode de journalisation rollback par défaut amplifie la contention
Correction : activer WAL, réduire la concurrence d’écriture, garder les transactions courtes, ajouter batching ciblé, définir un busy_timeout raisonnable.
Symptôme : corruption de la base après reboot/panne de courant
Cause racine : synchronous=OFF/NORMAL choisi sans compréhension ; le stockage ment sur fsync ; ou la BDD placée sur un système de fichiers inadapté
Correction : utiliser disque local, régler synchronous=FULL pour les données critiques, éviter les systèmes de fichiers réseau, valider avec integrity_check et des exercices de restauration.
Symptôme : la sauvegarde se restaure mais l’app se comporte bizarrement (lignes manquantes, erreurs de contraintes plus tard)
Cause racine : copie à chaud du fichier pendant des écritures actives ; WAL non capturé de façon cohérente
Correction : utiliser l’API de sauvegarde SQLite ; checkpointer de manière appropriée ; tester la restauration et exécuter integrity_check sur les sauvegardes.
Symptôme : pics de latence périodiques toutes les quelques minutes
Cause racine : checkpointing WAL causant des rafales IO, souvent aggravé par des disques lents ou des lecteurs longs empêchant la complétion du checkpoint
Correction : ajuster la stratégie de checkpoint, réduire les transactions de lecture longues, envisager un checkpoint manuel pendant les périodes de faible charge.

Erreurs PostgreSQL

Symptôme : CPU élevé et erreurs « too many connections »
Cause racine : pattern une-connexion-par-requête ; absence de pooling ; max_connections augmenté jusqu’à ce que la mémoire souffre
Correction : utiliser un pooler de connexions, limiter les connexions, corriger la réutilisation des connexions côté application, surveiller pg_stat_activity.
Symptôme : pics de latence d’écriture et périodes fsync-intensives
Cause racine : rafales de checkpoint, WAL sur stockage lent, shared_buffers/checkpoint mal dimensionnés, ou IO saturé
Correction : placer le WAL sur un stockage rapide, régler checkpoint_timeout et checkpoint_completion_target prudemment, mesurer l’IO avec des outils système.
Symptôme : requêtes qui ralentissent sur plusieurs semaines, croissance d’index, explosion de l’utilisation disque
Cause racine : vacuum en retard ; accumulation de bloat ; transactions longues empêchant le nettoyage
Correction : régler autovacuum par table, éliminer les transactions longues, envisager le partitionnement, exécuter vacuum/analyze quand approprié.
Symptôme : disque rempli de WAL de façon inattendue
Cause racine : slot de réplication épinglé ; replica hors ligne ; archivage WAL cassé et rétention qui croît
Correction : inspecter les replication slots, restaurer les consommateurs, supprimer les slots inutilisés, alerter sur la taille de pg_wal et les erreurs d’archivage.
Symptôme : une migration provoque des timeouts généralisés
Cause racine : DDL acquérant des verrous ; transactions longues bloquant le DDL et vice versa
Correction : utiliser des patterns de migration minimisant les verrous, définir des lock timeouts, déployer en horaires creux, vérifier les blocages avec pg_blocking_pids.

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

Checklist A : Quand SQLite est le bon choix (et comment ne pas le regretter)

Confirmer la forme de la charge : principalement des lectures, écritures concurrentes limitées, transactions courtes.
Placer la BDD sur disque local : éviter NFS/CIFS et les « volumes partagés » aux sémantiques de verrouillage floues.
Activer le mode WAL : utiliser WAL pour une meilleure concurrence lecture/écriture.
Choisir la durabilité intentionnellement : synchronous=FULL pour les écritures critiques ; documenter si vous choisissez NORMAL.
Implémenter des sauvegardes via l’API de backup : planifier, faire des rotations, et vérifier les restaurations.
Ajouter des contrôles d’intégrité : exécuter integrity_check en CI pour les artefacts de sauvegarde ou après des arrêts non propres.
Planifier la sortie : définir le seuil auquel migrer vers Postgres (nombre d’écrivains, accès distant, multi-instance).

Checklist B : Quand PostgreSQL est le bon choix (et comment le garder ennuyeux)

Taille correcte des connexions : ne confondez pas « plus de connexions » avec « plus de débit ». Utilisez le pooling.
Mettre le WAL sur un bon stockage : les disques à faible latence comptent plus que vous ne voulez l’admettre.
Activer la visibilité essentielle : slow query logging, pg_stat_statements, monitoring des verrous, surveillance de la réplication.
Faire du vacuum une priorité : surveiller le bloat, les tuples morts, et les transactions longues.
Sauvegardes + drills de restauration : choisir logical/physical/PITR selon RPO/RTO, et répéter les restaurations.
Les changements de schéma sont des déploiements : pratiquer des migrations sûres et définir des lock timeouts.
Avoir un plan de basculement : même manuel, l’écrire et le tester quand vous êtes calme.

Étape par étape : choisir entre eux dans un projet réel

Écrire la tolérance à la défaillance : perte de données acceptable (RPO) et indisponibilité acceptable (RTO).
Quantifier la concurrence : nombre d’écrivains concurrents et durée maximale de transaction dans le chemin critique.
Décider de la topologie de déploiement : nœud unique vs multi-instance ; besoin d’accès distant ; besoin de réplicas.
Choisir la chose la plus simple qui respecte le SLO : SQLite si mono-nœud et faible contention d’écriture ; Postgres sinon.
Prototyper le pire cas : test de charge sur les écritures ; injecter des pannes (kill -9, reboot en staging, simulation de latence disque).
Opérationnaliser : sauvegardes, alertes, dashboards et tests de restauration avant de dire « terminé ».

FAQ

1) SQLite est-il « moins fiable » que PostgreSQL ?

Pas intrinsèquement. SQLite peut être extrêmement fiable sur un stockage local avec des réglages raisonnables. Il est moins tolérant quand vous ajoutez de la concurrence,
des systèmes de fichiers partagés ou des pratiques de sauvegarde laxistes. PostgreSQL est conçu pour l’accès partagé et les motifs de récupération opérationnelle, donc il a tendance à rester fiable à mesure que la complexité augmente.

2) Quelle est la façon la plus courante dont SQLite échoue en production ?

Contention de verrous : trop d’écrivains ou transactions trop longues. Le symptôme est « database is locked », des timeouts, ou de la latence visible par l’utilisateur.
La correction est architecturale (réduire la concurrence d’écriture) plus que du tuning PRAGMA magique.

3) Puis-je exécuter SQLite sur NFS ou un volume partagé Kubernetes ?

Vous pouvez, mais vous pariez vos données sur des sémantiques de système de fichiers que vous n’avez probablement pas testées en condition de panne. Si vous avez besoin d’accès partagé entre nœuds,
PostgreSQL est le défaut plus sûr. Si vous devez absolument utiliser SQLite, gardez-le sur du stockage persistant node-local et traitez-le comme un état par instance.

4) Le mode WAL rend-il SQLite « multi-writer » ?

Non. WAL améliore la concurrence lecture/écriture et réduit le blocage des lecteurs par les écrivains, mais les écritures se sérialisent toujours. Vous pouvez réduire la douleur, pas changer le modèle central.

5) Quelle est la façon la plus courante dont PostgreSQL échoue en premier ?

Surcharge opérationnelle : trop de connexions, saturation IO, ou autovacuum en retard. Postgres conserve généralement la correction ; il devient juste lent ou bloqué derrière des verrous.

6) Si PostgreSQL est plus puissant, pourquoi ne pas l’utiliser toujours ?

Parce que la taxe opérateur est réelle. Vous avez besoin de sauvegardes, monitoring, upgrades et planification de capacité. Pour de petites applications mono-nœud,
SQLite peut offrir une excellente fiabilité avec une surface opérationnelle dramatiquement réduite.

7) En quoi les sauvegardes diffèrent-elles dans le sens de « ce qui casse en premier » ?

Le risque SQLite est « nous avons copié le fichier incorrectement ». Le risque PostgreSQL est « nous avons configuré des sauvegardes mais n’avons jamais testé la restauration », ou « l’archivage WAL a cassé silencieusement ».
Les deux sont des problèmes humains ; Postgres vous offre juste plus de façons de bien faire si vous vous engagez à la pratique.

8) Lequel est plus facile à déboguer pendant un incident ?

PostgreSQL, en général. Vous avez de la visibilité : pg_stat_activity, verrous, statistiques de requêtes, vues de réplication. Le débogage SQLite commence souvent dans vos logs applicatifs et le comportement fichier/IO au niveau OS.
La simplicité de SQLite réduit la surface de défaillance, mais quand elle casse, la cause est souvent en dehors du « SQL ».

9) Qu’en est-il du risque de corruption des données ?

Les deux sont conçus pour éviter la corruption. Le risque plus grand est l’environnement : stockage peu fiable, réglages non sûrs, et procédures de sauvegarde incorrectes.
SQLite est plus exposé aux bizarreries du système de fichiers car c’est un fichier unique avec des attentes de verrouillage. PostgreSQL est plus exposé à la mauvaise configuration opérationnelle et aux événements disque-plein.

10) Quel est le chemin de migration propre si je commence avec SQLite ?

Planifiez-le tôt : gardez les schémas compatibles si possible, évitez les bizarreries propres à SQLite, et construisez un outil de migration qui peut exporter/importer de manière déterministe.
Quand le moment vient, faites un double-écriture ou un basculement contrôlé avec vérification, pas un « juste du SQL » un vendredi soir.

Étapes suivantes à entreprendre cette semaine

Si vous choisissez aujourd’hui : prenez SQLite pour des applications mono-nœud avec une concurrence d’écriture modeste et une forte préférence pour un minimum d’opérations.
Choisissez PostgreSQL lorsque vous avez besoin de concurrence, d’accès distant, de HA, ou quand vous ne pouvez pas tolérer « ça marchait jusqu’à ce que la charge change ».

Puis faites le travail sans glamour :

Pour SQLite : activez WAL + niveau synchronous choisi intentionnellement, déplacez la BDD sur stockage local, et sauvegardez-la avec l’API backup. Testez la restauration.
Pour PostgreSQL : limitez les connexions et ajoutez du pooling, surveillez les verrous et la santé du vacuum, et implémentez des sauvegardes avec drills de restauration. Traitez les migrations comme des changements en production.
Pour les deux : alertez sur l’espace disque, validez les hypothèses sur le système de fichiers, et rédigez votre modèle de défaillance en anglais simple (ou français simple dans votre cas).

La base gagnante est celle dont vous avez répété les défaillances. La base perdante est celle que vous avez choisie parce qu’elle semblait simple, jusqu’à ce qu’elle ne le soit plus.