prefer-timescale-drop-chunks

What triggers it

A DeleteStmt whose target table (via dmlTargetTable()) resolves through ctx.tableExtensions or lookupTableExtensions() to a hypertable with a known timeColumn, and whose whereClause contains an A_Expr whose operator name is <, <=, > or >= with a ColumnRef on either side whose last field matches that time column. The search recurses through the whole clause tree, so the comparison can sit inside an AND or OR.

What does not

Every run without --database-url — both hypertable membership and the time dimension come from the TimescaleDB catalog (requiresDatabaseUrl is set). A hypertable whose timeColumn the server does not report: the rule stays silent rather than guess which column is the dimension. A DELETE with no whereClause, which is MP067's. A WHERE that filters only on something else, such as device_id. Non-hypertables.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It only fires on tables managed by TimescaleDB. It needs --database-url: without a connection it has nothing to read and stays silent.

The lock, and what it blocks

DELETE takes ROW EXCLUSIVE on the chunks it touches, which blocks nothing ordinary traffic does — and that is exactly the point of the rule. Nothing is blocked; this is simply the most expensive way to remove the rows, a WAL record and a dead tuple each, with the space not returning until vacuum has been through. drop_chunks() takes ACCESS EXCLUSIVE on each chunk it removes, briefly, and unlinks the files.

Why it matters

Deleting old data row by row is the most expensive way to do the job: a WAL record per row, a dead tuple per row for vacuum to clean up later, and bloat that stays until the vacuum finishes. On a hypertable that work is also unnecessary — chunks are already partitioned on the time column, so the rows in a retention window are whole chunks, and drop_chunks() drops them as tables. No per-row work, and the space comes back immediately rather than after a vacuum.

Unsafe, and safe

DELETE FROM metrics WHERE time < now() - interval '30 days';

SELECT drop_chunks('metrics', older_than => INTERVAL '30 days');

-- drop_chunks only removes chunks whose entire range falls outside the
-- bound, so rows in a partially-covered chunk survive.

What it assumes

The operator test is purely structural and never looks at direction or bound. Any <, <=, > or >= against the time column counts, so WHERE time > now() - interval '1 hour' — a targeted delete of recent rows, not a retention sweep — is flagged as readily as a retention bound. BETWEEN parses as a different A_Expr with the name BETWEEN and is missed, as is =. The rule also has no idea whether the range lines up with chunk boundaries, and drop_chunks() only removes chunks whose entire range falls outside the bound, so the suggested replacement is not row-for-row equivalent at the boundary chunk.

This rule reads live catalogue state, so it says nothing at all without --database-url. That is the trade: no connection, no guess.

What the CLI prints

⚠ [MP106] WARNING (line 1)
  DELETE on hypertable "metrics" (420 chunks) filters on "time", its time dimension, so this removes whole chunks the slow way, one row at a time, leaving dead tuples for vacuum. drop_chunks() unlinks the chunks instead.

  Safe alternative:
  -- Drop the chunks that fall entirely outside the retention window:
  SELECT drop_chunks('metrics', older_than => INTERVAL '30 days');
  
  -- drop_chunks only removes chunks whose whole range is outside the bound, so
  -- data inside a partially-covered chunk stays. Check what would go first:
  SELECT chunk_name, range_start, range_end
  FROM timescaledb_information.chunks
  WHERE hypertable_name = 'metrics'
  ORDER BY range_end;
  
  -- For an ongoing policy rather than a one-off:
  SELECT add_retention_policy('metrics', INTERVAL '30 days');

  Why: Deleting old data from a hypertable row by row does the most expensive possible version of the job: a WAL record per row, a dead tuple per row for vacuum to clean up later, and bloat that survives until the vacuum finishes. Chunks are already partitioned on the time column, so the rows in a retention window are whole chunks. drop_chunks() drops those chunks as tables: no per-row work, no WAL per row, and the space comes back immediately.
  Docs: https://migrationpilot.dev/rules/mp106

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. The catalogue figures come from the production context this rule documents.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP106
DELETE FROM metrics WHERE time < now() - interval '30 days';

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

rules:
  MP106: false

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

Related rules

• MP105warn-timescale-hypertable-ddlDDL against a hypertable
• MP067warn-backfill-no-batchinga WHERE-less DELETE, flagged without a database
• MP103warn-replication-lag-riskwhat a per-row DELETE sends to replicas