PostgreSQL vs SQLite : chemin d’évolution — comment migrer d’une base fichier sans temps d’arrêt

octobre 30, 2025 • février 3, 2026 • Lecture : 32 min • Views: 11

Cet article vous a aidé ?

Vous ne « dépassez » pas SQLite un mardi à 14h. Vous le dépassez à 2 h 07 quand un déploiement anodin ajoute un écrivain supplémentaire, vos graphiques de latence deviennent de l’art moderne, et quelqu’un demande pourquoi « un fichier » bloque une flotte de serveurs web.

Voici le chemin pragmatique d’évolution : comment passer de SQLite (une excellente base embarquée) à PostgreSQL (un excellent serveur de base) sans temps d’arrêt — ou, plus réalistement, avec un temps d’arrêt si court qu’il se cache entre les contrôles de santé du load balancer. Nous aborderons les écritures doubles, la capture de changements, la parité des données, la mécanique de bascule et les modes d’échec qu’on n’apprend qu’en faisant cela en production.

Quand SQLite est la bonne réponse (et quand ce n’est pas le cas)

SQLite n’est pas une « base de donnée pour jouets ». C’est une réalisation d’ingénierie sérieuse : une base SQL transactionnelle dans une seule bibliothèque qui écrit dans un seul fichier. Elle est embarquée. Elle est portable. Elle est extrêmement simple à déployer. Cette combinaison est si puissante qu’il est facile de se tromper d’usage.

SQLite est gagnant quand

Votre application est essentiellement mono-processus ou mono-écrivain et la base vit sur le disque local.
Vous avez besoin d’un faible coût opérationnel : pas de démon, pas de réseau, pas de gestion d’utilisateurs, pas de sauvegardes nécessitant des snapshots cohérents entre hôtes.
Vous déployez des logiciels sur des périphériques edge, postes de travail, mobiles, bornes ou appliances. SQLite est pratiquement la lingua franca là-bas.
Votre « base de données » fait partie de votre artefact : catalogue empaqueté, jeu de données statique, cache reconstruisible ou queue locale.

SQLite commence à poser problème quand

Vous introduisez plusieurs écrivains concurrents (ou vous pensiez ne pas en avoir, mais votre code en décide autrement).
Vous scalez horizontalement et plusieurs instances applicatives doivent partager la même vérité.
Votre système de fichiers devient problématique : systèmes de fichiers en réseau, couches d’overlay de conteneurs, disques lents, voisins bruyants.
Vous avez besoin de fonctionnalités opérationnelles comme HA managé, réplicas en lecture, contrôles d’accès fins, changements de schéma en ligne et observabilité intégrée au serveur.

Le modèle de verrouillage de SQLite est la clé : il excelle pour sérialiser un écrivain et de nombreux lecteurs, mais « exceller » n’est pas synonyme d’« infini ». Si votre charge évolue vers « beaucoup de petites écritures depuis plusieurs hôtes », PostgreSQL devient l’adulte dans la pièce.

Une courte blague (1/2) : SQLite, c’est comme un vélo : parfait jusqu’à ce que vous essayiez de tracter un bateau avec.

PostgreSQL vs SQLite : points de décision importants en production

Concurrence et verrouillage : la vraie raison des migrations

SQLite coordonne l’accès à un seul fichier de base. Les lecteurs peuvent être concurrents ; les écrivains sont plus contraints. En mode WAL, vous obtenez une concurrence nettement améliorée — les lecteurs bloquent moins les écrivains — mais il reste un endroit principal où la vérité réside : ce fichier.

PostgreSQL est un serveur multi-processus avec MVCC conçu pour des écritures concurrentes depuis de nombreux clients, avec des verrous au niveau des lignes, la visibilité des snapshots, et un WAL séparé pour la durabilité et la réplication. Si vous construisez quelque chose multi-tenant, multi-worker ou multi-hôte, PostgreSQL est simplement conçu pour ça.

Durabilité et récupération : « c’est commité » devrait signifier la même chose chaque jour

SQLite peut être extrêmement durable si bien configuré, mais il est aussi facile de le configurer dans un mode « rapide mais peut-être » (synchronous=NORMAL/OFF, réglages du journal, comportement fsync non sûr dans certains environnements). PostgreSQL a ses propres pièges, mais en général ses défauts par défaut vont vers la durabilité serveur avec WAL et récupération après crash comme fonctionnalité prioritaire.

Ergonomie opérationnelle

SQLite fait de votre application l’opérateur de la base. C’est acceptable jusqu’à ce que vous fassiez mal le travail d’opérateur. PostgreSQL fait du serveur de base l’opérateur, que vous devez toujours exécuter, patcher, sauvegarder et observer — mais au moins les frontières sont claires et l’écosystème d’outils est mature.

Planificateur de requêtes et fonctionnalités

Le planificateur de PostgreSQL, ses index et ses fonctionnalités avancées (CTE, fonctions fenêtres, index partiels, GIN/GiST, JSONB, colonnes générées, contraintes robustes) changent la manière de modéliser les données et de garder la latence prévisible. SQLite a aussi beaucoup de fonctionnalités, mais la feuille de route « gros système sous charge » suppose généralement une base serveur.

Ce n’est pas une supériorité morale. C’est un ajustement d’architecture. SQLite n’est pas « pire ». Il est « différent », et la différence commence à vous coûter quand votre charge change.

