Étiquette : PostgreSQL

En bref : j’ai fait un kill -9 sur les processus postgres : une colonne identity PostgreSQL est passée de l’id 9 à 37, parce que les séquences PostgreSQL journalisent 32 valeurs d’avance dans le WAL. Rollbacks et insertions en échec brûlent aussi des valeurs, et une colonne BY DEFAULT ignore les id explicites. Préférer GENERATED ... AS IDENTITY à serial, et ne jamais compter sur des id continus.

PostgreSQL 18.6, cluster jetable sous Rocky Linux 10.2. Partie 1 sur 5 (PostgreSQL, MariaDB, Oracle, SQL Server, résumé). Toutes les sorties viennent de mes propres tests.

Trois façons d’obtenir une valeur générée

CREATE TABLE t_ident  (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, v text);
CREATE TABLE t_serial (id serial PRIMARY KEY, v text);
CREATE SEQUENCE s1;
CREATE TABLE t_seq    (id int PRIMARY KEY DEFAULT nextval('s1'), v text);

Chaque INSERT ... RETURNING id renvoie 1 sur une table neuve. Le catalogue montre la différence :

 column_name |            column_default            | is_identity | identity_generation
-------------+--------------------------------------+-------------+---------------------
 id          |                                      | YES         | ALWAYS
 id          | nextval('s1'::regclass)              | NO          |
 id          | nextval('t_serial_id_seq'::regclass) | NO          |

Une colonne identity n’a pas d’expression par défaut ; la séquence est attachée à la colonne (pg_get_serial_sequence('t_ident','id') renvoie public.t_ident_id_seq). serial est un raccourci : colonne entière plus défaut appelant nextval.

ALWAYS ou BY DEFAULT

Avec GENERATED BY DEFAULT, une valeur explicite est acceptée mais ne fait pas avancer le compteur :

INSERT INTO t_by(id, v) VALUES (2, 'explicit 2');         -- ok
INSERT INTO t_by(v) VALUES ('default 1') RETURNING id;    -- 1
INSERT INTO t_by(v) VALUES ('default 2') RETURNING id;    -- ERROR: duplicate key ... (id)=(2) already exists
INSERT INTO t_by(v) VALUES ('default 3') RETURNING id;    -- 3

L’insertion en échec a brûlé la valeur 2 du compteur. Avec GENERATED ALWAYS, une valeur explicite est refusée sauf demande expresse :

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Après OVERRIDING SYSTEM VALUE VALUES (50, ...), l’insertion par défaut suivante a renvoyé 1 : le compteur est indépendant des données.

Rollback et échecs consomment des valeurs

BEGIN; INSERT INTO t_ident(v) VALUES ('rolled back') RETURNING id;   -- 2
ROLLBACK;
INSERT INTO t_ident(v) VALUES ('after rollback') RETURNING id;       -- 3

Idem avec une violation d’unicité : id 1, erreur sur le doublon, puis 3. Un compteur sans trou demande une autre conception (table de compteur verrouillée), au prix de la sérialisation des écritures.

Concurrence : l’ordre des id n’est pas l’ordre des commits

La session A insère en premier et valide 3 secondes après B ; la session B insère en second et valide aussitôt :

A got id 1
B got id 2
B: committed 08:18:48.652
A: committed 08:18:51.154

La ligne de B est visible avant celle de A, bien que son id soit plus grand. Ne pas utiliser « max(id) déjà vu » comme curseur de réplication ou de polling.

CACHE est par session

CREATE SEQUENCE s_cache CACHE 10;
same session twice : 1|2
new session        : 11
another new session: 21

Avec les séquences PostgreSQL, chaque session réserve un bloc de 10. Les valeurs de sessions différentes s’entrelacent et des trous apparaissent dès qu’une session se termine.

Limites et CYCLE

ERROR:  nextval: reached maximum value of sequence "s_small" (3)

Après ALTER SEQUENCE s_small CYCLE, la valeur suivante est 1. Une identity sur smallint s’arrête à 32767 avec la même erreur sur t_small_id_seq. Les identity integer s’arrêtent à 2147483647 : prendre bigint pour les tables à longue vie.

Redémarrage et crash

Trois lignes insérées, la dernière supprimée, redémarrage propre :

sequence before the restart: 3|30|t     (last_value | log_cnt | is_called)
sequence after the restart:  3|30|t
new id = 4

Puis cinq lignes de plus (dernier id 9), kill -9 de tous les processus postgres, redémarrage :

sequence before the crash: 9|27|t
sequence after the crash:  36|0|t
new id = 37
ids now: 1,2,4,5,6,7,8,9,37

log_cnt est le nombre de valeurs encore couvertes par le dernier enregistrement WAL. PostgreSQL écrit un enregistrement WAL pour 32 valeurs ; après la récupération sur crash, il repart de la fin de la plage réservée (9 + 27 = 36), donc l’id suivant est 37. Un arrêt propre écrit l’état exact, sans trou.

Où lire l’état des séquences PostgreSQL

SELECT * FROM pg_sequences;                -- départ, incrément, max, cache_size, cycle, last_value
SELECT last_value, log_cnt, is_called FROM t_ident_id_seq;
SELECT attrelid::regclass, attname, attidentity FROM pg_attribute WHERE attidentity <> '';  -- 'a' ALWAYS, 'd' BY DEFAULT

Documentation officielle

PostgreSQL, site du projet

CREATE SEQUENCE, documentation PostgreSQL 18

Sequence manipulation functions, nextval, currval, setval

CREATE TABLE, GENERATED ... AS IDENTITY et serial

pg_sequences, la vue système utilisée plus haut

*Non testé ici : réplication logique des séquences, pg_upgrade, tables partitionnées, ALTER SEQUENCE ... RESTART en cours d’usage.*

Toutes les parties de la série

1/5 PostgreSQL

2/5 MariaDB (à paraître le 17/11)

3/5 Oracle (à paraître le 24/11)

4/5 SQL Server (à paraître le 1/12)

5/5 TL;DR : les quatre moteurs comparés (à paraître le 8/12)

Articles liés

Dans les fichiers d’une base de données (1/3) : InnoDB, de MySQL à MariaDB

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

Dans les fichiers d’une base de données (3/3) : Oracle, et les trois côte à côte

J’ai lancé un script en lecture seule sur ma propre machine pour regarder à l’intérieur d’une table LanceDB, et un assistant IA (Claude, d’Anthropic) m’a aidée à écrire ce script et à rédiger cet article. Les chiffres ci-dessous sont la sortie réelle de ce lancement, avec LanceDB 0.41.0 et pyarrow 26.0.1. J’ai remplacé le nom de la table et les chemins. C’est le premier article d’une série sur LanceDB vu par une DBA.

Réponse rapide

Une table LanceDB est un répertoire, et rien d’autre : des fichiers de données dans data/, un manifeste par version dans _versions/, un fichier de transaction par validation dans _transactions/, et un répertoire _indices/ dès qu’on construit un index. Ma table contient 1 130 595 lignes de vecteurs à 1024 dimensions et pèse 13,4 Gio sur disque, mais sa version courante, la 265, ne référence qu’un seul fichier de données de 5,8 Gio. Les 7,6 Gio restants environ sont répartis dans 259 anciens fichiers de données dont aucune version courante n’a besoin, et ils restent sur disque tant que personne ne nettoie les anciennes versions. Chaque ajout crée un nouveau fichier de données et une nouvelle version, c’est ainsi qu’une table atteint 265 versions et 260 fichiers de données, et la dernière version a fusionné 226 fragments en un sans supprimer les anciens fichiers.

