Skip to main content

PG.L2.CLUSTER — CLUSTER locks the table for the whole rewrite

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

CLUSTER writes the whole table into a new file in the order of an index, 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. It also needs disk for a second copy of the table and its indexes until it is done. The order it writes is not kept: rows inserted or updated afterwards go wherever there is room, so the statement has to run again to keep its effect.

Measured on PostgreSQL 18.0, with the locks read from pg_locks inside the statement's own transaction: CLUSTER orders USING orders_n_idx held AccessExclusiveLock on the table and on each of its indexes.

Flagged​

DB::statement('CLUSTER orders USING orders_created_at_index');

Preferred​

// Reorder the table with pg_repack outside the deploy. A migration can refresh
// the planner's statistics without a lock that blocks anyone:
DB::statement('ANALYZE orders');

pg_repack writes a table in the order of its clustering index, or of the columns given with --order-by, while it stays in use, and holds the exclusive lock only for a moment at the start and at the end. If it has to be CLUSTER, run it outside the deploy, in a maintenance window.

What is read​

CLUSTER <table>, with USING <index>, with VERBOSE in either spelling, and the older CLUSTER <index> ON <table>. A CLUSTER without a table re-clusters every table that was clustered before, which no target can name, and PostgreSQL refuses it inside a transaction block: its migration stays undetermined. VACUUM FULL is the same kind of rewrite and is reported by PG.L2.VACUUM_FULL.

Set a lock timeout regardless​

Before the rewrite starts, CLUSTER 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.

Sources​

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.