PostgreSQL vs SQLite — Concurrence d’écriture : qui gagne et pourquoi

La réponse claire : qui gagne ?

Pour les écrivains concurrents, PostgreSQL gagne. Pas de peu. Par conception.

SQLite est une base de données embarquée brillante qui optimise la simplicité, l’absence d’administration et la fiabilité dans un fichier unique.
Elle peut gérer plusieurs lecteurs simultanément. Elle peut même gérer « plusieurs écrivains » au sens où plusieurs processus peuvent tenter d’écrire.
Mais elle sérialise en fin de compte les écritures via des verrous autour du fichier de base de données, en particulier au moment des commits.

PostgreSQL est une base de données serveur conçue pour accepter de nombreuses connexions clientes et soutenir un flux constant de transactions
depuis de nombreuses sessions concurrentes. Elle utilise MVCC (contrôle de concurrence multi-version) pour découpler lecteurs et écrivains et dispose d’une gestion de verrous mature,
de processus d’arrière-plan, d’options de réplication via WAL et d’outils pour vous dire ce qui est bloqué et pourquoi.

Si votre charge inclut plusieurs workers indépendants insérant/mettant à jour fréquemment — pensez requêtes web, jobs en arrière-plan, pipelines d’ingestion
ou toute chose de type « file » — PostgreSQL est le choix ennuyeux et correct. SQLite peut encore être adapté, mais seulement si vous concevez
autour de sa sérialisation d’écriture (regroupement, pattern single-writer, mode WAL avec attentes réalistes).

Règle en une phrase : si la contention d’écriture fait partie du problème, ne choisissez pas une base mono-fichier puis soyez surpris
quand ce fichier devient le goulot d’étranglement.

Faits intéressants et contexte historique

• SQLite a commencé en 2000 comme base embarquée pour des outils internes. Son design « sans serveur » est une fonctionnalité, pas un oubli.
• PostgreSQL remonte aux années 1980 (projet POSTGRES à l’UC Berkeley). Cette lignée se voit dans son focus sur la concurrence et l’extensibilité.
• La base SQLite est un seul fichier (plus des fichiers partagés/WAL optionnels). C’est pratique opérationnellement et hostile à la concurrence.
• Le mode WAL de SQLite (introduit en 2010) a amélioré la concurrence lecteur/écrivain de manière significative, mais n’en a pas fait un moteur multi-écrivains.
• Le WAL de PostgreSQL existe pour garantir la durabilité et permettre la récupération après crash ; il permet aussi la réplication et la récupération point-in-time.
• SQLite est omniprésent : téléphones, navigateurs, appareils, jeux. La charge typique est locale, transactions courtes, faible concurrence d’écriture.
• Le MVCC de PostgreSQL signifie que les mises à jour créent de nouvelles versions de lignes ; les anciennes versions restent jusqu’au vacuum. C’est le prix à payer pour une forte concurrence.
• « database is locked » de SQLite n’est pas un mystère ; c’est le moteur qui vous dit : « j’écris ; attendez votre tour. »
• Les diagnostics de verrous de PostgreSQL sont de première classe via les catalogues système ; vous pouvez voir bloqueurs, attendants et le texte des requêtes en production.

Ce que « écrivains concurrents » signifie réellement dans chaque moteur

La concurrence n’est pas du parallélisme ; c’est de l’ordonnancement sous contention

Quand on dit « écrivains concurrents », on entend souvent « j’ai N workers et je veux un débit à peu près proportionnel à N ».
Cette attente est raisonnable pour PostgreSQL (jusqu’à saturer CPU, I/O, verrous ou latence de commit). Pour SQLite, c’est un mauvais modèle mental sauf si vous avez centralisé les écritures.

Un écrivain n’est pas juste un INSERT ou un UPDATE. C’est :

• acquérir des verrous,
• écrire des pages / enregistrements WAL,
• effectuer des fsync (durabilité),
• mettre à jour des index,
• et potentiellement attendre d’autres écrivains.

Si le système passe 20ms par transaction à attendre le commit durable, vous ne pouvez pas atteindre 10 000 TPS en ouvrant plus de threads. Vous créerez juste une fête d’attente de verrous.

SQLite : « une voie, on fusionne poliment »

SQLite utilise des verrous sur le fichier de base de données (et, en mode WAL, des fichiers de coordination) pour assurer la cohérence. En mode journal rollback
(l’ancien défaut), un écrivain bloque les lecteurs pendant certaines phases du commit. Le mode WAL améliore cela : les lecteurs n’empêchent pas les écrivains d’avancer et inversement dans une certaine mesure. Mais les écrivains se sérialisent toujours. Un seul écrivain peut commit à la fois.

Vous pouvez avoir plusieurs processus tentant d’écrire simultanément. SQLite les mettra en file via la contention de verrous. Si vous ne configurez pas les timeouts et la logique de retry, vous verrez des échecs.

PostgreSQL : « plusieurs voies, feux de circulation et salle de contrôle »

PostgreSQL est conçu pour des sessions concurrentes. Les écrivains ne bloquent généralement pas les lecteurs grâce au MVCC. Les écrivains peuvent se bloquer entre eux,
mais typiquement seulement sur les mêmes lignes (ou sur des verrous lourds comme les changements de schéma).