Ce que contient le répertoire d’une table LanceDB

La première partie de mon script parcourt le répertoire de la table LanceDB, docs_index.lance, et n’a pas besoin de LanceDB. L’arborescence contient trois sous-répertoires. La documentation de l’agencement des fichiers en liste d’autres, dont deux, _indices/ et _deletions/, sont absents de ma table.

Répertoire	Ce que dit la documentation	Ce que j’ai trouvé
data/	fichiers de données (*.lance)	260 fichiers, 13,4 Gio, médiane 21,6 Mio, le plus gros 5,8 Gio
_versions/	manifestes (*.manifest) et un latest_version_hint.json optionnel	265 manifestes, 2,4 Mio au total, plus un fichier d’indice de 15 octets
_transactions/	fichiers de transaction (*.txn)	265 fichiers, 56,7 Kio au total, médiane 132 octets
_indices/	un dossier par index	absent

La table compte 791 fichiers en tout. Les statistiques donnent num_indices: 0 et num_deleted_rows: 0, ce qui correspond à l’absence de ces deux répertoires : je n’ai jamais construit d’index et jamais supprimé de ligne.

Une version, c’est un manifeste et une transaction

Le manifeste est le cœur du format. La documentation le décrit comme la description immuable d’une version unique, avec le schéma, la liste des fragments, le numéro de version, un horodatage et une référence à la transaction qui l’a créée. Ma table a 265 manifestes pour 265 versions, donc exactement un manifeste par version, et 265 fichiers de transaction, donc chaque validation en a laissé un.

Les noms portent de l’information. Un fichier de transaction s’appelle {read_version}-{uuid}.txn, où le nombre est la version à partir de laquelle la transaction a été construite, et le plus gros de ma table est 263-45522501-…txn, de 20,1 Kio. Les manifestes déroutent au début, mais la logique est simple : le fichier de la version 265 est 18446744073709551350.manifest. Ce nombre est 18446744073709551615, le plus grand entier non signé sur 64 bits, moins 265, et la version 1 est 18446744073709551614.manifest. Les noms décroissent donc quand les versions augmentent. Les pages de documentation que j’ai lues n’expliquent pas ce schéma, donc ma lecture n’est qu’une hypothèse : la version la plus récente arrive en premier dans un listing de répertoire. Le fichier latest_version_hint.json fait 15 octets. La documentation parle d’une « simple optimisation », destinée à accélérer la recherche de la dernière version sur les stockages où lister un répertoire coûte cher.

Chaque ajout crée un fragment

Les cinq dernières versions montrent comment la table a grossi. list_versions a renvoyé ces lignes, que j’ai réduites aux champs intéressants et alignées en tableau :

version  timestamp                    rows       data files
261      2026-11-03 22:55:19.615921   1,123,032  224
262      2026-11-03 22:56:55.260015   1,127,160  225
263      2026-11-03 22:58:15.392524   1,130,595  226
264      2026-11-03 22:59:22.144490   1,130,595  226
265      2026-11-03 22:59:22.177593   1,130,595  1

De la 261 à la 263, chaque version ajoute un fichier de données et quelques milliers de lignes : 4 128 lignes pour le premier ajout et 3 435 pour le second. C’est l’opération Append de la documentation, qui ajoute de nouveaux fragments sans modifier les données existantes. De petits ajouts produisent donc de petits fichiers de données, et une table alimentée par petits lots accumule les fragments : la mienne en avait 226, et le fichier de données médian fait 21,6 Mio.

Les versions 264 et 265 sont séparées de 33 millisecondes, et le nombre de lignes ne change pas. J’ai décodé leurs transactions avec les définitions protobuf publiées dans le dépôt Lance. La version 264 est une transaction reserve_fragments de 46 octets : elle réserve un identifiant de fragment, et le max_fragment_id du manifeste passe de 258 à 259 alors que les 226 fragments restent les mêmes. La version 265 est une transaction rewrite, le compactage : elle remplace les 226 anciens fragments par un nouveau fragment, d’identifiant 259, qui contient les 1 130 595 lignes. La documentation décrit une Rewrite comme une opération qui « réorganise les données sans modification sémantique », et c’est ce que montre le fichier. Les versions 21 et 33 de la même table contiennent des réécritures plus anciennes, de 21 fragments en un et de 6 en un, et la première version est un overwrite de 329 octets.

La version courante d’une table LanceDB est plus petite que la table

Les statistiques de la version 265 donnent la taille de ce dont la table a réellement besoin :

total_bytes: 6,203,819,954   num_rows: 1,130,595   num_deleted_rows: 0
num_indices: 0               num_fragments: 1      num_small_fragments: 0

Cela fait 5,8 Gio, soit environ 5 487 octets par ligne. La colonne de vecteurs contient 1024 flottants de 4 octets, donc 4 096 octets par ligne, et les 1 391 octets restants par ligne contiennent le texte, le titre, le chemin et le numéro de segment. Le répertoire data/ pèse 13,4 Gio, ce qui laisse environ 7,6 Gio dans 259 fichiers de données que la version 265 ne référence pas. Ce sont les fragments des versions précédentes, toujours là parce que rien ne les a supprimés. Dans cette table, la fusion des fragments n’a pas réduit le répertoire : le nouveau fichier a été ajouté et les anciens sont restés en place, toujours référencés par les anciennes versions.

Pourquoi le manifeste d’un seul fragment pèse 20,5 Kio

Les manifestes grossissent avec le nombre de fragments : 628 et 736 octets pour les premiers, 20,4 Kio à 226 fragments, soit environ 90 octets par fragment. La version 265 n’a qu’un fragment et son fichier fait pourtant 21 031 octets, et je l’ai décodé pour comprendre pourquoi. Le fichier commence par une longueur sur 4 octets et une copie de la transaction, puis une seconde longueur et le manifeste proprement dit, puis un pied de 16 octets qui se termine par la marque LANC. Le manifeste proprement dit fait 391 octets. Les 20 620 autres octets sont la transaction intégrée, la même réécriture que celle stockée dans 263-45522501-…txn, qui liste les 226 fragments qu’elle remplace, à 88,6 octets chacun en moyenne. À titre de comparaison, les manifestes des versions 263 et 264 font chacun 20,8 Kio de liste de fragments, plus une petite transaction intégrée de 136 et 50 octets.

Les 20 Kio du dernier manifeste ne décrivent donc pas la version courante. Ils enregistrent ce que le compactage a retiré de la table, et ils sont présents deux fois sur disque, dans le manifeste et dans le fichier de transaction. Ce champ s’appelle transaction_section dans le protobuf. Je n’ai pas trouvé cela décrit dans les pages du format que j’ai lues.

Ce que je n’ai pas testé

Je n’ai lancé aucune opération d’écriture sur la table d’origine, parce que je voulais la laisser intacte : ni compactage, ni nettoyage des anciennes versions, ni index. Le script ne fait que lister des fichiers et appeler count_rows, schema, list_versions, list_indices et stats. J’ai décodé les manifestes et les fichiers de transaction uniquement pour les versions 1, 21, 33 et 263 à 265, et uniquement sur une copie de la table. Je n’ai pas non plus mesuré de temps de requête, qui dépend de l’index et viendra dans un prochain article.

Documentation officielle

Les pages format de table Lance, agencement des fichiers, versions et transactions décrivent ce que j’ai mesuré, et les définitions protobuf table.proto et transaction.proto donnent les noms de champs. Le projet LanceDB documente l’API Python derrière le script.

