Searching by meaning
In short: Vector search turns text into a vector of numbers (an embedding) and finds the records closest in meaning by cosine similarity.
Problem
"Something for breakfast"
A customer types "something for breakfast" into the shop's search. The word is not in the product names:
The full-text search from the previous lesson finds only the review "The wholegrain bread is great for breakfast". Yet milk, a roll, coffee and banitsa are exactly what the customer wants. They are looking for meaning, not a word. Synonyms, translations and "something for…" are not caught with words.
The idea
A text as a point
A language model (an embedding model) turns a text into a vector: a list of numbers. It is trained so that texts with close meaning give close vectors. "Milk", "roll" and "something for breakfast" are close, and "laundry powder" is far.
Real models give from a few hundred to a few thousand numbers, and none of them has a name. Here, so that you can see what happens, the vectors are made up and have only four named dimensions: breakfast, sweet, cooking and cleaning. The table product_vectors keeps the vector of every product as a JSON array:
The roll is very much "breakfast" and a little "sweet". The potatoes are only "cooking", and the laundry powder only "cleaning".
More precisely, if you are curious
The vector is computed once, when the product is added or its description changes, and it is kept in the database. The customer's question is turned into a vector with the same model on every search. The two vectors must come from the same model: vectors from different models cannot be compared. There are open models (for example from the sentence-transformers family, which run on an ordinary computer) and paid services.
The idea
How close: the cosine
Two vectors are close when they point in the same direction. The measure is the cosine similarity: 1 means the same direction, 0 means nothing in common. It is computed like this: multiply the numbers in the same places and add them up (the dot product), then divide by the lengths of the two vectors.
json_each unfolds a JSON array into rows: key is the place, and value is the number. The white cheese against the question "something for breakfast" ([1, 0.2, 0.1, 0], as the model would give it):
The sum of the last column is 0.66. Divided by the lengths (0.849 for the cheese and 1.025 for the question), it gives 0.759. The same for all the products, from the closest down:
WITH q(v) gives the vector of the question. The two json_each are joined by the place, and GROUP BY adds up the products for each item. None of these products has the word "breakfast" in its name.
Try it
Something sweet for breakfast
The model has turned the question "something sweet for breakfast" into the vector [0.7, 0.9, 0, 0]. Find the three closest products (columns name and similarity, rounded to 0.001), from the closest down.
Solution
WITH q(v) AS (SELECT '[0.7, 0.9, 0, 0]')
SELECT p.name,
ROUND(SUM(a.value * b.value)
/ (sqrt(SUM(a.value * a.value)) * sqrt(SUM(b.value * b.value))), 3) AS similarity
FROM product_vectors pv
JOIN products p ON p.id = pv.product_id
CROSS JOIN q
JOIN json_each(pv.embedding) a
JOIN json_each(q.v) b ON b.key = a.key
GROUP BY p.id, p.name
ORDER BY similarity DESC
LIMIT 3;The same query with '[0.7, 0.9, 0, 0]' in WITH q(v). With ORDER BY similarity without DESC the farthest come out: the cleaning and kitchen products, with similarity 0. And the vector of "something for breakfast" gives coffee and banitsa, which are not sweet.
The idea
Filter before the ordering
Search by meaning often also has ordinary conditions: under some price, only in stock, only in a category. The condition goes in WHERE, that is before GROUP BY, ORDER BY and LIMIT. If you first take the closest three and then filter, nothing may be left: they are coffee for 14.90 €, banitsa for 2.50 € and milk for 1.60 €.
More precisely, if you are curious
Vector indexes (the next card) have exactly this problem. The approximate search returns, say, the closest 100, and if the filter comes after it while the cheap products are not among those 100, the result is empty. That is why vector databases have filters that are applied during the search, and pgvector has settings like hnsw.ef_search and iterative scans (hnsw.iterative_scan).
Exercise
Cheap and similar
1. The customer has chosen "up to 1.50 €".
Find the three products closest to "something for breakfast" ([1, 0.2, 0.1, 0]) with a price under 1.50 € (columns name and similarity), from the closest down.
Solution
WITH q(v) AS (SELECT '[1, 0.2, 0.1, 0]')
SELECT p.name,
ROUND(SUM(a.value * b.value)
/ (sqrt(SUM(a.value * a.value)) * sqrt(SUM(b.value * b.value))), 3) AS similarity
FROM product_vectors pv
JOIN products p ON p.id = pv.product_id
CROSS JOIN q
JOIN json_each(pv.embedding) a
JOIN json_each(q.v) b ON b.key = a.key
WHERE p.price < 1.5
GROUP BY p.id, p.name
ORDER BY similarity DESC
LIMIT 3;WHERE p.price < 1.5 before GROUP BY. If you first take the closest three and then filter by price, not one of them is left: the coffee, the banitsa and the milk are over 1.50 €.
2. The product page shows "similar products". The vector of the question is the vector of the product itself.
Find the three products most similar to the white cheese (product 7), without the cheese itself (columns name and similarity), from the closest down.
Solution
WITH q(v) AS (SELECT embedding FROM product_vectors WHERE product_id = 7)
SELECT p.name,
ROUND(SUM(a.value * b.value)
/ (sqrt(SUM(a.value * a.value)) * sqrt(SUM(b.value * b.value))), 3) AS similarity
FROM product_vectors pv
JOIN products p ON p.id = pv.product_id
CROSS JOIN q
JOIN json_each(pv.embedding) a
JOIN json_each(q.v) b ON b.key = a.key
WHERE p.id <> 7
GROUP BY p.id, p.name
ORDER BY similarity DESC
LIMIT 3;WITH q(v) AS (SELECT embedding FROM product_vectors WHERE product_id = 7) and WHERE p.id <> 7. Without the condition the first place goes to the cheese itself, with similarity 1.
The idea
Millions of vectors: an approximate index
The query compares the question with every product. The plan shows SCAN p:
With 23 products of 4 numbers each this is nothing. With 10 million documents of 1536 numbers each, every search is 15 billion multiplications. An ordinary index does not help: it sorts by one value, while "close" in vectors is in hundreds of directions at once.
That is why vector databases use approximate indexes (ANN, approximate nearest neighbours). The most widespread is HNSW: a graph in which every vector is linked to its close ones, on several levels. The search starts at the top, sparse level and goes down, always moving to closer neighbours. It finds the closest in milliseconds, but sometimes misses one of them. The share of the true closest that the index finds is the recall, and it is tuned against the speed.
The idea
Where the vectors live
The simplest is for the vectors to sit next to the data. pgvector is an extension of PostgreSQL:
CREATE EXTENSION vector;CREATE TABLE product_vectors ( product_id integer PRIMARY KEY REFERENCES products(id), embedding vector(384));CREATE INDEX ON product_vectors USING hnsw (embedding vector_cosine_ops);
-- <=> is the cosine distance (1 minus the similarity)SELECT p.nameFROM product_vectors pv JOIN products p ON p.id = pv.product_idWHERE p.price < 1.5ORDER BY pv.embedding <=> $1LIMIT 3;Separate vector databases (Qdrant, Weaviate, Milvus, Pinecone) pay off with hundreds of millions of vectors or when search by meaning is the main job. Elasticsearch, OpenSearch and Redis also have vector search.
The most common use today is RAG (retrieval-augmented generation): the shop's chatbot turns the question into a vector, finds the closest pieces of descriptions, reviews and rules and gives them to the language model, so that it answers from them and not from memory. Search by meaning is often mixed with full-text search: the words catch exact names and codes, and the vectors the meaning.
Test