agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH] doc: explain database-wide impact of old xmin on VACUUM 1+ messages / 1 participants [nested] [flat]
* [PATCH] doc: explain database-wide impact of old xmin on VACUUM @ 2026-05-28 09:52 Zhilong Liu <liuzhilong62@outlook.com> 0 siblings, 0 replies; 1+ messages in thread From: Zhilong Liu @ 2026-05-28 09:52 UTC (permalink / raw) 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 =3D=3D 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.sgml index 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=3D"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 --_000_OS3PR01MB8553C5907830DE25A8A4986ED9152OS3PR01MB8553jpnp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"= > </head> <body> <div dir=3D"ltr" style=3D"font-family: Aptos, Arial, Helvetica, sans-serif;= font-size: 12pt; color: rgb(0, 0, 0);"> ^ permalink raw reply [nested|flat] 1+ messages in thread
only message in thread Thread overview: 1+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-05-28 09:52 [PATCH] doc: explain database-wide impact of old xmin on VACUUM Zhilong Liu <liuzhilong62@outlook.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox