pgvector: When Postgres Is Enough of a Vector Database

The architecture diagram had a new box on it: a dedicated vector database, sitting beside the Postgres instance that already held every row of application data the embeddings were derived from. New service to deploy, new client library, new failure mode, new thing to back up, and — the part that always gets waved away — a synchronization problem, because now the documents live in one database and their vectors live in another and nothing keeps them honest with each other. The corpus was about 200,000 chunks. I asked what the vector database was doing that Postgres couldn't. The room went quiet, and someone said "scale."

Two hundred thousand vectors is not scale. And as of last month, the argument got weaker still: pgvector 0.5.0 shipped HNSW indexing, which was the last real technical gap between "Postgres with an extension" and "a database whose entire job is vectors." So this piece is about what pgvector actually does, how to tune it without cargo-culting a blog post, the one sharp edge that genuinely surprises people, and — honestly — where the line is, because there is a line and I'm not going to pretend otherwise.

What does pgvector actually add?

pgvector is a Postgres extension that adds a vector column type, distance operators, and approximate-nearest-neighbour index types — which means embeddings become an ordinary column on an ordinary table, queried with ordinary SQL. That framing is the whole value proposition, and it's easy to undersell.

CREATE EXTENSION vector;

-- The embedding is just a column. Alongside the data it describes.
CREATE TABLE documents (
    id          bigserial PRIMARY KEY,
    tenant_id   int NOT NULL,
    title       text,
    body        text,
    published   boolean DEFAULT false,
    embedding   vector(1536)          -- OpenAI ada-002 dimensionality
);

-- Similarity search is just ORDER BY. <=> is cosine distance.
SELECT id, title
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

Three distance operators cover the usual cases: <=> for cosine distance, <-> for L2/Euclidean, and <#> for negative inner product. Match the operator to how your embedding model was trained — cosine for most text embedding models — and match your index to the operator, because an index built for L2 will not serve a cosine query.

What you get for free is the part people underrate: transactions, joins, foreign keys, your existing backups, your existing replicas, your existing connection pooler, your existing monitoring, and your existing on-call runbook. The embedding and the row it describes are updated in the same transaction, so they cannot drift apart. There is no sync job. There is no eventual consistency between "the document" and "the document's vector." That's not a small thing — it's the entire class of bug the second database introduces.

ivfflat or HNSW?

An exact nearest-neighbour search compares your query vector against every row — accurate, and linear. At 200k rows that's often fine (a few hundred milliseconds); at millions it isn't. So you build an approximate index and trade a little recall for a lot of speed. pgvector now offers two, and the choice is genuinely consequential.

ivfflat: partition the space into lists

An ivfflat index clusters vectors into lists partitions (via k-means at build time), records each partition's centroid, and at query time searches only the probes partitions whose centroids are closest to your query. Fewer probes, faster and less accurate; more probes, slower and more accurate.

-- Build AFTER the table has representative data — it clusters what's there.
-- Rule of thumb: lists ≈ rows/1000 up to ~1M rows, then ≈ sqrt(rows).
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 200);

SET ivfflat.probes = 10;   -- recall/speed dial, per session

The trap is in that first comment. ivfflat's partitions are computed from the data present at build time. Build the index on an empty or unrepresentative table and the centroids are meaningless — the index exists, queries use it, and recall is quietly terrible. Nothing errors. You just get worse answers than you'd get from a sequential scan, which is a spectacularly annoying bug to chase.

HNSW: a navigable graph (new in 0.5)

HNSW builds a multi-layer proximity graph — sparse long-range links up top for coarse navigation, dense local links at the bottom for precision — and a search greedily walks from the entry point toward the query, descending layers as it homes in. I went through the algorithm itself in the HNSW and IVF piece; the news is that pgvector has it now.

-- m: links per node. ef_construction: candidate list size while building.
-- Higher = better recall, slower build, more memory.
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 40;   -- recall/speed dial, per session

HNSW gives noticeably better recall-per-latency than ivfflat, and — this is the practically important bit — it doesn't depend on the data distribution at build time, so you can create it on an empty table and insert afterwards. It costs more to build and more memory to hold.

