prefer-hnsw-over-ivfflat

What triggers it

An IndexStmt whose accessMethod is exactly the string ivfflat. Nothing else is examined — not the opclass, not the WITH (lists = ...) options, not the column type. Case is handled by the parser rather than the rule: an unquoted USING IVFFLAT arrives lowered to ivfflat, verified against the bundled parser.

What does not

USING hnsw, or any other access method. Any non-IndexStmt node. A quoted access method that preserves case, USING "IVFFlat", would fail the exact-string comparison, though PostgreSQL would not resolve that name anyway.

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.

The lock, and what it blocks

The rule is silent on locking, and deliberately so — MP001 covers whether the CREATE INDEX should be CONCURRENTLY. The concern here is index quality over time: IVFFlat builds its cluster centroids from whatever rows exist at build time, so recall decays as the vector distribution drifts and the only remedy is a REINDEX, which is itself a heavy operation you now have to schedule forever.

Why it matters

pgvector IVFFlat indexes need representative data at creation time to build clusters. As data changes, recall degrades and periodic REINDEX is needed. HNSW indexes build incrementally, have consistently better recall, and never need retraining.

Unsafe, and safe

CREATE INDEX idx_embeddings ON items
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

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

What it assumes

It cannot check whether the recommendation is right for this workload. IVFFlat is a legitimate choice when build time and index size matter more than recall, and the rule has no view of either the vector count or the recall target — the fixer classification says as much, which is why it is unfixable. It also does not verify that pgvector is installed or at a version that has HNSW (added in pgvector 0.5.0); it infers everything from the access-method name alone.

What the CLI prints

⚠ [MP050] WARNING (line 1)
  Index "idx_embeddings" on "items" uses IVFFlat. HNSW provides better recall without needing training data or periodic reindexing.

  Safe alternative:
  -- Use HNSW instead of IVFFlat for better recall:
  -- CREATE INDEX idx_embeddings ON items USING hnsw (embedding vector_cosine_ops);

  Why: pgvector IVFFlat indexes need enough representative data at creation time to build good clusters. As data distribution changes, recall degrades and you must REINDEX. HNSW indexes build incrementally, have consistently better recall, and never need retraining. Prefer HNSW unless you have a specific reason to use IVFFlat.
  Docs: https://migrationpilot.dev/rules/mp050

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 MP050
CREATE INDEX idx_embeddings ON items

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

rules:
  MP050: false

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