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) etpg_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 PostgreSQL

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 avecinitdb -kavant), et deux décalages,pd_loweretpd_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) rejointpd_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
bigintettimestamp). Une colonnebooleansuivie d’unbigintlaisse 7 octets perdus. Ordonner les colonnes de la plus grande à la plus petite peut réduire une grosse table jusqu’à environ 20 % (19 % sur une table de type commandes dans mon test, voir plus bas) ; - 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 unSELECT *qui lit une colonne TOAST va chercher ces morceaux : ne sélectionnez que les colonnes utiles.
Un UPDATE PostgreSQL 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. Un
VACUUMsimple ne réécrit jamais la table.VACUUM FULL,CLUSTERet l’extensionpg_repack(sans verrou long) le font, tout commeTRUNCATEet certaines formes d’ALTER TABLE, comme le changement de type d’une colonne ; - 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 : la limite dure est 231, soit environ 2,1 milliards de transactions, mais autovacuum lance le travail anti-bouclage bien plus tôt, à
autovacuum_freeze_max_age(200 millions par défaut). Pour qu’une très vieille ligne ne paraisse pas soudain « dans le futur »,VACUUMla 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 que j’ai mesuré (mise à jour du 2026-11-04)
J’ai d’abord écrit ce billet à partir de la documentation. Je l’ai ensuite testé sur PostgreSQL 16.15 (le paquet de Rocky Linux 10.2), WSL2, sur un cluster jetable créé avec un simple initdb. Ces tests ont corrigé trois affirmations plus haut (ce qui réécrit une table, le chiffre du bouclage et la fourchette de 10 à 20 %). J’ai aussi testé PostgreSQL 18.6 (paquets PGDG), voir le dernier bloc de cette section : les sommes de contrôle y sont activées par défaut, et désactivées sur la 16.15 après un simple initdb.
Quelles opérations changent le nom du fichier (relfilenode). Un VACUUM simple non ; VACUUM FULL, CLUSTER, ALTER COLUMN TYPE et TRUNCATE oui. ADD COLUMN non, même avec un DEFAULT constant :
start : base/16384/16438
after VACUUM : base/16384/16438
after VACUUM FULL : base/16384/16445
after CLUSTER : base/16384/16451
after ALTER COLUMN TYPE : base/16384/16459
after ADD COLUMN (nullable) : base/16384/16459
after ADD COLUMN DEFAULT 0 : base/16384/16459
after TRUNCATE : base/16384/16466
Les hint bits. HEAP_XMIN_COMMITTED vaut 0x0100. Il n’est pas positionné après l’INSERT et l’est par le premier SELECT, ce qui explique que la première lecture après un chargement massif écrive :
lp | infomask | xmin_committed
----+----------+----------------
1 | 800 | f
2 | 800 | f
3 | 800 | f
(3 rows)
-- after SELECT count(*)
lp | infomask | xmin_committed
----+----------+----------------
1 | 900 | t
2 | 900 | t
3 | 900 | t
(3 rows)
VACUUM contre VACUUM FULL, et un snapshot resté ouvert. 300 000 lignes, 90 % supprimées. Le VACUUM simple retire 270 000 versions mortes et le fichier garde ses 69 Mo ; VACUUM FULL écrit un nouveau fichier de 7 Mo. Avec une transaction REPEATABLE READ laissée ouverte, 100 000 lignes supprimées sont « dead but not yet removable » ; une fois la transaction terminée, VACUUM les retire et tronque les pages vides en fin de fichier :
-- after loading 300000 rows
file base/16384/16470: 72286208 bytes (69 MB), dead tuples (stats): 0, live: 300000
-- after DELETE of 90% of the rows
file base/16384/16470: 72286208 bytes (69 MB), dead tuples (stats): 270000, live: 30000
pages: 0 removed, 8824 remain, 8824 scanned (100.00% of total)
tuples: 270000 removed, 30000 remain, 0 are dead but not yet removable
-- after VACUUM
file base/16384/16470: 72286208 bytes (69 MB), dead tuples (stats): 0, live: 30000
-- after VACUUM FULL
file base/16384/16475: 7233536 bytes (7064 kB), dead tuples (stats): 0, live: 30000
-- VACUUM VERBOSE while the old snapshot is open
pages: 0 removed, 3824 remain, 3824 scanned (100.00% of total)
tuples: 0 removed, 130000 remain, 100000 are dead but not yet removable
-- VACUUM VERBOSE after the transaction ended
pages: 2941 removed, 883 remain, 2942 scanned (76.94% of total)
tuples: 100000 removed, 6931 remain, 0 are dead but not yet removable
L’ordre des colonnes. La première paire est une table construite pour perdre du padding (boolean, bigint, boolean, …), la seconde une table de type commandes (boolean, bigint, smallint, timestamptz, …). 1 000 000 de lignes chacune. Le gain dépend du schéma :
-- cas construit pour perdre du padding : col_bad (boolean, bigint, ...) contre col_good (trié)
relname | bytes | size
----------+----------+-------
col_good | 60235776 | 57 MB
col_bad | 76562432 | 73 MB
(2 rows)
saving: 21.3 %
-- table de type commandes : col_real_a (ordre par défaut) contre col_real_b (trié)
relname | size | bytes
------------+-------+----------
col_real_a | 80 MB | 84377600
col_real_b | 65 MB | 68272128
(2 rows)
saving: 19.1 %
Les segments. Une table de 1 482 Mo est découpée en fichiers de 1 Gio :
1073741824 Nov 4 17:25 /tmp/dp/data/base/16384/16525
479920128 Nov 4 17:26 /tmp/dp/data/base/16384/16525.1
TOAST. Une valeur de 960 caractères reste dans la ligne. Des valeurs de 2 240 et 9 600 caractères vont dans la table TOAST, par morceaux d’environ 2 Ko (2 et 5 morceaux) :
chunk_id | chunks | bytes
----------+--------+-------
16534 | 2 | 2240
16535 | 5 | 9600
(2 rows)
id | chars | stored_bytes
----+-------+--------------
1 | 960 | 964
2 | 2240 | 2240
3 | 9600 | 9600
(3 rows)
(a value with no chunk in the toast table stayed in the heap tuple)
Les images de page complètes. Juste après un CHECKPOINT, le premier UPDATE d’une page écrit environ 26 Ko de WAL dans ce test, surtout des images de page (les enregistrements dont le total avoisine 8 Ko ; « page image » marque ceux dont le total est très supérieur à l’enregistrement lui-même, donc avec une page jointe). L’UPDATE suivant sur la même page écrit 128 octets, hors enregistrement de commit :
-- first UPDATE (start 0/99272A78, end 0/99279350)
Heap2 PRUNE record 60 B, total 7456 B page image
Heap LOCK record 59 B, total 8219 B page image
Heap UPDATE record 73 B, total 3589 B page image
Btree INSERT_LEAF record 53 B, total 7453 B page image
-- second UPDATE (start 0/99279350, end 0/99279400)
Heap2 PRUNE record 57 B, total 57 B
Heap HOT_UPDATE record 71 B, total 71 B
PostgreSQL 18.6 (paquets PGDG, Rocky Linux 10.2, WSL2), simple initdb. data_checksums vaut on, initdb --no-data-checksums le désactive et -k est toujours accepté. La somme de contrôle de la page 0 calculée par page_checksum() est égale à celle stockée dans le fichier, et pg_checksums --check ne trouve aucun bloc défectueux. Les opérations qui réécrivent une table sont les mêmes que sur la 16.15 :
PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
data_checksums | on
server_version | 18.6
Data page checksum version: 1
Blocks scanned: 3842
Bad checksums: 0
### initdb --no-data-checksums, and initdb -k, in two scratch directories
initdb --no-data-checksums -> Data page checksum version: 0
initdb -k -> Data page checksum version: 1
page_checksum() : 15056
on disk pd_checksum: 15056
start : base/16384/16436
after VACUUM : base/16384/16436
after VACUUM FULL : base/16384/16444
after CLUSTER : base/16384/16450
after ALTER COLUMN TYPE : base/16384/16459
after ADD COLUMN (nullable) : base/16384/16459
after ADD COLUMN DEFAULT 0 : base/16384/16459
after TRUNCATE : base/16384/16466
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 → · English version
Laisser un commentaire