[Ideas] pluggable cumulative statistics in Cloudberry

[Alena0704]

Description

Proposal: pluggable cumulative statistics in Cloudberry

The idea and intended result

Pluggable cumulative statistics let extensions define their own statistics and store them through the same infrastructure as built-in statistics. An extension registers a statistics kind describing its data, and the common system provides shared-memory storage, reads, resets, and persistence across clean restarts.

We want new statistics and their SQL interfaces to evolve in extensions. Adding an extension-owned metric should not require adding a built-in catalog function or system-view column and bumping the core catalog version for that change. Extensions should also be able to reuse the common statistics store instead of implementing storage, synchronization, resets, and persistence independently.

Pluggable statistics provide the storage mechanism. The extension author decides what to count, where to update the counters, and how to expose them through SQL. New measurement points may still require core hooks.

How extensions and users would use it

An extension developer defines counters, registers their kind at server startup, and connects measurement code or reporting hooks to updates of those counters. The extension supplies SQL functions and views for reading the values, plus any collection controls and reset functions it needs.

An operator loads the module through shared_preload_libraries, restarts the server, and installs the extension's SQL objects in the relevant databases. Monitoring then queries the extension's views and compares observations over time, accounting for resets. In Cloudberry, the extension also defines how local observations are exposed and aggregated across the coordinator and segments.

This gives each consumer control over its metrics and SQL interface while keeping storage and lifecycle operations in the common cumulative-statistics system. The VACUUM example below follows this path from measurement to SQL.

What Cloudberry has today and why it is insufficient

Before the proposed port, Cloudberry already has a cumulative-statistics infrastructure for built-in kinds, including Cloudberry-specific resource-queue statistics. What is missing is the supported registration API that lets extensions define their own kinds and participate in that infrastructure.

There are two existing approaches to adding statistics, each with a limitation:

Approach	Limitation
Add a built-in statistics kind or extend existing core statistics and SQL interfaces	Metric-specific changes become core changes; adding catalog functions or system-view columns also requires catalog maintenance
Keep statistics in an extension-owned store	The extension must maintain its own storage, synchronization, reset, and persistence behavior

For example, pg_stat_statements maintains its own shared hash table with a limit set by pg_stat_statements.max and removes entries when it fills up. This illustrates the separate storage machinery an extension maintains today. pg_stat_statements implementation

The common cumulative-statistics system also uses a hash table, backed by dynamic shared memory. It allocates memory as entries are added, without a configured entry-count limit like pg_stat_statements.max. Making this store available to custom kinds would let extensions reuse it. Statistics memory allocation

Proposed implementation and how it works

This mechanism has already been added to PostgreSQL and is documented in PostgreSQL 19. PR #2102 proposes bringing it to Cloudberry by cherry-picking the upstream commits and adapting them, including preservation of Cloudberry-specific resource-queue statistics.

The implementation extends the existing cumulative-statistics infrastructure with registration and access APIs. Its operation and division of responsibilities are described first, followed by the commits that introduce them.

Registration and storage

An extension describes a statistics kind with PgStat_KindInfo and registers it through pgstat_register_kind() while loading through shared_preload_libraries.

Two object models are supported:

Model	Example use
Fixed-numbered statistics	A fixed set of counters for a subsystem
Variable-numbered statistics	Separate entries for tables, indexes, or databases

The kind descriptor defines the data layout, storage properties, and applicable callbacks. Extensions can use pending local updates with callbacks to flush them into shared memory. The write_to_file property controls whether statistics participate in persistence.

The PR also adds an optional reset_data_cb for variable-numbered kinds. It lets a reset clear counters while preserving identifying metadata in the entry payload. Without the callback, the existing zero-fill behavior remains. PR #2102

Limitations

Dynamic allocation does not guarantee that a statistics update will succeed when memory is exhausted. Variable-numbered kinds share the cumulative-statistics memory infrastructure. Their memory use grows with the number of tracked objects and the size of each entry, with additional overhead for the shared hash table, backend-local references, pending updates, and snapshots. There is no per-kind entry-count limit or automatic eviction policy comparable to pg_stat_statements.max.

