Re: [Proposal] Expose internal MultiXact member count function for efficient monitoring - Mailing list pgsql-hackers

On Mon, Aug 4, 2025 at 1:16 AM Naga Appani <nagnrik@gmail.com> wrote:
>
> Hi Ashutosh, Michael,
>
> Thanks for the detailed reviews. I have incorporated the feedback;
> please find attached v2 and my responses inline below.
>
> On Fri, Jul 25, 2025 at 5:57 AM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
>
> > In [1], we decided to document pg_get_multixact_member() in section
> > "Transaction ID and Snapshot Information Functions". I think the
> > discussion in the email thread applies to this function as well.
>
> Done -- the function is now documented under “Transaction ID and
> Snapshot Information Functions” for consistency.
>
> > The description here doesn't follow the format of the other functions
> > in this section.
>
> Updated the description in func.sgml to match the style of other
> functions. Extended usage guidance is now in maintenance.sgml.
>
> > Throwing an error causes the surrounding transaction to abort, so it
> > should be avoided in a monitoring/reporting function if possible.
>
> The function now returns NULL instead of throwing an error when counts
> can’t be read.
>
> > If ReadMultiXactCounts() returns false, MultiXactMemberFreezeThreshold() returns 0...
>
> Noted -- the docs now mention that the function can be used to
> anticipate more aggressive autovacuum behavior in such cases.
>
> > In PG14+, the transaction wraparound is triggered if the size of the
> > directory exceeds 10GB. This function does not help monitoring that
> > condition. So a user will need to use du or pg_ls_multixactdir()
> > anyway, which defeats the purpose of this function being more
> > efficient than those methods. Am I correct? Can we also report the
> > size of the directory in this function?
>
> Correct, that is the intent of the function. The members count
> returned by this function already provides the necessary information
> to determine the directory size, since each member entry has a fixed
> size. The constants and formulas in [0] and discussed in [1] show that
> each group stores four bytes of flags plus four TransactionIds (20
> bytes total), yielding 409 groups per 8 KB page and a fixed
> members‑to‑bytes ratio. This means ~2 billion members corresponds to
> ~10 GB (aggressive autovacuum threshold) and ~4 billion members
> corresponds to ~20 GB (wraparound).
>
> Since the function already provides the member count, including the
> physical size in its output would duplicate information and add no
> extra benefit.
>
> > The patch needs tests.
>
> Added an isolation test to cover initial state, MultiXact creation,
> counts, and oldest MultiXact reporting.
>
> On Mon, Jul 28, 2025 at 1:00 AM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
>
> > Let's say if the user knows that the counts are so high that a
> > wraparound is imminent, but vacuuming isn't solving the problem...
> > Here's a quick patch implementing the same. Please feel free to
> > incorporate and refine it in your patch if you like it.
>
> Thank you for sharing the patch. I have incorporated it into this
> version with minor adjustments, and it fits well with the overall
> design of the function.
>
> On Mon, Jul 28, 2025 at 4:22 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> > Yep, let's be consistent.
>
> Done -- placed in “Transaction ID and Snapshot Information Functions”
> for consistency.
>
> > Most likely returning NULL is the best thing we can do, as a safe fallback.
>
> Implemented -- the function now returns NULL if counts can’t be read.
>
> > The patch needs tests.
>
> Isolation tests have been added as described above.
>
> > May I also suggest a split of the multixact SQL functions into a
> > separate file, a src/backend/utils/adt/multixactfuncs.c?
>
> I agree that would be cleaner, but I’d prefer to keep the
> implementation in multixact.c for now to maintain focus on this patch
> and revisit the refactoring later.
>
> > +PG_FUNCTION_INFO_V1(pg_get_multixact_count);
>
> Removed -- now handled entirely by pg_proc.dat.
>
> > ...You may want to add an example of how one can use it for monitoring in the docs.
>
> I’ve added a usage example with sample output in the docs. If you had
> a different kind of demo in mind (e.g., creating multixacts manually
> and showing the output), please let me know.
>
> References:
> [0] https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.c#L130-L156
> [1] https://www.postgresql.org/message-id/flat/CACbFw60UOk6fCC02KsyT3OfU9Dnuq5roYxdw2aFisiN_p1L0bg%40mail.gmail.com
>
> Best regards,
> Naga Appani

Following up on my v2 from yesterday — the recent commit [0] changed
the directory layout, which broke the patch (v2). This v3 updates the
code to work with the new structure and also fixes some formatting
issues I noticed while revisiting the changes.

The rest of the patch remains the same as v2, which incorporated
feedback from Ashutosh and Michael (see my previous email for
details).

Please find v3 attached.

References:
[0] https://github.com/postgres/postgres/commit/4e23c9ef65accde7eb3e56aa28d50ae5cf79b64b

Best regards,
Naga Appani