pgstattuple : étudier les lignes

Le but de cette extension est de fournir des informations sur les lignes vivantes et mortes, ainsi que sur l’espace libre.

Au niveau de PostgreSQL, quand une ligne est supprimée, elle n’est pas physiquement retirée de la table. Elle est toujours présente dans le fichier, mais une information système indique que la ligne n’est plus visible (en fonction des transactions). De même, quand une ligne est mise à jour, l’ancienne version n’est pas remplacée par la nouvelle. La nouvelle version est ajoutée ailleurs dans la table, et l’ancienne dispose d’une information système indiquant que celle-ci n’est plus à jour.

Les lignes « mortes » sont celles à présent invisibles de toutes les sessions. Les lignes « vivantes » sont encore visibles par une ou plusieurs sessions. Une ligne morte nettoyée laisse un espace vide dans le bloc de la table.

Tip

Si vous voulez plus d’informations sur ce mécanisme, nous vous conseillons la lecture du module M4 (Mécanique du moteur transactionnel) (formation DBA2).

Dans cet article, nous allons nous concentrer sur l’extension pgstattuple et sur les informations qu’elle apporte.

Note

pg_stattuple est aussi une contrib. Les binaires sont généralement installés avec PostgreSQL.

Commençons à tester pgstattuple

Nous allons créer une base, et une table qui nous serviront de jeu de tests :

CREATE DATABASE b1;
\c b1
CREATE TABLE t1 (c1 integer, c2 text);
INSERT INTO t1 VALUES (1, 'ligne 1');

Nous allons aussi ajouter l’extension pgstattuple :

CREATE EXTENSION pgstattuple;

Cette extension a ajouté quatre fonctions, comme l’indique la métacommande \dx+ :

b1=# \dx+ pgstattuple
Objects in extension "pgstattuple"
     Object Description     
----------------------------
 function pg_relpages(text)
 function pgstatindex(text)
 function pgstattuple(oid)
 function pgstattuple(text)
(4 rows)

La fonction pg_relpages a un intérêt assez limité : elle lit la table pour trouver le nombre de blocs occupés sur le disque. Cette information est généralement fournie par la colonne relpages du catalogue système pg_class, mais il ne faut pas oublier que ce champ n’est modifié que par les ordres VACUUM et ANALYZE (CREATE INDEX aussi, et peut-être quelques autres). Nous pouvons donc déjà voir une différence entre le retour de cette fonction et la colonne relpages :

b1=# SELECT pg_relpages(relname), relpages FROM pg_class WHERE relname='t1';
 pg_relpages | relpages 
-------------+----------
           1 |        0
(1 row)

Évidemment, après un VACUUM, cette différence disparaît :

b1=# VACUUM t1;
VACUUM
b1=# SELECT pg_relpages(relname), relpages FROM pg_class WHERE relname='t1';
 pg_relpages | relpages 
-------------+----------
           1 |        1
(1 row)

La fonction pgstattuple est plus intéressante : elle fournit des informations disponibles nulle part ailleurs. C’est une fonction SRF (Set Returning Functions), à placer dans une clause FROM :

b1=# \x
Expanded display is on.
b1=# SELECT * FROM pgstattuple('t1');
-[ RECORD 1 ]------+------
table_len          | 8192
tuple_count        | 1
tuple_len          | 36
tuple_percent      | 0.44
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 8120
free_percent       | 99.12

On apprend ainsi que la table fait 8 ko (colonne table_len). C’est logique : nous n’avons ajouté qu’une ligne dans la table, avec peu de données dans cette ligne. On apprend aussi que le nombre de lignes est de 1 et qu’elle occupe 36 octets (respectivement les colonnes tuple_count et tuple_len). Nous avons ajouté l’entier 1 (soit 4 octets) et le texte « ligne 1 » (7 caractères). À cela, il faut ajouter les colonnes système, ce qui fait que nous arrivons rapidement aux 36 octets. Toutes les colonnes dead* sur les lignes mortes sont à zéro. Comme nous n’avons rien modifié ou supprimé, c’est normal. L’espace libre est de 8120 octets. Nous occupons 36 octets avec la ligne, nous avons aussi en début de bloc un entête (24 octets) et un pointeur de ligne (4 octets). L’espace libre restant est donc 8128 octets, auxquels 8 octets sont encore retranchés pour des raisons d’alignement.

Ajoutons plus de données :

b1=# INSERT INTO t1 SELECT i, 'ligne '||i FROM generate_series(2, 100000) AS i;
INSERT 0 99999
b1=# SELECT * FROM pgstattuple('t1');
-[ RECORD 1 ]------+--------
table_len          | 4431872
tuple_count        | 100000
tuple_len          | 3988895
tuple_percent      | 90
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 16716
free_percent       | 0.38

