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

From 0f9bff594eccf2f7aea288e73f8c147edf884857 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Fri, 6 Jun 2025 05:18:15 +0000
Subject: [PATCH v1] Add pg_get_multixact_count function and related

Add pg_get_multixact_count() SQL function for monitoring MultiXact usage

PostgreSQL exposes mxid_age() to track MultiXact ID wraparound risk,
but there is currently no SQL-accessible way to monitor MultiXact member
consumption, which can independently trigger aggressive vacuuming or
wraparound protection. The only workaround today involves scanning the
pg_multixact/members directory, which is I/O intensive and unsuitable
for monitoring tools.

This patch adds pg_get_multixact_count(), a SQL-callable function that
returns a composite record with two fields:
- multixacts:  number of MultiXact IDs that currently exist
- members:     number of MultiXact member entries that currently exist

The function calls ReadMultiXactCounts() and returns the values using the
standard record output convention.

Documentation has been added to func.sgml under a new section titled
"MultiXact Information Functions", with a cross-reference in maintenance.sgml
to help users track MultiXact usage relative to autovacuum thresholds.

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by:
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com

---
 doc/src/sgml/func.sgml                 | 58 ++++++++++++++++++++++++++
 doc/src/sgml/maintenance.sgml          |  8 ++++
 src/backend/access/transam/multixact.c | 30 +++++++++++++
 src/include/catalog/pg_proc.dat        | 15 +++++++
 4 files changed, 111 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f..bdb64f83c23 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28496,6 +28496,64 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-info-multixact-information">
+   <title>MultiXact Information Functions</title>
+
+   <para>
+    The function shown in <xref linkend="functions-multixact-information"/>
+    exposes internal MultiXact counters used by
+    <productname>PostgreSQL</productname>'s locking and transaction management subsystems.
+    It is primarily intended for monitoring and diagnostic purposes, such as analyzing
+    MultiXact consumption patterns or anticipating wraparound-related maintenance.
+   </para>
+
+   <table id="functions-multixact-information">
+    <title>MultiXact Information Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry">
+        <para role="func_signature">Function</para>
+        <para>Description</para>
+       </entry>
+      </row>
+     </thead>
+     <tbody>
+      <row>
+       <entry role="func_table_entry">
+        <para role="func_signature">
+         <indexterm><primary>pg_get_multixact_count</primary></indexterm>
+         <function>pg_get_multixact_count</function> ()
+         <returnvalue>record</returnvalue>
+        </para>
+        <para>
+         Returns a record with the fields <structfield>multixacts</structfield> and <structfield>members</structfield>:
+         <itemizedlist>
+          <listitem>
+           <para><structfield>multixacts</structfield>: Number of MultiXacts assigned.
+            PostgreSQL initiates aggressive autovacuum when this value grows beyond the threshold
+            defined by <varname>autovacuum_multixact_freeze_max_age</varname>, which is based on
+            the age of <literal>datminmxid</literal>. For more details, see
+            <ulink url="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND">
+            Routine Vacuuming: Multixact Wraparound</ulink>.</para>
+          </listitem>
+          <listitem>
+           <para><structfield>members</structfield>: Number of MultiXact member entries created.
+            These are stored in files under the <filename>pg_multixact/members</filename> subdirectory.
+            Wraparound occurs after approximately 4.29 billion entries (~20 GiB). PostgreSQL initiates
+            aggressive autovacuum when the number of members created exceeds approximately 2.145 billion
+            or when storage consumption in <filename>pg_multixact/members</filename> approaches 10 GiB.</para>
+          </listitem>
+         </itemizedlist>
+        </para>
+       </entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
 
   <sect1 id="functions-admin">
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 600e4b3f2f3..a445d1b061c 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -818,6 +818,14 @@ HINT:  Execute a database-wide VACUUM in that database.
      area can grow up to about 20GB before reaching wraparound.
     </para>
 
+    <para>
+     The <function><link linkend="functions-multixact-information">pg_get_multixact_count</link></function>
+     function provides a way to check how many multixacts and member entries have been allocated. This can
+     be useful for identifying unusual multixact activity, monitoring progress toward wraparound, anticipating
+     system-wide aggressive autovacuum as usage approaches critical thresholds, or verifying whether autovacuum
+     is keeping up with demand.
+    </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 3c06ac45532..ed29746eaa9 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -3585,3 +3585,33 @@ multixactmemberssyncfiletag(const FileTag *ftag, char *path)
 {
 	return SlruSyncFileTag(MultiXactMemberCtl, ftag, path);
 }
+
+/*
+ * Returns the current count of multixact members and multixact IDs
+ */
+PG_FUNCTION_INFO_V1(pg_get_multixact_count);
+
+Datum
+pg_get_multixact_count(PG_FUNCTION_ARGS)
+{
+	TupleDesc		tupdesc;
+	Datum			values[2];
+	bool			nulls[2] = {false, false};
+	MultiXactOffset	members;
+	uint32			multixacts;
+	HeapTuple		tuple;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errmsg("return type must be a row type")));
+
+	if (!ReadMultiXactCounts(&multixacts, &members))
+		ereport(ERROR,
+				(errmsg("could not read multixact counts")));
+
+	values[0] = UInt32GetDatum(multixacts);
+	values[1] = UInt32GetDatum(members);
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d3d28a263fa..09115ad1b35 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12556,4 +12556,19 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Returns current counts of multixact members and multixact IDs
+{
+  oid => '9001',
+  descr => 'get current multixact member and multixact ID counts',
+  proname => 'pg_get_multixact_count',
+  prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int4,int8}',
+  proargmodes => '{o,o}',
+  proargnames => '{multixacts,members}',
+  provolatile => 'v',
+  proparallel => 's',
+  prosrc => 'pg_get_multixact_count'
+},
+
 ]
-- 
2.47.1

diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 6b3f38a2fd6..8b31b57140d 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2863,17 +2863,16 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
  * exist.  Return false if unable to determine.
  */
 static bool
-ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
+ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members, MultiXactId *oldestMultiXactId)
 {
 	MultiXactOffset nextOffset;
 	MultiXactOffset oldestOffset;
-	MultiXactId oldestMultiXactId;
 	MultiXactId nextMultiXactId;
 	bool		oldestOffsetKnown;
 
 	LWLockAcquire(MultiXactGenLock, LW_SHARED);
 	nextOffset = MultiXactState->nextOffset;
-	oldestMultiXactId = MultiXactState->oldestMultiXactId;
+	*oldestMultiXactId = MultiXactState->oldestMultiXactId;
 	nextMultiXactId = MultiXactState->nextMXact;
 	oldestOffset = MultiXactState->oldestOffset;
 	oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
@@ -2883,7 +2882,7 @@ ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
 		return false;
 
 	*members = nextOffset - oldestOffset;
-	*multixacts = nextMultiXactId - oldestMultiXactId;
+	*multixacts = nextMultiXactId - *oldestMultiXactId;
 	return true;
 }
 
@@ -2922,9 +2921,10 @@ MultiXactMemberFreezeThreshold(void)
 	uint32		victim_multixacts;
 	double		fraction;
 	int			result;
+	MultiXactId oldestMultiXactId;
 
 	/* If we can't determine member space utilization, assume the worst. */
-	if (!ReadMultiXactCounts(&multixacts, &members))
+	if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
 		return 0;
 
 	/* If member space utilization is low, no special action is required. */
@@ -3503,22 +3503,24 @@ Datum
 pg_get_multixact_count(PG_FUNCTION_ARGS)
 {
 	TupleDesc		tupdesc;
-	Datum			values[2];
-	bool			nulls[2] = {false, false};
+	Datum			values[3];
+	bool			nulls[3] = {false, false, false};
 	MultiXactOffset	members;
 	uint32			multixacts;
 	HeapTuple		tuple;
+	MultiXactId oldestMultiXactId;
 
 	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
 		ereport(ERROR,
 				(errmsg("return type must be a row type")));
 
-	if (!ReadMultiXactCounts(&multixacts, &members))
+	if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
 		ereport(ERROR,
 				(errmsg("could not read multixact counts")));
 
 	values[0] = UInt32GetDatum(multixacts);
 	values[1] = UInt32GetDatum(members);
+	values[2] = UInt32GetDatum(oldestMultiXactId);
 
 	tuple = heap_form_tuple(tupdesc, values, nulls);
 	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0e511dd3473..2be0cd35e12 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12579,13 +12579,13 @@
 # Returns current counts of multixact members and multixact IDs
 {
   oid => '9001',
-  descr => 'get current multixact member and multixact ID counts',
+  descr => 'get current multixact member and multixact ID counts and oldest multixact',
   proname => 'pg_get_multixact_count',
   prorettype => 'record',
   proargtypes => '',
-  proallargtypes => '{int4,int8}',
-  proargmodes => '{o,o}',
-  proargnames => '{multixacts,members}',
+  proallargtypes => '{int4,int8,int4}',
+  proargmodes => '{o,o,o}',
+  proargnames => '{multixacts,members,oldestmultixact}',
   provolatile => 'v',
   proparallel => 's',
   prosrc => 'pg_get_multixact_count'

From 180ef463808023fa117595ce554326028724cf43 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Mon, 4 Aug 2025 03:17:28 +0000
Subject: [PATCH v2] Add pg_get_multixact_stats() SQL function for monitoring
 MultiXact usage

This patch adds pg_get_multixact_stats(), a SQL-callable function that returns
MultiXact statistics to aid in monitoring wraparound risk and vacuum behavior.
It reports:

multixacts: the number of MultiXact IDs created since the oldest one still needed
members: the number of MultiXact member entries that currently exist
oldest_multixact: the oldest MultiXact ID still needed by any database

The function modifies ReadMultiXactCounts() to expose the oldestMultiXactId and
returns all three values in a composite record. This allows users to monitor
MultiXact usage and identify potential wraparound issues, particularly useful
when combined with pg_get_multixact_members() to investigate specific MultiXacts.

Usage:
SELECT * FROM pg_get_multixact_stats();

Documentation is added to:
- "Transaction ID and Snapshot Information Functions" section in func.sgml
- "Multixacts and Wraparound" section in maintenance.sgml
  (routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND)

Isolation tests are added to verify:
- Initial state with zero MultiXacts
- MultiXact creation with overlapping shared locks
- Correct counting of MultiXacts and members
- Proper tracking of oldest MultiXact ID

Author: Naga Appani <nagnifk@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CAM2BeoX%2BRasKfG6W8w4qYZZz4BnhyEQMA_y5cEDnKEY_z8o9Czg%40mail.gmail.com
---
 doc/src/sgml/func.sgml                        | 32 ++++++++
 doc/src/sgml/maintenance.sgml                 | 37 ++++++++-
 src/backend/access/transam/multixact.c        | 79 ++++++++++++++-----
 src/include/catalog/pg_proc.dat               | 14 ++++
 .../isolation/expected/multixact_stats.out    | 59 ++++++++++++++
 src/test/isolation/specs/multixact_stats.spec | 35 ++++++++
 6 files changed, 233 insertions(+), 23 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 74a16af04ad..0255a51cdad 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27732,6 +27732,38 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+     <row>
+      <entry role="func_table_entry">
+       <para role="func_signature">
+        <indexterm><primary>pg_get_multixact_stats</primary></indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>multixacts</parameter> <type>integer</type>,
+          <parameter>members</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>integer</type> )
+       </para>
+
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>multixacts</literal> is the number of multixact IDs assigned,
+        <literal>members</literal> is the number of multixact member entries created,
+        and <literal>oldest_multixact</literal> is the oldest multixact ID still in use.
+        These values can be used to monitor multixact consumption and anticipate
+        autovacuum behavior. See <xref linkend="vacuum-for-multixact-wraparound"/>
+        for further details on multixact wraparound.
+       </para>
+
+       <para>
+        <literal>SELECT * FROM pg_get_multixact_stats();</literal>
+<programlisting>
+ multixacts |   members   | oldest_multixact
+------------+-------------+------------------
+ 182371396  | 2826221174  | 754321
+</programlisting>
+       </para>
+      </entry>
+     </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..d6bd305b0b0 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,12 +813,41 @@ 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 either
+     the storage occupied by multixact members exceeds about 10GB or the number
+     of members created exceeds approximately 2 billion entries, aggressive vacuum
      scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
+     have the oldest multixact-age. Both of these kinds of aggressive
      scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     area can grow up to about 20GB or approximately 4 billion entries before
+     reaching wraparound.
+    </para>
+
+    <para>
+     The <function>pg_get_multixact_stats()</function> function provides a way
+     to monitor multixact allocation and usage patterns in real time. By exposing
+     the age of the oldest multixact ID, number of member entries, and the oldest multixact ID still in use, it helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+         Identify unusual multixact activity from concurrent row-level locks
+         or foreign key operations
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+         Monitor progress toward wraparound thresholds that trigger aggressive
+         autovacuum (approximately 2 billion members or 10GB storage)
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+         Verify whether autovacuum is effectively managing multixact cleanup
+         before reaching critical thresholds
+       </simpara>
+      </listitem>
+     </orderedlist>
+     See <xref linkend="functions-info-snapshot"/> for details.
     </para>
 
     <para>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 3cb09c3d598..59e8fc17b7f 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2863,28 +2863,27 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
  * exist.  Return false if unable to determine.
  */
 static bool
-ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
+ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members, MultiXactId *oldestMultiXactId)
 {
-	MultiXactOffset nextOffset;
-	MultiXactOffset oldestOffset;
-	MultiXactId oldestMultiXactId;
-	MultiXactId nextMultiXactId;
-	bool		oldestOffsetKnown;
+    MultiXactOffset nextOffset;
+    MultiXactOffset oldestOffset;
+    MultiXactId nextMultiXactId;
+    bool oldestOffsetKnown;
 
-	LWLockAcquire(MultiXactGenLock, LW_SHARED);
-	nextOffset = MultiXactState->nextOffset;
-	oldestMultiXactId = MultiXactState->oldestMultiXactId;
-	nextMultiXactId = MultiXactState->nextMXact;
-	oldestOffset = MultiXactState->oldestOffset;
-	oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
-	LWLockRelease(MultiXactGenLock);
+    LWLockAcquire(MultiXactGenLock, LW_SHARED);
+    nextOffset = MultiXactState->nextOffset;
+    *oldestMultiXactId = MultiXactState->oldestMultiXactId;  /* Use the parameter directly */
+    nextMultiXactId = MultiXactState->nextMXact;
+    oldestOffset = MultiXactState->oldestOffset;
+    oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
+    LWLockRelease(MultiXactGenLock);
 
-	if (!oldestOffsetKnown)
-		return false;
+    if (!oldestOffsetKnown)
+        return false;
 
-	*members = nextOffset - oldestOffset;
-	*multixacts = nextMultiXactId - oldestMultiXactId;
-	return true;
+    *members = nextOffset - oldestOffset;
+    *multixacts = nextMultiXactId - *oldestMultiXactId;  /* Use the parameter */
+    return true;
 }
 
 /*
@@ -2922,9 +2921,10 @@ MultiXactMemberFreezeThreshold(void)
 	uint32		victim_multixacts;
 	double		fraction;
 	int			result;
+	MultiXactId oldestMultiXactId;
 
 	/* If we can't determine member space utilization, assume the worst. */
