Skip to main content

DEPLOY.PREFLIGHT.AUTOVACUUM_DISABLED — A table this deploy touches has autovacuum switched off

  • Category: safety
  • Severity: medium, fixed. It does not stop a deploy, so reporting it beside a finding that does would teach a reader to discount both — but it changes what every other number in the report is worth for that table, which is more than housekeeping.
  • Level: 0
  • Downtime class: none. This check reports on the trustworthiness of an estimate and on a vacuum that has not started; neither is a claim about how long the deploy takes.
  • Stability: stable
  • Suites: deploy
  • Applies to: PostgreSQL. InnoDB has no per-table equivalent — STATS_AUTO_RECALC decides whether optimizer statistics are refreshed, not whether dead rows are reclaimed, so reading it here would answer a different question under this id.

What it reports​

A table the pending run touches that carries autovacuum_enabled = false in its reloptions:

ALTER TABLE orders SET (autovacuum_enabled = false);

Why a deploy cares, and the first reason is about the report itself​

The run's own estimates stand on sand. A table without autovacuum keeps its dead rows. Its file size therefore stops predicting what a rewrite would copy, and its row estimate goes stale with it — autovacuum is what triggers most ANALYZE runs. Every duration and every size this report states for that table is derived from exactly those two numbers, so they are quietly the least trustworthy figures in it. That is a statement about the report, which is why it belongs in the report rather than only in an audit somebody read months ago.

And the vacuum arrives anyway. Switching autovacuum off defers rather than avoids. Once the table crosses autovacuum_freeze_max_age the server starts an anti-wraparound worker on it regardless, that worker does not yield to a lock request, and by then there is more to do. DEPLOY.PREFLIGHT.FREEZE_HORIZON measures how close that is; this check says why the table got there.

Why this exists beside the audit rule​

PG.L7.AUTOVACUUM_DISABLED reports every such table in the database. That 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. This check asks the narrower question at the moment it matters — is one of the tables this run touches in that state — and that is the only form of the question a deploy report can act on.

Which tables it looks at​

Every table the pending statements name as their subject, whatever they do to it. That is deliberately wider than the lock-shaped checks beside it: a stale row estimate makes the report's numbers untrustworthy for any statement whose cost it estimates, not only for one that takes an exclusive lock.

The targets come from the statements' own resolved targets rather than from re-parsing the SQL here. A second classification is free to disagree with the one the report is built on.

The read is three-valued and only one value is reported​

false is the finding. true is somebody being deliberate the other way. Absent — the ordinary case — means the table runs under the cluster's setting. The filter is in SQL so the third state never needs a representation in code: a boolean has no room for it, and the absent case is the common one.

False positives​

A table vacuumed by hand in a window somebody chose is a legitimate arrangement, and nothing in the catalog separates it from a setting nobody revisited. This check cannot see the cron, so it does not try to. Exempt it with an ignore entry and write the reason beside it:

'ignore' => [
    'DEPLOY.PREFLIGHT.AUTOVACUUM_DISABLED' => [
        // Bulk-loaded nightly and vacuumed at 03:10 by ops/vacuum-events.sh; autovacuum mid-load
        // doubled the write time. Revisit when the loader moves to COPY.
        'public.events',
    ],
],

A heuristic here guessing at intent from a name or a size would silence the common case to spare the rare one, and it would do so without anybody writing down why.

What it does NOT read​

Per-table thresholds — autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor — tuned so high that autovacuum never fires in practice. That is the same state in a quieter form, and whether it belongs under this id or under one of its own is an open question rather than an omission.

Remedy​

If the setting has outlived its reason, put the table back under the cluster's policy:

ALTER TABLE orders RESET (autovacuum_enabled);

RESET rather than SET (autovacuum_enabled = true): resetting returns the table to whatever the cluster decides, while setting it true pins it against a future change to that policy.

Before a deploy whose estimates matter, refresh the statistics by hand so the figures in the report mean something:

ANALYZE orders;