Linux Pratique - Maintenance d’une instance PostgreSQL

Reviers, 3 mars 2025

Une fois un serveur PostgreSQL installé, plusieurs thématiques sont à prendre en considération : la sauvegarde, la supervision et la maintenance. C’est ce dernier point que nous allons voir dans cet article.

Il existe principalement deux types de maintenance à réaliser sur un serveur PostgreSQL. Ils visent uniquement à préserver de bonnes performances du système de bases de données. Il est question de lutter contre une fragmentation trop importante des tables et des index (pour éviter une surconsommation des accès disques et mémoires), et il est question de s’assurer d’avoir de bonnes statistiques sur les données (pour avoir de bons plans d’exécution, et ainsi de bonnes performances des requêtes). Cet article commence par expliquer ces types de maintenance, puis aborde la question de sa mise en place et de son automatisation.

Mise à jour des statistiques sur les données

C’est certainement le point le moins problématique et le plus compréhensible.

Pour créer un plan d’exécution, l’optimiseur de requêtes de PostgreSQL se base notamment sur des statistiques sur les données. Par exemple, pour choisir entre un parcours de table et un parcours d’index, le ratio de valeurs filtrées est une information importante. Plus ce ratio sera faible (c.-à-d. grand nombre de lignes filtrées, peu de lignes renvoyées), plus un index sera intéressant pour les performances. Mais pour avoir cette information du ratio, il faut avoir une idée ou un résumé des données contenues dans chaque colonne. Pour cela, PostgreSQL peut calculer, enregistrer et utiliser des statistiques sur ces données. La commande ANALYZE a pour responsabilité de générer ces statistiques.

Pour le calcul des statistiques, un échantillon des lignes de la table est pris en compte. Cet échantillon (en nombre de lignes) correspond au résultat de la multiplication de la valeur du paramètre default_statistics_target avec une valeur en dur (300). Ce paramètre vaut 100 par défaut, l’échantillon par défaut est donc de 30000 lignes. Ces lignes sont prises au hasard, chacune dans un bloc différent. Donc ANALYZE lit par défaut 30000 blocs pour chaque table (tout du moins pour les tables qui ont au moins 30000 blocs).

Pour chacune de ces lignes, la commande calcule les statistiques de chaque colonne de la table, et les enregistre dans le catalogue système pg_statistic. Depuis la version 10, il peut aussi calculer des statistiques sur le contenu de plusieurs colonnes. Nous parlons alors de statistiques étendues, enregistrées dans les catalogues systèmes pg_statistic_ext et pg_statistic_ext_data. Il est possible de regarder le contenu de ces catalogues, mais c’est difficilement compréhensible pour un humain. Il est préférable de passer par les vues pg_stats et pg_stats_ext, qui ont été spécialement créées pour être plus facilement appréhendables.

Revenons à l’échantillon. Plus il est grand, plus les statistiques seront précises. Cependant, plus il est grand, plus l’opération d’ANALYZE sera longue. Et cela peut paraître contre-intuitif, mais avoir plus de statistiques va demander plus de travail à l’optimiseur qui mettra donc plus de temps à proposer un plan d’exécution. Donc un échantillon plus grand n’est pas automatiquement une bonne chose. Il faut faire la balance entre les gains sur les plans générés et les pertes dues au calcul et à l’optimisation plus longue.

Même si la configuration basique est globale sur toutes les tables, l’échantillon n’a en fait aucun intérêt d’être le même pour toutes les tables. Chaque colonne de chaque table peut avoir une distribution spécifique des données, et il pourrait donc être préférable de spécifier cette configuration par colonne, voire par table. Même s’il n’est actuellement pas possible de la spécifier par table, il est possible de le faire par colonne avec la commande ALTER TABLE. Par exemple :

ALTER TABLE t1 ALTER COLUMN c1 SET STATISTICS 200;

Les statistiques sont calculées à l’exécution de la commande ANALYZE. Au fur et à mesure des écritures dans la base, les statistiques vont devenir obsolètes et il sera nécessaire de les calculer à nouveau. Autrement dit, il est essentiel d’exécuter périodiquement cette commande. La fréquence de son exécution dépend principalement de la fréquence des requêtes d’écriture de données (INSERT, COPY, UPDATE, DELETE) ou plus exactement du nombre de lignes impactées par ces requêtes.

