Section 3 of 3 · 7 minutes
Tune from one table's change rate
Your goal
What you will be able to do
Choose table-level autovacuum thresholds from table size, change rate, and maintenance duration.
Why this matters at work
The practical reason
One global setting rarely fits both small busy tables and large slowly changing tables.
Learn
The idea in plain English
Autovacuum triggers combine a fixed threshold with a fraction of the table. For a very large table, the default fraction may allow too many changes before maintenance starts.
Measure how quickly dead rows accumulate and how long vacuum takes. Change one table-level setting, then watch several maintenance cycles for lock, IO, and query effects.
Remember these points
- Calculate the trigger in rows for the actual table size.
- Use table-level settings for exceptional workloads.
- Verify several cycles before declaring the change successful.
See it in SQL
Read table-specific autovacuum settings
Check whether a busy table overrides the server defaults.
SELECT
relname AS table_name,
reloptions
FROM pg_class
WHERE oid = 'training_events'::regclass;`reloptions` contains table-level overrides such as vacuum scale factors. A null value means the table uses inherited defaults.
Record the old options before changing them so rollback is exact.
The stored table-level options for training_events, or null when none are set.
Useful words
Important terms
- Scale factor
- The fraction of a table used when calculating an autovacuum or autoanalyze trigger.
Quick check · Not graded
Check your understanding
When is a table-level autovacuum setting most useful?
Your progress is saved to your signed-in account.