-	if (!ReadMultiXactCounts(&multixacts, &members))
+	if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
 		return 0;
 
 	/* If member space utilization is low, no special action is required. */
@@ -3493,3 +3493,44 @@ multixactmemberssyncfiletag(const FileTag *ftag, char *path)
 {
 	return SlruSyncFileTag(MultiXactMemberCtl, ftag, path);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * SQL-callable function to retrieve MultiXact statistics.
+ *
+ * Returns a composite row containing:
+ * - total number of MultiXact IDs created since startup,
+ * - total number of MultiXact members created,
+ * - the oldest existing MultiXact ID.
+ *
+ * This is primarily useful for monitoring MultiXact usage and ensuring
+ * appropriate wraparound protection.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[3];
+	bool		nulls[3] = {false, false, false};
+	MultiXactOffset members;
+	MultiXactId oldestMultiXactId;
+	uint32		multixacts;
+	HeapTuple	tuple;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errmsg("return type must be a row type")));
+
+	if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
+		PG_RETURN_NULL();
+
+	values[0] = UInt32GetDatum(multixacts);
+	values[1] = UInt32GetDatum(members);
+	values[2] = UInt32GetDatum(oldestMultiXactId);
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
+
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3ee8fed7e53..756ba39425c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,18 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+{
+  oid => '9001',
+  descr => 'get current multixact member and multixact ID counts and oldest multixact',
+  proname => 'pg_get_multixact_stats',
+  prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int4,int8,int4}',
+  proargmodes => '{o,o,o}',
+  proargnames => '{multixacts,members,oldest_multixact}',
+  provolatile => 'v',
+  proparallel => 's',
+  prosrc => 'pg_get_multixact_stats'
+},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..54e3238d727
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,59 @@
+Parsed test spec with 3 sessions
+
+starting permutation: stats_init check begin1 lock1 begin2 lock2 check commit1 commit2 check
+step stats_init: 
+    CREATE TEMP TABLE stats_before AS 
+    SELECT multixacts, members, oldest_multixact FROM pg_get_multixact_stats(); 
+
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         0|      0|               1
+(1 row)
+
+step begin1: BEGIN;
+step lock1: SELECT * FROM multixact_test WHERE id = 1 FOR SHARE;
+id|val
+--+---
+ 1| 10
+(1 row)
+
+step begin2: BEGIN;
+step lock2: SELECT * FROM multixact_test WHERE id = 1 FOR SHARE;
+id|val
+--+---
+ 1| 10
+(1 row)
+
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         1|      3|               1
+(1 row)
+
+step commit1: COMMIT;
+step commit2: COMMIT;
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         1|      3|               1
+(1 row)
+
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..53fcad38c54
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,35 @@
+setup
+{
+    CREATE TABLE multixact_test(id int PRIMARY KEY, val int);
+    INSERT INTO multixact_test VALUES (1, 10);
+}
+
+teardown
+{
+    DROP TABLE multixact_test;
+}
+
+session s1
+step begin1     { BEGIN; }
+step lock1      { SELECT * FROM multixact_test WHERE id = 1 FOR SHARE; }
+step commit1    { COMMIT; }
+
+session s2
+step begin2     { BEGIN; }
+step lock2      { SELECT * FROM multixact_test WHERE id = 1 FOR SHARE; }
+step commit2    { COMMIT; }
+
+session s3
+step stats_init { 
+    CREATE TEMP TABLE stats_before AS 
+    SELECT multixacts, members, oldest_multixact FROM pg_get_multixact_stats(); 
+}
+step check      {
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+}
+
+permutation stats_init check begin1 lock1 begin2 lock2 check commit1 commit2 check
-- 
2.47.3

From 5b3341fa9759f131c5e368b6debcb8f208c96a12 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Mon, 4 Aug 2025 21:02:26 +0000
Subject: [PATCH v2] Add pg_get_multixact_stats() SQL function for monitoring
 multixact usage

This patch adds pg_get_multixact_stats(), a SQL-callable function that returns
multixact statistics to aid in monitoring wraparound risk and vacuum behavior.
It reports:

multixacts: the number of multixact IDs created since the oldest one still needed
members: the number of multixact member entries that currently exist
oldest_multixact: the oldest multixact ID still needed by any database

The function modifies ReadMultiXactCounts() to expose the oldestMultiXactId and
returns all three values in a composite record. This allows users to monitor
multixact usage and identify potential wraparound issues, particularly useful
when combined with pg_get_multixact_members() to investigate specific multixacts.

Usage:
SELECT * FROM pg_get_multixact_stats();

Documentation is added to:
- "Transaction ID and Snapshot Information Functions" section in func.sgml
- "Multixacts and Wraparound" section in maintenance.sgml
  (routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND)

Isolation tests are added to verify:
- Initial state with zero multixacts
- multixact creation with overlapping shared locks
- Correct counting of multixacts and members
- Proper tracking of oldest multixact ID

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CAM2BeoX%2BRasKfG6W8w4qYZZz4BnhyEQMA_y5cEDnKEY_z8o9Czg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              | 32 ++++++++++
 doc/src/sgml/maintenance.sgml                 | 37 ++++++++++--
 src/backend/access/transam/multixact.c        | 50 ++++++++++++++--
 src/include/catalog/pg_proc.dat               | 15 +++++
 .../isolation/expected/multixact_stats.out    | 59 +++++++++++++++++++
 src/test/isolation/specs/multixact_stats.spec | 35 +++++++++++
 6 files changed, 219 insertions(+), 9 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index b507bfaf64b..75ce2c6e403 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2967,6 +2967,38 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+     <row>
+      <entry role="func_table_entry">
+       <para role="func_signature">
+        <indexterm><primary>pg_get_multixact_stats</primary></indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>multixacts</parameter> <type>integer</type>,
+          <parameter>members</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>integer</type> )
+       </para>
+
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>multixacts</literal> is the number of multixact IDs assigned,
+        <literal>members</literal> is the number of multixact member entries created,
+        and <literal>oldest_multixact</literal> is the oldest MultiXact ID still in use.
+        These values can be used to monitor multixact consumption and anticipate
+        autovacuum behavior. See <xref linkend="vacuum-for-multixact-wraparound"/>
+        for further details on multixact wraparound.
+       </para>
+
+       <para>
+        <literal>SELECT * FROM pg_get_multixact_stats();</literal>
+<programlisting>
+ multixacts |   members   | oldest_multixact
+------------+-------------+------------------
+ 182371396  | 2826221174  | 754321
+</programlisting>
+       </para>
+      </entry>
+     </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..d6bd305b0b0 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,12 +813,41 @@ 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 either
+     the storage occupied by multixact members exceeds about 10GB or the number
+     of members created exceeds approximately 2 billion entries, aggressive vacuum
      scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
+     have the oldest multixact-age. Both of these kinds of aggressive
      scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     area can grow up to about 20GB or approximately 4 billion entries before
+     reaching wraparound.
+    </para>
+
+    <para>
+     The <function>pg_get_multixact_stats()</function> function provides a way
+     to monitor multixact allocation and usage patterns in real time. By exposing
+     counts of multixacts, member entries, and the oldest multixact ID, it helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+         Identify unusual multixact activity from concurrent row-level locks
+         or foreign key operations
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+         Monitor progress toward wraparound thresholds that trigger aggressive
+         autovacuum (approximately 2 billion members or 10GB storage)
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+         Verify whether autovacuum is effectively managing multixact cleanup
+         before reaching critical thresholds
+       </simpara>
+      </listitem>
+     </orderedlist>
+     See <xref linkend="functions-info-snapshot"/> for details.
     </para>
 
     <para>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 3cb09c3d598..ec7ff959416 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2863,17 +2863,16 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
  * exist.  Return false if unable to determine.
  */
 static bool
-ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
+ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members, MultiXactId *oldestMultiXactId)
 {
 	MultiXactOffset nextOffset;
 	MultiXactOffset oldestOffset;
-	MultiXactId oldestMultiXactId;
 	MultiXactId nextMultiXactId;
 	bool		oldestOffsetKnown;
 
 	LWLockAcquire(MultiXactGenLock, LW_SHARED);
 	nextOffset = MultiXactState->nextOffset;
-	oldestMultiXactId = MultiXactState->oldestMultiXactId;
+	*oldestMultiXactId = MultiXactState->oldestMultiXactId;
 	nextMultiXactId = MultiXactState->nextMXact;
 	oldestOffset = MultiXactState->oldestOffset;
 	oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
@@ -2883,7 +2882,7 @@ ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
 		return false;
 
 	*members = nextOffset - oldestOffset;
-	*multixacts = nextMultiXactId - oldestMultiXactId;
+	*multixacts = nextMultiXactId - *oldestMultiXactId;
 	return true;
 }
 
@@ -2922,9 +2921,10 @@ MultiXactMemberFreezeThreshold(void)
 	uint32		victim_multixacts;
 	double		fraction;
 	int			result;
+	MultiXactId oldestMultiXactId;
 
 	/* If we can't determine member space utilization, assume the worst. */
-	if (!ReadMultiXactCounts(&multixacts, &members))
+	if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
 		return 0;
 
 	/* If member space utilization is low, no special action is required. */
@@ -3493,3 +3493,43 @@ multixactmemberssyncfiletag(const FileTag *ftag, char *path)
 {
 	return SlruSyncFileTag(MultiXactMemberCtl, ftag, path);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * SQL-callable function to retrieve MultiXact statistics.
+ *
+ * Returns a composite row containing:
+ * - total number of MultiXact IDs created since startup,
+ * - total number of MultiXact members created,
+ * - the oldest existing MultiXact ID.
+ *
+ * This is primarily useful for monitoring MultiXact usage and ensuring
+ * appropriate wraparound protection.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[3];
+	bool		nulls[3] = {false, false, false};
+	MultiXactOffset members;
+	MultiXactId oldestMultiXactId;
+	uint32		multixacts;
+	HeapTuple	tuple;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errmsg("return type must be a row type")));
+
+	if (!ReadMultiXactCounts(&multixacts, &members, &oldestMultiXactId))
+		PG_RETURN_NULL();
+
+	values[0] = UInt32GetDatum(multixacts);
+	values[1] = UInt32GetDatum(members);
+	values[2] = UInt32GetDatum(oldestMultiXactId);
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..9d9e28c2770 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,19 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# MultiXact stats
+{
+	oid => '9001',
+	descr => 'get current multixact member and multixact ID counts and oldest multixact',
+	proname => 'pg_get_multixact_stats',
+	prorettype => 'record',
+	proargtypes => '',
+	proallargtypes => '{int4,int8,int4}',
+	proargmodes => '{o,o,o}',
+	proargnames => '{multixacts,members,oldest_multixact}',
+	provolatile => 'v',
+	proparallel => 's',
+	prosrc => 'pg_get_multixact_stats'
+},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..54e3238d727
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,59 @@
+Parsed test spec with 3 sessions
+
+starting permutation: stats_init check begin1 lock1 begin2 lock2 check commit1 commit2 check
+step stats_init: 
+    CREATE TEMP TABLE stats_before AS 
+    SELECT multixacts, members, oldest_multixact FROM pg_get_multixact_stats(); 
+
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         0|      0|               1
+(1 row)
+
+step begin1: BEGIN;
+step lock1: SELECT * FROM multixact_test WHERE id = 1 FOR SHARE;
+id|val
+--+---
+ 1| 10
+(1 row)
+
+step begin2: BEGIN;
+step lock2: SELECT * FROM multixact_test WHERE id = 1 FOR SHARE;
+id|val
+--+---
+ 1| 10
+(1 row)
+
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         1|      3|               1
+(1 row)
+
+step commit1: COMMIT;
+step commit2: COMMIT;
+step check: 
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+
+multixacts|members|oldest_multixact
+----------+-------+----------------
+         1|      3|               1
+(1 row)
+
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..53fcad38c54
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,35 @@
+setup
+{
+    CREATE TABLE multixact_test(id int PRIMARY KEY, val int);
+    INSERT INTO multixact_test VALUES (1, 10);
+}
+
+teardown
+{
+    DROP TABLE multixact_test;
+}
+
+session s1
+step begin1     { BEGIN; }
+step lock1      { SELECT * FROM multixact_test WHERE id = 1 FOR SHARE; }
+step commit1    { COMMIT; }
+
+session s2
+step begin2     { BEGIN; }
+step lock2      { SELECT * FROM multixact_test WHERE id = 1 FOR SHARE; }
+step commit2    { COMMIT; }
+
+session s3
+step stats_init { 
+    CREATE TEMP TABLE stats_before AS 
+    SELECT multixacts, members, oldest_multixact FROM pg_get_multixact_stats(); 
+}
+step check      {
+    SELECT 
+        multixacts,
+        members,
+        oldest_multixact 
+    FROM pg_get_multixact_stats();
+}
+
+permutation stats_init check begin1 lock1 begin2 lock2 check commit1 commit2 check
-- 
2.47.3

