pgvector: vectors in the database you already have

Crumb’s WhatsApp archive has been waiting since the start of this module: 3.2 million messages from five years of orders, complaints and compliments. Meera still wants to search it by meaning. “Complaints about packaging at the Baner store in 2023.” “Customers who asked about sugar-free hampers before Diwali, and what they ended up ordering.”

Those messages do not live in a file. They live in Crumb’s main database, PostgreSQL, in a table called whatsapp_messages, right next to the orders, customers and stores tables. Every message already has a customer, a store, a timestamp and, often, an order it belongs to.

Arjun’s plan, after the café project, is to copy everything into Pinecone. He has a script half written when an email arrives from Meera, forwarded from a customer: “Under the data protection law, please delete all my messages and personal data from your systems.”

Two copies, one deletion request

Arjun handles the deletion request the way the support team always has: one SQL statement that removes the customer’s rows from whatsapp_messages, orders and customers. Done.

Then he remembers his half-written Pinecone script, which he had test-run on a sample the week before. He searches the Pinecone sample for the customer’s phone number in metadata. Forty-one of their messages are still there, fully searchable. The copy did not know the original had been deleted.

“That’s the problem with two copies of the same data,” Priya says. “Every insert, every edit and every delete has to happen twice, and the day one of them doesn’t, you have two versions of the truth. For a privacy request, the wrong version is a legal problem.”

“So never copy?”

“Sometimes copying is right, like for the cafés, whose data was never in our database. But here, the messages, the customers and the orders already live together in Postgres. What if the meaning of each message lived in the same row as the message?”

The label on the same folder

Priya goes back to the filing cabinet. “Right now Crumb has one cabinet: Postgres. Each message is a folder, with labels like customer, store and date. Your Pinecone plan builds a second cabinet, with a copy of every folder, filed by meaning instead. Now there are two cabinets to keep in sync.”

“And the alternative?”

“Write the meaning on the folder itself. One more label: a list of 384 numbers. Then one clerk can do everything: ‘folders from Baner, dated 2023, closest in meaning to crushed box’, and when a customer asks to be forgotten, one action throws away the folder, meaning and all.”

“Postgres can search by meaning?”

“With an extension called pgvector, yes. It adds a column type for vectors, operators to measure distance, and the same kinds of indexes you met in the theory lesson. It’s the car already parked in your garage.”

Following one message through Postgres

Trace one message from storage to search result.

Step 1: the row. Message 88412 in whatsapp_messages:

id	customer_id	store_id	order_id	created_at	body
88412	5531	baner	240117	2023-08-14 18:02	box was crushed, cake smushed on one side, not happy

Step 2: add a vector column. One schema change: embedding vector(384). Each row gets room for 384 numbers.

Step 3: fill it. A Python job reads the body, embeds it with all-MiniLM-L6-v2 (384 numbers, length 1) and writes the vector back into the same row. Storage: pgvector stores each number as a 4-byte float plus a small header, about 1,544 bytes per row. For 3.2 million rows that is about 4.9 GB, the same arithmetic as in the theory lesson.

Step 4: a question arrives. Meera’s query, “complaints about packaging”, is embedded by the same model into a 384-number vector, here called :q.

Step 5: the SQL.

SELECT m.id, m.body, m.embedding <=> :q AS distance
FROM whatsapp_messages m
WHERE m.store_id = 'baner'
  AND m.created_at >= '2023-01-01' AND m.created_at < '2024-01-01'
ORDER BY m.embedding <=> :q
LIMIT 10;

<=> is pgvector’s cosine distance operator: 1 minus the cosine similarity, so smaller means closer. ORDER BY ... LIMIT 10 asks for the ten nearest.

Step 6: the result.

id	body	distance
88412	box was crushed, cake smushed on one side, not happy	0.29
90377	packaging torn when it arrived, cream all over the box	0.33
101265	the box was dented and the cake had shifted	0.35

Distance 0.29 is a cosine similarity of 0.71: a strong match.

Step 7: the part no separate vector database can do in one step. Join the matches to the orders they belong to:

SELECT m.body, o.total_inr, o.refund_status
FROM whatsapp_messages m
JOIN orders o ON o.id = m.order_id
WHERE m.store_id = 'baner' AND m.created_at >= '2023-01-01' AND m.created_at < '2024-01-01'
ORDER BY m.embedding <=> :q
LIMIT 10;