Lutte contre la fragmentation

Au fil de l’utilisation d’une base de données de PostgreSQL, les tables deviennent de plus en plus volumineuses si aucune opération de maintenance n’est réalisée. En effet, lors d’opérations de mise à jour ou de suppression de lignes dans une table, les lignes concernées ne sont pas supprimées directement du fichier correspondant à cette table. Cela ralentirait beaucoup les écritures si la suppression physique était immédiate. De plus, les lignes sont conservées pour la session qui les a supprimées au cas où celle-ci devrait annuler la suppression. Elles sont aussi conservées pour le cas où d’autres sessions exécuteraient des lectures de la même table tant que la session qui supprime ces lignes n’a pas validé sa transaction. Même si cette transaction est validée, les lignes sont conservées au cas où les autres sessions utilisent un niveau transactionnel qui leur permet toujours de voir ces anciennes lignes. Bref, il existe plein de cas où ces lignes mises à jour ou supprimées doivent rester accessibles à certaines sessions.

Entrons un peu plus dans le détail des différentes opérations d’écriture. En cas d’insertion (INSERT ou COPY), soit il existe un emplacement de libre dans un bloc de fichier, auquel cas la nouvelle ligne est enregistrée à cet emplacement, soit il n’en existe pas de libre, ce qui cause l’ajout d’un nouveau bloc, et l’enregistrement de la nouvelle ligne dans ce nouveau bloc. L’index est modifié pour référencer la nouvelle ligne.

En cas de suppression (DELETE), la ligne ciblée est indiquée comme supprimée dans le fichier de la table. L’index n’est pas touché et référence donc toujours l’ancienne ligne.

En cas de mise à jour (UPDATE), PostgreSQL ne met pas à jour la ligne ciblée. Il fait l’équivalent d’un Copy-On-Write, autrement dit une copie de la ligne est effectuée dans le fichier de la table, la copie contient la nouvelle (version de cette) ligne avec les données modifiées, l’ancienne (version de cette) ligne est toujours présente avec les anciennes données. Comme indiqué entre parenthèses, nous parlons plutôt de version de ligne, l’ancienne étant la version originale, la nouvelle étant la version modifiée. Cette nouvelle version est enregistrée comme toute nouvelle ligne. Donc, avec PostgreSQL, un UPDATE est l’exact équivalent d’un DELETE de la ligne actuelle, suivi d’un INSERT de la ligne avec les données modifiées. De ce fait, l’index se voit ajouter (généralement) une nouvelle référence à cette ligne, mais avec un pointeur indiquant l’emplacement de la nouvelle ligne, tout en conservant le pointeur vers l’ancienne ligne.

J’ai indiqué « l’index ». C’est évidemment à condition qu’il y ait un index sur cette table, et s’il y en a plusieurs, tous sont pris en compte, à quelques subtilités près (index partiel, mise à jour HOT).

Nous nous retrouvons donc avec des lignes déclarées comme supprimées dans le fichier, mais bien physiquement présentes dans le fichier. Pendant un moment, elles restent visibles par certaines transactions. Cependant, au bout d’un moment, toutes les transactions qui voyaient encore ces lignes se terminent. À ce moment-là, aucune transaction ne peut voir ces lignes supprimées. Elles sont pourtant toujours présentes dans le fichier de la table et il existe toujours des références dans l’index qui pointent vers ces lignes. Pour ne pas perdre du temps à chaque opération d’écriture dans la table, les développeurs de PostgreSQL ont décidé qu’il faudrait exécuter une opération spécifique pour trouver les lignes supprimées visibles par aucune session et les marquer d’une façon particulière indiquant qu’il est possible de réutiliser l’espace qu’elles occupent. Cette opération s’exécute en utilisant l’instruction VACUUM.

Cette opération réalise trois étapes :

• récupération de la liste des lignes actuellement invisibles par toutes les transactions en cours ;
• suppression des références de ces lignes dans les index de la table ;
• mise à jour du fichier FSM indiquant les espaces libres réutilisables.