Dernier volet de la série, Oracle. Je ne fais pas tourner Oracle chez moi. Ce billet s’est d’abord appuyé sur mon métier de DBA et sur la documentation d’Oracle ; je l’ai depuis vérifié sur un conteneur Oracle AI Database 26ai Free jetable (voir la fin du billet). Oracle est le plus ancien des trois moteurs, et le plus explicite sur sa structure : il distingue soigneusement la vue logique (tablespaces, segments, extents, blocs) des fichiers physiques, et il expose presque tout dans des vues du dictionnaire.

Structure logique et fichiers physiques

Une base Oracle se compose de plusieurs familles de fichiers :

• les datafiles contiennent les données. Un tablespace regroupe un ou plusieurs datafiles, et un datafile n’appartient qu’à un seul tablespace ;
• les control files sont la carte de la base : la liste des datafiles et des journaux, le SCN (le numéro de version global de la base) du dernier checkpoint, l’historique des sauvegardes RMAN. On les multiplexe sur plusieurs disques, car sans eux la base ne démarre pas ;
• les online redo logs forment un journal circulaire, organisé en groupes de fichiers identiques (membres). Quand un groupe est plein, Oracle passe au suivant (log switch). En mode ARCHIVELOG, chaque groupe plein est copié en archived log avant d’être réutilisé : c’est ce qui permet une restauration à un instant précis ;
• le tablespace d’undo garde les images d’avant des données modifiées ;
• les tempfiles servent aux tris et aux jointures qui ne tiennent pas en mémoire ; ils ne sont pas journalisés ni sauvegardés ;
• le spfile contient les paramètres de l’instance, et le fichier de mots de passe permet les connexions administrateur à distance.

SELECT tablespace_name, file_name, bytes/1024/1024 AS mb, autoextensible FROM dba_data_files;
SELECT group#, members, bytes/1024/1024 AS mb, status FROM v$log;
SELECT name FROM v$controlfile;

Côté logique, un segment correspond à un objet qui occupe de la place : une table, un index, une partition, un LOB. Un segment est composé d’extents, chacun formé de blocs contigus dans un seul datafile. Un segment peut donc s’étaler sur plusieurs fichiers, extent par extent. Enfin, le bloc est l’unité d’entrée-sortie, 8 Ko par défaut (DB_BLOCK_SIZE, fixé à la création de la base).

SELECT segment_name, segment_type, extents, blocks FROM dba_segments WHERE owner = 'SHOP';
SELECT extent_id, file_id, block_id, blocks FROM dba_extents
WHERE owner = 'SHOP' AND segment_name = 'ORDERS' ORDER BY extent_id;

Dans un datafile Oracle

Le début de chaque datafile contient son en-tête (identité du fichier, SCN du dernier checkpoint, état de sauvegarde), puis, dans un tablespace géré localement (le cas normal aujourd’hui), un bitmap qui indique quels extents sont occupés. Avant, cette information vivait dans des tables du dictionnaire, ce qui créait de la contention lors des allocations. Deux politiques d’allocation existent : UNIFORM (tous les extents ont la même taille) et AUTOALLOCATE, où Oracle choisit des extents de plus en plus grands à mesure que le segment grossit (64 Ko, puis 1 Mo, puis 8 Mo… ; dans le test en fin de billet, un extent de 5 Mo est aussi apparu entre ceux de 1 Mo et de 8 Mo).

Dans un segment en ASSM (Automatic Segment Space Management, le défaut), des blocs bitmap à trois niveaux décrivent le remplissage de chaque bloc de données, par tranches de 25 %. Une session qui insère cherche un bloc assez libre dans ces bitmaps, et des sessions différentes sont orientées vers des blocs différents, ce qui évite qu’elles se battent pour le même bloc. L’en-tête de segment garde la liste des extents et la High Water Mark (HWM), la limite des blocs déjà formatés. Un parcours complet de la table lit tous les blocs jusqu’à la HWM, même vides : après un gros DELETE, un ALTER TABLE orders SHRINK SPACE (ou MOVE) fait redescendre la HWM.

Anatomie d’un bloc Oracle

• L’en-tête cache (20 octets) identifie le bloc : type, adresse (le RDBA, numéro de fichier et de bloc), SCN de la dernière modification, somme de contrôle. Avec DB_BLOCK_CHECKSUM=TYPICAL, la somme est vérifiée à chaque lecture depuis le disque ;
• l’ITL (Interested Transaction List) est propre à Oracle : une entrée de 24 octets par transaction qui modifie le bloc, avec son identifiant (XID), l’adresse de son undo et son état. INITRANS fixe le nombre d’entrées réservées à la création du bloc. Si toutes sont prises et que le bloc n’a plus de place pour en ajouter, une nouvelle transaction attend : c’est l’attente enq: TX - allocate ITL entry, qu’on corrige en augmentant INITRANS sur les tables très concurrentes ;
• le répertoire des lignes donne le décalage de chaque ligne dans le bloc, deux octets par ligne. Un ROWID désigne une entrée de ce répertoire, ce qui permet, comme chez PostgreSQL, de déplacer une ligne dans le bloc sans changer son adresse ;
• PCTFREE (10 % par défaut) réserve de l’espace libre pour les UPDATE qui allongent les lignes. Sans cette réserve, une ligne qui grandit ne tient plus dans son bloc et doit migrer : elle part dans un autre bloc et laisse à sa place un pointeur. Chaque lecture par ROWID coûte alors deux blocs. Une ligne trop grande pour n’importe quel bloc est chaînée sur plusieurs blocs. La statistique table fetch continued row mesure les deux ;
• la queue (4 octets) répète une partie du SCN, du type et de la séquence de l’en-tête : un bloc dont la tête et la queue ne correspondent pas a été écrit à moitié (fractured block). Oracle n’a pas de doublewrite : ce cas est détecté à la lecture, et le bloc se répare depuis une sauvegarde RMAN et les journaux (RECOVER ... BLOCK). Pendant une sauvegarde à chaud manuelle (BEGIN BACKUP), Oracle écrit l’image complète des blocs dans le redo à leur première modification, comme le full_page_writes de PostgreSQL.

Une ligne et son ROWID

Une ligne commence par trois octets : des indicateurs, l’octet de verrou et le nombre de colonnes. Viennent ensuite, pour chaque colonne, sa longueur et sa valeur. Les NULL au milieu de la ligne coûtent un octet (0xFF), et les NULL en fin de ligne ne sont pas stockés du tout. Placer les colonnes souvent nulles en dernier économise donc de la place.

L’octet de verrou est le détail le plus élégant du format : quand une transaction modifie une ligne, elle y inscrit le numéro de son entrée dans l’ITL du bloc. Le verrou de ligne est donc stocké dans le bloc lui-même, sans table de verrous en mémoire : verrouiller un million de lignes ne coûte pas plus de mémoire que d’en verrouiller une. Une autre transaction qui veut la même ligne lit l’ITL, trouve la transaction propriétaire et attend la fin de celle-ci. Après le COMMIT, l’octet de verrou n’est pas remis à zéro tout de suite : il continue de pointer vers l’entrée ITL jusqu’à ce qu’une visite ultérieure nettoie le bloc (voir le test en fin de billet).

Le ROWID est l’adresse physique d’une ligne : numéro d’objet, numéro de fichier relatif au tablespace (0 dans un tablespace bigfile, le type par défaut sur Oracle 26ai Free), numéro de bloc, numéro de ligne dans le répertoire. C’est l’accès le plus rapide qui soit, et c’est ce que contiennent les index B-tree. Le paquet DBMS_ROWID le décode :