PostgreSQL dispose de :

• verrous au niveau ligne pour les mises à jour conflictuelles,
• instantanés de transaction,
• processus background writer et checkpointer,
• tampons WAL et group commit,
• et vues d’introspection pour diagnostiquer les attentes.

L’essentiel : la concurrence fait partie de l’architecture, pas d’un mode « ça peut marcher si vous êtes prudent ».

SQLite sous contention d’écriture : que se passe-t-il vraiment

Modes de verrouillage et pourquoi votre benchmark « rapide » ment

SQLite paraît incroyable dans un benchmark mono-thread. Il s’exécute dans le même processus, pas de réseau, pas de changements de contexte serveur,
et le planificateur de requêtes est efficace. Puis vous ajoutez des écrivains.

En mode journal rollback, un écrivain doit verrouiller la base pour des parties de la transaction d’écriture. Le verrou évolue par phases (shared, reserved, pending, exclusive),
et d’autres connexions peuvent être bloquées ou rejetées selon le timing. En mode WAL, les lecteurs peuvent continuer pendant qu’un écrivain ajoute au WAL, mais les commits se sérialisent toujours.

Ce que vous ressentez opérationnellement :

• les latences d’écriture augmentent sous contention,
• des erreurs « database is locked » apparaissent à moins d’attendre/retry,
• les checkpoints deviennent un levier de performance caché,
• et « ajouter plus de workers » cesse rapidement de fonctionner.

Le mode WAL aide, mais ce n’est pas un miracle multi-écrivains

Le mode WAL est généralement le bon choix si vous avez une quelconque concurrence significative avec SQLite. Il évite que les lecteurs bloquent les écrivains
dans le cas courant. Mais WAL introduit une nouvelle variable : les checkpoints. Si les checkpoints ne suivent pas, le fichier WAL grossit et les lecteurs
peuvent être forcés de scanner plus d’historique. Et votre système de fichiers voit de nouveaux motifs d’écriture à gérer.

Vous avez toujours un seul écrivain au moment du commit. C’est la contrainte architecturale. Vous pouvez la gérer. Vous pouvez même la faire fonctionner
correctement. Mais vous ne transformez pas SQLite en écriture concurrente véritable par tuning seul.

Paramètres de durabilité : vous échangez la sécurité contre la vitesse

Les PRAGMA synchronous et PRAGMA journal_mode de SQLite sont là où les équipes font discrètement des compromis avec le démon de la fiabilité.
Si vous définissez synchronous=OFF, vous pouvez absolument obtenir un débit supérieur. Vous pouvez aussi perdre des transactions « commises » en cas de coupure d’alimentation.
Décidez en adulte : écrivez l’exigence de durabilité, puis configurez en conséquence.

Blague n°1 : SQLite est « sans serveur » comme les nouilles instantanées sont « cuisine ». Ça marche, mais ne prétendez pas que c’est la même cuisine.

PostgreSQL sous contention d’écriture : que se passe-t-il vraiment

MVCC : la raison pour laquelle les lecteurs ne hurlent pas quand arrivent les écrivains

Le MVCC de PostgreSQL signifie que les lecteurs voient un instantané de la base à l’ouverture de leur transaction (ou de la requête, selon l’isolation).
Les écrivains créent de nouvelles versions de lignes. Les lecteurs continuent de lire les anciennes versions tant qu’ils ne sont pas terminés. C’est le gain de concurrence fondamental.

Le compromis est le nettoyage. Les anciennes versions de lignes (tuples morts) doivent être vacuumées. Si vous ignorez le vacuum, vous payez plus tard en bloat, churn de cache
et inefficacité des index. PostgreSQL ne vous évitera pas la négligence ; il attendra simplement jusqu’à ce que vous soyez le plus occupé.

WAL, commits et pourquoi fsync est votre plafond de débit réel

Chaque commit durable signifie que le WAL doit atteindre un stockage stable. PostgreSQL peut faire du group commit : plusieurs transactions peuvent partager un fsync.
C’est ainsi qu’il soutient des taux de commit élevés sur un stockage correct. Mais la physique sous-jacente reste : les commits durables sont limités par la latence d’écriture.

Si votre sous-système I/O a une latence de 3–10ms pour les flushs sous charge, votre nombre maximal de commits durables par seconde n’est pas infini, quel que soit le CPU.
C’est là qu’apparaît la réalité SRE : le tuning de performance devient rapidement de l’ingénierie stockage.

Verrouillage : le vrai mode d’échec n’est pas « les verrous existent », c’est « les verrous vous surprennent »

PostgreSQL a une sémantique de verrous robuste. Ce n’est pas le problème. Le problème, ce sont les équipes qui ne comprennent pas d’où viennent les verrous :

• transactions longues maintenant des verrous de ligne,
• migrations de schéma prenant des verrous ACCESS EXCLUSIVE,
• autovacuum ou vacuum full,
• contrôles de clés étrangères sous fort taux d’écriture,
• points chauds sur les mêmes pages d’index (surtout avec des clés monotones croissantes).

