Skip to main content

PG.L7.AUTOVACUUM_COST_PER_WORKER — More autovacuum workers than the cost limit they share pays 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 server that may run more autovacuum workers than the default three, on a cost limit that does not pay for them. autovacuum_vacuum_cost_limit is not a limit per worker: PostgreSQL divides it among the workers running at the same time, so that their limits never add up to more than it. Each worker's share is held against the share the defaults give, a limit of 200 split three ways.

What it costs​

More workers, no more throughput. A server that raises autovacuum_max_workers and leaves the limit alone gives each worker a smaller share. Every one of them reaches its limit sooner and sleeps, so each single vacuum takes longer, and the extra workers add nothing.

A vacuum that falls behind ends where nobody wants it. Dead rows pile up on the tables it reaches late, and a table that crosses autovacuum_freeze_max_age gets the anti-wraparound vacuum, which does not yield to a conflicting lock request. That is the vacuum DEPLOY.PREFLIGHT.FREEZE_HORIZON warns about before a deploy.

What is compared​

  • The workers that can run: autovacuum_max_workers, capped by autovacuum_worker_slots, the pool PostgreSQL 18 takes the workers from. A setting above the pool has no effect.
  • The limit they share: autovacuum_vacuum_cost_limit, or vacuum_cost_limit where it is -1, which is its default. The -1 is resolved rather than read: a server that raised vacuum_cost_limit is not blamed for it, and ten workers on an explicit 200 are not missed.

All three are read as the value a new connection gets, reset_val, beside each other in one statement. Three workers or fewer are not this rule's subject: three workers on a lower limit is a slower vacuum chosen on purpose, not one diluted by workers nobody paid for.

Flagged​

# postgresql.conf
autovacuum_max_workers = 5
# autovacuum_vacuum_cost_limit stays -1, so the five share vacuum_cost_limit = 200

Preferred​

Raise the shared limit with the workers, to 200 split three ways per worker:

# postgresql.conf
autovacuum_max_workers = 5
autovacuum_vacuum_cost_limit = 334

Both settings take a reload. autovacuum_worker_slots, the pool, takes a restart.

What it does not model​

A table that sets its own autovacuum_vacuum_cost_limit or autovacuum_vacuum_cost_delay storage parameter is left out of the split, so its worker keeps its own budget. The rule judges the server's settings and does not read every table's options.

It also counts the workers the server may run, not the ones running at the moment. A server whose tables rarely need a vacuum may never start more than one worker, and then nothing is split. Such a server is suppressed through the baseline once, with the reason recorded.

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.