Les mains dans le cambouis #5 - Emplacement physique des lignes

Lyon, le 1 Juillet 2025

La série des mains dans le cambouis continue malgré une pause d’une bonne année. Pour ce nouvel article, on va se focaliser sur l’emplacement physique des lignes d’une table. Et vous verrez qu’il peut y avoir quelques surprises.

Allons voir comment sont stockées nos données dans PostgreSQL !

Lien entre table et fichiers

Vous le savez certainement, pour chaque relation (table ou index) dans PostgreSQL, existent un ou plusieurs fichiers sur disque. Créons une table ma_table dans la base postgres et récupérons son emplacement dans $PGDATA.

postgres=# CREATE TABLE ma_table (c1 integer);
CREATE TABLE
postgres=# SELECT pg_relation_filepath('ma_table');
-[ RECORD 1 ]--------+--------------
pg_relation_filepath | base/5/480736

Le fichier existe bien sur disque, mais avec une taille de 0 octet. Tant qu’aucune donnée n’est ajoutée à cette table, le fichier fera zéro octet sur disque.

$ ls -l base/5/480736 
-rw------- 1 postgres postgres 0 juin  24 13:53 base/5/480736

Insérons une ligne dans notre table et vérifions sa taille. Un entier étant codé sur 4 octets, on obtient une taille de… 8 ko !

postgres=# SELECT pg_size_pretty(pg_relation_size('ma_table', 'main'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

En effet, PostgreSQL ne sait travailler qu’avec des pages de 8 ko. Ceci est configuré via le paramètre block_size qui est défini lors de la compilation de PostgreSQL. Il n’est pas modifiable après coup. PostgreSQL crée donc une première page de 8 ko pour stocker notre entier.

Très logiquement, si on ajoute quelques entiers supplémentaires dans cette table, sa taille de 8 ko ne doit pas évoluer, puisqu’il reste suffisamment de place dans la première page allouée à cette table.

postgres=# SELECT * FROM ma_table;
 c1 
----
  1
  2
  3
  4
  5
(5 rows)

postgres=# SELECT pg_size_pretty(pg_relation_size('ma_table', 'main'));
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

Ordre des données

Le résultat du SELECT * montre des données parfaitement triées selon la colonne c1. Attention, cela peut porter à confusion, mais les données ne sont PAS automatiquement triées ! Les lignes d’une tables sont lues dans l’ordre d’apparition dans le fichier. Ici, on observe ce comportement car les insertions ont été faites de manière incrémentale.

Pour s’assurer de ce propos, utilisons l’extension pageinspect qui permet d’avoir un aperçu du contenu d’un fichier de table. Les fonctions get_raw_page et heap_page_items sont particulièrement intéressantes. Seule la colonne t_data correspondant à la donnée sera récupérée pour cet exemple.

postgres=# CREATE extension IF NOT EXISTS pageinspect;
CREATE EXTENSION
postgres=# SELECT t_data FROM heap_page_items(get_raw_page('ma_table', 0));
   t_data   
------------
 \x01000000
 \x02000000
 \x03000000
 \x04000000
 \x05000000
(5 rows)

Maintenant, supprimons les trois premières lignes et voyons ce qu’il se passe au niveau du contenu de notre page.

postgres=# DELETE FROM ma_table WHERE c1 < 4;
DELETE 3

Malgré le DELETE, les données dans le bloc semblent rester identiques. En effet, le processus d’autovacuum n’est probablement pas encore passé sur la table. Les lignes ne sont plus utilisables par les autres requêtes, mais sont encore présentes dans le fichier en attendant que le nettoyage soit fait.

postgres=# SELECT t_data FROM heap_page_items(get_raw_page('ma_table', 0));
   t_data   
------------
 \x01000000
 \x02000000
 \x03000000
 \x04000000
 \x05000000
(5 rows)

Pour ne pas attendre son déclenchement automatique, lançons un VACUUM manuellement sur la table et regardons ce qui est désormais présent dans la page de 8 ko.

postgres=# VACUUM ma_table;
VACUUM

postgres=# SELECT t_data FROM heap_page_items(get_raw_page('ma_table', 0));
   t_data   
------------



 \x04000000
 \x05000000
(5 rows)

Les trois premières lignes ont désormais disparu et, physiquement, il y a bien trois emplacements disponibles. L’objectif d’un VACUUM, qu’il soit manuel ou automatique, est de rendre l’espace réutilisable pour les futures opérations de INSERT ou UPDATE.

Ainsi, si on insère trois nouvelles lignes de même taille, elles se retrouveront physiquement au début de la page. PostgreSQL sait réutiliser l’espace disponible.

postgres=# INSERT INTO ma_table VALUES (6), (7), (8);

