warn-index-on-write-hot-table

What triggers it

An IndexStmt on a table for which the catalog returned TableFacts, where write traffic clears one of two bars: writesPerSecond() — (inserts + updates + deletes) / windowSeconds — at 50 or above, or, when windowSeconds is undefined, a raw total of 5,000,000 row writes since the counters were last reset. idx.concurrent only chooses the closing sentence of the message.

What does not

Any run without --database-url: no ctx.tableFacts means an immediate null (requiresDatabaseUrl is set). A table below 50 writes/sec is silent regardless of how large the absolute counters are, because the rate branch wins whenever windowSeconds is known. Nothing about the index itself is checked — not the access method, not the key columns, not whether the table is large.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It needs --database-url: without a connection it has nothing to read and stays silent.

The lock, and what it blocks

A plain CREATE INDEX takes SHARE, which blocks every INSERT, UPDATE and DELETE on the table until the build finishes — on the busiest table in the system by construction, since write traffic is what the rule selected for. CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE instead and blocks no writes, but has to track everything committed while it runs, so it takes longer here than it would anywhere else.

Why it matters

An index is not free once it is built. Every INSERT and DELETE maintains it, and an UPDATE that touches an indexed column loses the heap-only-tuple optimisation, so it writes a new index entry too. The build is also at its most disruptive here: a plain CREATE INDEX blocks writes for its whole duration, and CONCURRENTLY has to keep up with everything committed while it runs. Write rates come from pg_stat_user_tables, so this rule only fires with --database-url.

Unsafe, and safe

-- events takes ~120 writes/sec in production
CREATE INDEX idx_events_type ON events (event_type);

-- On a write-hot table, confirm the indexes it already has earn their
-- upkeep before adding another — then build outside a transaction with
-- CREATE INDEX CONCURRENTLY.
SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'events'
ORDER BY idx_scan;

Deploy and transaction boundaries

CREATE INDEX CONCURRENTLY, the build this rule points at, cannot run inside a transaction block, so it has to be its own migration or run outside the tool's transaction wrapper.

What it assumes

50 writes/sec is a fixed constant, not a percentile of this server. A machine that absorbs 500 writes/sec comfortably and one saturated at 40 are judged against the same number. The counters are cumulative since the last stats reset and the window comes from pg_stat_database.stats_reset, so a batch job that ran once months ago is averaged in as steady traffic, and a table that only became hot yesterday looks quiet. The rule measures the cost side only: it has no way to know what the index is worth, and it cannot tell whether the indexed column is one that UPDATEs actually touch, which is what decides the heap-only-tuple penalty.

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

⚠ [MP101] WARNING (line 2)
  "events" is taking about 117 writes/sec (10,100,000 row writes over the last 24.0 h): 8,000,000 inserts, 2,000,000 updates, 100,000 deletes. Adding "idx_events_type" makes every one of those writes maintain another index. This build is not CONCURRENTLY, so writes are blocked on the table until it finishes.

  Safe alternative:
  -- Build without blocking writes, outside a transaction:
  CREATE INDEX CONCURRENTLY idx_events_type ON events (...);
  
  -- Then confirm the index earns the write overhead it costs:
  SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
  WHERE relname = 'events'
  ORDER BY idx_scan;
  
  -- Indexes that never appear in idx_scan are pure write tax. Drop them.

  Why: An index is not free after it is built. Every INSERT and DELETE maintains it, and an UPDATE that touches an indexed column loses the heap-only-tuple optimisation, so it writes a new index entry as well. On a write-hot table that shows up as higher latency on the write path and more WAL. The build is also slower and more disruptive here than anywhere else: a plain CREATE INDEX blocks writes for its whole duration, and CONCURRENTLY has to keep up with everything committed while it runs.
  Docs: https://migrationpilot.dev/rules/mp101

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 MP101
CREATE INDEX idx_events_type ON events (event_type);

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

rules:
  MP101: false

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

• MP001require-concurrent-indexCREATE INDEX must use CONCURRENTLY
• MP013high-traffic-table-ddlquery volume rather than write volume
• MP100warn-redundant-indexan index that costs writes and buys nothing
• MP104warn-long-index-buildhow long the build itself will take