My default now: HNSW unless build time or memory is a real constraint. The better recall curve and the freedom from build-time data dependence are worth it, and both of ivfflat's characteristic failure modes — built too early, or degraded after the data moved — simply don't exist. ivfflat still earns its place when you're indexing tens of millions of rows and the HNSW build cost is genuinely painful.

The sharp edge: filtered vector search

Here's the thing nobody warns you about, and it bites everyone exactly once. Real queries are almost never pure similarity search. They're similarity search plus a filter — this tenant's documents, published only, from the last 90 days:

SELECT id, title
FROM documents
WHERE tenant_id = 42 AND published = true    -- the filter
ORDER BY embedding <=> $1                    -- the similarity
LIMIT 10;

That query looks completely reasonable and can behave badly, because the ANN index and the filter don't cooperate the way you'd hope. The planner has two unappealing options: walk the vector index and discard rows failing the filter — but the index returns a bounded candidate set, so if tenant 42 is rare you may exhaust it and return fewer than 10 rows, or none, despite plenty of matching documents existing; or filter first and then brute-force the survivors, which is exact but linear in the filtered set.

flowchart TB
    Q["WHERE tenant_id = 42
ORDER BY embedding <=> $1 LIMIT 10"] --> P{"Planner picks"}
    P -->|"path A: index first"| A1["Walk HNSW graph
collect ~ef_search candidates"]
    A1 --> A2["Discard rows failing the filter"]
    A2 --> A3["Rare tenant?
candidates exhausted"]
    A3 --> A4["Returns 3 rows, not 10
— silently. No error."]
    P -->|"path B: filter first"| B1["Fetch all tenant 42 rows"]
    B1 --> B2["Brute-force distance on survivors"]
    B2 --> B3["Exact — but linear
in the filtered set"]
          

The mitigations, roughly in order of how often I reach for them: raise ef_search and measure recall against a brute-force baseline; add a conventional B-tree index on the filter columns so the planner can cheaply choose the filter-first path when selectivity is high (this is the right index for the right query shape question, unchanged by the vectors); and for strong tenant isolation, partition the table so each tenant's rows — and their vectors — live in separate physical partitions.

When is Postgres genuinely not enough?

I'd be doing the same thing I complained about in the intro if I claimed pgvector is always the answer. Reach for a dedicated vector database when:

• You're well past tens of millions of vectors and vector search is the primary workload rather than a feature of an application. At that point specialized systems' distribution, sharding, and memory management earn their operational cost.
• You need vector search to scale independently of your transactional database. Sharing an instance means an HNSW build competing with your checkout traffic for the same buffer cache, and that's a real conversation.
• You need features Postgres doesn't have — built-in hybrid dense/sparse retrieval, learned sparse models, first-class multi-tenancy in the index itself.
• Your embeddings have no relational home. If nothing joins to them, the main argument for pgvector — colocation with the data — evaporates.

But notice what's not on that list: "we're building a RAG prototype," "we have a few hundred thousand chunks," "we might scale someday." That's where most projects actually are, and for those the calculus strongly favors the database you already run, back up, monitor, and know how to fix at 3am. The Postgres you already understand — its MVCC, its planner, its operational surface — doesn't stop being true because there's a vector column on the table.

What to carry away

With HNSW in 0.5, pgvector closed the gap that made "just use Postgres" feel like a compromise, and the burden of proof has flipped: the question isn't "why would you use Postgres for vectors," it's "what specifically is the second database doing that this isn't?" — and for a corpus in the hundreds of thousands, "scale" is not an answer. Default to HNSW unless build time or memory says otherwise, because it dodges both of ivfflat's quiet failure modes. Test your filtered queries, not just your unfiltered ones, against a brute-force baseline — the filtered-ANN under-return is silent, looks exactly like a bad embedding model, and will cost you a week of debugging the wrong layer. And keep the real advantage in view: the embedding and the row it describes update in one transaction, which means the synchronization bug that a separate vector store guarantees you'll eventually write simply cannot happen.