What you will build
You will create a small semantic document search in PostgreSQL, inspect ranked matches and decide when an approximate index is useful.
A refund question without the words “refund policy”
A learner asks, “Can I get my money back?” Keyword search may miss a document titled “Cancellation and refund policy.” An embedding represents meaning as numbers, allowing similar meanings to be placed near one another.
Text becomes a vector before SQL searches it
The application uses the same embedding model for stored documents and the incoming query. That model determines the vector dimension. A vector(1536) column cannot accept a vector with a different number of dimensions.
Enable pgvector and create the table
01CREATE EXTENSION IF NOT EXISTS vector;0203CREATE TABLE knowledge_chunks (04chunk_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,05document_id bigint NOT NULL,06content text NOT NULL,07embedding vector(1536) NOT NULL08);
Store the source text and its embedding together so a retrieved vector can still be traced to human-readable evidence.
Rank by cosine distance
01SELECT chunk_id,02content,031 - (embedding <=> $1::vector) AS similarity04FROM knowledge_chunks05ORDER BY embedding <=> $1::vector06LIMIT 3;
<=> is pgvector’s cosine-distance operator. Smaller distance means closer vectors; the example converts it to a similarity-style value for display. $1 is a parameter supplied by the application—not raw text concatenated into SQL.
Example result
| chunk_id | content | similarity |
|---|---|---|
| 42 | Refunds are available within the stated return window… | 0.91 |
| 18 | Cancel an order before dispatch… | 0.72 |
| 67 | Product warranty coverage… | 0.48 |
Add HNSW when the workload needs it
01CREATE INDEX knowledge_chunks_embedding_hnsw02ON knowledge_chunks03USING hnsw (embedding vector_cosine_ops);
Without an approximate index, PostgreSQL can compare the query with every vector and return exact nearest neighbours. HNSW can reduce search work on larger collections, but speed, memory, build time and recall must be measured with representative data.
Now use the lab to compare those two paths. Change top-k only after you understand that it controls result count, while the index toggle changes how candidates are found.
Real-world application: grounded policy assistant
The application embeds a question, retrieves the closest authorised policy chunks and gives only those chunks to the language model. Vector search finds candidates; it does not prove that the final answer is correct. The app still needs access control, relevance thresholds, citations and evaluation.
pgvector or a separate vector service?
PostgreSQL with pgvector is attractive when structured records, permissions, transactions and vectors belong together. A specialised vector service may be appropriate when scale, distributed search, operational ownership or retrieval features require it. The decision depends on measured workload—not one universal row-count rule.
Common vector-search mistakes
Common mistakes
- Storing vectors from different embedding models in one comparable column.
- Copying an ellipsis such as
[0.12, ...]into supposedly runnable SQL. - Creating an index with an operator class that does not match the query metric.
- Treating similarity as factual correctness.
- Adding approximate search without measuring recall and latency.
Practice the retrieval design
Design a support_articles table with article_id, title, content and embedding vector(768). Write a parameterised cosine-distance query that returns five matches.
Choose an answer, inspect the explanation and explain the idea in your own words.
Why must stored and query embeddings use the same model and dimension?
Interview-ready explanation
Exact search vs HNSW
Exact search compares the query against every eligible vector and returns the true nearest neighbours for that metric. HNSW searches an index for likely neighbours, usually improving latency at scale while introducing memory, build-time and recall trade-offs.
Questions beginners usually ask
Is similarity the same as confidence? No. It measures closeness in an embedding space, not whether the passage or final answer is factually correct.
Do I always need HNSW? No. Start with correctness and measurement. Add an approximate index when representative latency, scale and recall tests justify it.
What to remember
Key takeaways
- Embeddings enable meaning-based retrieval; SQL ranks vectors by a chosen distance metric.
- pgvector keeps relational data and vector retrieval in PostgreSQL.
- Exact search and approximate indexes have different performance and recall trade-offs.
- Retrieval still needs authorisation, evaluation and traceable source content.
Next Byte: use SQL to turn event history into reproducible, point-in-time-correct machine-learning features.