Technical Analysis: Add pg_stat_kind_info System View

Core Problem

PostgreSQL 17 introduced pluggable cumulative statistics via commit 7949d959458, allowing extensions to register custom statistics kinds through the pgstat infrastructure. This was a significant extensibility win, but it left a critical observability gap: there is no SQL-level introspection mechanism for either built-in or custom statistics kinds.

This manifests in two concrete problems:

1. No per-kind memory accounting: The pg_shmem_allocations view shows a single monolithic "Shared Memory Stats" entry. Operators and extension developers cannot determine how much shared memory each statistics kind consumes, making it impossible to diagnose memory pressure from specific stats kinds or right-size deployments.

2. No metadata introspection for extension authors: Custom statistics authors have no way to verify their registration is correct, check entry counts, or validate their callback configuration through SQL. The only way to inspect the statistics subsystem's internal state is through C-level debugging.

Proposed Solution

Tristan Partin proposes a new system view pg_stat_kind_info backed by a new SQL function pg_stat_get_kind_info(). The view exposes per-kind metadata with the following schema:

The count column leverages the existing track_entry_count flag in PgStat_KindInfo and calls pgstat_get_entry_count(). When the flag is false (as it is for most variable-numbered built-in kinds like database, relation, function stats), the column returns NULL — a sensible design choice that avoids misleading zero values.

The implementation requires including pgstat_internal.h in pgstatfuncs.c, which is notable because this header contains internal-only definitions. This suggests the current pgstat API doesn't cleanly expose enough metadata through its public interface — a design tension worth tracking.

Key Technical Insights

The Fixed vs. Variable Numbered Stats Dichotomy

The output reveals an important architectural detail about PostgreSQL's statistics subsystem. Statistics kinds fall into two categories:

• Variable-numbered kinds (id 1-6): database, relation, function, replslot, subscription, backend — these have count = NULL and non-zero shared_size per entry. They use hash tables with dynamic entry allocation.
• Fixed-numbered kinds (id 7-13): archiver, bgwriter, checkpointer, io, lock, slru, wal — these have count = 1 and shared_size = 0. They use statically allocated shared memory regions at known offsets.

This distinction is central to Michael Paquier's review feedback, which argues the view should expose this dichotomy explicitly and use it for SQL-level validation.

Paquier's Expansion Proposal: SQL-Level Consistency Checking

Michael Paquier's review is architecturally significant. Rather than treating this view as a simple informational display, he proposes making it a verification tool for pgstats kind registration correctness. His suggested additional columns include:

• fixed_amount: Boolean flag explicitly surfacing whether a kind is fixed-numbered, rather than inferring it from shared_size > 0.
• snapshot_ctl_off and shared_ctl_off: Offsets into the snapshot and shared control structures — these should only be set for fixed-amount kinds.
• pending_size: Size of pending data structures — should only exist for variable-numbered kinds.
• Callback existence booleans: Whether each callback (flush_pending_cb, delete_pending_cb, reset_timestamp_cb, init_shmem_cb, reset_all_cb, snapshot_cb, serialization callbacks) is registered. These have strict invariants: flush/delete/reset_timestamp/serialization callbacks are for variable-numbered kinds only; init_shmem/reset_all/snapshot are for fixed-numbered kinds only.

This transforms the view from a simple monitoring tool into a declarative specification of pgstats kind invariants expressed in SQL. Regression tests can then enforce these invariants, catching misconfigurations for both core developers modifying built-in stats and extension authors implementing custom stats. This is a particularly valuable pattern because pgstat_internal.h documents these requirements in comments, but they are not currently machine-enforceable.

Test Stability Concern

Paquier identifies a practical testing issue: the proposed regression test queries pg_stat_kind_info ordered by name without filtering to built-in kinds. Running installcheck against an instance with loaded custom stats extensions would produce unexpected rows, causing test failures. The fix is straightforward — add WHERE builtin = true — but it highlights the general challenge of testing extension-aware system views.

The Shared Memory Visibility Gap

The example output shows the current state: pg_shmem_allocations reports a single 321,976-byte allocation for "Shared Memory Stats." With the new view, an operator can now compute per-kind contributions. For variable-numbered kinds, count * shared_size gives the per-kind memory footprint. For fixed-numbered kinds, the memory is embedded in the monolithic stats shared memory segment at the offsets Paquier wants exposed. This granularity is essential for capacity planning in extension-heavy deployments.

Design Tradeoffs and Open Questions

1. Column naming: The author acknowledges shared_size may not be the best name. For variable-numbered kinds, it represents per-entry size; for fixed-numbered kinds, it's 0 (the memory is accounted differently). This asymmetry could confuse users.

2. Internal header dependency: The need to include pgstat_internal.h in pgstatfuncs.c suggests the pgstat public API may need expansion. If this view is accepted, it would be cleaner to expose the necessary metadata through pgstat.h rather than reaching into internals.

3. Scope of exposed internals: Paquier's proposal to expose snapshot_ctl_off and shared_ctl_off raises the question of whether these implementation details should be visible at the SQL level. They're primarily useful for developer-level validation, not operational monitoring. However, since they enable powerful regression tests, the tradeoff favors inclusion.

4. Custom stats kind IDs: The example shows custom kinds starting at id 25, with a gap from 14-24. This reflects the reserved ID range for future built-in kinds — an important detail for extension authors to understand.

Status and Next Steps

As of the last message, this patch is in early review. Paquier is supportive of the core concept (especially the track_entry_count exposure) but requests significant expansion of the exposed metadata to enable SQL-level invariant checking. A v2 patch incorporating this feedback has not yet been posted. The patch targets what appears to be PostgreSQL 18 development.