Horton

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

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>

View thread
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
To: Naga Appani <nagnrik@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Kirill Reshke <reshkekirill@gmail.com>, pgsql-hackers@postgresql.org
Date: 2025-08-18T11:56:06Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add pg_get_multixact_stats()

  2. Add MultiXactOffsetStorageSize() to multixact_internal.h

  3. Change GetMultiXactInfo() to return the next multixact offset

  4. Widen MultiXactOffset to 64 bits

  5. Refactor ReadMultiXactCounts() into GetMultiXactInfo()

  6. Move SQL-callable code related to multixacts into its own file

  7. Split func.sgml into more manageable pieces

On Sun, Aug 17, 2025 at 11:57 AM Naga Appani <nagnrik@gmail.com> wrote:
> On Fri, Aug 8, 2025 at 4:33 AM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:

>
> > In case each member starts consuming more or less space than it does
> > today what would be the basis of triggering wraparound? Space or
> > number of members? I think we should mention only that.
>
> I updated the docs to describe wraparound in terms of member counts only.
> The earlier mention of disk size has been dropped, since the thresholds
> are defined by counts.

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.

>
> That’s a cool idea, thanks for pointing it out. For now I have kept the
> SQL function focused only on exposing the raw counts (num_mxids,
> num_members, oldest IDs). My thought was that keeping the API lean makes
> it easier to maintain across versions, while leaving any derived
> calculations like approximate storage size to SQL or external tooling.
> This way the function remains simple and future-proof, while still
> giving users the building blocks to get the view they need.
>
> I’m happy to revisit this if others feel it would be better for the
> function to provide an approximate size directly — I wanted to start
> with the simplest surface and gather feedback first.

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.

If size is being used as a threshold, reporting count is useless
because user wouldn't know the relation easily. If count is used as a
threshold, reporting count makes sense.

-- 
Best Wishes,
Ashutosh Bapat