warn-autovacuum-disabled

What triggers it

A CreateStmt with a WITH (autovacuum_enabled = false) storage option, or the AT_SetRelOptions subtype of ALTER TABLE ... SET (autovacuum_enabled = false) — both checked by scanning the option list's DefElem entries for defname === 'autovacuum_enabled' with a string value of 'false'.

What does not

CREATE TABLE or ALTER TABLE ... SET with any other storage option, or with autovacuum_enabled set to true or anything other than the literal string 'false', and any AlterTableCmd subtype other than AT_SetRelOptions.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

No lock is named in the source — the concern isn't blocking, it's what happens after: without autovacuum, dead tuples build up and the table's transaction id counter stops advancing, pushing it toward a wraparound shutdown over time.

Why it matters

Autovacuum prevents table bloat by reclaiming dead tuples, and prevents transaction ID wraparound — which can freeze the entire database. Disabling autovacuum is occasionally justified for temporary bulk-load staging tables, but is dangerous for any table that serves production traffic.

The operation, and the mitigation

CREATE TABLE staging_data (id INT)
  WITH (autovacuum_enabled = false);

-- Create with autovacuum disabled for bulk load, then re-enable
CREATE TABLE staging_data (id INT)
  WITH (autovacuum_enabled = false);
-- After bulk load:
ALTER TABLE staging_data SET (autovacuum_enabled = true);

This operation is irreversible, so there is no syntax that makes it safe. The second block is what care looks like — and MigrationPilot still flags it. The mitigation is process: confirm nothing reads the object, keep a way back, and do it in a window where you can watch.

What it assumes

The rule fires the same way whether this is a permanent production table or a short-lived staging table for a bulk load, where disabling autovacuum temporarily is a common, legitimate optimization — it only sees that the option was set, not the table's purpose.

What the CLI prints

⚠ [MP066] WARNING (line 1)
  Table "staging_data" has autovacuum disabled. This causes table bloat and risks transaction ID wraparound. Re-enable autovacuum after bulk loading.

  Why: Autovacuum prevents table bloat by reclaiming dead tuples, and prevents transaction ID wraparound, which can freeze the entire database. Disabling autovacuum is occasionally justified for temporary bulk-load staging tables, but is dangerous for any table that serves production traffic.
  Docs: https://migrationpilot.dev/rules/mp066

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP066
CREATE TABLE staging_data (id INT)

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP066: false

# or keep it, and downgrade it
rules:
  MP066:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.