Ces trois étapes peuvent être exécutées plusieurs fois s’il n’est pas possible de conserver en mémoire l’ensemble des lignes invisibles. La mémoire utilisée pour cela dépend de la valeur du paramètre maintenance_work_mem, sachant que l’opération VACUUM ne pourra pas utiliser plus de 1 Go (ce qui représente quand même 178 millions de lignes mortes).

Une fois cette opération effectuée, un fichier FSM est présent sur disque. Tout nouvel ajout de ligne (suite à un INSERT, COPY ou UPDATE) lira ce fichier pour trouver où placer la nouvelle ligne. Ceci permet d’utiliser les espaces rendus disponibles et évite de faire grossir le fichier de la table.

Dans la majorité des cas, le nettoyage de la table et des index ne permet pas aux fichiers de ces objets de perdre en volumétrie. Il permet principalement de renseigner la structure FSM pour savoir où écrire sans faire grossir le fichier. Cependant, si un ou plusieurs blocs en toute fin de fichier sont complètement libérés de leurs lignes et qu’il est possible d’obtenir rapidement un verrou exclusif sur la table, l’opération VACUUM pourra tronquer le fichier pour rendre ces blocs au système de fichiers. De mon expérience, cela est suffisamment peu fréquent pour être visible.

Pour ce qui est de l’utilisation de l’instruction VACUUM, celle-ci peut viser spécifiquement une table si son nom est indiqué. Par exemple, VACUUM t1 permettra de traiter la table t1. Si aucune table n’est indiquée, toutes les tables de la base seront traitées.

L’instruction VACUUM accepte plusieurs options. Vu le nombre (14 !), nous n’allons pas les citer toutes ici. La plus fréquemment utilisée est l’option ANALYZE, qui permet de profiter des lectures de la table pour en plus mettre à jour les statistiques sur les données, à l’image de l’instruction ANALYZE.

L’option PARALLEL permet de paralléliser le traitement des index d’une table sur plusieurs CPU, avec un CPU par index. C’est particulièrement intéressant pour les tables volumineuses dotées de nombreux index.

Mais l’option la plus fréquemment évoquée est l’option FULL. Cette option change complètement le travail de la commande VACUUM, et on peut se questionner sur le bien-fondé de ne pas avoir créé une instruction spécifique pour cette opération. En utilisant l’option FULL, nous forçons la réécriture complète de la table et de ses index, ce qui aura pour conséquence une diminution sur disque de la volumétrie des fichiers associés à ces objets. Cette opération a cependant deux gros inconvénients : les objets sont totalement verrouillés pendant cette opération (donc l’écriture et la lecture par d’autres processus sont bloqués le temps du traitement), et il est essentiel d’avoir la place nécessaire pour les nouveaux fichiers le temps du traitement (l’estimation de la place nécessaire est très basique : comptez exactement la même volumétrie que la table et ses index occupent avant l’opération).

Une autre opération fait à peu près la même chose : CLUSTER. La seule différence avec VACUUM FULL est que CLUSTER trie en plus les données avant de les stocker dans le nouveau fichier de table. Le tri se fait par rapport au tri d’un index. Il faut donc indiquer à l’instruction la table à traiter et l’index à suivre.

Voilà pour ce qui est des tables, mais les index peuvent aussi se fragmenter. Ils sont toujours correctement balancés, mais les blocs du fichier de l’index peuvent ne pas être remplis. Dans ce cas, il convient de les réindexer. L’opération est aussi lente que leur création, elle est aussi bloquante (pas de lecture de l’index, pas d’écriture dans la table associée). Cette opération utilise l’instruction REINDEX. Il est possible de réindexer un seul index (REINDEX INDEX i1;), tous les index d’une table (REINDEX TABLE t1;), tous les index d’un schéma (REINDEX SCHEMA s1;), tous les index d’une base (REINDEX;).

Cette instruction accepte deux options : CONCURRENTLY (pour éviter le verrou sur les écritures dans la table, ce qui a pour conséquence une opération plus longue et le risque d’obtenir au final un index invalide) et TABLESPACE (pour placer le nouveau fichier de l’index dans un autre tablespace).

Comment savoir qu’un objet est fragmenté

Il existe deux moyens. Les deux sont à utiliser.

