PRAGMA SQLite : régler ses connexions en production

PRAGMA : votre canal de communication avec le moteur

Une instruction PRAGMA est propre à SQLite : elle permet de lire ou de modifier le comportement du moteur. On l'exécute comme n'importe quelle requête SQL, sauf qu'au lieu d'agir sur vos données, elle agit sur la configuration de la base.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

Exécuté comme une requête, un PRAGMA renvoie la valeur actuelle. Exécuté comme une affectation, il modifie cette valeur :

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

Le bon réflexe : la plupart des PRAGMA s'appliquent par connexion. Dès que vous ouvrez une nouvelle connexion, vous repartez avec les valeurs par défaut. C'est pour ça qu'en production, on retrouve presque toujours un petit bloc d'instructions PRAGMA exécuté juste après l'ouverture de chaque connexion.

La configuration de base pour SQLite en production

S'il ne fallait retenir que cinq PRAGMA pour configurer SQLite en production, ce serait ceux-là :

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT 'configuré' AS status;

C'est un réglage par défaut tout à fait sensé pour quasiment n'importe quelle application qui utilise SQLite comme stockage principal. Chaque PRAGMA mérite qu'on s'y attarde — la suite de cette page les passe en revue un par un.

journal_mode = WAL : activer le mode WAL de SQLite

Le mode de journalisation détermine comment SQLite garantit la durabilité des écritures. Par défaut, DELETE s'appuie sur un journal de rollback : les écrivains bloquent les lecteurs, et inversement. Ça passe encore pour un outil en ligne de commande, mais ça devient vite douloureux côté application web.

Le mode WAL (Write-Ahead Logging) renverse complètement la logique. Lecteurs et écrivains ne se bloquent plus entre eux — les lecteurs continuent de voir un instantané cohérent pendant qu'une écriture est en cours de commit. On reste limité à un seul écrivain à la fois, mais les lectures restent rapides même sous forte charge.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Quelques points à garder en tête :

• journal_mode est persistant : une fois défini, le réglage reste attaché au fichier de base. Inutile de le repositionner à chaque connexion, mais ça ne pose pas de problème non plus.
• Le mode WAL ajoute deux fichiers à côté de votre .db : un -wal et un -shm. Ne les supprimez surtout pas pendant que la base est ouverte.
• WAL se comporte mal sur les systèmes de fichiers réseau (NFS, SMB). Gardez la base sur un disque local.

Un autre document détaille le mode WAL et la gestion de la concurrence. Pour l'instant, contentez-vous de l'activer.

synchronous = NORMAL

Le PRAGMA synchronous règle l'agressivité avec laquelle SQLite écrit sur le disque. Le compromis se joue entre la durabilité et la vitesse.

• FULL (valeur par défaut) — écriture forcée après chaque commit. Durabilité maximale, mais plus lent.
• NORMAL — écriture forcée aux points de contrôle sûrs. Sans risque avec WAL, et nettement plus rapide.
• OFF — on laisse le système d'exploitation gérer. Très rapide, mais avec un risque de corruption en cas de coupure de courant.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

L'entier renvoyé (1) correspond à NORMAL. En mode WAL, NORMAL est le réglage recommandé : vous ne perdez pas les transactions déjà validées en cas de crash, seules les plus récentes peuvent être perdues lors d'une coupure de courant. Pour la plupart des applis, c'est le bon compromis.

Évitez OFF, sauf si vous remplissez une base jetable que vous pouvez recréer de zéro.

PRAGMA foreign_keys SQLite : activer les clés étrangères

Voilà un piège classique. SQLite gère les clés étrangères, mais leur application est désactivée par défaut, et c'est un paramètre propre à chaque connexion :

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Fantômes', 999);

Avec foreign_keys = ON, ce dernier INSERT échoue : aucun auteur avec l'id 999 n'existe. Sans ce PRAGMA, SQLite écrit tranquillement la ligne orpheline, et vous découvrez le bazar des mois plus tard.

Lancez PRAGMA foreign_keys = ON; en toute première instruction sur chaque nouvelle connexion. La plupart des ORM le font automatiquement ; si vous utilisez le driver brut, c'est à vous de jouer.

busy_timeout = 5000

SQLite n'autorise qu'un seul writer à la fois. Si une deuxième connexion tente d'écrire pendant qu'une première transaction est en cours, elle reçoit SQLITE_BUSY et abandonne aussitôt — c'est le comportement par défaut.

Le busy_timeout indique à SQLite d'attendre et de réessayer plutôt que de jeter l'éponge :

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

La valeur est exprimée en millisecondes. 5000 signifie « attendre jusqu'à 5 secondes que le verrou se libère avant d'abandonner ». Couplé au mode WAL, ce réglage élimine la majorité des erreurs database is locked qui surgissent dans les applications concurrentes.