Faits intéressants et courte histoire (qui influencent réellement vos décisions)

SQLite a été créé en 2000 par D. Richard Hipp pour éviter la charge administrative et remplacer des fichiers plats ad hoc dans les systèmes embarqués.
SQLite est dans le domaine public, ce qui est inhabituel pour un moteur de base de données et une grande raison pour laquelle il est distribué partout sans drame de licence.
SQLite vise « petit, rapide, fiable » en tant que bibliothèque — pas de processus serveur séparé — donc votre application hérite des responsabilités de la base (permissions de fichier, espace disque, sémantique de verrouillage).
PostgreSQL descend de POSTGRES (1986), un projet de recherche qui a évolué en un système relationnel de production avec un fort accent sur la correction et l’extensibilité.
Le modèle MVCC de PostgreSQL est devenu une pierre angulaire pour une forte concurrence sans blocage lecteur/écrivain dans les charges OLTP typiques.
Le mode WAL de SQLite a été une avancée majeure pour la concurrence ; de nombreuses équipes « découvrent » WAL après leur première tempête de verrous.
La réplication PostgreSQL a mûri au fil du temps : la réplication physique en streaming est ancienne et éprouvée ; la réplication logique est plus récente et change la manière de faire les migrations et les rollouts.
SQLite est le moteur de base de données le plus déployé en nombre d’appareils/paquets, même si personne ne « l’exécute » en tant que serveur.

Le chemin de montée en charge : de la base fichier à la base serveur sans temps d’arrêt

Voici l’objectif de migration énoncé clairement : continuer à servir les requêtes pendant que vous déplacez l’ensemble de données autoritatif d’un fichier SQLite vers PostgreSQL, puis basculer les lectures/écritures vers PostgreSQL avec un risque minimal.

Si votre application peut tolérer une fenêtre de maintenance, parfait. Faites-le. Mais si vous ne le pouvez pas — parce que vous êtes un SaaS B2B avec des clients dans tous les fuseaux, ou parce que vous exploitez une flotte d’appliances qui ne dort jamais — alors vous avez besoin d’un plan qui traite la migration comme une mise en production : incrémentale, observable, réversible.

Étape 0 : Définir ce que « pas de temps d’arrêt » signifie réellement

Les migrations sans temps d’arrêt signifient souvent l’une de ces options :

Pas de temps d’arrêt visible : les utilisateurs ne remarquent rien parce que les retries et les contrôles de santé absorbent le clignotement.
Pas de temps d’arrêt pour les écritures : les lectures peuvent se dégrader mais les écritures continuent (ou l’inverse).
Pas de temps d’arrêt planifié : vous ne planifiez pas de fenêtre, mais vous pouvez quand même avoir un bref incident de bascule si vous êtes négligent.

Choisissez un objectif. Donnez-lui un chiffre. Si votre SLO est 99,9 % de disponibilité mensuelle, votre budget est d’environ 43 minutes. Si vous dépensez déjà ce budget sur les déploiements, « migration sans temps d’arrêt » devient moins un argument et plus une stratégie de survie.

Étape 1 : Rendre le côté SQLite aussi sain que possible

Avant la migration, stabilisez. Une migration amplifie le bazar existant. Si votre base SQLite corrompt déjà de temps en temps, ou si vous avez une demi-douzaine de patterns de connexion ad hoc, vous copierez ce bazar dans PostgreSQL et appellerez ensuite cela « instabilité Postgres ». La physique ne fonctionne pas comme ça.

Au minimum :

Activez le mode WAL si ce n’est pas déjà fait (sauf si votre environnement le rend dangereux).
Standardisez le comportement d’ouverture/fermeture des connexions et le busy timeout.
Identifiez tous les chemins d’écriture. Il y en a toujours plus que vous ne le pensez.

Étape 2 : Traduire le schéma et les types de manière délibérée

SQLite est typé dynamiquement. PostgreSQL ne l’est pas. Si vous avez stocké des timestamps comme « ce qui marchait », PostgreSQL vous obligera à choisir : timestamptz ou timestamp, types numériques, text vs jsonb, contraintes que vous passiez auparavant sous silence.

Choisissez la rigueur maintenant. Les migrations sont le moment où vous payez la dette de types. Sinon vous la payerez plus tard avec des intérêts incidents.

Étape 3 : Construire un backfill (copie en masse) réexécutable

La première grosse copie est un backfill : extraire depuis SQLite et charger dans PostgreSQL. Traitez-le comme n’importe quel autre job en production :

Idempotent : sûr à relancer plusieurs fois.
Découpé en morceaux : ne fait pas exploser la mémoire ou les logs de transactions.
Observable : logs des compteurs, durées et erreurs.

La possibilité de relancer compte parce que vous découvrirez des écarts. Vous découvrez toujours des écarts.

Étape 4 : Garder PostgreSQL synchronisé : écritures doubles ou capture de changements

Vous avez deux schémas pratiques :

Schéma A : Écritures doubles (l’application écrit vers les deux)

Avantages : concept simple, pas d’outillage sophistiqué. Inconvénients : gestion des pannes délicate ; vous pouvez créer un split-brain au niveau applicatif si vous ne concevez pas soigneusement.

Règles pour des écritures doubles qui vous évitent les ennuis :