Message 88412 belonged to order 240117: ₹1,450, refunded. Meera can now see not only that packaging complaints happened, but how much they cost. In a separate vector database, this needs two systems, two queries and code to stitch the results together.

Step 8: the deletion request. DELETE FROM whatsapp_messages WHERE customer_id = 5531; removes the text, the metadata and the vector in one statement. There is no second copy to forget.

Show the answer

A pgvector index can only be used when the query orders by the distance operator itself, ascending, with a LIMIT: ORDER BY embedding <=> :q LIMIT 10. Wrapping it in an expression (1 - ..., sorted descending) hides the operator from PostgreSQL’s query planner (the part that decides how to run a query), so it falls back to computing the distance for every one of the 3.2 million rows and sorting them. Keep ORDER BY embedding <=> :q and compute the similarity in the SELECT list instead: 1 - (embedding <=> :q) AS similarity.

Opening the hood: what pgvector adds

pgvector is an open-source extension: an add-on that teaches PostgreSQL new types, functions and index methods. You enable it once per database with CREATE EXTENSION vector;. Most hosted Postgres services, including Amazon RDS, Google Cloud SQL, Azure, Supabase and Neon, offer it.

Types.

• vector(n): n 4-byte floats. The everyday choice.
• halfvec(n): n 2-byte floats, half the storage, very little quality loss for most embeddings. Scalar quantisation, built in.
• bit(n): one bit per dimension, for binary quantisation.
• sparsevec(n): sparse vectors that store only the non-zero values, for keyword-style representations.

Distance operators.

Operator	Measures	Use with
<->	Euclidean (L2) distance	vector_l2_ops
<#>	Negative inner product	vector_ip_ops
<=>	Cosine distance	vector_cosine_ops
<+>	L1 (Manhattan) distance	vector_l1_ops

<#> returns the negative inner product, so that “smaller is closer” holds for every operator and ORDER BY ... ASC always works.

Indexes. Without an index, pgvector does an exact flat search: perfect recall, slow on big tables. Two approximate index types are available:

• HNSW: the layered graph from the theory lesson. Settings: m (links per node, default 16), ef_construction (build effort, default 64) and, at query time, hnsw.ef_search (shortlist size, default 40). Better speed and recall, slower to build, uses more memory. It can be created on an empty table and grows as rows are added.
• IVFFlat: the clustered “sections” index. Setting: lists (number of clusters) and, at query time, ivfflat.probes (default 1). Faster to build and smaller, but it learns its clusters from existing data, so create it after loading, and rebuild it if the data changes a lot.

Each index is created for one operator class, such as vector_cosine_ops, and only speeds up queries that use the matching operator.

Filtering. Postgres can combine a vector index with WHERE clauses in two ways: walk the vector index and discard rows that fail the filter, or use an ordinary index (on store_id, say) to find the matching rows and compute exact distances for only those rows. The planner chooses based on its estimates, and it does not always choose well, which leads to the bug below. Recent pgvector versions add iterative index scans: if the filter discards too many candidates, the index scan keeps going until it has enough results.

Building it, step by step

Step 1: run Postgres with pgvector

The quickest local setup uses Docker and the official image, which has the extension preinstalled:

docker run -d --name crumb-pg -e POSTGRES_PASSWORD=crumb -p 5432:5432 pgvector/pgvector:pg17

Put the connection string in .env: DATABASE_URL=postgresql://postgres:crumb@localhost:5432/postgres. If your company’s Postgres is hosted, check that pgvector is available in its extension list.

Step 2: enable the extension and add a column

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE whatsapp_messages ADD COLUMN embedding vector(384);

For a fresh practice table:

CREATE TABLE whatsapp_messages (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    store_id    text   NOT NULL,
    order_id    bigint,
    created_at  timestamptz NOT NULL,
    body        text   NOT NULL,
    embedding   vector(384)
);

Step 3: fill the embeddings from Python

uv add "psycopg[binary]" pgvector sentence-transformers numpy python-dotenv

# embed_messages.py
import os
import psycopg
from dotenv import load_dotenv
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

load_dotenv()
model = SentenceTransformer("all-MiniLM-L6-v2")
conn = psycopg.connect(os.environ["DATABASE_URL"], autocommit=True)
register_vector(conn)                         # lets psycopg send and receive vectors as NumPy arrays

