warn-hnsw-build-memory

What triggers it

An IndexStmt whose accessMethod lowercases to hnsw, on a table whose TableStats.rowCount is at least 1_000_000, where ctx.cluster.settings.maintenanceWorkMemBytes is defined and below 1024 ** 3 bytes (1 GB). All three conditions have to hold together.

What does not

Every run without --database-url, which supplies neither the row count nor the setting (requiresDatabaseUrl is set). A server where maintenance_work_mem could not be read: undefined is treated as "do not warn", never as "assume it is small". Tables under 1,000,000 rows. IVFFlat and every non-vector access method.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It only fires on tables managed by pgvector. It needs --database-url: without a connection it has nothing to read and stays silent.

The lock, and what it blocks

No lock finding of its own, and the rule never inspects concurrent. A plain build holds SHARE and a CONCURRENTLY build SHARE UPDATE EXCLUSIVE; what this rule adds is that whichever window applies can stretch from minutes to hours purely because of a server setting the migration file never mentions.

Why it matters

pgvector builds the HNSW graph in maintenance_work_mem. While the graph fits, the build is fast; once it does not, pgvector logs "hnsw graph no longer fits into maintenance_work_mem after N tuples" and finishes the rest on a much slower path. The statement is identical either way, which is what makes this hard to catch — a build that took minutes in staging can run for hours in production purely because the setting is lower there. The setting can be raised for the session that runs the build, so this is usually the cheapest fix available.

Unsafe, and safe

-- items has 8M rows; maintenance_work_mem is 64MB
CREATE INDEX idx_items_embedding ON items
  USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

-- Raise the limits for the session that builds the index, then run the
-- CREATE INDEX in that same session.
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;

Deploy and transaction boundaries

SET maintenance_work_mem applies only to the session that issues it, so the raise has to happen in the same session as the build. max_parallel_maintenance_workers is separately capped by max_parallel_workers, so raising one alone does not necessarily add workers.

What it assumes

1,000,000 rows and 1 GB are a heuristic for the shape of the problem, not a computed graph size. The memory an HNSW graph actually needs depends on the vector dimensions and on m, and neither is derivable from the statement — the column type is never resolved and m may not even be supplied, which is MP109's remit. So the rule warns on a million rows of vector(3) that would have fitted comfortably, and stays quiet at 900,000 rows of vector(1536) that will not. maintenance_work_mem is the server's current value, which the session that actually runs the build can raise or lower without the rule knowing.

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

⚠ [MP112] WARNING (line 2)
  HNSW build "idx_items_embedding" on "items" covers 8,000,000 rows with maintenance_work_mem at 64MB. pgvector keeps the graph in that memory while it fits and drops to a much slower path once it does not. The notice reads "hnsw graph no longer fits into maintenance_work_mem after N tuples". max_parallel_maintenance_workers is 2, which also caps how much of the build can run in parallel.

  Safe alternative:
  -- Raise the limits for the session that builds the index:
  SET maintenance_work_mem = '8GB';
  SET max_parallel_maintenance_workers = 7;
  
  CREATE INDEX idx_items_embedding ON items
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 128);
  
  -- Watch the server log during the build: if the "no longer fits" notice appears,
  -- the rest of the build is on the slow path and more memory would have helped.
  
  -- max_parallel_maintenance_workers is capped by max_parallel_workers, so raise
  -- that too if you want more than a couple of workers on the build.

  Why: pgvector builds the HNSW graph in maintenance_work_mem. While the graph fits, the build is fast; once it does not, pgvector logs "hnsw graph no longer fits into maintenance_work_mem after N tuples" and finishes on a much slower path. The statement is identical either way, so a build that took minutes in staging can run for hours in production purely because the setting is lower there. maintenance_work_mem can be raised for the session that runs the build, which is the cheapest fix available.
  Docs: https://migrationpilot.dev/rules/mp112

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 MP112
CREATE INDEX idx_items_embedding ON items

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

rules:
  MP112: false

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

• MP109require-vector-index-paramssetting m and ef_construction deliberately
• MP050prefer-hnsw-over-ivfflatwhich access method to use
• MP104warn-long-index-buildbuild duration for ordinary indexes