Choisissez une source de vérité pendant la phase d’écriture double. Habituellement SQLite reste autoritatif jusqu’à la bascule.
Rendez les écritures idempotentes. Utilisez des clés primaires stables et retry en sécurité.
Logguez et réconciliez les échecs. Le mode « fire and forget » pour les écritures doubles est la recette de la perte silencieuse de données.

Schéma B : Capture de données de changement (CDC) depuis SQLite

SQLite n’a pas de réplication logique intégrée comme PostgreSQL. Mais vous pouvez approximer la CDC en :

Ajoutant des triggers qui écrivent dans une table d’historique append-only.
Utilisant un lecteur de write-ahead log (plus dur, plus fragile et dépendant de l’environnement).
Écrivant les changements dans une table outbox dans SQLite qu’un worker expédie vers PostgreSQL.

Triggers + outbox sont souvent la solution la moins mauvaise. Ce n’est pas glamour. Ça marche.

Étape 5 : Lecture en parallèle et contrôles de parité

Avant de basculer les lectures, shadowez : lisez depuis SQLite comme d’habitude, lisez aussi depuis PostgreSQL en arrière-plan, comparez les résultats et émettez des métriques. Cela détecte les problèmes de type, différences de collation et différences sémantiques des requêtes.

Ne comparez pas des payloads entiers aveuglément. Comparez ce qui compte : clés primaires retournées, compteurs, agrégats, champs spécifiques avec ordre déterministe. PostgreSQL et SQLite peuvent diverger sur l’ordre sauf si vous le spécifiez.

Étape 6 : Basculez d’abord les lectures, puis les écritures (habituellement)

Séquence courante à faible risque :

Backfill vers PostgreSQL.
Écritures doubles avec SQLite comme source de vérité.
Lecture en parallèle et comparaison.
Basculez les lectures vers PostgreSQL (conservez les écritures doubles).
Basculez les écritures vers PostgreSQL (SQLite devient secours / audit temporairement).
Supprimez les écritures doubles après confiance et délai de stabilisation.

La bascule des écritures est le moment potentiellement douloureux. La bascule des lectures est celle où vous apprenez. Étalez-la.

Étape 7 : Gardez un plan de rollback réaliste

Les plans de rollback échouent parce qu’ils supposent une symétrie parfaite. Après avoir basculé les écritures vers PostgreSQL, revenir à SQLite signifie devoir rejouer les écritures. Si vous n’avez pas construit ce pipeline, le rollback est un diaporama, pas une capacité.

Un plan réaliste : après la bascule des écritures, conservez l’écriture double ou l’outbox suffisamment longtemps pour que le rollback reste faisable. Définissez un horizon temporel (heures/jours). Mesurez la difficulté de rejouer. Entraînez-vous une fois en staging avec un jeu de données réaliste.

Une citation (idée paraphrasée) : Werner Vogels (CTO d’Amazon) a souvent poussé l’idée que « tout échoue, tout le temps », donc vous concevez les systèmes en supposant l’échec, pas en espérant qu’il n’arrivera pas.

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

Ce sont des tâches réelles que vous pouvez exécuter pendant la planification, l’exécution ou le dépannage de la migration. Chacune inclut (1) une commande, (2) une sortie d’exemple, et (3) la décision issue de celle-ci.

Task 1: Identify SQLite journal mode and busy timeout behavior

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

Ce que cela signifie : Le mode WAL est activé ; synchronous=2 (FULL) ; busy_timeout=5000ms.

Décision : WAL est bon pour la concurrence ; FULL est plus sûr mais plus lent. Si vous observez de la contention sur les verrous, augmentez busy_timeout et réduisez la fréquence des écritures. Ne passez pas à synchronous=OFF comme « correction performance » sauf si vous aimez expliquer la perte de données.

Task 2: Detect SQLite lock contention quickly

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "PRAGMA compile_options;" | grep -i threads
THREADSAFE=1

Ce que cela signifie : SQLite a été compilé threadsafe, donc vos problèmes de verrou n’ont pas pour cause « sqlite est mono-thread ». Ils sont liés aux charges et aux patterns d’accès.

Décision : Concentrez-vous sur l’étendue des transactions et le nombre d’écrivains, pas sur le folklore. Instrumentez les endroits où les écritures ont lieu.

Task 3: Get table sizes in SQLite (to estimate backfill time)

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "SELECT name, SUM(pgsize) AS bytes FROM dbstat GROUP BY name ORDER BY bytes DESC LIMIT 5;"
events|2147483648
users|402653184
sessions|268435456
sqlite_schema|4096

Ce que cela signifie : events fait ~2 Gio ; la stratégie de backfill nécessite du chunking et peut-être du partitionnement dans PostgreSQL.

Décision : Si vous backfill en une seule transaction, vous exploserez le WAL et peut-être le disque. Planifiez une copie en morceaux par clé primaire ou par fenêtres temporelles.

Task 4: Verify SQLite integrity before copying

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

Ce que cela signifie : Le contrôle de corruption de base passe.

Décision : Si ce n’est pas « ok », arrêtez. Corrigez la corruption d’abord (restaurez depuis une sauvegarde, reconstruisez depuis les événements sources). Migrer la corruption ne fait que déplacer la faute.

Task 5: Dump SQLite schema (don’t trust what you remember)

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db ".schema users"
CREATE TABLE users(
  id TEXT PRIMARY KEY,
  email TEXT,
  created_at TEXT
);
CREATE INDEX idx_users_email ON users(email);

Ce que cela signifie : Les timestamps sont stockés en TEXT. Cela sera un sujet plus tard.

