# Pgvector Knowledge Patch > pgvector - Skill: `nevaberry/pgvector-knowledge-patch` (Agent Skill, multi-file: 5 files) - Install (CLI): `npx skillmds@latest add nevaberry/pgvector-knowledge-patch` - Raw SKILL.md: https://api.skillmd.com/api/skills/nevaberry/pgvector-knowledge-patch/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - License: MIT - Author: nevaberry (https://skillmd.com/u/nevaberry) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/nevaberry/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](references/iterative-scans.md) | Filtered HNSW and IVFFlat searches, scan modes and limits, exact reordering, planner choices | | [references/indexing-and-storage.md](references/indexing-and-storage.md) | Sparse-vector limits, array casts, index omissions, 32-bit IVFFlat safety, HNSW maintenance | | [references/deployment.md](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](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](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 | ```sql 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. ```sql 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](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`: ```sql 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`. ```sql 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: ```shell 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: ```shell docker run --shm-size=1g ... ``` See [references/deployment.md](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`. ```sql 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: ```sql REINDEX INDEX CONCURRENTLY index_name; VACUUM table_name; ``` See [references/indexing-and-storage.md](references/indexing-and-storage.md) for the maintenance sequence and indexing constraints.