Autovacuum, explained for engineers, new improved version 2015 Vienna

Lots of things improved, now these slides can be more useful without talk


  1. Great overview. Another aspect of vacuum is how covering indexes are dependent whether a relallvisible are kept up-to-date (maintained by vacuum). After each ETL or other data processing job we run the following query and vacuums tables which are not up to date. Otherwise our reports suffer massively on covering indexes not being used.

    select nspname, relname
    from pg_class c
    join pg_catalog.pg_namespace n on n.oid = c.relnamespace
    where relkind = 'r'
    and relallvisible != relpages
    order by nspname, relname

    Are there any setting that will ensure autovacuum is run, when relallvisible differs from relpages? Will autovacuum_vacuum_threshold =1 do the trick or will that have side effects?

    We are very dependent on covering indexes for real-time analysis.


Post a Comment

You might also be interested in

Deep dive into postgres stats: pg_stat_replication

Deep dive into Postgres stats: pg_stat_progress_vacuum

pg_wal is too big… what's going on?