Décision : Décidez comment mapper created_at vers timestamptz et comment parser les valeurs legacy. Construisez une fonction de conversion et des tests.

Task 6: Create PostgreSQL roles and a dedicated database

cr0x@server:~$ sudo -u postgres psql -c "CREATE ROLE myapp LOGIN PASSWORD 'REDACTED' NOSUPERUSER NOCREATEDB NOCREATEROLE;"
CREATE ROLE

Ce que cela signifie : Vous avez maintenant un rôle applicatif au moindre privilège.

Décision : Ne faites pas tourner votre application en tant que postgres. Si vous le faites, vous finirez par exécuter quelque chose d’excitant et irréversible.

Task 7: Check PostgreSQL is actually durable (fsync on, full_page_writes on)

cr0x@server:~$ sudo -u postgres psql -tAc "SHOW fsync; SHOW synchronous_commit; SHOW full_page_writes;"
on
on
on

Ce que cela signifie : Vous n’êtes pas en mode « YOLO durability ».

Décision : Gardez ces options activées en production. Si la performance est mauvaise, corrigez le schéma et l’I/O, pas la correction.

Task 8: Estimate PostgreSQL capacity and bloat risk (baseline DB size)

cr0x@server:~$ sudo -u postgres psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;"
  datname  | pg_size_pretty
-----------+----------------
 postgres  | 8456 kB
 template1 | 8280 kB
 template0 | 8208 kB
(3 rows)

Ce que cela signifie : Le baseline est petit ; vous avez de la marge. Vous allez bientôt l’agrandir.

Décision : Confirmez l’espace disque pour : données + index + WAL + overhead + sauvegardes. Si vous ne pouvez pas l’énoncer, vous n’êtes pas prêt à couper.

Task 9: Backfill using a safe, chunked pipeline (example: CSV with COPY)

cr0x@server:~$ sqlite3 -header -csv /var/lib/myapp/app.db "SELECT id,email,created_at FROM users;" > /tmp/users.csv
cr0x@server:~$ sudo -u postgres psql myapp -c "\copy users(id,email,created_at) FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true);"
COPY 120543

Ce que cela signifie : 120 543 lignes chargées.

Décision : Si COPY est lent, vérifiez index et contraintes. Pendant le backfill, chargez dans une table de staging sans index lourds, puis créez les index après.

Task 10: Verify row counts match (coarse parity check)

cr0x@server:~$ sqlite3 /var/lib/myapp/app.db "SELECT COUNT(*) FROM users;"
120543
cr0x@server:~$ sudo -u postgres psql myapp -tAc "SELECT COUNT(*) FROM users;"
120543

Ce que cela signifie : Les comptes correspondent pour cette table.

Décision : Les comptes sont nécessaires mais pas suffisants. Si les comptes correspondent mais que les données diffèrent, vous perdrez quand même.

Task 11: Verify key coverage and missing IDs (spot silent loss)

cr0x@server:~$ sqlite3 -csv /var/lib/myapp/app.db "SELECT id FROM users ORDER BY id LIMIT 5;"
001a,00b9,00c1,00d0,00f2
cr0x@server:~$ sudo -u postgres psql myapp -tAc "SELECT id FROM users ORDER BY id LIMIT 5;"
001a
00b9
00c1
00d0
00f2

Ce que cela signifie : Les IDs faibles existent ; l’ordre semble cohérent pour cet échantillon.

Décision : Vérifiez aussi des échantillons aléatoires et les plages maximales ; les bugs se cachent dans la queue.

Task 12: Check PostgreSQL slow queries during shadow reads

cr0x@server:~$ sudo -u postgres psql myapp -c "SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;"
                query                 | calls | mean_exec_time
--------------------------------------+-------+---------------
 SELECT * FROM events WHERE user_id=$1 |  9321 |        187.42
 SELECT * FROM sessions WHERE id=$1    | 21144 |         12.11
(2 rows)

Ce que cela signifie : Votre requête la plus chaude est lente dans PostgreSQL ; probablement un index manquant ou une mauvaise forme de requête.

Décision : Ajoutez un index (events(user_id, created_at) peut-être), ou modifiez la requête pour sélectionner des colonnes précises. Ne basculez pas les lectures tant que ce n’est pas sous contrôle.

Task 13: Confirm index usage with EXPLAIN (avoid guessing)

cr0x@server:~$ sudo -u postgres psql myapp -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id='00f2' ORDER BY created_at DESC LIMIT 50;"
 Limit  (cost=0.42..12.55 rows=50 width=128) (actual time=0.311..0.829 rows=50 loops=1)
   Buffers: shared hit=210
   ->  Index Scan Backward using idx_events_user_created_at on events  (cost=0.42..812.11 rows=3400 width=128) (actual time=0.309..0.814 rows=50 loops=1)
         Index Cond: (user_id = '00f2'::text)
 Planning Time: 0.220 ms
 Execution Time: 0.901 ms

Ce que cela signifie : Scan d’index ; exécution sous la milliseconde. C’est ce qu’on veut.

Décision : Si vous voyez des scans séquentiels sur de grandes tables, ne discutez pas avec le planner — corrigez le schéma/index ou la requête.

Task 14: Watch connection saturation (a classic Postgres cutover faceplant)

cr0x@server:~$ sudo -u postgres psql -tAc "SHOW max_connections; SELECT count(*) FROM pg_stat_activity;"
200
187