From 1a85c94f93985d39a6090a35d155f6ee6c788c14 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Sat, 16 Aug 2025 17:51:52 +0000
Subject: [PATCH v4] Rename ReadMultiXactCounts() to GetMultiXactInfo() and
 make it public

Following review feedback from Michael Paquier, this patch exposes
GetMultiXactInfo(), a public accessor that returns snapshot of
MultiXact state (counts and horizons) in one call, replacing
ReadMultiXactCounts().

Provide a single snapshot of MultiXact state and return:
- multixacts
- members
- oldestMultiXactId
- oldestOffset

Return false when the oldest offset is not known; in that case set all
outputs to 0/invalid for consistency.  Declare the function in
multixact.h and switch MultiXactMemberFreezeThreshold() to the new API.

This accessor underpins pg_get_multixact_stats() and is available to
extensions that wish to monitor MultiXact usage.

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 src/backend/access/transam/multixact.c | 45 ++++++++++++++++++--------
 src/include/access/multixact.h         |  1 +
 2 files changed, 32 insertions(+), 14 deletions(-)

diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 3cb09c3d598..eeeec81abc9 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2859,31 +2859,46 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
 }
 
 /*
- * Determine how many multixacts, and how many multixact members, currently
- * exist.  Return false if unable to determine.
+ * GetMultiXactInfo
+ *
+ * Returns information about current MultiXact state in a single atomic read:
+ *		- multixacts: Number of MultiXacts (nextMultiXactId - oldestMultiXactId)
+ *		- members: Number of member entries (nextOffset - oldestOffset)
+ *		- oldestMultiXactId: Oldest MultiXact ID still in use
+ *		- oldestOffset: Oldest offset still in use
+ *
+ * Returns false if the oldest offset is not known, in which case all output
+ * parameters are set to 0/invalid values for consistency.
  */
-static bool
-ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
+bool
+GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members,
+				 MultiXactId *oldestMultiXactId, MultiXactOffset *oldestOffset)
 {
-	MultiXactOffset nextOffset;
-	MultiXactOffset oldestOffset;
-	MultiXactId oldestMultiXactId;
-	MultiXactId nextMultiXactId;
-	bool		oldestOffsetKnown;
+	MultiXactOffset	nextOffset;
+	MultiXactId		nextMultiXactId;
+	bool			oldestOffsetKnown;
 
+	/* Take one consistent snapshot of the state */
 	LWLockAcquire(MultiXactGenLock, LW_SHARED);
 	nextOffset = MultiXactState->nextOffset;
-	oldestMultiXactId = MultiXactState->oldestMultiXactId;
+	*oldestMultiXactId = MultiXactState->oldestMultiXactId;
 	nextMultiXactId = MultiXactState->nextMXact;
-	oldestOffset = MultiXactState->oldestOffset;
+	*oldestOffset = MultiXactState->oldestOffset;
 	oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
 	LWLockRelease(MultiXactGenLock);
 
 	if (!oldestOffsetKnown)
+	{
+		/* Set all outputs to 0/invalid for consistency */
+		*members = 0;
+		*multixacts = 0;
+		*oldestMultiXactId = InvalidMultiXactId;
+		*oldestOffset = 0;
 		return false;
+	}
 
-	*members = nextOffset - oldestOffset;
-	*multixacts = nextMultiXactId - oldestMultiXactId;
+	*members = nextOffset - *oldestOffset;
+	*multixacts = nextMultiXactId - *oldestMultiXactId;
 	return true;
 }
 
@@ -2922,9 +2937,11 @@ MultiXactMemberFreezeThreshold(void)
 	uint32		victim_multixacts;
 	double		fraction;
 	int			result;
+	MultiXactId	oldestMultiXactId;
+	MultiXactOffset oldestOffset;
 
 	/* If we can't determine member space utilization, assume the worst. */
-	if (!ReadMultiXactCounts(&multixacts, &members))
+	if (!GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
 		return 0;
 
 	/* If member space utilization is low, no special action is required. */
diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h
index b876e98f46e..e0878461c2c 100644
--- a/src/include/access/multixact.h
+++ b/src/include/access/multixact.h
@@ -158,5 +158,6 @@ extern void multixact_desc(StringInfo buf, XLogReaderState *record);
 extern const char *multixact_identify(uint8 info);
 extern char *mxid_to_string(MultiXactId multi, int nmembers,
 							MultiXactMember *members);
+extern bool GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members, MultiXactId *oldestMultiXactId, MultiXactOffset *oldestOffset);
 
 #endif							/* MULTIXACT_H */
-- 
2.47.3

From 46c0c3d632a069a70923dd8d378b6c5d92b7eb1b Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Sat, 16 Aug 2025 17:52:06 +0000
Subject: [PATCH v4] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Expose multixact state via a new SQL-callable function
pg_get_multixact_stats(), returning:

- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- oldest_multixact   : oldest MultiXact ID still needed
- oldest_offset      : oldest member offset still in use

The function returns NULLs if the MultiXact subsystem is not yet
initialized.

An isolation test (multixact_stats) asserts only invariants that are
stable while a newly created multixact is pinned: (1) adding a second
locker on the same tuple increases members by ≥1; (2) num_mxids and
num_members do not decrease across snapshots; and (3) oldest_* never
decrease. The test prints a deterministic key/value table
("assertion | ok") and makes no assertions after locks are released,
so it remains robust even if background VACUUM/FREEZE runs.

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- new module: src/backend/utils/adt/multixactfuncs.c
- pg_proc.dat entry
- meson.build integration

Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
---
 doc/src/sgml/func/func-info.sgml              |  28 ++++
 doc/src/sgml/maintenance.sgml                 |  54 +++++++-
 src/backend/utils/adt/Makefile                |   1 +
 src/backend/utils/adt/meson.build             |   1 +
 src/backend/utils/adt/multixactfuncs.c        |  62 +++++++++
 src/include/catalog/pg_proc.dat               |  15 ++
 .../isolation/expected/multixact_stats.out    |  94 +++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 128 ++++++++++++++++++
 9 files changed, 379 insertions(+), 5 deletions(-)
 create mode 100644 src/backend/utils/adt/multixactfuncs.c
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..ea063f6a81d 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,34 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type>,
+          <parameter>oldest_offset</parameter> <type>bigint</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the number of multixact IDs assigned,
+        <literal>num_members</literal> is the number of multixact member entries created,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use, and
+        <literal>oldest_offset</literal> is the oldest member offset still in use.
+        These values can be used to monitor multixact consumption and anticipate
+        autovacuum behavior. See <xref linkend="vacuum-for-multixact-wraparound"/>
+        for further details on multixact wraparound.
+       </para>
+       <para>
+        This is a live snapshot of shared counters; the numbers can change between calls, 
+        even within the same transaction.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..e3a63c5b864 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 billion entries, aggressive vacuum
      scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. The members can grow 
+     up to approximately 4 billion entries before reaching wraparound.
+    </para>
+
+    <para>
+     The <function>pg_get_multixact_stats()</function> function, described in
+     <xref linkend="functions-pg-snapshot"/>, provides a way to monitor
+     multixact allocation and usage patterns in real time. For example:
+     <programlisting>
+postgres=# SELECT * FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | oldest_multixact | oldest_offset
+-----------+-------------+------------------+---------------
+  99883849 |   773468747 |         39974368 |     351952978
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~100 million
+     multixact IDs and ~773 million member entries have been created since the oldest
+     surviving multixact (ID 39974368). By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in num_mxids might indicate
+        multiple sessions running UPDATE statements with foreign key checks,
+        concurrent SELECT FOR SHARE operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while num_members grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
     </para>
 
     <para>
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index ffeacf2b819..cc68ac545a5 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -68,6 +68,7 @@ OBJS = \
 	misc.o \
 	multirangetypes.o \
 	multirangetypes_selfuncs.o \
+	multixactfuncs.o \
 	name.o \
 	network.o \
 	network_gist.o \
diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build
index ed9bbd7b926..dac372c3bea 100644
--- a/src/backend/utils/adt/meson.build
+++ b/src/backend/utils/adt/meson.build
@@ -55,6 +55,7 @@ backend_sources += files(
   'misc.c',
   'multirangetypes.c',
   'multirangetypes_selfuncs.c',
+  'multixactfuncs.c',
   'name.c',
   'network.c',
   'network_gist.c',
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
new file mode 100644
index 00000000000..faf02bd1626
--- /dev/null
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -0,0 +1,62 @@
+/*-------------------------------------------------------------------------
+ * multixactfuncs.c
+ *		Functions for reporting on multixact state.
+ *
+ * This module provides SQL-callable functions that expose internal multixact
+ * state information for monitoring usage and detecting potential wraparound
+ * conditions that may require vacuum maintenance.
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *		src/backend/utils/adt/multixactfuncs.c
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/multixact.h"
+#include "funcapi.h"
+#include "utils/builtins.h"
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current MultiXact usage:
+ *		- num_mxids: Number of MultiXact IDs in use
+ *		- num_members: Total number of member entries
+ *		- oldest_multixact: Oldest MultiXact ID still needed
+ *		- oldest_offset: Oldest offset still in use
+ *
+ * Returns a row of NULLs if the MultiXact system is not yet initialized.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[4];
+	bool		nulls[4] = {true, true, true, true};
+	MultiXactOffset members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset oldestOffset;
+	HeapTuple	tuple;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+	{
+		values[0] = Int32GetDatum(multixacts);
+		values[1] = Int64GetDatum(members);
+		values[2] = UInt32GetDatum(oldestMultiXactId);
+		values[3] = Int64GetDatum(oldestOffset);
+		nulls[0] = nulls[1] = nulls[2] = nulls[3] = false;
+	}
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..837bba938e6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,19 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get MultiXact state 
+{
+    oid => '9001',
+    descr => 'get current multixact member and multixact ID counts and oldest values',
+    proname => 'pg_get_multixact_stats',
+    prorettype => 'record',
+    proargtypes => '',
+    proallargtypes => '{int4,int8,xid,int8}',
+    proargmodes => '{o,o,o,o}',
+    proargnames => '{num_mxids,num_members,oldest_multixact,oldest_offset}',
+    provolatile => 'v',
+    proparallel => 's',
+    prosrc => 'pg_get_multixact_stats'
+},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..2893c4d9f36
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,94 @@
+Parsed test spec with 3 sessions
+
+starting permutation: d_begin snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned d_commit s1_commit s2_commit
+step d_begin: BEGIN; SET client_min_messages = warning;
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |t 
+is_members_nondec_01    |t 
+is_members_nondec_12    |t 
+(13 rows)
+
+step d_commit: COMMIT;
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 4411d3c86dd..7da500bf6cf 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -117,3 +117,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..cbf4b57294e
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,128 @@
+# High-signal invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second locker arrived,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+# NOTE: Snapshots snap0 and subsequent checks are taken inside an open driver
+# transaction to narrow the window for unrelated truncation between snapshots.
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : “in-use” deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can’t advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two lockers on the SAME tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Driver session: keep a transaction open while we take snapshots and check.
+session "driver"
+step d_begin { BEGIN; SET client_min_messages = warning; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row (still in driver xact).
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members (still in driver xact).
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_init_oldest_off       : oldest_offset is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_oldest_off_nondec_01  : oldest_offset did not decrease (snap0→snap1)
+#   is_oldest_off_nondec_12  : oldest_offset did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+step d_commit { COMMIT; }
+
+permutation d_begin snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned d_commit s1_commit s2_commit
+
-- 
2.47.3

From 883781d4da9c133aa1c2408379276bb4f52bf3a8 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Mon, 18 Aug 2025 20:51:25 +0000
Subject: [PATCH v5] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_bytes      : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed
- oldest_offset      : oldest member offset still in use

This patch:
1. Renames ReadMultiXactCounts() to GetMultiXactInfo() and makes it public
   - Provides a single accessor for MultiXact state
   - Returns counts and horizons in one call

2. Adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Returns NULLs if MultiXact system not initialized
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  31 +++++
 doc/src/sgml/maintenance.sgml                 |  54 +++++++-
 src/backend/access/transam/multixact.c        |  43 ++++--
 src/backend/utils/adt/multixactfuncs.c        |  52 +++++++
 src/include/access/multixact.h                |   1 +
 src/include/catalog/pg_proc.dat               |  15 +++
 .../isolation/expected/multixact_stats.out    |  94 +++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 127 ++++++++++++++++++
 9 files changed, 399 insertions(+), 19 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..9dedc3715d7 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,37 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_bytes</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type>,
+          <parameter>oldest_offset</parameter> <type>bigint</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the number of multixact IDs assigned,
+        <literal>num_members</literal> is the number of multixact member entries created,
+        <literal>members_bytes</literal> is the storage occupied by <literal>num_members</literal>
+        in <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use, and
+        <literal>oldest_offset</literal> is the oldest member offset still in use.
+        These values can be used to monitor multixact consumption and anticipate
+        autovacuum behavior. See <xref linkend="vacuum-for-multixact-wraparound"/>
+        for further details on multixact wraparound.
+       </para>
+       <para>
+        This is a live snapshot of shared counters; the numbers can change between calls,
+        even within the same transaction.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..badd3392c4f 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 billion entries, aggressive vacuum
      scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. The members can grow
+     up to approximately 4 billion entries before reaching wraparound.
+    </para>
+
+    <para>
+     The <function>pg_get_multixact_stats()</function> function, described in
+     <xref linkend="functions-pg-snapshot"/>, provides a way to monitor
+     multixact allocation and usage patterns in real time. For example:
+     <programlisting>
+postgres=# SELECT  num_mxids,num_members,oldest_multixact,oldest_offset FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | oldest_multixact | oldest_offset
+-----------+-------------+------------------+---------------
+  99883849 |   773468747 |         39974368 |     351952978
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~100 million
+     multixact IDs and ~773 million member entries have been created since the oldest
+     surviving multixact (ID 39974368). By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in num_mxids might indicate
+        multiple sessions running UPDATE statements with foreign key checks,
+        concurrent SELECT FOR SHARE operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while num_members grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
     </para>
 
     <para>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 886740d2d55..5fb7c12fdce 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2857,31 +2857,44 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
 }
 
 /*
- * Determine how many multixacts, and how many multixact members, currently
- * exist.  Return false if unable to determine.
+ * GetMultiXactInfo
+ *
+ * Returns information about current MultiXact state:
+ *		- multixacts: Number of MultiXacts (nextMultiXactId - oldestMultiXactId)
+ *		- members: Number of member entries (nextOffset - oldestOffset)
+ *		- oldestMultiXactId: Oldest MultiXact ID still in use
+ *		- oldestOffset: Oldest offset still in use
+ *
+ * Returns false if the oldest offset is not known, in which case all output
+ * parameters are set to 0/invalid values for consistency.
  */
-static bool
-ReadMultiXactCounts(uint32 *multixacts, MultiXactOffset *members)
+bool
+GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members,
+				 MultiXactId *oldestMultiXactId, MultiXactOffset *oldestOffset)
 {
-	MultiXactOffset nextOffset;
-	MultiXactOffset oldestOffset;
-	MultiXactId oldestMultiXactId;
-	MultiXactId nextMultiXactId;
-	bool		oldestOffsetKnown;
+	MultiXactOffset	nextOffset;
+	MultiXactId 	nextMultiXactId;
+	bool 		oldestOffsetKnown;
 
 	LWLockAcquire(MultiXactGenLock, LW_SHARED);
 	nextOffset = MultiXactState->nextOffset;
-	oldestMultiXactId = MultiXactState->oldestMultiXactId;
+	*oldestMultiXactId = MultiXactState->oldestMultiXactId;
 	nextMultiXactId = MultiXactState->nextMXact;
-	oldestOffset = MultiXactState->oldestOffset;
+	*oldestOffset = MultiXactState->oldestOffset;
 	oldestOffsetKnown = MultiXactState->oldestOffsetKnown;
 	LWLockRelease(MultiXactGenLock);
 
 	if (!oldestOffsetKnown)
+	{
+		*members = 0;
+		*multixacts = 0;
+		*oldestMultiXactId = InvalidMultiXactId;
+		*oldestOffset = 0;
 		return false;
+	}
 
-	*members = nextOffset - oldestOffset;
-	*multixacts = nextMultiXactId - oldestMultiXactId;
+	*members = nextOffset - *oldestOffset;
+	*multixacts = nextMultiXactId - *oldestMultiXactId;
 	return true;
 }
 