BATCH = 1_000
while True:
    rows = conn.execute(
        "SELECT id, body FROM whatsapp_messages WHERE embedding IS NULL LIMIT %s", (BATCH,)
    ).fetchall()
    if not rows:
        break
    vecs = model.encode([body for _, body in rows], normalize_embeddings=True)
    with conn.cursor() as cur:
        cur.executemany("UPDATE whatsapp_messages SET embedding = %s WHERE id = %s",
                        [(v, id_) for (id_, _), v in zip(rows, vecs)])
    print("embedded", len(rows))

Because it only picks up rows where embedding IS NULL, the job is safe to stop and restart, and it naturally handles new messages that arrive later. For millions of rows, PostgreSQL’s COPY command loads data much faster than individual updates; the pgvector README shows how.

You should now see SELECT count(*) FROM whatsapp_messages WHERE embedding IS NULL fall to zero.

Step 4: search from Python

def search(question, store=None, k=10):
    q = model.encode([question], normalize_embeddings=True)[0]
    sql = """
        SELECT id, body, 1 - (embedding <=> %(q)s) AS similarity
        FROM whatsapp_messages
        WHERE (%(store)s::text IS NULL OR store_id = %(store)s)
        ORDER BY embedding <=> %(q)s
        LIMIT %(k)s
    """
    return conn.execute(sql, {"q": q, "store": store, "k": k}).fetchall()

for row in search("complaints about packaging", store="baner"):
    print(row)

The similarity is computed in the SELECT, while ORDER BY uses the bare operator, so an index can be used.

Step 5: add an HNSW index and confirm it is used

SET maintenance_work_mem = '2GB';                 -- building in memory is much faster
CREATE INDEX CONCURRENTLY whatsapp_embedding_hnsw
    ON whatsapp_messages USING hnsw (embedding vector_cosine_ops);

CONCURRENTLY lets the shop keep writing new messages while the index builds, which on 3.2 million rows can take a good while; it took Arjun’s server a little under an hour. Then check the plan:

EXPLAIN ANALYZE
SELECT id FROM whatsapp_messages ORDER BY embedding <=> '[0.01, -0.03, ...]' LIMIT 10;

You should now see Index Scan using whatsapp_embedding_hnsw in the plan, and an execution time of a few milliseconds instead of seconds. If you see Seq Scan, the index is not being used: check the operator matches the operator class, and that the query has ORDER BY and LIMIT.

Step 6: tune recall per query

SET hnsw.ef_search = 100;    -- default 40; higher means better recall, slower queries

Measure it exactly as in the theory lesson: run 100 real queries with the index, then again with the index disabled (SET enable_indexscan = off; in a test session) to get the exact answer, and compare the top 10s.

Where it breaks: the search that found three complaints

Meera’s first real query, “packaging complaints at Baner in 2023”, returns only 3 rows, though she asked for 10 and the store had hundreds of such complaints.

Arjun runs EXPLAIN. Postgres chose to walk the HNSW index, which finds the 40 nearest messages overall (ef_search = 40), then applied the WHERE filter. Baner’s 2023 messages are about 4% of the table. Of the 40 nearest packaging complaints from all stores and all years, only 3 happened to be from Baner in 2023. The rest were thrown away, and the index scan stopped there.

Show the answer

If 4% of candidates pass the filter, you need about 10 / 0.04 = 250 candidates to expect 10 survivors, and more to be safe. Raising ef_search to a few hundred works, but it makes every query slower, including unfiltered ones, and a stricter filter (one customer, one week) would need thousands. Better fixes are iterative index scans (so the scan keeps going until enough rows pass), an ordinary index on the filter columns so Postgres can choose exact search over the small filtered set, or partial indexes for the most common filters.

Arjun’s fixes, in order:

-- 1. Let the HNSW scan keep going until enough filtered rows are found (pgvector 0.8 and later).
SET hnsw.iterative_scan = relaxed_order;

-- 2. Give the planner a cheap way to find the filtered rows directly.
CREATE INDEX ON whatsapp_messages (store_id, created_at);

-- 3. For a filter used constantly, a partial vector index covering only those rows.
CREATE INDEX ON whatsapp_messages USING hnsw (embedding vector_cosine_ops)
    WHERE store_id = 'baner';

With these, the query returns 10 rows in 12 milliseconds. With a very selective filter, such as one customer’s messages, Postgres now uses the ordinary index and computes exact distances over a few dozen rows, which is both fast and perfectly accurate. Iterative scans are a newer feature, and library and database features change quickly; check the pgvector README for your version.

