postgres-impl-pgvector-similarity
postgres-impl-pgvector-similarity
Quick Reference :
pgvector adds vector similarity search to PostgreSQL while keeping ACID, transactions, and standard SQL. Install with CREATE EXTENSION vector. Store embeddings in a vector(n) column, query with ORDER BY embedding <=> $1 LIMIT k.
Four storage types : vector (float32, store up to 16000 dim, index up to 2000), halfvec (float16, store up to 16000, index up to 4000 , use it to index high-dimension embeddings), sparsevec (sparse, store up to 16000 non-zero), bit (binary). Six distance operators : <-> L2, <=> cosine, <#> negative inner product, <+> L1 (v0.7+), <~> Hamming and <%> Jaccard (bit only).
Two index methods, both approximate (ANN) : HNSW is the default choice , no training step, best recall and query speed, slower build and more memory. ivfflat is faster to build and lighter, but recall is lower AND it MUST be built after representative rows are loaded (it k-means-trains on existing data).
The single most common failure : the index operator class MUST match the distance operator the query uses. An hnsw (embedding vector_cosine_ops) index accelerates <=> only , a query using <-> ignores it and does a Seq Scan. ALWAYS pick one distance metric per column, build the matching opclass, and query with the matching operator.