@@ -2920,9 +2933,11 @@ MultiXactMemberFreezeThreshold(void)
 	uint32		victim_multixacts;
 	double		fraction;
 	int			result;
+	MultiXactId	oldestMultiXactId;
+	MultiXactOffset oldestOffset;
 
 	/* If we can't determine member space utilization, assume the worst. */
-	if (!ReadMultiXactCounts(&multixacts, &members))
+	if (!GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
 		return 0;
 
 	/* If member space utilization is low, no special action is required. */
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index e74ea938348..ba9e8313ab4 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -85,3 +85,55 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current MultiXact usage:
+ *		- num_mxids: Number of MultiXact IDs in use
+ *		- num_members: Total number of member entries
+ *		- oldest_multixact: Oldest MultiXact ID still needed
+ *		- oldest_offset: Oldest offset still in use
+ *
+ * Returns a row of NULLs if the MultiXact system is not yet initialized.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[5];
+	bool		nulls[5] = {true, true, true, true, true};
+	MultiXactOffset members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset oldestOffset;
+	int64		membersBytes;
+	HeapTuple	tuple;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+	{
+		/*
+		* Calculate approximate storage space:
+		* - Members are stored in groups of 4
+		* - Each group takes 20 bytes (5 bytes per member)
+		* Note: This ignores small page overhead (12 bytes per 8KB)
+		*/
+		membersBytes = (int64) members * 5;
+
+		values[0] = Int32GetDatum(multixacts);
+		values[1] = Int64GetDatum(members);
+		values[2] = Int64GetDatum(membersBytes);
+		values[3] = UInt32GetDatum(oldestMultiXactId);
+		values[4] = Int64GetDatum(oldestOffset);
+		nulls[0] = nulls[1] = nulls[2] = nulls[3] = nulls[4] = false;
+	}
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h
index 6607b645a18..19de74950cb 100644
--- a/src/include/access/multixact.h
+++ b/src/include/access/multixact.h
@@ -159,5 +159,6 @@ extern const char *multixact_identify(uint8 info);
 extern char *mxid_to_string(MultiXactId multi, int nmembers,
 							MultiXactMember *members);
 extern char *mxstatus_to_string(MultiXactStatus status);
+extern bool GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members, MultiXactId *oldestMultiXactId, MultiXactOffset *oldestOffset);
 
 #endif							/* MULTIXACT_H */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..985364e0cd6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,19 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get MultiXact state
+{
+  oid => '9001',
+  descr => 'get current multixact member and multixact ID counts and oldest values',
+  proname => 'pg_get_multixact_stats',
+  prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int4,int8,int8,xid,int8}',
+  proargmodes => '{o,o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_bytes,oldest_multixact,oldest_offset}',
+  provolatile => 'v',
+  proparallel => 's',
+  prosrc => 'pg_get_multixact_stats'
+},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..2893c4d9f36
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,94 @@
+Parsed test spec with 3 sessions
+
+starting permutation: d_begin snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned d_commit s1_commit s2_commit
+step d_begin: BEGIN; SET client_min_messages = warning;
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |t 
+is_members_nondec_01    |t 
+is_members_nondec_12    |t 
+(13 rows)
+
+step d_commit: COMMIT;
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 9f1e997d81b..4d94fc94e77 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -118,3 +118,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..9098b6f5c5d
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,127 @@
+# High-signal invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second locker arrived,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+# NOTE: Snapshots snap0 and subsequent checks are taken inside an open driver
+# transaction to narrow the window for unrelated truncation between snapshots.
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : “in-use” deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can’t advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two lockers on the SAME tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Driver session: keep a transaction open while we take snapshots and check.
+session "driver"
+step d_begin { BEGIN; SET client_min_messages = warning; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row (still in driver xact).
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members (still in driver xact).
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_init_oldest_off       : oldest_offset is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_oldest_off_nondec_01  : oldest_offset did not decrease (snap0→snap1)
+#   is_oldest_off_nondec_12  : oldest_offset did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+step d_commit { COMMIT; }
+
+permutation d_begin snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned d_commit s1_commit s2_commit
-- 
2.47.3

From 67dc7b387f950364234e95fe6f7099a85a445834 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Mon, 18 Aug 2025 20:51:25 +0000
Subject: [PATCH v6] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_bytes      : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed
- oldest_offset      : oldest member offset still in use

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Returns NULLs if MultiXact system not initialized
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  31 +++++
 doc/src/sgml/maintenance.sgml                 |  54 +++++++-
 src/backend/utils/adt/multixactfuncs.c        |  52 +++++++
 src/include/catalog/pg_proc.dat               |  15 +++
 .../isolation/expected/multixact_stats.out    |  94 +++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 127 ++++++++++++++++++
 7 files changed, 369 insertions(+), 5 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..9dedc3715d7 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,37 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_bytes</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type>,
+          <parameter>oldest_offset</parameter> <type>bigint</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the number of multixact IDs assigned,
+        <literal>num_members</literal> is the number of multixact member entries created,
+        <literal>members_bytes</literal> is the storage occupied by <literal>num_members</literal>
+        in <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use, and
+        <literal>oldest_offset</literal> is the oldest member offset still in use.
+        These values can be used to monitor multixact consumption and anticipate
+        autovacuum behavior. See <xref linkend="vacuum-for-multixact-wraparound"/>
+        for further details on multixact wraparound.
+       </para>
+       <para>
+        This is a live snapshot of shared counters; the numbers can change between calls,
+        even within the same transaction.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
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
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. The members can grow
+     up to approximately 2^32 entries before reaching wraparound.
+    </para>
+
+    <para>
+     The <function>pg_get_multixact_stats()</function> function, described in
+     <xref linkend="functions-pg-snapshot"/>, provides a way to monitor
+     multixact allocation and usage patterns in real time. For example:
+     <programlisting>
+postgres=# SELECT  num_mxids,num_members,oldest_multixact,oldest_offset FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | oldest_multixact | oldest_offset
+-----------+-------------+------------------+---------------
+  99883849 |   773468747 |         39974368 |     351952978
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~100 million
+     multixact IDs and ~773 million member entries have been created since the oldest
+     surviving multixact (ID 39974368). By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in num_mxids might indicate
+        multiple sessions running UPDATE statements with foreign key checks,
+        concurrent SELECT FOR SHARE operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while num_members grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
     </para>
 
     <para>
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index e74ea938348..ba9e8313ab4 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -85,3 +85,55 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current MultiXact usage:
+ *		- num_mxids: Number of MultiXact IDs in use
+ *		- num_members: Total number of member entries
+ *		- oldest_multixact: Oldest MultiXact ID still needed
+ *		- oldest_offset: Oldest offset still in use
+ *
+ * Returns a row of NULLs if the MultiXact system is not yet initialized.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[5];
+	bool		nulls[5] = {true, true, true, true, true};
+	MultiXactOffset members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset oldestOffset;
+	int64		membersBytes;
+	HeapTuple	tuple;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+	{
+		/*
+		* Calculate approximate storage space:
+		* - Members are stored in groups of 4
+		* - Each group takes 20 bytes (5 bytes per member)
+		* Note: This ignores small page overhead (12 bytes per 8KB)
+		*/
+		membersBytes = (int64) members * 5;
+
+		values[0] = Int32GetDatum(multixacts);
+		values[1] = Int64GetDatum(members);
+		values[2] = Int64GetDatum(membersBytes);
+		values[3] = UInt32GetDatum(oldestMultiXactId);
+		values[4] = Int64GetDatum(oldestOffset);
+		nulls[0] = nulls[1] = nulls[2] = nulls[3] = nulls[4] = false;
+	}
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..985364e0cd6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,19 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get MultiXact state
+{
+  oid => '9001',
+  descr => 'get current multixact member and multixact ID counts and oldest values',
+  proname => 'pg_get_multixact_stats',
+  prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int4,int8,int8,xid,int8}',
+  proargmodes => '{o,o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_bytes,oldest_multixact,oldest_offset}',
+  provolatile => 'v',
+  proparallel => 's',
+  prosrc => 'pg_get_multixact_stats'
+},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..2893c4d9f36
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,94 @@
+Parsed test spec with 3 sessions
+
+starting permutation: d_begin snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned d_commit s1_commit s2_commit
+step d_begin: BEGIN; SET client_min_messages = warning;
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |t 
+is_members_nondec_01    |t 
+is_members_nondec_12    |t 
+(13 rows)
+
+step d_commit: COMMIT;
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 9f1e997d81b..4d94fc94e77 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -118,3 +118,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..9098b6f5c5d
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,127 @@
+# High-signal invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second locker arrived,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+# NOTE: Snapshots snap0 and subsequent checks are taken inside an open driver
+# transaction to narrow the window for unrelated truncation between snapshots.
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : “in-use” deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can’t advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two lockers on the SAME tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Driver session: keep a transaction open while we take snapshots and check.
+session "driver"
+step d_begin { BEGIN; SET client_min_messages = warning; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row (still in driver xact).
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members (still in driver xact).
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_init_oldest_off       : oldest_offset is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_oldest_off_nondec_01  : oldest_offset did not decrease (snap0→snap1)
+#   is_oldest_off_nondec_12  : oldest_offset did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+step d_commit { COMMIT; }
+
+permutation d_begin snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned d_commit s1_commit s2_commit
-- 
2.47.3

From 79dc6ffd50ad0a2dc84eeb2da3353d823b0ced91 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Wed, 3 Sep 2025 04:09:56 +0000
Subject: [PATCH v7] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_size       : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed
- oldest_offset      : oldest member offset still in use

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Returns NULLs if MultiXact system not initialized
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  34 +++++
 doc/src/sgml/maintenance.sgml                 |  57 +++++++-
 src/backend/utils/adt/multixactfuncs.c        |  47 +++++++
 src/include/catalog/pg_proc.dat               |  15 +++
 .../isolation/expected/multixact_stats.out    |  94 +++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 127 ++++++++++++++++++
 7 files changed, 369 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..fcc5e4bde38 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,40 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_size</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type>,
+          <parameter>oldest_offset</parameter> <type>bigint</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the number of multixact IDs assigned,
+        <literal>num_members</literal> is the number of multixact member entries created,
+        <literal>members_size</literal> is the storage occupied by <literal>num_members</literal>
+        in <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use, and
+        <literal>oldest_offset</literal> is the oldest member offset still in use.
+        See <xref linkend="vacuum-for-multixact-wraparound"/> for further details on multixact wraparound.
+       </para>
+       <para>
+        The function reports statistics at the time it is invoked. Values may vary between calls,
+        even within a single transaction.
+       </para>
+       <para>
+        The function returns <literal>NULL</literal> when multixact statistics are unavailable.
+        For example, during startup before multixact initialization completes or when
+        the oldest member offset cannot be determined.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..8b265e2348f 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,14 +813,59 @@ 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
-     scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the number
+     of multixact member entries created exceeds approximately 2^31 entries
+     (occupying roughly 10GB in the <literal>pg_multixact/members</literal> directory),
+     aggressive vacuum scans will occur more often for all tables, starting with those that
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. The members can grow
+     up to approximately 2^32 entries before reaching wraparound.
     </para>
 
+    <para>
+     The <function>pg_get_multixact_stats()</function> function described in
+     <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+     multixact allocation and usage patterns in real time. For example:
+     <programlisting>
+postgres=# SELECT *,pg_size_pretty(members_size) members_size_pretty FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | oldest_offset | members_size_pretty
+-----------+-------------+--------------+------------------+---------------+---------------------
+ 311740299 |  2785241176 |  13926205880 |                2 |             3 | 13 GB
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.
+     By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in <literal>num_mxids</literal> might indicate
+        multiple sessions running <literal>UPDATE</literal> statements with foreign key checks,
+        concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while <literal>num_members</literal> grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
+     </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index e74ea938348..c393f11a38c 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -85,3 +85,50 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current MultiXact usage.
+ *
+ * Returns NULL if the oldest referenced offset is unknown, which happens during
+ * system startup or when no MultiXact references exist in any relation.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[5];
+	bool		nulls[5];
+	MultiXactOffset	members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset	oldestOffset;
+	int64		membersBytes;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+	{
+		/*
+		 * Calculate storage space for members. Members are stored in groups of 4,
+		 * with each group taking 20 bytes, resulting in 5 bytes per member.
+		 * Note: This ignores small page overhead (12 bytes per 8KB)
+		 */
+		membersBytes = (int64) members * 5;
+
+		values[0] = Int32GetDatum(multixacts);
+		values[1] = Int64GetDatum(members);
+		values[2] = Int64GetDatum(membersBytes);
+		values[3] = UInt32GetDatum(oldestMultiXactId);
+		values[4] = Int64GetDatum(oldestOffset);
+		memset(nulls, false, sizeof(nulls));
+
+		return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+	}
+
+	PG_RETURN_NULL();
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..f5f224b8a14 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12576,4 +12576,19 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get MultiXact state
+{
+  oid => '9001',
+  descr => 'get current multixact member and multixact ID counts and oldest values',
+  proname => 'pg_get_multixact_stats',
+  prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int4,int8,int8,xid,int8}',
+  proargmodes => '{o,o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_size,oldest_multixact,oldest_offset}',
+  provolatile => 'v',
+  proparallel => 's',
+  prosrc => 'pg_get_multixact_stats'
+},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..2893c4d9f36
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,94 @@
+Parsed test spec with 3 sessions
+
+starting permutation: d_begin snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned d_commit s1_commit s2_commit
+step d_begin: BEGIN; SET client_min_messages = warning;
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |t 
+is_members_nondec_01    |t 
+is_members_nondec_12    |t 
+(13 rows)
+
+step d_commit: COMMIT;
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 9f1e997d81b..4d94fc94e77 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -118,3 +118,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..9098b6f5c5d
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,127 @@
+# High-signal invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second locker arrived,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+# NOTE: Snapshots snap0 and subsequent checks are taken inside an open driver
+# transaction to narrow the window for unrelated truncation between snapshots.
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : “in-use” deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can’t advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two lockers on the SAME tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Driver session: keep a transaction open while we take snapshots and check.
+session "driver"
+step d_begin { BEGIN; SET client_min_messages = warning; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row (still in driver xact).
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members (still in driver xact).
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_init_oldest_off       : oldest_offset is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_oldest_off_nondec_01  : oldest_offset did not decrease (snap0→snap1)
+#   is_oldest_off_nondec_12  : oldest_offset did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+step d_commit { COMMIT; }
+
+permutation d_begin snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned d_commit s1_commit s2_commit
-- 
2.47.3

From 4752fdd586f78da4ca68879f5a8dafdbfbb36445 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Thu, 11 Sep 2025 22:18:51 +0000
Subject: [PATCH v8] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_size       : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed
- oldest_offset      : oldest member offset still in use

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Returns NULLs if MultiXact system not initialized
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  35 ++++++
 doc/src/sgml/maintenance.sgml                 |  58 ++++++++-
 src/backend/utils/adt/multixactfuncs.c        |  47 +++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 .../isolation/expected/multixact_stats.out    |  92 ++++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 119 ++++++++++++++++++
 7 files changed, 356 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..2d21d0d3af5 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,41 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_size</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type>,
+          <parameter>oldest_offset</parameter> <type>bigint</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the total number of multixact IDs assigned since startup,
+        <literal>num_members</literal> is the total number of multixact member entries created since startup,
+        <literal>members_size</literal> is the storage occupied by <literal>num_members</literal>
+        in the <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use, and
+        <literal>oldest_offset</literal> is the oldest member offset still in use.
+        See <xref linkend="vacuum-for-multixact-wraparound"/> for further details on multixact wraparound.
+       </para>
+       <para>
+        The function reports statistics at the time it is invoked. Values may vary between calls,
+        even within a single transaction.
+       </para>
+       <para>
+        Returns <literal>NULL</literal> when multixact statistics are unavailable,
+        such as during startup before multixact initialization completes.
+        Specifically, this occurs when the oldest multixact offset
+        corresponding to a multixact referenced by a relation is not known.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..58be621182b 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,14 +813,60 @@ 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
-     scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the number
+     of multixact member entries created exceeds approximately 2^31 entries
+     (occupying roughly 10GB in the <literal>pg_multixact/members</literal> directory),
+     aggressive vacuum scans will occur more often for all tables, starting with those that
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. The members can grow
+     up to approximately 2^32 entries(occupying roughly 20GB in the
+     <literal>pg_multixact/members</literal> directory) before reaching wraparound.
     </para>
 
+    <para>
+     The <function>pg_get_multixact_stats()</function> function described in
+     <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+     multixact allocation and usage patterns in real time, for example:
+     <programlisting>
+postgres=# SELECT *,pg_size_pretty(members_size) members_size_pretty FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | oldest_offset | members_size_pretty
+-----------+-------------+--------------+------------------+---------------+---------------------
+ 311740299 |  2785241176 |  13926205880 |                2 |             3 | 13 GB
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.
+     By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in <literal>num_mxids</literal> might indicate
+        multiple sessions running <literal>UPDATE</literal> statements with foreign key checks,
+        concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while <literal>num_members</literal> grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
+     </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index e74ea938348..3117acb19fa 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -85,3 +85,50 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current multixact usage.
+ *
+ * Returns NULL if the oldest referenced offset is unknown, which happens during
+ * system startup.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[5];
+	bool		nulls[5];
+	MultiXactOffset	members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset	oldestOffset;
+	int64		membersBytes;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+	{
+		/*
+		 * Calculate storage space for members. Members are stored in groups of 4,
+		 * with each group taking 20 bytes, resulting in 5 bytes per member.
+		 * Note: This ignores small page overhead (12 bytes per 8KB)
+		 */
+		membersBytes = (int64) members * 5;
+
+		values[0] = UInt32GetDatum(multixacts);
+		values[1] = UInt32GetDatum(members);
+		values[2] = Int64GetDatum(membersBytes);
+		values[3] = UInt32GetDatum(oldestMultiXactId);
+		values[4] = UInt32GetDatum(oldestOffset);
+		memset(nulls, false, sizeof(nulls));
+
+		return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+	}
+
+	PG_RETURN_NULL();
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 03e82d28c87..e3bdb187da0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12588,4 +12588,14 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get multixact usage
+{ oid => '9001', descr => 'get current multixact usage statistics',
+  proname => 'pg_get_multixact_stats',
+  provolatile => 'v', proparallel => 's', prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int4,int8,int8,xid,int8}',
+  proargmodes => '{o,o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_size,oldest_multixact,oldest_offset}',
+  prosrc => 'pg_get_multixact_stats'},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..69845f058e4
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,92 @@
+Parsed test spec with 2 sessions
+
+starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |t 
+is_members_nondec_01    |t 
+is_members_nondec_12    |t 
+(13 rows)
+
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 5afae33d370..bab8a8eaf31 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -120,3 +120,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..af6b091248a
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,119 @@
+# Test invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second locker arrived,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : "in-use" deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can't advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two lockers on the SAME tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row.
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members.
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_init_oldest_off       : oldest_offset is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_oldest_off_nondec_01  : oldest_offset did not decrease (snap0→snap1)
+#   is_oldest_off_nondec_12  : oldest_offset did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
-- 
2.47.3

