Re: Option to not use ringbuffer in VACUUM, using it in failsafe mode

Hi,

On 2023-01-11 10:27:20 -0800, Andres Freund wrote:
> On cloud hardware with higher fsync latency I've seen > 15x time differences
> between using the ringbuffers and avoiding them by using pg_prewarm.

A slightly edited version of what I've in the past to defeat the ringbuffers
using pg_prewarm, as I think it might be useful for others:

WITH what_rel AS (
SELECT 'copytest_0'::regclass AS vacuum_me
),
what_to_prefetch AS (
SELECT vacuum_me, greatest(heap_blks_total - 1, 0) AS last_block,
CASE WHEN phase = 'scanning heap' THEN heap_blks_scanned ELSE heap_blks_vacuumed END AS current_pos
FROM what_rel, pg_stat_progress_vacuum
WHERE relid = vacuum_me AND phase IN ('scanning heap', 'vacuuming heap')
)
SELECT
vacuum_me, current_pos,
pg_prewarm(vacuum_me, 'buffer', 'main', current_pos, least(current_pos + 10000, last_block))
FROM what_to_prefetch
\watch 0.1

Having this running in the background brings the s_b=128MB, ringbuffer enabled
case down from 77797ms to 14838ms. Close to the version with the ringbuffer
disabled.

Unfortunately, afaik, that trick isn't currently possible for the index vacuum
phase, as we don't yet expose the current scan position. And not every index
might be as readily prefetchable as just prefetching the next 10k blocks from
the current position.

That's not too bad if your indexes are small, but unfortunately that's not
always the case...

Greetings,

Andres Freund