SELECT rowid,
       dbms_rowid.rowid_object(rowid)       AS obj,
       dbms_rowid.rowid_relative_fno(rowid) AS file#,
       dbms_rowid.rowid_block_number(rowid) AS block#,
       dbms_rowid.rowid_row_number(rowid)   AS row#
FROM shop.orders WHERE id = 7;

Pour voir le contenu brut d’un bloc, ALTER SYSTEM DUMP DATAFILE 4 BLOCK 151; écrit sa description complète (en-têtes, ITL, chaque ligne en hexadécimal) dans le fichier de trace de la session. À réserver à une base de test : c’est très instructif, mais ce n’est pas un outil de production.

Lecture cohérente : l’undo

Comme InnoDB, Oracle modifie les blocs sur place et range l’image d’avant dans l’undo. Quand une requête démarre, elle note le SCN courant. Si elle tombe sur un bloc modifié après ce SCN, elle en fabrique une copie en mémoire et lui applique à rebours les enregistrements d’undo désignés par l’ITL, jusqu’à obtenir le bloc tel qu’il était à son SCN de départ : c’est le consistent read. Les lecteurs ne bloquent jamais les écrivains, ni l’inverse.

Le revers : si l’undo nécessaire a déjà été écrasé par des transactions plus récentes, la requête échoue avec ORA-01555: snapshot too old. UNDO_RETENTION indique combien de temps Oracle doit essayer de garder l’undo, à condition que le tablespace d’undo soit assez grand. Autre phénomène, cousin des hint bits de PostgreSQL : le delayed block cleanout. Après un gros COMMIT, Oracle ne revisite pas tous les blocs modifiés, et c’est le premier SELECT suivant qui nettoie les entrées ITL… et génère du redo, à la surprise générale.

Les trois moteurs côte à côte

	InnoDB	PostgreSQL	Oracle
Taille de page	16 Ko	8 Ko	8 Ko (2 à 32 Ko)
Organisation de la table	arbre B+ de la clé primaire	tas (heap)	tas (heap) par défaut, IOT en option
Adresse d’une ligne dans un index	clé primaire (index secondaires)	ctid (page, slot)	ROWID (objet, fichier, bloc, ligne)
Anciennes versions	undo log	dans la table	tablespace d’undo
Nettoyage	purge thread	VACUUM	réutilisation de l’undo
Pages déchirées	doublewrite buffer	full_page_writes (WAL)	contrôle du bloc + restauration RMAN
Verrous de ligne	en mémoire (lock system)	xmax dans le tuple	octet de verrou → ITL du bloc

Aucun de ces choix n’est meilleur dans l’absolu. L’index cluster d’InnoDB rend les recherches par clé primaire imbattables mais pénalise les clés aléatoires. Le tas de PostgreSQL rend les mises à jour simples et les retours en arrière instantanés, au prix de VACUUM. L’undo séparé d’Oracle et d’InnoDB garde les tables compactes, mais impose de dimensionner l’undo. Savoir comment les données sont rangées sur le disque, c’est savoir lequel de ces compromis on paie, et à quel moment.

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 Oracle AI Database 26ai Free 23.26.3.0.0, l’image container-registry.oracle.com/database/free lancée avec podman 5.8.2 sur Rocky Linux 10.2, WSL2, dans la base pluggable FREEPDB1, avec un utilisateur et un tablespace jetables. C’est une édition Free, pas Enterprise, et pas une 19c : les comportements ci-dessous sont ceux de cette version. Les tests ont corrigé deux points plus haut (le numéro de fichier du ROWID et la cellule du tableau pour InnoDB) et ajouté deux précisions (l’extent de 5 Mo, et l’octet de verrou après un commit).

Fichiers et structure logique. Trois groupes de redo de 200 Mo avec un seul membre, deux fichiers de contrôle, le mode ARCHIVELOG, un bloc de 8 Ko, DB_BLOCK_CHECKSUM = TYPICAL. Le tablespace d’undo et les datafiles sont dans la PDB, les redo logs et les fichiers de contrôle dans la base conteneur. Le type de tablespace par défaut est BIGFILE :

BANNER_FULL
------------------------------------------------------------------------------------------
Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
Version 23.26.3.0.0
NAME	  CDB	  CON_ID
--------- --- ----------
FREE	  YES	       0
### in the PDB FREEPDB1
TABLESPACE_N FILE_NAME									    MB AUT
------------ ---------------------------------------------------------------------- ---------- ---
SYSTEM	     /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf				   290 YES
SYSAUX	     /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf				   420 YES
UNDOTBS1     /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf				  2690 YES
USERS	     /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf				     7 YES
SHOP_TS      /opt/oracle/oradata/FREE/FREEPDB1/shop_ts01.dbf				   900 YES
DB_BLOCK_SIZE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8192
    GROUP#    MEMBERS	      MB STATUS
---------- ---------- ---------- ----------------
	 1	    1	     200 CURRENT
	 2	    1	     200 ACTIVE
	 3	    1	     200 ACTIVE
NAME
----------------------------------------------------------------------
/opt/oracle/oradata/FREE/control01.ctl
/opt/oracle/oradata/FREE/control02.ctl
TABLESPACE_N SEGMEN ALLOCATIO BLOCK_SIZE
------------ ------ --------- ----------
SYSTEM	     MANUAL SYSTEM	    8192
SYSAUX	     AUTO   SYSTEM	    8192
UNDOTBS1     MANUAL SYSTEM	    8192
TEMP	     MANUAL UNIFORM	    8192
USERS	     AUTO   SYSTEM	    8192
SHOP_TS      AUTO   SYSTEM	    8192
NAME								       VALUE
---------------------------------------------------------------------- ------------------------------------------------------------
spfile								       /opt/oracle/product/26ai/dbhomeFree/dbs/spfileFREE.ora
db_block_checksum						       TYPICAL
db_block_size							       8192
undo_tablespace 						       UNDOTBS1
undo_retention							       900
LOG_MODE
------------
ARCHIVELOG
TABLESPACE_N BIG
------------ ---
SYSTEM	     YES
SYSAUX	     YES
UNDOTBS1     YES
TEMP	     NO
USERS	     YES
SHOP_TS      YES
DEFAULT_TABLESPACE_TYPE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BIGFILE
FILE_NAME								       MB
---------------------------------------------------------------------- ----------
/opt/oracle/oradata/FREE/FREEPDB1/temp01.dbf				       20
USERNAME   SYSDB
---------- -----
SYS	   TRUE
### redo logs and control files live in the CDB, not in the PDB
    GROUP# MEMBER
---------- ----------------------------------------------------------------------
	 3 /opt/oracle/oradata/FREE/redo03.log
	 2 /opt/oracle/oradata/FREE/redo02.log
	 1 /opt/oracle/oradata/FREE/redo01.log

La croissance des extents. 3 millions de lignes dans un nouveau tablespace AUTOALLOCATE (ALLOCATION_TYPE = SYSTEM, ASSM) : 16 extents de 64 Ko, puis 63 de 1 Mo, un de 5 Mo, puis des extents de 8 Mo :

TABLESPACE_NAME 	       SEGMEN ALLOCATIO
------------------------------ ------ ---------
SHOP_TS 		       AUTO   SYSTEM
SEGMENT_NAME															 SEGMENT_TYPE	       EXTENTS	   BLOCKS	  MB
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ---------- ----------
ORDERS																 TABLE			    88	    17024	 133
 EXTENT_ID    FILE_ID	BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
	 0	   17	     776	  8
	 1	   17	     792	  8
	 2	   17	     808	  8
 EXTENT_KB    EXTENTS FIRST_EXTENT LAST_EXTENT
