Technical Analysis: [PATCH] doc: explain database-wide impact of old xmin on VACUUM

Core Problem

This thread addresses a documentation gap in PostgreSQL regarding how a single old transaction's xmin horizon can impact VACUUM effectiveness across an entire database, not just the table(s) that the transaction has touched.

Why This Matters Architecturally

PostgreSQL's MVCC implementation relies on transaction IDs (XIDs) to determine tuple visibility. VACUUM reclaims dead tuples that are no longer visible to any active transaction. The critical architectural detail is that the xmin horizon — the oldest transaction ID that any running transaction might still need to see — is computed globally (at the database level, and in some cases cluster-wide for shared catalogs).

This means:

• A long-running transaction (or an abandoned replication slot, or a prepared transaction) holding an old xmin prevents VACUUM from removing dead tuples in any table in the database, even tables that transaction never accessed.
• This is a common source of table bloat and performance degradation that catches many DBAs by surprise.
• The documentation prior to this patch apparently did not make this database-wide scope sufficiently clear to users.

The Visibility/Horizon Mechanism

The xmin horizon is determined by:

1. The oldest running transaction's XID (oldestXmin)
2. Replication slot xmin and catalog_xmin values
3. Prepared (2PC) transactions
4. Hot standby feedback from replicas

When VACUUM processes a table, it cannot remove any tuple version whose xmax (deletion XID) is >= this global horizon, regardless of whether the transaction holding the horizon open ever touched that specific table.

Proposed Solution

The patch is a documentation improvement that explicitly explains:

1. That a single old xmin affects VACUUM's ability to clean up dead tuples database-wide
2. The mechanisms by which old xmin values persist (long transactions, replication slots, prepared transactions)
3. The practical consequences for table bloat

This is a "doc-only" patch — no code changes — aimed at improving user understanding of a frequently misunderstood architectural behavior.

Key Technical Insights

The database-wide nature of the xmin horizon is a fundamental consequence of PostgreSQL's MVCC design:

• Transaction snapshots are taken at the database (or cluster) level
• There is no per-table tracking of "which transactions care about this table"
• This is a deliberate simplicity/performance tradeoff — per-table horizon tracking would add significant complexity to snapshot management

Assessment

This is a straightforward documentation patch addressing a well-known operational pain point. The technical content it documents is uncontroversial — the behavior is well-established. The main review questions would be:

• Whether the documentation placement is appropriate (likely in the VACUUM or routine maintenance sections)
• Whether the explanation is clear and complete enough
• Whether it should cross-reference related topics like pg_stat_activity.backend_xmin, replication slots, and old_snapshot_threshold

Given that this is a single-message thread (initial submission), no review feedback has been provided yet.