Vector and Similarity Search

Reviewed & published by Brayan K

Modern apps search by meaning, not just keywords: "find documents like this one", "recommend similar products", "answer from my docs" (RAG). The trick is to store an embedding — a numeric vector from an ML model — next to each row, then find rows whose vectors are closest. This lesson covers the pgvector extension end to end: a vector(1536) column, the distance operators (<->, <=>, <#>), k-nearest-neighbour queries, and the approximate indexes (HNSW, IVFFlat) that make it fast at scale.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• What embeddings are and where they come from
• Enable pgvector and declare a vector(n) column
• Store embeddings alongside your normal data
• Measure similarity with the <->, <=>, and <#> operators
• Run k-nearest-neighbour search with ORDER BY ... LIMIT k
• Speed it up with HNSW and IVFFlat approximate indexes

The Big Idea: Meaning as Numbers

An embedding is a list of numbers (a vector) that an ML model produces to represent the meaning of a piece of text, an image, or audio. Similar meanings land at nearby points in that high-dimensional space, so "search by meaning" becomes "find the nearest vectors".

Real embeddings have hundreds or thousands of dimensions (e.g. 1536). The model is what assigns the numbers — Postgres just stores and compares them.

You do not need an extension to see why this works. The two blocks below store those same three vectors as ordinary numeric columns and work out the distance by hand, in SQL that runs anywhere — so you can watch "meaning" become a number before pgvector makes it short and fast.

-- The three texts from the table above, stored as ordinary numbers, so
-- that plain SQL — no extension, no special type — can already compare them.
-- Real embeddings have 1536 dimensions; these are shortened to 4 so the
-- arithmetic stays readable, one column per dimension.
CREATE TABLE doc_vectors (
    label TEXT,
    d1 REAL, d2 REAL, d3 REAL, d4 REAL
);

INSERT INTO doc_vectors (label, d1, d2, d3, d4) VALUES
    ('return policy',          0.12, -0.03,  0.88, 0.41),
    ('how do I get a refund?', 0.10,  0.02,  0.85, 0.40),
    ('weather forecast',      -0.71,  0.55, -0.10, 0.02);

-- A closing SELECT, so you can see the vectors that went in. SQL prints
-- 0.10 back as 0.1 and 0.40 as 0.4 — the same numbers, just without the
-- trailing zero.
SELECT label, d1, d2, d3, d4 FROM doc_vectors;

-- ✅ Expected result:
-- label | d1 | d2 | d3 | d4
-- return policy | 0.12 | -0.03 | 0.88 | 0.41
-- how do I get a refund? | 0.1 | 0.02 | 0.85 | 0.4
-- weather forecast | -0.71 | 0.55 | -0.1 | 0.02

-- Cosine distance spelled out: 1 - (a·b) / (|a| × |b|).
-- That one expression is all pgvector's <=> operator computes. The
-- extension makes it short and fast; it is not magic.
-- The query vector [0.10, 0.02, 0.85, 0.40] is the embedding of
-- "how do I get a refund?".
SELECT
    label,
    ROUND(CAST(
        1 - (d1*0.10 + d2*0.02 + d3*0.85 + d4*0.40) /
            (SQRT(d1*d1 + d2*d2 + d3*d3 + d4*d4) *
             SQRT(0.10*0.10 + 0.02*0.02 + 0.85*0.85 + 0.40*0.40))
    AS numeric), 4) AS cosine_distance
FROM doc_vectors
ORDER BY cosine_distance;

-- Smallest distance first — exactly the shape of a k-NN query. The two
-- refund-related texts sit almost on top of each other; the weather one
-- is more than a whole unit away.

-- ✅ Expected result:
-- label | cosine_distance
-- how do I get a refund? | 0
-- return policy | 0.0015
-- weather forecast | 1.1604

1. Enable pgvector

Postgres doesn't know about vectors out of the box. The pgvector extension adds a native vector data type and the operators to compare them. You enable it once per database with CREATE EXTENSION vector. After that, vector columns and similarity operators just work.

Think of a library where every book is placed on a giant map by topic, not by title. Books about the same subject sit close together. To "find similar books" you just look at the shelf nearby. Embeddings are those map coordinates; pgvector is the map.

-- engine: postgres — pgvector is a PostgreSQL extension, so from here on the
-- lesson needs a PostgreSQL server that has pgvector installed. SQLite, and
-- most browser playgrounds, cannot run the blocks below.
-- Step 1: turn on the pgvector extension (once per database).
-- pgvector adds a native 'vector' type plus distance operators.
CREATE EXTENSION IF NOT EXISTS vector;

-- Now Postgres understands vectors. Without this line, a vector
-- column declaration fails with "type vector does not exist".

2. A vector(n) Column

You store embeddings in a column typed vector(n), where n is the number of dimensions your model outputs (commonly 1536). It lives right alongside your ordinary columns, so each row keeps both the human-readable text and the numeric vector of what that text means.

💡 The dimension is fixed by the model

Pick n to match your embedding model exactly. If the model returns 1536 numbers, the column must be vector(1536). Mixing dimensions (storing a 768-vector in a 1536 column) is an error.

-- engine: postgres — needs PostgreSQL with the pgvector extension.
-- Step 2: store embeddings alongside your normal columns.
-- The number in vector(1536) is the DIMENSIONALITY of your embeddings,
-- which is fixed by the ML model you use (e.g. 1536 for many models).
CREATE TABLE documents (
    id        BIGINT PRIMARY KEY,
    title     TEXT,
    body      TEXT,
    embedding vector(1536)        -- the meaning of 'body' as numbers
);

-- Each row keeps its human-readable text AND a vector capturing what
-- that text MEANS. The vector is produced by an external ML model.

-- engine: postgres — needs PostgreSQL with the pgvector extension.
-- Step 3: insert a row with its embedding.
-- In real code your app calls an embedding model, gets back an array
-- of floats, and stores it. (Shown short here; real ones have 1536.)
INSERT INTO documents (id, title, body, embedding)
VALUES (
    1,
    'Refund policy',
    'You can return items within 30 days for a full refund.',
    '[0.12, -0.03, 0.88, 0.41]'      -- the model's output vector
);

-- Important: the vector is NOT computed by SQL. You generate it with
-- an ML model (in Python, JS, etc.), then hand the array to Postgres.

3. Distance Operators

pgvector gives you three ways to measure how far apart two vectors are — smaller means more similar. <-> is L2 (Euclidean) distance, the straight-line gap. <=> is cosine distance, based on the angle between vectors (it ignores length and is the usual choice for text). <#> is the (negative) inner product.

-- engine: postgres — needs PostgreSQL with the pgvector extension.
-- The three distance operators pgvector gives you.
-- Each returns how FAR APART two vectors are (smaller = more similar).
SELECT
    embedding <-> '[0.1, 0.0, 0.9, 0.4]' AS l2_distance,      -- Euclidean
    embedding <=> '[0.1, 0.0, 0.9, 0.4]' AS cosine_distance,  -- angle-based
    embedding <#> '[0.1, 0.0, 0.9, 0.4]' AS neg_inner_product -- inner product
FROM documents
WHERE id = 1;

--   <->  L2 / Euclidean: straight-line distance between the points.
--   <=>  cosine: based on the ANGLE, ignores vector length. Most common
--        for text embeddings.
--   <#>  negative inner product (note: pgvector returns it negated).

4. k-Nearest-Neighbour Search

This is the heart of the whole topic. To find the k most similar rows to a query, you ORDER BY the distance operator (ascending — smallest distance first) and LIMIT k. Embed the user's question with the same model you used for your rows, drop that query vector into the ORDER BY, and you've built semantic search.

-- engine: postgres — needs PostgreSQL with the pgvector extension.
-- k-Nearest-Neighbour: the core of semantic search.
-- "Find the 5 documents whose meaning is CLOSEST to my query vector."
-- ORDER BY the distance ascending, then LIMIT k.
SELECT id, title, body
FROM documents
ORDER BY embedding <=> '[0.10, 0.02, 0.85, 0.40]'   -- the query embedding
LIMIT 5;

-- This is the whole trick: embed the user's question with the SAME
-- model, then ask the database for the rows with the smallest cosine
-- distance. Those are the most semantically relevant results.

Your Turn: write a k-NN query

Fill in the cosine operator and the clause that keeps only the top results, so the query returns the 3 most similar documents. The expected behaviour is in the comments.

-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: return the 3 documents most similar to a query embedding,
-- using cosine distance.

SELECT id, title
FROM documents
ORDER BY embedding ___ '[0.10, 0.02, 0.85, 0.40]'   -- 👉 the cosine operator
___ 3;                                               -- 👉 keep only the top 3

-- ✅ Expected: 3 rows, ordered from most similar to least similar by
--    cosine distance to the query vector.

5. Approximate Indexes: HNSW & IVFFlat

With a few rows, scanning every vector is fine. With millions, it's too slow — so pgvector offers approximate nearest-neighbour (ANN) indexes. HNSW builds a navigable graph: excellent recall and fast queries, slower to build and more memory. IVFFlat groups vectors into clusters (lists) and searches the nearest few: cheaper to build, you tune the lists parameter.

⚠️ Match the operator class

The index's operator class must match the distance you query with: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. A mismatched index simply won't be used.

-- engine: postgres — needs PostgreSQL with the pgvector extension.
-- At scale, scanning every vector is slow. Add an APPROXIMATE index.
-- HNSW (graph-based): excellent recall, fast queries, slower to build.
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

-- IVFFlat (cluster-based): faster to build, set a 'lists' parameter.
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Match the index's *_ops to the operator you query with: use
-- vector_cosine_ops for <=>, vector_l2_ops for <->. These are
-- APPROXIMATE (ANN) indexes: a tiny bit of recall traded for big speed.

Your Turn: add a vector index

Fill in the graph-based ANN index type so cosine searches use an index instead of scanning every row.

-- 🎯 YOUR TURN — add an approximate index for fast cosine search.

CREATE INDEX ON documents
USING ___ (embedding vector_cosine_ops);   -- 👉 the graph-based ANN index

-- ✅ Expected: a vector index exists so that ORDER BY embedding <=> ...
--    LIMIT k uses the index instead of scanning every row.

6. Why This Powers Modern AI Apps

Vector search is the backbone of three huge use cases. Semantic search finds results by meaning, even when no keywords match. RAG (retrieval-augmented generation) fetches the most relevant chunks of your documents and feeds them to a language model so it can answer from your data. Recommendations surface "items similar to this" by nearest-neighbour over product or content embeddings. In every case the recipe is the same: embed with a model, store the vector, search by distance.

Common Errors (and the fix)

• "type vector does not exist": you forgot CREATE EXTENSION vector, or your Postgres doesn't have pgvector installed. Enable the extension (and make sure the build includes it).
• Dimension mismatch: storing a 768-dim vector in a vector(1536) column (or querying with a wrong-length vector) errors. The column size must equal your model's output dimension.
• Wrong operator class on the index: a cosine query (<=>) won't use an index built with vector_l2_ops. Match the ops to the operator you query with.
• Expecting exact results from an ANN index: HNSW and IVFFlat are approximate; they may miss a true nearest neighbour occasionally. That's the speed/recall trade-off — tune index parameters if recall matters.
• Embedding queries with a different model: the query vector and the stored vectors must come from the same embedding model, or distances are meaningless. Keep them consistent.

Frequently Asked Questions

Q: Does SQL compute the embeddings?

No. You generate embeddings with an ML model in your application (Python, JavaScript, etc.), then store the returned array of floats in the vector column. Postgres only stores and compares them.

Q: Which distance operator should I use?

For text embeddings, cosine distance (<=>) is the standard default because it compares direction (meaning) and ignores magnitude. Use <-> (L2) or <#> (inner product) only if your model's documentation recommends it.

Q: HNSW or IVFFlat?

HNSW usually gives better recall and query speed at the cost of slower builds and more memory — a good default. IVFFlat is cheaper to build and tunable via lists, handy for very large or frequently rebuilt datasets.

Q: What is RAG and how does this fit?

Retrieval-augmented generation: you embed the user's question, run a k-NN search to pull the most relevant document chunks, and feed those to a language model as context so it answers from your data. The k-NN step is exactly the query in this lesson.

Mini-Challenge: A Tiny Semantic Search Backend

Put it together — a brief, a blank canvas, and the expected result in the comments. Write it, then run it on a Postgres instance with pgvector to confirm.

-- 🎯 MINI-CHALLENGE: a tiny semantic-search backend
-- Using ONLY what this lesson covered:
--   1. CREATE EXTENSION for pgvector
--   2. CREATE TABLE articles (id BIGINT PRIMARY KEY, title TEXT,
--      embedding vector(3))   -- 3 dims to keep it short
--   3. Add an HNSW index on embedding for cosine search
--   4. Write a query returning the top 2 articles nearest to
--      the query vector '[0.2, 0.5, 0.1]' by cosine distance
--
-- ✅ Expected: a working schema + a k-NN query that ORDER BYs
--    embedding <=> the query vector and LIMITs 2.

-- your statements here

🎉 Lesson Complete

• ✅ An embedding is a vector from an ML model that captures meaning
• ✅ CREATE EXTENSION vector enables pgvector; store embeddings in a vector(n) column
• ✅ Distance operators: <-> L2, <=> cosine, <#> inner product
• ✅ ORDER BY embedding <=> query LIMIT k is k-nearest-neighbour semantic search
• ✅ HNSW and IVFFlat are approximate indexes that keep searches fast at scale
• ✅ This powers semantic search, RAG, and recommendations
• 🎓 That's the final lesson — congratulations on finishing the SQL course!

Practice quiz

Continue this course

• Previous: MERGE & UPSERT
• Quick reference: SQL cheat sheet

Related lessons

• CREATE TABLE
• indexes
• ORDER BY / LIMIT