estimate_vacuum_completion.sql

CREATE OR REPLACE FUNCTION estimate_vacuum_completion(
    samples int DEFAULT 5,
    wait_ms int DEFAULT 2000
)
RETURNS TABLE (pid int, relname text, pct_done numeric, time_remaining interval)
AS $$
DECLARE
    s1_scanned bigint; s1_time numeric;
    s2_scanned bigint; s2_time numeric;
    s2_total bigint;   s2_relname text; s2_pid int;
BEGIN
    SELECT v.pid, v.heap_blks_scanned,
           v.heap_vacuum_time + v.index_vacuum_time + v.index_cleanup_time
    INTO s2_pid, s1_scanned, s1_time
    FROM pg_stat_progress_vacuum v LIMIT 1;

    IF NOT FOUND THEN RETURN; END IF;

    PERFORM pg_sleep(wait_ms * samples / 1000.0);

    SELECT v.heap_blks_scanned, v.heap_blks_total,
           v.heap_vacuum_time + v.index_vacuum_time + v.index_cleanup_time,
           v.relid::regclass::text
    INTO s2_scanned, s2_total, s2_time, s2_relname
    FROM pg_stat_progress_vacuum v WHERE v.pid = s2_pid;

    IF NOT FOUND OR s2_scanned <= s1_scanned THEN RETURN; END IF;

    RETURN QUERY SELECT
        s2_pid,
        s2_relname,
        round(s2_scanned::numeric / s2_total * 100, 2),
        make_interval(secs =>
            (s2_total - s2_scanned)::float
            / (s2_scanned - s1_scanned)::float
            * (s2_time - s1_time)::float);
END;
$$ LANGUAGE plpgsql;

SELECT * FROM estimate_vacuum_completion();