skip to content

In PostgreSQL 18, what thresholds make autovacuum pick a table for VACUUM or ANALYZE, and why do the defaults fail large tables?

level: middleimportance: must knowfreq 68%

answer

  1. base plus a fraction of reltuples
  2. 0.2 for vacuum, 0.1 for analyze
  3. 18 caps the dead-tuple trigger
  4. inserts have their own trigger
  5. storage parameters per table

basics

~20 s

Autovacuum vacuums a table once dead tuples pass 50 + 0.2 × reltuples (capped at 100 million in PostgreSQL 18) or inserts pass a similar bar, and analyzes after 50 + 0.1 × reltuples changes, so big tables wait for millions of dead rows.

solid answer

~40 s

Every `autovacuum_naptime` (1 min) the launcher starts a worker in each database, and the worker checks each table against counters from the cumulative statistics system, so `track_counts` must be on. A table is vacuumed when dead tuples since the last vacuum exceed `autovacuum_vacuum_threshold` + `autovacuum_vacuum_scale_factor` × `reltuples` (defaults 50 and 0.2), which PostgreSQL 18 caps at `autovacuum_vacuum_max_threshold` (100,000,000). It is also vacuumed when inserts exceed `autovacuum_vacuum_insert_threshold` (1000) + 0.2 × `reltuples` × the unfrozen fraction, and analyzed when inserts, updates and deletes exceed 50 + 0.1 × `reltuples`. On a 500-million-row table the vacuum trigger is still 100 million dead tuples, so hot big tables get their own lower scale factor through `ALTER TABLE ... SET`.

code

sql · 8 lines
sql
-- Where a table stands against the PostgreSQL 18 vacuum trigger (default settings)
SELECT c.relname,
       c.reltuples::bigint AS reltuples,
       least(100000000, 50 + 0.2 * c.reltuples)::bigint AS vacuum_trigger,
       (50 + 0.1 * c.reltuples)::bigint AS analyze_trigger,
       c.reloptions
FROM pg_class AS c
WHERE c.relname = 'user_sessions';

go deeper

for a junior

Recall that autovacuum is on by default and starts vacuuming a table once a share of its rows, 20 percent plus 50 by default, has been updated or deleted.

for a middle

Explain the three triggers with their defaults, the reltuples term, 18's 100-million cap and unfrozen-fraction insert trigger, and why a percentage fails on big tables.

for a senior

Show how you would pick per-table overrides for the few big, hot tables, and the blind spots such as partitioned parents and temporary tables that need manual ANALYZE.

for a principal

Weigh cluster-wide defaults against per-table exceptions: global changes affect thousands of small tables, while per-table settings need an owner and review as tables grow.

