Pourquoi nous avons choisi SQLite

Mise à jour : 1er octobre 2026

Cet article présente notre exploration initiale de Gleam pour Curling IO v3. Nous avons finalement choisi Rust et TypeScript. Pourquoi nous avons choisi Rust pour la version 3 explique cette décision et ce que nous avons conservé de cette exploration.

À propos de cet article

Cette note d’architecture technique porte sur le choix de base de données pour Curling IO v3. Elle s’adresse aux ingénieurs logiciels et aux responsables de l’exploitation, et aborde SQLite, Litestream, l’isolation des bases de données, l’hébergement et les compromis de récupération plus en détail que nos articles habituels sur le produit.

La version 3 devrait coûter moins cher à exploiter, être plus facile à restaurer et rester rapide aux pointes d’inscription et de pointage en direct. Ces objectifs nous ont menés à un choix inattendu : SQLite.

Au départ, nous pensions utiliser PostgreSQL. Après dix ans à exploiter Postgres en production, nous connaissions les outils, les modes de défaillance et les procédures d’exploitation. Puis nous avons comparé Postgres autohébergé à SQLite avec Litestream, et changé d’avis.

Les technologies de la version 2

Curling IO version 2 utilise Rails avec PostgreSQL sur Crunchy Bridge, un service Postgres géré qui nous a bien servis. Crunchy Data s’occupe des sauvegardes, du basculement et de l’optimisation. Nous n’avons pas à penser aux paramètres shared_buffers ou autovacuum, à exploiter pgBackRest ou à planifier les sauvegardes de base. Crunchy Data a été acquis par Snowflake en 2025, ce qui nous donne une raison supplémentaire de réduire notre dépendance aux services gérés par des tiers.

Le compromis concerne le coût, la dépendance au fournisseur et la juridiction. Crunchy Bridge fonctionne seulement sur AWS. Nos serveurs d’application doivent donc aussi être sur AWS. Toute notre infrastructure dépend ainsi d’un seul fournisseur infonuagique américain. Nous sommes une entreprise entièrement canadienne et préférons garder notre infrastructure plus près de chez nous, surtout dans le contexte d’incertitude politique au sud de la frontière.

Pour la version 3, nous passons à OVH, un fournisseur détenu par des intérêts français qui possède des centres de données au Québec et en Ontario. Toutes les données restent au Canada. Crunchy Bridge ne fonctionne pas sur OVH, nous ne pouvons donc pas le conserver. Autohéberger Postgres sur OVH nous obligerait à prendre en charge tout ce que Crunchy gère actuellement : les pools de connexions, le réglage du serveur (shared_buffers, effective_cache_size, work_mem, max_connections), la surveillance d’autovacuum et l’ensemble des sauvegardes, soit pgBackRest, l’archivage WAL, les sauvegardes de base planifiées, les procédures de restauration testées et la surveillance de la fraîcheur des sauvegardes. C’est beaucoup pour une petite organisation.

L’architecture : une base de données par sport

La version 3 utilise une architecture avec une base de données par sport. Chaque sport possède son propre fichier SQLite avec un schéma identique :

db/
├── curling.db          # All curling club data
├── pickleball.db       # All pickleball club data
└── shared.db           # Cross-sport reference data, jobs, email suppressions

Chaque base de sport héberge plusieurs organisations. Toutes les tables contenant leurs données possèdent une colonne org_id, et chaque requête filtre sur celle-ci. Ajouter un sport revient à créer un fichier de base de données avec le même schéma. Aucune requête entre les sports, aucune contention sur une base partagée.

shared.db contient les éléments communs aux sports : les juridictions fiscales canadiennes (17 lignes couvrant les taux de TPS, TVH, TVP et TVQ par province), la file de tâches en arrière-plan et les listes de suppression de courriels provenant des webhooks Postmark.

Au démarrage, le serveur ouvre une connexion par base de données et la conserve pendant toute la durée de l’application :

Configuration des connexions

