SQLite est l’intrus de cette série : pas de serveur, pas de répertoire de données, pas de processus d’arrière-plan. Les fichiers SQLite tiennent dans un seul fichier ordinaire que l’on peut lire avec un vidage hexadécimal. Cet article prolonge les trois premiers, InnoDB, PostgreSQL et Oracle avec les trois côte à côte. Le tableau final ne remplace rien : il ajoute une quatrième colonne à la comparaison.
J’ai rencontré SQLite dans d’autres outils, par exemple ProxySQL, qui garde sa configuration dans une base SQLite. Alors j’ai voulu voir les octets. Tout ce qui suit vient d’une base de laboratoire : testé sur Rocky Linux 10.2, SQLite 3.46.1, WSL2. Je prévois aussi une série plus détaillée en sept parties, « SQLite from the inside », pour 2027. Cet article reste court et suit le même plan que les trois autres.
Un fichier, jusqu’à trois voisins
Une base SQLite est un seul fichier, qu’un programme ouvre directement. Dans le mode par défaut, un deuxième fichier n’apparaît que pendant une transaction d’écriture. C’est le journal de retour arrière, et il garde la version d’origine de chaque page que la transaction modifie.
-rw-r--r--. 1 root root 8192 Nov 2 21:21 demo.db -rw-r--r--. 1 root root 4616 Nov 2 21:21 demo.db-journal
Le journal fait 4 616 octets. Son en-tête l’explique : un secteur de 512 octets, puis un enregistrement de page de 4 + 4 096 + 4 octets (numéro de page, page, somme de contrôle). Au COMMIT, le journal a disparu. Pendant la transaction, ses huit premiers octets, là où se trouve la chaîne magique, valaient encore zéro. Je n’ai pas testé pourquoi, donc je rapporte seulement ce que j’ai vu.
En mode WAL, deux autres fichiers apparaissent : le fichier -wal, où s’ajoutent les nouvelles versions de pages, et le fichier -shm, un index de 32 Kio en mémoire partagée. Contrairement aux autres modes de journal, le mode WAL est enregistré dans la base : il survit à un redémarrage. Les deux fichiers disparaissent à la fermeture propre de la dernière connexion, comme le dit la documentation.
L’en-tête de 100 octets
Chaque fichier de base SQLite commence par un en-tête de 100 octets. Les 16 premiers octets sont le texte SQLite format 3 et un octet nul. Viennent ensuite la taille de page, le compteur de changements, la taille en pages, la liste libre, le cookie de schéma, l’encodage du texte et la version de la bibliothèque qui a écrit le fichier. Tous les nombres sont en gros-boutiste.
$ xxd -l 32 demo.db 00000000: 5351 4c69 7465 2066 6f72 6d61 7420 3300 SQLite format 3. 00000010: 1000 0101 0040 2020 0000 0002 0000 0002 .....@ ........
J’ai décodé les champs avec quelques lignes de Python, et le client en ligne de commande est d’accord.
$ sqlite3 demo.db ".dbinfo" database page size: 4096 file change counter: 2 database page count: 2 freelist page count: 0 schema cookie: 1 text encoding: 1 (utf8) [...] software version: 3046001
Deux champs sont à ta disposition. PRAGMA user_version et PRAGMA application_id écrivent directement dans les octets 60 à 71 : après avoir réglé 42 et 1234567890, les octets valent 0000 002a et 4996 02d2. Beaucoup d’outils s’en servent pour marquer le format d’un fichier.
Pages et arbres B dans les fichiers SQLite
Après l’en-tête, le fichier est une suite de pages de même taille, ici 4 096 octets. La page 1 contient l’en-tête et la table de schéma, sqlite_schema. Chaque table est un arbre B classé par son rowid, et chaque index est un autre arbre B. Chaque arbre a une page racine, dont le numéro est dans sqlite_schema. Ce numéro n’a jamais changé pendant mes tests, même quand l’arbre grandissait. La table virtuelle dbstat, compilée dans le paquet Rocky, compte les pages de chaque arbre.
name|pagetype|pages|cells|payload|unused customers|internal|1|21|0|3917 customers|leaf|22|2000|77781|2282 idx_email|internal|1|13|316|3677 idx_email|leaf|14|1987|48449|2822 sqlite_schema|leaf|1|2|186|3794
Avec 2 000 lignes, la table a besoin d’une page interne et de 22 feuilles. L’index est un arbre à part, avec une page interne et 14 feuilles. Dans un index, les cellules internes portent aussi des entrées : 13 + 1 987 donnent les 2 000 clés. Une page interne de table ne porte que des clés et des pointeurs vers les enfants.
Une table déclarée WITHOUT ROWID est stockée dans un seul arbre, classé par sa clé primaire. Une table normale avec une clé texte a besoin de deux arbres, la table et un index automatique.
type|name|tbl_name|rootpage table|kv|kv|45 -- WITHOUT ROWID: one tree table|kv2|kv2|46 -- rowid table with a TEXT primary key: the table... index|sqlite_autoindex_kv2_1|kv2|47 -- ...and a second tree for the key
Les grandes valeurs quittent la page. Avec une valeur de 20 000 caractères, la feuille a gardé 3 637 octets et quatre pages de débordement ont porté le reste, 4 092 octets chacune. La documentation donne la règle, avec U = 4 096 et une charge P = 20 005 : X = U – 35 = 4 061 et M = 489. Comme P dépasse X, la partie locale vaut K = M + ((P – M) % (U – 4)) = 3 637. C’est exactement ce que rapporte dbstat.
name|pageno|pagetype|ncell|payload|unused|mx_payload notes|40|leaf|1|3637|441|20005 notes|41|overflow|0|4092|0|0 notes|42|overflow|0|4092|0|0 notes|43|overflow|0|4092|0|0 notes|44|overflow|0|4092|0|0
Anatomie d’une page
Une page d’arbre B a un en-tête de 8 octets sur une feuille (12 sur une page interne), puis un tableau de pointeurs de 2 octets vers les cellules. Les cellules elles-mêmes sont écrites depuis la fin de la page, donc l’espace libre se trouve au milieu. Sur la page 2 du laboratoire, le premier octet est 0d (une feuille de table), il y a 2 cellules, et le contenu commence au décalage 4 038.
$ xxd -s 4096 -l 16 demo.db 00001000: 0d00 0000 020f c600 0fe1 0fc6 0000 0000 ................ $ xxd -s 8128 -l 64 demo.db # the end of page 2, where the cells live 00001fc0: 0000 0000 0000 1902 0500 132b 1142 6f62 ...........+.Bob 00001fd0: 626f 6240 6578 616d 706c 652e 6e65 7444 bob@example.netD 00001fe0: 451d 0105 0017 2f11 416c 6963 6561 6c69 E...../.Aliceali 00001ff0: 6365 4065 7861 6d70 6c65 2e6e 6574 4652 ce@example.netFR
L’espace libéré dans la zone de contenu devient un bloc libre, une petite liste chaînée dans la page. Les trous de 1 à 3 octets sont seulement comptés comme octets fragmentés dans l’en-tête.
Un enregistrement, octet par octet
Une cellule contient une taille de données, un rowid et un enregistrement. Les tailles et les rowids sont des entiers variables : 1 à 9 octets, 7 bits à la fois, et le bit de poids fort signifie qu’un octet suit. L’enregistrement commence par la taille de son en-tête, puis un type par colonne. Un type de 13 ou plus et impair désigne du texte, et la longueur vaut (type – 13) / 2. Le type 0 désigne NULL. Ici la colonne id est le rowid : SQLite stocke NULL et garde la valeur dans la clé de la cellule.
1d 01 05 00 17 2f 11 41 6c 69 63 65 61 6c 69 63 65 40 65 78 61 6d 70 6c 65 2e 6e 65 74 46 52 | | | | | | '----- 'Alice' 'alice@example.net' 'FR' (5 + 17 + 2 bytes) | | | | | '-- serial type 17: text of 2 bytes | | | | '----- serial type 47: text of 17 bytes | | | '-------- serial type 23: text of 5 bytes | | '----------- serial type 0: NULL (id is the rowid) | '-------------- record header: 5 bytes '----------------- rowid 1, payload of 29 bytes (1d is the payload size, 01 the rowid)
SELECT rowid, id, name, email, country FROM customers renvoie 1|1|Alice|alice@example.net|FR : le décodage est juste.
UPDATE et DELETE sur le disque
Rien dans les fichiers SQLite ne fonctionne comme un stock d’annulation. SQLite modifie la page elle-même, et il garde l’ancienne version dans le journal ou le WAL. Chaque changement validé incrémente le compteur de changements dans l’en-tête. Un UPDATE de même taille a réécrit la valeur sur place, avec le même nombre de cellules et le même espace libre, mais le compteur est passé de 7 à 8.
-- page 3 is the first leaf of customers before change counter 7 page 3: ncell 102, payload 3655, unused 25 page_count 39 same-size UPDATE counter 8 page 3: ncell 102, payload 3655, unused 25 larger UPDATE counter 9 page 3: ncell 89, payload 3242, unused 490 page_count 40
L’UPDATE plus grand a fait davantage. La page était presque pleine, donc elle a dû se diviser : la page 3 est passée de 102 à 89 cellules, le fichier a gagné une page et la racine un pointeur. En bref, une ligne qui grossit peut coûter une page.
Un DELETE libère la cellule et transforme son espace en bloc libre. Le paquet Rocky est compilé avec SECURE_DELETE, donc PRAGMA secure_delete renvoie 1 et les octets libérés sont mis à zéro. Avec le réglage désactivé, deux occurrences de l’adresse e-mail sont restées dans le fichier.
$ sqlite3 del.db "PRAGMA secure_delete;" 1 -- page 3 header after DELETE FROM customers WHERE id=5: cells 101 | first freeblock offset 3921 | fragmented bytes 0 freeblock at 3921: next 0, size 36 bytes its bytes: 00 00 00 24 00 00 00 00 00 00 00 00 [...] 00 00 00 00 <- zeroed $ grep -a -c "user5@example.net" del.db # secure_delete on 0 $ sqlite3 del2.db "PRAGMA secure_delete=OFF; DELETE FROM customers WHERE id=5;" $ grep -a -c "user5@example.net" del2.db # secure_delete off 2
Pourquoi les fichiers SQLite ne rétrécissent pas
Quand une page entière se vide, elle rejoint la liste libre, que décrivent les octets 32 à 39 de l’en-tête. Le fichier garde sa taille. Après un DELETE de 1 800 lignes, 31 pages sur 39 étaient libres, et le fichier faisait toujours 159 744 octets. VACUUM l’a reconstruit en 32 768 octets.
before DELETE page_count 39 freelist_count 0 size 159744 DELETE ... id > 200 page_count 39 freelist_count 31 size 159744 (header bytes 32-39: 0000 0006 0000 001f) after VACUUM page_count 8 freelist_count 0 size 32768
Avec auto_vacuum en mode incrémental, on peut rendre des pages par étapes. L’octet 52 de l’en-tête contient alors la plus grande page racine, et l’octet 64 passe à 1. La page 2 devient une table de pointeurs, faite d’entrées de 5 octets : un type et une page parente. dbstat saute cette page.
PRAGMA auto_vacuum=INCREMENTAL; VACUUM; -> header byte 52 = 4, byte 64 = 1 DELETE ... id > 200 page_count 40 freelist_count 31 size 163840 PRAGMA incremental_vacuum(5); page_count 35 freelist_count 26 size 143360 PRAGMA incremental_vacuum; page_count 9 freelist_count 0 size 36864 page 2 (pointer map) starts: 01 00000000 | 01 00000000 | 05 00000003 | 05 00000003 | ...
Un type de page est documenté mais non testé ici : la page d’octet verrou, qui n’existe que si le fichier dépasse 1 Gio.
Le WAL
En mode WAL, les fichiers SQLite changent dans un autre ordre : un écrivain ajoute d’abord des trames au fichier -wal. Chaque trame est un en-tête de 24 octets suivi d’une page. Une trame dont le champ de taille n’est pas nul est un commit. Les lecteurs utilisent le dernier commit et l’index shm pour trouver la bonne version de chaque page.
-rw-r--r--. 1 root root 159744 Nov 2 21:23 wal.db -rw-r--r--. 1 root root 32768 Nov 2 21:23 wal.db-shm -rw-r--r--. 1 root root 12392 Nov 2 21:23 wal.db-wal
WAL magic : 0x377f0682 format version : 3007000 page size : 4096 checkpoint seq : 0 file size : 12392 bytes = 32 + 3 frames x (24 + 4096) frame 1: page 24, commit-size-after 0 (not a commit frame) frame 2: page 39, commit-size-after 39 frame 3: page 3, commit-size-after 39
Un point de contrôle copie les trames dans le fichier principal. Un point de contrôle PASSIVE l’a fait sans toucher au fichier WAL, alors que TRUNCATE l’a vidé. J’ai désactivé le point de contrôle automatique pour garder les trames visibles ; par défaut, il se déclenche à 1 000 pages.
PRAGMA wal_checkpoint(PASSIVE); -> 0|3|3 (busy, frames in the WAL, frames copied) -wal still 12392 bytes PRAGMA wal_checkpoint(TRUNCATE); -> 0|0|0 -wal now 0 bytes last connection closes -> demo.db-wal and demo.db-shm are gone
Les quatre moteurs côte à côte
Voici comment les fichiers SQLite se comparent aux trois premières parties, avec les valeurs par défaut.
| InnoDB | PostgreSQL | Oracle | SQLite | |
|---|---|---|---|---|
| Où vivent les données | fichiers de tablespace, un par table par défaut | un fichier par table ou index, en segments | datafiles dans des tablespaces | un seul fichier pour tout |
| Unité d’E/S | page de 16 Ko | page de 8 Ko | bloc, 8 Ko par défaut | page, 4 096 octets ici |
| Format d’une ligne | index clusterisé : la table est l’arbre de la clé primaire | tas, les index y pointent | bloc avec un répertoire de lignes et un ROWID | arbre B classé par rowid, ou par clé pour WITHOUT ROWID |
| Anciennes versions | journaux d’annulation, à part | dans la table, nettoyées par VACUUM | tablespace d’annulation | aucune : le journal ou le WAL les garde jusqu’au commit |
| Sécurité en cas de panne | journal de reprise et doublewrite buffer | WAL et écriture de pages complètes | journaux de reprise | journal de retour arrière ou WAL |
| Récupérer de la place | reconstruire la table | VACUUM, VACUUM FULL | shrink, move | VACUUM, incremental_vacuum |
Le plus proche cousin de SQLite ici est PostgreSQL pour le WAL, et InnoDB pour l’arbre. La grande différence est qu’aucun processus serveur ne se tient entre ton programme et le fichier : le format du fichier et les verrous de fichier du système portent les garanties.
Sources
- SQLite documentation: database file format
- SQLite documentation: write-ahead logging
- SQLite documentation: PRAGMA statements
- SQLite documentation: the dbstat virtual table
- SQLite documentation: VACUUM
- Les sorties enregistrées viennent de mon propre laboratoire : Rocky Linux 10.2, SQLite 3.46.1, WSL2.
Cet article fait partie d’une série de notes d’admin système (étiquette « carnet d’admin »).
Série « Dans les fichiers d’une base de données », partie 4/3+1. · ← Article précédent