Autovacuum Behaviour and Tuning

Read the autovacuum trigger formula from pg_settings, override it for one high-churn table, and watch it fire faster than the cluster default

Autovacuum decides whether to vacuum a table using one formula, checked on every autovacuum cycle for every table in the cluster:\n\n dead tuples > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × reltuples)\n\nThe cluster-wide defaults — threshold 50, scale factor 0.2 — mean a 10-million-row table needs roughly two million dead tuples before autovacuum touches it. That is fine for a quiet reference table and much too slow for a high-churn queue or session table. ALTER TABLE ... SET (autovacuum_...) overrides both numbers for one table only, without touching the cluster default anything else relies on.\n\nThis lab also shortens autovacuum_naptime — the interval between autovacuum's own check cycles — from its default one minute down to five seconds, purely so the effect is observable in a lab timeframe rather than requiring a genuine wait. That change is explicitly a lab convenience, not something to carry into a real cluster.

The Trigger Formula

SELECT name, setting FROM pg_settings
WHERE name IN ('autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_naptime');

Autovacuum fires a VACUUM on a table once:

n_dead_tup > autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor × reltuples)

With the defaults (threshold 50, scale factor 0.2), a table with 20,000 rows needs roughly 4,050 dead tuples before autovacuum considers it due.

Overriding Per Table

ALTER TABLE monitoring.events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 50
);

This table now triggers at roughly 50 + (0.01 × 20,000) = 250 dead tuples — sixteen times more sensitive than the cluster default, while every other table in the database keeps using the unmodified global settings.

Speeding Up Observation (Lab Only)

ALTER SYSTEM SET autovacuum_naptime = '5s';
SELECT pg_reload_conf();

autovacuum_naptime controls how often the autovacuum launcher wakes up to re-check every table against the formula above. The default of one minute is completely reasonable in production; shortening it here exists purely so this lab's result appears in seconds rather than requiring a real wait.

autovacuum_vacuum_scale_factor

The fraction of a table's estimated row count (reltuples) added to autovacuum_vacuum_threshold to compute the dead-tuple count that triggers a VACUUM. A smaller scale factor makes autovacuum fire sooner, at the cost of running more often; a larger one lets more bloat accumulate between runs.

ALTER TABLE ... SET (autovacuum_...)

A storage parameter override scoped to a single table, stored in that table's own pg_class.reloptions rather than in postgresql.conf. It takes effect immediately for that table alone — no reload or restart needed — and never affects any other table in the cluster.

📐 Read the Trigger Formula

Check the cluster-wide autovacuum settings that decide when any table gets vacuumed.

psql -U postgres -d beer_db -c "SELECT name, setting FROM pg_settings WHERE name IN ('autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_naptime');"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT name, setting FROM pg_settings WHERE name IN ('autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor', 'autovacuum_naptime');" SET name | setting ---------------------------------+--------- autovacuum_naptime | 60 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 (3 rows)

⏱️ Shorten the Naptime (Lab Only)

Lower autovacuum_naptime to 5 seconds so this lab's result is observable without a real wait — never a production setting.

psql -U postgres -d beer_db -c "ALTER SYSTEM SET autovacuum_naptime = '5s';" -c "SELECT pg_reload_conf();"

student@lab:~$ psql -U postgres -d beer_db -c "ALTER SYSTEM SET autovacuum_naptime = '5s';" -c "SELECT pg_reload_conf();" SET ALTER SYSTEM pg_reload_conf ---------------- t (1 row)

🎯 Tune This One Table

Override autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold on monitoring.events only.

psql -U postgres -d beer_db -c "ALTER TABLE monitoring.events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 50);"

student@lab:~$ psql -U postgres -d beer_db -c "ALTER TABLE monitoring.events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 50);" SET ALTER TABLE

💥 Generate Dead Tuples

Delete 20% of the rows and immediately check n_dead_tup and last_autovacuum.

psql -U postgres -d beer_db -c "DELETE FROM monitoring.events WHERE id % 5 = 0;"

psql -U postgres -d beer_db -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'events';"

student@lab:~$ psql -U postgres -d beer_db -c "DELETE FROM monitoring.events WHERE id % 5 = 0;" SET DELETE 4000 student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'events';" SET relname | n_live_tup | n_dead_tup | last_autovacuum ---------+------------+------------+----------------- events | 16000 | 4000 | (1 row)

✅ Watch the Tuned Trigger Fire

Wait long enough for one naptime cycle to pass, then confirm the dead tuples were cleared and last_autovacuum is populated.

sleep 10

psql -U postgres -d beer_db -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'events';"

student@lab:~$ sleep 10 student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'events';" SET relname | n_live_tup | n_dead_tup | last_autovacuum ---------+------------+------------+------------------------------- events | 16000 | 0 | 2026-06-26 16:02:47.776386+00 (1 row)

🔍 Confirm the Override Persisted

Read the per-table override back from pg_class to confirm it survived, independent of the session that set it.

psql -U postgres -d beer_db -c "SELECT relname, reloptions FROM pg_class WHERE relname = 'events';"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, reloptions FROM pg_class WHERE relname = 'events';" SET relname | reloptions ---------+---------------------------------------------------------------------- events | {autovacuum_vacuum_scale_factor=0.01,autovacuum_vacuum_threshold=50} (1 row)

Lab 2.4.4 complete. You can now read, override, and verify autovacuum's trigger behaviour per table:\n\n\n Trigger formula read : ✅ threshold + scale_factor × reltuples\n Per-table override : ✅ ALTER TABLE ... SET (autovacuum_...)\n Naptime shortened for lab : ✅ observed the effect in seconds\n Tuned trigger fired : ✅ dead tuples cleared, last_autovacuum advanced\n

Enable JavaScript to run the live terminal and track your progress.