NotesSystem

pgvector

Also: pg_vector · Postgres vector extension

A Postgres extension that adds vector types, distance operators and ANN indexes. Its defining choice is to keep embeddings in the same database, rows and transactions as the data they describe.

Design choices

  • Types: vector (indexable up to 2,000 dimensions), halfvec (half precision, up to 4,000), bit (binary quantization) and sparsevec.
  • Exact search by default: without an index, a nearest-neighbour query scans the table, with perfect recall.
  • Approximate Nearest Neighbor indexes: HNSW (since 0.5.0, 2023; m 16, ef_construction 64, hnsw.ef_search 40) and IVFFlat (lists ≈ rows/1000 up to 1M rows; ivfflat.probes 1).
  • Filtering: the index runs before the WHERE clause; iterative index scans (0.8.0, 2024) keep scanning until enough rows pass.
  • Indexes are ordinary Postgres indexes: WAL-logged, replicated, transactional.

Notable trade-offs

  • One system instead of two, but vector workloads now share CPU, memory and vacuum with OLTP traffic.
  • HNSW builds are slow and want the graph in maintenance_work_mem; selective filters still need care.
  • Instacart replaced a separate FAISS service with pgvector for hybrid product search inside its sharded Postgres.