Hello
pour commencer cette année 2025 , voici un petit article PostgreSQL ou l’on vous présente comment optimiser les opérations de maintenance que sont les VACUUM et les ANALYZE.
Ces 2 opérations sont essentielles pour conserver des performances optimales pour notre instance et garantir au planner de construire des plans d’exécutions optimisés.
les opérations VACUUM et/ou ANALYZE peuvent être longues et sources de nombreuses écritures dans les WALs sur des tables volumineuses.
C’est pourquoi, et ce depuis la version PostgreSQL 16, il est possible de modifier le comportement de ces opérations en affectant une taille de buffer. Il s’agit du “buffer_usage_limit“.
Principe de fonctionnement.
Ce procédé s’appuie sur le principe de “ring buffer” configuré pour PostgreSQL.
Attention, à ne pas confondre, évidement, avec les “rings buffer” de SQL Server !!
Pour rappel, PostgreSQL utilise cette stratégie de “ring buffer” afin de dédier un espace mémoire pour les opérations lourdes , telles, la lecture séquentielle sur une table volumineuse, un CREATE TABLE AS SELECT, un COPY…. mais aussi un VACUUM !
En fait, cet espace est utilisé pour éviter de “flusher” sur disque de manière trop brutale les pages en mémoire montées dans le “shared buffer“. Cela pénaliserait en grande partie toute opération concurrente à notre traitement actif puisqu’elle n’aurait plus d’espace pour mettre ses propres pages en mémoire.
Jusqu’à la version PostgreSQL 16, cet espace mémoire était défini à 256Ko. Ainsi, au cours d’une lecture séquentielle, chaque page de 8Ko par défaut, est montée en mémoire dans cet espace si le nombre de pages totales à traiter pour la table, dépasse 1/4 du paramètre “shared_buffer“.
Il en est de même pour une opération VACUUM ou ANALYZE qui utilise également ce ring buffer et permet d’optimiser cette opération.
Nouveautés PostgreSQL 16 et PostgreSQL 17
Depuis la version PostgreSQL 16, il est possible de configurer la taille du buffer de façon unitaire. Par exemple, lors d’un VACUUM, il est tout à fait possible de choisir une valeur pour “BUFFER_USAGE_LIMIT“.
Depuis le version PostgreSQL 17, la valeur par défaut affectée à “BUFFER_USAGE_LIMIT” est de 2Mo.
Il vous est possible de paramétrer la valeur de 128Ko jusqu’à 16Go. Attention, cependant, cette valeur ne peux excéder 1/8 du paramètre “shared_buffer“.
Si vous faites le calcul, pour un serveur comportant 32Go de RAM, vous ne pourrez obtenir, au plus, 1Go pour votre ring buffer.
Cas d’utilisation pour un VACUUM
Sur une instance PostgreSQL 13, nous lançons un VACUUM simple sur une table de 2,5Go. Nous utilisons une base exemple créée via “pgbench”.
Nous avons utilisé les options “DISABLE_PAGE_SKIPPING” pour analyser, dans un premier temps, tous les blocs de notre table et ne pas sur baser sur les informations de la visibility_map.
(postgres@[local]:5433) [pgbenchmark] primaire $ vacuum (verbose,DISABLE_PAGE_SKIPPING) public.pgbench_accounts; INFO: 00000: aggressively vacuuming "public.pgbench_accounts" LOCATION: lazy_scan_heap, vacuumlazy.c:797 INFO: 00000: "pgbench_accounts": found 0 removable, 20000000 nonremovable row versions in 327869 out of 327869 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 196215 There were 0 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 1.34 s, system: 0.67 s, elapsed: 18.90 s. LOCATION: lazy_scan_heap, vacuumlazy.c:1759 VACUUM Time: 18920.292 ms (00:18.920)
Le temps passé pour cette opération est d’un peu plus de 18 secondes en temps CPU pour analyser les 327869 blocs de notre table. Soit une taille de 2.5Go.
Nous effectuons la même opération sur cette même table, mais sur un moteur PostgreSQL 17. Nous positionnons le paramètre BUFFER_USAGE_LIMIT à 8Mo.
(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (verbose,DISABLE_PAGE_SKIPPING,BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts; INFO: 00000: aggressively vacuuming "pgbenchmark.public.pgbench_accounts" LOCATION: heap_vacuum_rel, vacuumlazy.c:475 INFO: 00000: finished vacuuming "pgbenchmark.public.pgbench_accounts": index scans: 0 pages: 0 removed, 327869 remain, 327869 scanned (100.00% of total) tuples: 0 removed, 20000000 remain, 0 are dead but not yet removable removable cutoff: 208163, which was 0 XIDs old when operation ended new relfrozenxid: 208163, which is 5 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 134.208 MB/s, avg write rate: 0.034 MB/s buffer usage: 330350 hits, 325508 misses, 83 dirtied WAL usage: 84 records, 83 full page images, 684232 bytes system usage: CPU: user: 1.41 s, system: 0.64 s, elapsed: 18.94 s LOCATION: heap_vacuum_rel, vacuumlazy.c:763 VACUUM Time: 18955.006 ms (00:18.955)
C’est à peu de chose près, dans le même temps d’exécution. soir 18 secondes.
La suite consiste à redémarrer l’instance PostgreSQL 17 et constater les temps d’exécution pour chaque occurrence de lancement.
Nous exécutons donc, les mêmes ordres VACUUM, mais sans l’option “DISABLE_PAGE_SKIPPING“
Sur la version PostgreSQL 13, nous voyons qu’à la première exécution, juste après redémarrage, nous sommes à 32 millisecondes. Et à chaque exécution suivante, nous ne descendons pas en dessous de 15 millisecondes….
(postgres@[local]:5433) [pgbenchmark] primaire $ vacuum public.pgbench_accounts; VACUUM Time: 32.149 ms (postgres@[local]:5433) [pgbenchmark] primaire $ vacuum public.pgbench_accounts; VACUUM Time: 15.001 ms (postgres@[local]:5433) [pgbenchmark] primaire $ vacuum public.pgbench_accounts; VACUUM Time: 15.295 ms
En version PostgreSQL 17, nous faisons également un “flush” des pages dans le buffer cache à chaque exécution, tout en modifiant la valeur de “BUFFER_USAGE_LIMIT“.
(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (BUFFER_USAGE_LIMIT '128kB') public.pgbench_accounts; VACUUM Time: 18.098 ms (postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts; VACUUM Time: 6.461 ms (postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (BUFFER_USAGE_LIMIT '16MB') public.pgbench_accounts; VACUUM Time: 4.333 ms
Le constat est simple, plus nous augmentons le “BUFFER_USAGE_LIMIT“, et plus le temps d’exécution du VACUUM diminue.
Nous comprendrons donc que sur une table de plus de 100Go, le gain peut être assez important.
Cas d’utilisation pour un ANALYZE
Pour l’instance PostgreSQL 13, nous exécutons le calcul de statistiques sur cette même table
(postgres@[local]:5433) [pgbenchmark] primaire $ vacuum (analyze,verbose) public.pgbench_accounts; INFO: 00000: vacuuming "public.pgbench_accounts" LOCATION: lazy_scan_heap, vacuumlazy.c:802 INFO: 00000: "pgbench_accounts": found 0 removable, 52 nonremovable row versions in 1 out of 327869 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 196278 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. LOCATION: lazy_scan_heap, vacuumlazy.c:1759 INFO: 00000: analyzing "public.pgbench_accounts" LOCATION: do_analyze_rel, analyze.c:336 INFO: 00000: "pgbench_accounts": scanned 30000 of 327869 pages, containing 1830000 live rows and 0 dead rows; 30000 rows in sample, 20000009 estimated total rows LOCATION: acquire_sample_rows, analyze.c:1190 VACUUM Time: 29526.404 ms (00:29.526)
Nous sommes autour de 29 secondes pour analyser 30000 pages sur les 32769 que composent cette table.
Le sample est choisi en fonction de la valeur de “default_statistics_target“, par défaut à 100, avec 30000 lignes analysées par défaut.
Sur la version PostgreSQL 17, les résultats sont les suivants
(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (analyze,verbose,BUFFER_USAGE_LIMIT '128kB') public.pgbench_accounts; INFO: 00000: vacuuming "pgbenchmark.public.pgbench_accounts" LOCATION: heap_vacuum_rel, vacuumlazy.c:480 INFO: 00000: finished vacuuming "pgbenchmark.public.pgbench_accounts": index scans: 0 pages: 0 removed, 327869 remain, 1 scanned (0.00% of total) tuples: 0 removed, 20000000 remain, 0 are dead but not yet removable removable cutoff: 208163, which was 0 XIDs old when operation ended frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 46.211 MB/s, avg write rate: 0.000 MB/s buffer usage: 37 hits, 100 misses, 0 dirtied WAL usage: 0 records, 0 full page images, 0 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s LOCATION: heap_vacuum_rel, vacuumlazy.c:763 INFO: 00000: analyzing "public.pgbench_accounts" LOCATION: do_analyze_rel, analyze.c:321 INFO: 00000: "pgbench_accounts": scanned 30000 of 327869 pages, containing 1830000 live rows and 0 dead rows; 30000 rows in sample, 20000009 estimated total rows LOCATION: acquire_sample_rows, analyze.c:1301 VACUUM Time: 8151.201 ms (00:08.151) (postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (analyze,BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts; VACUUM Time: 7282.546 ms (00:07.283)
Les différences de gains sont moins impressionnantes que sur un simple VACUUM à chaque changement de “BUFFER_USAGE_LIMIT“, mais on voit qu’en version PostgreSQL 17, nous sommes tout de même 4 fois plus rapide qu’en version PostgreSQL 13.
Remarques
Gardez à l’esprit que la valeur de “BUFFER_USAGE_LIMIT” est plafonnée à 1/8 de “shared_buffer”. Inutile donc de mettre à 1024Mo, si vous ne possédez que 8Go de RAM.
Attention si vous mettez une valeur trop grande, les transactions concurrentes effectuant des lectures séquentielles seront pénalisées par les opérations VACUUM. D’ailleurs, il est possible de mettre “BUFFER_USAGE_LIMIT” à 0, mais ceci n’est pas conseillé lors d’une activité transactionnelle en cours.
Pour aller plus loin dans l’optimisation d’une opération de vacuum, vous pouvez également passer le paramètre “INDEX_CLEANUP” à off. Ceci aura pour effet de ne pas s’occuper de traiter les entrées des index qui pointent sur les lignes mortes de la table.
Un “REINDEX” sera alors nécessaire à la fin du VACUUM sur les index de la table.
De plus, il est possible de positionner l’option “SKIP_DATABASE_STATS” afin d’indiquer à l’ordre VACUUM de ne pas rechercher l’ID de transaction le plus ancien pour l’ensemble des tables de la base et de geler celui-ci (datfrozenid).
Les opérations VACUUM sur les grosses tables seront bien entendu optimisées mais attention aux plages de maintenance choisies !!
Bonne journée à vous.
Emmanuel Rami
Continuez votre lecture sur le blog :
- PostgreSQL 13 : présentation (Emmanuel RAMI) [PostgreSQL]
- “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]
- Requêtes consommatrices sous PostgreSQL (épisode 1) (David Baffaleuf) [PostgreSQL]
- Nouveautés MySQL 8.0 : Les Histogrammes (Capdata team) [MySQL]
- Réplication logique avec PostgreSQL (Capdata team) [PostgreSQL]