From b6267ba1e64dadf79d2582ddc9ceca67c4c12857 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Mon, 15 Sep 2025 04:15:22 +0000
Subject: [PATCH v9] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_size       : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed
- oldest_offset      : oldest member offset still in use

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Returns NULLs if MultiXact system not initialized
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  35 ++++++
 doc/src/sgml/maintenance.sgml                 |  58 ++++++++-
 src/backend/utils/adt/multixactfuncs.c        |  46 +++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 .../isolation/expected/multixact_stats.out    |  92 ++++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 119 ++++++++++++++++++
 7 files changed, 355 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..623b590e338 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,41 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_size</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type>,
+          <parameter>oldest_offset</parameter> <type>bigint</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the total number of multixact IDs currently present in the system,
+        <literal>num_members</literal> is the total number of multixact member entries currently
+        present in the system,
+        <literal>members_size</literal> is the storage occupied by <literal>num_members</literal>
+        in the <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use, and
+        <literal>oldest_offset</literal> is the oldest member offset still in use.
+       </para>
+       <para>
+        The function reports statistics at the time it is invoked. Values may vary between calls,
+        even within a single transaction.
+       </para>
+       <para>
+        Returns <literal>NULL</literal> when multixact statistics are unavailable,
+        such as during startup before multixact initialization completes.
+        Specifically, this occurs when the oldest multixact offset
+        corresponding to a multixact referenced by a relation is not known.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..cf56307090d 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,14 +813,60 @@ 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
-     scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the number
+     of multixact member entries created exceeds approximately 2^31 entries
+     (occupying roughly 10GB in the <literal>pg_multixact/members</literal> directory),
+     aggressive vacuum scans will occur more often for all tables, starting with those that
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. The members can grow
+     up to approximately 2^32 entries (occupying roughly 20GB in the
+     <literal>pg_multixact/members</literal> directory) before reaching wraparound.
     </para>
 
+    <para>
+     The <function>pg_get_multixact_stats()</function> function described in
+     <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+     multixact allocation and usage patterns in real time, for example:
+     <programlisting>
+postgres=# SELECT *,pg_size_pretty(members_size) members_size_pretty FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | oldest_offset | members_size_pretty
+-----------+-------------+--------------+------------------+---------------+---------------------
+ 311740299 |  2785241176 |  13926205880 |                2 |             3 | 13 GB
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.
+     By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in <literal>num_mxids</literal> might indicate
+        multiple sessions running <literal>UPDATE</literal> statements with foreign key checks,
+        concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while <literal>num_members</literal> grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
+     </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index e74ea938348..9bf7a52a242 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -85,3 +85,49 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current multixact usage.
+ *
+ * Returns NULL if the oldest referenced offset is unknown.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[5];
+	bool		nulls[5];
+	MultiXactOffset	members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset	oldestOffset;
+	int64		membersBytes;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+	{
+		/*
+		 * Calculate storage space for members. Members are stored in groups of 4,
+		 * with each group taking 20 bytes, resulting in 5 bytes per member.
+		 * Note: This ignores small page overhead (12 bytes per 8KB)
+		 */
+		membersBytes = (int64) members * 5;
+
+		values[0] = UInt32GetDatum(multixacts);
+		values[1] = UInt32GetDatum(members);
+		values[2] = Int64GetDatum(membersBytes);
+		values[3] = UInt32GetDatum(oldestMultiXactId);
+		values[4] = UInt32GetDatum(oldestOffset);
+		memset(nulls, false, sizeof(nulls));
+
+		return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+	}
+
+	PG_RETURN_NULL();
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 03e82d28c87..3e9376c875b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12588,4 +12588,14 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get multixact usage
+{ oid => '9001', descr => 'get current multixact usage statistics',
+  proname => 'pg_get_multixact_stats',
+  provolatile => 'v', proparallel => 's', prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int8,int8,int8,xid,int8}',
+  proargmodes => '{o,o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_size,oldest_multixact,oldest_offset}',
+  prosrc => 'pg_get_multixact_stats'},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..69845f058e4
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,92 @@
+Parsed test spec with 2 sessions
+
+starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |t 
+is_members_nondec_01    |t 
+is_members_nondec_12    |t 
+(13 rows)
+
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 5afae33d370..bab8a8eaf31 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -120,3 +120,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..7ef05f1bff0
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,119 @@
+# Test invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second session locked the row,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : "in-use" deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can't advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two sessions that lock on the same tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row.
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members.
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_init_oldest_off       : oldest_offset is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_oldest_off_nondec_01  : oldest_offset did not decrease (snap0→snap1)
+#   is_oldest_off_nondec_12  : oldest_offset did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
-- 
2.47.3

From 208ffde2dfebfdeed59890bbb11782bbd7daf8c2 Mon Sep 17 00:00:00 2001
From: alterego655 <824662526@qq.com>
Date: Fri, 17 Oct 2025 10:02:00 +0800
Subject: [PATCH v10] Add pg_get_multixact_stats() function for monitoring 
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_size       : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed
- oldest_offset      : oldest member offset still in use

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Returns NULLs if MultiXact system not initialized
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  35 ++++++
 doc/src/sgml/maintenance.sgml                 |  58 ++++++++-
 src/backend/utils/adt/multixactfuncs.c        |  47 +++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 .../isolation/expected/multixact_stats.out    |  92 ++++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 119 ++++++++++++++++++
 7 files changed, 356 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..623b590e338 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,41 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_size</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type>,
