Add semantic search to a synthetic products table in local Postgres: local embeddings, an HNSW index, and recall-versus-latency measured across two parameter sets — plus a storage and build-memory forecast for 10x growth. The starter query ships with a planted mismatch that silently disables the index; EXPLAIN is how you catch it.
Ananya's product team wants "search that understands 'warm jacket for Himalayan trek'" and the CTO wants it inside the Postgres they already run, not a new vector database. That is the right call — and it makes vector search a DBA problem: index types, operator classes, recall trade-offs, build memory, and capacity forecasts. This lab treats pgvector the way you would treat any new index type in production: measure before believing, and read the plan before trusting the query.
~120 minutes hands-on · Core tier (maps to module M4 · The AI-Ready Database Estate — Vectors in Your Engine).
Docker image with pgvector, Python with sentence-transformers (fully local, ~90MB model, $0):
docker run -d --name vector-lab -e POSTGRES_PASSWORD=lab -p 5434:5432 pgvector/pgvector:pg16
sleep 5
docker exec -it vector-lab psql -U postgres -c "create database shopdb"
pip install sentence-transformers psycopg2-binary
Schema plus 5,000 synthetic products, seed_products.py:
import random, psycopg2
random.seed(7)
CATS = {"jacket": ["insulated","waterproof","down","fleece","windproof"],
"tent": ["2-person","4-season","ultralight","dome","family"],
"stove": ["butane","multi-fuel","compact","windproof","titanium"],
"boot": ["trekking","waterproof","insulated","approach","alpine"],
"pack": ["55L","daypack","framed","ultralight","expedition"]}
USE = ["for high-altitude trekking","for monsoon camping","for winter summits",
"for weekend trails","for desert expeditions","for family campsites"]
conn = psycopg2.connect("host=localhost port=5434 dbname=shopdb user=postgres password=lab")
cur = conn.cursor()
cur.execute("""create extension if not exists vector;
create table products (
product_id int primary key,
name text not null,
description text not null,
price numeric(10,2) not null,
embedding vector(384));""")
for pid in range(1, 5001):
cat = random.choice(list(CATS))
adjs = random.sample(CATS[cat], 2)
name = f"{adjs[0].title()} {adjs[1]} {cat} model {pid}"
desc = f"A {adjs[0]}, {adjs[1]} {cat} {random.choice(USE)}."
cur.execute("insert into products values (%s,%s,%s,%s,null)",
(pid, name, desc, round(random.uniform(999, 24999), 2)))
conn.commit(); print("seeded 5000 products")
The starter search query the app team drafted, search_draft.sql — defect included, keep as-is for now:
-- search_draft.sql (app team's draft)
select product_id, name, embedding <-> %s::vector as score
from products order by score limit 10;
Seed and embed. Run python seed_products.py, then embed name+description with a local model, embed.py:
from sentence_transformers import SentenceTransformer
import psycopg2
model = SentenceTransformer("all-MiniLM-L6-v2") # 384-dim
conn = psycopg2.connect("host=localhost port=5434 dbname=shopdb user=postgres password=lab")
cur = conn.cursor()
cur.execute("select product_id, name || '. ' || description from products")
rows = cur.fetchall()
for i in range(0, len(rows), 200):
batch = rows[i:i+200]
vecs = model.encode([t for _, t in batch], normalize_embeddings=True)
for (pid, _), v in zip(batch, vecs):
cur.execute("update products set embedding = %s where product_id = %s",
(v.tolist(), pid))
conn.commit()
print("embedded", len(rows))
Expected: 5,000 rows embedded in a couple of minutes on CPU. Note the normalize_embeddings=True — it matters for the operator decision coming next.
Establish exact-search ground truth. Before any index exists, every query is a sequential scan — slow but exact. Build a 20-query evaluation set (e.g., "warm jacket for himalayan trek", "light tent for two", "fuel stove for winter", spanning all five categories), embed each, and record the true top-10 product_ids per query using cosine distance (<=>). Save as ground_truth.json. Also record the seq-scan latency (expect tens of ms at 5k rows). Recall@10 will be measured against this truth.
Build the HNSW index — parameter set A. Defaults first:
set maintenance_work_mem = '256MB';
create index idx_products_hnsw on products
using hnsw (embedding vector_cosine_ops) with (m = 16, ef_construction = 64);
Record build time and index size (select pg_size_pretty(pg_relation_size('idx_products_hnsw'))). Expected: a few seconds, roughly 10–20MB at this scale.
Run the app team's draft — and read the plan. Execute search_draft.sql with one of your query vectors, then run it under explain analyze. Expected finding — the planted defect: the plan shows a sequential scan, not the index. The draft orders by <-> (L2 distance) but the index was built with vector_cosine_ops — pgvector only uses an index whose operator class matches the query operator. The results even look fine (normalized vectors make L2 and cosine rank almost identically), which is exactly why unmeasured vector search ships broken: correct-looking answers at 50x the cost. Fix the query to <=>, re-explain, and confirm Index Scan using idx_products_hnsw. Write the incident note.
Measure recall@10 versus latency — set A. For each of the 20 queries, run the fixed query at ef_search 40 (default) and compare against ground truth. Compute recall@10 (overlap/10, averaged) and mean latency, measure.py style: run each query 5 times, take the median. Expected at this scale: recall in the 0.95–1.0 range, latency around or under a millisecond or two.
Parameter set B — trade recall for speed and back. Rebuild with (m = 32, ef_construction = 128) (record the longer build time and larger size), then measure at set hnsw.ef_search = 20; and = 100;. You now have a small grid: two builds × two search settings. Expected shape: ef_search = 20 visibly dents recall on at least a few queries; m = 32 buys recall headroom at build-time and memory cost. Tabulate all four cells: recall@10, median latency, build time, index size.
Forecast 10x. The table that goes to capacity planning, reasoned from measurements, not vibes: at 50,000 products — embedding storage (384 floats × 4 bytes × rows, plus row overhead), index size (extrapolate from your two builds — HNSW grows roughly linearly in rows, superlinearly in m), build memory (the graph must fit in maintenance_work_mem during build or the build spills and slows dramatically — state the setting you would use), build time, and whether ef_search needs to rise as the graph grows to hold recall (it does; note the re-measurement plan). Add the operational lines: the index lives in shared_buffers competition with your OLTP working set, and every product update means an embedding update (that is Lab 08's problem — name it as a handoff).
Package the endpoint. search.sql — the final parameterized query (cosine operator, ef_search set explicitly per session, LIMIT bound) — plus tuning-table.md with the four-cell grid, the ground-truth method, the draft-query incident note, and the 10x forecast.
explain analyze output exists for the draft query (seq scan) and the fixed query (index scan) — the operator-class mismatch is documented as a caught defect.ground_truth.json covers 20 queries built from exact scans before the index existed.Self-grading rubric (pass = 7/10): ground truth built exact-first (0–2); operator mismatch caught via plan, not vibes (0–3); four-cell grid measured with medians (0–2); forecast quantitative and tied to measurements (0–2); final endpoint pins operator, ef_search, and LIMIT explicitly (0–1).
sentence-transformers install is slow or failsit pulls PyTorch; on a constrained machine use the CPU-only wheel (pip install torch --index-url https://download.pytorch.org/whl/cpu first), or swap in Ollama with an embedding model and adjust the vector dimension accordingly.v.tolist()), and confirm the column is vector(384), matching MiniLM's output dimension exactly.explain shows an index scan even for the draft queryyou built the index with vector_l2_ops by accident; \d+ products shows the operator class — the lab's defect requires vector_cosine_ops against a <-> query.ef_search = 20 and below. If all cells read 1.0, drop ef_search to 10 for the low cell and note that graph-quality effects grow with N — which is precisely why the forecast mandates re-measurement at 50k.Vector search inside your engine is still database work: operator classes decide whether the index is even used, recall is a number you measure against exact ground truth, and capacity is a forecast you derive from two builds — not from a vendor's benchmark slide.