pgvector Search skill for Codex

Done well means: the query shape (ORDER BY embedding <op> $1 LIMIT k) matches an index built with the same operator class, EXPLAIN ANALYZE shows the index scan, recall@k against exact search is measured, and filtered queries return a full k rows.

Check the ground first

SELECT extversion FROM pg_extension WHERE extname = 'vector';   -- want 0.8.0 or later
SELECT version();
\d+ chunks                                                      -- column type and indexes
SELECT pg_size_pretty(pg_total_relation_size('chunks'));
SELECT count(*) FROM chunks;

Read AGENTS.md and the migration tool (Alembic, Prisma, Drizzle, Rails, Flyway) before writing DDL: index changes ship as migrations, never as ad-hoc SQL against shared databases. Managed hosts (RDS, Cloud SQL, Supabase, Neon, Azure) bundle a pgvector version; check it, because iterative scans need 0.8.0+.

Column type

Type	Max dims indexed	Use
vector(n)	2,000	Default for float32 embeddings up to 2,000 dims
halfvec(n)	4,000	Half the storage, near-identical recall; needed for 3072-dim models
bit(n)	64,000	Binary quantization for a first-pass candidate search
sparsevec(n)	1,000 non-zero	SPLADE/BM25-style sparse vectors

Store the dimension in the type (vector(1536)), not bare vector; the index requires it.

Operators and operator classes must match

Distance	Operator	HNSW/IVFFlat opclass (vector)	Notes
Cosine	<=>	vector_cosine_ops	Default for most text models; similarity = 1 - distance
L2	<->	vector_l2_ops
Inner product	<#>	vector_ip_ops	Returns the negative inner product; ascending order is correct
L1	<+>	vector_l1_ops	HNSW only
Hamming (bit)	<~>	bit_hamming_ops

For halfvec use halfvec_cosine_ops etc. If vectors are unit-normalised, inner product is the fastest equivalent of cosine.

HNSW vs IVFFlat

	HNSW (default choice)	IVFFlat
Build	Slower, more memory	Fast
Query speed/recall	Better trade-off	Lower recall at same speed
Empty table at creation	Fine	Bad: lists are trained on existing rows
Heavy ongoing inserts	Fine	Recall drifts; rebuild periodically
Knobs	m, ef_construction, hnsw.ef_search	lists, ivfflat.probes

Pick HNSW unless build time or memory is the hard constraint.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id          bigserial PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  doc_id      uuid NOT NULL,
  content     text NOT NULL,
  embedding   vector(1536) NOT NULL,
  embed_model text NOT NULL DEFAULT 'text-embedding-3-small'
);

-- Build without blocking writes; give the build memory and workers for this session only
SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 4;
CREATE INDEX CONCURRENTLY chunks_embedding_hnsw
  ON chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

CREATE INDEX chunks_tenant_idx ON chunks (tenant_id);

Starting parameters: m = 16, ef_construction = 64 (defaults). Raise ef_construction to 128-200 if recall plateaus below target at reasonable ef_search. If the build log says the graph no longer fits in maintenance_work_mem, the build slows sharply: raise it or build during a quiet window. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so mark the migration non-transactional.

IVFFlat when chosen: build after data is loaded, lists = rows / 1000 up to 1M rows and sqrt(rows) above that; start ivfflat.probes at sqrt(lists).

Query shape

-- $1 = query embedding, $2 = tenant
SELECT id, doc_id, content, 1 - (embedding <=> $1) AS similarity
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

The index is used only when the query orders by the distance operator ascending with a LIMIT. These break index use: ordering by 1 - (embedding <=> $1) DESC, a distance threshold in WHERE without ORDER BY ... LIMIT, a different operator than the opclass, or casting the column in the query but not in the index.

Recall knobs per query

BEGIN;
SET LOCAL hnsw.ef_search = 100;   -- default 40; must be >= LIMIT
SELECT ... ORDER BY embedding <=> $1 LIMIT 20;
COMMIT;

Use SET LOCAL inside a transaction so pooled connections (PgBouncer transaction mode) do not leak settings to other requests. Raise ef_search until recall@k meets target, then stop: latency grows roughly linearly.

Filtered search (the usual production bug)

