[Proposal] Expose internal MultiXact member count function for efficient monitoring

Hi,

I would like to propose exposing an internal PostgreSQL function called
ReadMultiXactCounts() to allow for efficient monitoring of MultiXact member
usage. This function provides an accurate, real-time view of MultiXact
activity by directly retrieving the actual member count, rather than
relying on storage-based calculations.

*Current Challenges: *The existing approach we are currently using to
estimate MultiXact member usage has several drawbacks:

- *Filesystem scanning overhead: *These functions recursively scan the
pg_multixact directory, iterating over potentially thousands or millions
of files, and retrieving file sizes using stat() calls, which introduces
significant I/O overhead.
- *Potential performance bottleneck:* On systems with high transaction
throughput generating large numbers of MultiXact members, the
filesystem-based approach scales poorly due to the latency of stat() calls,
especially on network-based filesystems like RDS/Aurora.
- *Not a real-time or memory-efficient solution:* The current approach
does not provide a direct, in-memory view of MultiXact activity.

*Proposed Solution*The internal ReadMultiXactCounts() function, implemented
in multixact.c, directly calculates the number of MultiXact members by
reading live state from shared memory. This approach avoids the performance
issues of the current filesystem-based estimation methods.

By exposing ReadMultiXactCounts() for external use, we can provide
PostgreSQL users with an efficient way to monitor MultiXact member usage.
This could be particularly useful for integrating with tools like Amazon
RDS Performance Insights and Amazon CloudWatch to provide enhanced database
insights and proactive managed monitoring for users.

The performance comparison between the current and proposed approaches
shows a significant improvement, with the proposed solution taking only a
fraction of a millisecond to retrieve the MultiXact member count, compared
to tens or hundreds of milliseconds for the current filesystem-based
approach.

Following is the comparison of performance between calculating storage of
MultiXact members directory and retrieving the count of members.

Implementation Used size MultiXact members (approx) Time taken (ms) Time
factor (vs Baseline)
EC2 community (RDS version of pg_ls_multixactdir) 8642 MB 1.8 billion 96.879
1.00
Linux du command 8642 MB 1.8 billion 96 NA
Proposal (ReadMultiXactCounts) N/A 1.99 billion 0.167 580 times faster

I believe exposing ReadMultiXactCounts() would be a valuable addition to
the PostgreSQL ecosystem, providing users with a more reliable and
efficient way to monitor MultiXact usage. Appreciate your feedback or
discussion on this proposal.

Please let me know if this approach is acceptable, so I’ll go ahead and
submit a patch.

Thank you!

Best regards,
Naga Appani
Postgres Database Engineer
Amazon Web Services