Le premier est une estimation. Il permet d’avoir rapidement une idée de la fragmentation. Cette estimation se base notamment sur les statistiques sur les données et elle sera d’autant plus fiable que ces statistiques sont récentes. Deux groupes de requêtes sont disponibles, le premier pour les tables, le second pour les index Btree. Les autres méthodes d’indexation ne sont donc pas couvertes, cependant le Btree est la méthode par défaut et la plus couramment utilisée. Ces requêtes sont disponibles dans le dépôt GitHub pgsql-bloat-estimation. Généralement, je place ces requêtes dans des vues pour une utilisation facilitée. Cela me donnerait par exemple cette requête pour trouver les 20 tables les plus fragmentées :

SELECT quote_ident(schemaname)||'.'||quote_ident(tblname) AS table,
       real_size AS taille,
       bloat_size AS taille_fragmentation,
       round(bloat_pct::numeric, 2) AS ratio_fragmentation
FROM v_table_bloat
ORDER BY bloat_size DESC
LIMIT 20;

Le second est une extension (pgstattuple) qui donnera une information exacte. Cependant, pour donner une information exacte, les fonctions qu’elle propose doivent parcourir les objets entiers, ce qui se révèle très lent par rapport aux requêtes citées précédemment. L’extension a été mise à jour récemment pour proposer une fonction d’approximation qui se base, elle, sur le contenu de la Visibility Map et sur le contenu de la Free Space Map. Voici un exemple de requête utilisant la fonction exacte pour récupérer là aussi les 20 tables les plus fragmentées :

SELECT quote_ident(n.nspname)||'.'||quote_ident(c.relname) AS table,
       s.table_len AS taille,
       s.dead_tuple_len AS taille_invisibles,
       s.free_space AS taille_libre
FROM pg_class c
  JOIN pg_namespace n ON n.oid=c.relnamespace,
  LATERAL pgstattuple(c.oid) s
WHERE c.relkind='r'
ORDER BY s.dead_tuple_len DESC
LIMIT 20;

et celle utilisant la fonction d’approximation :

SELECT quote_ident(n.nspname)||'.'||quote_ident(c.relname) AS table,
       s.table_len AS taille,
       s.dead_tuple_len AS taille_invisibles,
       s.approx_free_space AS taille_libre
FROM pg_class c
  JOIN pg_namespace n ON n.oid=c.relnamespace,
  LATERAL pgstattuple_approx(c.oid) s
WHERE c.relkind='r'
ORDER BY s.dead_tuple_len DESC
LIMIT 20;

Cette extension dispose aussi de fonctions pour tester les index des méthodes d’accès Btree, GIN et Hash.

Pour revenir au début de ce chapitre, je disais qu’il fallait utiliser les deux, sans expliquer pourquoi. Les fonctions exactes sont trop longues si vous voulez les exécuter fréquemment sur des bases de grosse volumétrie. Une stratégie intéressante est d’utiliser les vues ou fonctions d’estimation pour avoir une idée des objets les plus fragmentés et d’utiliser les fonctions exactes uniquement sur les tables suffisamment fragmentées pour valider ou non les informations des estimations. Cela permet de connaître les tables à traiter réellement.

En ce qui concerne la supervision, il peut être intéressant de suivre la volumétrie de la base et sa fragmentation. Cela peut se faire avec la requête suivante :

SELECT 'volumétrie de la base' AS "Type", pg_size_pretty(pg_database_size(current_database())) AS "Size"
UNION
SELECT 'volumétrie de la fragmentation des tables', pg_size_pretty(sum(bloat_size)::numeric) FROM v_table_bloat
UNION
SELECT 'volumétrie de la fragmentation des index', pg_size_pretty(sum(bloat_size)::numeric) FROM v_index_bloat;

Ceci n’est qu’un exemple, qui sera facile à adapter le cas échéant.

Comment exécuter les opérations de maintenance

Une exécution manuelle est possible, mais ne convient qu’en de rares cas.

Il est préférable d’automatiser ces opérations. Les développeurs de PostgreSQL en ont automatisé certaines en développant un sous-processus appelé autovacuum. Ce sous-processus va traiter chacune des bases de l’instance et déclenchera si besoin des opérations VACUUM et ANALYZE. Ce besoin est détecté en se basant sur le nombre de lignes insérées, modifiées et supprimées pour l’ANALYZE et sur le nombre de lignes mortes pour le VACUUM. Ces informations font partie des statistiques d’activité récupérées en temps réel par PostgreSQL. Une configuration permet d’indiquer à partir de quel ratio de lignes nous souhaitons voir ces opérations déclenchées. Ces ratios sont par défaut très haut, ce qui fait que peu d’opérations sont déclenchées, mais il est possible de les diminuer globalement ou table par table si vos bases subissent beaucoup d’écriture.