Nous avons ajouté 100 000 lignes en tout dans cette table. L’espace libre a beaucoup diminué. En fait, tout est pratiquement occupé.

Désactivons l’autovacuum pour cette table (uniquement à titre pédagogique pour maîtriser ce qui va se passer) puis supprimons quelques données :

b1=#  ALTER TABLE t1 SET (autovacuum_enabled=off);
ALTER TABLE
b1=# DELETE FROM t1 WHERE c1 <= 10000;
DELETE 10000
b1=# SELECT * FROM pgstattuple('t1');
-[ RECORD 1 ]------+--------
table_len          | 4431872
tuple_count        | 90000
tuple_len          | 3600001
tuple_percent      | 81.23
dead_tuple_count   | 10000
dead_tuple_len     | 388894
dead_tuple_percent | 8.77
free_space         | 16716
free_percent       | 0.38

L’espace libre n’a pas changé vu qu’aucun VACUUM n’a eu lieu sur cette table (colonnes free*). La taille de la table n’a pas non plus changé. Par contre, le nombre de lignes vivantes (tuple_count), leur taille (tuple_len), ainsi que leur pourcentage (tuple_percent) ont changé. De même, les colonnes sur les lignes mortes (dead*) ont toutes évolué, de façon logique par rapport à ma suppression.

Lançons maintenant un VACUUM :

b1=# VACUUM t1;
VACUUM
b1=# SELECT * FROM pgstattuple('t1');
-[ RECORD 1 ]------+--------
table_len          | 4431872
tuple_count        | 90000
tuple_len          | 3600001
tuple_percent      | 81.23
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 416716
free_percent       | 9.4

Les compteurs dead* sont revenus à zéro. En fait, tout l’espace pris par les lignes mortes est maintenant disponible, et se retrouve donc dans l’espace libre : l’ancien dead_tuple_len additionné à l’ancien free_space donne à peu près le nouveau free_space (la valeur exacte peut varier). Le VACUUM transforme donc l’espace mort en espace disponible pour être réutilisé.

Modifions maintenant quelques données :

b1=# UPDATE t1 SET c2=upper(c2) WHERE c1 BETWEEN 10000 AND 15000;
UPDATE 5000

b1=# SELECT * FROM pgstattuple('t1');
-[ RECORD 1 ]------+--------
table_len          | 4431872
tuple_count        | 90000
tuple_len          | 3600001
tuple_percent      | 81.23
dead_tuple_count   | 5000
dead_tuple_len     | 200000
dead_tuple_percent | 4.51
free_space         | 216716
free_percent       | 4.89

Le nombre de lignes vivantes reste à 90 000. Nous avons bien 5000 nouvelles lignes mortes. Cependant, la table n’a pas changé en taille. Il y a de fortes chances que PostgreSQL ait pu réutiliser l’espace mis à disposition par le dernier VACUUM. Essayons de modifier plus de données que ce qu’il reste comme espace libre :

b1=# UPDATE t1 SET c2=upper(c2) WHERE c1 BETWEEN 15000 AND 25000;
UPDATE 10001
b1=# SELECT * FROM pgstattuple('t1');
-[ RECORD 1 ]------+--------
table_len          | 4644864
tuple_count        | 90000
tuple_len          | 3600001
tuple_percent      | 77.5
dead_tuple_count   | 10001
dead_tuple_len     | 400040
dead_tuple_percent | 8.61
free_space         | 206576
free_percent       | 4.45

Cette fois, l’espace pris par la table a augmenté. (Aparté : après suppression de 10 000 lignes supplémentaires, on s’attendrait à trouver dead_tuple_count à 15 000. En fait, pg_stat_user_tables.n_dead_tup indique presque 15 000, à une dizaines à jour HOT près, qui ont pu se faire dans des blocs partiellement remplis. Il se trouve qu’un mécanisme, dit de page pruning, a nettoyé les 5000 lignes mortes « en passant » à la lecture des blocs pour faciliter d’éventuelles futures mises à jour. Mais la Free Space Map et les statistiques ne sont pas mises à jour.)

Pour regagner l’espace manquant, il nous faut faire un VACUUM FULL (pas toujours faisable en production car l’opération bloque la table) :

b1=# VACUUM FULL t1;
VACUUM
b1=# SELECT * FROM pgstattuple('t1');
-[ RECORD 1 ]------+--------
table_len          | 3989504
tuple_count        | 90000
tuple_len          | 3600001
tuple_percent      | 90.24
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 15860
free_percent       | 0.4

