Pgvector Optimization

pgvector Optimization

Skill Resources

• Activation and cross-agent notes: references/activation.md
• Use ultrathink or the deepest available reasoning mode before changing architecture, security, migration, or performance-critical paths.
• Template: templates/index-tuning-record.md

pgvector with default settings gives you correct results but not optimal performance. The gap between default and tuned can be 10–100x in query speed and 4–8x in memory usage. This skill covers what to tune and when.

HNSW vs. IVFFlat: Choosing the Right Index

Default recommendation: Use HNSW unless your collection is static and you need faster build time.

HNSW Index Parameters

-- Basic HNSW index
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Higher quality (more memory, slower build, better recall)
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
WITH (m = 32, ef_construction = 200);

Memory Estimate for HNSW

Index memory ≈ (1.1 × m × 8 bytes × num_vectors) + (num_vectors × dimensions × 4 bytes)

Example: 1M vectors at 1024 dimensions, m=16:
  Graph:   1.1 × 16 × 8 × 1,000,000 = 140 MB
  Vectors: 1,000,000 × 1024 × 4     = 4 GB
  Total:   ~4.14 GB

ef_search: Query-Time Recall vs. Speed Trade-off

ef_search (or hnsw.ef_search in pgvector) controls the candidate list size during query execution. Higher values search more candidates before returning results — better recall but slower queries.

-- Set for the current session
SET hnsw.ef_search = 40;   -- Default; fast but lower recall
SET hnsw.ef_search = 100;  -- Better recall; moderate cost
SET hnsw.ef_search = 200;  -- High recall; slower

-- Set as a server default (postgresql.conf or ALTER SYSTEM)
ALTER SYSTEM SET hnsw.ef_search = 100;
SELECT pg_reload_conf();

Recall vs. ef_search Calibration

Measure recall by comparing ANN results against exact KNN results on a sample:

-- Exact KNN (no index — use for recall measurement only)
SET enable_indexscan = off;
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
RESET enable_indexscan;

-- ANN with index
SET hnsw.ef_search = 40;
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;

Count the overlap between both result sets. ef_search = 40 typically gives ~88–92% recall; ef_search = 200 gives ~98%+.

Rule of thumb: ef_search should be at least as large as the LIMIT value in your query. For LIMIT 10, ef_search = 40 gives adequate recall. For LIMIT 100, use ef_search = 200.

IVFFlat Parameters

-- Build after loading data — centroids are computed from existing rows
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Set probes for the session
SET ivfflat.probes = 10;  -- Search 10 clusters; good recall for lists=100

-- Recommended: probes = sqrt(lists)
-- For lists=100: probes=10
-- For lists=1000: probes=32

Critical: IVFFlat requires data to be loaded before building the index. Building on an empty table gives meaningless centroids.

-- Load your data first
INSERT INTO items SELECT ...;

-- Then build the index
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Distance Metric Selection

pgvector supports three distance operators — the correct choice depends on how your embedding model was trained.

-- Check if your vectors are normalized
SELECT AVG(vector_norm(embedding)) FROM items LIMIT 1000;
-- Should be ~1.0 for normalized vectors

-- If not normalized and your model uses cosine similarity, normalize at insert time
-- or use <-> with un-normalized vectors (results differ from cosine)

Filtered Queries: The Recall Collapse Problem

Vector search with aggressive filters causes recall collapse — the ANN index returns candidates that are then filtered out, leaving fewer results than requested.

-- ❌ This may return fewer than 10 results if the filter is selective
SELECT id, embedding <=> $1 AS distance
FROM items
WHERE category = 'restaurant' AND city_id = 42
ORDER BY embedding <=> $1
LIMIT 10;

Iterative Scanning (pgvector 0.7.0+)

pgvector 0.7.0 introduced iterative index scans — the engine fetches more candidates automatically until the LIMIT is satisfied.

-- Enable iterative scanning (requires pgvector 0.7.0+)
SET hnsw.iterative_scan = relaxed_order;
-- Options:
--   strict_order:   No iterative scan (default; may return < LIMIT results)
--   relaxed_order:  Iterative scan; results may not be in exact distance order
--   off:            Disabled

SELECT id FROM items
WHERE category = 'restaurant' AND city_id = 42
ORDER BY embedding <=> $1
LIMIT 10;

Partial Index to Pre-Filter by Category

For high-cardinality filters that are known at index creation time:

-- Index only restaurant embeddings — queries filtering by category = 'restaurant'
-- will use this smaller, faster index
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
WHERE category = 'restaurant';

Two-Stage Retrieval for Selective Filters

