Dans les fichiers d’une base de données (2/3) : PostgreSQL

Après InnoDB, PostgreSQL. Les deux moteurs résolvent les mêmes problèmes (stocker des lignes, les retrouver vite, survivre à un crash, laisser lire pendant qu’on écrit) mais ont fait des choix presque opposés. Les connaître explique le besoin de VACUUM, le gonflement des tables (bloat), ou pourquoi le premier SELECT après un gros chargement écrit sur le disque.

Le répertoire de données

Premier réflexe à perdre : chercher ses tables par leur nom. PostgreSQL désigne tout par des numéros. Chaque base a un OID, et c’est le nom de son répertoire sous base/. Chaque table, index ou séquence a un relfilenode, et c’est le nom de son fichier. Ce numéro change quand la table est réécrite (TRUNCATE, VACUUM FULL, CLUSTER, certains ALTER TABLE) : PostgreSQL crée un nouveau fichier et bascule, ce qui rend ces opérations sûres en cas de crash.

SELECT oid, datname FROM pg_database;
SELECT pg_relation_filepath('orders');        -- base/16384/24576
SELECT pg_size_pretty(pg_total_relation_size('orders'));   -- table + index + TOAST

• global/ contient les catalogues partagés par toutes les bases (rôles, liste des bases) et pg_control, le petit fichier qui dit où reprendre après un crash ;
• pg_wal/ est le journal d’écriture anticipée (WAL), en segments de 16 Mo : l’équivalent du redo d’InnoDB ;
• pg_xact/ garde l’état de chaque transaction sur deux bits : en cours, validée, annulée. C’est lui qui permet de savoir si une version de ligne est visible ;
• pg_tblspc/ contient des liens symboliques vers les tablespaces créés ailleurs. Chez PostgreSQL, un tablespace n’est qu’un répertoire sur un autre disque.

Une relation : des forks et des segments

Chaque table est en réalité un petit ensemble de fichiers. Le main fork contient les données, découpé en fichiers de 1 Go au plus (24576, 24576.1…). Cette limite date d’une époque où tous les systèmes de fichiers ne géraient pas les gros fichiers, et elle simplifie toujours la sauvegarde et la copie. La free space map indique approximativement la place libre de chaque page, pour qu’un INSERT trouve vite une page où écrire. La visibility map garde deux bits par page : « toutes les lignes sont visibles par tout le monde » et « toutes les lignes sont gelées ». Elle permet à VACUUM de sauter les pages déjà propres, et aux index-only scans de ne pas lire la table.

Anatomie d’une page

Les pages font 8 Ko (une constante de compilation, BLCKSZ). Leur structure est la même pour les tables et les index :

• l’en-tête de 24 octets contient le pd_lsn, la position dans le WAL de la dernière modification. PostgreSQL l’utilise pour une règle simple : une page ne peut pas être écrite sur le disque avant que le WAL qui la décrit le soit. Il contient aussi la somme de contrôle (activée par défaut à l’initialisation depuis PostgreSQL 18, à demander avec initdb -k avant), et deux décalages, pd_lower et pd_upper, qui bornent l’espace libre ;
• les line pointers forment un tableau en début de page. Chacun contient le décalage et la longueur d’un tuple. L’adresse d’une ligne, son ctid, vaut (numéro de page, numéro de pointeur), par exemple (0,2). Cette indirection permet de déplacer un tuple dans la page pour la défragmenter sans changer son adresse, donc sans toucher aux index ;
• les tuples sont écrits depuis la fin de la page, vers le haut. Quand pd_lower (fin des pointeurs) rejoint pd_upper (début des tuples), la page est pleine ;
• l’espace spécial en fin de page est vide pour une table. Les index B-tree y rangent les liens vers les pages voisines et le niveau dans l’arbre.

On peut tout regarder avec l’extension pageinspect, sur une base de test :

CREATE EXTENSION pageinspect;
SELECT lower, upper, special, pagesize FROM page_header(get_raw_page('orders', 0));
SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('orders', 0));

Un tuple

Chaque version de ligne commence par 23 octets d’en-tête, arrondis à 24 par l’alignement. Les champs essentiels sont t_xmin (la transaction qui a créé cette version), t_xmax (celle qui l’a supprimée ou remplacée) et t_ctid (l’adresse de la version suivante). Trois détails pratiques :

