pgvector adds vector types, distance operators, exact nearest-neighbor search, and approximate HNSW and IVFFlat indexes to PostgreSQL. Exact search provides the recall baseline; approximate indexes trade recall, build time, memory, storage, and query speed. Operator, operator class, distance metric, dimensions, filters, and tuning must match and be evaluated together.
Confirm the extension is available for your OS/managed service and its supported version. Installation is environment-specific. In an isolated database with proper authority:
Exact search calculates eligible distances and sorts. Approximate indexes navigate a reduced candidate structure; faster search may omit true nearest neighbors.
Example for a deliberately small dimension—use the actual embedding model dimension:
The expression in ORDER BY and LIMIT matters for index use. Choose vector type (vector, half precision, sparse, binary where supported), distance operator, and operator class from exact requirements and current pgvector docs.
HNSW often offers strong query performance/recall with higher build/memory cost and supports incremental construction. IVFFlat uses lists/probes and generally needs representative data/training conditions; tuning and maintenance differ. These are workload-dependent, not a universal ranking.
Approximate search plus restrictive tenant/ACL filters can return too few results because the index finds approximate candidates before/with filtering behavior depending on plan and extension features. Use current pgvector iterative-scan/filter guidance, partitioning or partial indexes only when justified, and measure authorized recall—not global recall.
Create a labeled query dataset. Use exact search as ground truth, then compare approximate top-k intersection/recall across index parameters, query tuning, tenant sizes, and filters. Record p50/p95 latency, result count, recall@k, index size, build time, insert cost, and plan.
Using synthetic 3D vectors, build exact and one approximate search path and calculate recall@5 for ten queries.
Acceptance criterion: exact ground truth is preserved, tenant filters never leak, and speed claims include recall and representative scale.