Ce que cela signifie : Vous êtes déjà proche du max de connexions.

Décision : Ajoutez un pool de connexions (PgBouncer), réduisez les tailles de pool applicatif, ou scalez la DB. Si vous basculez maintenant, vous aurez des vagues de connexions et des pannes auto-infligées.

Task 15: Monitor WAL growth during backfill (don’t fill disk silently)

cr0x@server:~$ sudo -u postgres psql -tAc "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS wal_bytes_since_start;"
42 GB

Ce que cela signifie : Beaucoup de WAL généré (exemple). Pendant les chargements en masse, le WAL peut gonfler.

Décision : Assurez l’espace disque ; envisagez le batching et des checkpoints. Si vous répliquez, assurez-vous que les réplicas suivent sinon vous conserverez le WAL et remplirez le disque.

Task 16: Confirm replication lag (if using replicas for safety)

cr0x@server:~$ sudo -u postgres psql -c "SELECT application_name, state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"
 application_name |   state   | write_lag | flush_lag | replay_lag
------------------+-----------+-----------+-----------+------------
 standby1          | streaming | 00:00:01  | 00:00:02  | 00:00:03
(1 row)

Ce que cela signifie : La réplication est saine avec un faible lag.

Décision : Ne basculez pas les écritures si les réplicas ont des minutes de retard et que votre plan de bascule suppose qu’ils sont à jour.

Task 17: Validate constraints that SQLite didn’t enforce the way you assumed

cr0x@server:~$ sudo -u postgres psql myapp -c "SELECT COUNT(*) FROM users WHERE email IS NULL;"
14

Ce que cela signifie : Vous avez des emails nuls. Si vous aviez prévu email NOT NULL, votre migration échouera ou votre application aussi.

Décision : Décidez : corriger les données (backfill de valeurs par défaut), changer la contrainte, ou ajouter une contrainte progressive plus tard. Ne l’ignorez pas « temporairement » pour toujours.

Task 18: Cutover switch check: app points to Postgres and health checks pass

cr0x@server:~$ systemctl restart myapp
cr0x@server:~$ journalctl -u myapp -n 20 --no-pager
Dec 30 12:11:02 server myapp[19422]: db: connected to postgres host=pg1 dbname=myapp
Dec 30 12:11:02 server myapp[19422]: migrations: none pending
Dec 30 12:11:03 server myapp[19422]: http: listening on :8080

Ce que cela signifie : L’application utilise PostgreSQL et a démarré proprement.

Décision : Procédez d’abord par un canary. Si une seule instance utilise Postgres, vous pouvez revenir rapidement.

Une courte blague (2/2) : Rien n’est plus permanent qu’une « écriture double temporaire » que personne n’ose supprimer.

Playbook de diagnostic rapide : trouver le goulot d’étranglement en minutes

C’est la voie « le pager sonne, Slack est en feu ». Vous êtes en pleine migration ou après la bascule et quelque chose est lent ou bloqué. Vérifiez ceci dans l’ordre.

Premier : Est-ce de la contention de verrous ou une exhaustion de connexions ?

Symptômes SQLite : erreurs « database is locked », latence d’écriture élevée, threads bloqués sur commit.
Symptômes Postgres : timeouts de connexion, trop de clients, pool applicatif en attente.

cr0x@server:~$ sudo -u postgres psql -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"
 state  | count
--------+-------
 active | 42
 idle   | 131
(2 rows)

Décision : Si active est élevé et idle aussi, vous avez probablement des pools applicatifs surdimensionnés. Si active est saturé et les requêtes sont lentes, vous avez des problèmes de requêtes/index.

Deuxième : La base est-elle liée à l’I/O ?

cr0x@server:~$ iostat -x 1 3
Device            r/s     w/s   rkB/s   wkB/s  await  %util
nvme0n1         120.0   340.0  8200.0 42000.0   9.4  92.1

Décision : Si %util est proche de 100% et await est élevé, vous êtes I/O-bound. Arrêtez d’ajouter des index en plein vol. Réduisez le débit d’écriture, batch, ou scalez le stockage/instance.

Troisième : Les requêtes lentes sont-elles la cause (et sont-elles nouvelles) ?

cr0x@server:~$ sudo -u postgres psql myapp -c "SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;"
                  query                   | calls | total_exec_time
------------------------------------------+-------+----------------
 SELECT * FROM events WHERE user_id=$1     |  9233 |       1730042.0
 UPDATE users SET last_seen_at=$1 WHERE id=$2 | 60321 |        402112.3
(2 rows)

Décision : Si une requête domine le temps total, corrigez-la d’abord. Ajoutez l’index manquant, réduisez les colonnes retournées, ou mettez en cache. Ne « tunez Postgres » de façon générique.

Quatrième : L’autovacuum suit-il (problème d’usure Postgres) ?

cr0x@server:~$ sudo -u postgres psql myapp -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
---------+------------+-------------------------------
 events  |   18200421 | 2025-12-30 11:40:22.12345+00
(1 row)

Décision : Si les dead tuples sont énormes et que l’autovacuum est périmé, votre table à mises à jour va se dégrader. Ajustez autovacuum pour cette table, ou redessinez les mises à jour.

Cinquième : Le lag de réplication casse-t-il vos hypothèses de sécurité ?

