recallA retrieval
field guide

PostgreSQL + pgvector

Build this skill

Store and search embeddings in PostgreSQL with pgvector, matching operators to index operator classes.

Vectors in a database you already know

PostgreSQL is one of the most widely used open-source relational databases. pgvector is an extension that adds three things to it: a vector data type, operators that measure the distance between vectors, and vector indexes. With pgvector, an embedding is just another column. It sits in an ordinary table next to the passage text, document ID, date and permissions, and it is searched with ordinary SQL.

Many teams already run PostgreSQL and know how to back it up and secure it. Adding pgvector means no new database to learn or operate, and one query can combine similarity search with normal SQL conditions and joins. That simplicity is a major reason pgvector is one of the most common ways to store embeddings in production.

CREATEEXTENSIONvectorTablevector(1024)columnInsert rowstext +embeddingCreate indexHNSW, matchingopsQueryORDER BY <=>LIMIT k

Five steps from an empty database to fast semantic search with SQL.

A table with a vector column

pgvector is switched on once per database, and many managed PostgreSQL services support it. After that, a vector column is declared like any other column.

sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE passages (
    id          bigserial PRIMARY KEY,
    doc_id      text NOT NULL,
    section     text,
    content     text NOT NULL,
    embedding   vector(1024) NOT NULL
);

INSERT INTO passages (doc_id, section, content, embedding)
VALUES ('acad-reg-2026', '4. Medical Leave',
        'Students may take up to 10 days of medical leave in a semester.',
        '[0.12, -0.48, 0.33, ...]');

The type vector(1024) means "a list of exactly 1,024 numbers". PostgreSQL rejects an embedding of any other length, which catches the mistake of mixing two different models. An embedding is written as a list in square brackets; in practice your Python code passes it in.

Distance operators

pgvector adds three operators that work out the distance between two vectors. For all three, a smaller result means the vectors are closer, so results are sorted in ascending order.

Operator Measures Use when
<-> Euclidean (L2) distance The model expects Euclidean distance
<=> Cosine distance (1 − cosine similarity) The usual choice for text embeddings
<#> Negative inner product Normalised embeddings; fastest

The inner product is negated because a large inner product means "very similar", and flipping its sign lets the most similar row sort first like the others.

A similarity search is then an ordinary ORDER BY ... LIMIT query.

sql
SELECT doc_id, section, content,
       1 - (embedding <=> :query_embedding) AS similarity
FROM passages
ORDER BY embedding <=> :query_embedding
LIMIT 5;

:query_embedding is a placeholder that your program fills with the embedding of the user's question. ORDER BY sorts the rows from the smallest cosine distance to the largest, and LIMIT 5 keeps the first five. The 1 - (...) in the first line turns distance back into similarity, purely for display.

The three operators on real numbers

The operators are simple arithmetic, so they can be reproduced in NumPy. The program below applies all three to a table of three passages and shows which row each operator would return first.

python · runs in your browser

Expected output:

text
passage                     <->    <=>    <#>
Medical leave rules        0.05   0.00  -0.73
Hostel timings             1.01   0.63  -0.29
Leave certificate form     1.20   0.07  -1.49
ORDER BY embedding <-> q LIMIT 1  ->  Medical leave rules
ORDER BY embedding <=> q LIMIT 1  ->  Medical leave rules
ORDER BY embedding <#> q LIMIT 1  ->  Leave certificate form

Each function is one operator, and the min(...) line does what ORDER BY ... LIMIT 1 does: it picks the row with the smallest value.

The operators disagree. <-> and <=> choose the medical leave rules, but <#> chooses the certificate form, only because its vector is longer and a longer vector gives a larger inner product. Once every vector is scaled to length 1 (normalised), all three rank rows in the same order, which is why <#> is recommended only for normalised embeddings.

Adding a vector index

Without an index, the search compares the question with every row: exact, but slow on a large table. pgvector offers two index types.

sql
-- HNSW index for cosine distance (the usual choice)
CREATE INDEX ON passages USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Or an IVFFlat index: build it after the table has data, because it learns clusters from the rows
CREATE INDEX ON passages USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);

The word after the column name is the operator class, and it says which distance the index is built for. It must match the operator in your queries.

Query uses Index must be built with
<=> vector_cosine_ops
<-> vector_l2_ops
<#> vector_ip_ops

If they do not match, PostgreSQL gives no error. It quietly ignores the index and compares every row, and the only symptom is a slow query.

The settings that trade recall for speed at query time are changed with SET, and they apply to the queries that follow in the same session.

sql
SET hnsw.ef_search = 100;     -- more candidates: better recall, slower
SET ivfflat.probes = 10;      -- more lists searched: better recall, slower

From Python

In a Python application, the psycopg driver and the pgvector package let you pass NumPy arrays straight into queries. This runs on your own computer with a PostgreSQL server available.

python
# pip install "psycopg[binary]" pgvector numpy
import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect("dbname=kit_kb")
register_vector(conn)                         # teach psycopg about the vector type

query_vec = embed("How many sick days can I take?")   # your embedding model
rows = conn.execute(
    "SELECT section, content FROM passages ORDER BY embedding <=> %s LIMIT 3",
    (query_vec,),
).fetchall()

Without register_vector, the driver does not know how to send a NumPy array as a vector, and the query fails. The %s is the placeholder that query_vec fills.

Where pgvector fits, and where it strains

pgvector's strengths are familiarity, a single system to operate, and full SQL alongside vector search. Its limits show at very large scale: one PostgreSQL server holding hundreds of millions of vectors under heavy traffic needs careful tuning, and purpose-built vector databases offer more options for spreading data across many machines. For most college and company knowledge bases, with thousands to a few million passages, pgvector is a comfortable fit.

Follow your curiosity.

↑ ↓ to explore · Enter to open · Escape to close