---------- ---------- ------------ -----------
	64	   16		 0	    15
      1024	   63		16	    78
      5120	    1		79	    79
      8192	    8		80	    87

Le high water mark. Après avoir supprimé toutes les lignes d’une table de 500 000 lignes, le segment garde ses 7 936 blocs et un parcours complet de la table vide coûte 7 740 consistent gets. Après SHRINK SPACE, le segment fait 8 blocs et le parcours en coûte 2 :

WHAT				       BLOCKS	      MB
---------------------------------- ---------- ----------
after load				 7936	      62
WHAT				       BLOCKS	      MB
---------------------------------- ---------- ----------
after DELETE of all rows		 7936	      62
NAME				    VALUE
------------------------------ ----------
consistent gets 		    35822
 ROWS_LEFT
----------
	 0
NAME				    VALUE
------------------------------ ----------
consistent gets 		    43562
WHAT				       BLOCKS	      MB
---------------------------------- ---------- ----------
after SHRINK SPACE			    8	   .0625
NAME				    VALUE
------------------------------ ----------
consistent gets 		    44895
 ROWS_LEFT
----------
	 0
NAME				    VALUE
------------------------------ ----------
consistent gets 		    44897

Un ROWID décodé avec DBMS_ROWID. Le numéro de fichier vaut 0 parce que le tablespace est de type bigfile ; le numéro de bloc 1302 est celui du bloc vidé plus bas (identifiant de fichier absolu 17) :

ROWID			     ID        OBJ	FILE#	  BLOCK#       ROW#
-------------------- ---------- ---------- ---------- ---------- ----------
AAAR2tAAAAAAAUWAAA	      1      73133	    0	    1302	  0
AAAR2tAAAAAAAUWAAB	      2      73133	    0	    1302	  1
AAAR2tAAAAAAAUWAAC	      3      73133	    0	    1302	  2

ALTER SYSTEM DUMP DATAFILE 17 BLOCK 1302 juste après les insertions et le commit, abrégé aux lignes utiles. Le bloc a 2 entrées ITL (itc: 2). Chaque ligne commence par tl (longueur totale), fb (indicateurs), lb (l’octet de verrou) et cc (le nombre de colonnes). La ligne d’id 2 (tab 0, row 1) a un NULL au milieu (*NULL*) et un NULL final qui n’est pas stocké (cc: 4 pour 5 colonnes) ; la ligne d’id 3 (tab 0, row 2) ne stocke que 2 colonnes. Après le commit, les trois lignes portent encore lb: 0x1 et l’entrée ITL affiche toujours Lck 3 : le commit ne nettoie pas le bloc :

  frmt: 0x02 chkval: 0xa025 type: 0x06=trans data
Block header dump:  0x00000516
 Object id on Block? Y
 seg/obj: 0x11dad  csc:  0x00000000002faa20  itc: 2  flg: E-  typ: 1 - DATA
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.01f.000002c5  0x0000af26.0159.25  --U-    3  fsc 0x0000.002faa22
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x00000516 type: 0x06 SAGE: 0
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f68
avsp=0x1f4f
tosp=0x1f4f
0xe:pti[0]	nrow=3	offs=0
tab 0, row 0, @0x1f7b
tl: 29 fb: --H-FL-- lb: 0x1  cc: 5
col  0: [ 2]  c1 02
col  1: [ 5]  61 6c 70 68 61
col  2: [ 4]  62 65 74 61
col  3: [ 5]  67 61 6d 6d 61
col  4: [ 5]  64 65 6c 74 61
tab 0, row 1, @0x1f70
tl: 11 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 2]  c1 03
col  1: [ 1]  78
col  2: *NULL*
col  3: [ 1]  7a
tab 0, row 2, @0x1f68
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  78

Le même bloc pendant qu’une autre session détient un UPDATE non validé de la ligne d’id 1 (tab 0, row 0). La ligne modifiée porte maintenant lb: 0x2, qui désigne l’entrée ITL 2 (Lck 1, un nouvel identifiant de transaction) ; l’entrée 1 a été nettoyée (C---, Lck 0) et les autres lignes ont lb: 0x0. La ligne a grossi de 29 à 31 octets et s’est déplacée dans le bloc :

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.01f.000002c5  0x0000af26.0159.25  C---    0  scn  0x00000000002faa22
0x02   0x0005.00b.0000029d  0x00000184.013f.0b  ----    1  fsc 0x0000.00000000
tab 0, row 0, @0x1f49
tl: 31 fb: --H-FL-- lb: 0x2  cc: 5
tab 0, row 1, @0x1f70
tl: 11 fb: --H-FL-- lb: 0x0  cc: 4
tab 0, row 2, @0x1f68
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2

Verrous de ligne et lignes migrées. Modifier 1 ligne ou 500 000 lignes prend les deux mêmes verrous dans V$LOCK (un TX, un TM) ; ce qui grossit, c’est l’undo (1 enregistrement, puis 500 000 enregistrements dans 5 815 blocs d’undo). Verrouiller un million de lignes ne coûte donc pas de mémoire dans une table de verrous, mais coûte de l’undo. Pour les lignes migrées, 100 000 lignes ont été insérées avec PCTFREE 0 puis allongées de 1 à 300 caractères : les relire a enregistré 99 733 événements table fetch continued row (des lignes qu’il a fallu suivre vers un autre bloc) :

### locking 1 row versus 500000 rows: v$lock and undo
WHAT		       TY      LOCKS
---------------------- -- ----------
after 1 row	       TM	   1
after 1 row	       TX	   1
WHAT			USED_UBLK  USED_UREC
---------------------- ---------- ----------
after 1 row			1	   1
WHAT		       TY      LOCKS
---------------------- -- ----------
after 500000 rows      TM	   1
after 500000 rows      TX	   1
WHAT			USED_UBLK  USED_UREC
---------------------- ---------- ----------
after 500000 rows	     5815     500000
### migrated rows: PCTFREE 0, rows that grow
TABLE_NAME															   NUM_ROWS	BLOCKS	CHAIN_CNT
-------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ----------
MIG																     100000	  4528		0
  COUNT(*)
----------
    100000
STAT				       DELTA
--------------------------------- ----------
table fetch continued row (delta)      99733

La lecture cohérente. Une transaction en lecture seule continue de voir la valeur 10 après qu’une autre session a validé 20 ; les statistiques montrent un enregistrement d’undo appliqué :

WHO			V
-------------- ----------
reader, before	       10
WHO			   V
----------------- ----------
writer, committed	  20
WHO				  V
------------------------ ----------
reader, after the commit	 10
NAME								  VALUE
------------------------------------------------------------ ----------
consistent changes						      1
data blocks consistent reads - undo records applied		      1

Le nettoyage différé des blocs. Après avoir modifié et validé 2 000 000 de lignes, un simple SELECT COUNT(*) a généré environ 5 Mo de redo et 57 046 « cleanouts only – consistent read gets » :

  COUNT(*)
----------
   2000000
STAT				       DELTA
--------------------------------- ----------
redo size generated by the SELECT    5020444
STAT					   DELTA
------------------------------------- ----------
cleanouts only - consistent read gets	   57046