postgres=# SELECT t_data FROM heap_page_items(get_raw_page('ma_table', 0));
   t_data   
------------
 \x06000000
 \x07000000
 \x08000000
 \x04000000
 \x05000000
(5 rows)

Un ordre SELECT sur cette table montre bien que les données ne sont pas du tout triées. Morale de l’histoire : pensez bien à utiliser la clause ORDER BY si l’ordre de récupération vous est important.

postgres=# SELECT * FROM ma_table;
 c1 
----
  6
  7
  8
  4
  5
(5 rows)

postgres=# SELECT * FROM ma_table ORDER BY 1;
 c1 
----
  4
  5
  6
  7
  8
(5 rows)

La commande CLUSTER

Par défaut donc, PostgreSQL ne fait que réutiliser l’espace libre disponible, mais se soucie peu de l’ordre des données. Cependant la commande CLUSTER peut vous aider.

Peu connue mais présente depuis fort longtemps dans PostgreSQL, elle permet de réorganiser physiquement les lignes d’une table. La réorganisation des lignes se fait selon un index. Il faut donc en créer un, puisqu’il n’y en a pas pour le moment.

postgres=# CREATE INDEX ON ma_table (c1);
CREATE INDEX

postgres=# CLUSTER ma_table USING ma_table_c1_idx;
CLUSTER

Les données sont maintenant physiquement triées selon l’index renseigné, ici celui portant sur la colonne c1.

postgres=# SELECT t_data FROM heap_page_items(get_raw_page('ma_table', 0));
   t_data   
------------
 \x04000000
 \x05000000
 \x06000000
 \x07000000
 \x08000000
(5 rows)

Une opération CLUSTER sur une table est ponctuelle. Elle réorganise physiquement les lignes lors de son exécution, mais ces emplacements ne sont pas garantis dans le futur. Selon l’activité et les modifications apportées aux lignes de la table, elles pourraient être réécrites et voir leur emplacement physique modifié.

Un intérêt d’avoir les lignes regroupées physiquement est de limiter les accès disque et, in fine, d’accélérer les requêtes. En effet, si les lignes de la table sont triées et regroupées selon un index connu, lorsque PostgreSQL trouvera la première ligne demandée, il y aura de très grande chance que les suivantes physiquement fassent elles aussi partie de la réponse. Ceci est valide si la requête utilise l’index. Autrement dit, si vos requêtes récupèrent des données qui ne sont pas liées ou qui sont réparties dans tous les pages de la table, utiliser CLUSTER ne sera pas bénéfique.

CLUSTER est une opération lourde qui nécessite la réécriture et la réindexation des données. Comme pour un VACUUM FULL, avec CLUSTER la table ciblée est verrouillée en lecture et écriture le temps de cette opération.

Le paramètre fillfactor

fillfactor : fill : remplir et factor : facteur.

Il s’agit d’un facteur de remplissage, et si il y a bien une chose que l’on remplit avec PostgreSQL, ce sont des tables, des tables et encore des tables (parfois, c’est l’espace disque qu’on remplit avec nos WALs, qui sature, et entraîne l’arrêt de notre instance, mais ça, c’est une autre histoire 😅).

Ce paramètre est donc un pourcentage de remplissage des pages d’une table. Mais que contrôle ce taux exactement ? Remplir une table consiste à y ajouter des lignes. Cette opération d’ajout se fait notamment avec des ordres INSERT par exemple.

Avec une valeur positionnée à 100 (ce qui est d’ailleurs le paramétrage par défaut), les opérations d’INSERT peuvent utiliser la totalité de l’espace disponible dans les pages pour insérer leurs données. Si la page est pleine, ou si l’espace libre est trop petit pour l’INSERT, une nouvelle sera créée, ou il ira voir dans d’autres blocs.

Quand le fillfactor est plus bas (disons 80% par exemple) et qu’une opération d’INSERT est effectuée, si l’ajout des lignes fait dépasser ce seuil de remplissage, PostgreSQL décidera de créer une nouvelle page de 8 ko.

Alors c’est très bien, mais que fait PostgreSQL de l’espace réservé par le fillfactor ?

Il le réserve à la mise à jour des lignes de cette même page. Autrement dit, cet espace sera utilisé par des UPDATE qui voudront mettre à jour une ou plusieurs lignes de cette page. Une opération UPDATE a donc plus de chance de placer sa nouvelle version de la ligne dans la même page. C’est nettement plus efficace que de devoir la mettre dans une nouvelle page.

En plus de cela, sous certaines conditions, notamment que les colonnes modifiées ne soient pas indexées, un autre mécanisme peut rentrer en jeu : le mécanisme HOT, pour Heap-Only Tuples.

Le mécanisme de Heap-Only Tuples