pub fn open(path: String) -> Result(sqlight.Connection, sqlight.Error) {
  use conn <- result.try(sqlight.open(path))
  use _ <- result.try(sqlight.exec("PRAGMA journal_mode=WAL;", conn))
  use _ <- result.try(sqlight.exec("PRAGMA busy_timeout=5000;", conn))
  use _ <- result.try(sqlight.exec("PRAGMA foreign_keys=ON;", conn))
  Ok(conn)
}

Chaque connexion reçoit trois PRAGMA : le mode WAL pour les lectures simultanées, un délai d’attente de 5 secondes en cas d’occupation pour que les écritures attendent plutôt que d’échouer immédiatement, et l’application des clés étrangères, désactivée par défaut dans SQLite.

Une connexion par base de données. Aucun pool de connexions ni configuration de pool. Chaque requête HTTP entrante reçoit la connexion du sport, déterminé par le nom d’hôte, et la connexion partagée.

Nous avons aussi créé Marmot pour Gleam pour générer des appels typés à partir du SQL. Nous l’avons ensuite porté vers Marmot pour Rust, où son développement se poursuit. La version Rust analyse le SQL par rapport au schéma SQLite et génère des fonctions typées qui utilisent rusqlite. Les requêtes restent à côté des parcours auxquels elles appartiennent. Nous gardons ainsi le SQL explicite sans écrire manuellement la liaison des paramètres et le décodage des lignes.

Pourquoi SQLite dans le processus de l’application

SQLite fonctionne dans le processus de l’application. Aucun socket, aucun aller-retour TCP, aucune sérialisation entre l’application et la base de données. Une requête est un appel de fonction.

Notre charge de travail consiste surtout en recherches indexées simples : une ligne par clé primaire, des listes filtrées par org_id et quelques articles de commande joints à une commande. Nos bancs d’essai montrent un débit important pour ce type de charge. Plus de dix ans de trafic de la version 2 nous donnent aussi une base pour estimer la demande. Même aux pointes d’inscription ou lorsqu’un championnat provincial affiche les pointages en direct, le profil reste très majoritairement constitué de lectures, avec des poussées occasionnelles d’écritures.

Le coût découle de l’architecture. Sans serveur de base de données distinct, aucune facture d’hébergement de base de données. L’ensemble de la version 3 fonctionne sur un seul serveur pour moins cher que Postgres géré seul sur AWS.

Mode WAL et concurrence

Le journal d’écriture anticipée, ou WAL (Write-Ahead Logging), rend SQLite viable pour une application web concurrente. Sans lui, toute écriture verrouille la base entière pour les lecteurs et les rédacteurs. Avec WAL, les lectures se poursuivent pendant les écritures. C’est essentiel sur BEAM, où des centaines de processus légers peuvent interroger la base simultanément pendant une pointe de trafic.

Les écritures restent sérialisées. Un seul rédacteur à la fois, coordonné par un mutex dans la bibliothèque SQLite. Si le verrou d’écriture n’est pas obtenu dans le délai de 5 secondes, SQLite retourne SQLITE_BUSY. Avec notre charge surtout composée de lectures, la contention d’écriture n’a pas été un problème concret. C’est pourquoi nous utilisons une base distincte pour les tâches en arrière-plan. Leur traitement écrit dans shared.db, et non dans les bases des sports. Il n’entre donc jamais en concurrence avec les requêtes d’inscription. Avec le modèle à rédacteur unique de SQLite, séparer les charges riches en écritures dans différents fichiers est une vraie décision d’architecture, pas seulement une façon de ranger les données.

Litestream : le facteur décisif

C’est ce qui a fait pencher la balance.

SQLite est un fichier. Litestream surveille ce fichier et réplique continuellement ses trames WAL vers un stockage d’objets compatible S3. Nous utilisons OVH Object Storage dans deux lieux géographiques distincts au Canada. Les changements se synchronisent toutes les 10 secondes vers les deux.

La configuration tient dans un seul fichier YAML :

