Postgres per-table autovacuum management

Andrew Klotz

October 28, 2024 - 4 minutes read - 669 words

Per-table autovacuum tuning in Postgres is one of those things that is easy to set up but hard to keep track of once you have dozens of tables with different characteristics. This guide walks through adjusting global defaults, applying table-specific overrides, and building a single SQL view that shows you exactly where every table stands.

Postgres auto-vacuum and auto-analyze settings are important for managing database performance. The first improvement you will probably make is adjusting the default, conservative global values. These values generally work for smaller data sizes but start causing pain in larger data sets. The trigger kicks in using (scale_factor * rows) + threshold, so for large tables a default scale_factor of 0.20 will trigger far less often than you may like.

Default global auto-vacuum settings

autovacuum_analyze_scale_factor = .10
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = .20
autovacuum_vacuum_threshold = 50

After changing global settings, you will likely have tables with significantly different characteristics than others. This leads us into per-table tuning, which can be done pretty easily.

Alter table-specific autovacuum settings

ALTER TABLE foo SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100);

While making the changes on one table might be a good start, it is easy to start losing track of which tables have what settings. For myself, it is nice to have a quick view of what the current settings are.

The following query gives all the high-level information to understand the status of your per-table auto-vacuum settings.

Column	Notes
nspname	The namespace of the table
relname	The table name
reltuples	Total number of tuples
n_dead_tup	Number of dead tuples needing to be vacuumed
v_threshold	Autovacuum threshold for dead tuples
n_mod_since_analyze	Number of tuples modified since last analyze
a_threshold	Autoanalyze threshold for dead tuples
caf	Custom autoanalyze factor
cat	Custom autoanalyze threshold
cvt	Custom autovacuum threshold
cvf	Custom autovacuum factor
af	Global autoanalyze factor
at	Global autoanalyze threshold
vf	Global autovacuum factor
vt	Global autovacuum threshold
last_vacuum	Timestamp of last manual vacuum
last_autovacuum	Timestamp of last autovacuum

View all table-specific auto-vacuum settings

WITH raw_data AS (
  SELECT
    pg_namespace.nspname,
    pg_class.relname,
    pg_class.oid AS relid,
    pg_class.reltuples,
    pg_stat_all_tables.n_dead_tup,
    pg_stat_all_tables.n_mod_since_analyze,
    (SELECT split_part(x, '=', 2) FROM unnest(pg_class.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_scale_factor=' ) AS c_analyze_factor,
    (SELECT split_part(x, '=', 2) FROM unnest(pg_class.reloptions) q (x) WHERE x ~ '^autovacuum_analyze_threshold=' ) AS c_analyze_threshold,
    (SELECT split_part(x, '=', 2) FROM unnest(pg_class.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_scale_factor=' ) AS c_vacuum_factor,
    (SELECT split_part(x, '=', 2) FROM unnest(pg_class.reloptions) q (x) WHERE x ~ '^autovacuum_vacuum_threshold=' ) AS c_vacuum_threshold,
    to_char(pg_stat_all_tables.last_vacuum, 'YYYY-MM-DD HH24:MI:SS') AS last_vacuum,
    to_char(pg_stat_all_tables.last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') AS last_autovacuum
  FROM
    pg_class
  JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
    LEFT OUTER JOIN pg_stat_all_tables ON pg_class.oid = pg_stat_all_tables.relid
  WHERE
    n_dead_tup IS NOT NULL
    AND nspname NOT IN ('information_schema', 'pg_catalog')
    AND nspname NOT LIKE 'pg_toast%'
    AND pg_class.relkind = 'r'
), data AS (
  SELECT
    *,
    COALESCE(raw_data.c_analyze_factor, current_setting('autovacuum_analyze_scale_factor'))::float8 AS analyze_factor,
    COALESCE(raw_data.c_analyze_threshold, current_setting('autovacuum_analyze_threshold'))::float8 AS analyze_threshold,
    COALESCE(raw_data.c_vacuum_factor, current_setting('autovacuum_vacuum_scale_factor'))::float8 AS vacuum_factor,
    COALESCE(raw_data.c_vacuum_threshold, current_setting('autovacuum_vacuum_threshold'))::float8 AS vacuum_threshold
  FROM raw_data
)
SELECT
  relid,
  nspname,
  relname,
  reltuples,
  n_dead_tup,
  ROUND(reltuples * vacuum_factor + vacuum_threshold) AS v_threshold,
  n_mod_since_analyze,
  ROUND(reltuples * analyze_factor + analyze_threshold) AS a_threshold,
  c_analyze_factor AS caf,
  c_analyze_threshold AS cat,
  c_vacuum_factor AS cvf,
  c_vacuum_threshold AS cvt,
  analyze_factor AS af,
  analyze_threshold AS at,
  vacuum_factor AS vf,
  vacuum_threshold AS vt,
  last_vacuum,
  last_autovacuum
FROM
  data
ORDER BY n_dead_tup DESC;

We pull a few columns from the pg_class table, which is the catalog for anything that looks like a table. The relkind field narrows our search down to an ordinary table. We also filter out information_schema, pg_catalog, and anything that looks like a toast table with pg_toast.

The pg_stat_all_tables view records table statistics, which gives us the number of dead tuples in a table.

To get the namespace name we join the pg_namespace table.

It is worth noting that reloptions, the field for custom table auto-vacuum settings, is a string array. While most often it is the vacuum and analyze settings that are configured on a per-table basis, you could modify this query to view any of the other vacuum tuning options like autovacuum_vacuum_cost_limit if those are being used.

References

Postgres per-table autovacuum management (original post)