When a new entry is needed, the current implementation inserts it into the shared hash table and allocates its payload with dsa_allocate0(). This allocation does not use DSA_ALLOC_NO_OOM: if memory cannot be allocated, the allocator raises ERROR. The infrastructure therefore does not guarantee that it will simply skip a statistics sample under memory pressure. The error can propagate to the operation collecting the statistics, including a VACUUM reporting through an extension hook. See the local implementation in pgstat_shmem.c, dsa.h, and dsa.c.

This makes memory budgeting and cleanup part of extension design. Extensions should keep payloads compact, avoid unnecessarily high-cardinality keys, and connect object deletion to entry cleanup. A per-kind quota, eviction policy, or a safe way to drop a sample on allocation failure would require additional design; the proposed API does not provide these guarantees. Memory-pressure tests should establish both the error seen by the caller and whether subsequent collection remains usable.

Other limitations also affect consumers:

• Custom kind IDs are limited to 24–32 and must be unique across loaded extensions. Registration happens during preload, so enabling a new module requires a restart.
• Shared-entry locking, pending-update flushing, and snapshot creation have a cost. The API does not guarantee negligible overhead under frequent updates or concurrent access.
• Registration supplies storage APIs; extensions still need measurement hooks, SQL accessors, reset permissions, and Cloudberry-specific aggregation and distributed reset behavior.
• Persistence requires a compatible payload layout and the same kinds to be registered at startup. Clean-restart persistence is not crash durability; upgrade and module-removal risks are described below.

Commits cherry-picked from PostgreSQL

PR #2102 includes 15 PostgreSQL commits. In application order, they prepare the existing statistics code for extensions, add the registration API, and include follow-up fixes. The links below point to the original PostgreSQL commits recorded in the cherry-pick messages. The purpose column explains each change's role in the port, based on those messages. The series includes preparatory refactoring, the registration API itself, and subsequent usability, correctness, and performance changes; not every commit is independently required to register a custom kind.

PostgreSQL commit	What it adds or changes	Why it is included in the pluggable-statistics port
b19db55bd687	Removes the unused named_on_disk flag; existing callbacks already control name serialization.	Descriptor cleanup. Removes a redundant field before extending PgStat_KindInfo into the custom-kind interface. Name serialization is already determined by callbacks, so this keeps a single source of truth; it does not add a new capability.
9004abf6206e	Reads fixed-numbered statistics using kind descriptions instead of hardcoded handling.	Prepare generic loading. Replaces reads tied to individual built-in structures with descriptor-driven reads. This prepares the loader to restore kinds described by extensions without adding a hardcoded read path for each one.
9fd02525793f	Gives statistics-file entry markers named constants.	Prepare the file-format code. Names the existing record markers before the series adds the fixed-numbered F record. This is supporting refactoring: it makes the subsequent format change explicit, while leaving the format and behavior unchanged on its own.
b68b29bc8fec	Writes fixed-numbered statistics using kind descriptions as well.	Prepare generic saving. Complements the loading refactor: offsets and lengths in kind descriptions tell the writer where to find data. Custom fixed-numbered kinds can then participate in persistence without a separate core write path per kind.
21471f18e9d9	Adds init_shmem_cb so a kind can initialize its shared-memory area.	Delegate initialization to the kind. Custom fixed-numbered statistics may need locks or other state initialized in their allocated shared-memory area. The callback lets the kind own that initialization instead of requiring core to know its structure.
9e4664d950c1	Adds an F file entry containing the kind ID, allowing fixed-numbered kinds to be identified when loading saved statistics.	Identify persisted custom kinds. With a configurable set of extension kinds, the loader cannot rely on a fixed sequence of built-in records. Storing the kind ID lets it identify each fixed-numbered record and check whether its kind is registered.
3188a4582a8c	Changes PgStat_Kind from an enum to uint32 to support extension-defined IDs.	Allow IDs outside the built-in enum. Internal APIs and loops must accept IDs assigned to extensions, not just enum members compiled into core. This prepares the ID representation for the registration API.
ca1ba50fcb6f	Adds clearer warnings when a statistics file cannot be read.	Make persistence failures diagnosable. Adds details about which part of a statistics file could not be read. These diagnostics help investigate file-format and loading problems while introducing custom persisted data; they do not enable registration themselves.
028b4b21df26	Fixes log format specifiers for unsigned statistics-kind IDs.	Keep diagnostics consistent with the new ID type. Updates logging to use unsigned format specifiers after PgStat_Kind becomes uint32. This is a correctness cleanup for the preceding changes, not an additional extension API.
7949d9594582	Adds the pluggable API: extensions can register fixed-numbered and variable-numbered statistics kinds at startup.	Introduce the mechanism itself. Adds registration during preload, separates built-in and custom kind IDs, and supplies infrastructure for both custom object models. This is the central change that lets an out-of-core extension plug its kind into cumulative statistics.
2eff9e678d35	Adds helpers for reading custom fixed-numbered statistics from snapshots and shared memory.	Make fixed-numbered kinds usable by extensions. Provides access to their shared-memory data and snapshots without making extension code depend on the private pgStatLocal snapshot layout. Registration alone would leave that access dependent on internals.
341e9a05e7b4	Fixes two null-pointer crashes when loading saved statistics whose kinds are no longer registered.	Fix a restart failure introduced by custom kinds. Changing shared_preload_libraries can leave saved records whose kind is no longer registered. The checks prevent null-pointer crashes for fixed-numbered records and named variable-numbered records; they do not guarantee that the remaining statistics are preserved.
c06e71d1acaf	Adds write_to_file, allowing each kind to choose whether its values are saved at shutdown.	Let each kind choose persistence. Some extension statistics only make sense during one server lifetime. The flag lets fixed- and variable-numbered kinds opt out of shutdown serialization instead of forcing every registered kind to persist.
d35ea27e51c0	Moves kind IDs and related definitions into pgstat_kind.h.	Separate the kind-ID definitions. Keeps built-in IDs, custom ranges, and related macros in a dedicated header that can also be used by frontend tools without the backend dependencies of pgstat.h. This is interface organization, not a prerequisite for registration itself.
ac000fca743e	Reduces the kind-ID range to lower processing overhead; custom kind IDs become 24–32. This limits the number of kinds, not entries within a kind.	Reduce overhead from the expanded ID space. The original custom-kind bounds made scans over kinds more expensive, including the no-pending-statistics path in pgstat_report_stat(). Smaller bounds reduce that cost even for simple queries, at the price of fewer available custom-kind IDs.

