GitHub user Alena0704 created a discussion: [Proposal]
### Proposers _No response_ ### 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](https://www.postgresql.org/message-id/bee34abe-fe87-49b3-8523-8ae19e641ccf%40yandex.ru) and the [follow-up explanation of per-relation counters](https://www.postgresql.org/message-id/abfa1c84b6bd70cabe0713d8b23758129ac34300.camel%40moonset.ru). 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](https://github.com/zubkov-andrei/pg_profile/blob/master/doc/pg_profile.md), 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 discussion](pluggable-statistics.md) 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_libraries` on the coordinator, segments, and mirrors, with configuration distributed through `gpconfig`. `CREATE EXTENSION ext_vacuum_statistics` installs its SQL interface. `vacuum_statistics.enabled` controls extension collection and is synchronized from the coordinator to segments. I/O timing requires `track_io_timing`; cost-delay timing requires `track_cost_delay_timing`. ### Cluster reporting, resets, and lifecycle The extension exposes local `pg_stats_vacuum_*` views and cluster `gp_stats_vacuum_*` views with `gp_segment_id`. Cluster reporting uses `gp_dist_random`. Summary views aggregate distributed relations across segments, normalize replicated relations by the segment count, and use coordinator statistics for system relations. Per-instance rows remain available for investigating differences hidden by totals. Summed segment time describes accumulated work rather than the wall-clock duration of a distributed VACUUM. For Cloudberry, the `gp_*` reset wrappers provide distributed reset behavior. The original extension reset functions affect only the current node. Native and extension counters have separate reset functions; resetting extension statistics does not reset the native work counters. Statistics lifecycle follows transactional object creation and deletion: committed drops remove entries, rolled-back drops preserve them, and reused OIDs start with fresh counters. Compatible clean restarts can preserve saved values. Statistics are local to each instance and do not survive failover as a continuous history. Changes to extension payloads must keep loaded code, shared data, saved statistics, and SQL definitions compatible. ### Partial implementation for `REL_2_STABLE` The stable-branch proposal uses its PostgreSQL 14 statistics collector. [PR #2078](https://github.com/apache/cloudberry/pull/2078) adds instrumentation, performance logging, maintenance timing, and visibility-map clearing counters. [PR #2079](https://github.com/apache/cloudberry/pull/2079) adds cumulative work statistics and SQL access through `vacuum_stats`. Collection and storage remain in core. All new functions and views belong to the extension, preserving existing system views, built-in function OIDs, and the system catalog version. `CREATE EXTENSION vacuum_stats` installs local `pg_stat_vacuum_*` and cluster `gp_stat_vacuum_*` views. The saved-statistics format changes, so old saved statistics are discarded on startup. This partial version covers heap and index cleanup, AO/AOCO compaction results, timing, visibility-map clearings, failsafe, and interruptions. It does not add `pg_class.relallfrozen`, the cumulative buffer/I/O/WAL fields, or the AO phase resource views from the `main` series. It uses collector counters rather than the pluggable statistics mechanism proposed for `main`. Extended work counters require `track_vacuum_statistics = on` on the coordinator and segments and a restart. Timing, visibility-map clearings, failsafe, and interruption counts follow `track_counts` independently. Cost-delay measurement also requires `track_cost_delay_timing`. The [stable-branch extension documentation](https://github.com/apache/cloudberry/blob/cb934b82cb2a46d8daceea2436e091ce23815db7/contrib/vacuum_stats/README.md) describes the controls and counter semantics. ### Optional historical analysis Optional `pg_profile` integration would add sampling and reports for the new metrics. Samples should retain collection times, relation and segment identity, and reset boundaries. Interval analysis must distinguish cumulative counters from last-run observations and account for resets, relation recreation, and failover. For example, repeated observations of retained tuples can increase a cumulative counter without increasing the current amount of dead data. ### Examples of diagnostic patterns The following figures use observations published in the supplied diagnostic report, from the October 5, 2026 run on commit `79f6c1ca3c7ab1abd62388bf80582f29d40bcef1`. They illustrate counter behavior that longer-term analysis could help detect. They come from a historical implementation and are not measurements of the current PR branches or reports generated by `pg_profile`. The source tables provide ordered stages and per-run deltas, rather than timestamped raw snapshot exports. Consequently, the horizontal axes show scenario stages, not equally spaced time samples. Cumulative curves reconstructed from deltas use an explicitly normalized zero baseline. See [data and snapshot methodology](snapshot-methodology.md) for provenance and metric mapping. #### Experimental setup and comparison rules The historical experiments used a Cloudberry build based on `REL_2_STABLE` / PostgreSQL 14.10, assertions enabled, 32 KiB blocks, and `optimizer=off`. Distributed scenarios used a coordinator and two primary segments, with autovacuum disabled. Failsafe was exercised on a separate utility node. The report states that scenarios were reproduced twice and publishes observations from the second run. These are functional experiments showing counter behavior. An assertions-enabled run with one published observation per stage cannot establish production overhead or a statistically significant speedup. For each case, retain the same object and workload across the affected and follow-up passes unless the intervention deliberately rebuilds it. Record the intervention separately from the VACUUM interval. Compare per-instance counter deltas first; aggregate only after checking their scope. The expected diagnostic outcome is a change consistent with the known intervention, not simply a lower elapsed time. #### Retained snapshots: the removal delta changes after releasing the reader **Question.** Can the statistics distinguish repeated VACUUMs that cannot remove versions from a successful cleanup after the retaining transaction ends? **Method.** Insert 20,000 rows, open a REPEATABLE READ snapshot, and delete 16,000 rows from another session. Take S0. Run VACUUM twice, collecting S1 and S2 after completion. End the reader transaction, repeat the same VACUUM, and collect S3. The reader's `backend_xmin` supplies evidence of the retained visibility horizon. | Interval | Reader state | Heap removals | Retained observations | Index removals | | --- | --- | ---: | ---: | ---: | | S0 → S1 | Snapshot retained | 0 | 16,000 | 0 | | S1 → S2 | Snapshot retained | 0 | 16,000 | 0 | | S2 → S3 | Reader released | 16,000 | 0 | 16,000 | Values are work deltas across two data segments. S0–S3 are explanatory sample labels, not identifiers from a raw snapshot export.  Two VACUUMs each observe 16,000 retained versions and remove none while the reader keeps its snapshot. After the reader releases it, the next VACUUM removes 16,000 heap versions and 16,000 index entries. The accumulated retained-observation counter remains at 32,000; the useful recovery signal is that its next **delta becomes zero**, while the removal delta increases. The historical report calls this cumulative retained-observation field `dead_tuples`. Current native `dead_tuples` is a last-run observation, so the historical curve must not be relabeled as the current field. Current cumulative retained observations are exposed through `recently_dead_tuples`, with the scope described in the proposal. #### Index bloat: REINDEX changes physical size after VACUUM has done cleanup **Question.** Did VACUUM fail to clean the index, or did successful cleanup leave a large file with reusable space? **Method.** Continue the old-snapshot scenario after releasing the reader. Run ordinary VACUUM with `TRUNCATE OFF`, capture its work deltas, and inspect physical sizes on both segments. Run REINDEX, inspect sizes again, then run VACUUM FULL and repeat physical inspection. Keep the table contents unchanged between these maintenance steps. | Observation | After ordinary VACUUM | After REINDEX | After VACUUM FULL | | --- | ---: | ---: | ---: | | Index size, segment 0 / 1 | 288 / 288 KiB | 128 / 128 KiB | 128 / 128 KiB | | Heap size, segment 0 | 5,472,256 B | 5,472,256 B | 1,081,344 B | | Heap size, segment 1 | 5,505,024 B | 5,505,024 B | 1,114,112 B | | Heap free space, segment 0 / 1 | 80.49% / 79.96% | Unchanged | 1.69% / 1.38% | This is a supplementary physical-state comparison. The report has no matched VACUUM resource measurements before and after REINDEX, so it cannot demonstrate that later VACUUMs use less time, I/O, or WAL. Such a claim would require equivalent maintenance workloads and fresh counter snapshots on both sides of the rebuild.  Ordinary VACUUM with `TRUNCATE OFF` has already removed 16,000 heap versions and 16,000 index entries. Nevertheless, each segment's index still occupies 288 KiB. After REINDEX, each index occupies 128 KiB: a 55.6% reduction. Heap size remains unchanged. A subsequent VACUUM FULL reduces heap size and its internal free space. This comparison separates completed cleanup from physical compaction. Index `pages_deleted` increased by eight across the two segments during ordinary VACUUM, but that does not establish file truncation. The physical index-size snapshots show the effect of REINDEX directly. The report does not publish numerical index leaf-density observations, so no density curve is inferred. The right panel shows **heap** free-space percentage, not index bloat percentage. Size reduction in this controlled workload does not establish a universal REINDEX threshold or prove query-performance improvement. #### AO compaction: remaining hidden rows give way to removals **Method.** Delete 16,000 of 20,000 AO rows. Capture one VACUUM FULL interval with compaction disabled. Enable compaction, set the lab compaction threshold to zero, and capture the next interval without changing the remaining rows. Compare hidden-row observations, removals, and old-file bytes released.  With compaction disabled, the run observes 16,000 remaining hidden rows and removes none. After compaction is enabled, the next run removes 16,000 rows and releases 6,447,552 bytes from old files. These are per-run measurements across two data segments. The released bytes are not net relation shrinkage because live rows are written to new files. In this historical scenario, the index removes 20,000 entries after compaction, including old entries for 4,000 moved live rows. The initial index-removal measurement was not reported and is not plotted as zero. #### Failsafe: follow-up maintenance completes the index cleanup **Method.** On a separate utility node, age a table using real XID allocation and run VACUUM with the scenario's failsafe thresholds. Capture table and index statistics after completion. In a fresh session with ordinary settings, run VACUUM again and subtract the previous observations. The failsafe warning in the log confirms the first pass's mode.  On the utility-node test, the protective pass removes 10,000 heap versions and freezes 209 pages, while index removals remain zero. The next ordinary VACUUM removes 10,000 index entries and marks 209 pages all-visible. The plots show interval deltas derived from cumulative observations. The cumulative table failsafe count stays at one after follow-up; it does not drop after recovery. Its interval delta changes from one to zero. The graph demonstrates completion of outstanding maintenance, not a permanent resolution of the workload's XID-aging risk. #### Freezing: work counters explain a run with zero removals **Method.** Use a table without dead rows. Capture an ordinary VACUUM, the first VACUUM FREEZE, and three more FREEZE passes without intervening DML. Compare page-work deltas and per-segment freeze-age counts.  Ordinary VACUUM marks 104 pages all-visible across the two data segments. The first FREEZE freezes 104 pages. Three further FREEZE passes add no new freezing, all-visible markings, or removals, although each increments `freeze_age_vacuum_count` by one on each segment. The useful signal is the combination of `delta pages_frozen`, `delta pages_all_visible`, and `delta tuples_deleted`. The freeze-age count classifies the run; by itself it neither measures useful work nor establishes that an anti-wraparound autovacuum occurred. ### Interpretation and scope Most measurements accumulate, so interval analysis requires two observations and a difference. Native `dead_tuples` and `total_file_segs`, however, retain the latest observation. The cumulative `recently_dead_tuples` counter can count the same retained rows again on successive runs; for AO it accumulates observations of hidden rows remaining after cleanup. These semantics are described in [PR #2105](https://github.com/apache/cloudberry/pull/2105) and [PR #2107](https://github.com/apache/cloudberry/pull/2107). Several distinctions matter when interpreting results: - Accumulated observations of retained tuples are not the current amount of dead data. - Removing tuple versions does not necessarily shrink files; freed space can remain available for reuse. - Summed instance elapsed time is not the duration experienced by the client. - Statistics resets, relation recreation, and failover can break interval continuity. - Resource accounting separates table and index work; native table elapsed time and extension resource counters must be interpreted according to their respective scopes. The statistics complement activity views, progress reporting, logs, and physical inspection. They make maintenance results measurable; additional evidence establishes the cause of a problem. The accompanying diagnostic report contains functional reproductions involving old snapshots, cost delay, cancellation, freezing, AO compaction, and protective VACUUM behavior on an earlier implementation. These scenarios are useful validation workloads. Their historical measurements should not be presented as results for the current PR series or as measurements of collection overhead. ### Measurements needed on the current PR series The next validation should answer two separate questions: whether the measurements are correct, and what collecting them costs. | Experiment | Comparison | Evidence to retain | | --- | --- | --- | | Counter semantics | Affected pass versus follow-up after a controlled intervention | SQL, per-instance samples, logs confirming the cause, reset boundaries, expected and observed deltas | | Resource collection overhead | Same build and workload with the extension unloaded, loaded but disabled, and enabled | Client elapsed time, CPU/I/O observations, WAL, memory, table/index sizes, settings | | Native instrumentation overhead | Pinned baseline build versus the patched build under equivalent conditions | Compiler/configure details, identical workload setup, repeated measurements | | VACUUM cost after REINDEX | Equivalent newly generated cleanup workload before versus after rebuilding the index | VACUUM time and index resource deltas, physical size and density, comparable cache conditions | | Cluster behavior | Distributed and replicated relations, with observations kept per instance | Node-level results and verification of summary normalization | Performance runs should use an optimized build without assertions, record the compiler, hardware, storage and cluster shape, and repeat measurements rather than quote a single elapsed time. Report a median with spread and retain raw samples. If the spread does not support a difference, report no measurable improvement. A resource reduction can still be useful, but it must be measured independently of the cleanup counts. For the REINDEX comparison, rerunning VACUUM on an already-clean table is not an equivalent workload. Recreate comparable dead tuples before each measured pass. A smaller index is established by the existing physical observations; lower subsequent VACUUM cost remains an open measurement. ### Rollout/Adoption Plan The implementation is already available in the PR series for `main` ([#2102](https://github.com/apache/cloudberry/pull/2102), [#2103](https://github.com/apache/cloudberry/pull/2103), [#2104](https://github.com/apache/cloudberry/pull/2104), [#2105](https://github.com/apache/cloudberry/pull/2105), [#2106](https://github.com/apache/cloudberry/pull/2106), and [#2107](https://github.com/apache/cloudberry/pull/2107)) and in the partial implementation for `REL_2_STABLE` ([#2078](https://github.com/apache/cloudberry/pull/2078) and [#2079](https://github.com/apache/cloudberry/pull/2079)). With the corresponding core changes installed, `main` provides basic statistics through built-in interfaces; additional resource reporting is enabled by configuring and installing `ext_vacuum_statistics`. On `REL_2_STABLE`, `vacuum_stats` supplies the new SQL interfaces without a system catalog version bump. The branch-specific collection settings and installation requirements are described in Implementation. Integration with `pg_profile` and more granular collection filters remain optional follow-up work. ### Are you willing to submit a PR? - [X] Yes I am willing to submit a PR! GitHub link: https://github.com/apache/cloudberry/discussions/2118 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
