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) andsparsevec. - 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;
m16,ef_construction64,hnsw.ef_search40) and IVFFlat (lists ≈ rows/1000 up to 1M rows;ivfflat.probes1). - Filtering: the index runs before the
WHEREclause; 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.