The backports keep Cloudberry's PGSTAT_KIND_RESQUEUE and omit changes to PostgreSQL's injection_points test module, which is absent here. Two additional commits adapt the mechanism to Cloudberry; these are not PostgreSQL cherry-picks:

Cloudberry change	Purpose
Preserve resource-queue persistence and update the statistics-file format	Keep Cloudberry-specific statistics participating in shutdown/restart persistence after the upstream format changes
Add reset_data_cb	Allow resets to clear counters while preserving identifying metadata in an entry's payload

Risks, limitations, and compatibility

The recommendations below describe how an extension can work within the proposed API. They are design and operational guidance, not additional guarantees implemented by PR #2102. Some risks can be reduced in extension code; transparent payload migration, safe module removal without losing other statistics, and graceful handling of allocation failure remain infrastructure questions.

Collection and startup requirements

An extension supplies its measurement hooks and SQL interface. Custom kinds are registered at server startup, so initially enabling a module requires a restart. The current port accepts custom kind IDs 24–32 inclusive: nine slots shared across loaded extensions, with 24 designated as the experimental ID. IDs outside this range are rejected by pgstat_register_kind().

This is a compile-time bound, not a fundamental limit of pluggable statistics. It can be increased in core by raising PGSTAT_KIND_MAX in pgstat_kind.h (the custom maximum and array sizes derive from it), rebuilding the server and affected extensions, and deploying compatible builds across the cluster. It cannot be raised through a GUC or by an extension alone. The upstream series originally allowed IDs 128–256; the later reduction lowered array/iteration overhead, including the statistics-reporting path for simple queries. A larger range therefore needs performance and compatibility validation. The limit counts kinds, not counters within a payload or objects stored under a kind.

Recommended extension design: keep kind IDs stable across releases and coordinate their allocation with other supported extensions. Use one kind for a family of objects with a compatible layout, rather than allocating a kind per metric or per object. Document the required preload configuration and deploy compatible extension binaries and kind assignments on the coordinator and all relevant segments.