Les paramètres les plus importants de ce sous-processus sont :

• autovacuum_analyze_scale_factor, ratio de lignes écrites avant de lancer un ANALYZE ;
• autovacuum_analyze_threshold, nombre minimum de lignes écrites avant de lancer un ANALYZE ;
• autovacuum_vacuum_scale_factor, ratio de lignes mortes avant de lancer un VACUUM ;
• autovacuum_vacuum_threshold, nombre minimum de lignes mortes avant de lancer un VACUUM ;
• autovacuum_vacuum_insert_scale_factor, ratio de lignes insérées avant de lancer un VACUUM ;
• autovacuum_vacuum_insert_threshold, ratio de lignes insérées avant de lancer un VACUUM.

Le sous-processus autovacuum calcule donc la valeur cible en additionnant le nombre minimum et le ratio multiplié par le nombre de lignes, et compare cette valeur cible au nombre de lignes mortes pour le cas d’un VACUUM. Si le nombre de lignes mortes dépasse la valeur cible, l’autovacuum lance un VACUUM sur la table concernée. Par défaut, le ratio est de 20 %. Sur une petite table, ce n’est pas énorme. Sur une table volumineuse par contre, le VACUUM ne sera lancé que très peu fréquemment.

Pour le dire autrement, pour une table de 100 Go, il faudra attendre 20 Go de fragmentation pour exécuter un VACUUM. Ce n’est pas bon, il aurait fallu le lancer bien avant. C’est pour cela qu’il est généralement conseillé de descendre ce ratio et de le faire table par table pour prendre en compte leur utilisation, leur fragmentation actuelle. Il est même parfois intéressant de placer à zéro le ratio et de ne prendre en compte que le nombre minimum de lignes, pour se baser sur un volume fixe de fragmentation.

Cependant, une configuration aussi détaillée demande du temps et des connaissances. Le plus simple, dans un premier temps, est certainement de diminuer les valeurs par défaut de manière globale. Il est fréquemment conseillé de diviser les ratios pour 10.

Il est à noter que l’autovacuum ne fait ni VACUUM FULL, ni REINDEX. La raison en est que ces opérations nécessitent un verrou exclusif qui est bloquant pour les autres sessions en cours d’exécution. Cela va donc générer de fortes contentions à des moments généralement très actifs. Ces deux opérations sont plutôt à lancer manuellement, quand le besoin s’en fait sentir ou quand il est prévu une fenêtre de maintenance où il n’y aura pas d’autres accès à la base.

Dans les autres paramètres intéressants, notons autovacuum_work_mem. Ce dernier a le même but que le paramètre maintenance_work_mem, mais ne concerne que le processus autovacuum. Ainsi, il est possible d’avoir une configuration pour ce sous-processus et une configuration pour les VACUUM manuels.

En conclusion

autovacuum est votre (meilleur) ami. Certains le désactivent parce qu’il consommerait beaucoup de ressources. Sans nier qu’il consomme effectivement des ressources, il permet surtout d’en sauver grâce à des statistiques à jour et en luttant contre le grossissement sans fin des tables. Dans la très grande majorité des cas, le désactiver est une erreur.

Si vous préférez passer par une exécution via cron, pourquoi pas, mais il faut bien comprendre ce que l’on fait et pourquoi.

Dans tous les cas, comme l’a dit Robert Haas, un des principaux développeurs de PostgreSQL, lors d’une conférence à Prague au PGConf.EU 2024 : “Vacuuming is like exercising. If it hurts, you’re not doing it enough!” (« Le VACUUM, c’est comme faire du sport. Si cela fait mal, c’est que vous n’en faites pas assez ! »).

Depuis quelques années, Guillaume Lelarge publie des articles dans le magazine « Linux Pratique » édité par les Éditions Diamond. Avec leur accord, il reprend ici une série destinée à guider l’installation, la maintenance et l’utilisation de PostgreSQL.