Détails de restauration avec Litestream

dbs:
  - path: /var/lib/curling-io/databases/curling.db
    replicas:
      - type: s3
        bucket: xxxxx
        endpoint: xxxxx
        sync-interval: 10s
      - type: s3
        bucket: xxxxx
        endpoint: xxxxx
        sync-interval: 10s

La récupération tient en une commande :

litestream restore -o /path/to/curling.db s3://xxxxx/curling.db

Elle produit un fichier de base de données prêt à l’emploi. Copiez-le au bon endroit, puis démarrez l’application.

Comparez maintenant avec les sauvegardes et la récupération de PostgreSQL autohébergé :

1. Installer pgBackRest.
2. Configurer une stanza et définir archive_mode et archive_command dans postgresql.conf.
3. Planifier les sauvegardes de base avec cron.
4. Surveiller leur réussite, la conservation du WAL et l’espace disque.
5. Tester régulièrement les restaurations, car une sauvegarde non testée n’est pas une sauvegarde.
6. En cas de sinistre : arrêter PostgreSQL, exécuter pgbackrest restore --type=time --target="...", attendre la relecture du WAL, vérifier le point de récupération, puis promouvoir le serveur au rôle principal.

PostgreSQL offre ce que Litestream n’offre pas : une récupération à un instant précis, au niveau exact de la transaction. Vous pouvez revenir à n’importe quelle transaction. Litestream fournit « le dernier instantané plus les trames WAL », soit une perte possible d’environ 10 secondes dans le pire cas. C’est amplement suffisant pour notre usage.

L’écart d’exploitation est décisif. Litestream, c’est un exécutable, un fichier de configuration et presque aucun entretien continu. Les sauvegardes PostgreSQL sont une pratique qu’il faut répéter, surveiller et maintenir pendant toute la vie du projet. Même avec des outils parfaits, on ne peut pas réduire « restaurer l’état d’un serveur en fonctionnement » à « télécharger un fichier et démarrer l’application ». Cet écart est architectural. De meilleurs outils Postgres ne le combleront pas, car la complexité est inhérente à la sauvegarde d’un processus serveur en fonctionnement.

Le degré de simplicité atteignable pour les sauvegardes SQLite dépasse fondamentalement celui de toute base de données client-serveur.

Le prix à payer pour SQLite

Voici ce que nous payons pour cette simplicité. PostgreSQL gère nativement chacun des éléments suivants.

Pas de vrais types

SQLite n’a pas de booléens, de dates ni d’énumérations. Les booléens sont stockés comme INTEGER 0/1, les dates comme entiers de l’époque Unix et les énumérations comme chaînes TEXT.

Chaque paramètre booléen exige un appel db.bool_to_int(value). Chaque décodeur booléen exige une vérification != 0. Le formatage des dates, la conversion des fuseaux horaires et les comparaisons se font entièrement dans le code de l’application. Il n’y a ni DATE_TRUNC, ni INTERVAL, ni AT TIME ZONE.

Toutes nos tables sont des tables STRICT, ce qui exige que les valeurs correspondent aux types déclarés des colonnes : INTEGER, TEXT, REAL et BLOB. C’est un filet de sécurité gratuit pour ce qui contourne la couche applicative, mais il ne résout pas les booléens, dates ou énumérations. L’application doit s’en charger. Cela reste gérable, puisque Gleam et Parrot sont les seules sources d’écritures et y imposent les types.

ALTER TABLE limité

SQLite stocke son schéma sous forme du texte CREATE TABLE d’origine plutôt que dans des catalogues système structurés comme PostgreSQL. Les possibilités d’ALTER TABLE sont donc limitées :

• ADD COLUMN, RENAME COLUMN et DROP COLUMN fonctionnent.
• Changer le type, la valeur par défaut ou la contrainte NOT NULL d’une colonne ne fonctionne pas.
• Ajouter ou modifier des contraintes CHECK ou FOREIGN KEY ne fonctionne pas.
• Ajouter UNIQUE à une colonne existante ne fonctionne pas.

