pgvector: storing and querying vectors
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
store embeddings in PostgreSQL and run a nearest-neighbour query.
Planned topics
- pgvector adds a
vector(n)column type and distance operators; enable it withCREATE EXTENSION vector. - The dimension
nmust match the model; keep amodelcolumn so you know what produced each row. - Query: order by distance,
LIMIT k. Without an index this is an exact scan, fine for small tables. - Embeddings stay next to relational data: filter by language or product with a normal
WHERE. - Store source text, a content hash, and the article id with the vector so rows can be refreshed.
Draft outline details
Demonstration
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE help_chunk (
id bigserial PRIMARY KEY, article_id bigint NOT NULL, lang text NOT NULL,
content text NOT NULL, content_hash text NOT NULL,
model text NOT NULL, embedding vector(768) NOT NULL);
SELECT id, article_id, embedding <=> :q AS distance
FROM help_chunk WHERE lang = 'en'
ORDER BY embedding <=> :q LIMIT 5;Dimension 768 is an example; verify against the model.
Check your understanding
Why store a model column?
Show answer
To detect and re-embed rows made with a different model.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.