timescale/pg-aiguide

Overview

This skill documents pgvector setup and operational best practices for semantic search with text embeddings in PostgreSQL. It provides a clear golden-path configuration, indexing and storage rules, and tuning guidance for HNSW and IVFFlat indexes. The goal is reliable, high-recall vector search at scale while keeping queries inside the database.

How this skill works

The content explains how to store embeddings as halfvec(N), create ANN indexes (HNSW by default), and query by casting search vectors to the matching type. It describes index parameters (m, ef_construction, hnsw.ef_search), iterative scan for filtered searches, and quantization strategies (binary quantization + re-ranking) when memory is constrained. It also covers bulk loading, maintenance, monitoring, and common failure modes with fixes.

When to use it

• When building semantic search inside PostgreSQL without an external ANN service
• When you need a simple, high-recall default: HNSW + halfvec + cosine distance
• When dataset fits in memory for HNSW or you can partition/augment RAM
• When write-heavy workloads or very large datasets force different tradeoffs (IVFFlat or binary quantization)
• When filtered vector search is required and you must choose prefiltering, partial indexes, or iterative scan

Best practices

• Enable the vector extension in every DB and store embeddings as halfvec(N) matching your model dimension
• Default to HNSW with halfvec_cosine_ops and query using ORDER BY embedding <=> $1::halfvec(N) LIMIT k
• Create indexes after bulk loading and use CREATE INDEX CONCURRENTLY in production
• Tune query recall first by setting hnsw.ef_search; increase m/ef_construction only if recall plateaus and memory allows
• Use iterative scan or B-tree prefiltering for selective WHERE clauses; consider partitioning or partial indexes for low-cardinality filters
• Use binary quantization + re-ranking only when index cannot fit in memory; store quantized bits in a generated column and re-rank with full-precision vectors

Example use cases

• Site search or help center retrieval where embeddings and metadata live in Postgres
• Reranking candidates for QA systems by running ANN inside the DB then re-ranking with exact distances
• Large document stores that fit in RAM for HNSW or use binary quantization with oversampling and re-ranking for 10M+ vectors
• Multi-tenant or label-filtered search using partitions or partial HNSW indexes per label
• Rapid prototyping: keep embeddings and vector logic in Postgres to avoid external services