Do You Need a Vector Database? Postgres vs Dedicated in 2026

Every team that adds semantic search hits the same fork in the road. The embeddings work, the prototype answers questions, and then someone asks which vector database to run in production. The market has a confident answer ready, because a dozen vendors are funded to provide one.

The honest answer is less exciting: most teams do not need a separate database for this. Postgres has done approximate nearest-neighbour search since pgvector shipped, and it does it next to the relational data you already have, inside the same transaction and the same backup. But “just use Postgres” is equally lazy advice, because pgvector does run out — in four specific, mechanical ways that have nothing to do with vendor benchmarks. This is a guide to what those mechanisms are, so you can tell which side of the line you are on.

What a Vector Database Actually Does

Strip away the positioning and a vector database does one thing: given a query vector, return the stored vectors closest to it under some distance function.

There are only two ways to do that.

Exact search compares the query against every stored vector. It is correct by construction — perfect recall, every time — and its cost grows linearly with the number of rows. This is what pgvector does by default, with no index at all.

Approximate search builds a structure that lets you skip most of the data. You get speed, and you pay in recall: some true nearest neighbours will be missed. The pgvector documentation is blunt about the consequence — “you will see different results for queries after adding an approximate index.”

That sentence deserves more attention than it usually gets. An approximate index is not a faster version of an exact index. It is a different answer. Every vector database on the market, hosted or self-run, is making that same trade; they differ in how they manage it, not in whether they make it.

pgvector offers two approximate index types, and the choice between them is a genuine engineering decision:

• HNSW builds a multilayer graph, based on the Malkov and Yashunin paper on Hierarchical Navigable Small World graphs. Better speed-to-recall tradeoff, slower builds, more memory. Because there is no training step, you can create the index on an empty table and let it fill.
• IVFFlat partitions vectors into lists and searches only the closest ones. Faster builds, less memory, weaker query performance at equal recall. It must be created after the table holds representative data, because it runs k-means over real vectors to find the partitions. The documented starting points are rows / 1000 lists up to a million rows, sqrt(rows) above that, and roughly sqrt(lists) probes at query time.

If you take one thing from this section: the interesting work in vector search is tuning recall, not choosing a vendor.

Postgres Already Does This

Here is the entire setup, from nothing to indexed similarity search:

CREATE EXTENSION vector;

CREATE TABLE documents (

  id bigserial PRIMARY KEY,

  tenant_id int NOT NULL,

  body text,

  embedding vector(1536)

);

CREATE INDEX ON documents

  USING hnsw (embedding vector_cosine_ops);

SELECT id, body

FROM documents

ORDER BY embedding <=> $1

LIMIT 5;