Vous pouvez voir bloqueurs et attendants dans PostgreSQL. Utilisez cela. Ne devinez pas.

Citation (idée paraphrasée), attribuée : Werner Vogels insiste souvent que « tout échoue, tout le temps » — concevez des systèmes pour que l’échec soit normal, pas exceptionnel.

Blague n°2 : Si vous placez SQLite derrière un serveur web et appelez ça « distribué », félicitations — vous venez d’inventer un très petit embouteillage.

Latence, fsync et couche de stockage (où résident la plupart des « problèmes DB »)

Les écrivains concurrents ne sont pas qu’une histoire de moteur de base de données. C’est une histoire de stockage. Les transactions durables signifient que vous devez flush.
Flush signifie que le noyau et le périphérique conviennent que les données sont sur un support stable. Cette opération a une latence et une variance.
La variance est le tueur : la latence p99 de commit détermine le backlog de la file, l’accumulation d’attentes de verrous et la latence de queue dans les services.

SQLite utilise typiquement les verrous du système de fichiers et écrit dans un fichier unique (plus journal/WAL). Sur de nombreux systèmes de fichiers, la contention sur ce fichier
et ses métadonnées apparaît rapidement sous de nombreux écrivains. PostgreSQL répartit le travail sur plusieurs fichiers (segments de relation) et maintient un WAL,
mais dépend toujours du comportement de flush du stockage.

Implication pratique : si PostgreSQL est lent sous charge d’écriture, vous pouvez souvent le corriger par de meilleurs IOPS/latences, le tuning WAL,
le tuning des checkpoints, des changements de schéma/index, du batching, ou du partitionnement. Si SQLite est lent avec de nombreux écrivains, la solution est généralement architecturale :
réduisez la concurrence sur le fichier de base ou migrez vers une base serveur.

Playbook de diagnostic rapide

Quand « les écritures sont lentes » frappe en production, ne commencez pas par des guerres d’opinion. Commencez par des preuves. Voici l’ordre qui minimise le temps gaspillé.

Première étape : confirmer la classe du goulot (verrou, CPU ou I/O)

• SQLite : voyez-vous database is locked ou de longues attentes sur les écritures ? C’est de la contention, pas une « lenteur mystérieuse ».
• PostgreSQL : vérifiez les wait events. Si des sessions attendent des verrous, c’est de la contention. Si elles attendent le WAL ou l’I/O, c’est le stockage/commit. Si le CPU est saturé, c’est du calcul/requêtes/index.

Deuxième étape : isoler latence de commit vs travail de requête

• Si les transactions sont petites mais que les commits sont lents, regardez fsync, flush WAL et latence de stockage.
• Si les commits sont rapides mais que les instructions sont lentes, regardez les index, les lignes chaudes et les plans de requête.

Troisième étape : vérifier s’il y a « une grosse transaction » qui tient tout le monde en otage

• Les transactions longue durée bloquent le vacuum dans PostgreSQL, ce qui augmente le bloat et peut se transformer en amplification d’écriture.
• Dans SQLite, une transaction ouverte pendant un batch peut garder le verrou d’écriture plus longtemps que prévu.

Quatrième étape : vérifier que votre contrôle de concurrence est volontaire

• SQLite : avez-vous un busy timeout et une stratégie de retry/backoff ? Le mode WAL est-il activé ? Checkpointez-vous raisonnablement ?
• PostgreSQL : avez-vous un pool de connexions ? Saturiez-vous le serveur avec trop d’écrivains concurrents ?

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

Voici le type de tâches que vous lancez pendant un incident ou une décision de migration. Chacune inclut : la commande, ce que signifie la sortie,
et la décision à partir de celle-ci. Les commandes sont réalistes ; adaptez les chemins et noms de services.

Tâche 1 : Trouver le journal_mode et le niveau synchronous de SQLite

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

Sens : le journal_mode est WAL ; synchronous=2 (FULL). Vous payez la durabilité à chaque transaction.

Décision : Si la latence est trop élevée, essayez d’abord de regrouper les écritures ou de réduire la fréquence de commit, pas de désactiver la durabilité.
Ne relaxez synchronous que si l’entreprise accepte explicitement la perte de données en cas de crash.

Tâche 2 : Chercher des erreurs de verrou dans les logs SQLite

cr0x@server:~$ journalctl -u app.service -n 200 | grep -E 'database is locked|SQLITE_BUSY' | tail
Dec 30 09:22:11 server app[1842]: ERROR db write failed: database is locked
Dec 30 09:22:12 server app[1842]: ERROR db write failed: SQLITE_BUSY in insert_event

Sens : l’application n’attend pas assez longtemps, ou la contention est suffisamment élevée pour que les timeouts expirent.

Décision : Ajoutez busy_timeout, implémentez un retry avec jitter, et réduisez le nombre d’écrivains concurrents (pattern file d’écriture unique).
Si les écrivains sont vraiment indépendants et à haut débit, planifiez une migration vers PostgreSQL.

Tâche 3 : Confirmer le busy timeout SQLite à l’exécution

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

Sens : pas de busy timeout. SQLite échouera immédiatement quand il ne peut pas obtenir le verrou.