Provide a runtime collection switch that hooks check before doing extension-specific collection or updates. This lets operators pause collection without unloading the module or unregistering its kinds. Disabling the consumer does not necessarily remove the cost of instrumentation in core, so measure unloaded, loaded-but-disabled, and enabled configurations separately. The initial restart requirement remains; the switch only controls an already-loaded extension.

Persistence and module compatibility

With write_to_file = true, a kind's statistics survive a clean restart when the saved format and payload layout remain compatible. After a crash, cumulative counters are reset: this is existing PostgreSQL behavior, not a new limitation introduced by pluggable statistics. Use write_to_file = false for kinds whose values should cover only the current server lifetime. PostgreSQL cumulative-statistics documentation

PR #2102 changes the statistics-file format, so the first startup with an old-format file resets cumulative statistics, including built-in counters. Subsequent clean restarts with a compatible format preserve persisted values. This is an upgrade event that must be documented; it does not remove table data or the planner statistics stored by ANALYZE. PR #2102

Proposed solution for extension upgrades: export and import

An extension can provide its own export and import functions to preserve its counters across an incompatible upgrade. The extension knows the meaning of its metrics and can convert an old exported representation into its new entry layout. Import restores live counters through the shared statistics APIs, so collection can continue from the restored values.

The proposed upgrade procedure is:

1. Disable collection on the coordinator and affected segments, and finish or flush outstanding updates so the export captures a stable state.
2. Export the extension's statistics to a durable file or ordinary table. Include the export-format version, instance and object identities, counter values, and reset metadata.
3. Upgrade the extension and restart with the new module preloaded and collection still disabled.
4. Run the new version's import function on each affected instance. It validates the export, converts supported older formats, and restores entries for objects whose identity has been verified.
5. Verify the restored values and resume collection.

The importer should replace the target values, rather than blindly add to them, so retrying an interrupted import cannot double-count statistics. Restrict import to authorized users, reject unsupported versions, and explicitly report missing or recreated objects. Preserve reset metadata only where counter continuity has actually been maintained; changed metric semantics require a new baseline.

This is a proposed extension feature: the current ext_vacuum_statistics interface has read and reset functions, but no export/import implementation. Its upgrade procedure must also define how the new server handles the old binary statistics file before import. Logical export/import restores the extension's values after startup; it does not itself make that file compatible.

Stopping collection or removing the module

To stop collection temporarily, keep the module preloaded and disable collection through its setting. Its kinds remain registered, so the server can still recognize their saved entries.

For permanent removal, export the extension's values if historical observations are needed. They can remain in a regular table or monitoring store; live custom counters require a loaded module that registers and understands their kind.

In the current implementation, removing a module whose kinds remain in the saved statistics file can reset all cumulative statistics on that instance. Extension-owned import restores only that extension's counters; it cannot restore built-in or other extensions' statistics automatically. Preserving those requires migration support for the affected kinds or a loader that can safely skip the removed kind. Resetting counters alone does not remove the kind's saved entries. Implementation documentation

Entry lifecycle and reset safety

An extension must connect object creation and deletion to the transactional statistics APIs and preserve entry identity during resets. Mistakes can leave stale counters after DROP or OID reuse.

Recommended extension design: use the transactional creation/deletion APIs rather than immediately deleting statistics from a DROP hook. Verify commit and rollback paths, including creation followed by rollback and reuse of an object OID. If the payload contains a discriminator or other identity metadata, use reset_data_cb to clear counters while retaining that metadata. Keep SQL reset functions permission-controlled and document whether each reset affects one object, one database, or multiple instances.

Expose reset timing or another clear reset boundary in the SQL interface so monitoring can reject deltas spanning a reset. Test these behaviors explicitly; the common storage layer cannot infer which object events or reset semantics an extension intends.

Memory and update overhead

Dynamic allocation does not make memory unlimited. More tracked objects require more memory, and frequent updates or concurrent reads add overhead. An allocation failure can propagate to the collecting operation, as described under Limitations.

Recommended extension design: use compact payloads and bounded object identities, remove obsolete entries through the lifecycle APIs, and estimate memory from the expected object count before enabling collection at scale. Where detailed per-object data is optional, consider database-level aggregates or configurable collection scope. Reducing report frequency can reduce update cost, but does not reduce the memory of entries that remain allocated. Similarly, disabling collection and resetting counters do not by themselves reclaim entries.