+          <parameter>oldest_offset</parameter> <type>bigint</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the total number of multixact IDs currently present in the system,
+        <literal>num_members</literal> is the total number of multixact member entries currently
+        present in the system,
+        <literal>members_size</literal> is the storage occupied by <literal>num_members</literal>
+        in the <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use, and
+        <literal>oldest_offset</literal> is the oldest member offset still in use.
+       </para>
+       <para>
+        The function reports statistics at the time it is invoked. Values may vary between calls,
+        even within a single transaction.
+       </para>
+       <para>
+        Returns <literal>NULL</literal> when multixact statistics are unavailable,
+        such as during startup before multixact initialization completes.
+        Specifically, this occurs when the oldest multixact offset
+        corresponding to a multixact referenced by a relation is not known.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index e7a9f58c015..cf56307090d 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,14 +813,60 @@ 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
-     scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the number
+     of multixact member entries created exceeds approximately 2^31 entries
+     (occupying roughly 10GB in the <literal>pg_multixact/members</literal> directory),
+     aggressive vacuum scans will occur more often for all tables, starting with those that
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. The members can grow
+     up to approximately 2^32 entries (occupying roughly 20GB in the
+     <literal>pg_multixact/members</literal> directory) before reaching wraparound.
     </para>
 
+    <para>
+     The <function>pg_get_multixact_stats()</function> function described in
+     <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+     multixact allocation and usage patterns in real time, for example:
+     <programlisting>
+postgres=# SELECT *,pg_size_pretty(members_size) members_size_pretty FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | oldest_offset | members_size_pretty
+-----------+-------------+--------------+------------------+---------------+---------------------
+ 311740299 |  2785241176 |  13926205880 |                2 |             3 | 13 GB
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.
+     By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in <literal>num_mxids</literal> might indicate
+        multiple sessions running <literal>UPDATE</literal> statements with foreign key checks,
+        concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while <literal>num_members</literal> grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
+     </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index e74ea938348..782045aa2c8 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -14,6 +14,7 @@
 
 #include "postgres.h"
 
+#include "access/htup_details.h"
 #include "access/multixact.h"
 #include "funcapi.h"
 #include "utils/builtins.h"
@@ -85,3 +86,49 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current multixact usage.
+ *
+ * Returns NULL if the oldest referenced offset is unknown.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[5];
+	bool		nulls[5];
+	MultiXactOffset	members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset	oldestOffset;
+	int64		membersBytes;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+	{
+		/*
+		 * Calculate storage space for members. Members are stored in groups of 4,
+		 * with each group taking 20 bytes, resulting in 5 bytes per member.
+		 * Note: This ignores small page overhead (12 bytes per 8KB)
+		 */
+		membersBytes = (int64) members * 5;
+
+		values[0] = UInt32GetDatum(multixacts);
+		values[1] = UInt32GetDatum(members);
+		values[2] = Int64GetDatum(membersBytes);
+		values[3] = UInt32GetDatum(oldestMultiXactId);
+		values[4] = UInt32GetDatum(oldestOffset);
+		memset(nulls, false, sizeof(nulls));
+
+		return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+	}
+
+	PG_RETURN_NULL();
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b51d2b17379..fd569f9c760 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12601,4 +12601,14 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get multixact usage
+{ oid => '9001', descr => 'get current multixact usage statistics',
+  proname => 'pg_get_multixact_stats',
+  provolatile => 'v', proparallel => 's', prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int8,int8,int8,xid,int8}',
+  proargmodes => '{o,o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_size,oldest_multixact,oldest_offset}',
+  prosrc => 'pg_get_multixact_stats'},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..69845f058e4
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,92 @@
+Parsed test spec with 2 sessions
+
+starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |t 
+is_members_nondec_01    |t 
+is_members_nondec_12    |t 
+(13 rows)
+
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 5afae33d370..bab8a8eaf31 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -120,3 +120,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..7ef05f1bff0
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,119 @@
+# Test invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second session locked the row,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : "in-use" deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can't advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two sessions that lock on the same tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row.
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members.
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact, oldest_offset
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_init_oldest_off       : oldest_offset is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_oldest_off_nondec_01  : oldest_offset did not decrease (snap0→snap1)
+#   is_oldest_off_nondec_12  : oldest_offset did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+      (s2.oldest_offset    IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+      (s1.oldest_offset    >= COALESCE(s0.oldest_offset,    0)),
+      (s2.oldest_offset    >= COALESCE(s1.oldest_offset,    0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
-- 
2.51.0

From 0b0c60f7abc7bb2913fb7e8b4a6286723a0caf74 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Thu, 23 Oct 2025 22:11:03 +0000
Subject: [PATCH v11] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_size       : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Returns NULLs if MultiXact system not initialized
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  33 +++++
 doc/src/sgml/maintenance.sgml                 |  58 ++++++++-
 src/backend/utils/adt/multixactfuncs.c        |  46 +++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 .../isolation/expected/multixact_stats.out    |  89 ++++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 113 ++++++++++++++++++
 7 files changed, 344 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..0d756186197 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,39 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_size</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the total number of multixact IDs currently present in the system,
+        <literal>num_members</literal> is the total number of multixact member entries currently
+        present in the system,
+        <literal>members_size</literal> is the storage occupied by <literal>num_members</literal>
+        in the <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use.
+       </para>
+       <para>
+        The function reports statistics at the time it is invoked. Values may vary between calls,
+        even within a single transaction.
+       </para>
+       <para>
+        Returns <literal>NULL</literal> when multixact statistics are unavailable,
+        such as during startup before multixact initialization completes.
+        Specifically, this occurs when the oldest multixact offset
+        corresponding to a multixact referenced by a relation is not known.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index dc59c88319e..4d7e172450a 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,14 +813,60 @@ 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
-     scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the number
+     of multixact member entries created exceeds approximately 2^31 entries
+     (occupying roughly 10GB in the <literal>pg_multixact/members</literal> directory),
+     aggressive vacuum scans will occur more often for all tables, starting with those that
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. The members can grow
+     up to approximately 2^32 entries (occupying roughly 20GB in the
+     <literal>pg_multixact/members</literal> directory) before reaching wraparound.
     </para>
 
+    <para>
+     The <function>pg_get_multixact_stats()</function> function described in
+     <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+     multixact allocation and usage patterns in real time, for example:
+     <programlisting>
+postgres=# SELECT *,pg_size_pretty(members_size) members_size_pretty FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | members_size_pretty
+-----------+-------------+--------------+------------------+---------------------
+ 311740299 |  2785241176 |  13926205880 |                2 | 13 GB
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.
+     By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in <literal>num_mxids</literal> might indicate
+        multiple sessions running <literal>UPDATE</literal> statements with foreign key checks,
+        concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while <literal>num_members</literal> grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
+     </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index e74ea938348..286676d3829 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -15,6 +15,7 @@
 #include "postgres.h"
 
 #include "access/multixact.h"
+#include "access/htup_details.h"
 #include "funcapi.h"
 #include "utils/builtins.h"
 
@@ -85,3 +86,48 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current multixact usage.
+ *
+ * Returns NULL if the oldest referenced offset is unknown.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[4];
+	bool		nulls[4];
+	MultiXactOffset	members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset	oldestOffset;
+	int64		membersBytes;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	if (GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset))
+	{
+		/*
+		 * Calculate storage space for members. Members are stored in groups of 4,
+		 * with each group taking 20 bytes, resulting in 5 bytes per member.
+		 * Note: This ignores small page overhead (12 bytes per 8KB)
+		 */
+		membersBytes = (int64) members * 5;
+
+		values[0] = UInt32GetDatum(multixacts);
+		values[1] = UInt32GetDatum(members);
+		values[2] = Int64GetDatum(membersBytes);
+		values[3] = UInt32GetDatum(oldestMultiXactId);
+		memset(nulls, false, sizeof(nulls));
+
+		return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+	}
+
+	PG_RETURN_NULL();
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f76..928aa3cff72 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12604,4 +12604,14 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get multixact usage
+{ oid => '9001', descr => 'get current multixact usage statistics',
+  proname => 'pg_get_multixact_stats',
+  provolatile => 'v', proparallel => 's', prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int8,int8,int8,xid}',
+  proargmodes => '{o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_size,oldest_multixact}',
+  prosrc => 'pg_get_multixact_stats'},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..27a6510c4ad
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,89 @@
+Parsed test spec with 2 sessions
+
+starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |  
+is_members_nondec_01    |  
+is_members_nondec_12    |  
+(13 rows)
+
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 5afae33d370..bab8a8eaf31 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -120,3 +120,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..7b034654504
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,113 @@
+# Test invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second session locked the row,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : "in-use" deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can't advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two sessions that lock on the same tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row.
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members.
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
-- 
2.47.3

From 54b1dabf3c8da43d700a1087307177a2f17e62ca Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Fri, 12 Dec 2025 22:44:46 +0000
Subject: [PATCH v12] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_size       : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Returns NULLs if MultiXact system not initialized
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  27 +++++
 doc/src/sgml/maintenance.sgml                 |  58 ++++++++-
 src/backend/utils/adt/multixactfuncs.c        |  45 +++++++
 src/include/catalog/pg_proc.dat               |  10 ++
 .../isolation/expected/multixact_stats.out    |  89 ++++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 113 ++++++++++++++++++
 7 files changed, 337 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..051c3b28985 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,33 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_size</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the total number of multixact IDs currently present in the system,
+        <literal>num_members</literal> is the total number of multixact member entries currently
+        present in the system,
+        <literal>members_size</literal> is the storage occupied by <literal>num_members</literal>
+        in the <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use.
+       </para>
+       <para>
+        The function reports statistics at the time it is invoked. Values may vary between calls,
+        even within a single transaction.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 08e6489afb8..8695b92e93e 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,14 +813,60 @@ 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
-     scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the number
+     of multixact member entries created exceeds approximately 2^31 entries
+     (occupying roughly 10GB in the <literal>pg_multixact/members</literal> directory),
+     aggressive vacuum scans will occur more often for all tables, starting with those that
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. At approximately 2^32 entries
+     (occupying roughly 20GB in the <literal>pg_multixact/members</literal> directory), even
+     more aggressive vacuum scans are triggered to reclaim member storage space.
     </para>
 
+    <para>
+     The <function>pg_get_multixact_stats()</function> function described in
+     <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+     multixact allocation and usage patterns in real time, for example:
+     <programlisting>
+postgres=# SELECT *,pg_size_pretty(members_size) members_size_pretty FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | members_size_pretty
+-----------+-------------+--------------+------------------+---------------------
+ 311740299 |  2785241176 |  13926205880 |                2 | 13 GB
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.
+     By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in <literal>num_mxids</literal> might indicate
+        multiple sessions running <literal>UPDATE</literal> statements with foreign key checks,
+        concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while <literal>num_members</literal> grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
+     </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index a428e140bc4..c0597cf5425 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -15,6 +15,8 @@
 #include "postgres.h"
 
 #include "access/multixact.h"
+#include "access/multixact_internal.h"
+#include "access/htup_details.h"
 #include "funcapi.h"
 #include "utils/builtins.h"
 
@@ -85,3 +87,46 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current multixact usage.
+ *
+ * Returns NULL if the oldest referenced offset is unknown.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[4];
+	bool		nulls[4];
+	MultiXactOffset	members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset	oldestOffset;
+	int64		membersBytes;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset);
+
+	/*
+	 * Calculate storage space for members. Members are stored in groups,
+	 * with each group containing MULTIXACT_MEMBERS_PER_MEMBERGROUP members
+	 * and taking MULTIXACT_MEMBERGROUP_SIZE bytes.
+	 */
+	membersBytes = (int64) (members / MULTIXACT_MEMBERS_PER_MEMBERGROUP) *
+				   MULTIXACT_MEMBERGROUP_SIZE;
+
+	values[0] = UInt32GetDatum(multixacts);
+	values[1] = Int64GetDatum(members);
+	values[2] = Int64GetDatum(membersBytes);
+	values[3] = UInt32GetDatum(oldestMultiXactId);
+	memset(nulls, false, sizeof(nulls));
+
+	return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fd9448ec7b9..6caea6c8281 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12612,4 +12612,14 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get multixact usage
+{ oid => '9001', descr => 'get current multixact usage statistics',
+  proname => 'pg_get_multixact_stats',
+  provolatile => 'v', proparallel => 's', prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int8,int8,int8,xid}',
+  proargmodes => '{o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_size,oldest_multixact}',
+  prosrc => 'pg_get_multixact_stats'},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..27a6510c4ad
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,89 @@
+Parsed test spec with 2 sessions
+
+starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |  
+is_members_nondec_01    |  
+is_members_nondec_12    |  
+(13 rows)
+
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 112f05a3677..67f0078d8ba 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -119,3 +119,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..7b034654504
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,113 @@
+# Test invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second session locked the row,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : "in-use" deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can't advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two sessions that lock on the same tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row.
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members.
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
-- 
2.47.3

From 5e198efdb916548c0852d429e62b89ef918d7869 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Wed, 24 Dec 2025 21:06:16 +0000
Subject: [PATCH v13] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_size       : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml              |  27 +++++
 doc/src/sgml/maintenance.sgml                 |  58 ++++++++-
 src/backend/utils/adt/multixactfuncs.c        |  39 ++++++
 src/include/access/multixact_internal.h       |   8 ++
 src/include/catalog/pg_proc.dat               |  10 ++
 .../isolation/expected/multixact_stats.out    |  89 ++++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact_stats.spec | 113 ++++++++++++++++++
 8 files changed, 339 insertions(+), 6 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact_stats.out
 create mode 100644 src/test/isolation/specs/multixact_stats.spec

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..051c3b28985 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,33 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_size</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the total number of multixact IDs currently present in the system,
+        <literal>num_members</literal> is the total number of multixact member entries currently
+        present in the system,
+        <literal>members_size</literal> is the storage occupied by <literal>num_members</literal>
+        in the <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still in use.
+       </para>
+       <para>
+        The function reports statistics at the time it is invoked. Values may vary between calls,
+        even within a single transaction.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 08e6489afb8..8695b92e93e 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,14 +813,60 @@ 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
-     scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the number
+     of multixact member entries created exceeds approximately 2^31 entries
+     (occupying roughly 10GB in the <literal>pg_multixact/members</literal> directory),
+     aggressive vacuum scans will occur more often for all tables, starting with those that
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. At approximately 2^32 entries
+     (occupying roughly 20GB in the <literal>pg_multixact/members</literal> directory), even
+     more aggressive vacuum scans are triggered to reclaim member storage space.
     </para>
 