## The moving parts PostgreSQL's **autovacuum** is a set of background processes, not one job: - The **autovacuum launcher** runs permanently and tries to start one worker in each database every `autovacuum_naptime` (default `1min`). - Each **autovacuum worker** walks the tables of its database and runs `VACUUM`, `ANALYZE` or both on the ones that pass a threshold. - At most `autovacuum_max_workers` (default `3`) workers run at once across the whole cluster. - The decision uses the **cumulative statistics system**: per-table counts of inserted, updated and deleted tuples. They are only collected when `track_counts` is `on` (the default), and autovacuum cannot work without them. The counts are eventually consistent, not exact. All of these change with a configuration reload, and every threshold below can be overridden per table with **storage parameters**. ## The dead-tuple trigger A table is vacuumed when the number of tuples updated or deleted since its last vacuum exceeds: ``` vacuum threshold = min(autovacuum_vacuum_max_threshold, autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples) ``` `reltuples` is the table's estimated row count in `pg_class`, refreshed by `VACUUM` and `ANALYZE`. | Setting | Default | Role | |---|---|---| | `autovacuum_vacuum_threshold` | 50 | fixed base, so tiny tables are not vacuumed after one update | | `autovacuum_vacuum_scale_factor` | 0.2 | fraction of the table that must be dead | | `autovacuum_vacuum_max_threshold` | 100000000 | cap added in PostgreSQL 18; `-1` disables it | | `autovacuum_vacuum_insert_threshold` | 1000 | base for the insert trigger; `-1` disables insert vacuums | | `autovacuum_vacuum_insert_scale_factor` | 0.2 | fraction for the insert trigger | | `autovacuum_analyze_threshold` | 50 | base for the analyze trigger | | `autovacuum_analyze_scale_factor` | 0.1 | fraction for the analyze trigger | ## The insert trigger A table that only receives inserts produces no dead tuples, yet it still needs vacuuming to set visibility map bits and freeze old tuples. So a second trigger fires when inserts since the last vacuum exceed: ``` insert threshold = autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * reltuples * (1 - relallfrozen / relpages) ``` The last factor, the **fraction of the table not yet frozen**, is new in PostgreSQL 18 together with the `pg_class.relallfrozen` column. On a large append-only table that is mostly frozen, 18 vacuums much sooner than 17 did, because 17 multiplied by the whole of `reltuples`. ## The analyze trigger `ANALYZE` runs when inserted, updated and deleted tuples since the last analyze exceed `autovacuum_analyze_threshold` + `autovacuum_analyze_scale_factor` × `reltuples`, by default 50 + 10%. There is no cap on this one, so on a very large table the planner can work from statistics that describe the data as it was many millions of changes ago. A worker that finds a table past both triggers runs `VACUUM ANALYZE` in one go, which is why `pg_stat_activity` sometimes shows `autovacuum: VACUUM ANALYZE` followed by the table name. What `ANALYZE` collects belongs to the statistics topic. ## Worked numbers | Table size (`reltuples`) | Vacuum trigger in 17 | Vacuum trigger in 18 | Analyze trigger | |---|---|---|---| | 10,000 | 2,050 | 2,050 | 1,050 | | 10,000,000 | 2,000,050 | 2,000,050 | 1,000,050 | | 1,000,000,000 | 200,000,050 | 100,000,000 | 100,000,050 | This is the core interview point. A percentage that suits a small table means **millions of dead tuples** on a big one before anything happens. When vacuum finally starts, it has a large backlog to process, the indexes have grown with dead entries, and queries have been scanning past dead tuples the whole time. The 18 cap helps only the very largest tables; a 50-million-row table still waits for 10 million dead tuples. ## Other rules that decide whether a table is processed 1. A table whose `relfrozenxid` is older than `autovacuum_freeze_max_age` is vacuumed regardless of these thresholds; that anti-wraparound path is the XID wraparound topic. 2. **Partitioned tables** hold no tuples, so autovacuum never vacuums or analyzes the parent; it processes each partition like a normal table. Run `ANALYZE` on the parent yourself after loading data or when its distribution changes. 3. **Temporary tables** are invisible to autovacuum; the session that owns them must vacuum and analyze them. 4. The storage parameter `autovacuum_enabled = false` takes a table out of threshold-based autovacuum, though not out of anti-wraparound vacuums. ## Fixing the large-table problem Override the thresholds for the tables that need it rather than lowering them for the whole cluster: ```sql ALTER TABLE user_sessions SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000, autovacuum_analyze_scale_factor = 0.02 ); ``` The new values show up in `pg_class.reloptions` and apply on the worker's next check. `ALTER TABLE ... RESET (...)` puts a parameter back to the global default.

  • Why does PostgreSQL's autovacuum never ANALYZE a partitioned table's parent, and what do you do about it?
    The parent stores no tuples, so no change counters accumulate on it and autovacuum skips it; it does process every partition. The parent's own statistics, which the planner uses for queries against it, therefore go stale. Run `ANALYZE` on the parent after the first load and whenever the data distribution shifts noticeably.
  • How does PostgreSQL 18's insert trigger differ from 17's on a large append-only table?
    17 fires after 1000 + 0.2 × `reltuples` inserts. 18 multiplies the scale-factor part by the unfrozen fraction, 1 − `relallfrozen` / `relpages`, so a table that is 90% frozen fires after roughly 2% of its rows instead of 20%, keeping visibility map bits and freezing current.
  • Do you need a restart to lower autovacuum_vacuum_scale_factor?
    No. It is a reload setting: change `postgresql.conf` and run `SELECT pg_reload_conf();`. For one table, `ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...)` takes only a `SHARE UPDATE EXCLUSIVE` lock and is used at the worker's next check.

  • Autovacuum runs on a fixed schedule every minute against every table.
  • The default scale factor of 0.2 means autovacuum starts at 20 percent table bloat on disk.
  • autovacuum_vacuum_max_threshold caps how many workers may run at once.
  • Autovacuum analyzes partitioned parents along with their partitions.
  • Insert-only tables never need vacuuming because they create no dead tuples.

Assumes PostgreSQL 18, which added autovacuum_vacuum_max_threshold (default 100,000,000) and made the insert trigger count only the unfrozen part of the table; 17 has neither.