A handy script that outputs reports to help understand how auto-vacuuming has been configured for a PostgreSQL database

pg-vacuuming

#!/bin/sh

# This script runs queries that interrogate vacuuming config and statistics.

set -e

function execute_query() {
  local query="$1"
  "$psql_cmd" "$database_url" -c "$query"
}

psql_cmd="${PSQL:-$(which psql || true)}"
if [ -z "$psql_cmd" ]; then
  echo "ERROR: please set the PSQL environment variable with the path to psql, or ensure the folder it lives in is in the PATH."
  exit 1
fi

if [ $# != 1 ]; then
  echo "USAGE: $(basename $0) [database_url|db_name]"
  exit 1
fi

database_url="$1"

if ! execute_query "SELECT 1" > /dev/null; then
  echo "ERROR: a problem occurred connecting to the database."
  exit 1
fi

settings_query="\
SELECT name AS parameter, boot_val AS default, unit, setting AS value, source
FROM pg_settings
WHERE name IN (
  'autovacuum',
  'autovacuum_analyze_scale_factor',
  'autovacuum_analyze_threshold',
  'autovacuum_freeze_max_age',
  'autovacuum_max_workers',
  'autovacuum_multixact_freeze_max_age',
  'autovacuum_naptime',
  'autovacuum_vacuum_cost_delay',
  'autovacuum_vacuum_cost_limit',
  'autovacuum_vacuum_scale_factor',
  'autovacuum_vacuum_threshold',
  'log_autovacuum_min_duration',
  'vacuum_cost_delay',
  'vacuum_cost_limit',
  'vacuum_cost_page_dirty',
  'vacuum_cost_page_hit',
  'vacuum_cost_page_miss',
  'vacuum_failsafe_age',
  'vacuum_freeze_min_age',
  'vacuum_freeze_table_age',
  'vacuum_multixact_failsafe_age',
  'vacuum_multixact_freeze_min_age',
  'vacuum_multixact_freeze_table_age'
)
ORDER BY 1;"

relopts_query="\
SELECT
  s.relname,
  opts.*
FROM pg_stat_user_tables s
  JOIN pg_class c ON s.relid = c.oid
  JOIN LATERAL pg_options_to_table(c.reloptions) opts ON true
WHERE c.relpersistence = 'p'
ORDER BY 1;"

thresholds_query="\
WITH opts_base AS (
  SELECT
    s.relid,
    opts.*
  FROM pg_stat_user_tables s
    JOIN pg_class c ON s.relid = c.oid
    LEFT JOIN LATERAL pg_options_to_table(c.reloptions) opts ON true
  WHERE c.relpersistence = 'p'
),
autovac_opts AS (
  SELECT
    s.relid,
    s.relname,
    o_a.option_value AS autovacuum_vacuum_scale_factor,
    o_b.option_value AS autovacuum_vacuum_threshold,
    o_c.option_value AS autovacuum_analyze_scale_factor,
    o_d.option_value AS autovacuum_analyze_threshold
  FROM pg_stat_user_tables s
  LEFT JOIN opts_base o_a ON s.relid = o_a.relid AND o_a.option_name = 'autovacuum_vacuum_scale_factor'
  LEFT JOIN opts_base o_b ON s.relid = o_b.relid AND o_b.option_name = 'autovacuum_vacuum_threshold'
  LEFT JOIN opts_base o_c ON s.relid = o_c.relid AND o_c.option_name = 'autovacuum_analyze_scale_factor'
  LEFT JOIN opts_base o_d ON s.relid = o_d.relid AND o_d.option_name = 'autovacuum_analyze_threshold'
)
SELECT
  c.relname,
  c.reltuples,
  coalesce(o.autovacuum_vacuum_threshold, current_setting('autovacuum_vacuum_threshold'))::float         AS vacuum_threshold,
  coalesce(o.autovacuum_vacuum_scale_factor, current_setting('autovacuum_vacuum_scale_factor'))::float   AS vacuum_scale_factor,
  s.n_dead_tup,
  (coalesce(o.autovacuum_vacuum_threshold, current_setting('autovacuum_vacuum_threshold'))::float
    + coalesce(o.autovacuum_vacuum_scale_factor, current_setting('autovacuum_vacuum_scale_factor'))::float
    * c.reltuples)::integer                                                                              AS resulting_vacuum_threshold,
  coalesce(o.autovacuum_analyze_threshold, current_setting('autovacuum_analyze_threshold'))::float       AS analyze_threshold,
  coalesce(o.autovacuum_analyze_scale_factor, current_setting('autovacuum_analyze_scale_factor'))::float AS analyze_scale_factor,
  s.n_mod_since_analyze,
  (coalesce(o.autovacuum_analyze_threshold, current_setting('autovacuum_analyze_threshold'))::float
    + coalesce(o.autovacuum_analyze_scale_factor, current_setting('autovacuum_analyze_scale_factor'))::float
    * c.reltuples)::integer                                                                              AS resulting_analyze_threshold
FROM pg_stat_user_tables s
  JOIN pg_class c ON s.relid = c.oid
  JOIN autovac_opts o ON o.relid = s.relid
WHERE c.relpersistence = 'p'
ORDER BY 1;"

stats_query="\
SELECT
  s.relname,
  autovacuum_count,
  age(current_timestamp, last_autovacuum)  AS last_autovacuum,
  autoanalyze_count,
  age(current_timestamp, last_autoanalyze) AS last_autoanalyze,
  vacuum_count,
  age(current_timestamp, last_vacuum)      AS last_vacuum,
  analyze_count,
  age(current_timestamp, last_analyze)     AS last_analyze
FROM pg_stat_user_tables s
  JOIN pg_class c ON s.relid = c.oid
WHERE c.relpersistence = 'p'
ORDER BY 1;"

output="\
Global settings
===============

This report shows global vacuuming related settings from pg_settings.

$(execute_query "$settings_query")


Table specific overrides
========================

This report shows any per-table settings from pg_class.reloptions.

$(execute_query "$relopts_query")


Calculated thresholds
=====================

This report calculates the current auto-vacuuming and auto-analyze thresholds,
taking into account that these settings can be overridden per-table. Like this:

* resulting_vacuum_threshold  = vacuum_threshold + vacuum_scale_factor * reltuples
* resulting_analyze_threshold = analyze_threshold + analyze_scale_factor * reltuples

Everytime the autovacuuming daemon runs for a table, it evaluates the thresholds like this:

* should_vacuum  = n_dead_tup > resulting_vacuum_threshold
* should_analyze = n_mod_since_analyze > resulting_analyze_threshold

$(execute_query "$thresholds_query")


Vacuuming statistics
====================

This report shows counts of runs and when the last run was.

$(execute_query "$stats_query")
"

echo "$output"