PostgreSQL Mastery lessons / Module 43: Vectors and pgvector

Embeddings and the vector type

Level 9: AI and Vector Search25 min

An embedding model turns text, images or products into vectors so that similar things end up close together. pgvector adds a vector type and distance operators to PostgreSQL, so semantic search sits next to the rows, joins, filters and transactions you already have.

This lesson is interactive. Run every example on a real PostgreSQL 18 server in your browser, read EXPLAIN ANALYZE plans, and take the quiz.

Open the interactive lesson

You will learn to

Store embeddings in vector columns and query them with distance operators
Choose between L2, cosine and inner-product distance
Use the course embedding function to see how nearest-neighbour search behaves

conceptThe concept

CREATE EXTENSION vector adds the vector(n) type (32-bit floats), plus halfvec, sparsevec and bit. Distance operators:

Operator	Distance	Use when
`<->`	Euclidean (L2)	The model's documentation says L2
`<=>`	Cosine distance (1 minus cosine similarity)	Most text embedding models
`<#>`	Negative inner product	Normalized vectors, where it ranks like cosine and is cheaper
`<+>`	Manhattan (L1)	Rarely, for specific models

Nearest-neighbour search is ORDER BY embedding <=> query_vector LIMIT k. Without an index that is an exact scan of every row; the next lesson adds approximate indexes.

This course cannot call a real embedding model from your browser, so the shop has pgm.embed(text): a deliberately simple function that scores eight support topics (shipping, returns, billing, account, warranty, battery, sizing, privacy) by keyword and normalizes the result. Real models return 384 to 3072 learned dimensions, but every query in this module works the same way with them.

whyWhy it matters

Keyword search misses "my parcel never came" when the article says "delivery". Embeddings capture meaning, so similar questions land near the same answers. Keeping them in PostgreSQL avoids a second database, a sync pipeline and the consistency bugs between them.

Use caseReal-world scenario

A help centre stores article chunks with their embeddings in kb_chunks. A customer types a question; the application embeds it with the same model and asks PostgreSQL for the five nearest chunks, filtered to articles the customer may see.

Try itExamples

Run and edit these in the interactive lesson.

Distance operators

SELECT '[1,2,3]'::vector <-> '[4,6,3]'::vector AS l2,
       round(('[1,0]'::vector <=> '[0,1]'::vector)::numeric, 3) AS cosine_orthogonal,
       round(('[1,1]'::vector <=> '[2,2]'::vector)::numeric, 3) AS cosine_same_direction,
       '[1,2,3]'::vector <#> '[1,1,1]'::vector AS negative_inner_product;

Cosine distance ignores length: [1,1] and [2,2] point the same way, so their distance is 0.

What the course embedding looks like

SELECT pgm.embed('Where is my parcel? Tracking says delivered') AS embedding,
       vector_dims(pgm.embed('anything')) AS dims,
       round(vector_norm(pgm.embed('refund to my card'))::numeric, 3) AS norm;

The largest value is the shipping dimension. Normalized vectors have length 1, so cosine and inner product rank the same.

Semantic search over the help centre

SELECT a.title, c.chunk_no,
       round((c.embedding <=> pgm.embed('my refund has not arrived'))::numeric, 3) AS distance
FROM kb_chunks c
JOIN kb_articles a ON a.id = c.article_id
ORDER BY c.embedding <=> pgm.embed('my refund has not arrived')
LIMIT 5;

The question never says "return", yet the return article ranks first. Notice the internal agents article in the results too: the hybrid search lesson filters it out.

Embedding on write with a generated column

BEGIN;
CREATE TABLE faq (
  id         integer PRIMARY KEY,
  question   text NOT NULL,
  embedding  vector(8) GENERATED ALWAYS AS (pgm.embed(question)) STORED
);
INSERT INTO faq (id, question) VALUES
  (1, 'How long does delivery take?'),
  (2, 'Can I get a refund on a sale item?'),
  (3, 'How do I change my password?');
SELECT id, question
FROM faq
ORDER BY embedding <=> pgm.embed('forgot my login password')
LIMIT 1;
ROLLBACK;

This works because pgm.embed is a pure SQL function. Real models are remote API calls, so applications embed before inserting, or a background worker fills a nullable column.

internalsUnder the hood

A vector(n) value is stored as a varlena: an 8-byte header plus 4 bytes per dimension, so a 1536-dimension embedding is about 6 KB and is TOASTed out of line. That makes exact scans expensive at scale and is why dimension count, halfvec and quantization matter for large tables.

Avoid / preferDo this, not that

Avoid

SELECT id FROM kb_chunks
ORDER BY 1 - (embedding <=> $1) DESC
LIMIT 5;

Prefer

SELECT id FROM kb_chunks
ORDER BY embedding <=> $1
LIMIT 5;

Indexes can only serve ORDER BY on the bare distance operator in ascending order. Compute similarity in the SELECT list if you need to show it.

Avoid

-- embed documents with model A, queries with model B

Prefer

-- one model per column; store the model name and re-embed everything when it changes

Vectors from different models live in unrelated spaces. Mixing them returns confident nonsense.

PitfallsCommon mistakes

Using L2 distance with a model trained for cosine similarity, or the reverse.
Declaring vector without dimensions and inserting embeddings of different sizes from two models.
Comparing distances across different queries as if they were absolute relevance scores.

LimitsWhen not to use it

Do not use vectors for exact lookups (order numbers, SKUs, email addresses) or for short structured filters; B-tree indexes and keyword search are faster, cheaper and exact. Embeddings shine on free text and fuzzy similarity.

SecuritySecurity notes

Embeddings can leak information about the text they came from; treat them as sensitive as the source. Never send restricted documents to a third-party embedding API without approval, and keep row-level access rules on the tables that hold vectors.

ChallengeTry it yourself

For every article, find the most similar other article. Represent each article by the average of its chunk embeddings.

Show one solution

One solution

WITH a AS (
  SELECT article_id, avg(embedding) AS centroid
  FROM kb_chunks
  GROUP BY article_id
)
SELECT x.article_id, nn.article_id AS nearest, round(nn.d::numeric, 3) AS distance
FROM a x
CROSS JOIN LATERAL (
  SELECT y.article_id, x.centroid <=> y.centroid AS d
  FROM a y
  WHERE y.article_id <> x.article_id
  ORDER BY 2
  LIMIT 1
) nn
ORDER BY x.article_id;

InterviewQuestions you should be able to answer

Why store embeddings in PostgreSQL instead of a dedicated vector database?

The vectors live next to the data they describe, so filters, joins, permissions, transactions and backups all work as usual and there is no sync pipeline to keep consistent. pgvector offers HNSW and IVFFlat indexes and performs well into tens of millions of vectors on one server. Dedicated vector databases make sense at very large scale, for specialised features, or when the team wants search fully separated from the transactional database.

Cosine distance, L2 or inner product?

Use what the embedding model was trained for; most text models use cosine. When vectors are normalized to length 1, cosine distance, L2 and inner product all produce the same ranking, and inner product (<#> in pgvector) is the cheapest to compute. The index operator class must match the operator you query with.

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

← Capacity planning from your own data HNSW and IVFFlat indexes →