Vector Search with pgvector
Use the pgvector extension to store embeddings and run approximate nearest neighbour search with ivfflat / HNSW.
Vector Search with pgvector is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Are Vectors For?
Embeddings encode meaning. Two documents about the same topic have similar vectors. Vector search powers semantic search, RAG (retrieval-augmented generation), and recommendation.
Enable pgvector
The extension is widely available (RDS, Cloud SQL, Supabase have it built in):
CREATE EXTENSION vector;Vector Column
Define a column with the dimensionality of your embedding model:
CREATE TABLE docs (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
embedding vector(1536)
);
-- 1536 is OpenAI text-embedding-3-small dimension; varies by model.Inserting Embeddings
Insert the vector as an array literal:
INSERT INTO docs (title, body, embedding)
VALUES ('SQL Course', 'Learn SQL', '[0.013, -0.024, ...]');Distance Operators
pgvector provides three:
<->— Euclidean (L2) distance<#>— negative inner product<=>— cosine distance (most common for embeddings)
Nearest Neighbour Search
Find the closest docs to a query vector:
SELECT id, title, 1 - (embedding <=> $1) AS similarity
FROM docs
ORDER BY embedding <=> $1
LIMIT 10;IVFFlat Index
For large datasets, build an approximate nearest neighbour (ANN) index:
CREATE INDEX docs_embedding_idx
ON docs USING IVFFLAT (embedding vector_cosine_ops)
WITH (lists = 100);
-- Tune lists by row count: rule of thumb sqrt(N).
-- Set probes per query for accuracy:
SET ivfflat.probes = 10;HNSW Index (PG 16+)
Better recall/latency than IVFFlat in many cases:
CREATE INDEX docs_embedding_hnsw
ON docs USING HNSW (embedding vector_cosine_ops);
SET hnsw.ef_search = 100;Filtered Vector Search
Combine vector and structured filters:
SELECT id, title
FROM docs
WHERE language = 'en'
AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY embedding <=> $1
LIMIT 10;
-- For best perf, partial index on the filter.RAG Pattern
Retrieve N relevant docs by embedding, feed them as context to an LLM:
-- 1. Embed user query
-- 2. SELECT top-N docs by cosine
-- 3. Send docs + query to LLM
-- 4. Return LLM's answer to userHybrid Search
Combine vector + lexical (tsvector) for best results — neither alone catches everything:
SELECT id, title,
0.6 * (1 - (embedding <=> $1)) + 0.4 * ts_rank(search_doc, $2) AS score
FROM docs
WHERE embedding <=> $1 < 0.7
OR search_doc @@ $2
ORDER BY score DESC LIMIT 10;Dimensionality Choice
Bigger embeddings = more accurate but more storage and slower. Many apps run fine on 384–768 dim models. Pick a model and stick with it.
Recap
pgvector adds first-class vector search to Postgres.
- vector(N) column type
- IVFFlat / HNSW indexes
- cosine distance via <=> for embeddings
- Hybrid with FTS for best recall
Quick Check
You're searching by semantic similarity using OpenAI embeddings. Which pgvector distance operator do you typically use?
Frequently asked questions
Is the “Vector Search with pgvector” lesson free?
Yes — the full text of “Vector Search with pgvector” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Vector Search with pgvector”?
Use the pgvector extension to store embeddings and run approximate nearest neighbour search with ivfflat / HNSW. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Vector Search with pgvector” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.