Vectors and similarity search

Draft

Keyword search matches characters. Vector search matches meaning. An embedding model reads a text and returns a list of numbers, a vector, chosen so that texts with similar meaning get vectors that are close together. Searching then means: turn the question into a vector too, and return the rows whose vectors are nearest.

Real embedding models return hundreds of numbers that no human can name. To keep the math readable, every vector here has just three, from mobilebert-uncased-mnli, a small model that scores how much a text is about [databases, web, AI]. The documents and the questions were all scored by it. It isn't perfect: it rates React components as more about AI than the web. Search is only as good as its embeddings.

The lesson uses plain PostgreSQL arrays so every step of the math is visible. The pgvector lesson does the same search with a real vector type and index.

The documents

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  -- [databases, web, AI]: how much the document is about each topic.
  embedding DOUBLE PRECISION[] NOT NULL
);

INSERT INTO documents (title, body, embedding)
VALUES
  ('SQL indexes', 'Indexes help PostgreSQL find rows without reading the whole table.', ARRAY[0.95, 0.03, 0.02]),
  ('JOIN patterns', 'Joins combine rows from related tables into one result.', ARRAY[0.66, 0.17, 0.17]),
  ('Query tuning', 'Slow queries get faster with the right index and a better plan.', ARRAY[0.62, 0.15, 0.23]),
  ('React components', 'Components split a user interface into small reusable pieces.', ARRAY[0.25, 0.26, 0.49]),
  ('CSS grid layouts', 'Grid and flexbox position elements on a web page.', ARRAY[0.15, 0.82, 0.03]),
  ('Neural networks', 'Neural networks learn patterns from training data.', ARRAY[0.14, 0.08, 0.78]),
  ('Chatbots', 'Language models answer questions written in natural language.', ARRAY[0.32, 0.26, 0.42]),
  ('Vector search', 'Embeddings let a database find documents with a similar meaning.', ARRAY[0.91, 0.04, 0.04]);

The embeddings

Each document has three numbers: how much it is about databases, the web, and AI.

Loaded Database:vectors.database-init

-- ::text shows the array the way Postgres writes it.
SELECT title, embedding::text AS embedding
FROM documents;

title	embedding
SQL indexes	{0.95,0.03,0.02}
JOIN patterns	{0.66,0.17,0.17}
Query tuning	{0.62,0.15,0.23}
React components	{0.25,0.26,0.49}
CSS grid layouts	{0.15,0.82,0.03}
Neural networks	{0.14,0.08,0.78}
Chatbots	{0.32,0.26,0.42}
Vector search	{0.91,0.04,0.04}

Unavailable: EXPLAIN doesn't support this statement.

Keywords miss meaning

A user searches for 'speed up my database'. No document contains those words, so a keyword search returns nothing, even though three documents are exactly about that.

Loaded Database:vectors.database-init

SELECT title
FROM documents
WHERE title ILIKE '%speed up%'
   OR body ILIKE '%speed up%';

No rows

Seq Scan on documents  (cost=0.00..19.45 rows=1 width=32)
  Filter: ((title ~~* '%speed up%'::text) OR (body ~~* '%speed up%'::text))

The question is a vector too

The same model scores 'speed up my database' as ARRAY[0.94, 0.04, 0.03]: mostly databases. Distance is Pythagoras in three dimensions: the smaller it is, the closer the meaning.

Loaded Database:vectors.database-init

SELECT
  title,
  round(sqrt(
    power(embedding[1] - 0.94, 2) +
    power(embedding[2] - 0.04, 2) +
    power(embedding[3] - 0.03, 2)
  )::numeric, 3) AS distance
FROM documents
ORDER BY distance;

title	distance
SQL indexes	0.017
Vector search	0.032
JOIN patterns	0.339
Query tuning	0.393
Chatbots	0.765
React components	0.858
Neural networks	1.097
CSS grid layouts	1.110

Sort  (cost=62.92..64.49 rows=630 width=64)
  Sort Key: (round((sqrt(((power((embedding[1] - '0.94'::double precision), '2'::double precision) + power((embedding[2] - '0.04'::double precision), '2'::double precision)) + power((embedding[3] - '0.03'::double precision), '2'::double precision))))::numeric, 3))
  ->  Seq Scan on documents  (cost=0.00..33.62 rows=630 width=64)

Distance as a function

Writing every dimension by hand stops working at 1,536 dimensions. Two SQL functions do the same math for arrays of any length:

Loaded Database:vectors.database-init

-- unnest(a, b) zips two arrays into rows of (x, y) pairs,
-- so these work for any number of dimensions.

-- Euclidean (L2) distance: the straight-line distance between two points.
CREATE FUNCTION l2_distance(a DOUBLE PRECISION[], b DOUBLE PRECISION[])
RETURNS DOUBLE PRECISION
LANGUAGE sql IMMUTABLE
AS $$
  SELECT sqrt(sum((x - y) ^ 2)) FROM unnest(a, b) AS t(x, y)
$$;