Modifier une colonne après le lancement demande quatre étapes : ajouter la nouvelle colonne, copier les données, supprimer l’ancienne et renommer la nouvelle. PostgreSQL le fait avec un seul ALTER COLUMN.

Aucune contrainte CHECK

Nous n’utilisons aucune contrainte CHECK. C’est une décision de politique dont nous ne sommes pas enchantés.

Les contraintes CHECK sont intégrées à la chaîne DDL CREATE TABLE. En modifier une exige de reconstruire toute la table. Ajouter une valeur à une énumération ne devrait pas l’exiger. Toute la validation se fait donc dans Gleam : types union pour les énumérations avec vérification exhaustive des motifs à la compilation, fonctions de validation pour la logique métier et let assert dans les décodeurs pour échouer rapidement si de mauvaises données entrent.

Sérialisation des écritures

Comme indiqué plus haut, les écritures sont sérialisées. Les transactions courtes et indexées gardent brièvement le verrou d’écriture, et aucun aller-retour réseau n’est nécessaire vers la base de données. Cela nous laisse une marge importante dans la charge mesurée par les bancs d’essai. Cela ne garantit pas que les rédacteurs n’attendent jamais : la durée des transactions et les écritures simultanées comptent toujours. Cette architecture exclut toutefois une mise à l’échelle horizontale où plusieurs serveurs d’application écrivent dans la même base de données.

Ce que nous avons mesuré

Nous avons publié un banc d’essai de profils de requêtes comparant Gleam, Rust et Ruby. Il utilise des données synthétiques et des profils de requêtes tirés de la production : environ 26 petites requêtes indexées pour une requête de lecture d’administration, et une courte transaction pour une mise à jour. Le parcours Rust Marmot a mesuré environ 12 900 séquences de lecture et 45 800 séquences de mise à jour par seconde sur un serveur Ryzen 7 9700X avec 62 Gio de mémoire vive, en mode WAL avec synchronous=NORMAL.

Ce sont des mesures de parcours en base de données, pas de requêtes HTTP complètes ni d’un test de charge concurrent de toute l’application. Le jeu de données utilise de petites tables de taille fixe. Dix mille requêtes ne signifient pas dix mille clubs. Ces résultats appuient le choix de requêtes courtes et indexées et de SQLite dans le processus, mais ne prouvent pas la capacité pour un nombre précis d’organisations.

Nous disposons aussi de plus de dix ans d’utilisation de la version 2. Notre heure observée la plus occupée comptait environ 240 000 requêtes, soit une moyenne d’environ 67 par seconde pendant cette heure. Les pointes plus courtes peuvent être plus élevées, et le coût des requêtes varie. Combiner cet historique au banc d’essai donne une base pour estimer la capacité. Ces estimations restent des extrapolations tant que des tests concurrents représentatifs de l’application n’établissent pas le débit et la latence à l’échelle proposée.

Un seul nœud

SQLite est un fichier sur un disque local. Plusieurs serveurs d’application ne peuvent pas le partager : NFS brise le verrouillage des fichiers SQLite et entraîne de la corruption. Nous sommes donc limités à une architecture à serveur unique. Les bancs d’essai et plus de dix ans d’utilisation de la version 2 nous donnent confiance dans cette approche. La capacité dépend des inscriptions simultanées, du trafic des pointages et des écritures en arrière-plan plutôt que du seul nombre de clubs.

Certains projets ajoutent une réplication distribuée à SQLite, comme LiteFS, rqlite et dqlite. Mais ils ajoutent une complexité importante : systèmes de fichiers FUSE, protocoles de consensus et infrastructure supplémentaire. À ce stade, vous forcez SQLite à se comporter comme Postgres et devriez probablement utiliser Postgres.

Le risque des NIF

Notre pilote SQLite, esqlite, est une NIF Erlang : du code C compilé qui fonctionne hors du contrôle de l’ordonnanceur BEAM. Une longue requête bloque un fil de l’ordonnanceur. Une erreur de segmentation arrête toute la machine virtuelle.

