Skip to main content

PG.L7.AUTOVACUUM_DISABLED — A table somebody switched autovacuum off for

  • 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 table whose storage parameters carry autovacuum_enabled = false, read out of pg_class.reloptions.

Three states, and only one of them is this finding:

The parameter is…MeaningReported?
falsesomebody switched it off for this tableyes
truesomebody switched it on explicitlyno
absentthe table runs under the cluster's setting — the ordinary caseno

The third row is why the value is kept as the server's own spelling rather than cast to a boolean: a boolean has no third value, and the third value is the common one. Reading "nobody said" as "somebody said no" would report every table in the schema.

Two consequences, and the second is not optional​

The estimates stop meaning anything. Without autovacuum the table accumulates dead rows, so its file size says the least about the rows a rewrite would actually copy — and the size is what every time and space statement about a deploy rests on. The row estimate goes stale for the same reason: autovacuum is what triggers most ANALYZE runs, so reltuples stops being refreshed along with the space.

The server takes it back anyway. Turning autovacuum off does not stop the freeze horizon. When the table crosses autovacuum_freeze_max_age, PostgreSQL starts an anti-wraparound vacuum on it regardless of this parameter — and that worker 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 the one that eventually comes larger. DEPLOY.PREFLIGHT.FREEZE_HORIZON is the deploy-time half of the same fact: it reports the table this is about to happen to, while a deploy is waiting.

Flagged​

Schema::table('events', function (Blueprint $table) {
    DB::statement('ALTER TABLE events SET (autovacuum_enabled = false)');
});

Preferred​

Leave it on, and tune instead of disabling where the default is genuinely wrong for the table:

DB::statement('ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02)');

A scale factor says vacuum this one more eagerly than the rest; enabled = false says never, until the server insists. The first is a decision about this table, the second is a decision about a moment somebody else will pick.

False positives, and there is a real one​

A table maintained by hand is a legitimate shape. A table bulk-loaded on a schedule is vacuumed better at a chosen moment than at an arbitrary one, and a team that runs VACUUM (ANALYZE) in its own window has made a defensible call.

This check cannot see that schedule — it is a cron, a job, an Ansible task, none of which are in the database. So the answer is an ignore entry with the reason beside it, not a heuristic in the rule guessing at intent from a table's name or size. A guess would be wrong on exactly the schemas that thought about this hardest.

What it does not claim​

  • That the table is bloated. That is a measurement this rule does not make; it reports the setting, and what follows from it depends on how much the table is written to.
  • That turning it back on is free. The first autovacuum after a long pause is the large one — which is the argument for turning it on at a moment you pick rather than leaving it off until the server picks one.
  • Anything about per-table thresholds. A table whose autovacuum_vacuum_threshold or autovacuum_vacuum_scale_factor is set so high that it never fires is the same state written more quietly, and this rule does not read those. It is the narrower, unambiguous case on purpose.

Why there is no MySQL twin​

InnoDB has no per-table equivalent. Its purge and its adaptive flushing are server-wide, and the nearest table-level knob — STATS_AUTO_RECALC — decides whether the optimizer's statistics are refreshed, not whether dead rows are reclaimed. A rule registered there would be a different finding wearing this one's name.

Sources​

The deploy-time half​

This rule reports every such table in the database, which is the right shape for an audit and the wrong one for a deploy: the list is read once, and somebody planning a migration has no reason to go back to it. DEPLOY.PREFLIGHT.AUTOVACUUM_DISABLED asks the narrower question at the moment it matters — is one of the tables this run touches in that state — and adds the consequence an audit cannot state: that the report's own size and duration figures for that table are the least trustworthy ones in it.