PostgreSQL + pgvector
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.
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.
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.
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.
Expected output:
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.
-- 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.
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.
# 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.