When filters are too selective for the index to satisfy, broaden the ANN search and filter in application code:

-- Stage 1: Over-fetch from the vector index
SELECT id, description, category, city_id,
       embedding <=> $1 AS distance
FROM items
ORDER BY embedding <=> $1
LIMIT 200;  -- Fetch 200 candidates

-- Stage 2: Apply business filters in application code
-- Then re-rank the filtered subset

Scalar Quantization: 4x Memory Reduction

Scalar quantization (SQ8) compresses each float32 (4 bytes) to int8 (1 byte) with ~1% recall penalty at equivalent ef_search. Available in pgvector 0.7.0+.

-- Full precision HNSW (baseline)
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Scalar quantization — 4x smaller, ~1% recall penalty
CREATE INDEX ON items USING hnsw (embedding vector_halfvec_ops)
WITH (m = 16, ef_construction = 64);

-- Or use the halfvec type directly (pgvector 0.7.0+)
ALTER TABLE items ADD COLUMN embedding_half halfvec(1024);
UPDATE items SET embedding_half = embedding::halfvec;
CREATE INDEX ON items USING hnsw (embedding_half vector_halfvec_ops);

Memory Comparison

1M vectors at 1024 dimensions:
  float32:  1,000,000 × 1024 × 4 bytes = 4.0 GB
  halfvec:  1,000,000 × 1024 × 2 bytes = 2.0 GB
  int8:     1,000,000 × 1024 × 1 byte  = 1.0 GB (binary quantization)

Binary Quantization: 32x Compression

Binary quantization (bit vectors) converts each dimension to a single bit. Only applicable for models that are explicitly trained for it (e.g., Qwen3 with binary quantization, Cohere Embed v3 with binary mode). Using it on models not trained for it produces poor recall.

-- Only use with models that explicitly support binary quantization
CREATE INDEX ON items USING hnsw (embedding bit_hamming_ops);

-- Query with bit vectors
SELECT id FROM items
ORDER BY embedding <~> $1::bit(1024)
LIMIT 10;

Matryoshka Dimension Reduction

Matryoshka-trained models (Qwen3, OpenAI text-embedding-3-*, some others) maintain quality when dimensions are truncated. Use this to reduce index size without retraining.

-- Store at full 4096 dims, but truncate for the index
-- (Qwen3 Matryoshka: 4096 → 1024 → 256)

-- Option 1: Store truncated embedding at insert time
INSERT INTO items (description, embedding)
SELECT description, embedding[:1024]  -- Slice to first 1024 dimensions
FROM source;

-- Option 2: Index on a truncated expression (avoids storing twice)
CREATE INDEX ON items USING hnsw ((embedding[:1024]) vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Query uses the same expression
SELECT id FROM items
ORDER BY embedding[:1024] <=> $1[:1024]
LIMIT 10;

Important: Only truncate dimensions from Matryoshka-trained models. Truncating a non-Matryoshka model's dimensions will degrade recall significantly.

Monitoring Index Health

-- Check index size and configuration
SELECT
  i.relname AS index_name,
  pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
  ix.indoption,
  am.amname AS access_method
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_am am ON am.oid = i.relam
WHERE ix.indrelid = 'items'::regclass;

-- Check if queries are using the index
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
-- Look for "Index Scan using <index_name>" — not "Seq Scan"

When the Planner Chooses a Seq Scan Over the Vector Index

-- pgvector's planner uses statistics on row counts and `max_scan_tuples`
-- If the table is small, the planner may choose Seq Scan

-- Check the current max_scan_tuples threshold
SHOW max_parallel_workers_per_gather;

-- Force index use for testing
SET enable_seqscan = off;
EXPLAIN (ANALYZE) SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
RESET enable_seqscan;

Optimization Decision Tree

Recall is poor (< 90%) at current ef_search?
  └── Increase ef_search (try 2x current value)
  └── If still poor: increase m during index rebuild

Index build is too slow?
  └── Use IVFFlat (batch workload) or reduce ef_construction

Memory footprint too large?
  └── Use halfvec (2x reduction, ~1% recall loss)
  └── Use int8 quantization (4x reduction, ~2-3% recall loss)
  └── Use Matryoshka dimension reduction if model supports it

Filtered queries returning < LIMIT results?
  └── pgvector 0.7.0+: SET hnsw.iterative_scan = relaxed_order
  └── Create partial indexes for common filter predicates
  └── Over-fetch and filter in application code

Queries with many concurrent users are slow?
  └── Check HNSW index is in shared_buffers (pg_prewarm)
  └── Check ef_search is not set too high for throughput
  └── Consider read replicas for vector search workloads