Agent Skills: pgvector Knowledge Patch

pgvector Knowledge Patch

Use this skill when changing pgvector queries, approximate indexes, extension builds, or deployments. Start with the compatibility and correctness notes below, then open the topic reference that matches the work.

Reference index

| Reference | Topics | | --- | --- | | references/iterative-scans.md | Filtered HNSW and IVFFlat searches, scan modes and limits, exact reordering, planner choices | | references/indexing-and-storage.md | Sparse-vector limits, array casts, index omissions, 32-bit IVFFlat safety, HNSW maintenance | | references/deployment.md | PostgreSQL compatibility, release artifacts, portable builds, container memory, Windows, hosted deployments |

Breaking compatibility change

PostgreSQL 12 is unsupported

Do not adopt the pgvector 0.8 release line on PostgreSQL 12. Upgrade the database server to PostgreSQL 13 or newer first.

This affects extension upgrades as well as new installations: verify the server version before changing the installed extension package or running ALTER EXTENSION vector UPDATE.

See references/deployment.md for supported artifact and platform combinations.

Apply the 32-bit IVFFlat fix before building

pgvector 0.8.6 fixes a buffer overflow in IVFFlat index builds on 32-bit systems. Upgrade before creating or rebuilding an IVFFlat index on such a system.

Treat this as a build-safety requirement, not a query-tuning change. Existing scan settings do not mitigate the faulty build path.

See references/indexing-and-storage.md for the related array-to-sparsevec enforcement change.

Use iterative scans for selective filters

Approximate indexes gather candidates before PostgreSQL applies ordinary filters. A selective WHERE clause can therefore leave fewer rows than the query's LIMIT. Iterative scans continue through the index until enough matching rows are found or a scan limit is reached.

HNSW modes

HNSW iterative scanning is off by default. Select a mode for the session:

| Setting | Behavior | | --- | --- | | off | Use the initial approximate scan only | | strict_order | Continue scanning while preserving exact distance order | | relaxed_order | Continue scanning with looser ordering for faster scans |

SET hnsw.iterative_scan = 'strict_order';

SELECT id, embedding <=> '[1,2,3]' AS distance
FROM items
WHERE category = 1
ORDER BY embedding <=> '[1,2,3]'
LIMIT 20;

Keep the distance ORDER BY immediately before LIMIT; iterative scanning depends on this query shape.

If filtering still prevents the query from filling its limit, tune these controls:

| Setting | Default | Effect | | --- | ---: | --- | | hnsw.max_scan_tuples | 20000 | Maximum tuples visited by an iterative scan | | hnsw.scan_mem_multiplier | 1 | Scan-memory cap as a multiple of work_mem |

IVFFlat mode

Enable ivfflat.iterative_scan when an initial filtered IVFFlat scan returns too few rows. The scan continues until it fills the result limit or reaches ivfflat.max_probes.

Re-sort relaxed HNSW results

When final ordering must be exact, retrieve a wider candidate set in a subquery and sort it again. An expression such as distance * 1 forces the outer sort.

SELECT *
FROM (
  SELECT id, embedding <=> '[1,2,3]' AS distance
  FROM items
  WHERE category = 1
  ORDER BY embedding <=> '[1,2,3]'
  LIMIT 20
) AS candidates
ORDER BY distance * 1;

See references/iterative-scans.md for the full filtered-search workflow and planner checks.

Verify the chosen query plan

Filtered-search costing can make a sequential scan or a conventional index cheaper than HNSW. Do not infer index use merely from the presence of a vector index. Run EXPLAIN on the real query shape and inspect the chosen plan.

Before increasing iterative-scan limits, distinguish a planner choice from an approximate scan that stopped too early.

Respect sparse-vector limits

PostgreSQL arrays can be cast directly to sparsevec:

SELECT ARRAY[1, 0, 2]::sparsevec;

Current casts enforce the sparse type's nonzero-element limit. Code that once used an oversized array cast to bypass that constraint must instead reduce or partition the data.

Stored sparse vectors can contain up to 16,000 nonzero elements, while an HNSW index supports at most 1,000. IVFFlat does not support sparsevec.

CREATE INDEX ON items USING hnsw (embedding sparsevec_l2_ops);

Choose a matching sparse operator class and validate the indexed values against the lower HNSW limit.

Account for approximate-index omissions

HNSW and IVFFlat omit NULL vectors. Cosine-distance variants also omit zero vectors. These exclusions can reduce result counts even when iterative-scan depth is sufficient.

Check the stored values and operator class before treating a short result set as a scan-limit problem.

Build portable extension artifacts

Some platforms compile with -march=native. A binary moved to a different processor can then fail with Illegal instruction. Clear the optimization flags for a portable build:

make OPTFLAGS=""

When a parallel HNSW build uses increased maintenance_work_mem inside Docker, give the container at least the same amount of shared memory:

docker run --shm-size=1g ...

See references/deployment.md before selecting a source tag, container tag, package-manager formula, or Windows toolchain.

Size hosted index builds deliberately

For Neon index builds, set session maintenance_work_mem near the vector index's working-set size without exceeding roughly 50–60% of available RAM. The default max_parallel_maintenance_workers is 2; raise it toward the compute's CPU count only within max_parallel_workers and max_worker_processes.

SET maintenance_work_mem = '10 GB';
SET max_parallel_maintenance_workers = 7;

Confirm the available compute resources before applying these example values.

Maintain HNSW indexes before vacuuming

Vacuuming a table with an HNSW index can be slow. Reindex the HNSW index concurrently before vacuuming the table:

REINDEX INDEX CONCURRENTLY index_name;
VACUUM table_name;

See references/indexing-and-storage.md for the maintenance sequence and indexing constraints.