Décision : Définissez un timeout raisonnable dans la configuration de connexion de l’application (par ex. 2000–10000ms selon les SLO) et ajoutez des retries.

Tâche 4 : Observer la croissance du WAL (pression de checkpoint)

cr0x@server:~$ ls -lh /var/lib/app/app.db*
-rw-r----- 1 app app 1.2G Dec 30 09:23 /var/lib/app/app.db
-rw-r----- 1 app app 3.8G Dec 30 09:23 /var/lib/app/app.db-wal
-rw-r----- 1 app app  32K Dec 30 09:23 /var/lib/app/app.db-shm

Sens : le WAL est énorme par rapport à la base. Le checkpointing ne suit pas ou est bloqué par des lecteurs longs.

Décision : Identifiez les transactions de lecture longues ; ajustez la stratégie de checkpoint (checkpoint wal_checkpoint au niveau applicatif si approprié),
ou déplacez la charge d’écriture lourde hors de SQLite.

Tâche 5 : Vérifier les lecteurs SQLite longue durée (bloqueurs fréquents de checkpoint WAL)

cr0x@server:~$ lsof /var/lib/app/app.db | head
COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
app     1842  app   12u   REG  253,0 1288490189  49155 /var/lib/app/app.db
worker  1910  app   10u   REG  253,0 1288490189  49155 /var/lib/app/app.db
report  2201  app    8u   REG  253,0 1288490189  49155 /var/lib/app/app.db

Sens : plusieurs processus ont la BD ouverte. C’est normal, mais des jobs de reporting longue durée peuvent garder des snapshots ouverts.

Décision : Assurez-vous que le reporting utilise des réplicas séparés (dans PostgreSQL) ou s’exécute sur des données exportées. Dans SQLite, raccourcissez les transactions de lecture et évitez « ouvrir une transaction tout en streamant les résultats ».

Tâche 6 : PostgreSQL : voir qui attend et sur quoi

cr0x@server:~$ psql -d appdb -c "SELECT pid, usename, wait_event_type, wait_event, state, now()-query_start AS age, left(query,80) AS query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC LIMIT 8;"
 pid  | usename | wait_event_type | wait_event | state  |   age    | query
------+--------+-----------------+------------+--------+----------+-----------------------------------------------
 6241 | app    | Lock            | tuple      | active | 00:01:12 | UPDATE orders SET status='paid' WHERE id=$1
 6310 | app    | IO              | DataFileRead| active | 00:00:49 | INSERT INTO events(ts, type, payload) VALUES...
 6188 | app    | WAL             | WALWrite   | active | 00:00:20 | INSERT INTO events(ts, type, payload) VALUES...

Sens : vous avez au moins trois types de goulots : attente de verrous de ligne (tuple), lectures de fichiers de données, et écritures WAL.

Décision : Pour Lock/tuple, trouvez le bloqueur et réduisez la contention sur les lignes chaudes. Pour WAL, investiguez la latence du device de commit/WAL et les paramètres de checkpoint.
Pour DataFileRead, vérifiez le taux de cache hit et les index manquants.

Tâche 7 : PostgreSQL : trouver la requête bloquante

cr0x@server:~$ psql -d appdb -c "SELECT blocked.pid AS blocked_pid, blocker.pid AS blocker_pid, blocked.query AS blocked_query, blocker.query AS blocker_query FROM pg_stat_activity blocked JOIN pg_locks bl ON bl.pid=blocked.pid AND NOT bl.granted JOIN pg_locks kl ON kl.locktype=bl.locktype AND kl.database IS NOT DISTINCT FROM bl.database AND kl.relation IS NOT DISTINCT FROM bl.relation AND kl.page IS NOT DISTINCT FROM bl.page AND kl.tuple IS NOT DISTINCT FROM bl.tuple AND kl.virtualxid IS NOT DISTINCT FROM bl.virtualxid AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid AND kl.classid IS NOT DISTINCT FROM bl.classid AND kl.objid IS NOT DISTINCT FROM bl.objid AND kl.objsubid IS NOT DISTINCT FROM bl.objsubid AND kl.pid != bl.pid JOIN pg_stat_activity blocker ON blocker.pid=kl.pid;"
 blocked_pid | blocker_pid |           blocked_query            |             blocker_query
------------+------------+------------------------------------+------------------------------------------
       6241 |       6177 | UPDATE orders SET status='paid'... | UPDATE orders SET status='paid'...

Sens : des écrivains se percutent sur les mêmes lignes (ou des lignes chaudes adjacentes).

Décision : Corrigez la logique applicative : évitez que plusieurs workers se battent pour le même ensemble de lignes ; utilisez les patrons de file avec SELECT ... FOR UPDATE SKIP LOCKED ; partitionnez les tables chaudes.

Tâche 8 : PostgreSQL : vérifier la pression de commit et WAL via les stats

cr0x@server:~$ psql -d appdb -c "SELECT datname, xact_commit, xact_rollback, blks_read, blks_hit, temp_files, temp_bytes FROM pg_stat_database WHERE datname='appdb';"
 datname | xact_commit | xact_rollback | blks_read | blks_hit | temp_files | temp_bytes
