Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w96Nn-001BZJ-2j for pgsql-hackers@arkaria.postgresql.org; Sat, 04 Apr 2026 19:10:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w96Nm-000958-00 for pgsql-hackers@arkaria.postgresql.org; Sat, 04 Apr 2026 19:10:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w96Nl-00094w-1t for pgsql-hackers@lists.postgresql.org; Sat, 04 Apr 2026 19:10:10 +0000 Received: from mail-oi1-x22c.google.com ([2607:f8b0:4864:20::22c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w96Nj-00000000bmw-0wMm for pgsql-hackers@postgresql.org; Sat, 04 Apr 2026 19:10:09 +0000 Received: by mail-oi1-x22c.google.com with SMTP id 5614622812f47-46fc5b8a06bso887768b6e.0 for ; Sat, 04 Apr 2026 12:10:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1775329805; x=1775934605; darn=postgresql.org; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=o4LP3Lz1g8H00mV7kE4X2PJmJP4KLDt82bC3TL78X+U=; b=KCzDNDv3kJnrEw+RU5b8LBOeXOrOdk+LmT54l8efuiOHDRQAlNXAmY8pGRWWowVNVE +NMICP0OD33FWJVpKzO7FZ8KnSTyjHgVcWM7wOTY5r376J+wfMQVMMpJkUtKaz/jTUr8 tqIe4lCL//HOQoblhmBL/C8VWzdG2vLXOUSY+pOuQJp/JO6yoK+j5EEJ4LiK9YpVAj7Y NVxLwnrwB0Jeszfycn6LPapY9hVh1QPYs6kVh8F5F0vYHift+VXij7De88M6hfV8QUpu NjlJMc2gK92504UoKCHhGNa0QircJk6pZSpVqKZDtZBiKo1tYviMTVH5gtIFTEa0pR/r 26Hg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1775329805; x=1775934605; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-gg:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=o4LP3Lz1g8H00mV7kE4X2PJmJP4KLDt82bC3TL78X+U=; b=H67XjqwYmZPPZ+LofKXXGkka2pokk/ig1aEVtuWxTYEC4N0R+wzsfsGFFLuLMs7inO 64xO17OeAEJfe6Vk+C+i4laXk5e31UcAe7ldOgMOU6Ml8p9GFRD+SVwivoM3wsUOi5kG 7pKQ8FM9Fly700Gl7VfPVSA1XqNUFnL13yobyLNBpvFtJm8MpRm8wcRop9HF7o0RxHSX IZCr+mRGaVTk1heY4F4LCuE0Ou7iDwXIDIdqolO3r3zPPlnF0UY1g9zlVc56SLx5AV0Y iO9qL8d3YJmojRwiMzRnPhntbOqpYV2WxSup5b9SLKjQNWUaXODd3MyD/7FWIYi4wJnL HoYQ== X-Forwarded-Encrypted: i=1; AJvYcCUF0iNSxo/A5J2rg2I+FSvt59yCUYEklukuXE5e3Lm+MBx35ada47YeKDT6wLTFVUtEqm1EtIVGMzgzdmeq@postgresql.org X-Gm-Message-State: AOJu0Yx4Zm8Cg+FdG9MuVDB1afFffNpP6eLpUt3EEUu9x0YWp1AJd4ZN 6fMj4qctV4Tma6ZpcLHn1B+PVnSubK6qiv5tZdSaEvmpQz+ZyNy1oG8l X-Gm-Gg: AeBDiestMzykL+54uEoatvS4COAvmPiV1fTYzoiog4mfr/q/JJpxXs0efw9FfyLbt1Y ZyURkO7YAq79fdfWVVns7JtjEl7zbQ1o4unCAo2JafL55sn3aWtlYEKgJrr45C1eymoVBYA5ICr 4/SsvpPKyjZFHlrzd9kbJmg3Wm8p0GMiPCgAgOy2YK4pYuPxpLFUmNLpjuDwh96qp8Nz1BEw8ue 1t0M9GI9dx1ZkoboBtDXUwzrBWS/v/G0UvAyfClqvP0qtG+8ijdqBdXJXyg95IaUQKYpq55FJYh S0telVn65myZxqb/jk0mLxv03yczVkFp4j+RE18VYj/glR2cFRxSWSN++iCUEDCU9RcANYfp/5+ jppX6lsf9Ca3sTSoJXvwRh3xZFsRjfjk9AuTKb8RyCgRM59ppr82+psN5BwNloyhnTeAXUwJe9G VlEVSH/rre4EZhyMEd8yhEheORWWJSXI/ceMn9LtAIxOiKM8lChe9bnEuOdP/M7goJowfDY1tdN GWiwSFrp2hQZ1BNySy/PQ== X-Received: by 2002:a05:6808:1525:b0:45a:6adf:4d44 with SMTP id 5614622812f47-46de0a24fb4mr5564702b6e.12.1775329804957; Sat, 04 Apr 2026 12:10:04 -0700 (PDT) Received: from nathan (162-195-168-172.lightspeed.stlsmo.sbcglobal.net. [162.195.168.172]) by smtp.gmail.com with ESMTPSA id 586e51a60fabf-422eb42db1bsm8601602fac.17.2026.04.04.12.10.03 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sat, 04 Apr 2026 12:10:04 -0700 (PDT) Date: Sat, 4 Apr 2026 14:10:02 -0500 From: Nathan Bossart To: Sami Imseih Cc: Bharath Rupireddy , Robert Treat , satyanarlapuram@gmail.com, pgsql-hackers Subject: Re: Add pg_stat_autovacuum_priority Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="wLga2yfBuT3a3SD4" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --wLga2yfBuT3a3SD4 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Sat, Apr 04, 2026 at 12:48:28PM -0500, Sami Imseih wrote: >> If we did report booleans, I would probably argue for just reporting >> dovacuum and doanalyze and calling out the criteria for why they may be >> false even when it looks like the table needs processing. > > Yes, we only require a needs_analyze and needs_vacuum. The latter > can be set to true due to thresholds or wraparound. But, I don't think we > should rely on the dovacuum or doanalyze, instead we can just have a flag > in AutoVacuumScores->needs and track what is needed. This will separate > the autovacuum processing from the reporting. Sorry for going in circles about this, but I'm not seeing why we wouldn't just return the booleans that relation_needs_vacanalyze() already returns. I think the question people will have is "what will autovacuum process and in what order?", and if we aren't giving them the exact same information that autovacuum is using to make its decisions, then I'm not sure what is the point. It's true that someone might disable autovacuum for a table and that it would otherwise be processed, but so be it. Concretely, like the attached 0003. IMHO this feels much more natural than giving folks booleans that usually represents dovacuum/doanalyze but that don't in certain cases. -- nathan --wLga2yfBuT3a3SD4 Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v12-0001-refactor-autovacuum-subroutine-in-preparation-fo.patch From 5964ff180193c0a976beef38609d4fcab38383a5 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Thu, 2 Apr 2026 14:17:11 -0500 Subject: [PATCH v12 1/3] refactor autovacuum subroutine in preparation for system view --- src/backend/postmaster/autovacuum.c | 73 ++++++----------------------- 1 file changed, 15 insertions(+), 58 deletions(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 8400e6722cc..1be1ba8a25f 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -370,13 +370,8 @@ static void FreeWorkerInfo(int code, Datum arg); static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map, TupleDesc pg_class_desc, int effective_multixact_freeze_max_age); -static void recheck_relation_needs_vacanalyze(Oid relid, AutoVacOpts *avopts, - Form_pg_class classForm, - int effective_multixact_freeze_max_age, - bool *dovacuum, bool *doanalyze, bool *wraparound); static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, Form_pg_class classForm, - PgStat_StatTabEntry *tabentry, int effective_multixact_freeze_max_age, int elevel, bool *dovacuum, bool *doanalyze, bool *wraparound, @@ -2029,7 +2024,6 @@ do_autovacuum(void) while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL) { Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple); - PgStat_StatTabEntry *tabentry; AutoVacOpts *relopts; Oid relid; bool dovacuum; @@ -2070,11 +2064,9 @@ do_autovacuum(void) /* Fetch reloptions and the pgstat entry for this table */ relopts = extract_autovac_opts(tuple, pg_class_desc); - tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, - relid); /* Check if it needs vacuum or analyze */ - relation_needs_vacanalyze(relid, relopts, classForm, tabentry, + relation_needs_vacanalyze(relid, relopts, classForm, effective_multixact_freeze_max_age, DEBUG3, &dovacuum, &doanalyze, &wraparound, @@ -2121,8 +2113,6 @@ do_autovacuum(void) /* Release stuff to avoid per-relation leakage */ if (relopts) pfree(relopts); - if (tabentry) - pfree(tabentry); } table_endscan(relScan); @@ -2137,7 +2127,6 @@ do_autovacuum(void) while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL) { Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple); - PgStat_StatTabEntry *tabentry; Oid relid; AutoVacOpts *relopts; bool free_relopts = false; @@ -2171,11 +2160,7 @@ do_autovacuum(void) relopts = &hentry->ar_reloptions; } - /* Fetch the pgstat entry for this table */ - tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, - relid); - - relation_needs_vacanalyze(relid, relopts, classForm, tabentry, + relation_needs_vacanalyze(relid, relopts, classForm, effective_multixact_freeze_max_age, DEBUG3, &dovacuum, &doanalyze, &wraparound, @@ -2194,8 +2179,6 @@ do_autovacuum(void) /* Release stuff to avoid leakage */ if (free_relopts) pfree(relopts); - if (tabentry) - pfree(tabentry); } table_endscan(relScan); @@ -2834,6 +2817,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, bool wraparound; AutoVacOpts *avopts; bool free_avopts = false; + AutoVacuumScores scores; /* fetch the relation's relcache entry */ classTup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid)); @@ -2859,9 +2843,11 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, avopts = &hentry->ar_reloptions; } - recheck_relation_needs_vacanalyze(relid, avopts, classForm, - effective_multixact_freeze_max_age, - &dovacuum, &doanalyze, &wraparound); + relation_needs_vacanalyze(relid, avopts, classForm, + effective_multixact_freeze_max_age, + DEBUG3, + &dovacuum, &doanalyze, &wraparound, + &scores); /* OK, it needs something done */ if (doanalyze || dovacuum) @@ -2970,41 +2956,6 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, return tab; } -/* - * recheck_relation_needs_vacanalyze - * - * Subroutine for table_recheck_autovac. - * - * Fetch the pgstat of a relation and recheck whether a relation - * needs to be vacuumed or analyzed. - */ -static void -recheck_relation_needs_vacanalyze(Oid relid, - AutoVacOpts *avopts, - Form_pg_class classForm, - int effective_multixact_freeze_max_age, - bool *dovacuum, - bool *doanalyze, - bool *wraparound) -{ - PgStat_StatTabEntry *tabentry; - AutoVacuumScores scores; - - /* fetch the pgstat table entry */ - tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, - relid); - - relation_needs_vacanalyze(relid, avopts, classForm, tabentry, - effective_multixact_freeze_max_age, - DEBUG3, - dovacuum, doanalyze, wraparound, - &scores); - - /* Release tabentry to avoid leakage */ - if (tabentry) - pfree(tabentry); -} - /* * relation_needs_vacanalyze * @@ -3089,7 +3040,6 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, Form_pg_class classForm, - PgStat_StatTabEntry *tabentry, int effective_multixact_freeze_max_age, int elevel, /* output params below */ @@ -3098,6 +3048,7 @@ relation_needs_vacanalyze(Oid relid, bool *wraparound, AutoVacuumScores *scores) { + PgStat_StatTabEntry *tabentry; bool force_vacuum; bool av_enabled; @@ -3265,6 +3216,8 @@ relation_needs_vacanalyze(Oid relid, * vacuuming only, so don't vacuum (or analyze) anything that's not being * forced. */ + tabentry = pgstat_fetch_stat_tabentry_ext(classForm->relisshared, + relid); if (!tabentry) return; @@ -3353,6 +3306,10 @@ relation_needs_vacanalyze(Oid relid, vactuples, vacthresh, scores->vac, anltuples, anlthresh, scores->anl, scores->xid, scores->mxid); + + /* Release tabentry to avoid leakage */ + if (tabentry) + pfree(tabentry); } /* -- 2.50.1 (Apple Git-155) --wLga2yfBuT3a3SD4 Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v12-0002-add-pg_stat_autovacuum_scores-system-view.patch From 7cc2c0212da9f7836a6bd4aa20303ea563f6e2a6 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Thu, 2 Apr 2026 15:47:19 -0500 Subject: [PATCH v12 2/3] add pg_stat_autovacuum_scores system view --- doc/src/sgml/maintenance.sgml | 6 ++ doc/src/sgml/monitoring.sgml | 143 +++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 15 +++ src/backend/postmaster/autovacuum.c | 70 +++++++++++++ src/include/catalog/pg_proc.dat | 7 ++ src/test/regress/expected/rules.out | 12 +++ 6 files changed, 253 insertions(+) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 0d2a28207ed..906aca2c228 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -1164,6 +1164,12 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu 2.0 effectively doubles the analyze component score. + + + The + pg_stat_autovacuum_scores + view shows the current scores of all tables in the current database. + diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 312374da5e0..b8b2967aa36 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -596,6 +596,16 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser user tables are shown. + + pg_stat_autovacuum_scorespg_stat_autovacuum_scores + + One row for each table in the current database, showing the current + autovacuum scores for that specific table. See + + pg_stat_autovacuum_scores for details. + + + pg_stat_all_indexespg_stat_all_indexes @@ -4502,6 +4512,139 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + <structname>pg_stat_autovacuum_scores</structname> + + + pg_stat_autovacuum_scores + + + + The pg_stat_autovacuum_scores view will contain one + row for each table in the current database (including TOAST tables), showing + the current autovacuum scores for that specific table. Tables deemed + eligible for processing are prioritized based on their + score, with higher scores indicating higher + priority. See for more information. + + + + <structname>pg_stat_autovacuum_scores</structname> View + + + + + Column Type + + + Description + + + + + + + + relid oid + + + Oid of the table. + + + + + + schemaname name + + + Name of the schema that the table is in. + + + + + + relname name + + + Name of the table. + + + + + + score double precision + + + Maximum value of all component scores. This is the value that + autovacuum uses to sort the list of tables to process. When the + "weight" parameters are set to their default values of + 1.0, scores greater than or equal to + 1.0 indicate the table will be processed (unless + autovacuum is disabled and neither xid_score nor + mxid_score are greater than or equal to + 1.0). + + + + + + xid_score double precision + + + Transaction ID age component score. Scores greater than or equal to + indicate the table + will be vacuumed. + + + + + + mxid_score double precision + + + Multixact ID age component score. Scores greater than or equal to + indicate + the table will be vacuumed. + + + + + + vacuum_score double precision + + + Vacuum component score. Scores greater than or equal to + indicate the table + will be vacuumed (unless autovacuum is disabled). + + + + + + vacuum_insert_score double precision + + + Vacuum insert component score. Scores greater than or equal to + indicate the + table will be vacuumed (unless autovacuum is disabled). + + + + + + analyze_score double precision + + + Analyze component score. Scores greater than or equal to + indicate the table + will be analyzed (unless autovacuum is disabled). + + + + +
+ + <structname>pg_stat_all_indexes</structname> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index eba25aa3e4d..5c2f2977965 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -795,6 +795,21 @@ CREATE VIEW pg_stat_xact_user_tables AS WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND schemaname !~ '^pg_toast'; +CREATE VIEW pg_stat_autovacuum_scores AS + SELECT + s.oid AS relid, + n.nspname AS schemaname, + c.relname AS relname, + s.score, + s.xid_score, + s.mxid_score, + s.vacuum_score, + s.vacuum_insert_score, + s.analyze_score + FROM pg_stat_get_autovacuum_scores() s + JOIN pg_class c on c.oid = s.oid + LEFT JOIN pg_namespace n ON n.oid = c.relnamespace; + CREATE VIEW pg_statio_all_tables AS SELECT C.oid AS relid, diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 1be1ba8a25f..c6c601dd3ad 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -80,6 +80,7 @@ #include "catalog/pg_namespace.h" #include "commands/vacuum.h" #include "common/int.h" +#include "funcapi.h" #include "lib/ilist.h" #include "libpq/pqsignal.h" #include "miscadmin.h" @@ -111,6 +112,7 @@ #include "utils/syscache.h" #include "utils/timeout.h" #include "utils/timestamp.h" +#include "utils/tuplestore.h" #include "utils/wait_event.h" @@ -3623,3 +3625,71 @@ check_av_worker_gucs(void) errdetail("The server will only start up to \"autovacuum_worker_slots\" (%d) autovacuum workers at a given time.", autovacuum_worker_slots))); } + +/* + * pg_stat_get_autovacuum_scores + * + * Returns current autovacuum scores for all relevant tables in the current + * database. + */ +Datum +pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS) +{ + int effective_multixact_freeze_max_age; + Relation rel; + TableScanDesc scan; + HeapTuple tup; + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + InitMaterializedSRF(fcinfo, 0); + + /* some prerequisite initialization */ + effective_multixact_freeze_max_age = MultiXactMemberFreezeThreshold(); + recentXid = ReadNextTransactionId(); + recentMulti = ReadNextMultiXactId(); + + /* scan pg_class */ + rel = table_open(RelationRelationId, AccessShareLock); + scan = table_beginscan_catalog(rel, 0, NULL); + while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + Form_pg_class form = (Form_pg_class) GETSTRUCT(tup); + AutoVacOpts *avopts; + bool dovacuum; + bool doanalyze; + bool wraparound; + AutoVacuumScores scores; + Datum vals[7]; + bool nulls[7] = {false}; + + /* skip ineligible entries */ + if (form->relkind != RELKIND_RELATION && + form->relkind != RELKIND_MATVIEW && + form->relkind != RELKIND_TOASTVALUE) + continue; + if (form->relpersistence == RELPERSISTENCE_TEMP) + continue; + + avopts = extract_autovac_opts(tup, RelationGetDescr(rel)); + relation_needs_vacanalyze(form->oid, avopts, form, + effective_multixact_freeze_max_age, 0, + &dovacuum, &doanalyze, &wraparound, + &scores); + if (avopts) + pfree(avopts); + + vals[0] = ObjectIdGetDatum(form->oid); + vals[1] = Float8GetDatum(scores.max); + vals[2] = Float8GetDatum(scores.xid); + vals[3] = Float8GetDatum(scores.mxid); + vals[4] = Float8GetDatum(scores.vac); + vals[5] = Float8GetDatum(scores.vac_ins); + vals[6] = Float8GetDatum(scores.anl); + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, vals, nulls); + } + table_endscan(scan); + table_close(rel, AccessShareLock); + + return (Datum) 0; +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index bd177aebfcb..0de3bb52eb2 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5667,6 +5667,13 @@ proname => 'pg_stat_get_total_autoanalyze_time', provolatile => 's', proparallel => 'r', prorettype => 'float8', proargtypes => 'oid', prosrc => 'pg_stat_get_total_autoanalyze_time' }, +{ oid => '8409', descr => 'autovacuum scores', + proname => 'pg_stat_get_autovacuum_scores', prorows => '100', proretset => 't', + provolatile => 'v', proparallel => 'u', prorettype => 'record', proargtypes => '', + proallargtypes => '{oid,float8,float8,float8,float8,float8,float8}', + proargmodes => '{o,o,o,o,o,o,o}', + proargnames => '{oid,score,xid_score,mxid_score,vacuum_score,vacuum_insert_score,analyze_score}', + prosrc => 'pg_stat_get_autovacuum_scores' }, { oid => '1936', descr => 'statistics: currently active backend IDs', proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't', provolatile => 's', proparallel => 'r', prorettype => 'int4', diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 81a73c426d2..b167e8d3cab 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1860,6 +1860,18 @@ pg_stat_archiver| SELECT archived_count, last_failed_time, stats_reset FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset); +pg_stat_autovacuum_scores| SELECT s.oid AS relid, + n.nspname AS schemaname, + c.relname, + s.score, + s.xid_score, + s.mxid_score, + s.vacuum_score, + s.vacuum_insert_score, + s.analyze_score + FROM ((pg_stat_get_autovacuum_scores() s(oid, score, xid_score, mxid_score, vacuum_score, vacuum_insert_score, analyze_score) + JOIN pg_class c ON ((c.oid = s.oid))) + LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))); pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_alloc() AS buffers_alloc, -- 2.50.1 (Apple Git-155) --wLga2yfBuT3a3SD4 Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename=v12-0003-add-booleans-to-view.patch From c1da752cc6f649d1ee4694091e12481232b569ca Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Sat, 4 Apr 2026 14:08:43 -0500 Subject: [PATCH v12 3/3] add booleans to view --- doc/src/sgml/monitoring.sgml | 43 ++++++++++++++++++++++------ src/backend/catalog/system_views.sql | 5 +++- src/backend/postmaster/autovacuum.c | 7 +++-- src/include/catalog/pg_proc.dat | 6 ++-- src/test/regress/expected/rules.out | 7 +++-- 5 files changed, 51 insertions(+), 17 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index b8b2967aa36..3888292bec3 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -4576,13 +4576,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage Maximum value of all component scores. This is the value that - autovacuum uses to sort the list of tables to process. When the - "weight" parameters are set to their default values of - 1.0, scores greater than or equal to - 1.0 indicate the table will be processed (unless - autovacuum is disabled and neither xid_score nor - mxid_score are greater than or equal to - 1.0). + autovacuum uses to sort the list of tables to process. @@ -4593,7 +4587,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage Transaction ID age component score. Scores greater than or equal to indicate the table - will be vacuumed. + will be vacuumed for transaction ID wraparound prevention. @@ -4604,7 +4598,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage Multixact ID age component score. Scores greater than or equal to indicate - the table will be vacuumed. + the table will be vacuumed for multixact ID wraparound prevention. @@ -4640,6 +4634,37 @@ description | Waiting for a newly initialized WAL file to reach durable storage will be analyzed (unless autovacuum is disabled). + + + + will_vacuum bool + + + Whether the table will be vacuumed. Note that even if the component + scores indicate that the table needs to be vacuumed, this may be + false if autovacuum is disabled. + + + + + + will_analyze bool + + + Whether the table will be analyzed. Note that even if the component + scores indicate that the table needs to be analyzed, this may be + false if autovacuum is disabled. + + + + + + for_wraparound bool + + + Whether the table will be vacuumed for wraparound prevention. + + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 5c2f2977965..83cc3edcbeb 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -805,7 +805,10 @@ CREATE VIEW pg_stat_autovacuum_scores AS s.mxid_score, s.vacuum_score, s.vacuum_insert_score, - s.analyze_score + s.analyze_score, + s.will_vacuum, + s.will_analyze, + s.for_wraparound FROM pg_stat_get_autovacuum_scores() s JOIN pg_class c on c.oid = s.oid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace; diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index c6c601dd3ad..26faa9870ba 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -3659,8 +3659,8 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS) bool doanalyze; bool wraparound; AutoVacuumScores scores; - Datum vals[7]; - bool nulls[7] = {false}; + Datum vals[10]; + bool nulls[10] = {false}; /* skip ineligible entries */ if (form->relkind != RELKIND_RELATION && @@ -3685,6 +3685,9 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS) vals[4] = Float8GetDatum(scores.vac); vals[5] = Float8GetDatum(scores.vac_ins); vals[6] = Float8GetDatum(scores.anl); + vals[7] = BoolGetDatum(dovacuum); + vals[8] = BoolGetDatum(doanalyze); + vals[9] = BoolGetDatum(wraparound); tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, vals, nulls); } diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 0de3bb52eb2..d79b4465b10 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5670,9 +5670,9 @@ { oid => '8409', descr => 'autovacuum scores', proname => 'pg_stat_get_autovacuum_scores', prorows => '100', proretset => 't', provolatile => 'v', proparallel => 'u', prorettype => 'record', proargtypes => '', - proallargtypes => '{oid,float8,float8,float8,float8,float8,float8}', - proargmodes => '{o,o,o,o,o,o,o}', - proargnames => '{oid,score,xid_score,mxid_score,vacuum_score,vacuum_insert_score,analyze_score}', + proallargtypes => '{oid,float8,float8,float8,float8,float8,float8,bool,bool,bool}', + proargmodes => '{o,o,o,o,o,o,o,o,o,o}', + proargnames => '{oid,score,xid_score,mxid_score,vacuum_score,vacuum_insert_score,analyze_score,will_vacuum,will_analyze,for_wraparound}', prosrc => 'pg_stat_get_autovacuum_scores' }, { oid => '1936', descr => 'statistics: currently active backend IDs', proname => 'pg_stat_get_backend_idset', prorows => '100', proretset => 't', diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index b167e8d3cab..d978aedbd07 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1868,8 +1868,11 @@ pg_stat_autovacuum_scores| SELECT s.oid AS relid, s.mxid_score, s.vacuum_score, s.vacuum_insert_score, - s.analyze_score - FROM ((pg_stat_get_autovacuum_scores() s(oid, score, xid_score, mxid_score, vacuum_score, vacuum_insert_score, analyze_score) + s.analyze_score, + s.will_vacuum, + s.will_analyze, + s.for_wraparound + FROM ((pg_stat_get_autovacuum_scores() s(oid, score, xid_score, mxid_score, vacuum_score, vacuum_insert_score, analyze_score, will_vacuum, will_analyze, for_wraparound) JOIN pg_class c ON ((c.oid = s.oid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))); pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, -- 2.50.1 (Apple Git-155) --wLga2yfBuT3a3SD4--