+    <para>
+     The <function>pg_get_multixact_stats()</function> function described in
+     <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+     multixact allocation and usage patterns in real time, for example:
+     <programlisting>
+postgres=# SELECT *,pg_size_pretty(members_size) members_size_pretty FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | members_size_pretty
+-----------+-------------+--------------+------------------+---------------------
+ 311740299 |  2785241176 |  13926205880 |                2 | 13 GB
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about ~312 million
+     multixact IDs and ~2.8 billion member entries consuming 13 GB of storage space.
+     By leveraging this information, the function helps:
+     <orderedlist>
+      <listitem>
+       <simpara>
+        Identify unusual multixact activity from concurrent row-level locks
+        or foreign key operations. For example, a spike in <literal>num_mxids</literal> might indicate
+        multiple sessions running <literal>UPDATE</literal> statements with foreign key checks,
+        concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent use of savepoints
+        causing lock contention.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Track multixact cleanup efficiency by monitoring oldest_multixact.
+        If this value remains unchanged while <literal>num_members</literal> grows, it could indicate
+        that long-running transactions are preventing cleanup, or autovacuum is
+        not keeping up with the workload.
+       </simpara>
+      </listitem>
+      <listitem>
+       <simpara>
+        Detect potential performance impacts before they become critical.
+        For instance, high multixact usage from frequent row-level locking or
+        foreign key operations can lead to increased I/O and CPU overhead during
+        vacuum operations. Monitoring these stats helps tune autovacuum frequency
+        and transaction patterns.
+       </simpara>
+      </listitem>
+     </orderedlist>
+     </para>
+
     <para>
      Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
      system will begin to emit warning messages when the database's oldest MXIDs reach forty
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index a428e140bc4..e8929f4d83f 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -15,6 +15,8 @@
 #include "postgres.h"
 
 #include "access/multixact.h"
+#include "access/multixact_internal.h"
+#include "access/htup_details.h"
 #include "funcapi.h"
 #include "utils/builtins.h"
 
@@ -85,3 +87,40 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current multixact usage.
+ *
+ * Returns NULL if the oldest referenced offset is unknown.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[4];
+	bool		nulls[4];
+	MultiXactOffset	members;
+	MultiXactId	oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset	oldestOffset;
+	int64		membersBytes;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset);
+
+	membersBytes = MultiXactMemberStorageSize(members);
+
+	values[0] = UInt32GetDatum(multixacts);
+	values[1] = Int64GetDatum(members);
+	values[2] = Int64GetDatum(membersBytes);
+	values[3] = UInt32GetDatum(oldestMultiXactId);
+	memset(nulls, false, sizeof(nulls));
+
+	return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+}
diff --git a/src/include/access/multixact_internal.h b/src/include/access/multixact_internal.h
index f2d6539e8a6..03bcf35ef79 100644
--- a/src/include/access/multixact_internal.h
+++ b/src/include/access/multixact_internal.h
@@ -121,4 +121,12 @@ MXOffsetToMemberOffset(MultiXactOffset offset)
 		member_in_group * sizeof(TransactionId);
 }
 
+/* Calculate storage space in bytes for a given number of members */
+static inline int64
+MultiXactMemberStorageSize(MultiXactOffset members)
+{
+	return (int64) (members / MULTIXACT_MEMBERS_PER_MEMBERGROUP) *
+		   MULTIXACT_MEMBERGROUP_SIZE;
+}
+
 #endif							/* MULTIXACT_INTERNAL_H */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fd9448ec7b9..6caea6c8281 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12612,4 +12612,14 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get multixact usage
+{ oid => '9001', descr => 'get current multixact usage statistics',
+  proname => 'pg_get_multixact_stats',
+  provolatile => 'v', proparallel => 's', prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int8,int8,int8,xid}',
+  proargmodes => '{o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_size,oldest_multixact}',
+  prosrc => 'pg_get_multixact_stats'},
+
 ]
diff --git a/src/test/isolation/expected/multixact_stats.out b/src/test/isolation/expected/multixact_stats.out
new file mode 100644
index 00000000000..27a6510c4ad
--- /dev/null
+++ b/src/test/isolation/expected/multixact_stats.out
@@ -0,0 +1,89 @@
+Parsed test spec with 2 sessions
+
+starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |  
+is_members_nondec_01    |  
+is_members_nondec_12    |  
+(13 rows)
+
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index f2e067b1fbc..5ba44d67a10 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -121,3 +121,4 @@ test: serializable-parallel-2
 test: serializable-parallel-3
 test: matview-write-skew
 test: lock-nowait
+test: multixact_stats
diff --git a/src/test/isolation/specs/multixact_stats.spec b/src/test/isolation/specs/multixact_stats.spec
new file mode 100644
index 00000000000..7b034654504
--- /dev/null
+++ b/src/test/isolation/specs/multixact_stats.spec
@@ -0,0 +1,113 @@
+# Test invariants for pg_get_multixact_stats()
+# We create exactly one fresh MultiXact on a brand-new table. While it is pinned
+# by two open transactions, we assert only invariants that background VACUUM/FREEZE
+# cannot violate:
+#   • members increased by ≥ 1 when the second session locked the row,
+#   • num_mxids / num_members did not decrease vs earlier snapshots,
+#   • oldest_* never decreases.
+# We make NO assertions after releasing locks (freezing/truncation may shrink deltas).
+#
+# Terminology (global counters):
+#   num_mxids, num_members    : "in-use" deltas derived from global horizons
+#   oldest_multixact, offset  : oldest horizons; they move forward, never backward
+#
+# All assertions execute while our multixact is pinned by open txns, which protects
+# the truncation horizon (VACUUM can't advance past our pinned multi).
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two sessions that lock on the same tuple -> one MultiXact with >= 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Baseline BEFORE any locking; may be NULLs if multixact isn't initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# After s1 has locked the row.
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# After s2 joins on the SAME tuple -> multixact with >= 2 members.
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value output of boolean checks.
+# Keys:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0→snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1→snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0→snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1→snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0→snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1→snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
-- 
2.47.3

From dbe13b3d61c03f5d8d6773021e87c626ab04b3b0 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Mon, 29 Dec 2025 11:39:56 +0900
Subject: [PATCH v14 1/3] Rework GetMultiXactInfo()

This routine returned a number of offsets as a MultiXactOffset, but it
is not actually an offset, just a number to define their range.  This
was confusing.

This type confusion comes from the original implementation of
MultiXactMemberFreezeThreshold().
---
 src/include/access/multixact.h         |  2 +-
 src/backend/access/transam/multixact.c | 16 ++++++++--------
 2 files changed, 9 insertions(+), 9 deletions(-)

diff --git a/src/include/access/multixact.h b/src/include/access/multixact.h
index 6433fe163641..d22abbb72512 100644
--- a/src/include/access/multixact.h
+++ b/src/include/access/multixact.h
@@ -109,7 +109,7 @@ extern bool MultiXactIdIsRunning(MultiXactId multi, bool isLockOnly);
 extern void MultiXactIdSetOldestMember(void);
 extern int	GetMultiXactIdMembers(MultiXactId multi, MultiXactMember **members,
 								  bool from_pgupgrade, bool isLockOnly);
-extern void GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members,
+extern void GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *nextOffset,
 							 MultiXactId *oldestMultiXactId,
 							 MultiXactOffset *oldestOffset);
 extern bool MultiXactIdPrecedes(MultiXactId multi1, MultiXactId multi2);
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 34956a5a6634..0d6f594e2a06 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2461,25 +2461,23 @@ find_multixact_start(MultiXactId multi, MultiXactOffset *result)
  *
  * Returns information about the current MultiXact state, as of:
  * multixacts: Number of MultiXacts (nextMultiXactId - oldestMultiXactId)
- * members: Number of member entries (nextOffset - oldestOffset)
+ * nextOffset: Next-to-be-assigned offset
  * oldestMultiXactId: Oldest MultiXact ID still in use
  * oldestOffset: Oldest offset still in use
  */
 void
-GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members,
+GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *nextOffset,
 				 MultiXactId *oldestMultiXactId, MultiXactOffset *oldestOffset)
 {
-	MultiXactOffset nextOffset;
 	MultiXactId nextMultiXactId;
 
 	LWLockAcquire(MultiXactGenLock, LW_SHARED);
-	nextOffset = MultiXactState->nextOffset;
+	*nextOffset = MultiXactState->nextOffset;
 	*oldestMultiXactId = MultiXactState->oldestMultiXactId;
 	nextMultiXactId = MultiXactState->nextMXact;
 	*oldestOffset = MultiXactState->oldestOffset;
 	LWLockRelease(MultiXactGenLock);
 
-	*members = nextOffset - *oldestOffset;
 	*multixacts = nextMultiXactId - *oldestMultiXactId;
 }
 
@@ -2514,16 +2512,18 @@ GetMultiXactInfo(uint32 *multixacts, MultiXactOffset *members,
 int
 MultiXactMemberFreezeThreshold(void)
 {
-	MultiXactOffset members;
 	uint32		multixacts;
 	uint32		victim_multixacts;
 	double		fraction;
 	int			result;
 	MultiXactId oldestMultiXactId;
 	MultiXactOffset oldestOffset;
+	MultiXactOffset nextOffset;
+	uint64		members;
 
-	/* Read the current offsets and members usage. */
-	GetMultiXactInfo(&multixacts, &members, &oldestMultiXactId, &oldestOffset);
+	/* Read the current offsets and multixact usage. */
+	GetMultiXactInfo(&multixacts, &nextOffset, &oldestMultiXactId, &oldestOffset);
+	members = nextOffset - oldestOffset;
 
 	/* If member space utilization is low, no special action is required. */
 	if (members <= MULTIXACT_MEMBER_LOW_THRESHOLD)
-- 
2.51.0

From 4ef3b82b91eaee132cfed886f6a1a1455bb084a3 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Mon, 29 Dec 2025 11:41:35 +0900
Subject: [PATCH v14 2/3] Add MultiXactOffsetStorageSize()

This calculates the amount of space taken by two multixact offsets,
useful on its own to know the amount of space multixacts may use.  This
will be used by an upcoming patch.
---
 src/include/access/multixact_internal.h | 10 ++++++++++
 1 file changed, 10 insertions(+)

diff --git a/src/include/access/multixact_internal.h b/src/include/access/multixact_internal.h
index f2d6539e8a67..65dc2be148db 100644
--- a/src/include/access/multixact_internal.h
+++ b/src/include/access/multixact_internal.h
@@ -121,4 +121,14 @@ MXOffsetToMemberOffset(MultiXactOffset offset)
 		member_in_group * sizeof(TransactionId);
 }
 
+/* Storage space consumed by a range of offsets, in bytes */
+static inline int64
+MultiXactOffsetStorageSize(MultiXactOffset new_offset,
+						   MultiXactOffset old_offset)
+{
+	Assert(new_offset >= old_offset);
+	return (int64) ((new_offset - old_offset) / MULTIXACT_MEMBERS_PER_MEMBERGROUP) *
+		   MULTIXACT_MEMBERGROUP_SIZE;
+}
+
 #endif							/* MULTIXACT_INTERNAL_H */
-- 
2.51.0

From a34c851f3421d5023f896f28549e77fd6d760100 Mon Sep 17 00:00:00 2001
From: Naga Appani <nagnrik@gmail.com>
Date: Wed, 24 Dec 2025 21:06:16 +0000
Subject: [PATCH v14 3/3] Add pg_get_multixact_stats() function for monitoring
 MultiXact usage

Expose multixact state via a new SQL-callable function pg_get_multixact_stats(),
returning:
- num_mxids          : number of MultiXact IDs in use
- num_members        : number of member entries in use
- members_size       : bytes used by num_members in pg_multixact/members directory
- oldest_multixact   : oldest MultiXact ID still needed

This patch adds pg_get_multixact_stats() function
   - SQL-callable interface to GetMultiXactInfo()
   - Includes isolation tests for monitoring invariants

Documentation updates:
- func-info.sgml: add function entry
- maintenance.sgml: mention monitoring multixact usage

Build and catalog:
- Add function to existing multixactfuncs.c
- pg_proc.dat entry

Author: Naga Appani <nagnrik@gmail.com>
Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/CA%2BQeY%2BAAsYK6WvBW4qYzHz4bahHycDAY_q5ECmHkEV_eB9ckzg%40mail.gmail.com
---
 src/include/catalog/pg_proc.dat               |  10 ++
 src/backend/utils/adt/multixactfuncs.c        |  53 +++++++++
 .../isolation/expected/multixact-stats.out    |  89 ++++++++++++++
 src/test/isolation/isolation_schedule         |   1 +
 src/test/isolation/specs/multixact-stats.spec | 111 ++++++++++++++++++
 src/test/regress/expected/misc_functions.out  |  29 +++++
 src/test/regress/sql/misc_functions.sql       |  15 +++
 doc/src/sgml/func/func-info.sgml              |  33 ++++++
 doc/src/sgml/maintenance.sgml                 |  39 +++++-
 9 files changed, 375 insertions(+), 5 deletions(-)
 create mode 100644 src/test/isolation/expected/multixact-stats.out
 create mode 100644 src/test/isolation/specs/multixact-stats.spec

diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fd9448ec7b98..6caea6c8281e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12612,4 +12612,14 @@
   proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
   prosrc => 'pg_get_aios' },
 
