![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2025\/02\/vacuum-300x200.png\")
<\/p>\n
Hello<\/p>\n
pour commencer cette ann\u00e9e 2025 , voici un petit article PostgreSQL ou l’on vous pr\u00e9sente comment optimiser les op\u00e9rations de maintenance que sont les VACUUM et les ANALYZE.<\/p>\n
Ces 2 op\u00e9rations sont essentielles pour conserver des performances optimales pour notre instance et garantir au planner de construire des plans d’ex\u00e9cutions optimis\u00e9s.<\/p>\n
les op\u00e9rations VACUUM et\/ou ANALYZE peuvent \u00eatre longues et sources de nombreuses \u00e9critures dans les WALs sur des tables volumineuses. <\/p>\n Ce proc\u00e9d\u00e9 s’appuie sur le principe de “ring buffer” configur\u00e9 pour PostgreSQL.<\/p>\n Attention, \u00e0 ne pas confondre, \u00e9videment, avec les “rings buffer” de SQL Server !!<\/p>\n Pour rappel, PostgreSQL utilise cette strat\u00e9gie de “ring buffer” afin de d\u00e9dier un espace m\u00e9moire pour les op\u00e9rations lourdes , telles, la lecture s\u00e9quentielle sur une table volumineuse, un CREATE TABLE AS SELECT, un COPY…. mais aussi un VACUUM !<\/p>\n En fait, cet espace est utilis\u00e9 pour \u00e9viter de “flusher” sur disque de mani\u00e8re trop brutale les pages en m\u00e9moire mont\u00e9es dans le “shared buffer<\/strong>“. Cela p\u00e9naliserait en grande partie toute op\u00e9ration concurrente \u00e0 notre traitement actif puisqu’elle n’aurait plus d’espace pour mettre ses propres pages en m\u00e9moire.<\/p>\n Jusqu’\u00e0 la version PostgreSQL 16, cet espace m\u00e9moire \u00e9tait d\u00e9fini \u00e0\u00a0256Ko<\/strong>. Ainsi, au cours d’une lecture s\u00e9quentielle, chaque page\u00a0 de 8Ko<\/strong> par d\u00e9faut, est mont\u00e9e en m\u00e9moire dans cet espace si le nombre de pages totales \u00e0 traiter pour la table, d\u00e9passe 1\/4 du param\u00e8tre “shared_buffer<\/strong>“.<\/p>\n Il en est de m\u00eame pour une op\u00e9ration VACUUM ou ANALYZE qui utilise \u00e9galement ce ring buffer et permet d’optimiser cette op\u00e9ration.<\/p>\n <\/p>\n <\/p>\n Depuis la version PostgreSQL 16, il est possible de configurer la taille du buffer de fa\u00e7on unitaire. Par exemple, lors d’un VACUUM, il est tout \u00e0 fait possible de choisir une valeur pour “BUFFER_USAGE_LIMIT<\/strong>“.<\/p>\n Depuis le version PostgreSQL 17, la valeur par d\u00e9faut affect\u00e9e \u00e0 “BUFFER_USAGE_LIMIT<\/strong>” est de 2Mo<\/strong>.<\/p>\n Il vous est possible de param\u00e9trer la valeur de 128Ko<\/strong> jusqu’\u00e0 16Go<\/strong>. Attention, cependant, cette valeur ne peux exc\u00e9der 1\/8<\/strong> du param\u00e8tre “shared_buffer<\/strong>“. <\/p>\n <\/p>\n Sur une instance PostgreSQL 13, nous lan\u00e7ons un VACUUM simple sur une table de 2,5Go. Nous utilisons une base exemple cr\u00e9\u00e9e via “pgbench”.<\/p>\n Nous avons utilis\u00e9 les options “DISABLE_PAGE_SKIPPING<\/strong>” pour analyser, dans un premier temps, tous les blocs de notre table et ne pas sur baser sur les informations de la visibility_map.<\/p>\n <\/p>\n <\/p>\n Le temps pass\u00e9 pour cette op\u00e9ration 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.<\/p>\n Nous effectuons la m\u00eame op\u00e9ration sur cette m\u00eame table, mais sur un moteur PostgreSQL 17<\/strong>. Nous positionnons le param\u00e8tre BUFFER_USAGE_LIMIT<\/strong>\u00a0 \u00e0 8Mo<\/strong>.<\/p>\n <\/p>\n <\/p>\n C’est\u00a0 \u00e0 peu de chose pr\u00e8s, dans le m\u00eame temps d’ex\u00e9cution. soir 18 secondes.<\/p>\n La suite consiste \u00e0 red\u00e9marrer l’instance PostgreSQL 17 et constater les temps d’ex\u00e9cution pour chaque occurrence de lancement. Sur la version PostgreSQL 13, nous voyons qu’\u00e0 la premi\u00e8re ex\u00e9cution, juste apr\u00e8s red\u00e9marrage, nous sommes \u00e0 32 millisecondes. Et \u00e0 chaque ex\u00e9cution suivante, nous ne descendons pas en dessous de 15 millisecondes….<\/p>\n <\/p>\n En version PostgreSQL 17, nous faisons \u00e9galement un “flush” des pages dans le buffer cache \u00e0 chaque ex\u00e9cution, tout en modifiant la valeur de “BUFFER_USAGE_LIMIT<\/strong>“.<\/p>\n <\/p>\n <\/p>\n Le constat est simple, plus nous augmentons le “BUFFER_USAGE_LIMIT<\/strong>“, et plus le temps d’ex\u00e9cution du VACUUM diminue.<\/p>\n Nous comprendrons donc que sur une table de plus de 100Go, le gain peut \u00eatre assez important.<\/p>\n <\/p>\n <\/p>\n Pour l’instance PostgreSQL 13, nous ex\u00e9cutons le calcul de statistiques sur cette m\u00eame table<\/p>\n <\/p>\n <\/p>\n Nous sommes autour de 29 secondes pour analyser 30000 pages sur les 32769 que composent cette table. <\/p>\n Sur la version PostgreSQL 17, les r\u00e9sultats sont les suivants<\/p>\n <\/p>\n Les diff\u00e9rences de gains sont moins impressionnantes que sur un simple VACUUM \u00e0 chaque changement de “BUFFER_USAGE_LIMIT<\/strong>“, mais on voit qu’en version PostgreSQL 17, nous sommes tout de m\u00eame 4 fois plus rapide qu’en version PostgreSQL 13.<\/p>\n <\/p>\n Gardez \u00e0 l’esprit que la valeur de “BUFFER_USAGE_LIMIT”<\/strong> est plafonn\u00e9e \u00e0 1\/8 <\/strong>de “shared_buffer”<\/strong>. Inutile donc de mettre \u00e0 1024Mo, si vous ne poss\u00e9dez que 8Go de RAM.<\/p>\n Attention si vous mettez une valeur trop grande, les transactions concurrentes effectuant des lectures s\u00e9quentielles seront p\u00e9nalis\u00e9es par les op\u00e9rations VACUUM. D’ailleurs, il est possible de mettre “BUFFER_USAGE_LIMIT<\/strong>” \u00e0 0, mais ceci n’est pas conseill\u00e9 lors d’une activit\u00e9 transactionnelle en cours.<\/p>\n Pour aller plus loin dans l’optimisation d’une op\u00e9ration de vacuum, vous pouvez \u00e9galement passer le param\u00e8tre “INDEX_CLEANUP<\/strong>” \u00e0 off<\/strong>. Ceci aura pour effet de ne pas s’occuper de traiter les entr\u00e9es des index qui pointent sur les lignes mortes de la table. De plus, il est possible de positionner l’option “SKIP_DATABASE_STATS<\/strong>” afin d’indiquer \u00e0 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).<\/p>\n Les op\u00e9rations VACUUM sur les grosses tables seront bien entendu optimis\u00e9es mais attention aux plages de maintenance choisies !!<\/p>\n <\/p>\n Bonne journ\u00e9e \u00e0 vous.<\/p>\n Emmanuel Rami<\/p>\n <\/p>\n Hello pour commencer cette ann\u00e9e 2025 , voici un petit article PostgreSQL ou l’on vous pr\u00e9sente comment optimiser les op\u00e9rations de maintenance que sont les VACUUM et les ANALYZE. Ces 2 op\u00e9rations sont essentielles pour conserver des performances optimales pour… Continuer la lecture
\nC’est pourquoi, et ce depuis la version PostgreSQL 16<\/strong>, il est possible de modifier le comportement de ces op\u00e9rations en affectant une taille de buffer. Il s’agit du “buffer_usage_limit<\/a>“.<\/p>\nPrincipe de fonctionnement.<\/h2>\n
Nouveaut\u00e9s PostgreSQL 16 et PostgreSQL 17<\/h3>\n
\nSi vous faites le calcul, pour un serveur comportant 32Go de RAM<\/strong>, vous ne pourrez obtenir, au plus, 1Go<\/strong> pour votre ring buffer.<\/p>\nCas d’utilisation pour un VACUUM<\/h5>\n
(postgres@[local]:5433) [pgbenchmark] primaire $ vacuum (verbose,DISABLE_PAGE_SKIPPING) public.pgbench_accounts;\r\nINFO: 00000: aggressively vacuuming "public.pgbench_accounts"\r\nLOCATION: lazy_scan_heap, vacuumlazy.c:797\r\nINFO: 00000: "pgbench_accounts": found 0 removable, 20000000 nonremovable row versions in 327869 out of 327869 pages\r\nDETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 196215\r\nThere were 0 unused item identifiers.\r\nSkipped 0 pages due to buffer pins, 0 frozen pages.\r\n0 pages are entirely empty.\r\nCPU: user: 1.34 s, system: 0.67 s, elapsed: 18.90 s.\r\nLOCATION: lazy_scan_heap, vacuumlazy.c:1759\r\nVACUUM\r\nTime: 18920.292 ms (00:18.920)<\/pre>\n
(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (verbose,DISABLE_PAGE_SKIPPING,BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts;\r\nINFO: 00000: aggressively vacuuming "pgbenchmark.public.pgbench_accounts"\r\nLOCATION: heap_vacuum_rel, vacuumlazy.c:475\r\nINFO: 00000: finished vacuuming "pgbenchmark.public.pgbench_accounts": index scans: 0\r\npages: 0 removed, 327869 remain, 327869 scanned (100.00% of total)\r\ntuples: 0 removed, 20000000 remain, 0 are dead but not yet removable\r\nremovable cutoff: 208163, which was 0 XIDs old when operation ended\r\nnew relfrozenxid: 208163, which is 5 XIDs ahead of previous value\r\nfrozen: 0 pages from table (0.00% of total) had 0 tuples frozen\r\nindex scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed\r\navg read rate: 134.208 MB\/s, avg write rate: 0.034 MB\/s\r\nbuffer usage: 330350 hits, 325508 misses, 83 dirtied\r\nWAL usage: 84 records, 83 full page images, 684232 bytes\r\nsystem usage: CPU: user: 1.41 s, system: 0.64 s, elapsed: 18.94 s\r\nLOCATION: heap_vacuum_rel, vacuumlazy.c:763\r\nVACUUM\r\nTime: 18955.006 ms (00:18.955)<\/pre>\n
\nNous ex\u00e9cutons donc, les m\u00eames ordres VACUUM, mais sans l’option “DISABLE_PAGE_SKIPPING<\/strong>“<\/p>\n(postgres@[local]:5433) [pgbenchmark] primaire $ vacuum public.pgbench_accounts;\r\nVACUUM\r\nTime: 32.149 ms\r\n\r\n(postgres@[local]:5433) [pgbenchmark] primaire $ vacuum public.pgbench_accounts;\r\nVACUUM\r\nTime: 15.001 ms\r\n\r\n(postgres@[local]:5433) [pgbenchmark] primaire $ vacuum public.pgbench_accounts;\r\nVACUUM\r\nTime: 15.295 ms<\/pre>\n
(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (BUFFER_USAGE_LIMIT '128kB') public.pgbench_accounts;\r\nVACUUM\r\nTime: 18.098 ms\r\n\r\n(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts;\r\nVACUUM\r\nTime: 6.461 ms\r\n\r\n(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (BUFFER_USAGE_LIMIT '16MB') public.pgbench_accounts;\r\nVACUUM\r\nTime: 4.333 ms<\/pre>\n
Cas d’utilisation pour un ANALYZE<\/h5>\n
(postgres@[local]:5433) [pgbenchmark] primaire $ vacuum (analyze,verbose) public.pgbench_accounts;\r\nINFO: 00000: vacuuming "public.pgbench_accounts"\r\nLOCATION: lazy_scan_heap, vacuumlazy.c:802\r\nINFO: 00000: "pgbench_accounts": found 0 removable, 52 nonremovable row versions in 1 out of 327869 pages\r\nDETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 196278\r\nThere were 0 unused item identifiers.\r\nSkipped 0 pages due to buffer pins, 0 frozen pages.\r\n0 pages are entirely empty.\r\nCPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.\r\nLOCATION: lazy_scan_heap, vacuumlazy.c:1759\r\nINFO: 00000: analyzing "public.pgbench_accounts"\r\nLOCATION: do_analyze_rel, analyze.c:336\r\nINFO: 00000: "pgbench_accounts": scanned 30000 of 327869 pages, containing 1830000 live rows and 0 dead rows; 30000 rows in sample, 20000009 estimated total rows\r\nLOCATION: acquire_sample_rows, analyze.c:1190\r\nVACUUM\r\nTime: 29526.404 ms (00:29.526)<\/pre>\n
\nLe sample est choisi en fonction de la valeur de “default_statistics_target<\/strong>“, par d\u00e9faut \u00e0 100, avec 30000 lignes analys\u00e9es par d\u00e9faut.<\/p>\n(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (analyze,verbose,BUFFER_USAGE_LIMIT '128kB') public.pgbench_accounts;\r\nINFO: 00000: vacuuming "pgbenchmark.public.pgbench_accounts"\r\nLOCATION: heap_vacuum_rel, vacuumlazy.c:480\r\nINFO: 00000: finished vacuuming "pgbenchmark.public.pgbench_accounts": index scans: 0\r\npages: 0 removed, 327869 remain, 1 scanned (0.00% of total)\r\ntuples: 0 removed, 20000000 remain, 0 are dead but not yet removable\r\nremovable cutoff: 208163, which was 0 XIDs old when operation ended\r\nfrozen: 0 pages from table (0.00% of total) had 0 tuples frozen\r\nindex scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed\r\navg read rate: 46.211 MB\/s, avg write rate: 0.000 MB\/s\r\nbuffer usage: 37 hits, 100 misses, 0 dirtied\r\nWAL usage: 0 records, 0 full page images, 0 bytes\r\nsystem usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s\r\nLOCATION: heap_vacuum_rel, vacuumlazy.c:763\r\nINFO: 00000: analyzing "public.pgbench_accounts"\r\nLOCATION: do_analyze_rel, analyze.c:321\r\nINFO: 00000: "pgbench_accounts": scanned 30000 of 327869 pages, containing 1830000 live rows and 0 dead rows; 30000 rows in sample, 20000009 estimated total rows\r\nLOCATION: acquire_sample_rows, analyze.c:1301\r\nVACUUM\r\nTime: 8151.201 ms (00:08.151)\r\n\r\n(postgres@[local]:5437) [pgbenchmark] primaire $ vacuum (analyze,BUFFER_USAGE_LIMIT '8MB') public.pgbench_accounts;\r\nVACUUM\r\nTime: 7282.546 ms (00:07.283)<\/pre>\n
Remarques<\/h4>\n
\nUn “REINDEX<\/strong>” sera alors n\u00e9cessaire \u00e0 la fin du VACUUM sur les index de la table.<\/p>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"