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

Hi Michael, Ashutosh,

Thanks a lot for taking the time to review this patch and share your thoughts.

Here’s a short summary of what has changed in v5:
- Added the new pg_get_multixact_stats() function in multixactfuncs.c.
- Fixed the misleading “atomic read” comment in the accessor.
- Clarified documentation: thresholds are described in terms of
counts, since that’s what the code uses.
- Added a members_bytes column in pg_get_multixact_stats() to give
users a rough size estimate (num_members * 5), while making it clear
this is layout-dependent.

Please see my in-line replies below.

---

On Mon, Aug 18, 2025 at 1:49 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> My point was a bit different: multixactfuncs.c should be created first
> because we already have one SQL function in multixact.c that can be
> moved inside it, with the declarations it requires added to
> multixact.h. I've extracted what you did, moved the existing
> pg_get_multixact_members() inside the new file, and applied the
> result.
>
Really appreciate your clarification and for making that change. I
misunderstood your earlier point.

> + * Returns information about current MultiXact state in a single atomic read:
>
> This comment is incorrect. This is not an atomic read, grabbing a
> consistent state of the data across one single lock acquisition.
>
Fixed and adjusted wording.

---

On Mon, Aug 18, 2025 at 6:56 AM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> The current document says
> "Also, if the storage occupied by multixacts members exceeds about
> 10GB, aggressive vacuum scans will occur more often for all tables,
> starting with those that have the oldest multixact-age." - do you mean
> that it's wrong. Instead of checking 10GB threashold, is the code
> checking the equivalent member count? If so, I think we need a
> separate patch to correct the documentation first. Can you please
> point me to the code? Documentation should reflect the code.
>
The decision is made in MultiXactMemberFreezeThreshold() [0], and it
is entirely count-based:

if (members <= MULTIXACT_MEMBER_SAFE_THRESHOLD)
return autovacuum_multixact_freeze_max_age;

fraction = (double) (members - MULTIXACT_MEMBER_SAFE_THRESHOLD) /
(MULTIXACT_MEMBER_DANGER_THRESHOLD - MULTIXACT_MEMBER_SAFE_THRESHOLD);

MaxMultiXactOffset is defined in multixact.h [1]:

#define MaxMultiXactOffset ((MultiXactOffset) 0xFFFFFFFF)

Thresholds are defined in multixact.c [2]
#define MULTIXACT_MEMBER_SAFE_THRESHOLD (MaxMultiXactOffset / 2)
#define MULTIXACT_MEMBER_DANGER_THRESHOLD \
(MaxMultiXactOffset - MaxMultiXactOffset / 4)

These translate to:
- MaxMultiXactOffset: ~4.29 billion (2^32 - 1)
- MULTIXACT_MEMBER_SAFE_THRESHOLD: ~2.14 billion (2^31 - 1)
- MULTIXACT_MEMBER_DANGER_THRESHOLD: ~3.22 billion (3/4 * 2^32)

So the code path is count-driven.

Regarding docs:
For earlier versions (18 and before), the storage-size approximation
remains relevant because users don’t have direct access to member
count information. Since we don’t plan to backpatch (I assume so) this
new function, the documentation for older branches should continue to
rely on the storage-based approximation.

Now that pg_get_multixact_stats() exposes num_members, the HEAD branch
docs can describe the thresholds in terms of counts directly. For
older branches, the storage approximation still provides users with a
practical way to reason about wraparound risk.

> The constant multiplier which converts a count into the disk size is
> in the server code. Duplicating it outside the server code, even in
> documentation, would require maintenance. GetMultiXactInfo() may not
> do the arithmetic but pg_get_multixact_stats() is lean enough to add a
> couple computations.
>
Thanks for suggesting this — it makes sense, especially for users
upgrading from earlier versions to 19 and higher. I’ve added a
members_bytes column to pg_get_multixact_stats(), computed as
num_members * 5. This respects the existing server-side logic while
also giving those users a familiar reference point, helping them
connect the older size-based guidance with the new count-based view.

---

References:
[0] https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.c#L2916
[1] https://github.com/postgres/postgres/blob/master/src/include/access/multixact.h#L31
[2] https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.c#L216-L218

Patch v5 is attached. Thanks again for the thoughtful reviews — I really
appreciate the guidance and look forward to further feedback.

Best regards,
Naga