Horton

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

torikoshia <torikoshia@oss.nttdata.com>

View thread
From: torikoshia <torikoshia@oss.nttdata.com>
To: Naga Appani <nagnrik@gmail.com>
Cc: Michael Paquier <michael@paquier.xyz>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Kirill Reshke <reshkekirill@gmail.com>, pgsql-hackers@postgresql.org
Date: 2025-08-22T00:28:49Z
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 2025-08-20 13:27, Naga Appani wrote:

Thanks for working on this!

> On Tue, Aug 19, 2025 at 1:32 AM Michael Paquier <michael@paquier.xyz> 
> wrote:
>> FWIW, I think that you should be a bit more careful before sending
>> updated patch sets.  You have missed an extra point I have raised
>> upthread about the refactoring pieces: the switch from
>> ReadMultiXactCounts() to GetMultiXactInfo() can be done in a patch of
>> its own.
>> 
>> So I have extracted this part from your latest patch, and applied it
>> independently of the SQL function business.  Now we are in an
>> advantageous position on HEAD: even if we do not conclude about the
>> SQL function to show the mxact numbers and offsets, we have the
>> function that gives an access to the data you are looking for.  In
>> short, it is now possible to provide an equivalent of the feature you
>> want outside of core.  Not saying that the patch cannot be useful, but
>> such refactoring pieces open more possibilities, and offer a cleaner
>> commit history with less churn in the main patches.
>> --
> 
> Thanks for the review and separating the refactoring into its own 
> commit.
> Point taken on being more careful when sending updated patch sets.
> I’ll make sure to keep
> refactoring and SQL layer changes clearly separated going forward.
> 
> Attached is v6, rebased on top of HEAD. This version is limited to the
> SQL function only.
> 

diff --git a/doc/src/sgml/maintenance.sgml 
b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..6f0e8d7c10a 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,12 +813,56 @@ HINT:  Execute a database-wide VACUUM in that 
database.
      <para>
       As a safety device, an aggressive vacuum scan will
       occur for any table whose multixact-age is greater than <xref
-     linkend="guc-autovacuum-multixact-freeze-max-age"/>.  Also, if the
-     storage occupied by multixacts members exceeds about 10GB, 
aggressive vacuum
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the 
number
+     of members created exceeds approximately 2^31 entries, aggressive 
vacuum
       scans will occur more often for all tables, starting with those 
that

Looking at commit ff20ccae9fdb, it seems that the documentation was 
intentionally written in terms of gigabytes rather than the number:

> The threshold is two billion members, which was interpreted as 2GB
> in the documentation. Fix to reflect that each member takes up five
> bytes, which translates to about 10GB. This is not exact, because of
> page boundaries. While at it, mention the maximum size 20GB.

Anyway, I also think, as Ashutosh suggested, that if we want to fix this 
documentation, it would be better to do so in a separate patch.

-- 
Regards,

--
Atsushi Torikoshi
Seconded from NTT DATA Japan Corporation to SRA OSS K.K.