Nous avons récupéré tout l’espace disponible. Le dernier bloc est partiellement rempli, d’où le free_space non nul.

Verrou de pgstattuple

Le souci principal de cette fonction est qu’elle va parcourir l’intégralité de la table. Cela ne bloquera pas les utilisateurs, mais l’utilisation du serveur va augmenter, notamment au niveau des disques.

Au niveau verrou, la fonction ne pose qu’un verrou AccessShareLock. Il est donc possible de lire et d’écrire dans la table, mais impossible de la supprimer ou de changer ses propriétés.

Trouver la fragmentation d’un index avec pgstattuple

La fragmentation d’un index est aussi récupérable. L’extension pgstattuple dispose d’une fonction permettant de connaître le niveau moyen de densité des nœuds de l’arbre d’un index.

Nous allons créer une table contenant deux colonnes et disposant d’un index (pour la clé primaire).

b1=# CREATE TABLE t1(c1 integer PRIMARY KEY, c2 text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
b1=# INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i;
INSERT 0 1000000
b1=# \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 c1     | integer | not null
 c2     | text    | 
Indexes:
    "t1_pkey" PRIMARY KEY, btree (c1)

Si on utilise la fonction pgstatindex sur le nouvel index, nous allons obtenir cette sortie :

b1=# SELECT avg_leaf_density FROM pgstatindex('t1_pkey');
 avg_leaf_density 
------------------
            90.06
(1 row)

Nous obtenons une densité de 90%. C’est normal car le facteur de remplissage (fillfactor) d’un index est à 90% par défaut.

Si nous faisons quelques suppressions, nous allons obtenir le résultat suivant :

b1=# DELETE FROM t1 WHERE c1%3=1;
DELETE 333334
b1=# SELECT avg_leaf_density FROM pgstatindex('t1_pkey');
 avg_leaf_density 
------------------
            90.06
(1 row)

Autrement dit, il n’y a eu aucune modification dans l’index. En effet, quand une commande de suppression ou de mise à jour (qui est en fait une suppression suivie d’une insertion dans PostgreSQL), les index ne sont pas immédiatement impactés. Ce n’est que pendant le VACUUM de la table que l’index est traité :

b1=# VACUUM;
VACUUM
b1=# SELECT avg_leaf_density FROM pgstatindex('t1_pkey');
 avg_leaf_density 
------------------
            60.14
(1 row)

Après suppression d’un tiers des lignes de la table, on se retrouve avec un tiers de densité en moins. Un REINDEX permet de gagner en espace disque :

b1=# SELECT pg_table_size('t1_pkey');
 pg_table_size 
---------------
      22487040
(1 row)

b1=# REINDEX INDEX t1_pkey;
REINDEX
b1=# SELECT pg_table_size('t1_pkey');
 pg_table_size 
---------------
      14999552
(1 row)

Le REINDEX nous a fait passer de 22 Mo à 15 Mo. La densité est repassée à 90% :

b1=# SELECT avg_leaf_density FROM pgstatindex('t1_pkey');
 avg_leaf_density 
------------------
            90.06
(1 row)

Contrairement à pg_freespacemap (qui ne lit que la petite Free Space Map maintenue par le VACUUM), pgstattuple nécessite la lecture de l’index complet. Cela pourrait avoir un impact sur les performances selon la taille de l’index.

Requêtes pour une analyse sur toutes les tables et index

Warning

Rappel : ces requêtes sont longues et posent un verrou empêchant de modifier la structure des tables.

Pour les tables :

-- pgstattuple pour toutes tables à partir d'une certaine taille

CREATE EXTENSION IF NOT EXISTS pgstattuple ;

SET lock_timeout TO '2s';

SELECT  relnamespace::regnamespace::text AS schema,
        c.relname,
        pg_size_pretty(pg_relation_size (c.oid)) AS taille,
        round(s.tuple_count::numeric/1.0e6,1) AS "nb_lignes_utiles_Ml",
        s.dead_tuple_count AS nb_lignes_mortes,
        to_char(greatest(last_vacuum, last_autovacuum),'YYYY-MM-DD HH:MI:SS') AS dernier_vacuum,
        pg_size_pretty(s.free_space) AS espace_libre,
        s.free_percent AS espace_libre_pc,
        pg_size_pretty(SUM(s.free_space) OVER() ) AS total_libre
FROM    pg_class c
        LEFT OUTER JOIN pg_stat_user_tables st ON (st.relname=c.relname AND st.schemaname=relnamespace::regnamespace::text),
        LATERAL pgstattuple (c.oid) s
