pgvector

The documents

-- CREATE EXTENSION also makes PGlite load pgvector for this database.
CREATE EXTENSION vector;

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  -- Exactly 3 dimensions: [databases, web, AI].
  embedding vector(3) NOT NULL
);

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

Same question, one operator

'speed up my database' is '[0.94, 0.04, 0.03]'. <-> is the L2 distance: the same l2_distance the vectors lesson wrote by hand, and the same three results.

SELECT
  title,
  round((embedding <-> '[0.94, 0.04, 0.03]')::numeric, 3) AS distance
FROM documents
ORDER BY embedding <-> '[0.94, 0.04, 0.03]'
LIMIT 3;

Limit  (cost=30.74..30.75 rows=3 width=72)
  ->  Sort  (cost=30.74..32.32 rows=630 width=72)
        Sort Key: ((embedding <-> '[0.94,0.04,0.03]'::vector))
        ->  Seq Scan on documents  (cost=0.00..22.60 rows=630 width=72)

The distance operators

'AI that understands text' is '[0.16, 0.10, 0.75]'. <=> is cosine distance (1 - cosine similarity). <#> is the negative inner product, negative so that ascending order still means closest first. All three: smaller is closer.

SELECT
  title,
  round((embedding <-> '[0.16, 0.10, 0.75]')::numeric, 3) AS l2,
  round((embedding <=> '[0.16, 0.10, 0.75]')::numeric, 3) AS cosine,
  round((embedding <#> '[0.16, 0.10, 0.75]')::numeric, 3) AS inner_product
FROM documents
ORDER BY embedding <=> '[0.16, 0.10, 0.75]';

Sort  (cost=61.34..62.92 rows=630 width=136)
  Sort Key: ((embedding <=> '[0.16,0.1,0.75]'::vector))
  ->  Seq Scan on documents  (cost=0.00..32.05 rows=630 width=136)

Which operator?

Use the one your embedding model was trained for; its documentation says. Most text models, including OpenAI's, return vectors of length 1, and then all three give the same order. <=> is the safe default. With length-1 vectors, <#> gives the same order and skips a division.

A real type

vector(3) is checked: inserting '[1, 2]' fails with 'expected 3 dimensions, not 2'. pgvector also ships helper functions, and casts to and from arrays.

SELECT
  title,
  vector_dims(embedding) AS dims,
  round(vector_norm(embedding)::numeric, 3) AS length,
  embedding::real[]::text AS as_array
FROM documents
LIMIT 3;

Limit  (cost=0.00..0.13 rows=3 width=100)
  ->  Seq Scan on documents  (cost=0.00..27.33 rows=630 width=100)

More like this

Related documents, with the stored embedding as the question. It's still SQL: joins and WHERE work as usual.

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

Limit  (cost=91.64..91.65 rows=3 width=40)
  ->  Sort  (cost=91.64..96.36 rows=1887 width=40)
        Sort Key: ((other.embedding <=> source.embedding))
        ->  Nested Loop  (cost=0.00..67.25 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)

Exact search: no index

Without a vector index, Postgres measures the distance to every row and sorts. A B-tree can't help: "closest to this point in 1,536 dimensions" has no sort order. Exact search is always right, and fine up to tens of thousands of rows.

Exact search on 5,008 rows

No vector index: the plan is a Seq Scan and a Sort. Postgres measures the distance to all 5,008 rows and sorts them. Slow on big tables, but always the true nearest rows: Vector search and SQL indexes come first.

SELECT title
FROM documents
ORDER BY embedding <=> '[0.94, 0.04, 0.03]'
LIMIT 5;

Limit  (cost=212.78..212.79 rows=5 width=32)
  ->  Sort  (cost=212.78..225.30 rows=5008 width=32)
        Sort Key: ((embedding <=> '[0.94,0.04,0.03]'::vector))
        ->  Seq Scan on documents  (cost=0.00..129.60 rows=5008 width=32)

The same search with HNSW

Same rows, same query, with an HNSW index. The plan is an Index Scan whose Order By is the distance: Postgres walks the graph instead of sorting every row. Compare the results with the exact search: an approximate index can miss some of the true nearest rows.

SELECT title
FROM documents
ORDER BY embedding <=> '[0.94, 0.04, 0.03]'
LIMIT 5;

Limit  (cost=83.96..85.03 rows=5 width=32)
  ->  Index Scan using documents_embedding_idx on documents  (cost=83.96..1156.16 rows=5008 width=32)
        Order By: (embedding <=> '[0.94,0.04,0.03]'::vector)

Approximate, on purpose

To be fast, an approximate index can miss a true neighbour now and then. SET hnsw.ef_search = 100 (default 40) searches more of the graph: better recall, slower queries. With a WHERE filter, the index returns the nearest rows first and the filter runs after, so a very selective filter can return fewer rows than the LIMIT; recent pgvector versions add hnsw.iterative_scan for that.

The same search with IVFFlat

Same rows, same query, with an IVFFlat index of 50 lists. The plan is an Index Scan again. With the default ivfflat.probes = 1 it reads only the nearest list, so a true neighbour sitting in the next list can be missed. Here it isn't: compare with the exact search.

SELECT title
FROM documents
ORDER BY embedding <=> '[0.94, 0.04, 0.03]'
LIMIT 5;

Limit  (cost=3.35..3.85 rows=5 width=32)
  ->  Index Scan using documents_embedding_ivfflat_idx on documents  (cost=3.35..498.16 rows=5008 width=32)
        Order By: (embedding <=> '[0.94,0.04,0.03]'::vector)

halfvec and bits

The same embeddings in smaller types. halfvec keeps 16 bits per number: 0.95 becomes 0.9501953, close enough. binary_quantize keeps 1 bit per number, positive or not: every document here becomes 111, because the model never scores a topic exactly 0.

SELECT
  title,
  embedding::halfvec(3)::text AS half,
  binary_quantize(embedding)::text AS bits
FROM documents;

Seq Scan on documents  (cost=0.00..25.75 rows=630 width=96)

Bits first, then re-rank

The usual pattern: let the cheap bit distance (<~>, Hamming) pick a few candidates, then order only those by the full vectors. Here it fails: all eight documents are 111, so the 6 candidates are arbitrary and Vector search, the true nearest, isn't among them. With 1,536-number vectors the bits differ and the candidates are good.

SELECT title
FROM (
  SELECT title, embedding
  FROM documents
  ORDER BY binary_quantize(embedding)::bit(3) <~> binary_quantize('[0.94, 0.04, 0.03]'::vector(3))
  LIMIT 6
) AS candidates
ORDER BY embedding <=> '[0.94, 0.04, 0.03]'
LIMIT 3;

Limit  (cost=32.49..32.49 rows=3 width=40)
  ->  Sort  (cost=32.49..32.50 rows=6 width=40)
        Sort Key: ((candidates.embedding <=> '[0.94,0.04,0.03]'::vector))
        ->  Subquery Scan on candidates  (cost=32.32..32.41 rows=6 width=40)
              ->  Limit  (cost=32.32..32.33 rows=6 width=72)
                    ->  Sort  (cost=32.32..33.89 rows=630 width=72)
                          Sort Key: (((binary_quantize(documents.embedding))::bit(3) <~> '111'::"bit"))
                          ->  Seq Scan on documents  (cost=0.00..21.02 rows=630 width=72)

Indexing smaller vectors

You don't need a second column: index an expression that casts the embedding, and use the same expression in the query.

-- Index a smaller copy of the vector, without storing a second column.
-- halfvec: 16-bit numbers. Half the index size, almost the same results.
CREATE INDEX ON documents
USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);

-- bit: 1 bit per number (positive or not). 32x smaller, much rougher.
CREATE INDEX ON documents
USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

-- Queries must use the same expression to hit the index:
SELECT title FROM documents
ORDER BY embedding::halfvec(1536) <=> $1::halfvec(1536)
LIMIT 10;