Regardons ce mécanisme un peu plus en détails en créant tout d’abord une nouvelle table avec un fillfactor à 50 (ce qui est très bas !). Cette table possède deux colonnes dont une clé primaire qui sera donc indexée.

postgres=# CREATE TABLE ma_table_hot (c1 integer PRIMARY KEY, c2 integer) WITH (fillfactor = 50);
CREATE TABLE

Insérons quelques lignes (113 exactement, nous verrons pourquoi) avec le même contenu dans les deux colonnes :

postgres=# INSERT INTO ma_table_hot SELECT val, val FROM generate_series(1,113) as val;
INSERT 0 113

Regardons les cinq dernières lignes du fichier. 71 en hexadécimal correspond bien à 113 en base 10. Bref, tout va bien.

postgres=# SELECT t_data FROM heap_page_items(get_raw_page('ma_table_hot', 0)) ORDER BY 1 DESC LIMIT 5;
      t_data       
------------------
\x7100000071000000
\x7000000070000000
\x6f0000006f000000
\x6e0000006e000000
\x6d0000006d000000
(5 rows)

Maintenant lançons une commande d’INSERT.

postgres=# INSERT INTO ma_table_hot VALUES (114,114);
INSERT 0 1

Relancez la commande précédente (SELECT t_data FROM heap_page_items…) et vous verrez que rien n’a changé. Pourquoi ? Cette nouvelle ligne n’est pas ajoutée à la première page de 8 ko, mais dans une nouvelle page qui vient d’être créée. En effet, la 114ᵉ ligne dépasse le seuil de remplissage. Elle se trouve donc insérée dans la deuxième page :

postgres=# SELECT t_data FROM heap_page_items(get_raw_page('ma_table_hot', 1)) ORDER BY 1 DESC LIMIT 5;
      t_data       
------------------
\x7200000072000000
(1 row)

Maintenant mettons à jour la première ligne de cette table. L’ordre ici est un UPDATE sur la colonne c2 qui, elle, n’est pas indexée. Le mécanisme HOT devrait donc rentrer en jeu. Récupérez la colonne lp en plus de t_data.

postgres=# UPDATE ma_table_hot SET c2 = 200 WHERE c1 = 1; 
UPDATE 1
postgres=# SELECT lp, t_data FROM heap_page_items(get_raw_page('ma_table_hot', 0)) ORDER BY 1 DESC LIMIT 5;
lp        t_data       
--- ------------------
114 \x01000000c8000000
113 \x7100000071000000
112 \x7000000070000000
111 \x6f0000006f000000
110 \x6e0000006e000000
(5 rows)

La nouvelle version de la ligne est bien présente à la fin de la première page. c8 en hexadécimal équivaut à 200 en base 10. La mise à jour s’est bien effectuée dans la même page.

Un intérêt du mécanisme HOT est de ne pas à avoir à toucher aux index lors d’une mise à jour de ligne. L’index c1 n’a pas été mis à jour lors de cet UPDATE. Pourtant ce devrait être nécessaire puisque la ligne est passée physiquement du premier au dernier emplacement la page.

Comment PostgreSQL parvient à garantir que l’index ciblera toujours la bonne donnée ? L’astuce consiste à faire pointer la première ligne vers la nouvelle ligne en ajoutant un « pointeur de ligne » vers la nouvelle version de cette ligne. Ainsi, un index ciblera la première ligne, qui indiquera à son tour que la donnée se trouve à un autre emplacement physique du même bloc, indiqué par le « pointeur de ligne ». Il y a donc un gain en écriture comme en utilisation du cache.

Pour les plus curieux d’entre vous, regardez la première ligne de la première page. Que constatez-vous ? … et oui, l’ancienne valeur est toujours présente ! Il faudra attendre un prochain article pour comprendre pourquoi. On parlera de visibilité et d’autovacuum !

Noter que ce mécanisme de redirection ne fonctionne qu’à l’intérieur d’un même bloc. Sans place suffisante dans le bloc, le mécanisme HOT est désactivé. Attention, tout de même, descendre le paramètre fillfactor aura d’autres impacts, comme par exemple, la taille sur disque de la table.

Conclusion

Nos données sont présentes dans des pages de 8 ko. C’est une valeur fixe qui n’est pas modifiable par défaut. Vous pouvez le faire uniquement si vous recompilez PostgreSQL. Les données ne suivent pas d’ordre logique dans les fichiers physiques. Elles changeront de place au fur et à mesure de leur existence en base. Pour les tables les plus souvent mises à jour, le mécanisme de fillfactor permet de conserver de l’espace pour les ordres UPDATE. La mise à jour d’une colonne non indexée permet en plus de déclencher le mécanisme de Heap-Only Tuples permettant de limiter la recréation de page et les manipulations des index.

Des questions, des commentaires ? Écrivez-nous !