WHERE c.relkind IN ( 'r', 'm' ) -- tables ou partitions, vues matérialisées
AND   relnamespace::regnamespace::text  NOT IN ('information_schema','pg_catalog')
-- filtre taille mini de table
AND   pg_relation_size (c.oid) > 1e6
-- filtres personnels
--AND relnamespace::regnamespace::text  IN ('nom_schéma')
--AND relname::text LIKE 'nom_table'
ORDER BY
-- taille table décroissante
pg_relation_size (c.oid) DESC ;

DROP EXTENSION pgstattuple ; -- optionnel

Exemple de sortie :

 schema |  relname   | taille  | nb_lignes_utiles_Ml | nb_lignes_mortes |   dernier_vacuum    | espace_libre | espace_libre_pc | total_libre 
--------+------------+---------+---------------------+------------------+---------------------+--------------+-----------------+-------------
 public | fragmentee | 35 MB   |                 0.1 |                0 | 2024-07-03 02:54:39 | 28 MB        |            79.8 | 28 MB
 public | grande     | 35 MB   |                 1.0 |                0 | 2023-11-03 11:43:11 | 123 kB       |            0.35 | 28 MB
 public | foo        | 35 MB   |                 1.0 |                0 | 2023-12-19 06:37:35 | 123 kB       |            0.35 | 28 MB
 public | t1         | 35 MB   |                 1.0 |                0 | 2023-12-28 05:56:47 | 123 kB       |            0.35 | 28 MB
 public | vari       | 25 MB   |                 0.5 |                0 | 2023-12-27 03:57:46 | 2340 bytes   |            0.01 | 28 MB
 public | varnum     | 25 MB   |                 0.5 |                0 | 2023-12-27 03:57:45 | 2340 bytes   |            0.01 | 28 MB
 public | varbi      | 25 MB   |                 0.5 |                0 | 2023-12-27 03:26:33 | 2340 bytes   |            0.01 | 28 MB
 public | bar        | 13 MB   |                 0.3 |                0 | 2023-12-19 06:37:36 | 45 kB        |            0.34 | 28 MB
 public | vard       | 5096 kB |                 0.1 |                0 | 2023-12

-27 03:33:39 | 468 bytes    |            0.01 | 28 MB
 public | varts      | 5096 kB |                 0.1 |                0 | 2023-12-27 03:33:39 | 468 bytes    |            0.01 | 28 MB

Pour les index B-tree :

-- pgstattuple pour toutes tables et index à partir d'une certaine taille

CREATE EXTENSION IF NOT EXISTS pgstattuple ;

SET lock_timeout TO '2s';

SELECT  c.relnamespace::regnamespace::text AS schema,
        i.indrelid::regclass AS table,
        c.relname AS index,
        pg_size_pretty(pg_relation_size (c.oid)) AS taille,
        round(s.tuple_count::numeric/1.0e6,1) AS "nb_lignes_utiles_Ml",
        s.dead_tuple_count AS nb_lignes_mortes,
        to_char(greatest(last_vacuum, last_autovacuum),'YYYY-MM-DD HH:MI:SS') AS dernier_vacuum,
        pg_size_pretty(s.free_space) AS espace_libre,
        s.free_percent AS espace_libre_pc,
        si.avg_leaf_density,
        si.leaf_fragmentation,
        pg_size_pretty(SUM(s.free_space) OVER() ) AS total_libre
FROM    pg_class c
        INNER JOIN pg_index i ON (c.oid=i.indexrelid)
        INNER JOIN pg_am a ON (a.oid=c.relam)
        LEFT OUTER JOIN pg_stat_user_tables st ON (st.relname=c.relname AND st.schemaname=relnamespace::regnamespace::text)
        , LATERAL pgstattuple (c.oid) s
        , LATERAL pgstatindex (c.oid) si
WHERE c.relkind IN ('i') AND a.amname = 'btree'
AND   relnamespace::regnamespace::text  NOT IN ('information_schema','pg_catalog')
-- filtre taille mini d'index
AND     pg_relation_size (c.oid) > 1e6
-- filtres personnels
--AND relnamespace::regnamespace::text  IN ('nom_schéma')
--AND relname::text LIKE 'nom_table'
ORDER BY
-- taille table décroissante
pg_relation_size (c.oid) DESC ;

DROP EXTENSION pgstattuple ; -- optionnel

Note

Pour les tables, il existe aussi une version pgstattuple_approx plus rapide.

Référence

• Documentation officielle
• Mécanique du moteur transactionnel (formation DBA2).
• Pour le page pruning : PostgreSQL 14 internals, chap. 5 (Egor Rogov)