Repository navigation
[Proposal] VACUUM statistics for performance analysis #2118
Proposal StatusUnder Discussion AbstractI 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 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 MotivationUnderstand the results and cost of maintenanceVACUUM 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:
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 timeRegular 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 Make statistics extensibleThe 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. ImplementationSeries for
|
| PR | Contribution |
|---|---|
| #2102 | Pluggable cumulative statistics, allowing extensions to reuse the common storage, reset, and persistence infrastructure |
| #2103 | Optional VACUUM buffer, I/O, and WAL instrumentation and resource reporting under VACUUM (VERBOSE) |
| #2104 | Native maintenance timing, cost-delay, visibility-map, failsafe, and interruption statistics; pg_class.relallfrozen |
| #2105 | Native VACUUM work statistics for heap, indexes, and AO |
| #2106 | The reporting hook and ext_vacuum_statistics, including local and cluster resource views, summaries, and distributed resets |
| #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 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 adds instrumentation, performance logging, maintenance timing, and visibility-map clearing counters. PR #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 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 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 and PR #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, #2103, #2104, #2105, #2106, and #2107) and in the partial implementation for REL_2_STABLE (#2078 and #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?
- Yes I am willing to submit a PR!
Replies: 1 comment 1 reply
|
I strongly support this proposal. Improving VACUUM statistics is a valuable step toward making Apache Cloudberry more observable and easier to operate in production. It aligns well with the broader goal of continuously improving the observability of the system, enabling DBAs to better understand maintenance costs, diagnose bottlenecks, and evaluate the effectiveness of autovacuum tuning. As we move forward, I think a few aspects deserve particular attention:
Overall, this is a meaningful improvement, and I look forward to seeing it evolve into a practical part of Cloudberry's operational observability toolkit. Thanks for the great work! Lirong |
Thank you, Lirong. Most of these aspects are already addressed in the proposed implementations for both main and REL_2_STABLE: documented metric semantics, per-segment views, and reset/restart handling with test coverage. The main series also provides cluster summary views. |
I strongly support this proposal.
Improving VACUUM statistics is a valuable step toward making Apache Cloudberry more observable and easier to operate in production. It aligns well with the broader goal of continuously improving the observability of the system, enabling DBAs to better understand maintenance costs, diagnose bottlenecks, and evaluate the effectiveness of autovacuum tuning.
As we move forward, I think a few aspects deserve particular attention: