Hi Hackers, On standbys, WAL replay of VACUUM-generated XLOG_HEAP2_PRUNE records requires a cleanup lock (exclusive lock + zero pin count) on the target buffer. If any backend holds a pin on that buffer, even from a trivial SELECT, replay stalls until max_standby_streaming_delay expires, at which point the query is canceled. This can also cause increased replication lag.
Andres and me chatted about this problem offline and he suggested below approaches to improve the current situation: 1. Shorten the time the queries hold the pins 2. Eliminate unnecessary cleanup lock requirements in replay 3. Improve diagnosis logging Additionally, we discussed an approach of redo process performing COW on cleanup lock conflict instead of waiting until the max_standby_streaming_delay under a GUC). I am not including it as part of this thread and can start a different one if there is enough interest in that approach. Attached 3 distinct patches for each of these. Patch 1: Reduce buffer pin lifetime for unique index point lookups The most common OLTP pattern is fetching a single row by primary key which holds a heap buffer pin in the scan descriptor (xs_cbuf) until the next fetch or scan end. For a point lookup returning exactly one row, this means the pin lives for the entire duration of whatever the executor does with that tuple (evaluating expressions, passing through plan nodes, pg_sleep() in a function, nested-loop outer side, etc.). This patch adds two new ScanOption flags: SO_MATERIALIZE_ON_FETCH (IndexScan) - After fetching the heap tuple, materialize it into the slot (heap_copytuple equivalent) and release both the slot's buffer pin and the scan descriptor's xs_cbuf pin. SO_RELEASE_PIN_ON_FETCH (IndexOnlyScan) - Since IOS only visits the heap for visibility checks (output data comes from the index), just release xs_cbuf. The slot's own pin is released by ExecClearTuple immediately after index_fetch_heap returns in IndexOnlyNext. No tuple copy is needed. Both flags are set only when: - The index is unique (rd_index->indisunique) - Scan keys cover all key columns (NumScanKeys >= indnkeyatts) This guarantees at most one heap page visit per scan, so there is no same-page pin reuse benefit to preserve (unlike range scans where consecutive tuples often share a buffer). I didn't see any obvious difference in performance with this change because the materialize cost (one heap_copytuple per point lookup) is negligible compared to the index traversal + buffer lock cycle. Patch 2: Skip cleanup lock on replay for freeze-only prune records Currently, all XLOG_HEAP2_PRUNE WAL records request a cleanup lock on replay (XLHP_CLEANUP_LOCK flag is unconditionally set). However, when a prune operation ONLY freezes tuples i.e. no redirections, no dead items, no unused items then it doesn't modify line pointers. Concurrent readers that follow line pointers are not affected by a freeze (which only touches tuple header xid fields). This patch passes `do_prune` instead of unconditional `true` as the cleanup_lock parameter to log_heap_prune_and_freeze(). When do_prune is false (freeze-only case), the WAL record is emitted without XLHP_CLEANUP_LOCK. On replay, the standby acquires only an exclusive content lock (not a cleanup lock), eliminating recovery conflicts for freeze-only operations. This is a targeted fix for a common scenario: aggressive_freeze or vacuum_freeze_min_age=0 vacuums that freeze pages without needing to prune dead tuples. The heapdesc.c change adds "cleanupLock: T/F" to pg_waldump output for prune records, making it easy to audit which records actually need cleanup locks. Patch 3: Add relation info to recovery conflict buffer pin logs When a recovery conflict on a buffer pin is logged (either resolved or triggering query cancellation), the current message only says "recovery conflict on buffer pin" with timing information. It doesn't tell which relation/page is causing the conflict. This patch adds the buffer tag (spcOid/dbOid/relNumber/blockNum) to the LOG message, so DBAs can identify: - Which table is being vacuumed heavily - Which specific block is hot - Whether the conflict is on a heap page or index page Example output: LOG: recovery conflict waiting on buffer pin: rel 1663/16384/16385 blk 42 Please review and let me know if there are other cases to optimize and additional cases to handle. Thanks, Satya
v1-0001-Reduce-buffer-pin-lifetime-for-unique-index-point-lookups.patch
Description: Binary data
v1-0003-Add-relation-identity-to-recovery-conflict-buffer-pin-logs.patch
Description: Binary data
v1-0002-Skip-cleanup-lock-on-replay-for-freeze-only-prune-records.patch
Description: Binary data
