warn-long-index-build

What triggers it

An IndexStmt on a table whose TableStats.rowCount is above 0, where the slow end of the estimated build time crosses 300 seconds. The estimate divides the row count by 125_000 rows/sec for the slow end and 2_000_000 rows/sec for the fast end, multiplying both by 3 when idx.concurrent is set — which puts the trigger at roughly 37.5M rows for a plain build and 12.5M for CONCURRENTLY.

What does not

Every run without --database-url, since the row count is the whole input (requiresDatabaseUrl is set). A rowCount of 0 or less, which is what pg_class.reltuples reports for a table that has never been analyzed. Anything whose slow end lands under 300 seconds. Nothing about the index is examined beyond concurrent, idxname and relation, so key width and access method never enter into it.

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 holds SHARE for the whole estimated window, blocking every write on the table. CONCURRENTLY swaps that for SHARE UPDATE EXCLUSIVE and blocks no writes, but holds a snapshot throughout, which stops vacuum from removing dead rows anywhere in the database — one long build can bloat every other table — and leaves an INVALID index behind if it fails or is cancelled.

Why it matters

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.

Unsafe, and safe

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

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

Deploy and transaction boundaries

CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and the SET maintenance_work_mem the rule suggests only takes effect in the session that issues it, so both have to be arranged around the migration rather than inside it.

What it assumes

The two ends of the estimate differ by a factor of 16, on purpose: the fast figure is a narrow integer key on a warm uncontended machine, the slow one a wide unique key on cloud storage under load. Key width, the real maintenance_work_mem, parallel workers and I/O decide where an actual build lands, and none of them feed the arithmetic — maintenance_work_mem and max_parallel_maintenance_workers are quoted in the message but do not move the number. The 3x multiplier for CONCURRENTLY is a rule of thumb for two table passes plus the waits between them. reltuples is itself an estimate maintained by analyze, so it can be badly stale on a table that is filling fast.

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

⚠ [MP104] WARNING (line 2)
  CREATE INDEX on "events" covers 500,000,000 rows (85.9 GB). Expect roughly 4 min to 67 min, a wide range because build speed depends on key width, memory, parallel workers, and I/O. Writes to "events" are blocked for that whole window: this build is not CONCURRENTLY.

  Safe alternative:
  -- Give the build more memory and workers for this session:
  SET maintenance_work_mem = '2GB';
  SET max_parallel_maintenance_workers = 4;
  
  -- Build without blocking writes (outside a transaction):
  CREATE INDEX CONCURRENTLY idx_events_ts ON events (...);
  
  -- Watch it while it runs:
  SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
  FROM pg_stat_progress_create_index;
  
  -- Afterwards, check nothing was left behind by a cancelled build:
  SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

  Why: Build time scales with row count, and a long build is a long exposure. A plain CREATE INDEX holds a SHARE lock that blocks every write on the table until it finishes. CONCURRENTLY does not block writes, but it makes two passes over the table and holds a snapshot throughout, which keeps vacuum from cleaning up dead rows anywhere in the database, and if it fails or is cancelled it leaves an INVALID index behind that must be dropped and rebuilt.
  Docs: https://migrationpilot.dev/rules/mp104

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 MP104
CREATE INDEX idx_events_ts ON events (created_at);

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

rules:
  MP104: false

# or keep it, and downgrade it
rules:
  MP104:
    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
• MP070warn-concurrent-index-invalidwhat a failed CONCURRENTLY build leaves behind
• MP101warn-index-on-write-hot-tablewhat the index costs after the build
• MP014large-table-ddllock duration on large tables generally