L’attente ITL. Une table de 3 000 lignes d’un caractère, créée avec PCTFREE 0, remplit complètement ses blocs : le bloc de la ligne 1500 contient 733 lignes et il lui reste 7 octets (avsp=0x7), avec ses 2 entrées ITL. Deux sessions modifient chacune une ligne différente de ce bloc et restent sans valider. Une troisième session qui modifie une troisième ligne du même bloc ne peut pas obtenir d’entrée ITL, faute de place pour en ajouter : elle attend sur enq: TX - allocate ITL entry, bloquée par l’une des autres sessions, jusqu’à leur annulation (14,7 secondes ici) :

block 38701, rows with ids 1467, 1468 and 1469 are used
 seg/obj: 0x11de1  csc:  0x000000000040b7b1  itc: 2  flg: E-  typ: 1 - DATA
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.005.0000026d  0x0000b5a0.00d7.04  --U-  733  fsc 0x0000.0040b7b3
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
nrow=733
avsp=0x7
       SID USERNAME EVENT				     SECONDS_IN_WAIT BLOCKING_SESSION
---------- -------- ---------------------------------------- --------------- ----------------
       227 SHOP     enq: TX - allocate ITL entry			   2		   58
Elapsed: 00:00:14.73

ORA-01555. Une table de 300 000 lignes est chargée normalement, puis le tablespace d’undo est remplacé par un tablespace de 10 Mo qui ne peut pas grandir, avec UNDO_RETENTION = 5. Un lecteur parcourt la table par son index dans une boucle PL/SQL et dort toutes les 20 000 lignes ; pendant ce temps, un écrivain valide 240 petites mises à jour (8 passes sur la table, 10 000 lignes par commit). Le lecteur échoue après 79 971 lignes parce que l’undo dont il avait besoin a été écrasé. Mon premier essai n’a pas échoué, sans doute parce que l’optimiseur avait lu et trié toutes les lignes dès le premier fetch, si bien que le lecteur n’avait plus besoin d’anciens blocs (je n’ai pas regardé le plan). Forcer un parcours d’index avec un hint a fait apparaître l’erreur :

undo_tablespace      UNDO_SMALL
undo_retention	     5
reader: failed after 79971 rows: ORA-01555: snapshot too old: rollback segment
number	with name "" too small
writer: 8 passes of 30 commits done

Non testé : la vérification de la queue de bloc et la réparation d’un bloc fracturé avec RMAN, les images de bloc complètes écrites pendant une sauvegarde à chaud, les tailles d’une entrée ITL (24 octets) et d’une entrée du répertoire des lignes (2 octets), l’octet unique d’un NULL au milieu d’une ligne, les tables organisées en index, et toute édition ou version autre qu’Oracle AI Database 26ai Free.

---

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 3/3. · ← Billet précédent · English version

Articles liés

• Les fichiers d’InnoDB, de MySQL à MariaDB
• Les fichiers de PostgreSQL
• Un cluster Oracle réparé

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 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 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 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 un SELECT * 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 VACUUM simple ne réécrit jamais la table. VACUUM FULL, CLUSTER et l’extension pg_repack (sans verrou long) le font, tout comme TRUNCATE et 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 2³¹, 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 », 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 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

Articles liés

• Les fichiers d’InnoDB, de MySQL à MariaDB
• Les fichiers d’Oracle, blocs et ROWID
• Les réglages MariaDB qui comptent

Avec InnoDB comme avec les autres moteurs, une base de données relationnelle donne l’illusion de tables, de lignes et de colonnes. Sur le disque, il n’y a que des fichiers découpés en blocs de taille fixe. Comprendre comment ces blocs sont organisés explique beaucoup de comportements qui semblent mystérieux : pourquoi un fichier ne rétrécit pas après un DELETE, pourquoi une clé primaire en UUID ralentit les insertions, pourquoi un index secondaire coûte plus cher qu’on ne le pense, ou ce que protège vraiment le « doublewrite buffer ».

Cette série de trois billets ouvre le capot de trois moteurs : InnoDB (le moteur de stockage de MySQL et de MariaDB), PostgreSQL et Oracle. Le même fil conducteur les traverse : les fichiers, les pages, les lignes, et l’endroit où vivent les anciennes versions des données. Premier arrêt : InnoDB.

Le répertoire de données

Chaque fichier a un rôle précis :

• ibdata1, le tablespace système. Historiquement, il contenait tout : tables, index, undo, dictionnaire. Aujourd’hui, il ne garde plus que le change buffer (sur MySQL) et, sur MariaDB, le doublewrite et parfois l’undo. Il ne rétrécit jamais : un ibdata1 de 50 Go gonflé par une vieille transaction le restera, et la seule façon de le réduire est un export complet suivi d’une réimportation ;
• un fichier .ibd par table, grâce à innodb_file_per_table=ON (le défaut depuis MySQL 5.6). C’est ce qui permet de récupérer l’espace d’une table supprimée ou vidée (DROP, TRUNCATE), de déplacer une table seule, ou de restaurer un seul tablespace ;
• les tablespaces d’undo (undo_001…) : les images d’avant des lignes modifiées, pour les ROLLBACK et pour la lecture cohérente des autres transactions ;
• le journal redo : chaque modification de page y est décrite avant que la page elle-même soit écrite sur le disque. Après un crash, InnoDB rejoue ce journal ;
• le doublewrite buffer, expliqué plus bas ;
• les binlogs, qui n’appartiennent pas à InnoDB mais au serveur : ils décrivent les changements au niveau SQL, pour la réplication et la restauration à un instant donné.

Une base de données SQL (CREATE DATABASE shop) n’est rien d’autre qu’un répertoire. Les noms exacts des fichiers globaux varient : MySQL et MariaDB ont divergé sur le dictionnaire (MySQL 8 a remplacé les fichiers .frm par un dictionnaire transactionnel dans mysql.ibd, MariaDB a gardé les .frm), sur le redo et sur le doublewrite. Le format interne des pages, lui, est commun. Pour voir les tablespaces d’une instance :

-- MySQL 8
SELECT name, file_size, allocated_size FROM information_schema.INNODB_TABLESPACES;
-- MariaDB
SELECT name, file_size, allocated_size FROM information_schema.INNODB_SYS_TABLESPACES;

Pages, extents, segments

L’unité de base est la page, de 16 Ko par défaut. Sa taille (innodb_page_size) est fixée à la création de l’instance et ne peut plus changer ensuite. Tout ce qu’InnoDB lit ou écrit, il le lit ou l’écrit par pages entières : lire une ligne de 100 octets, c’est charger 16 Ko en mémoire, dans le buffer pool.

Les pages sont regroupées en extents de 64 pages, soit 1 Mo. Allouer par extents plutôt que page par page garde les pages d’un même index physiquement proches, ce qui rend les lectures séquentielles efficaces.

Chaque index possède deux segments : un pour ses pages feuilles, un pour ses pages intermédiaires. Les séparer garde les feuilles (là où sont les données) contiguës, pour les parcours par plage. Pour ne pas réserver 1 Mo à chaque petite table, un segment reçoit d’abord jusqu’à 32 pages isolées, et ne passe aux extents complets qu’ensuite. C’est pourquoi une table vide occupe une centaine de kilo-octets, et pas plusieurs mégaoctets.

Les premières pages d’un .ibd sont réservées à la gestion de l’espace : la page 0 (FSP_HDR) décrit l’état des extents, la page 1 le bitmap du change buffer, la page 2 les « inodes » des segments. Sur MySQL 8, la page 3 contient la description de la table en JSON (SDI) et la racine de l’index cluster est en page 4. Sur MariaDB, qui garde des .frm, la racine est en page 3.

Anatomie d’une page InnoDB

Chaque zone a une raison d’être :

