#2118 [Proposal] New discussion created by Alena0704 Proposal Status Under Discussion Abstract I propose introducing VACUUM metric collection in Cloudberry to evaluate the work and resource cost of VACUUM over time and measure the effect of autovacuum tuning. The proposal adds measurements and statistics to core and exposes them partly through built-in functions and views and partly through

ext_vacuum_statistics

. Basic timing and work counters remain available independently of the extension. The extension accumulates additional buffer, I/O, and WAL measurements, exposes them alongside native counters, and provides a way to add further metrics and collection controls. Cumulative statistics for tables, indexes, and databases would support analysis of maintenance trends, including differences between segments. SQL access allows users to analyze the statistics directly or collect historical samples with their preferred monitoring tool. Integration with

pg_profile

is proposed as an optional follow-up. A partial implementation for

REL_2_STABLE

exposes its statistics through a separate

vacuum_stats

extension without changing the system catalog version. Motivation Understand the results and cost of maintenance VACUUM must keep up with dead tuples, freezing, and visibility-map maintenance while sharing resources with application queries. The appropriate autovacuum settings depend on each table's workload and its indexes. Administrators need measurements that show both the useful work performed and its cost. This is the motivation described in the initial PostgreSQL proposal and the follow-up explanation of per-relation counters. Existing invocation counts and timestamps show whether maintenance ran. Progress views describe operations currently running. Accumulated work and resource measurements would provide the history needed to answer questions such as: • Which tables and indexes account for most VACUUM time, buffer activity, and WAL generation? • Do repeated runs remove tuples, or keep encountering versions that cannot yet be removed? • Does a run with few removals still perform useful freezing or visibility-map work? • How much elapsed time comes from cost-based delays? • Does AO/AOCO compaction remove hidden rows, and what work remains afterwards? • Are maintenance costs or unfinished cleanup concentrated on particular segments? These measurements would help select relations for investigation and assess changes to their autovacuum settings. Activity views, logs, and physical inspection would supply the additional evidence needed to establish causes. Analyze changes over time Regular samples of cumulative counters would allow users to compare increments and growth rates over successive intervals. This would show how maintenance cost changes over hours, days, and weeks, how that change relates to application workload, and whether tuning has a sustained effect. Comparing periods before and after a settings change or REINDEX is one application of this history. As an optional follow-up, I propose adding the new metrics to pg_profile, which provides a historical sample repository and interval reports. Users could also query the SQL views directly, save their own samples, or use other monitoring tools. The core and extension statistics do not depend on

pg_profile

. Make statistics extensible The extension provides a place to develop additional metrics and their SQL interfaces without requiring a core catalog change for each new field. Measurements already supplied by core can be accumulated and exposed through an extension update. New measurement points may still require core changes. The extension also provides a place to develop flexible collection policies, such as collecting additional statistics only on selected segments or in selected databases, or enabling collection for selected extensions. These filters are proposed future work. They would let operators choose the scope of additional collection independently of the basic native statistics. Implementation Series for

main

The implementation separates instrumentation, native cumulative counters, and additional statistics owned by the extension. | PR | Contribution | | ------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------- | | [#2102](https://github.com/apache/cloudberry/pull/2102) | Pluggable cumulative statistics, allowing extensions to reuse the common storage, reset, and persistence infrastructure | | [#2103](https://github.com/apache/cloudberry/pull/2103) | Optional VACUUM buffer, I/O, and WAL instrumentation and resource reporting under VACUUM (VERBOSE) | | [#2104](https://github.com/apache/cloudberry/pull/2104) | Native maintenance timing, cost-delay, visibility-map, failsafe, and interruption statistics; pg_class.relallfrozen | | [#2105](https://github.com/apache/cloudberry/pull/2105) | Native VACUUM work statistics for heap, indexes, and AO | | [#2106](https://github.com/apache/cloudberry/pull/2106) | The reporting hook and ext_vacuum_statistics, including local and cluster resource views, summaries, and distributed resets | | [#2107](https://github.com/apache/cloudberry/pull/2107) | Detailed heap reporting and AO/AOCO reporting, including separate views of resource use by AO phase | Core accumulates basic timing and work counters and exposes them through built-in SQL interfaces. The extension reads the native work counters alongside its own resource statistics, without accumulating those native counters again. The consumer hook is introduced with the extension in PR #2106; the instrumentation in PR #2103 can report measurements through verbose logging independently. PR #2104 also adds

pg_class.relallfrozen

, an estimate of the number of heap pages marked all-frozen in the visibility map. Compared with

relpages

and

relallvisible

, it helps assess freezing coverage and tune freezing behavior. VACUUM can skip all-frozen pages even during an anti-wraparound scan while their marks remain set. This catalog value records the state observed at the last refresh by operations such as VACUUM or ANALYZE; it is separate from cumulative freezing-work counters. Extension storage and additional metrics On

main

,

ext_vacuum_statistics

registers relation and database statistics kinds with the pluggable

pgstat

infrastructure. The common system supplies dynamically allocated shared storage, reads, resets, and persistence across compatible clean restarts. This avoids maintaining a separate fixed-capacity hash table and its entry-eviction policy. Memory use still grows with the number and size of tracked entries. The <pluggable-statistics.md|pluggable-statistics discussion> describes the storage API and its constraints. The extension accumulates buffer reads, hits, dirtying and writes, measured I/O time, and WAL records, full-page images, and bytes. Table and index resource accounting is kept separate. AO reporting includes dedicated views for the PRE_CLEANUP, COMPACTION, and POST_CLEANUP phases, alongside native compaction work counters. Auxiliary heap relations retain statistics under their own OIDs. Collection requires loading

ext_vacuum_statistics

through `shared_preload_lib… apache/cloudberry