Skip to main content

PG.L2.VACUUM_FULL — VACUUM FULL locks the table for the whole rewrite

  • Category: safety
  • Level: 2
  • Confidence: deterministic
  • Downtime class: rewrite
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

VACUUM FULL writes the whole table into a new file, rebuilds every index on it, and holds an ACCESS EXCLUSIVE lock from the start of the rewrite to its end. That lock excludes everything, readers included, so the table is unavailable for as long as the rewrite runs, and the run grows with the table rather than with the space it gives back. It also needs disk for a second copy of the table and its indexes until it is done. Named without a table, it does the same to every table in the database, one after the other.

Measured on PostgreSQL 18.0, with the lock read from pg_locks while the statement ran: both VACUUM FULL orders and VACUUM (FULL, ANALYZE) orders held AccessExclusiveLock on the table.

Flagged​

DB::statement('VACUUM FULL orders');

Preferred​

// Marks dead rows for reuse under a lock that blocks no read and no write.
DB::statement('VACUUM (ANALYZE) orders');

The plain VACUUM takes SHARE UPDATE EXCLUSIVE, which no read and no write waits behind. It keeps the space inside the table for new rows instead of giving it back to the operating system, which is the trade it makes. When the space has to go back, pg_repack and pg_squeeze rewrite the table online and hold the exclusive lock only for a moment at the start and at the end. If it has to be VACUUM FULL, run it outside the deploy, in a maintenance window.

Inside a transaction it never runs​

PostgreSQL refuses VACUUM inside a transaction block with error 25001, measured on 18.0, and Laravel runs a migration inside one by default. In such a migration the statement aborts the deploy before it takes the lock, which PG.L3.VACUUM_IN_TRANSACTION reports. A VACUUM FULL there carries both findings. public $withinTransaction = false; lets it run, and brings the lock with it.

Set a lock timeout regardless​

Before the rewrite starts, VACUUM FULL waits for its lock behind every transaction that touches the table, and everything that arrives after it waits behind it. PG.L3.MISSING_LOCK_TIMEOUT reports a migration that leaves that wait unbounded.

False positives​

VACUUM (FULL false). The rule reads the word FULL, not its value, so an option list that switches the rewrite off is reported as well. Write the plain VACUUM instead.

Sources​

  • PostgreSQL 18 — VACUUM — VACUUM FULL rewrites the table into a new file under an ACCESS EXCLUSIVE lock, the plain form runs beside ordinary reads and writes, and neither runs inside a transaction block
  • PostgreSQL 18 — Explicit Locking — the lock modes: ACCESS EXCLUSIVE for VACUUM FULL, SHARE UPDATE EXCLUSIVE for the plain VACUUM

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.