• Le FIL header est commun à toutes les pages, quel que soit leur type. Il contient le numéro de page, le type de page et le LSN (Log Sequence Number) de la dernière modification. Dans l’ancienne disposition, il contient aussi la somme de contrôle, qui détecte une corruption à la lecture ; sur MariaDB 10.11, la disposition par défaut (full_crc32) laisse les octets 0 à 3 à zéro et place la somme de contrôle tout à la fin de la page (voir les tests en fin de billet). Après un crash, InnoDB compare ce LSN à celui de chaque entrée du redo : il ne rejoue que ce que la page n’a pas encore reçu. Les pointeurs vers les pages précédente et suivante relient toutes les pages d’un même niveau de l’arbre ;
• l’INDEX header donne le niveau de la page dans l’arbre (0 pour une feuille), le nombre de lignes et l’identifiant de l’index auquel elle appartient ;
• infimum et supremum sont deux fausses lignes, la plus petite et la plus grande possibles. Elles servent de début et de fin à la liste chaînée des lignes, ce qui simplifie le code d’insertion ;
• les lignes sont écrites dans l’ordre où elles arrivent, mais chacune pointe vers la suivante dans l’ordre de la clé. Insérer au milieu ne déplace rien : il suffit de modifier deux pointeurs ;
• le page directory part de la fin de la page et grandit vers le haut. Il contient un « slot » pour 4 à 8 lignes. Pour trouver une clé, InnoDB fait une recherche dichotomique dans ces slots, puis parcourt au plus quelques lignes de la liste chaînée ;
• le FIL trailer occupe les 8 derniers octets de la page. Dans l’ancienne disposition, il répète la somme de contrôle et les 32 bits de poids faible du LSN : une page dont le début et la fin ne correspondent pas n’a été écrite qu’à moitié. Sur MariaDB 10.11 (full_crc32), les 4 derniers octets sont la somme de contrôle elle-même, un CRC-32C du reste de la page, et les 4 octets qui les précèdent sont les 32 bits de poids faible du LSN.

Quand une page est pleine et qu’une ligne doit y entrer, InnoDB la coupe en deux (page split) et met à jour la page parente. Quand deux pages voisines descendent sous la moitié de leur remplissage, il peut les fusionner. Ces opérations sont coûteuses et laissent des pages à moitié vides : c’est la fragmentation.

Le doublewrite buffer : pourquoi écrire deux fois

Un disque garantit l’atomicité d’écriture de 512 octets ou 4 Ko, pas de 16 Ko. Si le serveur s’arrête au milieu de l’écriture d’une page, celle-ci peut se retrouver à moitié ancienne, à moitié nouvelle : une torn page. Le redo ne peut pas la réparer, car il décrit des modifications à appliquer sur une page cohérente. InnoDB écrit donc chaque page d’abord dans le doublewrite buffer, puis à son emplacement définitif. Après un crash, une page déchirée est remplacée par sa copie intacte, puis le redo s’applique. Sur un système de fichiers qui garantit lui-même des écritures atomiques de 16 Ko (ZFS, certains SSD), on peut le désactiver.

Une ligne

• Les colonnes cachées : DB_TRX_ID est l’identifiant de la dernière transaction qui a modifié la ligne, DB_ROLL_PTR pointe vers l’image précédente dans l’undo. Une transaction qui ne doit pas voir la modification suit ce pointeur pour reconstruire l’ancienne version ;
• sans clé primaire, InnoDB utilise la première clé unique non nulle, ou à défaut ajoute une colonne cachée DB_ROW_ID de 6 octets, alimentée par un compteur global partagé par toutes ces tables. Définissez toujours une clé primaire ;
• le bit « deleted » : un DELETE ne fait que marquer la ligne. Elle est réellement retirée plus tard par le purge thread, une fois qu’aucune transaction ne peut plus la voir ;
• les NULL ne coûtent qu’un bit dans le bitmap : aucune place dans les données ;
• les colonnes longues (TEXT, BLOB, gros VARCHAR) : une ligne doit tenir dans environ la moitié d’une page, pour qu’une page contienne au moins deux lignes. Au format DYNAMIC, une valeur trop longue part entière dans des pages de débordement, et la ligne ne garde qu’un pointeur de 20 octets.

L’index cluster d’InnoDB : la table est un arbre

C’est la décision de conception la plus importante d’InnoDB : la table est l’index de la clé primaire. Il n’existe pas de « tas » de lignes à côté : les lignes complètes sont stockées dans les feuilles d’un arbre B+ trié par clé primaire. Plusieurs conséquences pratiques en découlent :

• une recherche par clé primaire descend l’arbre et trouve directement la ligne. Avec des pages de 16 Ko et une clé BIGINT, une page intermédiaire référence plus d’un millier de pages filles : trois niveaux suffisent pour des centaines de millions de lignes ;
• une clé primaire croissante (AUTO_INCREMENT) ajoute toujours à droite de l’arbre : les pages se remplissent proprement, sans split au milieu. Une clé aléatoire (UUID v4) insère n’importe où, provoque des splits partout et laisse des pages à moitié pleines. Les UUID ordonnés dans le temps (UUID v7) évitent ce problème ;
• un index secondaire ne contient pas l’adresse physique de la ligne mais sa clé primaire. Une recherche par index secondaire coûte donc deux descentes d’arbre. En contrepartie, déplacer une ligne lors d’un split ne demande pas de mettre à jour les index secondaires ;
• une clé primaire longue est recopiée dans chaque index secondaire : une clé de 36 caractères multiplie la taille de tous les index ;
• un index couvrant, qui contient toutes les colonnes dont la requête a besoin, évite la seconde descente : EXPLAIN affiche alors Using index.

Pourquoi le fichier ne rétrécit pas

Un DELETE massif libère de la place dans les pages, que les insertions suivantes réutiliseront, mais ne rend rien au système de fichiers. Pour récupérer l’espace, il faut reconstruire la table :

OPTIMIZE TABLE shop.orders;          -- équivaut, pour InnoDB, à ALTER TABLE … FORCE
-- data_free n'est qu'une estimation, voir les tests plus bas
SELECT table_name, data_free/1024/1024 AS free_mb
FROM information_schema.tables WHERE table_schema='shop' ORDER BY data_free DESC;

N’utilisez pas data_free pour décider si une reconstruction vaut le coup : dans mon test, il est resté à 7,0 Mo après la suppression de 90 % d’une table dont le fichier faisait 80 Mio, et la reconstruction a ramené le fichier à 9 Mio. C’est la taille du fichier sur disque que la reconstruction récupère.

La reconstruction copie toutes les lignes dans un nouveau fichier, compact, puis remplace l’ancien. Elle se fait en ligne sur les versions récentes, mais demande temporairement l’espace d’une seconde copie de la table.

Prochain billet : PostgreSQL, qui a fait le choix inverse sur presque tous ces points : une table en tas, des pages de 8 Ko, et des anciennes versions gardées dans la table elle-même.

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 MariaDB 10.11.18, Rocky Linux 10.2, WSL2, sur une instance jetable avec un buffer pool de 256 Mo. Ces tests ont corrigé deux affirmations plus haut (la position de la somme de contrôle et data_free) et confirmé la disposition des pages, les colonnes cachées, le comportement d’OPTIMIZE TABLE et l’emplacement du doublewrite buffer. Je n’ai pas testé MySQL 8 : les numéros de page 3 (SDI) et 4 (racine) pour MySQL viennent de la documentation.

Les deux vues de l’information schema, et les fichiers d’une base d’une seule table :