cr0x@server:~$ sudo -u postgres psql -c "SELECT application_name, replay_lag FROM pg_stat_replication;"
 application_name | replay_lag
------------------+------------
 standby1          | 00:05:12
(1 row)

Décision : Si le lag est de plusieurs minutes, le basculement perdra des données par rapport au primaire sauf si vous utilisez la réplication synchrone (qui a ses propres compromis). Ajustez votre basculement et vos hypothèses de durabilité.

Trois mini-histoires d’entreprise (douleur, regret et une petite victoire)

Mini-histoire 1 : L’incident causé par une mauvaise hypothèse

Ils étaient un SaaS de taille moyenne avec un planificateur de jobs « simple ». Le planificateur stockait l’état des jobs dans SQLite parce qu’il avait commencé comme un utilitaire sur une VM unique. Avec le temps, le planificateur est devenu un service : plusieurs workers, autoscaling et un load balancer. Le fichier SQLite a été placé sur un système de fichiers réseau partagé parce que « tous les workers doivent le voir ».

La mauvaise hypothèse était : un système de fichiers partagé fait d’une base fichier une base partagée. Les jours calmes, ça marchait. Les jours chargés, ça s’est comporté comme un rond-point à une voie et mille voitures. Les écrivains s’accumulaient derrière les verrous. Les lecteurs retentaient. La latence montait. L’application commençait à timeouter, ce qui relançait des jobs, augmentant les écritures, augmentant les verrous. Une boucle de rétroaction qui ressemblait à une « croissance soudaine ».

Quand ils ont finalement creusé, la preuve était manifeste : des erreurs disant littéralement que la base était verrouillée. Mais comme le système était distribué, chaque nœud voyait une petite part de la douleur. Personne n’avait la vision d’ensemble avant d’agréger les logs. À ce stade, les dégâts visibles clients étaient faits.

La correction n’a pas été héroïque. Ils ont arrêté de prétendre que le système de fichiers partagé était une base de données. Ils ont déplacé l’état du planificateur vers PostgreSQL, mis PgBouncer devant, et utilisé un pattern single-writer pour quelques tables critiques. Le plus drôle : une fois la correction déployée, ils ont trouvé une série « d’optimisations » (boucles de sleep, backoff) qui existaient seulement pour composer avec les verrous SQLite. Ces hacks sont devenus ensuite des bugs de latence sur PostgreSQL parce qu’ils retardaient du travail légitime.

Mini-histoire 2 : L’optimisation qui s’est retournée

Une entreprise de retail avait un cache SQLite local sur chaque hôte applicatif. Ils souhaitaient migrer le store autoritatif vers PostgreSQL, mais ne voulaient pas « perdre du temps » à construire des écritures doubles correctes. Ils ont donc fait comme beaucoup sous pression : ils ont rendu les écritures SQLite asynchrones.

Concrètement, ils ont mis les écritures en mémoire et les ont flushées en batch toutes les quelques secondes. Ça fonctionnait en bench. En production, bien sûr, c’était autre chose. Sous charge, les batches grossissaient. La mémoire augmentait. Puis un déploiement a redémarré le service et la queue en mémoire a disparu. Les utilisateurs ont vu des paniers obsolètes et des mises à jour manquantes. Le titre du postmortem était en gros : « Nous avons inventé la perte de données pour économiser 15 % CPU. »

Quand ils ont tenté de corriger, ils ont ajouté une queue sur disque. Mieux, mais encore subtil : la queue était sur le même disque que SQLite, et les pics de flush ont causé à nouveau de la contention de verrou. Ils avaient réduit les appels système mais augmenté la latence pire-cas. C’était une perte nette : le système est devenu plus difficile à raisonner et n’a toujours pas monté en charge.

Finalement, ils ont fait la chose ennuyeuse mais correcte : une table outbox dans SQLite avec des triggers, un shipper fiable vers PostgreSQL, et un tableau de bord de parité. Ce n’était pas « rapide », mais c’était déterministe. Leur taux d’incidents a chuté principalement parce qu’ils ont arrêté de se surprendre eux-mêmes.

Mini-histoire 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

Une entreprise B2B a planifié une bascule sans temps d’arrêt de SQLite vers PostgreSQL pour un service de configuration. L’ensemble de données était petit, mais le blast radius énorme : chaque requête de la plateforme lisait la config. Ils ont traité la migration comme un exercice de fiabilité, pas seulement comme un exercice de données.

Ils ont construit le shadow read tôt. Chaque lecture de config provenait toujours de SQLite, mais une goroutine en arrière-plan récupérait la même clé depuis PostgreSQL et comparait un hash. Les mismatches étaient comptés et taggés par préfixe de clé. Ça semblait paranoïaque. Ça l’était. Et c’était correct.

Deux jours avant la bascule, ils ont vu un pic de mismatches sur des clés contenant de l’Unicode. Les différences de collation et de normalisation entre SQLite et PostgreSQL leur mordaient : les comparaisons de chaînes et l’ordonnancement n’étaient pas identiques, et un des chemins de code dépendait d’un « premier match gagne » sans ordre explicite. Sans les lectures parallèles, ils auraient basculé et lentement corrompu les lectures de config d’une manière ressemblant à des « pannes aléatoires ».

Ils ont corrigé la requête pour être explicite, ajouté un ordre déterministe et écrit une assertion au moment de la migration qui rejetait les clés ambiguës. Le jour de la bascule a été ennuyeux. Personne n’a remarqué. C’est le compliment le plus élevé que vous puissiez faire au travail opérationnel.