--------+-------------+---------------+----------+---------+-----------+-----------
 appdb  |     8921341 |         31221 |  1842290 | 44211987|      1842 | 987654321

Sens : volume de commits élevé et usage notable de fichiers temporaires. Les fichiers temporaires signifient souvent des tris/hashes qui déversent sur le disque.

Décision : Si les spills temporaires corrèlent avec la lenteur d’écriture, ajustez work_mem pour les chemins de requête concernés ou ajoutez des index pour éviter de gros tris.
Le volume de commits suggère du regroupement ou l’utilisation de moins de transactions si l’application peut le tolérer.

Tâche 9 : PostgreSQL : surveiller le comportement des checkpoints (coupable classique de latence d’écriture)

cr0x@server:~$ psql -d appdb -c "SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_backend FROM pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint | buffers_backend
------------------+-----------------+-----------------------+----------------------+-------------------+----------------
              122 |             987 |                932112 |               121009 |           8123344 |         223445

Sens : de nombreuses checkpoints demandées (forcées par le volume WAL) et un temps d’écriture de checkpoint important. Il y a aussi des écritures backend.

Décision : Réduisez les pics de checkpoint : augmentez max_wal_size, ajustez checkpoint_timeout, augmentez checkpoint_completion_target.
Puis validez que le stockage peut soutenir le taux d’écriture.

Tâche 10 : PostgreSQL : mesurer rapidement le taux de cache hit

cr0x@server:~$ psql -d appdb -c "SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit)+sum(blks_read),0),2) AS cache_hit_pct FROM pg_stat_database;"
 cache_hit_pct
--------------
         96.01

Sens : taux de hit correct, mais 96% sous forte charge peut encore signifier beaucoup de lectures.

Décision : Si le débit d’écriture est freiné par des lectures (index manquants, I/O aléatoire), concentrez-vous sur les plans de requête et les index avant d’acheter du hardware.

Tâche 11 : Niveau système : confirmer la latence de stockage sous charge

