warn-long-index-build

What It Detects

Index build on a table large enough that the build runs for minutes or hours.

Why It's Dangerous

Build time scales with row count, and a long build is a long exposure to whatever the build costs you. A plain CREATE INDEX holds a SHARE lock, blocking every write on the table until it finishes. CONCURRENTLY does not block writes, but it makes two passes and holds a snapshot the whole time, which stops vacuum from cleaning up dead rows anywhere in the database — a long build on one table can bloat every other table. A cancelled concurrent build also leaves an INVALID index behind.

Bad Example

-- events has 500M rows
CREATE INDEX idx_events_ts ON events (created_at);

Good Example

-- Give the build room first, then run it CONCURRENTLY in its own step
-- and watch it rather than assuming it finished.
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
FROM pg_stat_progress_create_index;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP104: false

Or change its severity:

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