exec_vacuum.sql

exec_vacuum.sql

\timing on
with info as (
    SELECT
        stat.relid as relid,
        stat.relid::regclass as regclass,
        age(c.relfrozenxid) > current_setting('vacuum_freeze_min_age')::int
        and age(c.relfrozenxid) > current_setting('vacuum_freeze_table_age')::int autovacuum_can_freeze,
        ROUND(100*(age(c.relfrozenxid)/current_setting('vacuum_freeze_table_age')::float)) as perc_to_frozen,
        ROUND(100*(age(c.relfrozenxid)/ current_setting('autovacuum_freeze_max_age')::float))  perc_to_wrap,
        CASE WHEN c.reltuples > 0 THEN
	         ROUND(100*(stat.n_ins_since_vacuum /c.reltuples))
	         ELSE 0
        END per_to_autovac,
        age(c.relfrozenxid),
        c.relfrozenxid,
        c.reltuples,
        stat.n_ins_since_vacuum,
        stat.last_vacuum,
        stat.last_autovacuum,
        stat.autovacuum_count,
        age(c.relfrozenxid) < age(t.relfrozenxid) toast_is_worse
    FROM
        pg_stat_all_tables stat
    LEFT JOIN
        pg_class c on (c.oid=stat.relid)
    LEFT JOIN
        pg_class t ON c.reltoastrelid = t.oid
)
select
    format($fmt$
        SELECT now()::text ||': starting vacuum of ' || %1$L || ' age is ' || (SELECT age(c.relfrozenxid) FROM pg_class c WHERE oid = %2$L);
    $fmt$, regclass, relid),

    format($fmt$
        VACUUM FREEZE VERBOSE %1$s;
    $fmt$, regclass, relid),

    format($fmt$
        SELECT now()::text ||': after vacuum of ' || %1$L || ' age is ' || (SELECT age(c.relfrozenxid) FROM pg_class c WHERE oid = %2$L);
    $fmt$, regclass, relid)
FROM info
--WHERE autovacuum_can_freeze
ORDER BY age desc
LIMIT 100\gexec





\timing on
with info as (
    SELECT
        c.oid as relid,
        c.oid::regclass as regclass,
        mxid_age(c.relminmxid) as age
    FROM
        pg_class c
    WHERE
        c.relkind != 't'
)
select
    format($fmt$
        SELECT now()::text ||': starting vacuum of ' || %1$L || ' age is ' || (SELECT  mxid_age(c.relminmxid) FROM pg_class c WHERE oid = %2$L);
    $fmt$, regclass, relid),

    SET SESSION vacuum_multixact_freeze_min_age = 0;
    SET SESSION vacuum_multixact_freeze_table_age = 0;

    format($fmt$
        VACUUM FREEZE VERBOSE %1$s;
    $fmt$, regclass, relid),

    format($fmt$
        SELECT now()::text ||': after vacuum of ' || %1$L || ' age is ' || (SELECT  mxid_age(c.relminmxid) FROM pg_class c WHERE oid = %2$L);
    $fmt$, regclass, relid)
FROM info
ORDER BY age desc
LIMIT 100\gexec