That is it. Cosine distance via <=>, with L2 (<->), inner product (<#>), L1 (<+>), Hamming and Jaccard also available. Note that you add an index per distance function you intend to use.

One trap worth knowing early: the index is only used when the query has both ORDER BY on a distance operator and a LIMIT, in ascending order. Wrap the distance in an expression — for example flipping cosine distance into a similarity score with 1 - (embedding <=> $1) DESC — and the planner silently falls back to a sequential scan. Your query still returns correct results, just slowly, which is the worst kind of bug to notice in production.

What you get for free is the part vendors cannot match, because it is not their product:

• JOINs. Retrieve the nearest chunks and their document metadata, permissions and author in one query, not two round trips and an application-side merge.
• ACID transactions. The embedding and the row it describes commit together or not at all. No dual-write reconciliation job.
• WAL replication and point-in-time recovery. Vectors are covered by the backup story you already operate.
• Hybrid search in one place. Postgres full-text search gives you the lexical half; combine it with vector distance using Reciprocal Rank Fusion or a cross-encoder. Both patterns are documented in the pgvector project.

If you have read the SQL vs NoSQL decision guide, this is the same argument in a new costume: adding a second store means adding a consistency problem you did not have.

The Ceiling Is the Index, Not the Table

The first hard limit surprises people, because it is not about scale at all.

The vector type stores up to 16,000 dimensions. An HNSW or IVFFlat index handles 2,000.

That gap is where real embeddings live. OpenAI’s text-embedding-3-small produces 1,536 dimensions and indexes cleanly. text-embedding-3-large produces 3,072 — you can store it, but you cannot build a plain vector index on it. Your similarity search silently becomes a full scan.

The documented ways out, in rough order of how often they are the right call:

halfvec is usually the quiet win. It halves the working set on the way through: a vector costs 4 × dimensions + 8 bytes, a halfvec costs 2 × dimensions + 8. For a million 1,536-dimension embeddings, that is roughly 6.2 GB of raw vector data versus 3.1 GB — before the index itself, which is additional.

That arithmetic is the honest way to size this. An index does not have to fit in memory, but performance is much better when it does, and that is where “free open source” turns into a line item on your Postgres instance.

The Filtering Trap

This is the one that generates production incidents, and almost nobody hits it in a prototype.

Real queries filter. You want the nearest chunks for this tenant, in this language, not archived. The intuitive mental model is that the database narrows to matching rows and then finds the nearest neighbours among them.

That is not what happens. With an approximate index, the filter is applied after the index scan.

The pgvector documentation gives the arithmetic directly: with the default hnsw.ef_search of 40, a condition matching 10% of rows leaves about 4 results on average. You asked for the top 20 and got 4. No error, no warning — just a quietly incomplete answer that looks plausible enough to ship.

pgvector 0.8.0 added iterative index scans for exactly this, and they are opt-in:

SET hnsw.iterative_scan = strict_order;

The index keeps scanning until it has enough results, or until it hits hnsw.max_scan_tuples (20,000 by default). strict_order guarantees exact distance ordering; relaxed_order allows slightly out-of-order results in exchange for better recall.

Two structural alternatives are often better than tuning:

• Filtering by a few distinct values? Use a partial index — CREATE INDEX ... WHERE (category_id = 123). Each index only contains its own slice.
• Filtering by many values? Partition the table. Each partition gets its own index, so the filter is structural rather than a post-scan discard.

And when the condition matches only a small percentage of rows, the boring answer wins: a plain B-tree index on the filter column and exact search over what is left. Fast, exactly correct, no recall to tune. Approximate indexes earn their keep on broad filters, not narrow ones — the same “measure before you optimise” discipline that runs through SQL query optimisation.

Where pgvector Genuinely Runs Out

Four mechanisms, not vibes. If none of these describes you, you do not have a vector database problem.

1. Multitenancy with a shared index. This is the sharpest one. When tenants share an approximate index, one tenant’s vectors affect the recall and speed of another tenant’s queries. That is not a bug; it is what a shared graph means. The documented fix is list partitioning or separate tables per tenant — workable for dozens of tenants, awkward for thousands, and genuinely painful when tenants are created at signup.

2. Memory pressure. The index does not have to fit in RAM, but you want it to. Growth pushes you up the instance-size ladder, and vertical scaling has a ceiling and a price curve.

3. Operational drag on large indexes. Vacuuming an HNSW index takes a while, to the point that the documentation recommends reindexing first to speed it up. Index builds compete for maintenance_work_mem. In production you build with CREATE INDEX CONCURRENTLY to avoid blocking writes, which is slower again. None of this is fatal — it is real work someone on your team now owns.

4. Horizontal scale. Postgres scales vertically for this workload. Beyond that you are looking at read replicas, or sharding with Citus or PgDog. That is a fine answer, and it is also the point where “we already run Postgres” stops being the simple option.

Worth noting what is not on this list: raw row count. A non-partitioned Postgres table holds up to 32 TB by default. Storage is not the constraint. The index and its memory are.

What a Dedicated Vector Database Actually Sells You

Take Pinecone as the reference implementation, since it is what most people are comparing against.

What you are buying is not better similarity math. It is:

• Namespaces. Records partition into namespaces, created implicitly on write, and every read and write targets exactly one. This is tenant isolation as a first-class primitive rather than a partitioning scheme you maintain — up to 100,000 namespaces per index on the Standard and Enterprise plans.
• Serverless elasticity. Indexes are backed by distributed object storage. Capacity is not an instance you resize at 2 a.m.
• Metadata filtering as a designed feature , with the operator set you would expect ($eq, $in, $gte, $and and friends) — though within real constraints: flat JSON only, no nested objects, no nulls, 40 KB of metadata per record, and a 10,000-value cap on $in.
• An operational model you do not run. No vacuum, no reindex window, no maintenance_work_mem.

And the compliance and isolation tiers, which is often the actual purchase order: SOC 2 across all plans, GDPR and ISO 27001 from the Builder tier, a HIPAA add-on on Standard, dedicated read nodes so queries do not share a queue, and bring-your-own-cloud on Enterprise.

The pricing shape matters more than any single number. Pinecone’s Standard plan carries a 50 dollar monthly minimum , then bills storage at 0.33 dollars per GB per month, write units at 4 to 4.50 dollars per million, and read units at 16 to 18 dollars per million, varying by cloud and region. Enterprise starts at a 500 dollar minimum with a 99.95% uptime SLA.

Read that structure carefully. Storage is cheap — a million 1,536-dimension embeddings is single-digit gigabytes, so a couple of dollars a month. The variable that moves your bill is read units , meaning query traffic. A vector database’s cost scales with how much you search, not how much you store. That is the opposite intuition from most databases, and it is the number to model before you migrate.

The Decision

There is a third option people forget: managed Postgres with pgvector. AWS RDS, Google Cloud SQL and AlloyDB, and Azure Database for PostgreSQL all offer it — and AlloyDB layers its own ScaNN index on top. You get the JOINs and the transactions and someone else’s pager, without adding a second data store. For a large share of teams that is the actual right answer, and it never appears in a vendor comparison chart because no vendor is selling it to you.

The Honest Answer

Start on Postgres. Not because it wins a benchmark, but because it removes a decision you are not yet equipped to make, and because retrieval quality — chunking, embedding choice, re-ranking — will dominate your results long before storage does.

Then measure. pgvector documents how: run the query with enable_indexscan = off to get exact results, compare against the approximate ones, and you have a real recall number instead of a feeling.

BEGIN;

SET LOCAL enable_indexscan = off; -- exact search, perfect recall

SELECT id FROM documents ORDER BY embedding <=> $1 LIMIT 10;

COMMIT;

Move when a specific mechanism breaks — tenant isolation you cannot partition your way out of, an index that no longer fits an instance you want to pay for, or an operational burden your team should not be carrying. Those are real reasons, and you will recognise them without a chart.

Migrating from pgvector to a dedicated engine later is a well-trodden path: you re-embed nothing, you copy vectors and metadata. The expensive mistake is the other direction — running a second database from day one for a workload that would have fit in a table, and paying for that split in every query, every backup and every incident for years.

The default answer to “do you need a vector database?” is no. Know precisely which of the four mechanisms would change that, and you will know when the answer flips.

Originally published at alekseialeinikov.com