require-vector-index-params

What triggers it

An IndexStmt whose accessMethod lowercases to hnsw or ivfflat, where the DefElem names collected from options are missing at least one expected parameter — m and ef_construction for HNSW, lists for IVFFlat. Supplying only one of the two HNSW parameters still fires, and the message names whichever is missing.

What does not

Any other access method, so B-tree, GIN, GiST and BRIN indexes are untouched. An HNSW index that sets both m and ef_construction, or an IVFFlat index that sets lists. The parameter values are never inspected, only the presence of the keys, so WITH (lists = 1) silences it. This is the one rule in the MP100 block with no requiresDatabaseUrl, so it never stands down for want of a connection.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It only fires on tables managed by pgvector. It works on the SQL text alone — no database connection needed.

Works from the SQL text alone — the absence of a WITH clause is visible without a database. Production context only makes the IVFFlat message more specific, turning the formula into a number.

The lock, and what it blocks

No lock finding. This is about build-time parameters rather than how the build is taken — MP104 and MP112 cover duration, MP001 covers CONCURRENTLY. The reason it belongs in migration review is that these parameters cannot be altered afterwards: changing m, ef_construction or lists means dropping and rebuilding the whole index, a full pass over every row in the table.

Why it matters

These are build-time decisions and they are expensive to revisit. For HNSW, m and ef_construction set the shape of the graph and therefore the recall ceiling, and the defaults are conservative. For IVFFlat, lists decides how the vectors are clustered, and pgvector ties the right value to the row count — rows / 1000 up to a million rows, sqrt(rows) beyond that — so a default that ignores the row count is wrong at scale in one direction or the other. Changing any of them later means rebuilding the whole index.

Unsafe, and safe

CREATE INDEX idx_items_embedding ON items USING hnsw (embedding vector_cosine_ops);

CREATE INDEX idx_docs_embedding ON documents USING ivfflat (embedding vector_l2_ops);

CREATE INDEX idx_items_embedding ON items
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 128);

-- lists sized to the data: 4M rows -> sqrt(4,000,000) = 2,000
CREATE INDEX idx_docs_embedding ON documents
  USING ivfflat (embedding vector_l2_ops)
  WITH (lists = 2000);

What it assumes

The rule cannot know your recall target, your vector dimensions, or how large the table will eventually be, so it asks for a deliberate choice rather than checking that the choice is good — WITH (m = 1, ef_construction = 1) passes. The IVFFlat sizing number appears only when ctx.tableStats.rowCount is available, and it applies pgvector's own formula (rows / 1000 up to 1,000,000 rows, sqrt(rows) above) to the row count today, which is the wrong basis for a table still filling up. It also fires alongside MP050 on an IVFFlat index, which is intentional rather than a duplicate.

What the CLI prints

⚠ [MP109] WARNING (line 1)
  HNSW index "idx_items_embedding" on "items" sets no m or ef_construction, so it builds with pgvector's defaults (m = 16, ef_construction = 64). Those choose the graph's recall ceiling, and changing them later means a full rebuild.

  Safe alternative:
  -- Set the graph parameters deliberately:
  CREATE INDEX idx_items_embedding ON items
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 128);
  
  -- Higher ef_construction means better recall for a slower build; m raises both
  -- recall and index size. Measure recall against a known-good result set before
  -- committing to the values, because changing them means rebuilding.
  
  -- ef_search is a query-time knob, not a build one. Tune it separately:
  SET hnsw.ef_search = 100;

  Why: pgvector index parameters are build-time decisions. For HNSW, m and ef_construction set the shape of the graph and therefore the recall ceiling; the defaults (m = 16, ef_construction = 64) are conservative and are frequently too low for production recall targets. For IVFFlat, lists decides how the vectors are clustered, and pgvector ties the right value to the row count: rows / 1000 up to a million rows, sqrt(rows) beyond that. Changing any of them later means rebuilding the whole index, which on a vector table is one of the most expensive builds in PostgreSQL.
  Docs: https://migrationpilot.dev/rules/mp109

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 MP109
CREATE INDEX idx_items_embedding ON items USING hnsw (embedding vector_cosine_ops);

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

rules:
  MP109: false

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

• MP050prefer-hnsw-over-ivfflatwhich access method to use in the first place
• MP112warn-hnsw-build-memorywhat an HNSW build needs to run fast
• MP104warn-long-index-buildbuild duration on large tables