HNSW returns the ef_search nearest candidates, then Postgres applies WHERE. With a selective filter (one tenant out of thousands) you get 3 rows instead of 10.

Fix	When
Iterative index scans (0.8.0+): SET LOCAL hnsw.iterative_scan = relaxed_order;	First thing to try; keeps scanning until enough rows pass the filter
Raise hnsw.max_scan_tuples (default 20,000)	Very selective filters still come back short
B-tree on the filter column only	Very selective filters: the planner may choose exact scan of the small subset, which is correct and fast
Partial index per hot value: ... WHERE category = 'faq'	A few fixed categories
Partition by tenant/region, index per partition	Large tenants, strict isolation

relaxed_order can return results slightly out of order; re-sort in an outer query if order matters: SELECT * FROM (<inner query>) t ORDER BY distance LIMIT 10 where the inner query selects embedding <=> $1 AS distance. strict_order keeps exact order at more cost.

Large dimensions and quantization

-- 3072-dim model: index a halfvec expression; query must use the identical cast
CREATE INDEX CONCURRENTLY ON docs USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);
SELECT id FROM docs ORDER BY embedding::halfvec(3072) <=> $1::halfvec(3072) LIMIT 10;

-- Binary quantization first pass, then exact rerank on full vectors
CREATE INDEX CONCURRENTLY ON docs USING hnsw ((binary_quantize(embedding)::bit(3072)) bit_hamming_ops);
SELECT id FROM (
  SELECT id, embedding FROM docs
  ORDER BY binary_quantize(embedding)::bit(3072) <~> binary_quantize($1)
  LIMIT 200
) c ORDER BY c.embedding <=> $1 LIMIT 10;

Measuring recall

-- Exact top-k (ground truth): disable index scans for this transaction
BEGIN; SET LOCAL enable_indexscan = off;
SELECT id FROM chunks WHERE tenant_id = $2 ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

Sample 100-200 real query embeddings, collect exact and ANN top-10 for each, compute mean(|ann ∩ exact| / 10). Target >= 0.95 for RAG unless the user accepts less. Re-run after any parameter change and after large data growth.

Operations

• Bulk load: COPY (binary format for speed) into a table without the HNSW index, then build the index. Building on a full table is much faster than inserting into an existing graph.
• Vacuum: HNSW indexes slow down with many dead tuples; deletes and updates of embeddings need regular VACUUM. REINDEX INDEX CONCURRENTLY rebuilds after heavy churn.
• Updating embeddings for a model change: add a new column (embedding_v2 vector(1024)), backfill in batches, build its index concurrently, switch reads, then drop the old column only after the user confirms.
• Memory: the HNSW index performs best when it fits in shared_buffers plus OS cache. Check pg_relation_size('chunks_embedding_hnsw').

Gotchas

• ORMs often generate ORDER BY ... DESC on similarity. Order by distance ascending.
• ef_search lower than LIMIT silently caps results.
• Mixing normalised and unnormalised vectors breaks inner-product search; normalise at ingest.
• vector parameters from drivers: register pgvector’s adapters (pgvector.psycopg.register_vector, pgvector-node or the ORM integration) instead of string-formatting arrays into SQL.
• Planner may choose a sequential scan on small tables (< ~10k rows); that is correct, not a bug.

Verification

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM chunks WHERE tenant_id = '...' ORDER BY embedding <=> '[...]' LIMIT 10;

• Plan shows Index Scan using chunks_embedding_hnsw (or a deliberate exact scan for a tiny filtered subset), rows returned = 10, execution time inside budget.
• Recall@10 vs exact meets target at the chosen ef_search.
• Filtered queries for the smallest real tenant return a full page.

Report template

**pgvector:** 0.8.0 on PostgreSQL 17; table chunks 4.2M rows, vector(1536), cosine
**Index:** HNSW m=16 ef_construction=128, 9.8 GB, built concurrently in 41 min
**Query settings:** hnsw.ef_search=80, hnsw.iterative_scan=relaxed_order (SET LOCAL)
**Recall@10 vs exact:** 0.962 (200 sampled queries); smallest tenant returns 10/10 rows
**Latency:** p50 9 ms, p95 24 ms at 150 QPS