# VACUUM et autovacuum > Nettoyage, fragmentation, autovacuum, FREEZE, suivi et travaux pratiques. --- Index LLMS : [llms.txt](/fr/llms.txt) --- ## 5/ VACUUM et autovacuum {#dba2-ch5} **Figure p245-1.** Logo PostgreSQL\. ![Logo PostgreSQL](/images/dba2/manual/p245-01.png) ### 5.1 AU MENU - Principe & fonctionnement du `VACUUM` - Options: `VACUUM` seul, `ANALYZE`, `FULL`, `FREEZE` - ne pas les confondre! - Suivi - Autovacuum - Paramétrages VACUUM est la contrepartie de la flexibilité du modèle MVCC. Derrière les différentes options de `VACUUM` se cachent plusieurs tâches très différentes. Malheureusement, la confusion est facile. Il est capital de les connaître et de comprendre leur fonctionnement. Autovacuum permet d’automatiser le VACUUM et allège considérablement le travail de l’administrateur. Il fonctionne généralement bien, mais il faut savoir le surveiller et l’optimiser. ### 5.2 VACUUM #### 5.2.1 VACUUM: nettoyage des lignes & lancement - `VACUUM` - nettoie d’abord les lignes mortes - mais aussi d’autres opérations de maintenance - Lancement: - manuel par `VACUUM` (SQL) - manuel par `vacuumdb` (shell, pour appels en masse) - par le démon `autovacuum` (seuils) `VACUUM` est né du besoin de nettoyer les lignes mortes. Au fil du temps, il a été couplé à d’autres ordres (`ANALYZE`, `VACUUM FREEZE`) et s’est occupé d’autres opérations de maintenance (création de la _visibility map_ par exemple). Des options permettent de réguler son activité. Son paramétrage n’est donc pas toujours très clair. `autovacuum` est un processus de l’instance PostgreSQL. Il est activé par défaut, et il est fortement conseillé de le conserver ainsi. Dans le cas général, son fonctionnement convient et il ne gênera pas les utilisateurs. Au contraire, il faudra parfois le rendre plus agressif. L’autovacuum ne gère pas toutes les variantes de `VACUUM` (notamment pas le `FULL`). #### 5.2.2 VACUUM ou ANALYZE en ligne de commande: vacuumdb - Nombreuses options: - `--all`: toutes les bases - `--jobs=…`: connexions simultanées - `--schema=… --exclude-schema` - `--echo --verbose` - Nettoyage: - `--[no|force]-index-cleanup` - Statistiques: `--analyze[-only]` - `--missing-stats-only` (v18) - Exceptionnellement: - `--full`, `--freeze` L’outil `vacuumdb` est un outil qui génère des ordres `VACUUM` après s’être connecté à PostgreSQL. Il se connecte comme n’importe quel client avec les options habituelles, depuis le serveur ou ailleurs. Il reprend les options de `VACUUM` (voir sa page de manuel[^1]). Il est plutôt destiné aux appels depuis le système ou comme tâche planifiée. Par rapport à un ordre `VACUUM` en SQL, `vacuumdb` facilite les appels en masse, avec notamment ces options: - `--all` pour nettoyer toutes les bases de données les unes après les autres; - `--jobs` pour paralléliser sur plusieurs sessions connectées; - `--parallel` pour paralléliser le nettoyage des index d’une table sur plusieurs processus (outrepasse le paramètre `max_parallel_maintenance_workers`, soit 2 par défaut); - des options pour ne traiter que certains objets ou en ignorer (`--table=…`, `--schema=…`, `--exclude-schema=…`). Les options `--echo` et `--verbose` sont conseillées pour voir et suivre ce qui est exécuté. Pour nettoyer un schéma par exemple: **Exemple p248-1.** Exemple extrait du PDF\, page 248\. ```shell vacuumdb -p5435 --echo -d pgbench ``` **Exemple p248-2.** Exemple extrait du PDF\, page 248\. ```sql SELECT pg_catalog.set_config('search_path', '', false); vacuumdb : exécution de VACUUM sur la base de données « pgbench » RESET search_path; ``` **Exemple p248-3.** Exemple extrait du PDF\, page 248\. ```sql SELECT pg_catalog.set_config('search_path', '', false); VACUUM (SKIP_DATABASE_STATS) public.pgbench_accounts; VACUUM (SKIP_DATABASE_STATS) public.pgbench_branches; VACUUM (SKIP_DATABASE_STATS) public.pgbench_tellers; VACUUM (SKIP_DATABASE_STATS) public.pgbench_history; ``` **Exemple p248-4.** Exemple extrait du PDF\, page 248\. ```sql VACUUM (SKIP_DATABASE_STATS) pg_catalog.pg_subscription; VACUUM (ONLY_DATABASE_STATS); ``` Par défaut, `vacuumdb` procède au nettoyage de toutes les tables de la base (`VACUUM`). Avec `--analyze` (`-z`), il procède aussi à l’ `ANALYZE`. Avec `--analyze-only`, il ne fait même que cela (il n’y a pas d’outil en ligne de commande dédié). `--analyze-in-stages` fait de même par étape, `--analyze-in-stages` fait de même en plusieurs passes avec une augmentation progressive de l’échantillonnage, l’objectif étant de produire rapidement des statistiques que l’on affine ensuite. L’option ne sert pratiquement que lors des migrations. À partir de PostgreSQL 18, `--missing-stats-only` est très pratique pour calculer uniquement les statistiques manquantes (nouvelles tables ou après une migration). `--full` et `--freeze` correspondent aux ordres `VACUUM FULL` et `VACUUM FREEZE` et sont utilisés exceptionnellement. D’autres options utiles sont: - `--no-index-cleanup` pour éviter le nettoyage des index (souvent long) si l’on est pressé, ou `--force-index-cleanup` pour le forcer; [^1]: https://docs.postgresql.fr/current/app-vacuumdb.html - `--buffer-usage-limit` améliore les performances en augmentant la quantité de mémoire allouable (voir `vacuum_buffer_usage_limit` plus bas; à partir de PostgreSQL 16); - `--dry-run` n’affiche que les ordres `VACUUM` et `ANALYZE` sans les lancer (pratique pour créer un script); #### 5.2.3 Fonctionnement de VACUUM **Figure 5.1.** Phase 1\/3\: recherche des enregistrements morts\. ![Phase 1\/3\: recherche des enregistrements morts](/images/dba2/manual/p249-figure-5-1.png) Un ordre `VACUUM` vise d’abord à nettoyer les lignes mortes. Le traitement `VACUUM` se déroule en trois passes. Cette première passe parcourt la table à nettoyer, à la recherche d’enregistrements morts. Un enregistrement est mort s’il possède un `xmax` qui correspond à une transaction validée, et que cet enregistrement n’est plus visible dans l’instantané d’aucune transaction en cours sur la base. D’autres lignes mortes portent un `xmin` d’une transaction annulée. L’enregistrement mort ne peut pas être supprimé immédiatement: des enregistrements d’index pointent vers lui et doivent aussi être nettoyés. La session effectuant le `VACUUM` garde en mémoire la liste des adresses des enregistrements morts, à hauteur d’une quantité indiquée par le paramètre `maintenance_work_mem`. Si cet espace est trop petit pour contenir tous les enregistrements morts, `VACUUM` effectue plusieurs séries de ces trois passes. #### 5.2.4 Fonctionnement de VACUUM (suite) **Figure 5.2.** Phase 2\/3\: nettoyage des index\. ![Phase 2\/3\: nettoyage des index](/images/dba2/manual/p250-figure-5-2.png) La seconde passe se charge de nettoyer les entrées d’index. `VACUUM` possède une liste de `tid` (`tuple id`) à invalider. Il parcourt donc toutes les pages de tous les index de la table à la recherche de ces `tid` et les supprime. En effet, les index sont triés afin de mettre en correspondance une valeur de clé (la colonne indexée par exemple) avec un `tid`. Il n’est par contre pas possible de trouver un `tid` directement. Les pages entièrement vides sont supprimées de l’arbre et stockées dans la liste des pages réutilisables, la _Free Space Map_ (FSM). Cette phase peut être longue sur les grosses tables, plus que le nettoyage de la table même. Elle peut être parallélisée avec la clause `PARALLEL`, chaque index pouvant être traité par un _worker_ fonctionnant sur un CPU. Un index peut être ignoré s’il n’y a pas grand-chose à nettoyer. Qwe - Si le temps presse, le nettoyage des index peut être désactivé avec l’option `INDEX_CLEANUP off` (voir plus loin): **Exemple p250-1.** Exemple extrait du PDF\, page 250\. ```sql VACUUM (VERBOSE, INDEX_CLEANUP off) nom_table ; ``` #### 5.2.5 Fonctionnement de VACUUM (suite) **Figure 5.3.** Phase 3\/3\: suppression des enregistrements morts\. ![Phase 3\/3\: suppression des enregistrements morts](/images/dba2/manual/p251-figure-5-3.png) <1 | NB: L’espace est rarement rendu à l’OS! Maintenant qu’il n’y a plus d’entrée d’index pointant sur les enregistrements morts identifiés, ceux-ci peuvent disparaître. C’est le rôle de cette passe. Quand un enregistrement est supprimé d’un bloc, ce bloc est complètement réorganisé afin de consolider l’espace libre. Cet espace est renseigné dans la _Free Space Map_ (FSM). Une fois cette passe terminée, si le parcours de la table n’a pas été terminé lors de la passe précédente, le travail reprend où il en était du parcours de la table. Si les derniers blocs de la table sont vides, ils sont rendus au système (si le verrou nécessaire peut être obtenu, et si l’option `TRUNCATE` n’est pas `off`). C’est le seul cas où `VACUUM` réduit la taille de la table. Les espaces vides (et réutilisables) au milieu de la table constituent le _bloat_ (littéralement « boursouflure » ou « gonflement », que l’on peut aussi traduire par fragmentation). Les statistiques d’activité sont aussi mises à jour. ### 5.3 LES OPTIONS DE VACUUM - Quelles tâches? - Comment améliorer les performances? - Quelles options en cas d’urgence? - Autres options #### 5.3.1 Tâches d’un VACUUM Ne pas confondre: - `VACUUM` seul - nettoyage des lignes mortes, _visibility map_, _hint bits_ - `ANALYZE` - statistiques sur les données - `VACUUM (ANALYZE)` - nettoyage & statistiques - `VACUUM (FREEZE)` - gel des lignes (parfois gênant ou long) - `VACUUM FULL` - bloquant! - jamais lancé par l’autovacuum **VACUUM** Par défaut, `VACUUM` procède principalement au nettoyage des lignes mortes. Pour que cela soit efficace, il met à jour la _visibility map_, et la crée au besoin. Au passage, il peut geler certaines lignes rencontrées. L’autovacuum le déclenchera sur les tables en fonction de l’activité. Le verrou `SHARE UPDATE EXCLUSIVE` posé protège la table contre les modifications simultanées du schéma, et ne gêne généralement pas les opérations, sauf les plus intrusives (il empêche par exemple un `LOCK TABLE`). L’autovacuum arrêtera spontanément un `VACUUM` qu’il aurait lancé et qui gênerait; mais un `VACUUM` lancé manuellement continuera jusqu’à la fin. **VACUUM ANALYZE** `ANALYZE` existe en tant qu’ordre séparé, pour rafraîchir les statistiques sur un échantillon des données, à destination de l’optimiseur. L’autovacuum se charge également de lancer des `ANALYZE` en fonction de l’activité. L’ordre `VACUUM ANALYZE` (ou `VACUUM (ANALYZE)`) force le calcul des statistiques sur les données en même temps que le `VACUUM`. **VACUUM FREEZE** `VACUUM FREEZE` procède au « gel » des lignes visibles par toutes les transactions en cours sur l’instance, afin de parer au problème du _wraparound_ des identifiants de transaction. Un ordre `FREEZE` n’existe pas en tant que tel. Préventivement, lors d’un `VACUUM` simple, l’autovacuum procède au gel de certaines des lignes rencontrées. De plus, il lancera un `VACUUM FREEZE` sur une table dont les plus vieilles transactions dépassent un certain âge. Ce peut être très long, et très lourd en écritures si une grosse table doit être entièrement gelée d’un coup. Autrement, l’activité n’est qu’exceptionnellement gênée (voir plus bas). L’opération de gel sera détaillée plus loin. **VACUUM FULL** L’ordre `VACUUM FULL` permet de reconstruire la table sans les espaces vides. C’est une opération très lourde, risquant de bloquer d’autres requêtes à cause du verrou exclusif qu’elle pose (on ne peut même plus lire la table!), mais il s’agit de la seule option qui permet de réduire la taille de la table au niveau du système de fichiers de façon certaine. Il faut prévoir l’espace disque (la table est reconstruite à côté de l’ancienne, puis l’ancienne est supprimée). Les index sont reconstruits au passage. Un `VACUUM FULL` gèle agressivement les lignes, et effectue donc au passage l’équivalent d’un `FREEZE`. L’autovacuum ne lancera jamais un `VACUUM FULL`! Il existe aussi un ordre `CLUSTER`, qui permet en plus de trier la table suivant un des index. #### 5.3.2 Options de performance de VACUUM - Index: - `PARALLEL` (défaut: 2) - Taille du _buffer ring_ (v16+) - `VACUUM (BUFFER_USAGE_LIMIT 2MB)` - paramètre `vacuum_buffer_usage_limit` - 256 ko ou 2 Mo par défaut, à monter - `SKIP_DATABASE_STATS`, `ONLY_DATABASE_STATS` (v16+) - Éviter les verrous - `SKIP_LOCKED` - `SET lock_timeout = '1s'` #### PARALLEL: L’option `PARALLEL` permet le traitement parallélisé des index. Le nombre indiqué après `PARALLEL` précise le niveau de parallélisation souhaité. Par exemple: **Exemple p254-1.** Exemple extrait du PDF\, page 254\. ```sql VACUUM (VERBOSE, PARALLEL 4) matable ; ``` **Exemple p254-2.** Exemple extrait du PDF\, page 254\. ```text INFO: vacuuming "public.matable" INFO: launched 3 parallel vacuum workers for index cleanup (planned: 3) ``` La valeur par défaut (2) découle du paramètre `max_parallel_maintenance_workers`. #### SKIP_DATABASE_STATS, ONLY_DATABASE_STATS: En fin d’exécution d’un `VACUUM`, même sur une seule table, le champ `pg_database.datfrozenxid` est mis à jour. Il contient le numéro de la transaction la plus ancienne non encore gelée dans toute la base de données. Cette opération impose de parcourir `pg_class` pour récupérer le plus ancien des numéros de transaction de chaque table (`relfrozenxid`), Or cette mise à jour n’est utile que pour l’autovacuum et le `VACUUM FREEZE`, et a rarement un caractère d’urgence. Depuis la version 16, l’option `SKIP_DATABASE_STATS` demande au `VACUUM` d’ignorer la mise à jour de l’identifiant de transaction. Le principe est d’activer cette option pour les nettoyages en masse. À l’inverse, l’option `ONLY_DATABASE_STATS` demande de ne faire que la mise à jour du `datfrozenxid`, ce qui peut être fait une seule fois en fin de traitement. L’outil `vacuumdb` procède ainsi automatiquement si le serveur est en version 16 minimum. Par exemple: **Exemple p254-3.** Exemple extrait du PDF\, page 254\. ```console $ vacuumdb --echo --all ``` **Exemple p254-4.** Exemple extrait du PDF\, page 254\. ```sql SELECT pg_catalog.set_config('search_path', '', false); vacuumdb : exécution de VACUUM sur la base de données « pgbench » RESET search_path; ``` **Exemple p254-5.** Exemple extrait du PDF\, page 254\. ```text … SELECTpg_catalog.set_config('search_path','',false); VACUUM(SKIP_DATABASE_STATS)public.pgbench_accounts; VACUUM(SKIP_DATABASE_STATS)pg_catalog.pg_proc; VACUUM(SKIP_DATABASE_STATS)pg_catalog.pg_attribute; VACUUM(SKIP_DATABASE_STATS)pg_catalog.pg_description; VACUUM(SKIP_DATABASE_STATS)pg_catalog.pg_statistic; ``` **Exemple p254-6.** Exemple extrait du PDF\, page 254\. ```sql VACUUM (ONLY_DATABASE_STATS); SELECT pg_catalog.set_config('search_path', '', false); vacuumdb : exécution de VACUUM sur la base de données « postgres » ``` **Exemple p254-7.** Exemple extrait du PDF\, page 254\. ```text … VACUUM(ONLY_DATABASE_STATS); ``` #### BUFFER_USAGE_LIMIT: Cette option apparue en version 16 permet d’augmenter la taille de la mémoire partagée que peuvent utiliser `VACUUM`, et `ANALYZE`. Par défaut, cet espace nommé _buffer ring_ n’est que de 256 ko de mémoire partagée, une valeur assez basse, cela pour protéger le cache (_shared buffers_). Si cette mémoire ne suffit pas, PostgreSQL doit recycler certains de ces buffers, d’où une écriture possible de journaux sur disque, avec un ralentissement à la clé. Monter la taille du _buffer ring_ avec `BUFFER_USAGE_LIMIT` permet une exécution plus rapide de VACUUM, et génère moins de journaux. De manière globale, on peut aussi modifier le paramètre `vacuum_buffer_usage_limit`. Les valeurs vont de 128 ko à 16 Go; 0 désactive le _buffer ring_, il n’y a alors aucune limite en terme d’utilisation du cache. La taille par défaut n’est que de 2 Mo (et même seulement 256 ko jusque PostgreSQL 16 inclus). Les machines modernes permettent de monter ce paramètre à quelques mégaoctets sans problème, pour un gain en vitesse d’écriture très appréciable[^2]. Il peut y avoir un impact négatif sur les autres requêtes si le débit en lecture ou écriture du `VACUUM` augmente trop. La valeur 0 est envisageable dans le cas d’une plage de maintenance où une purge du cache de PostgreSQL n’aurait pas de gros impact. Exemples d’utilisation: **Exemple p255-1.** Exemple extrait du PDF\, page 255\. ```sql ANALYZE (BUFFER_USAGE_LIMIT '8MB'); ``` **Exemple p255-2.** Exemple extrait du PDF\, page 255\. ```sql VACUUM (BUFFER_USAGE_LIMIT '8MB'); ``` **Exemple p255-3.** Exemple extrait du PDF\, page 255\. ```sql VACUUM (ANALYZE, BUFFER_USAGE_LIMIT 0) ; # sans limite ``` **Exemple p255-4.** Exemple extrait du PDF\, page 255\. ```shell vacuumdb --analyze --buffer-usage-limit=8MB --echo -d pgbench ``` **Exemple p255-5.** Exemple extrait du PDF\, page 255\. ```text … VACUUM(SKIP_DATABASE_STATS,ANALYZE,BUFFER_USAGE_LIMIT'8MB') public.pgbench_accounts; ``` #### Les verrous, SKIP_LOCKED et lock_timeout: L’option `SKIP_LOCKED` permet d’ignorer toute table pour laquelle la commande `VACUUM` ne peut pas obtenir immédiatement son verrou. Cela évite de bloquer le `VACUUM` sur une table, et permet d’éviter un empilement des verrous derrière celui que le `VACUUM` veut poser, surtout en cas de `VACUUM FULL`. La commande passe alors à la table suivante à traiter. Exemple: **Exemple p255-6.** Exemple extrait du PDF\, page 255\. ```sql VACUUM (FULL, SKIP_LOCKED) t_un_million_int, t_cent_mille_int ; ``` **Exemple p255-7.** Exemple extrait du PDF\, page 255\. ```text WARNING: skipping vacuum of "t_un_million_int" --- lock not available VACUUM ``` Une technique un peu différente est de paramétrer dans la session un petit délai avant abandon en cas de problème de verrou. Là encore, cela vise à limiter les empilements de verrou sur une base active. Par contre, comme l’ordre tombe immédiatement en erreur après le délai, il est plus adapté aux ordres ponctuels sur une table. **Exemple p255-8.** Exemple extrait du PDF\, page 255\. ```sql SET lock_timeout TO '100ms' ; -- Un LOCK TABLE a été fait dans une autre session VACUUM (verbose) pgbench_history,pgbench_tellers; ``` **Exemple p255-9.** Exemple extrait du PDF\, page 255\. ```text ERROR: canceling statement due to lock timeout Durée : 1000,373 ms (00:01,000) RESET lock_timeout ; ``` [^2]: https://blog.dalibo.com/2024/03/26/strategies-acces.html #### 5.3.3 Options pour un VACUUM en urgence **`VACUUM`** `(SKIP_DATABASE_STATS,` _`/* PG 16+ */`_ `INDEX_CLEANUP` **`off`** `,` _`/* défaut: auto */`_ `PROCESS_TOAST` **`off`** `,` **`TRUNCATE off`** `, BUFFER_USAGE_LIMIT '1GB'` _`/* voire 0 (PG 16+) */`_ `);` **`VACUUM`** `(ONLY_DATABASE_STATS);` _`/* PG 16+ */`_ Ces options sont surtout destinées à désactiver certaines étapes d’un `VACUUM` quand le temps presse vraiment. La plupart n’ont pas de sens avec `VACUUM FULL` (qui reconstruit la table, les index, les tables TOAST). Les options non traitées plus haut sont: #### INDEX_CLEANUP: L’option `INDEX_CLEANUP` à `on` déclenche systématiquement le nettoyage des index, c’est-à-dire que le `VACUUM` supprime les enregistrements de l’index qui pointent vers des lignes mortes de la table. Il est nécessaire de parcourir tout l’index, et cette opération est parfois la plus longue d’un `VACUUM`. Le défaut d’ `INDEX_CLEANUP` est `auto`: le `VACUUM` ne traite alors un index que si 2 % de blocs de la table sont à nettoyer. Ce seuil est vite atteint si les pointeurs de lignes mortes sont répartis dans la table, et non regroupés. En effet, le but est de garder une _visibility map_ à jour. Quand il faut nettoyer des lignes mortes urgemment dans une grosse table, la valeur `off` fait gagner beaucoup de temps: **Exemple p256-1.** Exemple extrait du PDF\, page 256\. ```sql VACUUM (VERBOSE, INDEX_CLEANUP off) unetable ; ``` Les index ne sont alors pas nettoyés (et quelques pointeurs de lignes dans la table non plus). Ces index conservent leur fragmentation, mais c’est généralement un souci mineur. Il faudra tout de même prévoir un autre `VACUUM` classique plus tard, éventuellement un `REINDEX`. Cette option existe aussi sous la forme d’un paramètre de stockage (`vacuum_index_cleanup`) propre à la table. La valeur `off` peut accélérer l’ `autovacuum`, mais il ne faut surtout pas oublier un `VACUUM (INDEX_CLEANUP on)` périodique sur les tables concernées. #### PROCESS_TOAST: Cette option active ou non le traitement de la partie TOAST associée à la table (parfois la partie la plus volumineuse d’une table). Elle est activée par défaut. Son utilité est la même que pour `INDEX_CLEANUP`. **Exemple p256-2.** Exemple extrait du PDF\, page 256\. ```sql VACUUM (VERBOSE, PROCESS_TOAST off) unetable ; ``` #### TRUNCATE: L’option `TRUNCATE` (à `on` par défaut) permet de tronquer les derniers blocs vides d’une table. `TRUNCATE off` évite d’avoir à poser un verrou exclusif certes court, mais parfois gênant. Cette option existe aussi sous la forme d’un paramètre de stockage de table (`vacuum_truncate`). #### BUFFER_USAGE_LIMIT: Le _ring buffer_ du `VACUUM` étant par défaut très réduit, une augmentation, même modeste, accélère les écritures. À la limite, s’il n’y a pas d’autre activité, on peut lui octroyer tout le cache de PostgreSQL (valeur `0`). #### 5.3.4 Autres options de VACUUM - `VERBOSE` - Ponctuellement: - `DISABLE_PAGE_SKIPPING` #### VERBOSE: Cette option affiche un grand nombre d’informations sur ce que fait la commande. En général, c’est une bonne idée de l’activer: **Exemple p257-1.** Exemple extrait du PDF\, page 257\. ```sql VACUUM (VERBOSE) pgbench_accounts_5 ; ``` **Exemple p257-2.** Exemple extrait du PDF\, page 257\. ```text INFO: vacuuming "public.pgbench_accounts_5" INFO: scanned index "pgbench_accounts_5_pkey" to remove 9999999 row versions DÉTAIL : CPU: user: 12.16 s, system: 0.87 s, elapsed: 18.15 s INFO: "pgbench_accounts_5": removed 9999999 row versions in 163935 pages DÉTAIL : CPU: user: 0.16 s, system: 0.00 s, elapsed: 0.20 s INFO: index "pgbench_accounts_5_pkey" now contains 100000000 row versions in 301613 pages DÉTAIL : 9999999 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "pgbench_accounts_5": found 10000001 removable, 10000051 nonremovable row versions in 327870 out of 1803279 pages DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 1071186825 There were 1 unused item identifiers. Skipped 0 pages due to buffer pins, 1475409 frozen pages. 0 pages are entirely empty. CPU: user: 13.77 s, system: 0.89 s, elapsed: 19.81 s. VACUUM ``` #### DISABLE_PAGE_SKIPPING: Par défaut, PostgreSQL ne traite que les blocs modifiés depuis le dernier `VACUUM`, ce qui est un gros gain en performance (l’information est stockée dans la _Visibility Map_, qui est généralement un tout petit fichier). Activer l’option `DISABLE_PAGE_SKIPPING` force l’analyse de tous les blocs de la table. La table est intégralement reparcourue. Ce peut être utile en cas de problème, notamment pour reconstruire cette _Visibility Map_. #### Mélange des options: Il est possible de mélanger toutes ces options presque à volonté, et de préciser plusieurs tables à nettoyer: **Exemple p258-1.** Exemple extrait du PDF\, page 258\. ```sql VACUUM (VERBOSE, ANALYZE, INDEX_CLEANUP off, TRUNCATE off, DISABLE_PAGE_SKIPPING) bigtable, smalltable ; ``` ### 5.4 SUIVI DU VACUUM - `pg_stat_activity` ou `top` - La table est-elle suffisamment nettoyée? - Vue `pg_stat_user_tables` - `last_vacuum` / `last_autovacuum` - `last_analyze` / `last_autoanalyze` - durées `total_*_time` - `log_autovacuum_min_duration = 0ms` (si possible) Un `VACUUM`, y compris lancé par l’autovacuum, apparaît dans `pg_stat_activity` et le processus est visible comme processus système avec `top` ou `ps`: `$ ps faux … postgres 3470724 0.0 0.0 12985308 6544? Ss 13:58 0:02 \_ postgres: 13/main:` `autovacuum launcher postgres 795432 7.8 0.0 14034140 13424? Rs 16:22 0:01 \_ postgres: 13/main:` `autovacuum worker pgbench1000p10` Il est fréquent de se demander si l’autovacuum s’occupe suffisamment d’une table qui grossit ou dont les statistiques semblent périmées. La vue `pg_stat_user_tables` contient quelques informations. Elle comprend les dates et nombres des `VACUUM` déclenchés automatiquement (`last_autovacuum`, `autovacuum_count`) et de ceux lancés manuellement ou par une application (`last_vacuum`, `vacuum_count`). À partir de PostgreSQL 18, les durées totales (en millisecondes) sont aussi calculées (`total_[auto]vacuum_time`). Comptes et durées se calculent depuis la réinitialisation des statistiques. Des champs similaires existent pour `ANALYZE`. Dans l’exemple ci-dessous, 161 113 lignes ont été modifiées depuis le rafraîchissement des statistiques (champ `n_mod_since_analyze`). Il reste 106 287 lignes mortes à nettoyer, contre 10 millions vivantes (`n_live_tup`). Aucune n’a été insérée depuis le dernier nettoyage (`n_ins_since_vacuum`). **Exemple p259-1.** Exemple extrait du PDF\, page 259\. ```sql SELECT * FROM pg_stat_user_tables WHERE relname ='pgbench_accounts' \gx ``` **Exemple p259-2.** Exemple extrait du PDF\, page 259\. ```text -[ RECORD 1 ]----------+------------------------------ relid | 717965 schemaname | public relname | pgbench_accounts seq_scan | 2 last_seq_scan | 2026-03-13 15:40:32.971908+01 seq_tup_read | 10000000 ``` **Exemple p260-1.** Exemple extrait du PDF\, page 260\. ```text idx_scan | 24298099 last_idx_scan | 2026-03-13 16:11:09.527817+01 idx_tup_fetch | 24398097 n_tup_ins | 10000000 n_tup_upd | 12249054 n_tup_del | 0 n_tup_hot_upd | 11573526 n_tup_newpage_upd | 675528 n_live_tup | 10000000 n_dead_tup | 106287 n_mod_since_analyze | 161113 n_ins_since_vacuum | 0 last_vacuum | 2026-03-13 15:55:11.372819+01 last_autovacuum | 2026-03-13 16:01:10.919666+01 last_analyze | 2026-03-13 15:54:46.972835+01 last_autoanalyze | 2026-03-13 16:00:12.06936+01 vacuum_count | 2 autovacuum_count | 3 analyze_count | 2 autoanalyze_count | 7 total_vacuum_time | 16834 total_autovacuum_time | 215526 total_analyze_time | 481 total_autoanalyze_time | 67172 ``` La vue `pg_stat_user_tables` peut être réinitialisée après un crash ou la promotion d’un serveur secondaire en primaire. Il est alors conseillé de lancer un `VACUUM ANALYZE` global. De plus, pour suivre les autovacuums dans les traces, il est courant et conseillé de passer le paramètre `log_autovacuum_min_duration` à 0 ou à une valeur faible comme `1s` (selon la taille des logs générés). Un outil comme pgBadger permet de voir aisément les tables les plus sollicitées par l’autovacuum. #### 5.4.1 Progression du VACUUM - Pour `VACUUM` simple / `VACUUM FREEZE` - vue `pg_stat_progress_vacuum` - blocs parcourus / nettoyés - nombre de passes dans l’index - Partie `ANALYZE` - `pg_stat_progress_analyze` - Manuel ou via autovacuum - Pour `VACUUM FULL` - vue `pg_stat_progress_cluster` La vue `pg_stat_progress_vacuum` contient une ligne par `VACUUM` (simple ou `FREEZE`) en cours d’exécution: **Exemple p261-1.** Exemple extrait du PDF\, page 261\. ```sql TABLE pg_stat_progress_vacuum \gx ``` **Tableau p261-1.** Tableau extrait du PDF\, page 261\. ```text -[ RECORD 1 ]--------+----------------- pid | 1408787 datid | 206998 datname | pgbench_4000_ssd relid | 207005 phase | scanning heap heap_blks_total | 6599090 heap_blks_scanned | 2378638 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuple_bytes | 2147483648 dead_tuple_bytes | 0 num_dead_item_ids | 0 indexes_total | 0 indexes_processed | 0 ``` Dans cet exemple, le `VACUUM` est exécuté par le processus indiqué par `pid`. La phase est le parcours de la table (`scanning heap`). Par la suite, peuvent apparaître les phases liées au nettoyage des index (`vacuuming indexes` …), entre autres. `heap_blks_scanned` indique que le `VACUUM` a parcouru 2 378 638 blocs (sur 6 599 090 blocs) lors de la copie d’écran, mais ce chiffre inclut les blocs ignorés grâce à la _visibility map_, souvent l’essentiel de la table. Pour les détails, voir la documentation officielle[^3]. À noter que le suivi du `VACUUM` dans les index (les deux derniers champs) nécessite au moins PostgreSQL 17. C’est souvent la partie la plus longue d’un `VACUUM`. Dans le cas d’un `VACUUM ANALYZE`, la seconde partie de recueil des statistiques pourra être suivie dans `pg_stat_progress_analyze`: **Exemple p261-2.** Exemple extrait du PDF\, page 261\. ```sql SELECT * FROM pg_stat_progress_analyze ; ``` **Tableau p261-2.** Tableau extrait du PDF\, page 261\. ```text -[ RECORD 1 ]--------+----------------- pid | 1408787 datid | 206998 datname | pgbench_4000_ssd relid | 207005 phase | scanning heap heap_blks_total | 6599090 heap_blks_scanned | 2378638 heap_blks_vacuumed | 0 index_vacuum_count | 0 max_dead_tuple_bytes | 2147483648 dead_tuple_bytes | 0 num_dead_item_ids | 0 indexes_total | 0 indexes_processed | 0 ``` [^3]: https://docs.postgresql.fr/current/progress-reporting.html#VACUUM-PROGRESS-REPORTING Les vues précédentes affichent aussi bien les opérations lancées manuellement que celles décidées par l’autovacuum. Par contre, pour un `VACUUM FULL`, il faut suivre la progression avec la vue `pg_stat_progress_cluster`. Cette vue est utilisable aussi avec l’ordre `CLUSTER`, d’où le nom. Exemple: **Exemple p262-1.** Exemple extrait du PDF\, page 262\. ```sql SELECT * FROM pg_stat_progress_cluster \gx ``` **Exemple p262-2.** Exemple extrait du PDF\, page 262\. ```text -[ RECORD 1 ]-------+------------------ pid | 21157 datid | 13444 datname | postgres relid | 16384 command | VACUUM FULL phase | seq scanning heap cluster_index_relid | 0 heap_tuples_scanned | 13749388 heap_tuples_written | 13749388 heap_blks_total | 199105 heap_blks_scanned | 60839 index_rebuild_count | 0 ``` Ces vues n’affichent que les opérations en cours, elles n’historisent rien. Si aucun `VACUUM` n’est en cours, elles n’afficheront rien. #### 5.4.2 Droit de lancer un VACUUM - Propriétaire - Superutilisateur - Inclus dans droit de maintenance (v17) ```sql GRANT MAINTAIN ON matable TO dba; -- granulaire GRANT pg_maintain TO dba; -- global ``` `VACUUM` est une opération de maintenance ne pouvant être effectuée que par: - le propriétaire de la table; - un superutilisateur; - à partir de PostgreSQL 17, un utilisateur possédant le droit de maintenir la table, soit via le rôle `pg_maintain` ou par un `GRANT MAINTAIN` sur la table. (Ce droit de maintenance inclut d’autres droits, comme les droits de `ANALYZE`, `CLUSTER`, `LOCK TABLE`, `REFRESH MATERIALIZED VIEW` et `REINDEX`, mais pas celui de lire les données.) Sans droit de maintenance, le `VACUUM` ne fonctionne pas pour un utilisateur non propriétaire de la table: **Exemple p262-3.** Exemple extrait du PDF\, page 262\. ```console $ vacuumdb --username maintenance --table t1 vacuumdb: vacuuming database "b3" WARNING: permission denied to vacuum "t1", skipping it ``` Si **maintenance** devient membre du rôle `pg_maintain`, tout fonctionne: **Exemple p263-1.** Exemple extrait du PDF\, page 263\. ```console $ psql --username postgres --command 'GRANT pg_maintain TO maintenance' GRANT ROLE ``` **Exemple p263-2.** Exemple extrait du PDF\, page 263\. ```console $ vacuumdb --username maintenance vacuumdb: vacuuming database "b3" ``` Avant la version 17, il est toujours possible d’avoir un groupe propriétaire de l’objet et d’ajouter un rôle de maintenance comme membre de ce groupe. Il aura le droit de lire les données. Mais si jamais un objet est créé par quelqu’un sans transférer la propriété au groupe, le rôle de maintenance ne peut faire d’opération de maintenance sur cet objet. ### 5.5 AUTOVACUUM #### 5.5.1 Autovacuum: buts & contraintes - Processus autovacuum - But: ne plus s’occuper de `VACUUM` - Suit l’activité - Seuil dépassé => worker dédié - Bridé, s’arrête en cas de verrou - Gère: `VACUUM`, `ANALYZE`, `FREEZE` - mais pas `FULL` Lancer manuellement des `VACUUM` étant fastidieux, un démon _autovacuum_ se charge de la tâche. Le principe est le suivant: Le démon `autovacuum launcher` s’occupe de lancer des _workers_ régulièrement sur les différentes bases. Ce nouveau processus inspecte les statistiques sur les tables (vue `pg_stat_all_tables`): nombres de lignes insérées, modifiées et supprimées. Quand certains seuils sont dépassés sur un objet, le _worker_ effectue un `VACUUM`, un `ANALYZE`, voire un `VACUUM FREEZE` (mais jamais, rappelons-le, un `VACUUM FULL`). Sauf exception, un `VACUUM` lancé par l’ _autovacuum_ est arrêté si le verrou qu’il pose entre en conflit avec l’activité normale. Le nombre de ces _workers_ est limité, afin de ne pas engendrer de charge trop élevée. De même, les débits en lecture/écriture sont limités. Tout cela se paramètre. Pour les détails précis sur tout qui suit, ne pas hésiter à consulter la documentation officielle[^4]. #### 5.5.2 Paramétrage du déclenchement de l’autovacuum - `autovacuum` (on!) - `autovacuum_naptime` (1 min) - `autovacuum_max_workers` (3) - plusieurs _workers_ simultanés sur une base - un seul par table - (v18) changement à chaud jusque `autovacuum_worker_slots` (16) [^4]: https://docs.postgresql.fr/current/routine-vacuuming.html#AUTOVACUUM #### Activation & désactivation de l’autovacuum: `autovacuum` (`on` par défaut) détermine si le démon autovacuum doit être activé. À `off`, il ne se déclenche pas spontanément. Il est fortement conseillé de laisser `autovacuum` à `on`! S’il le faut vraiment, il est possible de désactiver l’autovacuum sur une table précise: **Exemple p265-1.** Exemple extrait du PDF\, page 265\. ```sql ALTER TABLE nom_table SET (autovacuum_enabled = off); ``` Mais cela est très rare, et impose que le nettoyage soit géré manuellement. #### autovacuum_naptime: `autovacuum_naptime` est le temps d’attente minimum entre deux périodes de vérification sur une même base (1 minute par défaut). Son but est de ne pas déclencher l’autovacuum trop souvent et inutilement sur une même base. C’est la principale raison pour laquelle le déclenchement de l’autovacuum après des modifications de tables n’est pas instantané. Il arrive rarement qu’on réduise `autovacuum_naptime` quand l’autovacuum ne passe pas assez souvent sur certaines tables très actives. #### Workers de l’autovacuum: `autovacuum_max_workers` est le nombre maximum de _workers_ que l’autovacuum peut utiliser simultanément, chacun s’occupant d’une table (ou partition de table). Chaque table ne peut être traitée simultanément que par un unique _worker_. La valeur par défaut (3) est généralement suffisante. Néanmoins, s’il y a fréquemment trois _autovacuum workers_ travaillant en même temps, et surtout si cela dure, il peut être nécessaire d’augmenter ce paramètre. Cela est fréquent quand il y a de très nombreuses tables. Noter qu’il faut alors augmenter certaines ressources allouées au nettoyage, car les _workers_ se les partagent, comme le paramètre `autovacuum_vacuum_cost_limit` (voir plus bas). Une exception est `autovacuum_work_mem` qui, lui, est une quantité de RAM utilisable par chaque _worker_ indépendamment, et par défaut égal à `maintenance_work_mem`, souvent relativement élevé. Depuis PostgreSQL 18, `autovacuum_max_workers` peut être changé à chaud, ce qui n’était pas le cas auparavant. La valeur peut être augmentée jusqu’à une autre valeur fixée par `autovacuum_worker_slots` (valeur par défaut 16, qui devrait suffire). #### 5.5.3 Déclenchement de l’autovacuum - Seuil de déclenchement = _threshold_ + _scale factor_ × nb lignes de la table Dans le cas général, l’autovacuum déclenche un `VACUUM` ou un `ANALYZE` à partir de seuils calculés sur le principe d’un nombre de lignes minimal (_threshold_) et d’une proportion de la table existante (_scale factor_) de lignes modifiées, insérées ou effacées. Ces seuils pourront être adaptés table par table en modifiant les paramètres qui suivent. #### 5.5.4 Déclenchement de l’autovacuum: VACUUM - Pour `VACUUM` (modifications/suppressions) - `autovacuum_vacuum_scale_factor` (20 %) - `autovacuum_vacuum_threshold` (50) - (v18) seuil `autovacuum_vacuum_max_threshold` (100 Mlignes) - Pour `VACUUM` (insertions) - `autovacuum_vacuum_insert_threshold` (1000) - `autovacuum_vacuum_insert_scale_factor` (20 %) #### 5.5.4.1 VACUUM automatique suite aux modifications Pour le `VACUUM`, si on considère les enregistrements morts (supprimés ou anciennes versions de lignes), la condition de déclenchement est: - `nb_enregistrements_morts (pg_stat_all_tables.n_dead_tup) >= autovacuum_vacuum_threshold` - `+ autovacuum_vacuum_scale_factor × nb_enregs (pg_class.reltuples)` où, par défaut: - `autovacuum_vacuum_threshold` vaut 50 lignes; - `autovacuum_vacuum_scale_factor` vaut 0,2 soit 20 % de la table. Donc, par exemple, dans une table d’un million de lignes, modifier 200 050 lignes provoque le passage d’un `VACUUM`. Pour les grosses tables avec de l’historique, modifier 20 % de la volumétrie peut être extrêmement long. Quand l’ `autovacuum` lance enfin un `VACUUM`, celui-ci a donc beaucoup de travail et peut durer longtemps et générer beaucoup d’écritures. Il est donc fréquent de descendre la valeur de `autovacuum_vacuum_scale_factor` à quelques pour cent sur les grosses tables. S’il faut modifier un paramètre, il est préférable de ne pas le faire au niveau global, mais de cibler les tables où cela est nécessaire. Par exemple, l’ordre suivant réduit à 5 % de la table le nombre de lignes à modifier avant que l’ `autovacuum` y lance un `VACUUM`: **Exemple p266-1.** Exemple extrait du PDF\, page 266\. ```sql ALTER TABLE nom_table SET (autovacuum_vacuum_scale_factor = 0.05); ``` Une alternative est de monter `autovacuum_vacuum_threshold` à un nombre de lignes élevé et de descendre `autovacuum_vacuum_scale_factor` à 0, mais il faut alors calculer le nombre de lignes qui déclenchera le nettoyage, et cela dépend fortement de la table et de sa fréquence de mise à jour. Par exemple, si on estime que la table doit être nettoyée après 5 millions de modifications ou suppressions: **Exemple p267-1.** Exemple extrait du PDF\, page 267\. ```sql ALTER TABLE nom_table SET (autovacuum_vacuum_scale_factor = 0); ALTER TABLE nom_table SET (autovacuum_vacuum_threshold = 5_000_000); ``` Dans le même esprit, PostgreSQL 18 possède un paramètre `autovacuum_vacuum_max_threshold` (par défaut 100 millions): à partir de ce nombre de lignes modifiées, l’autovacuum lance un `VACUUM` sur la table, quels que soient les paramètres précédents. Par exemple, une table avec 10 milliards de lignes est nettoyée après 100 millions de modifications, et non après 2 milliards comme en PostgreSQL 17 avec le paramétrage par défaut. Ce seuil assez élevé peut être ajusté au besoin. La commande suivante au niveau d’une table est aussi possible et équivaut aux deux ordres précédents: **Exemple p267-2.** Exemple extrait du PDF\, page 267\. ```sql ALTER TABLE nom_table SET ( autovacuum_vacuum_max_threshold = 5_000_000 ) ; ``` La valeur -1 désactive ce seuil. #### 5.5.4.2 VACUUM automatique suite aux insertions Le démon autovacuum lance également un `VACUUM` sur une table après un certain nombre d’insertions de nouvelles lignes. Dans ce cas, l’intérêt n’est pas de nettoyer des lignes, mais principalement de créer et rafraîchir la _visibility map_, nécessaire à certaines optimisations, et de procéder à un gel des lignes plus régulier[^5] Les paramètres en jeu sont différents: - `nb_enregistrements_insérés (pg_stat_all_tables.n_ins_since_vacuum) >= autovacuum_vacuum_insert_threshold` > `+ autovacuum_vacuum_insert_scale_factor × nb_enregs (pg_class.reltuples)` Par défaut, ils valent aussi 50 lignes et 0,2. [^5]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b07642dbcd8d5de05f0ee1dbb72dd6760dd30436 #### 5.5.5 Adapter le déclenchement de l’autovacuum: VACUUM - Pour une grosse table: **Exemple p268-1.** Exemple extrait du PDF\, page 268\. ```sql ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0.1); ``` **Exemple p268-2.** Exemple extrait du PDF\, page 268\. ```sql ALTER TABLE table_name SET (autovacuum_vacuum_threshold = 1000000); ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0); ``` **Exemple p268-3.** Exemple extrait du PDF\, page 268\. ```sql -- v18 ALTER TABLE table_name SET (autovacuum_vacuum_max_threshold = 1000000); ``` - Ne pas être trop agressif! Ces exemples reprennent les trois méthodes de modification de paramétrage sur une grande table: réduire le _scale factor_; poser un seuil fixe et un _scale factor_ nul; poser un seuil maximum à la valeur calculée par défaut. Une méthode n’est pas meilleure qu’une autre. Le choix dépend du nombre de bases et tables à maintenir, des volumétries, de leur croissance prévisible… Ne rendez pas l’autovacuum trop agressif! Il doit tourner assez souvent sur les tables importantes, mais pas en permanence dès qu’il y a quelques modifications! Le premier risque est de saturer les _workers_. #### 5.5.6 Déclenchement de l’autovacuum: ANALYZE - Pour `ANALYZE` - `autovacuum_analyze_scale_factor` (10 %) - `autovacuum_analyze_threshold` (50) - Adapter pour une grosse table: **Exemple p268-4.** Exemple extrait du PDF\, page 268\. ```sql ALTER TABLE table_name SET (autovacuum_analyze_scale_factor = 0.05); ``` Pour le lancement automatique d’un `ANALYZE`, le principe est le même. Il n’y a que deux paramètres de calcul du seuil, qui prennent en compte toutes les lignes modifiées _ou_ insérées: **Exemple p268-5.** Exemple extrait du PDF\, page 268\. ```text nb_insert + nb_updates + nb_delete (n_mod_since_analyze) >= autovacuum_analyze_threshold + nb_enregs × autovacuum_analyze_scale_factor ``` où, par défaut: - `autovacuum_analyze_threshold` vaut 50 lignes; - `autovacuum_analyze_scale_factor` vaut 0,1, soit 10 %. Dans une table d’un million de lignes, modifier 100 050 lignes provoque donc le passage d’un `ANALYZE`. Là encore, il est fréquent de modifier ces paramètres sur les grosses tables pour rafraîchir les statistiques plus fréquemment. ### 5.6 PARAMÉTRAGE DE VACUUM & AUTOVACUUM - VACUUM vs autovacuum - Mémoire - Mémoire - Gestion des coûts - Gel des lignes En fonction de la tâche exacte, de l’agressivité acceptable ou de l’urgence, plusieurs paramètres peuvent être mis en place. Ces paramètres peuvent différer (par le nom ou la valeur) selon qu’ils s’appliquent à un `VACUUM` lancé manuellement ou par script, ou à un processus lancé par l’autovacuum. #### 5.6.1 VACUUM vs autovacuum **Tableau p270-1.** Tableau extrait du PDF\, page 270\. |**VACUUM manuel**|**autovacuum**| |---|---| |Urgent|Arrière-plan| |Pas de limite|Peu agressif| |Paramètres|Les mêmes + paramètres de surcharge| Quand on lance un ordre `VACUUM`, il y a souvent urgence, ou l’on est dans une période de maintenance, ou dans un batch. Les paramètres que nous allons voir ne cherchent donc pas, par défaut, à économiser des ressources. À l’inverse, un `VACUUM` lancé par l’autovacuum ne doit pas gêner une production peut-être chargée. Il existe donc des paramètres `autovacuum_*` surchargeant les précédents, et beaucoup plus conservateurs. #### 5.6.2 Mémoire - Quantité de mémoire allouable - `maintenance_work_mem` - `autovacuum_work_mem` - montés souvent à ½ à 1 Go - Impact - `VACUUM` - nettoyage & construction d’index `maintenance_work_mem` est la quantité de mémoire qu’un processus effectuant une opération de maintenance (c’est-à-dire n’exécutant pas des requêtes classiques comme `SELECT`, `INSERT`, `UPDATE` …) est autorisé à allouer pour sa tâche de maintenance. Cette mémoire est utilisée lors de la construction d’index ou l’ajout de clés étrangères et, dans le contexte de `VACUUM`, pour stocker les adresses des enregistrements pouvant être recyclés. Cette mémoire est remplie pendant la phase 1 du processus de `VACUUM`, tel qu’expliqué plus haut. Rappelons qu’une adresse d’enregistrement (`tid`, pour `tuple id`) a une taille de 6 octets et est composée du numéro de bloc dans la table (4 octets), et du numéro d’enregistrement dans le bloc (2 octets), par exemple `(0,1)`, `(3164,98)` ou `(5351510,42)`. Le défaut de 64 Mo est assez faible. Si tous les enregistrements morts d’une table ne tiennent pas dans `maintenance_work_mem`, `VACUUM` est obligé de faire plusieurs passes de nettoyage, donc plusieurs parcours complets de chaque index. Une valeur plus élevée de `maintenance_work_mem` est donc conseillée: s’il est déjà possible de stocker plusieurs dizaines de millions d’enregistrements à effacer dans 256 Mo, 1 Go peut être utile lors de très grosses purges. PostgreSQL 17 améliore beaucoup la consommation mémoire et la vitesse de nettoyage des index, et doit rendre rarissime les nettoyages d’index en plusieurs passes. PostgreSQL 17 fait aussi disparaître une limite de 1 Go pour le nettoyage des index: `VACUUM` ne sait pas en utiliser plus jusque PostgreSQL 16. Par contre, l’indexation de grosses tables pourra toujours bénéficier d’une valeur supérieure à 1 Go. Rappelons que plusieurs `VACUUM` ou `autovacuum` peuvent fonctionner simultanément et consommer chacun un `maintenance_work_mem`! (Voir `autovacuum_max_workers` plus haut.) `autovacuum_work_mem` permet de surcharger `maintenance_work_mem` spécifiquement pour l’autovacuum. Par défaut les deux sont identiques, et l’on conserve généralement cette configuration. Au besoin, `maintenance_work_mem` peut être surchargé le temps d’une session. #### 5.6.3 Bridage du VACUUM et de l’autovacuum - Pauses régulières après une certaine activité - Par bloc traité - `vacuum_cost_page_hit` / `_miss` / `_dirty` (1/2/20) - jusque total de: `vacuum_cost_limit` (200) - pause: `vacuum_cost_delay` (en manuel: 0 ms!) - Surcharge pour l’autovacuum - `autovacuum_vacuum_cost_limit` (identique) - `autovacuum_vacuum_cost_delay` (2 ms) - => débit en écriture max: ~40 Mo/s - Pour accélérer: augmenter la limite #### Principe: Les paramètres suivant permettent d’éviter qu’un `VACUUM` ne gêne les autres sessions en saturant le disque. Le principe est de provoquer une pause après qu’une certaine activité a été réalisée. #### Paramètres de coûts: Ces trois paramètres « quantifient » l’activité de `VACUUM`, affectant un coût arbitraire à chaque fois qu’une opération est réalisée: - `vacuum_cost_page_hit`: coût d’accès à chaque page présente dans le cache de PostgreSQL - (valeur: 1); - `vacuum_cost_page_miss`: coût d’accès à chaque page hors de ce cache (valeur: 2); - `vacuum_cost_page_dirty`: coût de modification d’une page, et donc de son écriture (valeur: - 20). Il est déconseillé de modifier ces paramètres de coût. #### Pause: Quand le coût cumulé atteint un seuil, l’opération de nettoyage marque une pause. Elle est gouvernée par deux paramètres: - `vacuum_cost_limit`: coût cumulé à atteindre avant de déclencher la pause (défaut: 200); - `vacuum_cost_delay`: temps à attendre (défaut: 0 ms!) - En conséquence, les `VACUUM` lancés manuellement (en ligne de commande ou via - `vacuumdb`) ne sont **pas** freinés par ce mécanisme et peuvent donc entraîner de fortes écritures! Mais c’est généralement ce que l’on veut dans un batch ou en urgence, et il vaut mieux alors être le plus rapide possible. Pour les urgences, rappelons que les options `INDEX_CLEANUP off` ou `PROCESS_TOAST off` permettent aussi d’ignorer le nettoyage des index ou des TOAST. #### Paramétrage pour le VACUUM manuel: Il est conseillé de ne pas toucher au paramétrage par défaut de `vacuum_cost_limit` et `vacuum_cost_delay`. Si on doit lancer un `VACUUM` manuellement en limitant son débit, procéder comme suit dans une session: **Exemple p273-1.** Exemple extrait du PDF\, page 273\. ```sql -- Reprise pour le VACUUM du paramétrage d'autovacuum SET vacuum_cost_limit = 200 ; SET vacuum_cost_delay = '2ms' ; VACUUM (VERBOSE) matable ; ``` Avec `vacuumdb`, il faudra passer par la variable d’environnement `PGOPTIONS`. #### Paramétrage pour l’autovacuum: Les `VACUUM` d’autovacuum, eux, sont par défaut limités en débit pour ne pas gêner l’activité normale de l’instance. Deux paramètres surchargent les précédents: - `autovacuum_vacuum_cost_limit` vaut par défaut -1, donc reprend la valeur 200 de - `vacuum_cost_limit`; - `autovacuum_vacuum_cost_delay` vaut par défaut 2 ms. Un (`autovacuum_`) `vacuum_cost_limit` à 200 consiste à traiter au plus 200 blocs lus en cache (car `vacuum_cost_page_hit` = 1), soit 1,6 Mo, avant de faire la pause de 2 ms. Si ces blocs doivent être écrits, on descend en-dessous de 10 blocs traités avant chaque pause (`vacuum_cost_page_dirty` = 20) avant la pause de 2 ms, d’où un débit en écriture maximal de l’autovacuum de 40 Mo/s Cela s’observe aisément par exemple avec `iotop`. Ce débit est partagé équitablement entre les différents _workers_ lancés par l’autovacuum (sauf paramétrage spécifique au niveau de la table). Pour rendre l’`autovacuum` plus rapide, il est préférable d’augmenter `autovacuum_vacuum_cost_limit` au-delà de 200, plutôt que de réduire `autovacuum_vacuum_cost_delay` qui n’est qu’à 2 ms, pour ne pas monopoliser le disque. (Exception : les versions antérieures à la 12, car `autovacuum_vacuum_cost_delay` valait alors 20 ms et le débit en écriture saturait à 4 Mo/s ! La valeur 2 ms tient mieux compte des disques actuels.) La prise en compte de la nouvelle valeur de la limite par les _workers_ en cours sur les tables est automatique à partir de PostgreSQL 16. Dans les versions précédentes, il faut arrêter les _workers_ en cours (avec `pg_cancel_backend()`) et attendre que l’`autovacuum` les relance. Quand `autovacuum_max_workers` est augmenté, prévoir aussi d’augmenter `autovacuum_vacuum_cost_limit`, sinon le débit global ne montera pas. Il faut d’ailleurs vérifier que les disques peuvent supporter la charge en plus. Sur le sujet, voir la conférence de Robert Haas à PGconf.EU 2023 à Prague[^6]. [^6]: https://www.postgresql.eu/events/pgconfeu2023/sessions/session/4847/slides/432/Understanding%20and%20Fixing%20Autovacuum%20-%20PGCONF.EU%202023.pdf#page=14 #### 5.6.4 Paramétrage du FREEZE (1) Le but est de geler les numéros de transaction assez vite: **Figure p274-1.** Cycle des identifiants de transaction \(txid\)\. ![Cycle des identifiants de transaction \(txid\)](/images/dba2/manual/p274-01.png) Rappelons que les numéros de transaction stockés sur les lignes ne sont stockés que sur 32 bits, et sont recyclés. Il y a donc un risque de mélanger le passé et le futur des transactions lors du rebouclage (_wraparound_). Afin d’éviter ce phénomène, l’opération `VACUUM FREEZE` « gèle » les vieux enregistrements, afin que ceux-ci ne se retrouvent pas brusquement dans le futur. Concrètement, il s’agit de positionner un _hint bit_ dans les entêtes des lignes concernées, indiquant qu’elle est plus vieille que tous les numéros de transactions actuellement actifs. (Avant PostgreSQL 9.4, la colonne système `xmin` était simplement remplacée par un numéro spécial `FrozenTransactionId`, soit 2, ce qui revient au même). #### 5.6.5 Paramétrage du FREEZE (2) Quand le `VACUUM` gèle-t-il les lignes? - « Âge » d’une table: `age (pgclass.relfrozenxid)` - Les blocs nettoyés/gelés sont notés dans la _visibility map_ - `vacuum_freeze_min_age` (50 Mtrx) - âge des lignes _rencontrées_ à geler - `vacuum_freeze_table_age` (150 Mtrx) - agressif (toute la table) - Au plus tard, par l’autovacuum sur toute la table: - `autovacuum_freeze_max_age` (200 Mtrx) - Généralement on ne touche pas Geler une ligne ancienne implique de réécrire le bloc et donc des écritures dans les journaux de transactions et les fichiers de données. Il est inutile de geler trop tôt une ligne récente, qui sera peut-être bientôt réécrite. Le numéro de transaction le plus ancien connu au sein d’une table est porté par `pgclass.relfrozenxid`, et est sur 32 bits. Il faut utiliser la fonction `age()` pour connaître l’écart par rapport au numéro de transaction courant (géré sur 64 bits en interne). **Exemple p275-1.** Exemple extrait du PDF\, page 275\. ```sql SELECT relname, relfrozenxid, round(age(relfrozenxid) /1e6,2) AS "age_Mtrx" FROM pg_class c WHERE relname LIKE 'pgbench%' AND relkind IN ('r','t') ORDER BY age(relfrozenxid) ; ``` **Exemple p275-2.** Exemple extrait du PDF\, page 275\. ```text relname | relfrozenxid | age_Mtrx ---------------------+--------------+---------- pgbench_accounts_7 | 882324041 | 0.00 pgbench_accounts_8 | 882324041 | 0.00 pgbench_accounts_2 | 882324041 | 0.00 pgbench_history | 882324040 | 0.00 pgbench_accounts_5 | 848990708 | 33.33 pgbench_tellers | 832324041 | 50.00 pgbench_accounts_3 | 719860155 | 162.46 ``` #### Paramétrage: Plusieurs seuils gèrent le déclenchement du gel des lignes d’une table. Leurs valeurs par défaut sont satisfaisantes pour la plupart des installations et ne sont pour ainsi dire jamais modifiées. Un souci lié au gel a généralement une cause plus profonde (dysfonctionnement du `VACUUM` …), et augmenter les seuils suivants ne devrait servir qu’à se donner le temps de le résoudre. Par contre, il est important de bien connaître le fonctionnement pour ne pas être surpris. Une partie du gel se fait lors d’un `VACUUM` normal. Si ce dernier rencontre, dans un bloc qu’il nettoie, un enregistrement plus vieux que `vacuum_freeze_min_age` (par défaut 50 millions de transactions écoulées), alors le _tuple_ peut et doit être gelé. Cela ne concerne que les lignes dans des blocs qui ont des lignes mortes à nettoyer: les lignes dans des blocs rarement utilisés y échappent. (Y échappent aussi les lignes qui ne sont pas forcément visibles par toutes les transactions ouvertes.) `VACUUM` doit donc périodiquement déclencher un nettoyage plus agressif de toute la table (et non pas uniquement des blocs modifiés depuis le dernier `VACUUM`), afin de nettoyer tous les vieux enregistrements. C’est le rôle de `vacuum_freeze_table_age` (par défaut 150 millions de transactions). Si la table a atteint cet âge, un `VACUUM` (manuel ou automatique) lancé dessus deviendra « agressif »: **Exemple p276-1.** Exemple extrait du PDF\, page 276\. ```sql VACUUM (VERBOSE) pgbench_tellers ; INFO: aggressively vacuuming "public.pgbench_tellers" ``` Les blocs ne contenant que des lignes visibles par toutes les transactions, normalement ignorés, sont tout de même parcourus.Seuls les blocs marqués comme entièrement gelés dans la _visibility map_ sont ignorés. Les lignes non gelées qui s’y trouvent et plus vieilles que `vacuum_freeze_min_age` seront alors gelées. Ce peut être long, ou pas, en fonction de l’efficacité de l’étape précédente. À côté des numéros de transaction habituels, les identifiants `multixact`, qui servent au verrouillage des lignes par des transactions multiples, évitent aussi le _wraparound_ avec des paramètres spécifiques (`vacuum_multixact_freeze_min_age`, `vacuum_multixact_freeze_table_age`) qui ont les mêmes valeurs que leurs homologues. Enfin, il faut traiter le cas des tables sur lesquelles un `VACUUM` n’a pas eu besoin de se déclencher, ni n’a été demandé, depuis très longtemps. L’autovacuum y veille: `autovacuum_freeze_max_age` (par défaut 200 millions de transactions) est l’âge maximum que doit avoir une table. S’il est dépassé, l’autovacuum lance un `VACUUM` agressif sur cette table. Il est visible dans `pg_stat_activity` avec la mention caractéristique _to prevent wraparound_: **Exemple p276-2.** Exemple extrait du PDF\, page 276\. ```text autovacuum: VACUUM public.pgbench_accounts (to prevent wraparound) ``` Ce traitement est lancé même si `autovacuum` est désactivé (c’est-à-dire à `off`). Un `VACUUM FREEZE` lancé manuellement gèle toutes les lignes possibles, même « jeunes ». Il équivaut donc à un `VACUUM` avec les paramètres `vacuum_freeze_table_age` (âge minimal de la table) et `vacuum_freeze_min_age` (âge minimal des lignes pour les geler) à 0. Monter `vacuum_freeze_table_age` et `autovacuum_freeze_max_age`, par exemple à 300 millions, est possible, mais cela ne fait souvent que repousser une opération nécessaire. **Charge induite par le gel**: Le gel des lignes peut être très lourd s’il y a beaucoup de lignes à geler, surtout en terme d’écriture et de journaux générés. En effet, le `FREEZE` réécrit tous les blocs concernés. Le déclenchement inopiné d’un `VACUUM FREEZE` sur l’intégralité d’une très grosse table très statique est une mauvaise surprise assez fréquente. La table est réécrite, les entrées-sorties sont chargées, la sauvegarde PITR enfle, évidemment à un moment où la base est chargée. Heureusement, le gel est généralement très rapide, car l’essentiel du travail a été fait par les nettoyages précédents. Si la table a déjà été entièrement gelée dans le passé, et pas modifiée, il peut juste s’agir d’une mise à jour du `relfrozenxid`. Les blocs déjà entièrement gelés sont recensés dans la _visibility map_, qui recense aussi les blocs sans ligne morte. Ces blocs gelés ne seront pas reparcourus s’ils ne sont plus modifiés. Cela accélère énormément le `FREEZE` sur les grosses tables. Si unpas d’inconvénient à l’interrompre s’il gêne. Par contre, l’ `VACUUM FREEZE` est interrompu, ce qu’il a déjà gelé n’est pas perdu. Il n’y a donc _autovacuum_ risque de le redéclencher très vite s’il le juge nécessaire. L’âge de la table peut dépasser `autovacuum_freeze_max_age` si le nettoyage est laborieux, ce qui explique la marge par rapport à la limite fatidique des 2 milliards de transactions. Quelques problèmes possibles sont évoqués plus bas. #### Évolution de l’âge d’une base: Nous avons vu que l’âge d’une base est en fait l’âge de la table la plus ancienne, qui se calcule à partir de la colonne `pg_database.datfrozenxid`: **Exemple p277-1.** Exemple extrait du PDF\, page 277\. ```sql SELECT datname, datfrozenxid, age (datfrozenxid) FROM pg_database ORDER BY 3 DESC ; ``` **Tableau p277-1.** Tableau extrait du PDF\, page 277\. ```text datname | datfrozenxid | age -----------+--------------+----------- pgbench | 1809610092 | 149835222 template0 | 1957896953 | 1548361 template1 | 1959012415 | 432899 postgres | 1959445305 | 9 ``` Concrètement, on voit l’âge d’une base de données s’approcher peu à peu de 200 millions de transactions, ce qui correspond à l’âge des plus « vieilles » tables, souvent celles sur lesquelles l’autovacuum ne passe jamais. L’âge des tables évolue même si l’essentiel de leur contenu, voire la totalité, est déjà gelé (car il reste au moins le `pg_class.relfrozenxid` à mettre à jour, ce qui est bien sûr très rapide). Cet âge retombe quand un gel est forcé sur ces tables, puis remonte, etc. **Figure 5.4.** Évolution de l’âge des trois bases d’une instance\. ![Évolution de l’âge des trois bases d’une instance](/images/dba2/manual/p278-figure-5-4.png) #### Résumé: Que retenir de ce paramétrage complexe? - le `VACUUM` gèlera une partie des lignes un peu anciennes lors de son fonctionnement habituel; - un bloc gelé non modifié ne sera plus à regeler; - de grosses tables statiques peuvent engendrer soudainement une grosse charge en écriture; il vaut mieux être proactif. ### 5.7 PROBLÈMES COURANTS #### 5.7.1 L’autovacuum dure trop longtemps £3 - Fréquence de passage? - Débit? - Nombre de workers? - Taille vraiment trop grosse? **Figure 5.5.** Nombre de workers avant et après la réduction de autovacuum\_vacuum\_cost\_delay\. ![Nombre de workers avant et après la réduction de autovacuum\_vacuum\_cost\_delay](/images/dba2/manual/p279-figure-5-5.png) Nous avons vu que le paramétrage de l’autovacuum vise à limiter la charge sur la base. Le nettoyage d’une grosse table peut donc être parfois très long. Ce n’est pas forcément un problème majeur si l’opération arrive à terme dans un délai raisonnable, mais il vaut mieux savoir pourquoi. Il peut y avoir plusieurs causes, qui ne s’excluent pas mutuellement. Il est fréquent que les grosses tables soient visitées trop peu souvent. Rappelons que la propriété `autovacuum_vacuum_scale_factor` de chaque table est par défaut à 20 %: lorsque l’autovacuum se déclenche, il doit donc traiter une volumétrie importante. Il est conseillé de réduire la valeur de ce paramètre (ou de jouer sur `autovacuum_vacuum_threshold`) pour un nettoyage plus fréquent. Dans l’idéal, la même volumétrie sera traitée de manière plus étalée. Les index risquent cependant d’être intégralement parcourus plus souvent, même si PostgreSQL ne nettoie pas un index en-dessous d’un certain seuil de modifications. Le compromis peut être délicat à trouver. Le débit en écriture peut être insuffisant (c’est fréquent sur les anciennes versions), auquel cas, avec des disques corrects, on peut baisser `autovacuum_vacuum_cost_delay` ou plutôt monter `autovacuum_vacuum_cost_limit`. Sur le graphique ci-dessus, issu d’un cas réel, les trois workers semblaient en permanence occupés. Il risquait donc d’y avoir un retard pour nettoyer certaines tables, ou rafraîchir les statistiques. La réduction de `autovacuum_vacuum_cost_delay` de 20 à 2 ms (le défaut à présent) a mené à une réduction drastique de la durée de traitement de chaque worker. Rappelons qu’un `VACUUM` manuel (ou planifié) n’est soumis à aucun bridage. Le nombre de workers peut être trop bas, notamment s’il y a de nombreuses tables. Auquel cas ils semblent tous activés en permanence, comme ci-dessus. Monter `autovacuum_max_workers` au-delà de 3 nécessite d’augmenter le débit autorisé avec les paramètres ci-dessus. Pour des grandes tables, le partitionnement permet de paralléliser l’activité de l’autova-cuum.Les _workers_ peuvent en effet travailler simultanément sur des partitions de tables différentes. Un grand nombre de bases actives peut devenir un frein et augmenter l’intervalle entre deux nettoyages d’une base, bien que l’ `autovacuum launcher` ignore les bases inutilisées. Exceptionnellement, l’autovacuum peut tomber en erreur (bloc corrompu, index fonctionnel avec une fonction boguée…) et ne jamais finir (surveiller les traces). #### 5.7.2 Arrêter un VACUUM? - Lancement manuel ou script - conflit avec certains verrous - Autovacuum - interrompre s’il gêne - par superutilisateur ou rôle `pg_signal_autovacuum_worker` (v18) - Exception: _to prevent wraparound_ lent **et** bloquant - `pg_cancel_backend` + `VACUUM FREEZE` manuel Le cas des `VACUUM` manuels a été vu plus haut: ils peuvent gêner quelques verrous ou opérations DDL. Il faudra les arrêter manuellement au besoin. C’est différent si l’ _autovacuum launcher_ a lancé le processus: l’opération s’arrête si un utilisateur pose un verrou en conflit. La seule exception concerne un `VACUUM FREEZE` lancé quand la table doit être gelée, donc avec la mention _to prevent wraparound_ dans `pg_stat_activity`: celui-ci ne sera pas interrompu. L’autovacuum ne pose certes qu’un verrou destiné à éviter les modifications de schéma simultanées (`SHARE UPDATE EXCLUSIVE`). Comme le débit en lecture et écriture est bridé par le paramétrage habituel de l’autovacuum, ce verrou peut durer assez longtemps. Cela peut s’avérer gênant avec certaines applications qui posent des verrous lourds (par exemple `LOCK TABLE`), ce qui est à éviter. (C’était surtout un souci avant PostgreSQL 9.6, où toute la table était relue à chaque `FREEZE`). Une solution pérenne est d’augmenter le débit en écriture (voir plus haut). Si les opérations sont impactées, on peut vouloir lancer soi-même un `VACUUM FREEZE` manuel, donc non bridé. Il faudra alors repérer le PID du `VACUUM FREEZE` en cours, l’arrêter avec `pg_cancel_backend`, puis lancer manuellement l’ordre `VACUUM FREEZE` sur la table concernée, (et rapidement avant que l’autovacuum ne relance un processus). La supervision peut se faire avec `pg_stat_progress_vacuum` et `iotop`. Les processus de l’autovacuum appartiennent aux superutilisateurs. Seul un superutilisateur peut les arrêter. Cependant, à partir de PostgreSQL 18, le nouveau rôle `pg_signal_autovacuum_worker` peut être attribué à un utilisateur pour annuler une requête ou arrêter une connexion d’ _autovacuum worker_. Comme pour tous les rôles `pg_*`, il convient d’être très prudent lors de l’attribution de ces rôles. #### 5.7.3 Si le VACUUM FREEZE tarde… - Messages quand le _wraparound_ approche: ```text WARNING: database "mydb" must be vacuumed within 39985967 transactions HINT: To avoid XID assignment failures, execute a database-wide VACUUM in that database. ``` ```text ERROR: database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "mydb" HINT: Execute a database-wide VACUUM in that database. ``` - Mode d’urgence (_failsafe_) automatique Si les messages ci-dessus apparaissent dans les traces, le _wraparound_ est à moins de 40 millions, respectivement 3 millions de transactions, dans le futur. Cela laisse un peu de temps pour procéder au nettoyage soi-même et voir pourquoi il n’est pas efficace. Ce peut être aussi très court sur certaines installations. #### Mode « failsafe »: Il existe un mode _failsafe_: son but est de rendre le `VACUUM` très agressif, uniquement pour éviter le _wraparound_. Quand la table atteint l’âge, très élevé, de 1,6 milliard de transactions (défaut des paramètres `vacuum_failsafe_age` et `vacuum_multixact_failsafe_age`), un `VACUUM` simple va automatiquement désactiver le nettoyage des index pour nettoyer plus rapidement la table et permettre, espérons-le, d’avancer l’identifiant le plus ancien de la table. #### 5.7.4 Ce qui peut bloquer le VACUUM FREEZE - Causes: - sessions _idle in transaction_ sur une longue durée - slot de réplication en retard/oublié - transactions préparées oubliées (`pg_prepared_xacts`) - erreur à l’exécution du `VACUUM` - Conséquences: - processus autovacuum répétés - plus de nouvelles transactions en écriture - Supervision: - `check_pg_activity`: `xmin`, `max_freeze_age` - surveillez les traces! Il arrive que le fonctionnement du `FREEZE` soit gêné par un problème qui lui interdit de recycler les plus anciens numéros de transactions, ce qui fait apparaître les messages d’alerte plus haut. (Ces causes gênent aussi un `VACUUM` simple, mais les symptômes sont alors surtout un gonflement des tables concernées.) Les causes possibles sont: - des sessions _idle in transactions_ en attente depuis des jours ou des semaines (voir le statut `idle in transaction` dans `pg_stat_activity`, et au besoin fermer la session): au pire, - elles disparaissent après redémarrage; - des slots de réplication pointent vers un secondaire très en retard, voire disparu (consulter `pg_replication_slots`, et supprimer le slot); - des transactions préparées (pas des requêtes préparées!) n’ont jamais été validées ni annulées, (voir `pg_prepared_xacts`, et annuler la transaction): elles ne disparaissent pas après redémarrage; - l’opération de `VACUUM` tombe en erreur: corruption de table ou index, fonction d’index fonctionnel buggée, etc. (voir les traces et corriger le problème, supprimer l’objet ou la fonction, etc.). Pour effectuer le `FREEZE` en urgence le plus rapidement possible, on peut utiliser: **Exemple p282-1.** Exemple extrait du PDF\, page 282\. ```sql VACUUM (FREEZE, VERBOSE, INDEX_CLEANUP off, TRUNCATE off) ; ``` - (À partir de PostgreSQL 16, ajouter `BUFFER_USAGE_LIMIT 0`). Cette commande force le gel de toutes les lignes, ignore le nettoyage des index et ne supprime pas les blocs vides finaux (le verrou peut être gênant). Un `VACUUM` classique serait à prévoir ensuite à l’occasion. On peut aussi cibler d’abord les tables les plus « anciennes » avec la requête déjà citée. **Exemple p282-2.** Exemple extrait du PDF\, page 282\. ```sql SELECT relname, relfrozenxid, round(age(relfrozenxid) /1e6,2) AS "age_Mtrx" ``` **Exemple p283-1.** Exemple extrait du PDF\, page 283\. ```sql FROM pg_class c WHERE relkind IN ('r','t') ORDER BY age(relfrozenxid) LIMIT 20 ; ``` **Exemple p283-2.** Exemple extrait du PDF\, page 283\. ```sql VACUUM (FREEZE, VERBOSE, INDEX_CLEANUP off, TRUNCATE off) nomdelatable ; ``` La clause `VERBOSE` permet d’avoir plus d’informations sur l’opération de gel. Elle est hélas assez pauvre sur ce sujet avant PostgreSQL 16. Dans cet exemple, la ligne `frozen` indique que 50,01 % de la table et 1 million de lignes ont été effectivement gelées. **Exemple p283-3.** Exemple extrait du PDF\, page 283\. ```sql VACUUM (FREEZE, VERBOSE, INDEX_CLEANUP off, TRUNCATE off) demo ; ``` **Exemple p283-4.** Exemple extrait du PDF\, page 283\. ```text INFO: aggressively vacuuming "postgres.public.demo" INFO: finished vacuuming "postgres.public.demo": index scans: 0 pages: 0 removed, 8850 remain, 4426 scanned (50.01% of total) tuples: 0 removed, 1987677 remain, 0 are dead but not yet removable removable cutoff: 290244781, which was 0 XIDs old when operation ended new relfrozenxid: 290244781, which is 3156351 XIDs ahead of previous value frozen: 4426 pages from table (50.01% of total) had 1000001 tuples frozen … ``` Il est bon de vérifier que le `new relfrozenxid` indiqué est proche du dernier numéro de transaction: **Exemple p283-5.** Exemple extrait du PDF\, page 283\. ```sql SELECT txid_current (); ``` **Exemple p283-6.** Exemple extrait du PDF\, page 283\. ```text txid_current ``` **Exemple p283-7.** Exemple extrait du PDF\, page 283\. ```text 290244783 ``` Ne pas oublier de nettoyer toutes les bases de l’instance. En toute rigueur, `VACUUM` sans l’option `FREEZE` est encore plus rapide: le mode agressif serait spontanément déclenché mais les lignes plus récentes que `vacuum_freeze_min_age` (50 millions de transaction) ne seraient pas tout de suite gelées. On peut même monter ce paramètre dans la session pour alléger au maximum la charge sur une table dont les lignes ont des âges bien étalés. Dans le pire des cas, plus aucune transaction ne devient possible (y compris les opérations d’administration comme `DROP`, ou `VACUUM` sans `TRUNCATE off`): ```text ERROR: database is not accepting commands to avoid wraparound data loss in database "db1" HINT: Stop the postmaster and vacuum that database in single-user mode. You might also need to commit or roll back old prepared transactions, or drop stale replication slots. ``` Mais il reste possible d’exécuter la commande `VACUUM (TRUNCATE OFF)` car celle-ci ne va pas consommer d’identifiant de transaction. Pour les versions 11 et inférieures, où l’option `TRUNCATE` de `VACUUM` n’était pas disponible, il était parfois nécessaire de redémarrer avec le très austère mode monoutilisateur[^7] de PostgreSQL, mais ce n’est plus nécessaire à partir de la version 12, contrairement à ce qui est suggéré par la ligne `HINT` dans les traces ci-dessus. Ce mauvais conseil a finalement été supprimé en version 17, comme expliqué dans cet article[^8]. Avec la sonde Nagios check_pgactivity[^9], et les services **max_freeze_age** et **oldest_xmin**, il est possible de vérifier que l’âge des bases ne dérive pas, ou de trouver quel processus porte le `xmin` le plus ancien. S’il y a un problème, il entraîne généralement l’apparition de nombreux messages dans les traces: lisez-les régulièrement! Comme référence, consulter le chapitre Éviter les cycles des identifiants de transactions[^10] de la documentation officielle. #### 5.7.5 FREEZE massif de l’essentiel de la base - Attention après un import massif/migration logique! - `VACUUM FREEZE` préventif en période de maintenance - éventuellement par étapes Le gel des lignes s’opère en général au fil de l’eau, et les écritures sont réparties dans le temps. Ce n’est pas toujours le cas. Si une base est chargée en bloc, et que l’essentiel de sa volumétrie est peu modifiée, il y a des chances que le `FREEZE` se déclenche sur toutes les tables en même temps et génèrent énormément d’écritures. Le délai avant le déclenchement du gel par l’autovacuum dépend de la consommation des numéros de transaction sur l’instance migrée, et varie de quelques semaines à des années. Le problème concerne bien sûr surtout les grosses bases. Sont concernés tout ce qui charge beaucoup de lignes en très peu de transactions proches: imports massifs, restauration de base (`pg_restore`), migration par réplication logique, migration depuis d’autres bases de données… Mais le souci ne concerne pas les migrations et restaurations physiques, qui préservent les numéros de transaction des lignes. [^7]: https://docs.postgresql.fr/current/app-postgres.html#APP-POSTGRES-SINGLE-USER [^8]: https://www.cybertec-postgresql.com/en/database-is-not-accepting-commands/ [^9]: https://github.com/OPMDG/check_pgactivity [^10]: https://docs.postgresql.fr/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND Après une migration ou un chargement massif, des ordres `VACUUM FREEZE` sur les plus grosses tables à des moments calmes permettent d’étaler ces écritures. Si ces ordres sont interrompus, l’essentiel de ce qu’ils auront pu geler n’est plus à re-geler plus tard. ### 5.8 RÉSUMÉ DES CONSEILS SUR L’AUTOVACUUM “Vacuuming is like exercising. If it hurts, you’re not doing it enough!” _(Robert Haas, PGConf.EU 2023, Prague, 13 décembre 2023)_ Certains sont frileux à l’idée de passer un `VACUUM`. En général, cela amène à plus de soucis que ceux que l’on croyait éviter. #### 5.8.1 Résumé des conseils sur l’autovacuum (1/2) - Laisser l’autovacuum faire son travail - Augmenter le débit autorisé - Surveiller `pg_stat_all_tables` - Nombre de _workers_ - Grosses tables, par ex: **Exemple p286-1.** Exemple extrait du PDF\, page 286\. ```sql ALTER TABLE table_name SET (autovacuum_analyze_scale_factor = 0.01) ; ALTER TABLE table_name SET (autovacuum_vacuum_threshold = 1000000) ; ``` L’autovacuum fonctionne convenablement pour les charges habituelles. Il ne faut pas s’étonner qu’il fonctionne longtemps en arrière-plan: il est justement conçu pour ne pas se presser. Au besoin, ne pas hésiter à lancer manuellement l’opération, donc sans bridage en débit. Si les disques sont bons, on peut augmenter le débit autorisé: - de préférence, en augmentant le coût à atteindre avant une pause (`autovacuum_vacuum_cost_limit`); - en augmentant `vacuum_buffer_usage_limit` (en version 16); - mais la durée de pause (`autovacuum_vacuum_cost_delay`) ne peut plus guère être réduite depuis sa réduction à 2 ms avec PostgreSQL 12. Comme le déclenchement d’ `autovacuum` est très lié à l’activité, il faut vérifier qu’il passe assez souvent sur les tables sensibles en surveillant `pg_stat_all_tables.last_autovacuum` et `last_autoanalyze`. Si les statistiques peinent à se rafraîchir, ne pas hésiter à activer plus souvent l’autovacuum sur les grosses tables problématiques ainsi: **Exemple p286-2.** Exemple extrait du PDF\, page 286\. ```sql -- analyze après 5 % de modification au lieu du défaut de 10 % ``` **Exemple p286-3.** Exemple extrait du PDF\, page 286\. ```sql ALTER TABLE table_name SET (autovacuum_analyze_scale_factor = 0.05) ; ``` De même, si la fragmentation s’envole, descendre `autovacuum_vacuum_scale_factor`. (On peut préférer utiliser les variantes en `*_threshold` de ces paramètres, et mettre les `*_scale_factor` à 0). Dans un modèle avec de très nombreuses tables actives, le nombre de _workers_ doit parfois être augmenté. #### 5.8.2 Résumé des conseils sur l’autovacuum (2/2) - Planifier nuit ou week-end - Mode manuel - batchs / tables temporaires - si pressé! - Danger du `FREEZE` brutal après migration logique ou gros import - prévenir - `VACUUM FULL`: dernière extrémité L’autovacuum n’est pas toujours assez rapide à se déclencher, par exemple entre les différentes étapes d’un batch: on intercalera des `VACUUM ANALYZE` manuels. Il faudra le faire systématiquement pour les tables temporaires (que l’autovacuum ne voit pas). Au final, on peut conseiller de planifier `vacuumdb`, `vacuum --analyze`, ou script SQL `VACUUM`, à un moment calme, chaque jour ou chaque week-end au moins. Il sera rapide s’il y a peu à faire, et peut être rendu agressif (penser à `--jobs`, `--buffer-usage-limit` …). Ce qu’il traite ne sera plus à nettoyer en journée, et la planification apporte la garantie qu’aucune table n’est négligée. Cette opération ne dispense pas de contrôler et affiner l’activité de l’autovacuum sur les grosses tables très sollicitées. Ces deux maintenances sont complémentaires. Un point d’attention reste le gel brutal de grosses quantités de données chargées ou modifiées en même temps. Un `VACUUM FREEZE` préventif dans une période calme reste la meilleure solution. Un `VACUUM FULL` sur une grande table est une opération très lourde, à réserver à la récupération d’une partie significative de son espace, qui ne serait pas réutilisé plus tard. ### 5.9 CONCLUSION - `VACUUM` fait de plus en plus de choses au fil des versions - Convient généralement - Paramétrage apparemment complexe - en fait relativement simple avec un peu d’habitude #### 5.9.1 Questions N’hésitez pas, c’est le moment! ### 5.10 QUIZ **https://dali.bo/m5_quiz** ### 5.11 TRAVAUX PRATIQUES La version en ligne des solutions de ces TP est disponible sur https://dali.bo/m5_solutions. #### 5.11.1 Traiter la fragmentation **But**: Traiter la fragmentation Créer une table `t3` avec une colonne `id` de type `integer`. Désactiver l’autovacuum pour la table `t3`. Insérer un million de lignes dans la table `t3` avec la fonction `generate_series`. Récupérer la taille de la table `t3`. Supprimer les 500 000 premières lignes de la table `t3`. Récupérer la taille de la table `t3`. Que faut-il en déduire? Exécuter un `VACUUM VERBOSE` sur la table `t3`. Quelle est l’information la plus importante? Récupérer la taille de la table `t3`. Que faut-il en déduire? Exécuter un `VACUUM FULL VERBOSE` sur la table `t3`. Récupérer la taille de la table `t3`. Que faut-il en déduire? Créer une table `t4` avec une colonne `id` de type `integer`. - Désactiver l’autovacuum pour la table `t4`. - Insérer un million de lignes dans la table `t4` avec `generate_series`. - Récupérer la taille de la table `t4`. - Supprimer les **500 000 dernières** lignes de la table `t4`. - Récupérer la taille de la table `t4`. Que faut-il en déduire? - Exécuter un `VACUUM` sur la table `t4`. - Récupérer la taille de la table `t4`. Que faut-il en déduire? #### 5.11.2 Détecter la fragmentation **But** : Détecter la fragmentation. - Créer une table `t5` avec deux colonnes : `c1` de type `integer` et `c2` de type `text`. - Désactiver l’autovacuum pour la table `t5`. - Insérer un million de lignes dans la table `t5` avec `generate_series`. - Installer l’extension `pg_freespacemap` (documentation : ). Que rapporte la fonction `pg_freespace()` quant à l’espace libre de la table `t5` ? - Modifier exactement 200 000 lignes de la table `t5`. - Que rapporte `pg_freespace` quant à l’espace libre de la table `t5`? Exécuter un `VACUUM` sur la table `t5`. Que rapporte `pg_freespace` quant à l’espace libre de la table `t5`? Récupérer la taille de la table `t5`. Exécuter un `VACUUM (FULL, VERBOSE)` sur la table `t5`. Récupérer la taille de la table `t5` et l’espace libre rapporté par `pg_freespacemap`. Que faut-il en déduire? #### 5.11.3 Gestion de l’autovacuum **But**: Voir fonctionner l’autovacuum Créer une table `t6` avec une colonne `id` de type `integer`. Insérer un million de lignes dans la table `t6`: **Exemple p292-1.** Exemple extrait du PDF\, page 292\. ```sql INSERT INTO t6(id) SELECT generate_series (1, 1000000) ; ``` Que contient la vue `pg_stat_user_tables` pour la table `t6`? Il faudra peut-être attendre une minute. (Si la version de PostgreSQL est antérieure à la 13, il faudra lancer un `VACUUM t6`.) Vérifier le nombre de lignes dans `pg_class.reltuples`. - Modifier 150 000 lignes de la table `t6` avec: **Exemple p292-2.** Exemple extrait du PDF\, page 292\. ```sql UPDATE t6 SET id = 0 WHERE id <= 150000 ; ``` - Attendre une minute. - Que contient la vue `pg_stat_user_tables` pour la table `t6`? - Que faut-il en déduire? - Modifier 60 000 lignes supplémentaires de la table `t6` avec: **Exemple p293-1.** Exemple extrait du PDF\, page 293\. ```sql UPDATE t6 SET id=1 WHERE id > 940000 ; ``` - Attendre une minute. - Que contient la vue `pg_stat_user_tables` pour la table `t6`? - Que faut-il en déduire? Descendre le facteur d’échelle de la table `t6` à 10 % pour le `VACUUM`. - Modifier encore 200 000 autres lignes de la table `t6`: **Exemple p293-2.** Exemple extrait du PDF\, page 293\. ```sql UPDATE t6 SET id=1 WHERE id > 740000 ; ``` - Attendre une minute. - Que contient la vue `pg_stat_user_tables` pour la table `t6`? - Que faut-il en déduire? ### 5.12 TRAVAUX PRATIQUES (SOLUTIONS) #### 5.12.1 Traiter la fragmentation Créer une table `t3` avec une colonne `id` de type `integer`. **Exemple p294-1.** Exemple extrait du PDF\, page 294\. ```sql CREATE TABLE t3(id integer); ``` **Exemple p294-2.** Exemple extrait du PDF\, page 294\. ```sql CREATE TABLE ``` Désactiver l’autovacuum pour la table `t3`. **Exemple p294-3.** Exemple extrait du PDF\, page 294\. ```sql ALTER TABLE t3 SET (autovacuum_enabled = false); ``` **Exemple p294-4.** Exemple extrait du PDF\, page 294\. ```sql ALTER TABLE ``` La désactivation de l’autovacuum ici a un but uniquement pédagogique. En production, c’est une très mauvaise idée! Insérer un million de lignes dans la table `t3` avec la fonction `generate_series`. **Exemple p294-5.** Exemple extrait du PDF\, page 294\. ```sql INSERT INTO t3 SELECT generate_series(1, 1000000); ``` **Exemple p294-6.** Exemple extrait du PDF\, page 294\. ```sql INSERT 0 1000000 ``` Récupérer la taille de la table `t3`. **Exemple p294-7.** Exemple extrait du PDF\, page 294\. ```sql SELECT pg_size_pretty(pg_table_size('t3')); ``` **Exemple p294-8.** Exemple extrait du PDF\, page 294\. ```text pg_size_pretty ``` **Exemple p294-9.** Exemple extrait du PDF\, page 294\. ```text 35MB ``` Supprimer les 500 000 premières lignes de la table `t3`. **Exemple p294-10.** Exemple extrait du PDF\, page 294\. ```sql DELETE FROM t3 WHERE id <= 500000; ``` **Exemple p294-11.** Exemple extrait du PDF\, page 294\. ```sql DELETE 500000 ``` Récupérer la taille de la table `t3`. Que faut-il en déduire? **Exemple p294-12.** Exemple extrait du PDF\, page 294\. ```sql SELECT pg_size_pretty(pg_table_size('t3')); ``` **Exemple p294-13.** Exemple extrait du PDF\, page 294\. ```text pg_size_pretty ``` **Exemple p294-14.** Exemple extrait du PDF\, page 294\. ```text 35MB ``` `DELETE` seul ne permet pas de regagner de la place sur le disque. Les lignes supprimées sont uniquement marquées comme étant mortes. Comme l’autovacuum est ici désactivé, PostgreSQL n’a pas encore nettoyé ces lignes. Exécuter un `VACUUM VERBOSE` sur la table `t3`. Quelle est l’information la plus importante? **Exemple p295-1.** Exemple extrait du PDF\, page 295\. ```sql VACUUM VERBOSE t3; ``` **Exemple p295-2.** Exemple extrait du PDF\, page 295\. ```text INFO: vacuuming "public.t3" INFO: "t3": removed 500000 row versions in 2213 pages INFO: "t3": found 500000 removable, 500000 nonremovable row versions in 4425 out of 4425 pages DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 3815272 There were 0 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.10 s. VACUUM ``` #### L’indication: **Exemple p295-3.** Exemple extrait du PDF\, page 295\. ```text removed 500000 row versions in 2213 pages ``` Elle indique que 500 000 lignes ont été nettoyées dans 2213 blocs (en gros, la moitié des blocs de la table). Pour compléter, l’indication suivante: **Exemple p295-4.** Exemple extrait du PDF\, page 295\. ```text found 500000 removable, 500000 nonremovable row versions in 4425 out of 4425 pages ``` Elle reprend l’indication sur 500 000 lignes mortes, et précise que 500 000 autres ne le sont pas. Les 4425 pages parcourues correspondent bien à la totalité des 35 Mo de la table complète. C’est la première fois que `VACUUM` passe sur cette table, il est normal qu’elle soit intégralement parcourue. Récupérer la taille de la table `t3`. Que faut-il en déduire? **Exemple p295-5.** Exemple extrait du PDF\, page 295\. ```sql SELECT pg_size_pretty(pg_table_size('t3')); ``` **Exemple p295-6.** Exemple extrait du PDF\, page 295\. ```text pg_size_pretty ---------------- 35 MB ``` `VACUUM` ne permet pas non plus de gagner en espace disque. Principalement, il renseigne la structure FSM (_free space map_) sur les emplacements libres dans les fichiers des tables. Exécuter un `VACUUM FULL VERBOSE` sur la table `t3`. **Exemple p295-7.** Exemple extrait du PDF\, page 295\. ```sql VACUUM FULL t3; ``` **Exemple p295-8.** Exemple extrait du PDF\, page 295\. ```text INFO: vacuuming "public.t3" INFO: "t3": found 0 removable, 500000 nonremovable row versions in 4425 pages DÉTAIL : 0 dead row versions cannot be removed yet. CPU: user: 0.10 s, system: 0.01 s, elapsed: 0.21 s. VACUUM ``` Récupérer la taille de la table `t3`. Que faut-il en déduire? **Exemple p295-9.** Exemple extrait du PDF\, page 295\. ```sql SELECT pg_size_pretty(pg_table_size('t3')); ``` **Exemple p296-1.** Exemple extrait du PDF\, page 296\. ```text pg_size_pretty ---------------- 17 MB ``` Là, par contre, nous gagnons en espace disque. Le `VACUUM FULL` reconstruit la table et la fragmentation disparaît. Créer une table `t4` avec une colonne `id` de type `integer`. **Exemple p296-3.** Exemple extrait du PDF\, page 296\. ```sql CREATE TABLE t4(id integer); ``` **Exemple p296-3-output.** Exemple extrait du PDF\, page 296\. ```text CREATE TABLE ``` Désactiver l’autovacuum pour la table `t4`. **Exemple p296-3a.** Exemple extrait du PDF\, page 296\. ```sql ALTER TABLE t4 SET (autovacuum_enabled = false); ``` **Exemple p296-3b.** Exemple extrait du PDF\, page 296\. ```text ALTER TABLE ``` Insérer un million de lignes dans la table `t4` avec `generate_series`. **Exemple p296-3c.** Exemple extrait du PDF\, page 296\. ```sql INSERT INTO t4(id) SELECT generate_series(1, 1000000); ``` **Exemple p296-4.** Exemple extrait du PDF\, page 296\. ```text INSERT 0 1000000 ``` Récupérer la taille de la table `t4`. **Exemple p296-5.** Exemple extrait du PDF\, page 296\. ```sql SELECT pg_size_pretty(pg_table_size('t4')); ``` **Exemple p296-5-output.** Exemple extrait du PDF\, page 296\. ```text pg_size_pretty ---------------- 35 MB ``` Supprimer les 500 000 **dernières** lignes de la table `t4`. **Exemple p296-6-command.** Exemple extrait du PDF\, page 296\. ```sql DELETE FROM t4 WHERE id > 500000; ``` **Exemple p296-6.** Exemple extrait du PDF\, page 296\. ```sql DELETE 500000 ``` Récupérer la taille de la table `t4`. Que faut-il en déduire? **Exemple p296-7.** Exemple extrait du PDF\, page 296\. ```sql SELECT pg_size_pretty(pg_table_size('t4')); ``` **Exemple p296-8.** Exemple extrait du PDF\, page 296\. ```text pg_size_pretty ---------------- 35 MB ``` Là aussi, nous n’avons rien perdu. Exécuter un `VACUUM` sur la table `t4`. **Exemple p297-1.** Exemple extrait du PDF\, page 297\. ```sql VACUUM t4; ``` **Exemple p297-2.** Exemple extrait du PDF\, page 297\. ```text VACUUM ``` Récupérer la taille de la table `t4`. Que faut-il en déduire? **Exemple p297-3.** Exemple extrait du PDF\, page 297\. ```sql SELECT pg_size_pretty(pg_table_size('t4')); ``` **Exemple p297-4.** Exemple extrait du PDF\, page 297\. ```text pg_size_pretty ``` **Exemple p297-5.** Exemple extrait du PDF\, page 297\. ```text 17MB ``` En fait, il existe un cas où il est possible de gagner de l’espace disque suite à un `VACUUM` simple : quand l’espace récupéré se trouve en fin de table et qu’il est possible de prendre rapidement un verrou exclusif sur la table pour la tronquer. C’est assez peu fréquent mais c’est une optimisation intéressante. #### 5.12.2 Détecter la fragmentation Créer une table `t5` avec deux colonnes: `c1` de type `integer` et `c2` de type `text`. **Exemple p297-6.** Exemple extrait du PDF\, page 297\. ```sql CREATE TABLE t5 (c1 integer, c2 text); ``` **Exemple p297-7.** Exemple extrait du PDF\, page 297\. ```sql CREATE TABLE ``` Désactiver l’autovacuum pour la table `t5`. **Exemple p297-8.** Exemple extrait du PDF\, page 297\. ```sql ALTER TABLE t5 SET (autovacuum_enabled=false); ``` **Exemple p297-9.** Exemple extrait du PDF\, page 297\. ```sql ALTER TABLE ``` Insérer un million de lignes dans la table `t5` avec `generate_series`. **Exemple p297-10.** Exemple extrait du PDF\, page 297\. ```sql INSERT INTO t5(c1, c2) SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) AS i; ``` **Exemple p297-11.** Exemple extrait du PDF\, page 297\. ```sql INSERT 0 1000000 ``` - Installer l’extension `pg_freespacemap` (documentation: https://docs.postgresql.fr/current/pgfreespacemap.html) - Que rapporte la fonction `pg_freespace()` quant à l’espace libre de la table `t5`? **Exemple p297-12.** Exemple extrait du PDF\, page 297\. ```sql CREATE EXTENSION pg_freespacemap; ``` ```sql CREATE EXTENSION ``` Cette extension installe une fonction nommée `pg_freespace`, dont la version la plus simple ne demande que la table en argument, et renvoie l’espace libre dans chaque bloc, en octets, _connu de la Free Space Map_. **Exemple p298-1.** Exemple extrait du PDF\, page 298\. ```sql SELECT count(blkno), sum(avail) FROM pg_freespace('t5'::regclass); ``` **Exemple p298-2.** Exemple extrait du PDF\, page 298\. ```text count | sum -------+----- 6274 | 0 ``` La table contient donc 6274 blocs (soit 51,4 Mo) sans aucun espace vide. - Modifier exactement 200 000 lignes de la table `t5`. - Que rapporte `pg_freespace` quant à l’espace libre de la table `t5`? **Exemple p298-3.** Exemple extrait du PDF\, page 298\. ```sql UPDATE t5 SET c2 = upper(c2) WHERE c1 <= 200000; ``` **Exemple p298-4.** Exemple extrait du PDF\, page 298\. ```sql UPDATE 200000 ``` **Exemple p298-5.** Exemple extrait du PDF\, page 298\. ```sql SELECT count(blkno), sum(avail) FROM pg_freespace('t5'::regclass); ``` **Exemple p298-6.** Exemple extrait du PDF\, page 298\. ```text count | sum -------+----- 7451 | 32 ``` La table comporte donc 20 % de blocs en plus, où sont stockées les nouvelles versions des lignes modifiées. Le champ _avail_ indique qu’il n’y a quasiment pas de place libre. (Ne pas prendre la valeur de 32 octets au pied de la lettre, la _Free Space Map_ ne cherche pas à fournir une valeur précise.) Exécuter un `VACUUM` sur la table `t5`. **Exemple p298-7.** Exemple extrait du PDF\, page 298\. ```sql VACUUM VERBOSE t5; ``` **Exemple p298-8.** Exemple extrait du PDF\, page 298\. ```text INFO: vacuuming "public.t5" INFO: "t5": removed 200000 row versions in 1178 pages INFO: "t5": found 200000 removable, 1000000 nonremovable row versions in 7451 out of 7451 pages DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 8685974 There were 0 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.11 s, system: 0.03 s, elapsed: 0.33 s. INFO: vacuuming "pg_toast.pg_toast_4160544" INFO: index "pg_toast_4160544_index" now contains 0 row versions in 1 pages DÉTAIL : 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "pg_toast_4160544": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DÉTAIL : 0 dead row versions cannot be removed yet, oldest xmin: 8685974 There were 0 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM ``` Que rapporte `pg_freespace` quant à l’espace libre de la table `t5`? **Exemple p298-9.** Exemple extrait du PDF\, page 298\. ```sql SELECT count(blkno), sum(avail) FROM pg_freespace('t5'::regclass); ``` **Exemple p299-1.** Exemple extrait du PDF\, page 299\. ```text count | sum -------+--------- 7451 | 8806816 ``` Il y a toujours autant de blocs, mais environ 8,8 Mo sont à présent repérés comme libres. Il faut donc bien exécuter un `VACUUM` pour que PostgreSQL nettoie les blocs et mette à jour la structure FSM, ce qui nous permet de déduire le taux de fragmentation de la table. Récupérer la taille de la table `t5`. ```sql SELECT pg_size_pretty(pg_table_size('t5')); ``` ```text pg_size_pretty ---------------- 58 MB ``` Exécuter un `VACUUM (FULL, VERBOSE)` sur la table `t5`. ```sql VACUUM (FULL, VERBOSE) t5; ``` ```text INFO: vacuuming "public.t5" INFO: "t5": found 200000 removable, 1000000 nonremovable row versions in 7451 pages DÉTAIL : 0 dead row versions cannot be removed yet. CPU: user: 0.49 s, system: 0.19 s, elapsed: 1.46 s. VACUUM ``` Récupérer la taille de la table `t5` et l’espace libre rapporté par `pg_freespacemap`. Que faut-il en déduire? ```sql SELECT count(blkno), sum(avail) FROM pg_freespace('t5'::regclass); ``` ```text count | sum -------+----- 6274 | 0 ``` ```sql SELECT pg_size_pretty(pg_table_size('t5')); ``` ```text pg_size_pretty ---------------- 49 MB ``` `VACUUM FULL` a réécrit la table sans les espaces morts, ce qui nous a fait gagner entre 8 et 9 Mo. La taille de la table maintenant correspond bien à celle de l’ancienne table, moins la place prise par les lignes mortes. #### 5.12.3 Gestion de l’autovacuum Créer une table `t6` avec une colonne `id` de type `integer`. **Exemple p299-2.** Exemple extrait du PDF\, page 299\. ```sql CREATE TABLE t6 (id integer) ; CREATE TABLE ``` Insérer un million de lignes dans la table `t6`: **Exemple p300-1.** Exemple extrait du PDF\, page 300\. ```sql INSERT INTO t6(id) SELECT generate_series (1, 1000000) ; ``` **Exemple p300-2.** Exemple extrait du PDF\, page 300\. ```sql INSERT INTO t6(id) SELECT generate_series (1, 1000000) ; ``` **Exemple p300-3.** Exemple extrait du PDF\, page 300\. ```text INSERT 0 1000000 ``` Que contient la vue `pg_stat_user_tables` pour la table `t6`? Il faudra peut-être attendre une minute. (Si la version de PostgreSQL est antérieure à la 13, il faudra lancer un `VACUUM t6`.) ```console \x ``` **Exemple p300-4.** Exemple extrait du PDF\, page 300\. ```text Expanded display is on. ``` **Exemple p300-5.** Exemple extrait du PDF\, page 300\. ```sql SELECT * FROM pg_stat_user_tables WHERE relname = 't6' ; ``` **Tableau p300-1.** Tableau extrait du PDF\, page 300\. ```text -[ RECORD 1 ]-------+------------------------------ relid | 4160608 schemaname | public relname | t6 seq_scan | 0 seq_tup_read | 0 idx_scan | ¤ idx_tup_fetch | ¤ n_tup_ins | 1000000 n_tup_upd | 0 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1000000 n_dead_tup | 0 n_mod_since_analyze | 0 n_ins_since_vacuum | 0 last_vacuum | ¤ last_autovacuum | 2021-02-22 17:42:43.612269+01 last_analyze | ¤ last_autoanalyze | 2021-02-22 17:42:43.719195+01 vacuum_count | 0 autovacuum_count | 1 analyze_count | 0 autoanalyze_count | 1 ``` Les deux dates `last_autovacuum` et `last_autoanalyze` sont renseignées. Il faudra peut-être attendre une minute que l’autovacuum passe sur la table (voire plus sur une instance chargée par ailleurs). Le seuil de déclenchement de l’autoanalyze est: ```text autovacuum_analyze_scale_factor × nombre de lignes + autovacuum_analyze_threshold ``` Soit, par défaut, 10 % × 0 + 50 = 50. Quand il n’y a que des insertions, le seuil pour l’autovacuum est: ```text autovacuum_vacuum_insert_scale_factor × nombre de lignes + autovacuum_vacuum_insert_threshold ``` Soit 20 % × 0 + 1000 = 1000. Avec un million de nouvelles lignes, les deux seuils sont franchis. Vérifier le nombre de lignes dans `pg_class.reltuples`. #### Vérifions que le nombre de lignes est à jour dans `pg_class`: **Exemple p301-1.** Exemple extrait du PDF\, page 301\. ```sql SELECT * FROM pg_class WHERE relname = 't6' ; ``` **Tableau p301-1.** Tableau extrait du PDF\, page 301\. ```text -[ RECORD 1 ]-------+-------- oid | 4160608 relname | t6 relnamespace | 2200 reltype | 4160610 reloftype | 0 relowner | 10 relam | 2 relfilenode | 4160608 reltablespace | 0 relpages | 4425 reltuples | 1e+06 ... ``` L’autovacuum se base entre autres sur cette valeur pour décider s’il doit passer ou pas. Si elle n’est pas encore à jour, il faut lancer manuellement: ```sql ANALYZE t6; ``` C’est d’ailleurs généralement conseillé après un gros chargement. - Modifier 150 000 lignes de la table `t6` avec: **Exemple p301-2.** Exemple extrait du PDF\, page 301\. ```sql UPDATE t6 SET id = 0 WHERE id <= 150000 ; ``` - Attendre une minute. - Que contient la vue `pg_stat_user_tables` pour la table `t6`? - Que faut-il en déduire? **Exemple p301-3.** Exemple extrait du PDF\, page 301\. ```sql UPDATE t6 SET id = 0 WHERE id <= 150000 ; ``` ```text UPDATE 150000 ``` Le démon _autovacuum_ ne se déclenche pas instantanément après les écritures, attendons un peu: **Exemple p301-4.** Exemple extrait du PDF\, page 301\. ```sql SELECT pg_sleep(60) ; ``` **Exemple p301-5.** Exemple extrait du PDF\, page 301\. ```sql SELECT * FROM pg_stat_user_tables WHERE relname = 't6' ; ``` **Tableau p301-2.** Tableau extrait du PDF\, page 301\. ```text -[ RECORD 1 ]-------+------------------------------ relid | 4160608 schemaname | public relname | t6 seq_scan | 1 seq_tup_read | 1000000 idx_scan | ¤ ``` **Tableau p302-1.** Tableau extrait du PDF\, page 302\. ```text idx_tup_fetch | ¤ n_tup_ins | 1000000 n_tup_upd | 150000 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1000000 n_dead_tup | 150000 n_mod_since_analyze | 0 n_ins_since_vacuum | 0 last_vacuum | ¤ last_autovacuum | 2021-02-22 17:42:43.612269+01 last_analyze | ¤ last_autoanalyze | 2021-02-22 17:43:43.561288+01 vacuum_count | 0 autovacuum_count | 1 analyze_count | 0 autoanalyze_count | 2 ``` Seul `last_autoanalyze` a été modifié, et il reste entre 150 000 lignes morts (`n_dead_tup`). En effet, le démon autovacuum traite séparément l’ `ANALYZE` (statistiques sur les valeurs des données) et le `VACUUM` (recherche des espaces morts). Si l’on recalcule les seuils de déclenchement, on trouve pour l’autoanalyze: ```text autovacuum_analyze_scale_factor × nombre de lignes + autovacuum_analyze_threshold ``` Soit, par défaut, 10 % × 1 000 000 + 50 = 100 050, dépassé ici. Pour l’autovacuum, le seuil est de: ```text autovacuum_vacuum_insert_scale_factor × nombre de lignes + autovacuum_vacuum_insert_threshold ``` Soit 20 % × 1 000 000 + 50 = 200 050, qui n’est pas atteint. - Modifier 60 000 lignes supplémentaires de la table `t6` avec: **Exemple p302-1.** Exemple extrait du PDF\, page 302\. ```sql UPDATE t6 SET id=1 WHERE id > 940000 ; ``` - Attendre une minute. - Que contient la vue `pg_stat_user_tables` pour la table `t6`? - Que faut-il en déduire? **Exemple p302-2.** Exemple extrait du PDF\, page 302\. ```sql UPDATE t6 SET id=1 WHERE id > 940000 ; ``` **Exemple p302-3.** Exemple extrait du PDF\, page 302\. ```sql UPDATE 60000 ``` L’autovacuum ne passe pas tout de suite, les 210 000 lignes mortes au total sont bien visibles: **Exemple p302-4.** Exemple extrait du PDF\, page 302\. ```sql SELECT * FROM pg_stat_user_tables WHERE relname = 't6'; ``` **Tableau p302-2.** Tableau extrait du PDF\, page 302\. ```text -[ RECORD 1 ]-------+------------------------------ relid | 4160608 schemaname | public relname | t6 seq_scan | 3 seq_tup_read | 3000000 ``` **Tableau p303-1.** Tableau extrait du PDF\, page 303\. ```text idx_scan | ¤ idx_tup_fetch | ¤ n_tup_ins | 1000000 n_tup_upd | 210000 n_tup_del | 0 n_tup_hot_upd | 65 n_live_tup | 1000000 n_dead_tup | 210000 n_mod_since_analyze | 60000 n_ins_since_vacuum | 0 last_vacuum | ¤ last_autovacuum | 2021-02-22 17:42:43.612269+01 last_analyze | ¤ last_autoanalyze | 2021-02-22 17:43:43.561288+01 vacuum_count | 0 autovacuum_count | 1 analyze_count | 0 autoanalyze_count | 2 ``` Mais comme le seuil de 200 050 lignes modifiées à été franchi, le démon lance un `VACUUM`: **Tableau p303-2.** Tableau extrait du PDF\, page 303\. ```text -[ RECORD 1 ]-------+------------------------------ relid | 4160608 schemaname | public relname | t6 seq_scan | 3 seq_tup_read | 3000000 idx_scan | ¤ idx_tup_fetch | ¤ n_tup_ins | 1000000 n_tup_upd | 210000 n_tup_del | 0 n_tup_hot_upd | 65 n_live_tup | 896905 n_dead_tup | 0 n_mod_since_analyze | 60000 n_ins_since_vacuum | 0 last_vacuum | ¤ last_autovacuum | 2021-02-22 17:47:43.740962+01 last_analyze | ¤ last_autoanalyze | 2021-02-22 17:43:43.561288+01 vacuum_count | 0 autovacuum_count | 2 analyze_count | 0 autoanalyze_count | 2 ``` Noter que `n_dead_tup` est revenu à 0. `last_auto_analyze` indique qu’un nouvel `ANALYZE` n’a pas été exécuté: seules 60 000 lignes ont été modifiées (voir `n_mod_since_analyze`), en-dessous du seuil de 100 050. Descendre le facteur d’échelle de la table `t6` à 10 % pour le `VACUUM`. **Exemple p303-1.** Exemple extrait du PDF\, page 303\. ```sql ALTER TABLE t6 SET (autovacuum_vacuum_scale_factor=0.1); ``` **Exemple p303-2.** Exemple extrait du PDF\, page 303\. ```sql ALTER TABLE ``` - Modifier encore 200 000 autres lignes de la table `t6`: **Exemple p304-1.** Exemple extrait du PDF\, page 304\. ```sql UPDATE t6 SET id=1 WHERE id > 740000 ; ``` - Attendre une minute. - Que contient la vue `pg_stat_user_tables` pour la table `t6`? - Que faut-il en déduire? **Exemple p304-2.** Exemple extrait du PDF\, page 304\. ```sql UPDATE t6 SET id=1 WHERE id > 740000 ; ``` **Exemple p304-3.** Exemple extrait du PDF\, page 304\. ```sql UPDATE 200000 ``` **Exemple p304-4.** Exemple extrait du PDF\, page 304\. ```sql SELECT pg_sleep(60); ``` **Exemple p304-5.** Exemple extrait du PDF\, page 304\. ```sql SELECT * FROM pg_stat_user_tables WHERE relname='t6' ; ``` **Tableau p304-1.** Tableau extrait du PDF\, page 304\. ```text -[ RECORD 1 ]-------+------------------------------ relid | 4160608 schemaname | public relname | t6 seq_scan | 4 seq_tup_read | 4000000 idx_scan | ¤ idx_tup_fetch | ¤ n_tup_ins | 1000000 n_tup_upd | 410000 n_tup_del | 0 n_tup_hot_upd | 65 n_live_tup | 1000000 n_dead_tup | 0 n_mod_since_analyze | 0 n_ins_since_vacuum | 0 last_vacuum | ¤ last_autovacuum | 2021-02-22 17:53:43.563671+01 last_analyze | ¤ last_autoanalyze | 2021-02-22 17:53:43.681023+01 vacuum_count | 0 autovacuum_count | 3 analyze_count | 0 autoanalyze_count | 3 ``` Le démon a relancé un `VACUUM` et un `ANALYZE`. Avec un facteur d’échelle à 10 %, il ne faut plus attendre que la modification de 100 050 lignes pour que le `VACUUM` soit déclenché par le démon. C’était déjà le seuil pour l’ `ANALYZE`. --- _Source : [manuel complet PDF](/downloads/dalibo/manuals/dba2-handout.pdf), révision 26.09, pages PDF 245-304. Les travaux pratiques et leurs solutions proviennent exclusivement de la version complète._ Pour poursuivre la lecture, voir [le partitionnement déclaratif](/fr/dba2/06-partitionnement/#dba2-ch6).