Date: Tue, 3 Mar 2026 14:31:25 -0600
From: Nathan Bossart <nathandbossart@gmail.com>
To: Shinya Kato <shinya11.kato@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: enhance wraparound warnings
Message-ID: <aadFHfocomG20wgM@nathan>
References: <aRdhSSFb9zZH_0zc@nathan>
 <CAOzEurR9vD9kZ7=LAYqTDRju8wvCyvyEeiEtJVHJ_C6wH1u_MA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="qmnHGfI3v6dYD6Bi"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAOzEurR9vD9kZ7=LAYqTDRju8wvCyvyEeiEtJVHJ_C6wH1u_MA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aadFHfocomG20wgM%40nathan>
Precedence: bulk


--qmnHGfI3v6dYD6Bi
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Wed, Feb 18, 2026 at 04:16:16PM +0900, Shinya Kato wrote:
> On Sat, Nov 15, 2025 at 2:05 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
>> I don't know about you, but I start getting antsy around a quarter tank.
>> In any case, I'm told that even 40M transactions aren't enough time to
>> react these days.  Attached are a few patches to enhance the wraparound
>> warnings.
> 
> Thank you for the patch!

Thanks for reviewing.

> I don't have a strong opinion on whether 100M is the right value, but
> I noticed a documentation issue in 0002.
> 
> <programlisting>
> WARNING:  database "mydb" must be vacuumed within 39985967 transactions
> DETAIL:  Approximately 1.86% of transaction IDs are available for use.
> HINT:  To avoid XID assignment failures, execute a database-wide
> VACUUM in that database.
> </programlisting>
> 
> In maintenance.sgml, above "39985967" and "1.86%" should be updated.

Fixed.

> I'm not sure 0003 is worth the added complexity. It adds a new field
> to TransamVariablesData and a modulo check in GetNewTransactionId(),
> which is a hot path. DBAs who need early warning can already monitor
> age(datfrozenxid) with more flexible thresholds.

Yeah, looking at this one again, I'm less sure it's worth pursuing.  I've
removed it.

-- 
nathan

--qmnHGfI3v6dYD6Bi
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v4-0001-Add-percentage-of-transaction-IDs-that-are-availa.patch

From 07cd0d9d323eecb566d30ce2a73cd2f5def4ac18 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 14 Nov 2025 09:59:15 -0600
Subject: [PATCH v4 1/2] Add percentage of transaction IDs that are available
 to wraparound warnings.

---
 doc/src/sgml/maintenance.sgml          | 1 +
 src/backend/access/transam/multixact.c | 8 ++++++++
 src/backend/access/transam/varsup.c    | 8 ++++++++
 3 files changed, 17 insertions(+)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 7c958b06273..f146e14d3d6 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -674,6 +674,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
+DETAIL:  Approximately 1.86% of transaction IDs are available for use.
 HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.
 </programlisting>
 
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 8a5c9818ed6..9f5f8e692b8 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1053,6 +1053,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 									   multiWrapLimit - result,
 									   oldest_datname,
 									   multiWrapLimit - result),
+						 errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+								   (double) (multiWrapLimit - result) / (MaxMultiXactId / 2) * 100),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
@@ -1062,6 +1064,8 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 									   multiWrapLimit - result,
 									   oldest_datoid,
 									   multiWrapLimit - result),
+						 errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+								   (double) (multiWrapLimit - result) / (MaxMultiXactId / 2) * 100),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
@@ -2186,6 +2190,8 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
 								   multiWrapLimit - curMulti,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
+					 errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+							   (double) (multiWrapLimit - curMulti) / (MaxMultiXactId / 2) * 100),
 					 errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
@@ -2195,6 +2201,8 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
 								   multiWrapLimit - curMulti,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
+					 errdetail("Approximately %.2f%% of MultiXactIds are available for use.",
+							   (double) (multiWrapLimit - curMulti) / (MaxMultiXactId / 2) * 100),
 					 errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 3e95d4cfd16..2921148ceba 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -175,6 +175,8 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
+						 errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+								   (double) (xidWrapLimit - xid) / (MaxTransactionId / 2) * 100),
 						 errhint("To avoid transaction ID assignment failures, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
@@ -182,6 +184,8 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
+						 errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+								   (double) (xidWrapLimit - xid) / (MaxTransactionId / 2) * 100),
 						 errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
