Skip to main content

PG.L1.TRUNCATE — Emptying a table locks out every reader

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

TRUNCATE is often reached for as "a DELETE that is quick about it". It is a different operation with two properties a DELETE does not have.

It takes an ACCESS EXCLUSIVE lock, so for its duration every concurrent reader and writer waits — not just writers. And it takes no predicate: there is no WHERE clause that could have been narrower, and no partial outcome to inspect afterwards. It empties the table.

Why it is flagged even though it is fast​

Speed is what makes it attractive in a migration and is beside the point. TRUNCATE does not scan the table, so on a large table it finishes far quicker than the equivalent DELETE — and empties it just as completely. The finding is about the two things that survive the speed: an exclusive lock during a deploy, and an irreversible result.

Flagged​

DB::statement('TRUNCATE TABLE audit_scratch');

Preferred​

// Delete in bounded batches instead of emptying the table under an exclusive lock:
DB::table('audit_scratch')->where('created_at', '<', now()->subDays(30))->limit(1000)->delete();

Three things change. The table stays readable throughout. The predicate says what is being removed, so the intent is in the code rather than in somebody's memory. And a batch that goes wrong is a bounded amount of wrong.

When TRUNCATE is the right answer​

A genuinely disposable table — a scratch space rebuilt on every run, a staging table nobody reads — is a legitimate case, and the rule does not know which yours is. State it:

#[SqlensAllowDestructive('audit_scratch is rebuilt from scratch by the nightly import')]
final class ResetAuditScratch extends Migration { /* … */ }

GEN.L1.DML_WITHOUT_WHERE covers the neighboring mistake — a DELETE or UPDATE that could have carried a predicate and did not. TRUNCATE cannot carry one at all, which is why it has a rule of its own rather than being folded into that one.

Sources​

  • PostgreSQL 18 — TRUNCATE — empties a table without scanning it, under a lock that excludes every concurrent reader and writer

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.