Erreurs fréquentes : symptôme → cause racine → correction

Ce sont les patterns qui reviennent sans cesse dans les migrations SQLite→Postgres. Si vous vous reconnaissez ici, tant mieux. Cela veut dire que vous pouvez arrêter de le faire.

1) « database is locked » après l’ajout d’une simple fonctionnalité

Symptôme : Latence en pics, échecs d’écriture intermittents, beaucoup de retries.
Cause racine : Plusieurs écrivains ont augmenté le chevauchement des transactions ; transactions longues ou scope transactionnel non borné.
Correction : Raccourcir les transactions ; activer WAL ; ajouter busy_timeout ; consolider les écritures ; ou accepter la réalité et déplacer les écritures vers PostgreSQL.

2) Postgres est « lent » juste après la bascule

Symptôme : Saut de latence P95 ; CPU correct ; I/O modéré ; requêtes plus lentes qu’attendu.
Cause racine : Index manquants dus aux lacunes de traduction de schéma ; requêtes qui exploitaient des bizarreries SQLite (casts implicites, typage lâche).
Correction : Utiliser pg_stat_statements et EXPLAIN (ANALYZE) ; ajouter les index adéquats ; appliquer des types ; réécrire les requêtes pour être explicites.

3) Les contraintes d’unicité échouent soudainement pendant le backfill

Symptôme : COPY échoue avec des erreurs de clé dupliquée.
Cause racine : SQLite permettait des doublons parce que la contrainte n’existait pas, ou parce que les valeurs différaient seulement par collation/casse/espaces.
Correction : Auditer les doublons dans SQLite avant la migration ; décider des règles de canonicalisation ; implémenter dans l’application et dans les scripts de migration.

4) Les timestamps deviennent incohérents (décalés ou invalides)

Symptôme : Les données semblent décalées ; les requêtes par fenêtre temporelle manquent des enregistrements.
Cause racine : SQLite a stocké les timestamps en TEXT sans fuseau ; Postgres parse en timestamp ou timestamptz avec des hypothèses différentes.
Correction : Choisissez timestamptz sauf raison contraire ; parsez explicitement les chaînes legacy ; définissez volontairement les timezones de connexion.

5) La migration « a fonctionné » mais des données manquent subtilement

Symptôme : Les comptes correspondent ; des utilisateurs signalent des éléments manquants ; audits montrent des trous.
Cause racine : Le backfill a été effectué une fois, mais les nouvelles écritures pendant le backfill n’ont jamais été copiées ; écritures doubles n’ont pas géré les échecs ; pas de contrôles de parité.
Correction : Implémentez la capture de changements (outbox/trigger) et vérifiez le lag ; exécutez des contrôles de parité ; bloquez la bascule jusqu’à ce que la dérive soit nulle (ou expliquée).

6) Postgres manque de connexions lors de pics

Symptôme : « too many clients », timeouts, pannes en cascade.
Cause racine : Chaque instance applicative garde un grand pool ; plus des jobs en arrière-plan ; plus des scripts admin ; Postgres n’est pas un jouet thread-per-connection.
Correction : Utilisez PgBouncer ; dimensionnez correctement les pools ; réduisez les connexions idle ; mettez des timeouts de requête et des coupe-circuits.

7) Autovacuum ne suit pas après la bascule

Symptôme : La performance se dégrade sur plusieurs jours ; bloat de tables ; index qui grossissent ; VACUUM qui tourne éternellement.
Cause racine : Charge riche en mises à jour (courante pour des champs « status ») et paramètres autovacuum non adaptés table par table.
Correction : Ajustez les seuils autovacuum pour les tables chaudes ; évitez les mises à jour inutiles (mettre à jour seulement si changement) ; envisagez un modèle append-only pour les données événementielles.

Listes de contrôle / plan pas à pas (ennuyeux, donc efficace)

Phase A : Checklist d’ingénierie pré-migration

Inventoriez toutes les bases SQLite, fichiers et chemins d’écriture. Si vous ne pouvez pas les lister, vous ne pouvez pas les migrer.
Standardisez les paramètres SQLite : journal_mode, synchronous, busy_timeout.
Décidez de la version PostgreSQL cible et du modèle de déploiement (géré vs auto-hébergé).
Définissez les règles de mapping des types (timestamps TEXT, booléens, blobs JSON, précision numérique).
Définissez une politique de « source de vérité » pour la phase d’écritures doubles.
Construisez des contrôles de parité : comptes, sommes, checksums, et vérifications ciblées pour les entités à haute valeur.

Phase B : Construire la machinerie de migration

Créer le schéma PostgreSQL avec types et contraintes explicites stageés sensiblement (commencez permissif, renforcez ensuite).
Backfill dans des tables de staging, puis swap/rename une fois chargé et indexé.
Implémenter la capture de changements (écritures doubles ou outbox SQLite avec shipper).
Implémenter les lectures en parallèle pour un échantillon représentatif des requêtes.
Opérationnaliser : métriques pour lag, comptes de mismatch, échecs d’écriture et latence des requêtes.

Phase C : Plan de cutover exécutable sous stress