@@ -490,6 +494,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
+					 errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+							   (double) (xidWrapLimit - curXid) / (MaxTransactionId / 2) * 100),
 					 errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
@@ -497,6 +503,8 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
+					 errdetail("Approximately %.2f%% of transaction IDs are available for use.",
+							   (double) (xidWrapLimit - curXid) / (MaxTransactionId / 2) * 100),
 					 errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
-- 
2.50.1 (Apple Git-155)


--qmnHGfI3v6dYD6Bi
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=v4-0002-Bump-transaction-ID-limit-to-warn-at-100M.patch

From 78b65793dedd5105c23e93a7133b4433db755a79 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 12 Dec 2025 13:10:05 -0600
Subject: [PATCH v4 2/2] Bump transaction ID limit to warn at 100M.

---
 doc/src/sgml/maintenance.sgml          | 8 ++++----
 src/backend/access/transam/multixact.c | 6 +++---
 src/backend/access/transam/varsup.c    | 6 +++---
 3 files changed, 10 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index f146e14d3d6..75c22405a09 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -670,11 +670,11 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
    <para>
     If for some reason autovacuum fails to clear old XIDs from a table, the
     system will begin to emit warning messages like this when the database's
-    oldest XIDs reach forty million transactions from the wraparound point:
+    oldest XIDs reach one hundred million transactions from the wraparound point:
 
 <programlisting>
-WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-DETAIL:  Approximately 1.86% of transaction IDs are available for use.
+WARNING:  database "mydb" must be vacuumed within 99985967 transactions
+DETAIL:  Approximately 4.66% of transaction IDs are available for use.
 HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.
 </programlisting>
 
@@ -853,7 +853,7 @@ HINT:  Execute a database-wide VACUUM in that database.
 
     <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
+     system will begin to emit warning messages when the database's oldest MXIDs reach one hundred
      million transactions from the wraparound point.  And, just as in the XID case, if these
      warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
      than three million left until wraparound.
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 9f5f8e692b8..978ade705e7 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2092,16 +2092,16 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid)
 		multiStopLimit -= FirstMultiXactId;
 
 	/*
-	 * We'll start complaining loudly when we get within 40M multis of data
+	 * We'll start complaining loudly when we get within 100M multis of data
 	 * loss.  This is kind of arbitrary, but if you let your gas gauge get
-	 * down to 2% of full, would you be looking for the next gas station?  We
+	 * down to 5% of full, would you be looking for the next gas station?  We
 	 * need to be fairly liberal about this number because there are lots of
 	 * scenarios where most transactions are done by automatic clients that
 	 * won't pay attention to warnings.  (No, we're not gonna make this
 	 * configurable.  If you know enough to configure it, you know enough to
 	 * not get in this kind of trouble in the first place.)
 	 */
-	multiWarnLimit = multiWrapLimit - 40000000;
+	multiWarnLimit = multiWrapLimit - 100000000;
 	if (multiWarnLimit < FirstMultiXactId)
 		multiWarnLimit -= FirstMultiXactId;
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 2921148ceba..1441a051773 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -411,16 +411,16 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 		xidStopLimit -= FirstNormalTransactionId;
 
 	/*
-	 * We'll start complaining loudly when we get within 40M transactions of
+	 * We'll start complaining loudly when we get within 100M transactions of
 	 * data loss.  This is kind of arbitrary, but if you let your gas gauge
-	 * get down to 2% of full, would you be looking for the next gas station?
+	 * get down to 5% of full, would you be looking for the next gas station?
 	 * We need to be fairly liberal about this number because there are lots
 	 * of scenarios where most transactions are done by automatic clients that
 	 * won't pay attention to warnings.  (No, we're not gonna make this
 	 * configurable.  If you know enough to configure it, you know enough to
 	 * not get in this kind of trouble in the first place.)
 	 */
-	xidWarnLimit = xidWrapLimit - 40000000;
+	xidWarnLimit = xidWrapLimit - 100000000;
 	if (xidWarnLimit < FirstNormalTransactionId)
 		xidWarnLimit -= FirstNormalTransactionId;
 
-- 
2.50.1 (Apple Git-155)


--qmnHGfI3v6dYD6Bi--