-- Cosine similarity: 1 when two vectors point the same way, 0 when unrelated.
CREATE FUNCTION cosine_similarity(a DOUBLE PRECISION[], b DOUBLE PRECISION[])
RETURNS DOUBLE PRECISION
LANGUAGE sql IMMUTABLE
AS $$
  SELECT sum(x * y) / (sqrt(sum(x * x)) * sqrt(sum(y * y)))
  FROM unnest(a, b) AS t(x, y)
$$;

Nearest neighbours

The same math, written once as a function. ORDER BY distance plus LIMIT is the whole of vector search: the k nearest neighbours.

Loaded Database:vectors.database-init

SELECT title
FROM documents
ORDER BY l2_distance(embedding, ARRAY[0.94, 0.04, 0.03])
LIMIT 3;

title
SQL indexes
Vector search
JOIN patterns

Limit  (cost=181.94..181.95 rows=3 width=40)
  ->  Sort  (cost=181.94..183.52 rows=630 width=40)
        Sort Key: (l2_distance(embedding, '{0.94,0.04,0.03}'::double precision[]))
        ->  Seq Scan on documents  (cost=0.00..173.80 rows=630 width=40)

Cosine similarity

'AI that understands text' becomes ARRAY[0.16, 0.10, 0.75]. Cosine similarity compares the direction of two vectors and ignores their length. Most embedding models return vectors of length 1, so both measures give the same order; cosine is the usual default.

Loaded Database:vectors.database-init

SELECT
  title,
  round(l2_distance(embedding, ARRAY[0.16, 0.10, 0.75])::numeric, 3) AS distance,
  round(cosine_similarity(embedding, ARRAY[0.16, 0.10, 0.75])::numeric, 3) AS similarity
FROM documents
ORDER BY similarity DESC;

title	distance	similarity
Neural networks	0.041	0.999
React components	0.318	0.921
Chatbots	0.400	0.862
Query tuning	0.696	0.547
JOIN patterns	0.769	0.460
Vector search	1.035	0.255
SQL indexes	1.078	0.231
CSS grid layouts	1.018	0.199

Sort  (cost=366.89..368.47 rows=630 width=96)
  Sort Key: (round((cosine_similarity(embedding, '{0.16,0.1,0.75}'::double precision[]))::numeric, 3)) DESC
  ->  Seq Scan on documents  (cost=0.00..337.60 rows=630 width=96)

Set a cutoff

Nearest neighbours always returns rows, even when nothing is really close. A WHERE on the distance keeps only good matches.

Loaded Database:vectors.database-init

SELECT title
FROM documents
WHERE l2_distance(embedding, ARRAY[0.16, 0.10, 0.75]) < 0.3
ORDER BY l2_distance(embedding, ARRAY[0.16, 0.10, 0.75]);

title
Neural networks

Sort  (cost=235.97..236.50 rows=210 width=40)
  Sort Key: (l2_distance(embedding, '{0.16,0.1,0.75}'::double precision[]))
  ->  Seq Scan on documents  (cost=0.00..227.88 rows=210 width=40)
        Filter: (l2_distance(embedding, '{0.16,0.1,0.75}'::double precision[]) < '0.3'::double precision)

More like this

A 'related articles' box: use a stored document's embedding as the question, and skip the document itself.

Loaded Database:vectors.database-init

SELECT other.title
FROM documents AS source
JOIN documents AS other ON other.id <> source.id
WHERE source.title = 'Vector search'
ORDER BY cosine_similarity(other.embedding, source.embedding) DESC
LIMIT 3;

title
SQL indexes
JOIN patterns
Query tuning

Limit  (cost=558.67..558.68 rows=3 width=40)
  ->  Sort  (cost=558.67..563.39 rows=1887 width=40)
        Sort Key: (cosine_similarity(other.embedding, source.embedding)) DESC
        ->  Nested Loop  (cost=0.00..534.28 rows=1887 width=40)
              Join Filter: (other.id <> source.id)
              ->  Seq Scan on documents other  (cost=0.00..16.30 rows=630 width=68)
              ->  Materialize  (cost=0.00..17.89 rows=3 width=36)
                    ->  Seq Scan on documents source  (cost=0.00..17.88 rows=3 width=36)
                          Filter: (title = 'Vector search'::text)

Why arrays are not enough

Look at the plans above: every search is a Seq Scan followed by a Sort. Postgres computes the distance for every row, and nothing can index an expression like l2_distance(embedding, ...). Fine for eight rows, slow for a million. Arrays also don't check that every embedding has the same number of dimensions.

The pgvector lesson fixes both, with the same documents and the same questions. To see how vectors compare with keyword, fuzzy, and full-text search, read Text search in Postgres.

What We Learned

Embeddinga list of numbers produced by a model, so that texts with similar meaning get nearby vectors.
L2 distancethe straight-line distance between two vectors: smaller is closer.
Cosine similaritycompares the direction of two vectors, ignoring their length: 1 is identical.
Nearest neighboursORDER BY distance LIMIT k: the whole of vector search.
Cutoffnearest neighbours always return rows; a WHERE on the distance keeps only good ones.
pgvectorthe PostgreSQL extension that makes all of this fast.