Other limits to know:

• The scale ceiling. pgvector handles many millions of vectors well on a good server. At hundreds of millions with heavy filtering and high query rates, a dedicated system like Milvus may be easier, as the last lesson found for FeastBox.
• Index memory. HNSW performs best when the index fits in memory. Use halfvec to halve it, and check the index size with pg_relation_size.
• IVFFlat built too early. Created on an empty or tiny table, its clusters are meaningless. Load data first, then build, and rebuild after large changes.
• Dimension limits on indexes. Indexes on vector support up to 2,000 dimensions, so a 3,072-dimension model needs halfvec (which indexes up to 4,000) or fewer dimensions.
• Model changes. A new embedding model means a new column (for example embedding_v2), a backfill and a new index, then switching queries over. Keep the old one until the new one is tested.

Arjun explains it to Meera

“So we didn’t need Pinecone for this one?” Meera asks.

“Not for the WhatsApp messages. They already live in our main database, next to orders and customers. We added one column to each message: its position on the map of meaning. Now one search can say ‘Baner, 2023, about packaging’ and also pull in what each order cost and whether it was refunded.”

“And that deletion request?”

“One delete command removes the customer’s messages, their meaning and everything else together. There’s no second copy anywhere to forget. That’s the main reason I chose this.”

“What did it cost?”

“No new service. About 5 GB more storage for the vectors, and an evening to build the index. The one thing to watch is that heavy searching doesn’t slow down order-taking, so the search runs on a copy of the database that only reads.”

“And the first search only found three complaints. I noticed.”

Arjun laughs. “So did I. The filter was throwing away most of what the index found. We fixed it, and it now finds all ten in about a hundredth of a second.”

Try it yourself

1. Start the pgvector/pgvector Docker image and create the practice table.
2. Insert at least 2,000 messages (real anonymised ones, or generated with an LLM), with three or four store_id values, and embed them.
3. Run a search with EXPLAIN ANALYZE before and after creating the HNSW index, and note the plan and time.
4. Run a filtered search for the smallest store with hnsw.ef_search at 10, then turn on hnsw.iterative_scan and run it again.

Expected result: before the index you see Seq Scan; after it, Index Scan using ...hnsw. With a tiny ef_search and a rare store, the filtered search returns fewer rows than LIMIT; with iterative scans enabled it returns the full count.

Show a hint for generating test messages

Ask an LLM for messages in batches by store and theme (“20 WhatsApp messages to a Pune bakery about late deliveries, mixed English and Hinglish”), then insert them with random timestamps across two years. Make one store much smaller than the others so the filtering problem shows up.

Common mistakes

• Ordering by an expression. ORDER BY 1 - (embedding <=> q) DESC cannot use the index. Order by the operator, compute similarity in SELECT.
• Operator and operator class that do not match. An index built with vector_cosine_ops does nothing for <-> queries.
• Assuming the filter is applied before the search. With approximate indexes it often is not. Test filtered queries and use iterative scans, ordinary indexes or partial indexes.
• Building IVFFlat on an empty table. It learns its clusters from existing rows. Load first.
• Running heavy vector work on the primary database at peak time. Use CONCURRENTLY, off-peak builds and read replicas.

Recap

• pgvector adds vector, halfvec, bit and sparsevec types, distance operators (<->, <#>, <=>, <+>) and HNSW and IVFFlat indexes to PostgreSQL.
• Storing embeddings in the same rows as your data gives you filters, joins, transactions and deletes in ordinary SQL, with no second copy to keep in sync.
• Indexes are used only when the query orders by the matching distance operator with a LIMIT; check with EXPLAIN.
• Filters and approximate indexes interact: tune hnsw.ef_search, enable iterative scans, and add ordinary or partial indexes for common filters.
• pgvector is an excellent default when your data already lives in Postgres, up to many millions of vectors; beyond that, compare with dedicated systems.

The module’s last piece of work is not for Crumb at all. A Pune developer meetup announces a weekend hackathon with the theme “learning from video”. Arjun has a playlist of 40 long conference talks he has been meaning to watch for a year. Priya, reading the announcement over his shoulder, says, “You could make them answer your questions instead. With timestamps.”

Go deeper

• pgvector on GitHub (the README is the best reference for operators, indexes and tuning)
• PostgreSQL documentation (see EXPLAIN and indexes)
• Hugging Face docs (embedding model cards)