-rw-rw---- 1 mysql mysql  1055 Nov  4 17:21 orders.frm
-rw-rw---- 1 mysql mysql 65536 Nov  4 17:21 orders.ibd
| name             | file_size | allocated_size |
| innodb_system    |  12582912 |       12587008 |
| shop/orders      |     65536 |          65536 |
| innodb_temporary |  12582912 |       12582912 |
ERROR 1109 (42S02) at line 1: Unknown table 'INNODB_TABLESPACES' in information_schema

Les premières pages de orders.ibd (4 pages de 16 Kio), après un arrêt propre. La page 0 est l’en-tête de l’espace, la 1 le bitmap du change buffer, la 2 les inodes de segments, et la page 3 est la racine de l’index clustered :

page 0: type=8 (FSP_HDR) page_no=0 space_id=5 lsn=46034 prev=0xffffffff next=0xffffffff
page 1: type=5 (IBUF_BITMAP) page_no=1 space_id=5 lsn=45760 prev=0xffffffff next=0xffffffff
page 2: type=3 (INODE) page_no=2 space_id=5 lsn=46034 prev=0xffffffff next=0xffffffff
page 3: type=17855 (INDEX) page_no=3 space_id=5 lsn=47160 prev=0xffffffff next=0xffffffff

Où se trouve la somme de contrôle. Avec innodb_checksum_algorithm = full_crc32, les octets 0 à 3 sont à zéro, et les 4 derniers octets égalent le CRC-32C des 16380 premiers octets de la page, pour chacune des 4 pages :

innodb_checksum_algorithm = full_crc32
page 0: bytes 0-3=0x00000000  last 4 bytes=0x34c1c1a7  crc32c(page without last 4 bytes)=0x34c1c1a7  match=True
page 1: bytes 0-3=0x00000000  last 4 bytes=0x248d385f  crc32c(page without last 4 bytes)=0x248d385f  match=True
page 2: bytes 0-3=0x00000000  last 4 bytes=0x85d98b4a  crc32c(page without last 4 bytes)=0x85d98b4a  match=True
page 3: bytes 0-3=0x00000000  last 4 bytes=0x3e60cc66  crc32c(page without last 4 bytes)=0x3e60cc66  match=True

Les colonnes cachées, lues directement dans la page 3. Après la clé primaire sur 4 octets (80000001, bit de signe inversé) viennent DB_TRX_ID sur 6 octets et DB_ROLL_PTR sur 7 octets, puis les colonnes utilisateur :

id=1 at page offset 128: pk=80000001 DB_TRX_ID=000000000013 DB_ROLL_PTR=84000001340110 columns: 616c6963656669727374060300000018
id=2 at page offset 163: pk=80000002 DB_TRX_ID=000000000013 DB_ROLL_PTR=8400000134011c columns: 626f627365636f6e64050500000020ff
id=3 at page offset 197: pk=80000003 DB_TRX_ID=000000000013 DB_ROLL_PTR=84000001340128 columns: 6361726f6c7468697264000000000000

Un DELETE ne réduit pas le fichier, data_free ne voit pas l’espace libre, et OPTIMIZE TABLE reconstruit la table. ALTER TABLE ... FORCE donne la même taille finale (9437184 octets). ibdata1 est passé de 12 à 76 Mio pendant le chargement (les undo logs sont ici dans le tablespace système, innodb_undo_tablespaces = 0) et n’a pas diminué ensuite. Dans les lignes de tableau ci-dessous, les colonnes sont table_rows, data_mb, index_mb et data_free_mb :

-- after loading 300000 rows
   big.ibd: 83886080 bytes
   ibdata1: 79691776 bytes
|     299999 |     0.0 |      0.0 |          7.0 |
-- after DELETE of 90% of the rows
   big.ibd: 83886080 bytes
   ibdata1: 79691776 bytes
|      29999 |     0.0 |      0.0 |          7.0 |
| shop.big | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
-- after OPTIMIZE TABLE
   big.ibd: 9437184 bytes
   ibdata1: 79691776 bytes
|      29614 |     8.5 |      0.0 |          0.0 |
-- before ALTER TABLE FORCE
   big.ibd: 146800640 bytes
   ibdata1: 79691776 bytes
|      29614 |     8.5 |      0.0 |          7.0 |
-- after ALTER TABLE FORCE
   big.ibd: 9437184 bytes
   ibdata1: 79691776 bytes
|      29614 |     8.5 |      0.0 |          0.0 |

Les clés aléatoires. 500 000 lignes, buffer pool de 256 Mo, tout en mémoire. L’UUID() horodaté (version 1) et la clé aléatoire de type version 4 donnent des fichiers plus gros qu’un BIGINT séquentiel. Les largeurs de clé diffèrent (8 et 36 octets) : comparez plutôt les deux variantes d’UUID entre elles, la clé aléatoire est 40 % plus grosse, ce qui est cohérent avec des pages moins remplies (je n’ai pas mesuré le remplissage). Je n’ai pas pu montrer de ralentissement des insertions avec tout en mémoire, et la dernière ligne inclut le coût de RANDOM_BYTES() :

insert 500000 rows, sequential bigint : 0.8 s
insert 500000 rows, random UUID (v1)  : 1.1 s
| shop/pk_seq  |    32.0 |
| shop/pk_uuid |    48.0 |
insert 500000 rows, random v4-like UUID : 2.3 s
| shop/pk_rand |    68.0 |

Où se trouve le doublewrite buffer. J’ai démarré MariaDB 10.11.18 deux fois, avec la valeur par défaut innodb_doublewrite=ON puis avec OFF, chargé 20 000 lignes dans une table et tracé les écritures de pages avec strace. Les deux essais utilisaient innodb_use_native_aio=OFF, sinon les écritures ne passent pas par pwrite64 et strace ne les voit pas. Avec le doublewrite activé, ibdata1 reçoit deux écritures groupées qui commencent à la page 64 (160 Kio et 64 Kio), et Innodb_dblwr_pages_written passe de 0 à 39. Désactivé, rien n’est écrit à la page 64 d’ibdata1 et le compteur reste à 0. Sur MariaDB 10.11, le doublewrite buffer se trouve donc dans le tablespace système. Je n’ai pas regardé sa disposition exacte au-delà de la page de départ.

################ innodb_doublewrite = ON (default), native AIO off
| innodb_doublewrite | ON    |
| Innodb_dblwr_pages_written | 0     |
| Innodb_dblwr_pages_written | 39    |
  ibdata1             4 writes,     262144 bytes, pages 0..316
first alternations (file, consecutive writes): [['dw.ibd', 10], ['ibdata1', 1], ['dw.ibd', 39], ['ibdata1', 1], ['dw.ibd', 1], ['ibdata1', 2]]
ibdata1 writes as (first page, KiB): [(316, 16), (64, 160), (64, 64), (0, 16)]
################ innodb_doublewrite = OFF, native AIO off
| innodb_doublewrite | OFF   |
| Innodb_dblwr_pages_written | 0     |
| Innodb_dblwr_pages_written | 0     |
  ibdata1             2 writes,      32768 bytes, pages 0..49
first alternations (file, consecutive writes): [['dw.ibd', 5], ['ibdata1', 1], ['dw.ibd', 20], ['ibdata1', 1]]
ibdata1 writes as (first page, KiB): [(49, 16), (0, 16)]

Les compteurs sont ceux de SHOW GLOBAL STATUS LIKE 'Innodb_dblwr%', avant et après le chargement.

---

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 1/3. · Billet suivant → · English version

Articles liés

• Les fichiers de PostgreSQL
• Les fichiers d’Oracle, blocs et ROWID
• Les réglages MariaDB qui comptent