Experimental patch for terminating VACUUM freeze blockers

First seen: 2026-05-13 11:56:43+00:00 · Messages: 4 · Participants: 3

Latest Update

2026-05-14 · claude-opus-4-6

Experimental Patch for Terminating VACUUM Freeze Blockers

The Core Problem: VACUUM Failsafe Cannot Overcome Horizon Blockers

PostgreSQL's anti-wraparound VACUUM mechanism has a well-known architectural gap. When a table's age reaches vacuum_failsafe_age (default 1.6 billion transactions), VACUUM enters failsafe mode — it bypasses cost-based throttling (autovacuum_vacuum_cost_delay), skips index vacuuming, and generally runs as aggressively as possible to freeze tuples and advance relfrozenxid. This mechanism was introduced in PostgreSQL 14 as a last-resort defense against transaction ID wraparound.

However, failsafe mode addresses only the throughput dimension of the problem. VACUUM's freeze cutoff (OldestXmin) is fundamentally bounded by the oldest transaction horizon visible anywhere in the system. If an idle-in-transaction session, a long-running query, or any backend holding an old xmin/XID exists, VACUUM physically cannot advance relfrozenxid past that point — no matter how fast it runs. The failsafe mechanism accelerates VACUUM's I/O, but it cannot shrink the gap between the current transaction counter and the oldest horizon holder. This means the system can still march inexorably toward wraparound shutdown even with failsafe active.

This is a genuine operational risk. In production environments, the scenario typically unfolds as: (1) an application leaves a session idle-in-transaction for hours or days, (2) autovacuum runs repeatedly but cannot advance relfrozenxid, (3) table age grows toward 2 billion, and (4) PostgreSQL eventually refuses to accept new transactions to prevent wraparound. The existing mitigations — idle_in_transaction_session_timeout, statement_timeout, monitoring — require proactive DBA configuration and do not cover all blocker types.

Proposed Solution: Terminate Freeze Horizon Blockers

The patch introduces a new GUC vacuum_freeze_terminate_blockers_pid (despite the name suggesting a PID, it appears to function as a boolean toggle) that, when enabled, allows VACUUM to terminate regular client backends whose transaction horizons are blocking freeze progress. The mechanism would specifically target:

The patch explicitly excludes other horizon-holding mechanisms:

Design Analogy: Replay/Query Conflicts on Standbys

The patch author draws an important architectural parallel to PostgreSQL's existing hot standby conflict resolution mechanism. On a streaming replica with hot_standby_feedback disabled, WAL replay can cancel queries that hold conflicting snapshots (controlled by max_standby_streaming_delay and max_standby_archive_delay). This is the same fundamental tradeoff: system-critical progress (WAL replay / freeze advancement) versus user-facing query availability.

The analogy is technically sound. In the standby case, PostgreSQL already accepts that terminating user sessions is sometimes the correct tradeoff when system health is at stake. The proposed patch extends this principle to the primary server's anti-wraparound mechanism.

Key Technical Concerns and Tradeoffs

1. Symptom vs. Root Cause (Sami Imseih's Critique)

The central objection raised is that this patch treats the symptom (VACUUM cannot advance relfrozenxid) rather than the root cause (poorly managed long-running transactions). This is a legitimate architectural concern with several dimensions:

2. Naming and Interface Issues

The GUC name vacuum_freeze_terminate_blockers_pid is misleading — it appears to be a boolean flag, not a PID parameter. This suggests the patch is early-stage and the interface design hasn't been fully thought through. Important design questions remain:

3. Scope Limitations

By excluding replication slots, hot standby feedback, and prepared transactions, the patch addresses only a subset of horizon blockers. Nathan Bossart additionally raises stranded temporary tables as another significant blocker that this patch doesn't address — temporary tables hold relfilenode mappings tied to the creating session's transaction and can hold back the horizon if the session is abandoned but not terminated.

4. Missing Blockers: The Broader Landscape

Nathan Bossart's response contextualizes this within the broader set of freeze-horizon problems:

Assessment

The patch addresses a real operational pain point but faces significant headwinds:

  1. Philosophical objection: The community generally prefers mechanisms that help DBAs identify and fix problems over mechanisms that silently work around them. Better VACUUM-blocker observability (system views, wait events) is likely to gain more traction.

  2. Incomplete coverage: Not addressing replication slots and prepared transactions limits practical utility, as these are often the worst offenders in production.

  3. Precedent concern: Allowing VACUUM to terminate arbitrary backends is a significant escalation of VACUUM's authority. The standby conflict analogy is imperfect — on a standby, query cancellation is expected and recoverable; on a primary, terminating a session with an active transaction causes rollback and potential application-level damage.

  4. Early stage: The naming issues and lack of nuanced configuration (thresholds, grace periods, per-table control) suggest this needs substantial design iteration before it could be considered for inclusion.

The discussion is valuable for surfacing the problem, but the solution likely needs to evolve toward either (a) better integration with the failsafe mechanism with configurable thresholds, or (b) focusing on observability improvements that let external tooling make the termination decision.