cr0x@server:~$ iostat -xz 1 3
Linux 6.8.0 (server)  12/30/2025  _x86_64_  (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    6.11   14.90    0.00   60.77

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz  aqu-sz  %util
nvme0n1         120.0   12288.0     0.0   0.00    1.80   102.40   950.0   97280.0     0.0   0.00   12.40   102.40   4.10  96.00

Sens : write await ~12ms avec 96% d’utilisation et iowait significatif. Votre « problème de base de données » est maintenant un problème de stockage.

Décision : Réduisez la fréquence de commit synchrone (regroupement/grouping), déplacez le WAL vers un stockage plus rapide, ou améliorez le dispositif/RAID/cache.
Validez aussi que vous n’êtes pas limité par les quotas de volume cloud.

Tâche 12 : PostgreSQL : vérifier le taux de génération WAL

cr0x@server:~$ psql -d appdb -c "SELECT now(), pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),'0/0')) AS wal_since_boot;"
              now              | wal_since_boot
------------------------------+----------------
 2025-12-30 09:25:33.11452+00 | 384 GB

Sens : vous produisez beaucoup de WAL. C’est normal pour de fortes écritures, mais cela entraîne checkpoints et bande passante de réplication.

Décision : Si le volume WAL est anormalement élevé, vérifiez le churn d’updates (mises à jour fréquentes des mêmes lignes), les index gonflés, ou les index inutiles.
Envisagez le partitionnement ou la réduction des index secondaires sur les tables d’ingestion.

Tâche 13 : PostgreSQL : trouver rapidement les requêtes d’écriture les plus lourdes

cr0x@server:~$ psql -d appdb -c "SELECT calls, rows, mean_exec_time, left(query,90) AS query FROM pg_stat_statements WHERE query ILIKE 'insert%' OR query ILIKE 'update%' ORDER BY mean_exec_time DESC LIMIT 5;"
 calls |  rows  | mean_exec_time | query
-------+--------+----------------+------------------------------------------------------------------------------------------
  8821 |  8821  |          14.22 | INSERT INTO events(ts, type, payload) VALUES ($1,$2,$3)
  1102 |  1102  |          11.90 | UPDATE orders SET status=$1, updated_at=now() WHERE id=$2

Sens : les inserts sont significativement coûteux. Cela peut venir du WAL/fsync, des index, ou de la contention.

Décision : Si le temps moyen d’insert suit les attentes WAL, concentrez-vous sur le commit et le device WAL. Si c’est lié au CPU, réduisez les index ou regroupez les inserts en multi-row insert ou COPY.

Tâche 14 : SQLite : tester la contention en simulant deux écrivains (repro rapide)

cr0x@server:~$ bash -lc 'sqlite3 /tmp/t.db "PRAGMA journal_mode=WAL; CREATE TABLE IF NOT EXISTS t(id INTEGER PRIMARY KEY, v TEXT);"; (sqlite3 /tmp/t.db "BEGIN; INSERT INTO t(v) VALUES(\"a\"); SELECT \"writer1 holding\"; SELECT sleep(2); COMMIT;" &) ; (sqlite3 /tmp/t.db "PRAGMA busy_timeout=500; INSERT INTO t(v) VALUES(\"b\");" ; wait)'
wal
writer1 holding
Error: database is locked

Sens : même en mode WAL, si le premier écrivain maintient une transaction, le second échoue s’il ne peut pas attendre suffisamment longtemps.

Décision : Gardez les transactions courtes ; définissez busy timeout ; ne maintenez pas de transaction d’écriture pendant des opérations applicatives.
Si vous avez besoin de véritables écritures concurrentes, cessez de lutter contre la physique : migrez vers PostgreSQL.

Trois mini-histoires du monde de l’entreprise (anonymisées, plausibles, douloureuses)

Incident : la fausse hypothèse (« SQLite gère les écritures concurrentes, non ? »)

Une équipe de taille moyenne a livré une fonctionnalité « analytics léger » dans un service qui traitait déjà des requêtes utilisateur.
Ils ont choisi SQLite parce que c’était simple : un fichier, un script de migration, et pas nouvelle infrastructure. Ils ont même activé le mode WAL.
En staging, ça volait. En production, le service tournait avec 12 processus workers et une file de jobs en rafales.

Le premier symptôme était subtil : des 500 sporadiques lors des pics de trafic, tous pointant vers des inserts échoués. Le texte d’erreur était clair :
database is locked. Mais l’équipe l’a lu comme un glitch transitoire plutôt qu’une contrainte de conception.
Ils ont ajouté des retries. C’était « mieux », puis pire.

Le vrai problème était que chaque worker ouvrait une transaction, faisait quelques inserts, puis effectuait un appel réseau avant de committer.
Cet appel réseau prenait parfois des centaines de millisecondes. Pendant ce temps, le verrou d’écrivain était maintenu. Les autres workers se mettaient en file, expiraient,
relançaient et créaient une tempête auto-amplifiée de tentatives de verrou.

Ils ont corrigé en deux phases. D’abord, ils ont déplacé l’appel réseau en dehors de la transaction et raccourci la portée de la transaction.
Cela a stabilisé le service. Ensuite, ils ont migré les écritures analytics vers PostgreSQL, laissant SQLite pour le cache local sur les nœuds edge.
Ce ne fut plus dramatique. C’était ennuyeux. Être ennuyeux est l’objectif.

Optimisation qui a échoué : « Augmentons la concurrence pour accélérer l’ingestion »

Une autre entreprise disposait d’un pipeline d’ingestion soutenu par PostgreSQL écrivant des événements dans une grande table.
Pour atteindre un nouvel objectif de débit, ils ont augmenté le nombre de workers d’ingestion de « quelques » à « beaucoup ».
Le CPU est resté correct. Le réseau aussi. La latence a pourtant explosé.

Ils ont supposé que la base scalerait linéairement avec le nombre de workers. À la place, le système a atteint la contention et les limites I/O.
L’autovacuum a travaillé plus. Les checkpoints sont devenus plus fréquents à cause du volume WAL.
Le p99 du temps de commit a monté, ce qui a bloqué la file, augmentant encore le parallélisme parce que « les workers étaient inactifs en attente ».

Ils avaient aussi un index bien intentionné sur un champ JSON à haute cardinalité qui était presque jamais utilisé pour les lectures.
Chaque insert payait pour cet index. Sous faible concurrence, c’était tolérable. Sous forte concurrence, c’était la taxe qui les a fait basculer.
Le système n’était pas « lent ». Il faisait exactement ce qu’on lui demandait, juste pas ce qu’on voulait.

La correction n’a pas été « plus de tuning ». Ce fut : réduire le nombre de workers pour correspondre à la capacité de commit du stockage, supprimer ou retarder l’index coûteux,
et regrouper les inserts en utilisant COPY pendant les fenêtres de pic. Après cela, le débit s’est amélioré et la latence s’est stabilisée.
Ils ont retenu la leçon SRE : la concurrence est un outil, pas une vertu.

Pratique ennuyeuse mais correcte qui a sauvé la mise : « Nous avons dimensionné le WAL et pratiqué les restaurations »

Un système adjacent aux paiements (pas le grand livre, mais assez critique) utilisait PostgreSQL avec une charge d’écriture régulière.
Le manager engineering a insisté sur trois choses que personne ne trouvait excitantes :
revue régulière du vacuum, paramètres WAL et checkpoint documentés avec justification, et exercices de restauration périodiques vers un environnement séparé.

Un après-midi, une migration de schéma déployait un nouvel index. Ce n’était pas une migration catastrophique, mais elle a augmenté l’amplification d’écriture et la génération WAL plus que prévu.
Le lag de réplication a augmenté. Les alertes se sont déclenchées. L’astreinte a pu le voir dans les statistiques :
plus de checkpoints demandés, plus de volume WAL, montée de la latence des commits.

Parce que l’équipe avait déjà dimensionné max_wal_size et les cibles de checkpoint de manière conservatrice, le système n’a pas immédiatement commencé à trimer.
Parce qu’ils avaient pratiqué les restaurations, rollbacker la migration et redeployer une version plus sûre n’a pas été terrifiant.
Et parce que la santé du vacuum était monitorée, le bloat n’a pas aggravé le problème.

L’incident est resté agaçant, mais il est resté dans la catégorie « agaçant ». Pas de perte de données. Pas de récupération épique de plusieurs jours.
Les pratiques ennuyeuses n’ont pas fait la une. Elles les ont évitées.

Erreurs courantes : symptômes → cause racine → correction

1) Symptôme : SQLite « database is locked » pendant les pics

