1"use strict";(self.webpackChunkdatabase_lab_docs=self.webpackChunkdatabase_lab_docs||[]).push([[4810],{27511(e,a,t){t.d(a,{A:()=>n});const n=t.p+"assets/images/0067_tables_to_be_autovacuumed_2-5b94267267c39498f1dc5fb632bc7cb5.png"},28453(e,a,t){t.d(a,{R:()=>r,x:()=>o});var n=t(96540);const s={},u=n.createContext(s);function r(e){const a=n.useContext(u);return n.useMemo(function(){return"function"==typeof e?e(a):{...a,...e}},[a,e])}function o(e){let a;return a=e.disableParentContext?"function"==typeof e.components?e.components(s):e.components||s:r(e.components),n.createElement(u.Provider,{value:a},e.children)}},77805(e,a,t){t.r(a),t.d(a,{assets:()=>i,contentTitle:()=>o,default:()=>d,frontMatter:()=>r,metadata:()=>n,toc:()=>c});const n=JSON.parse('{"id":"postgres-howtos/database-administration/maintenance/autovacuum-queue-and-progress","title":"Autovacuum \\"queue\\" and progress","description":"Monitor and manage autovacuum queue, understand worker activity and vacuum progress","source":"@site/docs/postgres-howtos/database-administration/maintenance/autovacuum-queue-and-progress.md","sourceDirName":"postgres-howtos/database-administration/maintenance","slug":"/postgres-howtos/database-administration/maintenance/autovacuum-queue-and-progress","permalink":"/docs/postgres-howtos/database-administration/maintenance/autovacuum-queue-and-progress","draft":false,"unlisted":false,"editUrl":"https://gitlab.com/postgres-ai/docs/-/edit/master/docs/postgres-howtos/database-administration/maintenance/autovacuum-queue-and-progress.md","tags":[{"inline":true,"label":"intermediate","permalink":"/docs/tags/intermediate"},{"inline":true,"label":"autovacuum","permalink":"/docs/tags/autovacuum"},{"inline":true,"label":"monitoring","permalink":"/docs/tags/monitoring"},{"inline":true,"label":"maintenance","permalink":"/docs/tags/maintenance"},{"inline":true,"label":"workers","permalink":"/docs/tags/workers"},{"inline":true,"label":"progress","permalink":"/docs/tags/progress"}],"version":"current","frontMatter":{"title":"Autovacuum \\"queue\\" and progress","sidebar_label":"Autovacuum \\"queue\\" and progress","description":"Monitor and manage autovacuum queue, understand worker activity and vacuum progress","keywords":["postgresql","autovacuum","\\"queue\\"","progress","intermediate"],"tags":["intermediate","autovacuum","monitoring","maintenance","workers","progress"],"difficulty":"intermediate","estimated_time":"5 min"},"sidebar":"baseSidebar","previous":{"title":"deal with bloat","permalink":"/docs/postgres-howtos/database-administration/maintenance/how-to-deal-with-bloat"},"next":{"title":"run ANALYZE (to collect statistics)","permalink":"/docs/postgres-howtos/database-administration/maintenance/how-to-run-analyze"}}');var s=t(74848),u=t(28453);const r={title:'Autovacuum "queue" and progress',sidebar_label:'Autovacuum "queue" and progress',description:"Monitor and manage autovacuum queue, understand worker activity and vacuum progress",keywords:["postgresql","autovacuum",'"queue"',"progress","intermediate"],tags:["intermediate","autovacuum","monitoring","maintenance","workers","progress"],difficulty:"intermediate",estimated_time:"5 min"},o='Autovacuum "queue" and progress',i={},c=[];function l(e){const a={a:"a",code:"code",h1:"h1",header:"header",img:"img",li:"li",p:"p",pre:"pre",ul:"ul",...(0,u.R)(),...e.components};return(0,s.jsxs)(s.Fragment,{children:[(0,s.jsx)(a.header,{children:(0,s.jsx)(a.h1,{id:"autovacuum-queue-and-progress",children:'Autovacuum "queue" and progress'})}),"\n",(0,s.jsxs)(a.p,{children:["In many workloads, ",(0,s.jsx)(a.code,{children:"autovacuum"})," settings \u2014 especially the defaults \u2014 need to be tuned to keep up with dead-tuple accumulation.\nOne way to tell whether the current settings are sufficient is to compare\n",(0,s.jsx)(a.a,{href:"https://postgresqlco.nf/doc/en/param/autovacuum_max_workers/",children:"autovacuum_max_workers"})," with the number of workers\nthat are actually running:"]}),"\n",(0,s.jsx)(a.pre,{children:(0,s.jsx)(a.code,{className:"language-sql",children:"show autovacuum_max_workers;\n\nselect\n state,\n count(*),\n array_agg(left(query, 25) order by xact_start)\nfrom pg_stat_activity\nwhere backend_type = 'autovacuum worker'\ngroup by state;\n"})}),"\n",(0,s.jsxs)(a.p,{children:["\ud83d\udc49 If the number of active workers regularly hits ",(0,s.jsx)(a.code,{children:"autovacuum_max_workers"}),", that is a strong signal to consider raising\nthe limit (which requires a restart) and/or letting workers run faster by\n",(0,s.jsx)(a.a,{href:"https://www.postgresql.org/docs/current/runtime-config-autovacuum.html",children:"adjusting quotas"}),"\n(",(0,s.jsx)("code",{children:(0,s.jsx)("b",{children:"[auto]vacuum_vacuum_cost_limit"})})," / ",(0,s.jsx)("code",{children:"[auto]vacuum_vacuum_cost_delay"}),")."]}),"\n",(0,s.jsxs)(a.p,{children:['A related question: how many tables are currently in the "queue" waiting to be processed by ',(0,s.jsx)(a.code,{children:"autovacuum"}),'?\nLooking at this queue gives a sense of how much work is pending and whether the current settings can keep up.\nQueue depth relative to the number of workers acts as a rough analog of "load average" for CPU.']}),"\n",(0,s.jsxs)(a.p,{children:["The report below (",(0,s.jsx)(a.a,{href:"https://gitlab.com/-/snippets/1889668",children:"source"}),") answers that question by combining:"]}),"\n",(0,s.jsxs)(a.ul,{children:["\n",(0,s.jsxs)(a.li,{children:["the current global ",(0,s.jsx)(a.code,{children:"autovacuum"})," settings,"]}),"\n",(0,s.jsx)(a.li,{children:"per-table vacuum overrides,"}),"\n",(0,s.jsx)(a.li,{children:"dead-tuple counts per table."}),"\n"]}),"\n",(0,s.jsx)(a.p,{children:"It cross-references them and produces a list of tables that need vacuuming."}),"\n",(0,s.jsxs)(a.p,{children:["It also inspects ",(0,s.jsx)(a.code,{children:"pg_stat_progress_vacuum"})," to show what is currently being processed."]}),"\n",(0,s.jsxs)(a.p,{children:["The query is adapted from ",(0,s.jsx)(a.a,{href:"https://github.com/avito-tech/dba-utils/blob/master/munin/vacuum_queue",children:"avito-tech/dba-utils"}),"."]}),"\n",(0,s.jsx)(a.p,{children:"A natural extension is to add the same kind of analysis for tables that need auto-analyze."}),"\n",(0,s.jsx)(a.pre,{children:(0,s.jsx)(a.code,{className:"language-sql",children:"with table_opts as (\n select\n pg_class.oid,\n relname,\n nspname,\n array_to_string(reloptions, '') as relopts\n from pg_class\n join pg_namespace as ns on relnamespace = ns.oid\n), vacuum_settings as (\n select\n oid,\n relname,\n nspname,\n case\n when relopts like '%autovacuum_vacuum_threshold%'\n then regexp_replace(relopts, '.*autovacuum_vacuum_threshold=([0-9.]+).*', e'\\\\1')::int8\n else current_setting('autovacuum_vacuum_threshold')::int8\n end as autovacuum_vacuum_threshold,\n case\n when relopts like '%autovacuum_vacuum_scale_factor%'\n then regexp_replace(relopts, '.*autovacuum_vacuum_scale_factor=([0-9.]+).*', e'\\\\1')::numeric\n else current_setting('autovacuum_vacuum_scale_factor')::numeric\n end as autovacuum_vacuum_scale_factor,\n case\n when relopts ~ 'autovacuum_enabled=(false|off)' then false\n else true\n end as autovacuum_enabled\n from table_opts\n), progress as (\n select *\n from pg_stat_progress_vacuum\n)\nselect\n coalesce(\n coalesce(nullif(vacuum_settings.nspname, 'public') || '.', '') || vacuum_settings.relname, -- current DB\n format('[something in \"%I\"]', progress.datname) -- another DB\n ) as relation,\n round((100 * psat.n_dead_tup::numeric / nullif(pg_class.reltuples, 0))::numeric, 2) as dead_tup_pct,\n pg_class.reltuples::numeric,\n psat.
1n_dead_tup,\n format(\n 'vt: %s, vsf: %s, %s', -- 'vt' \u2013 vacuum_threshold, 'vsf' \u2013 vacuum_scale_factor\n vacuum_settings.autovacuum_vacuum_threshold,\n vacuum_settings.autovacuum_vacuum_scale_factor,\n case when autovacuum_enabled then 'enabled' else 'DISABLED' end\n ) as effective_settings,\n case\n when last_autovacuum > coalesce(last_vacuum, '0001-01-01') then left(last_autovacuum::text, 19) || ' (auto)'\n when last_vacuum is not null then left(last_vacuum::text, 19) || ' (manual)'\n else null\n end as last_vacuumed,\n coalesce(progress.phase, '~~~ in queue ~~~') as status,\n progress.pid,\n case\n when activity.query ~ '^autovacuum.*to prevent wraparound' then 'wraparound'\n when activity.query ~ '^vacuum' then 'user'\n when activity.pid is null then null\n else 'regular'\n end as mode,\n case\n when activity.pid is null then null\n else coalesce(wait_event_type || '.' || wait_event, 'f')\n end as waiting,\n round(100.0 * progress.heap_blks_scanned / nullif(progress.heap_blks_total, 0), 1) as scanned_pct,\n round(100.0 * progress.heap_blks_vacuumed / nullif(progress.heap_blks_total, 0), 1) as vacuumed_pct,\n progress.index_vacuum_count,\n case\n when psat.relid is not null and progress.relid is not null\n then (select count(*) from pg_index where indrelid = psat.relid)\n else null\n end as index_count\nfrom pg_stat_all_tables as psat\njoin pg_class on psat.relid = pg_class.oid\nleft join vacuum_settings on pg_class.oid = vacuum_settings.oid\nfull outer join progress\n on progress.relid = psat.relid\n and progress.datname = current_database()\nleft join pg_stat_activity as activity using (pid)\nwhere\n psat.relid is null\n or progress.phase is not null\n or (\n autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor::numeric * pg_class.reltuples)\n < psat.n_dead_tup\n )\norder by status, relation;\n"})}),"\n",(0,s.jsxs)(a.p,{children:["Example output (run in psql with ",(0,s.jsx)(a.code,{children:"\\gx"})," instead of ",(0,s.jsx)(a.code,{children:";"})," at the end):"]}),"\n",(0,s.jsx)(a.p,{children:(0,s.jsx)(a.img,{alt:"tables to be autovacuumed",src:t(27511).A+"",width:"714",height:"916"})})]})}function d(e={}){const{wrapper:a}={...(0,u.R)(),...e.components};return a?(0,s.jsx)(a,{...e,children:(0,s.jsx)(l,{...e})}):l(e)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.