Your products, tickets or articles table already holds the text; your engine can already index vectors. This playbook connects the two — a local embedding model, a backfill, an HNSW index and a hybrid query — so users get meaning-aware search with no new platform, no data movement and no vendor bill.
Tier: Core · Workflow area: Vector & AI Features · ~40 minutes
Keyword search misses "waterproof jacket" when the row says "rain-resistant shell." Semantic search closes that gap, and doing it inside the engine keeps every guarantee you already operate: transactions, backups, access control, one system to page you. This playbook builds the whole path on a real table, with a local model so no data leaves the building, and finishes with the measurement that tells you whether it actually works.
Stand up a local embedding model and pick dimensions deliberately. Ollama serves capable open embedding models on modest hardware:
ollama pull nomic-embed-text
curl -s http://localhost:11434/api/embeddings \
-d '{"model": "nomic-embed-text", "prompt": "waterproof hiking jacket"}' \
| jq '.embedding | length'
# 768
768 dimensions at 4 bytes each ≈ 3 KB per row before index overhead. Smaller models embed faster and store cheaper; test retrieval quality before assuming bigger is better.
Decide what text represents a row — this choice outranks model choice. Concatenate the fields a human would read to judge relevance, and label them:
SELECT id, 'Name: ' || name || '. Category: ' || category ||
'. Description: ' || coalesce(description, '') AS embed_text
FROM products LIMIT 5;
Ask AI to critique the template against ten sampled rows: what signal is missing (specs, tags), what noise is present (boilerplate, SKUs), and what it would add. Judge its suggestions against the sample yourself.
Backfill embeddings in batches, tracking progress in the row. Add the column, then run a resumable batch job — a script the model can draft in your language of choice:
ALTER TABLE products ADD COLUMN embedding vector(768);
Write a Python script: select id, embed_text for 500 products where
embedding IS NULL; POST each to Ollama /api/embeddings
(nomic-embed-text); UPDATE the row; commit per batch; loop until
none remain. Log throughput. Make it safe to kill and restart.
Review the draft for the two classic flaws before running it: unbatched commits and no retry on model timeouts. Backfill on a replica-fed worker or off-peak — this is real write load.
Index, then wire the query — hybrid, not vector-only. Build the HNSW index (per pb-28), then serve search that combines semantic and lexical signals, because pure ANN misses exact-term intent:
CREATE INDEX CONCURRENTLY idx_products_hnsw
ON products USING hnsw (embedding vector_cosine_ops);
-- $1 = query embedding, $2 = raw query text
SELECT id, name,
1 - (embedding <=> $1) AS semantic_score
FROM products
WHERE in_stock
ORDER BY embedding <=> $1
LIMIT 20;
For hybrid, run this alongside a tsvector full-text query and merge with reciprocal rank fusion in the app or a SQL CTE — ask AI to draft the fusion query, then verify the ranking math on a worked example by hand.
Measure retrieval quality before declaring victory. Build a 30-query evaluation set from real search logs, each with human-judged relevant results. Score recall@10 for old keyword search vs. new hybrid search. Sample outcome worth shipping: keyword recall@10: 0.52 → hybrid recall@10: 0.81; vector-only: 0.74. If hybrid does not beat keyword on your queries, the feature is not done — revisit the embed-text template first, model second.
Hand over as a feature, not an experiment. Document: model name and version pinned, embed-text template, index parameters, the eval set and scores, and the re-embedding obligation when text changes — which is pb-30's whole subject.
VECTOR type and DiskANN index; embeddings can be generated via external model integration or the same local-model script pattern.VECTOR columns with VECTOR_DISTANCE queries; ONNX models can run in-database, removing the external call from the backfill loop entirely.Ticket and product text can carry customer names, emails and account details. A local model keeps embeddings in-house; if you use a hosted embedding API instead, that backfill is a bulk transfer of customer data — get the data-classification sign-off first, not after.
Keep the eval set forever. Every future change — new model, new template, new index parameters — reruns the same 30 queries, and you get a regression-tested search feature instead of vibes. That habit is what separates engine-side search you operate from a demo you once gave.