warn-index-on-write-hot-table

What It Detects

New index on a table with heavy write traffic. Every write pays for the extra index.

Why It's Dangerous

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.

Bad Example

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

Good Example

-- 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;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP101: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP101:
    severity: warning