En pratique, la bibliothèque C de SQLite est extrêmement fiable, et nos requêtes se terminent en quelques microsecondes. Nous réduisons le risque en gardant les requêtes simples : recherches indexées, aucun parcours complet des tables et déplacement du travail lourd vers les tâches en arrière-plan. Mais le risque existe.

Les écarts du dialecte SQL

Quelques petits irritants :

• DELETE FROM t WHERE ... LIMIT 1 ne fonctionne pas. Il faut une sous-requête : DELETE FROM t WHERE id = (SELECT id FROM t WHERE ... LIMIT 1).
• Aucun LISTEN/NOTIFY pour pousser des changements en temps réel depuis la base de données. Les notifications passent par un mécanisme de publication et d’abonnement dans l’application.
• Les comparaisons NULL exigent IS ? plutôt que = ? lorsque le paramètre peut être NULL.

Pourquoi nous l’acceptons

Chaque coût décrit plus haut se règle dans l’application : conversions de types, formatage de dates, étapes d’évolution du schéma, validation dans Gleam plutôt que par contraintes CHECK. Aucun ne change l’architecture. Ce sont des irritants, pas des obstacles.

L’utilisation observée de la version 2 et les bancs d’essai de profils de requêtes appuient le choix d’un seul serveur pour cette charge surtout composée de lectures. La contention d’écriture, la croissance des données et la latence sous charge concurrente restent les points à surveiller.

Quand passerions-nous à PostgreSQL?

Nous ne pensons pas en avoir besoin bientôt. Augmenter la capacité du serveur est une première étape pratique tant que la charge tient sur un seul serveur. Une contention d’écriture soutenue, une latence inacceptable ou le besoin de plusieurs serveurs d’application justifieraient une réévaluation.

La conversion SQL serait assez courante : changer la syntaxe des paramètres, adapter les colonnes d’identité et les booléens, revoir les instructions propres à SQLite et remplacer le pilote de base de données et les liaisons générées. Nos fichiers distincts pourraient correspondre à des schémas PostgreSQL, mais il faudrait vérifier l’isolation et les règles d’accès. Nous pensons conserver une grande partie de la logique métier.

Le reste exige son propre travail :

• Définir les limites et délais du pool de connexions, puis revoir les profils de requêtes qui nécessiteraient désormais un aller-retour réseau par instruction.
• Vérifier l’isolation des transactions, les verrous et les parcours de lecture suivie d’une écriture avec des rédacteurs concurrents. Ajouter des nouvelles tentatives en nombre limité lorsque les conflits l’exigent.
• Si nous ajoutons des nœuds d’application, coordonner les prises en charge durables des tâches, empêcher les effets externes en double et transmettre les notifications de l’application entre les processus.
• Configurer les sauvegardes, la réplication si nécessaire, la surveillance et une récupération testée. Répéter le transfert des données et le basculement, vérifier les données importées et définir le retour arrière avant de rediriger le trafic de production.

Cette migration est gérable, mais va au-delà d’un changement de dialecte. Il faudrait tester les parcours et les procédures d’exploitation touchés avant de la déclarer prête.

La suite

Si SQLite ne répond plus à nos besoins, PostgreSQL est une prochaine étape crédible. Garder le SQL à côté des parcours auxquels il appartient facilite la délimitation de la conversion. Les changements de concurrence et d’exploitation doivent toutefois être vérifiés.

Jusqu’ici, Litestream et SQLite dans le processus ont été l’une des meilleures décisions de la réécriture de la version 3. Un autre avantage mérite son propre article : puisque SQLite fonctionne dans le processus, chaque test obtient sa base de données en mémoire. Aucun état partagé, aucun nettoyage, aucun test instable. C’est le sujet du prochain article.

---

Cet article est le cinquième de la série sur les fondations de Curling IO. À suivre : l’isolation des tests offerte par SQLite.