From: liu zhilong <liuzhilong62@outlook.com>
To: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: [PATCH] doc: explain database-wide impact of old xmin on VACUUM
Date: Mon, 1 Jun 2026 06:10:51 +0000
Message-ID: <OS3PR01MB8553C5907830DE25A8A4986ED9152@OS3PR01MB8553.jpnprd01.prod.outlook.com> (raw)
inline patch 1: doc/src/sgml/maintenance.sgml (+19/-0 lines) download 0001-doc-explain-database-wide-impact-of-old-xmin-on-VACU.patchFrom 1a2b3c4d5e6f7890abcdef1234567890abcdef12 Mon Sep 17 00:00:00 2001
From: Zhilong Liu <liuzhilong62@outlook.com>
Date: Thu, 28 May 2026 17:52:41 +0800
Subject: [PATCH] doc: explain database-wide impact of old xmin on VACUUM
Add a note to the "Recovering Disk Space" section explaining that
VACUUM uses the oldest xmin across backends in the current database
to decide which tuples are dead, so a long-running transaction on any
table can prevent space reclamation for rows newer than that
transaction in any table of the same database. Rows older than the
oldest active transaction can still be reclaimed normally. Also
mention that stale replication slots and prepared transactions have
the same effect.
For shared system catalogs, backends in all databases are considered.
---
Source verification: the database-wide (not cluster-wide) behavior
is implemented in ComputeXidHorizons() in procarray.c, which only
includes backends where proc->databaseId == MyDatabaseId for the
data_oldest_nonremovable horizon.
doc/src/sgml/maintenance.sgml | 18 ++++++++++++++++++
1 file changed, 18 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgmlindex 4a21bdb..5963ba1 100644--- a/doc/src/sgml/maintenance.sgml+++ b/doc/src/sgml/maintenance.sgml@@ -165,6 +165,24 @@
space requirements. This is done by running <command>VACUUM</command>.
</para>
+ <note>+ <para>+ To decide which row versions can be removed,+ <command>VACUUM</command> considers the oldest active transaction+ ID (<firstterm>xmin</firstterm>) across backends in the current+ database. Because of this, a long-running transaction on one table+ can prevent <command>VACUUM</command> from reclaiming space+ occupied by rows that are newer than that transaction in+ <emphasis>any</emphasis> table in the same database. For rows+ older than the oldest active transaction,+ <command>VACUUM</command> can still reclaim them normally. Stale+ replication slots and abandoned prepared transactions have the+ same effect, since they also hold old xmin values that block+ cleanup. Note that for shared system catalogs, backends in all+ databases are considered. For troubleshooting, see+ <xref linkend="vacuum-for-wraparound"/>.+ </para>+ </note>+
<para>
The standard form of <command>VACUUM</command> removes dead row
versions in tables and indexes and marks the space available for
--
Best regards,
Zhilong Liu
Patches and attachments:download all (2 files, .tar.gz)0001-doc-explain-database-wide-impact-of-old-xmin-on-VACU.patch (application/octet-stream, 2.5K) download
From 1a2b3c4d5e6f7890abcdef1234567890abcdef12 Mon Sep 17 00:00:00 2001
From: Zhilong Liu <liuzhilong62@outlook.com>
Date: Thu, 28 May 2026 17:52:41 +0800
Subject: [PATCH] doc: explain database-wide impact of old xmin on VACUUM
Add a note to the "Recovering Disk Space" section explaining that
VACUUM uses the oldest xmin across backends in the current database
to decide which tuples are dead, so a long-running transaction on any
table can prevent space reclamation for rows newer than that
transaction in any table of the same database. Rows older than the
oldest active transaction can still be reclaimed normally. Also
mention that stale replication slots and prepared transactions have
the same effect.
For shared system catalogs, backends in all databases are considered.
---
Source verification: the database-wide (not cluster-wide) behavior
is implemented in ComputeXidHorizons() in procarray.c, which only
includes backends where proc->databaseId == MyDatabaseId for the
data_oldest_nonremovable horizon.
doc/src/sgml/maintenance.sgml | 18 ++++++++++++++++++
1 file changed, 18 insertions(+)
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgmlindex 4a21bdb..5963ba1 100644--- a/doc/src/sgml/maintenance.sgml+++ b/doc/src/sgml/maintenance.sgml@@ -165,6 +165,24 @@
space requirements. This is done by running <command>VACUUM</command>.
</para>
+ <note>+ <para>+ To decide which row versions can be removed,+ <command>VACUUM</command> considers the oldest active transaction+ ID (<firstterm>xmin</firstterm>) across backends in the current+ database. Because of this, a long-running transaction on one table+ can prevent <command>VACUUM</command> from reclaiming space+ occupied by rows that are newer than that transaction in+ <emphasis>any</emphasis> table in the same database. For rows+ older than the oldest active transaction,+ <command>VACUUM</command> can still reclaim them normally. Stale+ replication slots and abandoned prepared transactions have the+ same effect, since they also hold old xmin values that block+ cleanup. Note that for shared system catalogs, backends in all+ databases are considered. For troubleshooting, see+ <xref linkend="vacuum-for-wraparound"/>.+ </para>+ </note>+
<para>
The standard form of <command>VACUUM</command> removes dead row
versions in tables and indexes and marks the space available for
--
2.39.5 (Apple Git-154)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: liuzhilong62@outlook.com
Subject: Re: [PATCH] doc: explain database-wide impact of old xmin on VACUUM
In-Reply-To: <OS3PR01MB8553C5907830DE25A8A4986ED9152@OS3PR01MB8553.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox