Skip to main content

PG.L7.AUTOVACUUM_OFF — A server with autovacuum switched off for every table

  • Category: performance
  • Level: 7
  • Confidence: deterministic
  • Downtime class: none — nothing here is a schema change
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

What it reports​

A server whose autovacuum setting is off, read as the value a new connection gets. The autovacuum daemon then runs for no table at all, and a table whose storage parameters say autovacuum_enabled = true does not change that: the per-table parameter can switch one table off under a running daemon, never on under a stopped one.

PG.L7.AUTOVACUUM_DISABLED reports the per-table form. This is the same state for every table at once.

What it costs​

The estimates stop meaning anything. Dead rows are reclaimed by nobody, so every table grows and its file size says less and less about the rows a rewrite would copy, which is what every time and space statement about a deploy rests on. Row estimates go stale for the same reason: autovacuum is what triggers most ANALYZE runs.

The server takes it back anyway. Turning autovacuum off does not stop the freeze horizon. A table that crosses autovacuum_freeze_max_age is vacuumed regardless, by a worker that does not yield to a conflicting lock request the way an ordinary one does. So the setting does not avoid the vacuum: it defers it to a moment nobody chose and makes it larger.

Flagged​

# postgresql.conf
autovacuum = off

Preferred​

Leave it on, and tune the tables that need it rather than stopping the daemon for all of them:

# postgresql.conf
autovacuum = on
DB::statement('ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02)');

False positives​

A server maintained entirely by hand. A team that runs VACUUM (ANALYZE) on every table on its own schedule has made a defensible, if rare, call. This check cannot see that schedule, so the answer is an ignore entry with the reason beside it.

Unreadable is not off​

A role that may not read the setting gets undetermined with a named reason, never a pass. "We could not read it" and "it is on" are different sentences.

Why there is no MySQL twin​

InnoDB's purge cannot be switched off, and innodb_stats_auto_recalc decides whether optimizer statistics are refreshed, not whether dead rows are reclaimed.

Sources​

The fix material this rule carries​

A finding from this rule carries a payload whose strategy is none: this rule has looked, and there is no safe standard sequence. That is a conclusion rather than an omission — a finding with no payload at all says only that nobody wrote one.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.