Cause racine : trop d’écrivains concurrents, pas de busy timeout, transactions longues, ou écritures effectuées en maintenant la transaction ouverte.

Correction : activez le mode WAL ; définissez busy_timeout ; gardez les transactions courtes ; implémentez le retry avec jitter ; centralisez les écritures via un writer unique ; migrez les chemins d’écriture lourds vers PostgreSQL.

2) Symptôme : le fichier WAL SQLite grossit indéfiniment

Cause racine : checkpoints non exécutés, checkpoints bloqués par des lecteurs longs, ou un modèle applicatif qui garde les transactions de lecture ouvertes.

Correction : raccourcissez les transactions de lecture ; évitez les lectures en streaming pendant une transaction ouverte ; lancez des checkpoints périodiques ; envisagez de séparer le reporting des écritures OLTP.

3) Symptôme : les inserts PostgreSQL ralentissent à mesure que la concurrence augmente

Cause racine : latence de commit liée au WAL flush, saturation du stockage, ou trop de petites transactions.

Correction : regroupez les inserts ; utilisez COPY ; assurez-vous que le WAL est sur un stockage à faible latence ; tunez les checkpoints (max_wal_size, checkpoint_completion_target) ; envisagez async commit uniquement si acceptable.

4) Symptôme : écrivains PostgreSQL bloqués en attente de verrous

Cause racine : lignes chaudes, patrons de file sans SKIP LOCKED, transactions longues, ou DDL verrouillant pendant la charge de pointe.

Correction : redessinez les points chauds ; utilisez SELECT ... FOR UPDATE SKIP LOCKED ; gardez les transactions courtes ; exécutez les DDL avec des patterns favorables aux verrous ; planifiez les migrations lourdes hors-pointe.

5) Symptôme : bloat PostgreSQL et amplification d’écriture qui s’aggrave avec le temps

Cause racine : autovacuum en retard, transactions longues empêchant le nettoyage, ou mises à jour fréquentes des mêmes lignes.

Correction : surveillez le vacuum ; corrigez les transactions longues ; ajustez les seuils autovacuum par table ; réduisez le churn ; partitionnez les données à fort churn.

6) Symptôme : « Nous avons ajouté des index et c’est devenu plus lent »

Cause racine : chaque insert/update doit maintenir chaque index ; le chemin d’écriture est devenu plus lourd que le bénéfice lecture.

Correction : conservez seulement les index qui se rentabilisent ; créez les index après backfills ; utilisez des index partiels ; benchmarkez le coût d’écriture sous une concurrence réaliste.

Listes de contrôle / plan pas à pas

Checklist de décision : ce workload doit-il être sur SQLite ou PostgreSQL ?

1. Comptez vos écrivains. Si vous attendez plusieurs processus/threads indépendants écrivant fréquemment, partez par défaut sur PostgreSQL.
2. Définissez la durabilité. Si vous ne pouvez pas tolérer la perte d’écritures « réussies » en cas de crash, n’utilisez pas les pragmas SQLite non sûrs. Les valeurs par défaut de PostgreSQL sont plus sûres.
3. La forme des transactions compte. Si vous pouvez regrouper et tolérer un pattern single-writer, SQLite peut fonctionner.
4. Exigences opérationnelles. Si vous avez besoin de réplication, PITR, changements de schéma en ligne et introspection : PostgreSQL.
5. Environnement de déploiement. Si vous ne pouvez pas exécuter un serveur DB (appareils edge, apps hors-ligne), SQLite est une bénédiction.

Pas à pas : faire bien se comporter SQLite sous une concurrence d’écriture modeste

1. Activez le mode WAL.
2. Définissez un busy timeout et implémentez un retry avec jitter.
3. Gardez les transactions d’écriture courtes ; ne les maintenez jamais pendant des appels réseau.
4. Regroupez les écritures : moins de commits, plus de travail par transaction.
5. Contrôlez le checkpointing si le WAL grossit (et vérifiez que les lecteurs longs ne bloquent pas).
6. Si votre file d’écriture continue de croître, arrêtez d’optimiser et migrez.

Pas à pas : stabiliser le débit d’écriture PostgreSQL

1. Mesurez les wait events et la contention de verrous dans pg_stat_activity.
2. Vérifiez checkpoints et pression WAL dans pg_stat_bgwriter.
3. Validez la latence de stockage avec iostat ; confirmez les performances du device WAL.
4. Réduisez le nombre de transactions en regroupant ; utilisez COPY pour l’ingestion.
5. Supprimez les index inutiles et coûteux sur les tables d’ingestion.
6. Corrigez la contention sur les lignes chaudes (patrons de file, compteurs, mises à jour de statut).
7. Assurez-vous que le vacuum suit ; corrigez les transactions longues.
8. Ajoutez de la capacité seulement après avoir confirmé ce qui est saturé.

FAQ