Keep shared-entry lock hold times short. For frequent events, consider backend-local accumulation and the API's pending-update callbacks to batch shared writes, while documenting delayed visibility and budgeting the additional local memory. For infrequent operation-completion reports, direct shared updates may be simpler; choose based on measurements.

Set a capacity budget with headroom for DSA growth, hash-table resizing, and concurrent readers. Monitor host/container memory and the capacity of the configured shared-memory backing. Pausing collection before exhaustion can prevent further extension updates, but it is not a recovery mechanism once allocations are failing. A strict per-kind quota, automatic eviction, or a guaranteed “drop the sample and continue” path is not supplied by this proposal. Adding one requires a design that keeps entries, references, and locks consistent across allocation failures; simply catching an allocation error in the hook is not a demonstrated solution.

Cluster reporting and validation

Cluster reporting needs explicit aggregation rules so distributed and replicated relations retain meaningful statistics. A cluster-wide reset can also leave instances with different reset boundaries if only part of the operation succeeds.

Recommended extension interface: expose per-instance rows with instance identity and reset boundaries, then build summary views with documented rules for distributed relations, replicated relations, and database aggregates. Keep raw observations available so users can investigate segment differences. Report partial reset failures rather than treating them as a successful cluster-wide reset, and avoid comparing counter intervals across inconsistent reset boundaries.

Example: VACUUM statistics

ext_vacuum_statistics in PR #2106 demonstrates how an extension uses pluggable statistics from registration to SQL access.

1. Register the statistics. At startup, the extension calls pgstat_register_kind() with its PgStat_KindInfo descriptions. It registers ext_vacuum_statistics_relation for table and index entries, and ext_vacuum_statistics_db for database entries. These descriptions specify the data layout, persistence settings, and reset callback.
2. Receive measurements. VACUUM passes a report to the extension through a reporting hook. The hook supplies data; the registered kinds provide a place to store it.
3. Update the stored values. The extension finds or creates the appropriate entry through pgstat_get_entry_ref_locked(), updates its counters, and releases the lock. Relation entries are identified by statistics kind, database OID, and relation OID. Their memory is managed by the common statistics system.
4. Read the statistics through SQL. The extension's SQL functions use pgstat_fetch_entry() to read the stored values and return them through views such as ext_vacuum_statistics.pg_stats_vacuum_tables.
5. Use the common reset and lifecycle mechanisms. A reset clears counters while keeping the field that identifies the entry's data type. The extension also connects relation creation and deletion to the transactional statistics APIs: a rolled-back DROP keeps the statistics, a committed DROP removes them, and a reused OID starts with fresh counters. Persistence is enabled for these kinds so values can survive a clean restart.

VACUUM report
    → extension updates its registered statistics entries
    → cumulative statistics system stores the values
    → extension SQL functions read them
    → extension views show the results

For example, after loading and installing the extension, this query reads its local table statistics:

SELECT relid, relname, tuples_deleted, wal_bytes
FROM ext_vacuum_statistics.pg_stats_vacuum_tables;

For example, ext_vacuum_statistics provides vacuum_statistics.enabled = off to stop updating its counters without unloading the module. Example implementation

The important point is where these statistics are defined and stored: their kinds and SQL interface are supplied by the extension, while storage, reads, resets, and persistence use the existing cumulative statistics mechanism. Other extensions can use the same approach for their own statistics.

The individual VACUUM metrics, their diagnostic uses, and storage memory requirements are covered in the separate VACUUM discussion.

There is no default “statistics fail after X GiB of RAM is occupied” threshold. The statistics DSA starts with 256 KiB in main shared memory, then grows; pgstat_shmem.c removes its temporary startup size limit with dsa_set_size_limit(dsa, -1). Neither work_mem nor shared_buffers is a cap on this statistics store.

ext_vacuum_statistics is an example implementation of an extension using pluggable cumulative statistics. Its tests show how to check the mechanism in a real extension: collection control, resets, persistence, object creation and deletion, and cluster reporting. The extension is described in PR #2106. It builds on PR #2102 for pluggable statistics, PR #2103 for VACUUM resource instrumentation, PR #2104 for native timing and visibility-map statistics, and PR #2105 for native VACUUM work counters. PR #2102 also adds a coordinator/segment test for preserving existing resource-queue statistics across a clean restart.

Use case/motivation

No response

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!