+# Get multixact usage
+{ oid => '9001', descr => 'get current multixact usage statistics',
+  proname => 'pg_get_multixact_stats',
+  provolatile => 'v', proparallel => 's', prorettype => 'record',
+  proargtypes => '',
+  proallargtypes => '{int8,int8,int8,xid}',
+  proargmodes => '{o,o,o,o}',
+  proargnames => '{num_mxids,num_members,members_size,oldest_multixact}',
+  prosrc => 'pg_get_multixact_stats'},
+
 ]
diff --git a/src/backend/utils/adt/multixactfuncs.c b/src/backend/utils/adt/multixactfuncs.c
index a428e140bc4b..b39db200a391 100644
--- a/src/backend/utils/adt/multixactfuncs.c
+++ b/src/backend/utils/adt/multixactfuncs.c
@@ -15,7 +15,12 @@
 #include "postgres.h"
 
 #include "access/multixact.h"
+#include "access/multixact_internal.h"
+#include "access/htup_details.h"
+#include "catalog/pg_authid_d.h"
 #include "funcapi.h"
+#include "miscadmin.h"
+#include "utils/acl.h"
 #include "utils/builtins.h"
 
 /*
@@ -85,3 +90,51 @@ pg_get_multixact_members(PG_FUNCTION_ARGS)
 
 	SRF_RETURN_DONE(funccxt);
 }
+
+/*
+ * pg_get_multixact_stats
+ *
+ * Returns statistics about current multixact usage.
+ */
+Datum
+pg_get_multixact_stats(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum		values[4];
+	bool		nulls[4];
+	uint64		members;
+	MultiXactId oldestMultiXactId;
+	uint32		multixacts;
+	MultiXactOffset oldestOffset;
+	MultiXactOffset nextOffset;
+	int64		membersBytes;
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("return type must be a row type")));
+
+	GetMultiXactInfo(&multixacts, &nextOffset, &oldestMultiXactId, &oldestOffset);
+	members = nextOffset - oldestOffset;
+
+	membersBytes = MultiXactOffsetStorageSize(nextOffset, oldestOffset);
+
+	if (!has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS))
+	{
+		/*
+		 * Only superusers and roles with privileges of pg_read_all_stats can
+		 * see details.
+		 */
+		memset(nulls, true, sizeof(bool) * tupdesc->natts);
+	}
+	else
+	{
+		values[0] = UInt32GetDatum(multixacts);
+		values[1] = Int64GetDatum(members);
+		values[2] = Int64GetDatum(membersBytes);
+		values[3] = UInt32GetDatum(oldestMultiXactId);
+		memset(nulls, false, sizeof(nulls));
+	}
+
+	return HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls));
+}
diff --git a/src/test/isolation/expected/multixact-stats.out b/src/test/isolation/expected/multixact-stats.out
new file mode 100644
index 000000000000..27a6510c4ad5
--- /dev/null
+++ b/src/test/isolation/expected/multixact-stats.out
@@ -0,0 +1,89 @@
+Parsed test spec with 2 sessions
+
+starting permutation: snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
+step snap0: 
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step s1_begin: BEGIN;
+step s1_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap1: 
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step s2_begin: BEGIN;
+step s2_lock: SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE;
+?column?
+--------
+       1
+(1 row)
+
+step snap2: 
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+
+step check_while_pinned: 
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+
+assertion               |ok
+------------------------+--
+is_init_mxids           |t 
+is_init_members         |t 
+is_init_oldest_mxid     |t 
+is_init_oldest_off      |t 
+is_oldest_mxid_nondec_01|t 
+is_oldest_mxid_nondec_12|t 
+is_oldest_off_nondec_01 |t 
+is_oldest_off_nondec_12 |t 
+is_members_increased_ge1|t 
+is_mxids_nondec_01      |t 
+is_mxids_nondec_12      |  
+is_members_nondec_01    |  
+is_members_nondec_12    |  
+(13 rows)
+
+step s1_commit: COMMIT;
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index f2e067b1fbc5..01ff1c6586fe 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -63,6 +63,7 @@ test: delete-abort-savept-2
 test: aborted-keyrevoke
 test: multixact-no-deadlock
 test: multixact-no-forget
+test: multixact-stats
 test: lock-committed-update
 test: lock-committed-keyupdate
 test: update-locked-tuple
diff --git a/src/test/isolation/specs/multixact-stats.spec b/src/test/isolation/specs/multixact-stats.spec
new file mode 100644
index 000000000000..6c1dd94958d1
--- /dev/null
+++ b/src/test/isolation/specs/multixact-stats.spec
@@ -0,0 +1,111 @@
+# Test for pg_get_multixact_stats()
+#
+# We create exactly one fresh MultiXact on a brand-new table.  While it is
+# pinned by two open transactions, we check patterns of this function that
+# VACUUM/FREEZE cannot violate:
+# 1) "members" increased by ≥ 1 when the second session locked the row,
+# 2) (num_mxids / num_members) did not decrease compared to earlier snapshots
+# 3) "oldest_*" fields never decreases.
+#
+# This test does not do checks patterns after releasing locks, as freezing
+# and/or truncation may shrink the multixact ranges calculated.
+
+setup
+{
+    CREATE TABLE mxq(id int PRIMARY KEY, v int);
+    INSERT INTO mxq VALUES (1, 42);
+}
+
+teardown
+{
+    DROP TABLE mxq;
+}
+
+# Two sessions that lock the same tuple, leading to one MultiXact with
+# at least 2 members.
+session "s1"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s1_begin  { BEGIN; }
+step s1_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s1_commit { COMMIT; }
+
+session "s2"
+setup { SET client_min_messages = warning; SET lock_timeout = '5s'; }
+step s2_begin  { BEGIN; }
+step s2_lock   { SELECT 1 FROM mxq WHERE id=1 FOR KEY SHARE; }
+step s2_commit { COMMIT; }
+
+# Save multixact state *BEFORE* any locking; some of these may be NULLs if
+# multixacts have not initialized yet.
+step snap0 {
+  CREATE TEMP TABLE snap0 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# Save multixact state after s1 has locked the row.
+step snap1 {
+  CREATE TEMP TABLE snap1 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# Save multixact state after s2 joins to lock the same row, leading to
+# a multixact with at least 2 members.
+step snap2 {
+  CREATE TEMP TABLE snap2 AS
+  SELECT num_mxids, num_members, oldest_multixact
+  FROM pg_get_multixact_stats();
+}
+
+# Pretty, deterministic key/value outputs based of boolean checks:
+#   is_init_mxids            : num_mxids is non-NULL
+#   is_init_members          : num_members is non-NULL
+#   is_init_oldest_mxid      : oldest_multixact is non-NULL
+#   is_oldest_mxid_nondec_01 : oldest_multixact did not decrease (snap0->snap1)
+#   is_oldest_mxid_nondec_12 : oldest_multixact did not decrease (snap1->snap2)
+#   is_members_increased_ge1 : members increased by at least 1 when s2 joined
+#   is_mxids_nondec_01       : num_mxids did not decrease (snap0->snap1)
+#   is_mxids_nondec_12       : num_mxids did not decrease (snap1->snap2)
+#   is_members_nondec_01     : num_members did not decrease (snap0->snap1)
+#   is_members_nondec_12     : num_members did not decrease (snap1->snap2)
+step check_while_pinned {
+  SELECT r.assertion, r.ok
+  FROM snap0 s0
+  JOIN snap1 s1 ON TRUE
+  JOIN snap2 s2 ON TRUE,
+  LATERAL unnest(
+    ARRAY[
+      'is_init_mxids',
+      'is_init_members',
+      'is_init_oldest_mxid',
+      'is_init_oldest_off',
+      'is_oldest_mxid_nondec_01',
+      'is_oldest_mxid_nondec_12',
+      'is_oldest_off_nondec_01',
+      'is_oldest_off_nondec_12',
+      'is_members_increased_ge1',
+      'is_mxids_nondec_01',
+      'is_mxids_nondec_12',
+      'is_members_nondec_01',
+      'is_members_nondec_12'
+    ],
+    ARRAY[
+      (s2.num_mxids        IS NOT NULL),
+      (s2.num_members      IS NOT NULL),
+      (s2.oldest_multixact IS NOT NULL),
+
+      (s1.oldest_multixact::text::bigint >= COALESCE(s0.oldest_multixact::text::bigint, 0)),
+      (s2.oldest_multixact::text::bigint >= COALESCE(s1.oldest_multixact::text::bigint, 0)),
+
+      (s2.num_members >= COALESCE(s1.num_members, 0) + 1),
+
+      (s1.num_mxids   >= COALESCE(s0.num_mxids,   0)),
+      (s2.num_mxids   >= COALESCE(s1.num_mxids,   0)),
+      (s1.num_members >= COALESCE(s0.num_members, 0)),
+      (s2.num_members >= COALESCE(s1.num_members, 0))
+    ]
+  ) AS r(assertion, ok);
+}
+
+permutation snap0 s1_begin s1_lock snap1 s2_begin s2_lock snap2 check_while_pinned s1_commit s2_commit
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index d7d965d884a1..6c03b1a79d75 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -999,3 +999,32 @@ SELECT test_relpath();
 SELECT pg_replication_origin_create('regress_' || repeat('a', 505));
 ERROR:  replication origin name is too long
 DETAIL:  Replication origin names must be no longer than 512 bytes.
+-- pg_get_multixact_stats tests
+CREATE ROLE regress_multixact_funcs;
+-- Access granted for superusers.
+SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats();
+ null_result 
+-------------
+ f
+(1 row)
+
+-- Access revoked.
+SET ROLE regress_multixact_funcs;
+SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats();
+ null_result 
+-------------
+ t
+(1 row)
+
+RESET ROLE;
+-- Access granted for users with pg_monitor rights.
+GRANT pg_monitor TO regress_multixact_funcs;
+SET ROLE regress_multixact_funcs;
+SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats();
+ null_result 
+-------------
+ f
+(1 row)
+
+RESET ROLE;
+DROP ROLE regress_multixact_funcs;
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 0fc20fbb6b40..35b7983996c4 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -459,3 +459,18 @@ SELECT test_relpath();
 
 -- pg_replication_origin.roname limit
 SELECT pg_replication_origin_create('regress_' || repeat('a', 505));
+
+-- pg_get_multixact_stats tests
+CREATE ROLE regress_multixact_funcs;
+-- Access granted for superusers.
+SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats();
+-- Access revoked.
+SET ROLE regress_multixact_funcs;
+SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats();
+RESET ROLE;
+-- Access granted for users with pg_monitor rights.
+GRANT pg_monitor TO regress_multixact_funcs;
+SET ROLE regress_multixact_funcs;
+SELECT oldest_multixact IS NULL AS null_result FROM pg_get_multixact_stats();
+RESET ROLE;
+DROP ROLE regress_multixact_funcs;
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48e..175f18315cd4 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2975,6 +2975,39 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
         modify key columns.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_multixact_stats</primary>
+        </indexterm>
+        <function>pg_get_multixact_stats</function> ()
+        <returnvalue>record</returnvalue>
+        ( <parameter>num_mxids</parameter> <type>integer</type>,
+          <parameter>num_members</parameter> <type>bigint</type>,
+          <parameter>members_size</parameter> <type>bigint</type>,
+          <parameter>oldest_multixact</parameter> <type>xid</type> )
+       </para>
+       <para>
+        Returns statistics about current multixact usage:
+        <literal>num_mxids</literal> is the total number of multixact IDs
+        currently present in the system, <literal>num_members</literal> is
+        the total number of multixact member entries currently present in
+        the system, <literal>members_size</literal> is the storage occupied
+        by <literal>num_members</literal> in the
+        <literal>pg_multixact/members</literal> directory,
+        <literal>oldest_multixact</literal> is the oldest multixact ID still
+        in use.
+       </para>
+       <para>
+        The function reports statistics at the time it is invoked. Values may
+        vary between calls, even within a single transaction.
+       </para>
+       <para>
+        To use this function, you must have privileges of the
+        <literal>pg_read_all_stats</literal> role.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 08e6489afb8e..7c958b062731 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -813,12 +813,41 @@ 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 multixact member entries created exceeds approximately 2 billion
+     entries (occupying roughly 10GB in the
+     <literal>pg_multixact/members</literal> directory), aggressive vacuum
      scans will occur more often for all tables, starting with those that
-     have the oldest multixact-age.  Both of these kinds of aggressive
-     scans will occur even if autovacuum is nominally disabled. The members storage
-     area can grow up to about 20GB before reaching wraparound.
+     have the oldest multixact-age. Both of these kinds of aggressive
+     scans will occur even if autovacuum is nominally disabled. At approximately
+     4 billion entries (occupying roughly 20GB in the
+     <literal>pg_multixact/members</literal> directory), even more aggressive
+     vacuum scans are triggered to reclaim member storage space.
+    </para>
+
+    <para>
+     The <function>pg_get_multixact_stats()</function> function described in
+     <xref linkend="functions-pg-snapshot"/> provides a way to monitor
+     multixact allocation and usage patterns in real time, for example:
+     <programlisting>
+=# SELECT *, pg_size_pretty(members_size) members_size_pretty
+     FROM pg_catalog.pg_get_multixact_stats();
+ num_mxids | num_members | members_size | oldest_multixact | members_size_pretty
+-----------+-------------+--------------+------------------+---------------------
+ 311740299 |  2785241176 |  13926205880 |                2 | 13 GB
+(1 row)
+     </programlisting>
+     This output shows a system with significant multixact activity: about
+     312 million multixact IDs and about 2.8 billion member entries consuming
+     13 GB of storage space.
+     A spike in <literal>num_mxids</literal> might indicate multiple sessions
+     running <literal>UPDATE</literal> statements with foreign key checks,
+     concurrent <literal>SELECT FOR SHARE</literal> operations, or frequent
+     use of savepoints causing lock contention.
+     If <literal>oldest_multixact</literal> value remains unchanged while
+     <literal>num_members</literal> grows, it could indicate that long-running
+     transactions are preventing cleanup, or autovacuum is
+     not keeping up with the workload.
     </para>
 
     <para>
-- 
2.51.0