1) SQLite peut-il gérer plusieurs écrivains du tout ?

Plusieurs connexions peuvent tenter d’écrire, mais SQLite sérialise le travail d’écriture/commit réel via des verrous. Sous contention, vous verrez des waits ou SQLITE_BUSY.
C’est viable si vous gardez les transactions courtes et acceptez que les écritures se mettent en file les unes derrière les autres.

2) Le mode WAL rend-il SQLite « concurrent » comme PostgreSQL ?

Le mode WAL améliore la concurrence lecteur/écrivain. Il ne transforme pas SQLite en moteur multi-écrivains. Les écrivains se sérialisent toujours au commit.
WAL introduit aussi un comportement de checkpoint à connaître, sinon vous aurez des surprises d’utilisation disque et de latence.

3) Pourquoi PostgreSQL tolère-t-il mieux de nombreux écrivains ?

Le MVCC réduit le blocage lecteur/écrivain, les verrous au niveau ligne localisent les conflits, et le système est construit autour de sessions concurrentes avec des processus d’arrière-plan.
Il prend aussi en charge le group commit, ce qui aide à amortir le coût de fsync sur de nombreuses transactions.

4) Quelle est la vraie limite du débit d’écriture PostgreSQL ?

Souvent : la latence de commit durable (flush WAL) et le débit du stockage. Ensuite : la contention sur les lignes/index chauds, le comportement des checkpoints, et le CPU pour la maintenance d’index.
La limite n’est généralement pas « PostgreSQL ne peut pas », mais « votre stockage et votre schéma ne conviennent pas à votre charge ».

5) Dois-je simplement désactiver fsync/synchronous pour gagner en vitesse ?

Seulement si vous êtes à l’aise de perdre des données en cas de crash/coupure d’alimentation. Dans SQLite, PRAGMA synchronous=OFF est un levier réel de perte de données.
Dans PostgreSQL, changer les paramètres de durabilité a des compromis similaires. Notez d’abord la fenêtre de perte acceptable.

6) Le pooling de connexions est-il requis pour les écrivains PostgreSQL ?

Si vous avez de nombreuses instances applicatives, oui. Ce n’est pas parce que le pool accélère magiquement les requêtes, mais parce que trop de connexions créent de l’overhead
et peuvent augmenter la contention. Le pooling vous aide à contrôler la concurrence et à garder la base dans sa plage efficace.

7) Quand SQLite est-il le bon choix même avec des écritures ?

Applications local-first, systèmes mono-utilisateur, appareils edge, outils CI/test, couches de cache, ou quand vous pouvez imposer un writer unique et regrouper les mises à jour.
Si vous ne pouvez pas faire respecter ces contraintes, SQLite devient une dépendance fragile.

8) Quel est le chemin de migration le plus courant de SQLite vers PostgreSQL ?

Généralement : garder SQLite pour le cache local/mode hors-ligne, déplacer les écritures autoritaires vers PostgreSQL, ajouter un processus de synchronisation, puis supprimer progressivement les écritures SQLite.
L’essentiel est de cesser de traiter SQLite comme un hub d’écritures partagé pour des workers concurrents.

9) Pourquoi vois-je des attentes de verrous PostgreSQL alors que je fais « seulement des inserts » ?

Les inserts touchent toujours les index, les séquences, les clés étrangères, et parfois des pages chaudes. Si plusieurs sessions insèrent dans la même table avec le même motif d’index,
vous pouvez voir de la contention. De plus, les inserts qui mettent à jour des lignes « statut » ou des compteurs créent immédiatement des lignes chaudes.

10) Quel est le pattern le plus simple et sûr pour une file de travail ?

Dans PostgreSQL : une table de file avec SELECT ... FOR UPDATE SKIP LOCKED et des transactions courtes. Dans SQLite : un processus writer unique qui dequeue et écrit,
avec des lecteurs consommant des snapshots selon les besoins.

Prochaines étapes que vous pouvez réellement exécuter

Si vous choisissez entre PostgreSQL et SQLite pour un système avec écrivains concurrents, décidez en fonction de qui gère la contention.
SQLite fait porter la charge sur votre application. PostgreSQL partage le fardeau et vous donne des tableaux de bord, des leviers et des échappatoires.

• Si vous êtes sur SQLite et voyez des erreurs de verrou : activez WAL, définissez busy timeout, raccourcissez les transactions, centralisez les écritures. Si la charge nécessite encore de nombreux écrivains, planifiez une migration PostgreSQL.
• Si vous êtes sur PostgreSQL et que les écritures sont lentes : vérifiez les wait events et les bloqueurs, puis vérifiez WAL/checkpoints, puis la latence du stockage. Corrigez les lignes chaudes et la forme des transactions avant d’acheter du hardware.
• Dans les deux cas : mesurez la latence de commit p95/p99 et le taux de transactions. C’est la vérité terrain pour « écrivains concurrents ».

Le « gagnant » n’est pas un jugement moral. C’est un choix adapté au besoin. Pour des écrivains concurrents en production, PostgreSQL est l’outil mature.
Utilisez SQLite là où sa simplicité mono-fichier est une superpuissance — pas là où elle devient votre générateur de panne.