feat(metrics): add PostgreSQL 19 multixact_size support using pg_get_multixact_stats()

Summary

PostgreSQL 19 introduces a new native function pg_get_multixact_stats() for efficient monitoring of multixact usage. This function is significantly faster than filesystem-based scanning (~0.1ms vs seconds).

Background

The new function returns

Column Type Description
num_mxids integer Total count of multixact IDs
num_members bigint Total number of member entries
members_size bigint Bytes occupied by members in pg_multixact/members/
oldest_multixact xid Oldest multixact ID still needed

Implementation

  • Use native pg_get_multixact_stats() when available (PG19+)
  • Falls back to Aurora/RDS/filesystem methods for cloud providers
  • Calculate offsets_bytes from num_mxids (each SLRU segment is 256KB with 32768 8-byte entries)

References

Acceptance Criteria

  • A new metrics collector uses pg_get_multixact_stats() when connected to PostgreSQL 19+ to retrieve multixact usage data (num_mxids, num_members, members_size, oldest_multixact)
  • The collector falls back to existing filesystem-based or cloud-provider-specific methods (Aurora/RDS) for PostgreSQL versions below 19
  • offsets_bytes is calculated from num_mxids using the correct SLRU segment math (256KB segments with 32768 entries of 8 bytes each)
  • Performance improvement is validated: native function call completes in sub-millisecond time vs. seconds for filesystem scanning
  • Metrics are exposed in the same format as existing multixact metrics so dashboards and alerting rules work without modification

Definition of Done

  • Implementation merged with version detection logic that selects the appropriate collection method based on server_version_num
  • Unit tests cover both the PG19+ path and the fallback path
  • Integration test confirms correct data retrieval against a PG19+ instance (or mocked function output)
  • Existing dashboards and alert rules continue to work without modification
  • Documentation updated to note PG19 multixact monitoring improvements
Edited by Nikolay Samokhvalov