Canary : routez un petit pourcentage de lectures vers PostgreSQL ; conservez SQLite comme autorité.
Étendre la bascule des lectures progressivement ; surveillez les budgets d’erreur et les tableaux de requêtes lentes.
Bascule des écritures : switch du chemin d’écriture vers PostgreSQL tout en capturant encore les changements pour rollback (temporairement).
Geler et valider : courte fenêtre où vous assurez que la dérive est nulle et que les contraintes sont satisfaites.
Retirer l’écriture double uniquement après une période de chauffe et un drill réussi « rejouer vers SQLite » (si le rollback est requis par politique).

Phase D : Renforcement post-bascule

Activez des timeouts de requête pour le rôle applicatif.
Ajoutez la surveillance pour le lag de réplication, l’utilisation disque et la santé d’autovacuum.
Lancez une campagne de durcissement des contraintes : NOT NULL, CHECK, FK là où approprié.
Documentez les runbooks opérationnels : restauration, bascule, rebuild d’index et réduction d’urgence du trafic.

FAQ

1) SQLite peut-il gérer un trafic élevé si j’active WAL ?

WAL aide beaucoup, surtout pour des charges en lecture majoritaire avec quelques écritures. Il ne transforme pas SQLite en serveur multi-écrivains multi-hôtes. Si votre problème de montée en charge est « beaucoup d’écrivains sur plusieurs instances », WAL est un pansement, pas un remède.

2) Quelle est la stratégie la plus sûre pour une migration « sans temps d’arrêt » ?

Backfill → capture de changements (outbox ou écritures doubles) → lectures en parallèle → basculer les lectures → basculer les écritures. La version la plus sûre inclut des dashboards de parité et une fenêtre de rollback pendant laquelle vous pouvez rejouer les écritures en arrière si nécessaire.

3) Dois-je écrire en double depuis l’application ou utiliser des triggers ?

Si vous pouvez modifier l’app en toute sécurité et gérer retries/idempotence, l’écriture double dans l’app est simple. Si vous avez plusieurs applis écrivant, ou si vous voulez un mécanisme centralisé, des triggers SQLite dans une table outbox peuvent être plus propres. Dans tous les cas, vous avez besoin de réconciliation et d’observabilité.

4) Pourquoi ne pas simplement arrêter le monde et faire un dump/restore ?

Si vous le pouvez, faites-le. Le chemin « sans temps d’arrêt » a plus de pièces mobiles et plus de façons de créer une dérive subtile. La seule raison de le choisir est que vous devez continuer à servir pendant la migration.

5) Comment gérer le typage lâche de SQLite lors du passage à PostgreSQL ?

Décidez explicitement des types et écrivez du code de conversion. Attendez-vous à des pourritures : chaînes numériques, timestamps vides, formats mixtes. Construisez un chemin de quarantaine pour les lignes mauvaises plutôt que d’échouer toute la migration.

6) Qu’en est-il des fonctionnalités SQLite qui ne se mappent pas bien sur Postgres ?

Les points durs concernent généralement l’affinité de type, les conversions implicites, les clauses de conflit et les fonctions date/heure. Auditez les requêtes, ajoutez des casts explicites dans Postgres et ne comptez pas sur un ordre non spécifié.

7) Ai-je besoin d’un pool de connexions pour PostgreSQL ?

Dans la plupart des environnements de production : oui. Particulièrement si vous avez de nombreuses instances applicatives. PgBouncer est courant parce que le coût des connexions Postgres est réel et « augmenter max_connections » finit mal.

8) Comment prouver la parité des données au-delà des comptes de lignes ?

Utilisez plusieurs contrôles : comptes par table, comptes par partition (par jour/client), checksums/hashes de champs clés et vérifications aléatoires. Faites aussi des lectures parallèles sur de vraies requêtes applicatives et comparez les sorties.

9) Puis-je garder SQLite comme cache local après la migration vers Postgres ?

Oui, mais traitez-le comme un cache : reconstruisible, expirables et jamais autoritatif sauf si vous concevez explicitement le mode offline. Sinon vous réintroduisez un split-brain sous un joli nom.

10) Quel est le plus grand risque de migration que les gens sous-estiment ?

Ce n’est pas le backfill. C’est la synchronisation continue. La dérive est le tueur silencieux : ça a l’air correct jusqu’au premier audit, exécution de facturation ou incident de sécurité. Construisez la détection de dérive dès le premier jour.

Conclusion : prochaines étapes réalisables cette semaine

Si vous êtes sur SQLite et que vous souffrez, ne migrez pas dans la panique. Stabilisez d’abord, puis migrez comme un opérateur : mesurable, réversible et ennuyeux.

Inventoriez les écritures : trouvez tous les chemins de code qui mutent SQLite, y compris les jobs de maintenance en arrière-plan.
Choisissez votre stratégie de synchronisation : écritures doubles avec idempotence, ou outbox avec triggers et shipper.
Construisez backfill + contrôles de parité réexécutables sans drame.
Mettez en place PostgreSQL avec des garde-fous : pooling, monitoring, paramètres durables et marge de capacité.
Faites des lectures parallèles avant la bascule. Si vous sautez cette étape, vous jouez aux dés avec des inconnues.
Basculez d’abord les lectures, puis les écritures, et gardez le rollback faisable pendant une fenêtre définie.

La victoire n’est pas seulement « nous sommes sur Postgres maintenant ». La victoire, c’est que vous arrêtez de traiter votre base comme un fichier auquel vous espérez qu’il se comporte, et commencez à la traiter comme un système que vous pouvez observer, raisonner et restaurer sous pression.