• l’alignement : chaque colonne est alignée selon son type (8 octets pour bigint et timestamp). Une colonne boolean suivie d’un bigint laisse 7 octets perdus. Ordonner les colonnes de la plus grande à la plus petite peut réduire une grosse table de 10 à 20 % ;
• les NULL ne prennent qu’un bit dans le bitmap, présent seulement si la ligne contient au moins un NULL ;
• TOAST : quand une ligne dépasse environ 2 Ko, PostgreSQL compresse d’abord les grandes valeurs (pglz, ou lz4 si on le demande), puis, si ça ne suffit pas, les déplace dans une table annexe, pg_toast.pg_toast_<oid>, découpées en morceaux. La ligne ne garde qu’un pointeur de 18 octets. C’est transparent, mais un SELECT * qui lit une colonne TOAST va chercher ces morceaux : ne sélectionnez que les colonnes utiles.

Un UPDATE ne modifie rien sur place

C’est la différence fondamentale avec InnoDB et Oracle. Chez PostgreSQL, un UPDATE est un DELETE suivi d’un INSERT : la transaction écrit une nouvelle version complète de la ligne, et inscrit son numéro dans le xmax de l’ancienne. Il n’y a pas de journal d’undo. Les anciennes versions sont l’undo, et elles vivent dans la table.

Pour décider si une version est visible, une transaction compare xmin et xmax à son instantané (snapshot) : la version est visible si sa création est validée et antérieure à l’instantané, et si sa suppression n’a pas eu lieu, a été annulée ou est postérieure. Consulter pg_xact à chaque fois coûterait cher. La première transaction qui lit une version note donc le résultat dans des hint bits de l’en-tête (« xmin validé », « xmax annulé »…). Cela explique un phénomène qui surprend souvent : le premier SELECT après un gros chargement écrit sur le disque, parce qu’il pose ces bits sur chaque page.

Ce que ça implique : VACUUM, HOT et le gel

• VACUUM retire les versions que plus aucune transaction ne peut voir, rend leur place réutilisable (la free space map est mise à jour) et met à jour la visibility map. Il ne rend pas l’espace au système de fichiers, sauf pour les pages vides en fin de fichier. Seul VACUUM FULL (ou l’extension pg_repack, sans verrou long) réécrit la table ;
• une transaction ouverte trop longtemps bloque le nettoyage de toutes les versions plus récentes qu’elle, dans toute la base. C’est la première chose à vérifier quand une table gonfle : SELECT pid, xact_start, state FROM pg_stat_activity ORDER BY xact_start; ;
• les mises à jour HOT (Heap-Only Tuple) : si aucune colonne indexée ne change et que la nouvelle version tient dans la même page, PostgreSQL ne touche pas aux index. L’index pointe toujours vers l’ancien pointeur, qui mène à la nouvelle version par la chaîne de ctid. Laisser de la place dans les pages augmente les chances de HOT : ALTER TABLE orders SET (fillfactor = 90); ;
• le gel (freezing) : les identifiants de transaction tiennent sur 32 bits et finissent par boucler, après environ deux milliards de transactions. Pour qu’une très vieille ligne ne paraisse pas soudain « dans le futur », VACUUM la marque comme gelée, visible par tous. Si autovacuum prend trop de retard, PostgreSQL finit par refuser les écritures pour se protéger : c’est pourquoi on ne désactive jamais autovacuum.

Les index et le WAL

Un index B-tree utilise les mêmes pages de 8 Ko. Ses entrées contiennent la clé et le ctid du tuple, c’est-à-dire une adresse physique, contrairement à InnoDB qui stocke la clé primaire. Une recherche par index est donc une seule descente plus une lecture de page. Mais l’index ne sait rien de la visibilité : il faut lire la table pour savoir si la version trouvée est visible, sauf si la visibility map dit que la page est entièrement visible. C’est ce qui rend les index-only scans efficaces sur une table bien nettoyée.

Enfin, la protection contre les pages déchirées : le problème est le même que chez InnoDB (8 Ko écrits par un disque qui garantit 4 Ko), la solution est différente. Avec full_page_writes = on, la première modification d’une page après chaque checkpoint écrit l’image complète de la page dans le WAL. Après un crash, PostgreSQL repart de cette image intacte. C’est l’équivalent du doublewrite buffer, et la raison pour laquelle le volume de WAL grimpe juste après chaque checkpoint.

Dernier billet de la série : Oracle, qui partage avec InnoDB l’undo séparé, mais pousse la logique plus loin, avec des verrous rangés dans les blocs eux-mêmes.

---

Ce billet fait partie d’une série de notes d’administration système (tag « carnet d’admin »).
Série « Dans les fichiers d’une base de données », partie 2/3. · ← Billet précédent · Billet suivant → (à paraître le 27/10) · English version