require-spatial-index

What triggers it

A CreateStmt with at least one ColumnDef whose typeName.names[].String.sval, lowercased, is in the set geometry, geography, point, line, lseg, box, path, polygon, circle — and for which no later statement looks like a matching spatial index. That second half is a raw text scan, not AST inspection: it walks ctx.allStatements from statementIndex + 1 forward and stands down if any statement uppercased contains USING GIST or USING SPGIST and, separately, if that statement raw text contains one of the spatial column names as a case-sensitive substring.

What does not

A CreateStmt followed anywhere later in the same file by a statement satisfying that two-part text test. Tables with no spatial columns. ALTER TABLE ... ADD COLUMN geom geometry, which is an AlterTableStmt and is never inspected, so a spatial column added to an existing table is never checked for an index.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

geometry and geography come from PostGIS, but point, line, lseg, box, path, polygon and circle are core PostgreSQL geometric types. The rule fires on a plain PostgreSQL schema that has never installed PostGIS, and does not check whether the extension is present.

The lock, and what it blocks

The rule takes no position on locking — it is about a missing index and the sequential scans that follow. It is worth noting the lookback direction implies the opposite of a lock concern: the whole point of catching this at CREATE TABLE time is that the table is empty, so the index can be built immediately without the CONCURRENTLY dance a later ALTER would require.

Why it matters

PostGIS geometry and geography columns need a GIST or SP-GIST index for efficient spatial queries (ST_Contains, ST_DWithin, etc.). Without one, every spatial query triggers a full sequential scan.

Unsafe, and safe

CREATE TABLE locations (
  id bigint PRIMARY KEY,
  geom geometry NOT NULL
);
-- No spatial index!

CREATE TABLE locations (
  id bigint PRIMARY KEY,
  geom geometry NOT NULL
);
CREATE INDEX CONCURRENTLY idx_locations_geom
  ON locations USING GIST (geom);

What it assumes

The suppression check is text matching and inherits every text-matching weakness. An index written in a different migration file, or in an earlier statement of this file, is invisible — the scan only runs forward. USING GIST with two spaces still matches because the test is includes, but the column-name half is case-sensitive against un-uppercased SQL, so a quoted or differently-cased column name can miss. Worse in the other direction: a GIST index on a completely different table that happens to mention a column with the same name suppresses the warning. The rule also cannot know whether the spatial column is ever queried spatially.

What the CLI prints

⚠ [MP051] WARNING (line 1)
  Table "locations" has spatial column(s) (geom) without a GIST index. Spatial queries will require full sequential scans.

  Safe alternative:
  -- Add a GIST index for efficient spatial queries:
  CREATE INDEX CONCURRENTLY idx_locations_geom ON locations USING GIST (geom);

  Why: PostGIS geometry and geography columns need a GIST or SP-GIST index for efficient spatial queries (ST_Contains, ST_DWithin, etc.). Without one, every spatial query triggers a full sequential scan. Adding the index at table creation time avoids the need for a later lock-heavy ALTER.
  Docs: https://migrationpilot.dev/rules/mp051

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.

Turning it off

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

-- migrationpilot-disable MP051
CREATE TABLE locations (

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

rules:
  MP051: false

# or keep it, and downgrade it
rules:
  MP051:
    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
• MP042require-index-nameindexes should have explicit names
• MP050prefer-hnsw-over-ivfflatuse HNSW for vector indexes