warn-toast-bloat-risk

What triggers it

An UpdateStmt whose SQL text either calls one of a fixed list of JSON/JSONB/binary functions (jsonb_set, to_jsonb, encode, and others) or assigns, in its SET clause, to a column whose name matches one of a fixed list of typically TOAST-eligible names (metadata, payload, body, content, data, and more).

What does not

UPDATE statements whose SET clause neither calls a matched TOAST-related function nor assigns to a column on the hardcoded name list — updating an id or status column, for instance, or updating an unlisted text column with plain string concatenation.

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 — the rule isn't about locking, it's about storage. Each UPDATE to a TOAST-stored column writes a new TOAST chunk and marks the old one dead, and dead chunks are only reclaimed by VACUUM, not automatically.

Why it matters

When you UPDATE a row with TOAST-stored columns, PostgreSQL creates new TOAST chunks and marks old chunks as dead. Dead chunks are only reclaimed by VACUUM, causing tables to grow many times their logical size.

The operation, and the mitigation

UPDATE users SET metadata = jsonb_set(metadata, '{key}', '"value"');

UPDATE users SET metadata = jsonb_set(metadata, '{key}', '"value"');
-- Run VACUUM after bulk TOAST-column updates:
VACUUM (VERBOSE) users;

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

Detection depends entirely on the column matching one of a fixed list of common names or the SET clause calling a listed function — a TOAST-eligible column with an unlisted name (say, bio) updated with plain assignment would be missed entirely, while a short text column that's never actually TOASTed could still match the name pattern and produce a false positive.

What the CLI prints

⚠ [MP075] WARNING (line 1)
  UPDATE on "users" with JSONB/JSON function calls. TOAST chunk rewrites cause table bloat until VACUUM runs. Run VACUUM after bulk updates.

  Safe alternative:
  -- After bulk TOAST-column updates, reclaim space:
  UPDATE users SET metadata = jsonb_set(metadata, '{key}', '"value"')
  
  -- Run VACUUM to reclaim dead TOAST chunks:
  VACUUM (VERBOSE) users;

  Why: When you UPDATE a row with TOAST-stored columns, PostgreSQL creates new TOAST chunks and marks old chunks as dead. Dead chunks are only reclaimed by VACUUM. Bulk updates on TOAST-heavy columns can cause tables to grow many times their logical size, degrading query performance and exhausting disk space.
  Docs: https://migrationpilot.dev/rules/mp075

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 MP075
UPDATE users SET metadata = jsonb_set(metadata, '{key}', '"value"');

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

rules:
  MP075: false

# or keep it, and downgrade it
rules:
  MP075:
    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.