Vector Databases (pgvector)

Store your embeddings and find the closest ones in milliseconds — without standing up a whole new database.

Building with AI · Lesson 14 · 11 min read

You embedded all 50,000 of your help articles (last lesson). A user query arrives — also as a vector — and you need the 5 closest articles, in milliseconds, for every request. Looping through all 50,000 vectors on each query won’t scale, and you’d rather not run a second, exotic database just for this. So: where do the vectors live, and how do you find the nearest ones fast?

A vector database stores vectors and finds nearest neighbors

A vector database does two jobs: store a pile of vectors (each with some metadata — an id, a title, which user it belongs to) and, given a query vector, return the top-k closest ones by a distance measure (cosine, etc. — from last lesson). That “find the k nearest points” operation is the whole game. Everything else — the embedding model, the language model — sits around this lookup.

Exact vs approximate: the speed/accuracy trade

The simplest way to find the nearest vectors is brute force: compare the query to every stored vector and sort. It’s perfectly accurate, but the cost grows with the number of vectors — fine for thousands, painful for millions. So vector databases add an ANN index (Approximate Nearest Neighbor, e.g. HNSW or IVFFlat) that pre-organizes vectors so a query only examines a small slice of them. You get almost the true nearest neighbors, far faster. The knob you’re turning is recall vs speed: check more candidates for higher accuracy, fewer for more speed.

Worked example

Find nearest among vectors:
• 2,000 vectors, brute force: compare to all 2,000 — sub-millisecond, no index needed.
• 5,000,000 vectors, brute force: compare to all 5,000,000 every request — too slow.
• 5,000,000 vectors, HNSW index: examine a few thousand candidates, return the top 5 in milliseconds, occasionally missing a true-nearest by a hair. Worth it.

pgvector: vectors inside the database you already have

pgvector is an extension that teaches Postgres about vectors: a vector column type, distance operators (<=> for cosine distance, <-> for Euclidean), and ANN indexes. The payoff is that your embeddings live right next to your normal data. In one SQL query you can filter by metadata and sort by similarity — “the 5 nearest articles for this user and in English.” No separate service to sync, one source of truth, real transactions and joins. (Supabase ships pgvector, which is why this track uses it.) A dedicated vector database can win at extreme scale, but for most apps, “just use Postgres” is the simpler, cheaper start.

An everyday analogy

Finding the nearest coffee shop. Brute force is measuring the straight-line distance to every café in the whole city, then picking the smallest — accurate but exhausting. An ANN index is the city already divided into neighborhoods: you only check cafés in your block and the ones next door. You almost always find the true nearest, in a fraction of the effort. pgvector just keeps that map in the same building as all your other records, so you never leave the office to use it.

Worked example

A real pgvector query for the help-desk search:
``sql
SELECT id, title
FROM articles
WHERE locale = 'en'        -- metadata filter
ORDER BY embedding <=> $1  -- cosine distance to the query vector
LIMIT 5;                   -- top 5 nearest
`
1. $1 is the query’s embedding (computed the same way as the documents).
2. <=> measures cosine distance between each row’s embedding and the query.
3. ORDER BY ... ASC puts the closest (most similar) rows first.
4. LIMIT 5 returns just the top 5; WHERE locale = 'en' keeps it to English docs.
With an HNSW index on embedding, this stays fast even as articles` grows into the millions — and it’s plain SQL in the database you already run.

This is the reading. The interactive version — active-recall quiz, a hands-on experiment you run in your own AI, and an earned mastery check — is free in the app.

Start this lesson free →

← Embeddings & Meaning Build a RAG Pipeline →