Si vous vous retrouvez à pousser cette valeur au-delà de 30 secondes, le vrai correctif passe sans doute par des transactions plus courtes, pas par un timeout plus long.

cache_size

cache_size définit le nombre de pages de la base que SQLite garde en mémoire. Plus le cache est grand, moins on lit sur le disque, et plus les requêtes sont rapides sur les données chaudes.

La valeur peut s'écrire de deux manières :

• Nombre positif — en pages. Avec la taille de page par défaut de 4 Ko, 2000 correspond à 8 Mo.
• Nombre négatif — en kibioctets. -20000 vaut 20 Mo, peu importe la taille des pages.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

La forme négative est plus simple à raisonner — vous dites « donne-moi 20 Mo de cache » au lieu de faire des calculs avec la taille de page. Pour une petite appli, 20 à 50 Mo suffisent largement. Pour une charge à dominante lecture sur une base plus volumineuse, monte plus haut. Comme synchronous, cache_size s'applique par connexion.

mmap_size

Les E/S mappées en mémoire permettent à SQLite de lire des portions du fichier de base de données directement depuis le cache de pages de l'OS, en évitant une copie. Ça peut accélérer les lectures sur les grosses bases :

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

Cela fait 256 Mo. SQLite mappera en mémoire jusqu'à cette quantité de la base de données s'il y a la place. C'est l'OS qui gère la pagination : vous n'allouez donc pas réellement 256 Mo d'un coup, vous autorisez simplement le mapping jusqu'à cette limite.

mmap_size fait toute la différence sur les charges à dominante lecture. Et il reste inoffensif sur les petites bases. Les valeurs par défaut sont prudentes, donc l'augmenter est presque toujours bénéfique.

PRAGMA optimize

Le planificateur de requêtes s'appuie sur des statistiques pour choisir les index. Des statistiques obsolètes, ce sont des plans d'exécution médiocres. PRAGMA optimize met à jour ces statistiques sans coûter cher :

PRAGMA optimize;

Le pattern recommandé, c'est de la lancer juste avant de fermer les connexions de longue durée — à l'arrêt de l'application, ou en fin de handler de requête qui garde une connexion ouverte un moment. C'est rapide (souvent quelques millisecondes) et ça ne fait du travail que s'il y a vraiment quelque chose à mettre à jour.

À ne pas confondre avec ANALYZE, qui reconstruit complètement les statistiques. optimize en est la version légère, conçue pour être appelée souvent.

Lire toute la configuration

Pour voir comment une connexion est actuellement configurée, il suffit d'interroger les PRAGMA sans leur affecter de valeur :

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

Très utile en débogage : quand on se connecte avec un autre driver et qu'on se demande pourquoi le comportement a changé, c'est presque toujours une histoire de PRAGMA qui diffère.

Il existe aussi PRAGMA pragma_list;, qui liste tous les PRAGMA disponibles dans le build :

PRAGMA pragma_list;

Pas le genre de truc qu'on retient par cœur, mais bien pratique quand on en a besoin.

Réglages à définir à la création, pas à l'exécution

Certains PRAGMA configurent le fichier de base de données lui-même et ne sont pris en compte qu'avant la création de la moindre table :

• PRAGMA page_size = 8192; — taille des pages sur disque. La valeur par défaut est 4096, ce qui convient à la plupart des cas. Des pages plus grandes sont utiles pour les lignes volumineuses.
• PRAGMA encoding = 'UTF-8'; — encodage du texte.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

Si vous modifiez page_size sur une base existante, il faudra lancer VACUUM pour que le changement soit pris en compte. Le mieux : définir ces valeurs une bonne fois pour toutes à la création, puis ne plus y toucher.

Un exemple concret de configuration de connexion

Dans une vraie application, ce code se retrouve généralement à l'endroit où l'on ouvre la connexion. Voici l'idée :

-- À exécuter une fois sur chaque nouvelle connexion :
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- À exécuter périodiquement, ou avant la fermeture :
PRAGMA optimize;

temp_store = MEMORY garde les tables et index temporaires en RAM, ce qui accélère les requêtes qui doivent trier ou agréger sans index.

Et voilà, c'est toute la checklist pour la prod. Une demi-douzaine de lignes, et SQLite passe de « ça suffit en dev » à « OK pour une vraie charge de travail ».

La suite : les erreurs classiques

Même avec les bons PRAGMA, vous allez tomber sur les grands classiques de SQLite : database is locked, disk I/O error, constraint failed. La page suivante détaille ce que chacune veut vraiment dire, et comment s'en sortir.