adaptive_vacuum_tuning.sql

WITH stats AS (
SELECT
schemaname,
relname,
n_live_tup,
n_dead_tup,
n_tup_upd,
n_tup_del,
(n_tup_upd + n_tup_del)::numeric / NULLIF(n_live_tup, 0) AS churn_ratio
FROM pg_stat_all_tables
WHERE schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
)
SELECT
format(
‘ALTER TABLE %I.%I SET (
autovacuum_vacuum_scale_factor = %.4f,
autovacuum_vacuum_threshold = %s,
fillfactor = %s
);’,
schemaname,
relname,
CASE
WHEN churn_ratio IS NULL THEN 0.2 — default
WHEN churn_ratio > 0.5 THEN 0.01 — very high churn
WHEN churn_ratio > 0.2 THEN 0.02 — medium churn
ELSE 0.05 — mild churn
END,
CASE
WHEN n_live_tup < 10000 THEN ’50’ — small table WHEN n_live_tup < 100000 THEN ‘200’ — medium table ELSE ‘500’ — large table END, CASE WHEN churn_ratio > 0.5 THEN ’75’ — leave 25% free space
WHEN churn_ratio > 0.2 THEN ’85’
ELSE ‘100’
END
) AS tuning_command
FROM stats
WHERE n_live_tup > 0
ORDER BY churn_ratio DESC NULLS LAST;

Exemple output :

ALTER TABLE public.orders SET (
autovacuum_vacuum_scale_factor = 0.0100,
autovacuum_vacuum_threshold = 500,
fillfactor = 75
);

ALTER TABLE public.logs SET (
autovacuum_vacuum_scale_factor = 0.0200,
autovacuum_vacuum_threshold = 200,
fillfactor = 85
);

Automated this weekly via cronjob:

psql -U postgres -d mydb -f adaptive_vacuum_tuning.sql -A -t > apply_tuning.sql
psql -U postgres -d mydb -f apply_tuning.sql

Imspect autovacuum effectiveness:

SELECT relname,
last_autovacuum,
n_dead_tup,
vacuum_count,
autovacuum_count
FROM pg_stat_all_tables
WHERE schemaname NOT IN (‘pg_catalog’, ‘information_schema’)
ORDER BY n_dead_tup DESC;