Area 02 · RAG · Concept 05 of 20

pgvector

A Postgres extension that adds vector columns and similarity search to the database you already use.

01–10 · The basePhase 2 · Weeks 7–10≈ 1.5 hours

The idea

pgvector adds a vector(n) column type to Postgres plus distance operators: <=> for cosine distance, <-> for Euclidean and <#> for negative inner product. You store chunks, metadata and embeddings in one table and query with plain SQL.

Because it is Postgres, you get joins with your users and documents tables, transactions, backups, row-level security and the SQL you already know. Filtering by tenant, date or document type is a WHERE clause.

Add an HNSW index for speed once you have more than a few thousand rows. Managed Postgres services (Supabase, Neon, RDS, Cloud SQL) support the extension.

Why it matters

It is the default vector store in this roadmap, and the one most companies already have the skills to run.

Words to know

  • vector(n)Column type holding an n-dimensional vector.
  • <=>Cosine distance operator; smaller is closer.

Try it without code

Run Postgres with pgvector

  1. Run: docker run -e POSTGRES_PASSWORD=pw -p 5432:5432 pgvector/pgvector:pg16
  2. Connect with psql and run CREATE EXTENSION vector;

Use it in code

Create a table, insert embeddings and search with SQL.

python
# pip install "psycopg[binary]" pgvector sentence-transformers
import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("BAAI/bge-small-en-v1.5")
conn = psycopg.connect("postgresql://postgres:pw@localhost/postgres", autocommit=True)
conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
register_vector(conn)
conn.execute("""CREATE TABLE IF NOT EXISTS chunks (
  id bigserial PRIMARY KEY, doc text, body text, embedding vector(384))""")

for body in ["Refunds take 5 days.", "Reset your password in Settings."]:
    conn.execute("INSERT INTO chunks (doc, body, embedding) VALUES (%s, %s, %s)",
                 ("faq.md", body, model.encode(body, normalize_embeddings=True)))

q = model.encode("money back time", normalize_embeddings=True)
for row in conn.execute("SELECT body, embedding <=> %s AS dist FROM chunks ORDER BY dist LIMIT 3", (q,)):
    print(row)
PRACTICE · TICK IT AFTER THIS

Store your chunked documents in pgvector with doc, heading and page columns, and write a search function that returns them with distances.

Common mistakes

  • Wrong dimension in vector(n) for your model.
  • Forgetting to normalise when you rely on inner product.

Go deeper