Pgvector vs Vector Database: When Postgres Is Enough and When to Switch

You end up running two databases that must agree on every row, and many of the hardest bugs move into the gap between them. A deleted document can still show up in search. A permission change can lag behind the vectors.

Picture a common setup. A help-desk software company in Pune stores 1 lakh (100,000) help articles for 100 customer accounts in PostgreSQL. The team wants a "similar articles" box, so someone suggests adding a separate vector database. The setup is made up, but every number in this guide comes from a real run.

The pgvector vs vector database question is really a reuse-or-buy decision. Reuse means adding the pgvector extension to the Postgres you already run. Buy means adding a separate system and a sync pipeline to feed it. This guide gives you measured cutoffs for that decision, not opinions.

It does not explain how approximate search indexes work inside. The SevyDevy guide Vector Database Architecture Explained covers that. Here we only care about what the indexes cost and when Postgres runs out of room.

You need about ten terms. Each one is short, and the rest of the guide uses exactly these names.

• Embedding: a vector, meaning a list of numbers, made from a piece of text so that similar texts get nearby vectors. Our vectors have 768 numbers each, called 768 dimensions.
• Nearest-neighbour search: finding the stored vectors closest to a query vector. Closeness here is cosine distance, which compares the direction of two vectors.
• Exact search: comparing the query with every row. It is always right but gets slower as rows grow.
• Approximate search (ANN, approximate nearest neighbour): using an index to check only some rows. It is much faster but can miss a few true neighbours.
• Recall@10: out of the true 10 nearest rows, how many the search returned. A recall of 0.95 means 9.5 of 10 on average.
• HNSW and IVFFlat: the two approximate index types in pgvector. HNSW is a layered graph. IVFFlat splits vectors into groups called lists and searches only some lists.
• hnsw.ef_search and ivfflat.probes: the query-time dials. ef_search sets how many candidates HNSW keeps while searching (default 40). probes sets how many IVFFlat lists to scan (default 1).
• p50 latency: the middle query time. Half the queries were faster, half slower.
• maintenance_work_mem: the Postgres setting that caps memory for building an index. Its default is 64 MB.
• Tenant: one customer account in a shared table. Filtering by tenant is common in software sold to many customers.

Start with pgvector when your data already lives in Postgres and the index fits in memory. Move to a dedicated vector database when one Postgres server cannot hold or serve the vector workload, even after the tricks below.

pgvector is an open-source Postgres extension. As of 24 October 2026 its latest release is 0.8.7, from 1 October 2026. It adds vector column types, distance operators and the two index types. Your vectors then sit in the same rows, transactions and backups as the rest of your data.

Because a single database keeps the vector, the row and its permissions in one transaction, so one commit changes them together. With two stores, every insert, update and delete must travel through a sync pipeline, and each hop can fail.

In the help-desk example, an article has a title, a body, an account and an embedding. In Postgres, deleting the article removes its vector in the same commit. With a separate store, a background worker must notice the delete and repeat it there. Until it does, search can still return the article.

pgvector also inherits Postgres plumbing you already run. Its README notes that pgvector writes to the write-ahead log (WAL), the change log Postgres replays. So streaming replication, which keeps standby copies in sync, and point-in-time recovery, which restores to a past moment, cover vectors too.

The second store is not wrong. It is a cost you pay every day, so it should buy you something you measured. The rest of this guide measures what Postgres can do first.

We built PostgreSQL 16.15 with pgvector 0.8.7 from its tagged source and timed real queries. Every number below is from our run on 2 vCPUs and 8 GB RAM, PostgreSQL 16.15, pgvector 0.8.7. Treat them as one honest run, not a universal benchmark.

• Machine: a cloud virtual machine with 2 vCPUs (Intel Xeon at 2.80 GHz) and 8 GB RAM, Ubuntu 24.04, and shared_buffers, Postgres's own memory cache, set to 2 GB.
• Data: 100,000 rows of 768-number vectors, unit length, compared with cosine distance. Each row also has an account (1 of 100) and a product area (1 of 10).
• Queries: 200 held-out query vectors. We computed the true top 10 for each with exact maths in NumPy, then scored recall@10.
• Timing: p50 and p95 (the time 95% of queries beat) as seen by a Python client on the same machine, after warming the cache.

Real embeddings are not random noise, but no single synthetic set looks exactly like them. So we generated three sets of 768-number vectors. The table shows why that mattered.

Same settings, recall from 4% to 98%. Pure random vectors have no neighbourhood structure, so treat them as a stress test, not a forecast. We use the middle set from here on, because it has structure without being trivially clustered.

The lesson is not the exact percentage. It is that nobody else's benchmark tells you your recall. You must measure it on your own vectors, and the last section shows how.

HNSW gave the best recall for the time spent, while IVFFlat built about 30 times faster. Both crushed exact search on speed. Here is the embedding-like set, 100,000 vectors.

HNSW used its defaults: m 16, the links per graph node, and ef_construction 64, the search width while building. IVFFlat used the README's starting point of rows divided by 1,000 for lists. The README suggests starting probes at the square root of lists, which is 10 here. On our data that start gave only 66% recall.

halfvec stores each number in 2 bytes instead of 4. Indexing the column cast to halfvec halved the index to 195 MB with the same recall. The query must use the same cast, or the planner cannot use the index.

Exact search is not useless. On the same machine it took 37 ms at 10,000 rows and 96 ms at 30,000 rows, with perfect recall. For a small table with a relaxed latency budget, you may not need an approximate index at all.

Because pgvector applies your WHERE clause after the approximate index has picked its candidates. The pgvector README says this directly. If the index hands over 40 candidates and only 10% match the filter, you get about 4 rows, not 10.

Think of a librarian who fetches the 40 books closest to your topic, then you throw away every book not in Hindi. If one book in ten is in Hindi, about four survive. The librarian's 40 is ef_search. The Hindi rule is your WHERE clause. Asking the librarian to keep fetching until you have ten is an iterative index scan.

Where the picture breaks: the librarian walks a graph, not a shelf, and later fetches are not always in perfect distance order. That is why pgvector offers two orderings.

What iterative index scans changed in our run

pgvector 0.8.0 added iterative index scans. With hnsw.iterative_scan set to strict_order or relaxed_order, the index keeps searching until enough rows pass the filter. It stops at hnsw.max_scan_tuples, which defaults to 20,000 visited tuples, or at a memory limit set by hnsw.scan_mem_multiplier. The planner, the part of Postgres that picks how to run each query, may also skip the vector index entirely.

Three things stand out. First, relaxed_order gave better recall than strict_order, as the README says it should. Second, for the 1% filter the planner skipped HNSW and did an exact search on the matching rows, which was both perfect and faster than forced HNSW. Third, forcing HNSW with the default 20,000-tuple limit worked, but lowering it to 1,000 dropped recall to 0.32.

Why the exact path was so quick

Each 768-number vector takes 3,080 bytes, which the README gives as 4 times dimensions plus 8. When a row is wider than about 2 kB, Postgres moves large values out of the row into separate TOAST storage. In our table, the main rows took 6.7 MB and the vectors took 391 MB.

So a filtered exact scan reads the small main table, then fetches vectors only for matching rows. The README warns that the planner does not count this out-of-line storage in its cost estimates, so its choices can surprise you.

Iterative scans fix missing rows, not wrong rows

On the easy clustered set, an IVFFlat query with 10 probes and a 1% filter returned all 10 rows, yet recall was only 0.496. Turning on ivfflat.iterative_scan changed nothing, because the scan already had 10 rows. Iterative scans only kick in when rows run short.

Other ways to make filters cheap

• A B-tree index on the filter column lets the planner fetch just the matching rows and compare them exactly. In our run this was the best plan for a 1% tenant filter.
• A partial index is an index built over a subset of rows, such as one product area. The pgvector README suggests it when you filter by only a few distinct values.
• Partitioning splits one table into child tables by a column, such as account. The README suggests it for many distinct values and for tenant isolation.

In our run, one 768-number vector cost about 4 KB of HNSW index on disk, and about 3.8 KB of build memory. The index should fit in RAM for fast queries, and the build should fit in maintenance_work_mem.

The build cliff at maintenance_work_mem

When the graph being built outgrows maintenance_work_mem, pgvector prints a notice and switches to a slower on-disk build. In the 0.8.7 source, the code flushes the graph to disk and inserts every later row through its on-disk path.

The notice in our log read: hnsw graph no longer fits into maintenance_work_mem after 17699 tuples. Its hint said to increase maintenance_work_mem. Running short made the easy and random builds 3.2 to 3.6 times slower than with 1 GB.

You can turn this into a rule for your own table. 64 MB held 17,699 vectors and 256 MB held 70,810, so each vector needed about 3.8 KB. A million such vectors would need about 3.8 billion bytes, so a maintenance_work_mem of 4GB. That figure is our arithmetic for m 16 and 768 dimensions, undocumented as of 24 October 2026; pin your version and re-measure.

Turning sizes into a cutoff

Our 100,000-vector table took 404 MB and its HNSW index took 391 MB. Scaling linearly, which we did not test, 1 million vectors means about 4 GB of table plus 4 GB of index. At 10 million, about 40 GB of index alone.

That is the main pgvector vs vector database cutoff. If the index fits comfortably in the RAM of a server you would happily run, stay. The README lists the next steps before you leave: halfvec for tables, binary quantization for indexes, more memory and read replicas. Binary quantization keeps one bit per number in the index, then re-checks the top results with full vectors. After that come sharding tools, which split one table across servers.

Much slower than without one, because every insert also adds the vector to the graph. In our run, 2,000 single-row inserts, each its own transaction, ran at 142 rows per second with HNSW, against 8,252 rows per second with no vector index.

For the help-desk example that is fine. Articles change a few hundred times a day, not per second. For a bulk import, load the rows first and build the index once afterwards, which is what the README recommends.

• Build big indexes with CREATE INDEX CONCURRENTLY in production, which the README advises so the build does not block writes.
• Watch progress with the pg_stat_progress_create_index view. We used it to follow our slow 64 MB builds.
• Run heavy vector searches on a read replica, a read-only copy server, if they compete with your main app for CPU on the primary.

If you need thousands of vector writes per second on top of a busy app, measure a dedicated store under the same load. Do not assume a separate store makes index upkeep free.

A dedicated vector database earns its keep when you hit a limit you measured and the Postgres fixes cost more than a second system. These are the signals we would act on.

• The index will not fit in the memory of one Postgres server, even with halfvec or binary quantization, and nobody on the team wants to run sharded Postgres.
• Search traffic needs to scale separately from your transactional database, for example many search nodes for one write path.
• Filtered queries with many mixed conditions stay below your recall target after iterative scans, B-trees and partitions.
• You need a feature pgvector does not ship, such as an index type built to search from SSD instead of RAM.

Dedicated stores attack these limits in documented ways. Milvus's architecture page describes separate storage and compute layers that scale out compute nodes. Its docs also describe DiskANN, an on-disk index meant for NVMe SSDs. Qdrant's indexing docs describe payload indexes, built before the HNSW graph, that add filter-aware links to it.

Each of those still needs a sync pipeline, its own backups and its own upgrade plan. Before you switch, run the same recall and latency test from this guide against the candidate. Compare numbers, not feature lists.

Create the table, build the index with enough memory, query with the right settings, then compare approximate results with exact ones. We ran each block below on PostgreSQL 16.15 with pgvector 0.8.7 against the 100,000-row table. The table keeps only the columns the search needs.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE articles (
  id         bigint PRIMARY KEY,
  account_id int    NOT NULL,
  area_id    int    NOT NULL,
  embedding  vector(768)
);

-- after loading rows:
SET maintenance_work_mem = '1GB';
CREATE INDEX articles_embedding_hnsw
  ON articles USING hnsw (embedding vector_cosine_ops);
CREATE INDEX articles_account_id ON articles (account_id);

SELECT pg_size_pretty(pg_relation_size('articles_embedding_hnsw')) AS hnsw_size;

Expect hnsw_size to read 391 MB for 100,000 vectors of 768 numbers. If you see a notice about maintenance_work_mem, the build went to disk and will take far longer.

Your app's similar-articles query orders rows by pgvector's cosine distance operator, then takes LIMIT 10. The operator is three characters: a less-than sign, an equals sign and a greater-than sign. Only this operator form can use the HNSW index.

Wrap that query in a transaction so its settings apply to it alone. SET LOCAL does exactly that.

BEGIN;
SET LOCAL hnsw.ef_search = 200;
SET LOCAL hnsw.iterative_scan = relaxed_order;
-- run the filtered top-10 query here:
-- WHERE area_id = 3, ordered by the cosine distance operator, LIMIT 10
COMMIT;

Put EXPLAIN in front of the query to see the plan. With ef_search at 200 our planner used the HNSW index for a 10% area filter. At 100 it chose an exact scan of the table instead, so always check.

To measure recall, you need the true top 10. pgvector's cosine_distance function computes the same distance, and in our run the planner served it with a full exact scan. That makes it a handy source of truth.

CREATE TEMP TABLE exact AS
  SELECT id FROM articles
  WHERE id != 42
  ORDER BY cosine_distance(embedding, (SELECT embedding FROM articles WHERE id = 42))
  LIMIT 10;

Save your app's approximate top 10 for article 42, with the same WHERE clause, into a temp table named approx. Then count the overlap.

SELECT count(*) AS found_out_of_10 FROM approx JOIN exact USING (id);

found_out_of_10 is recall for one query; our run printed 10 at ef_search 100. The pgvector README suggests the same idea for monitoring: compare approximate results with exact ones. Repeat it for 100 or more real queries and average the result before you trust a setting.

1. 1

   Count your vectors

   Multiply rows by dimensions. At 768 dimensions, budget about 4 KB of HNSW index per vector, then check it against your server's RAM.

2. 2

   Build with enough memory

   Set maintenance_work_mem for the build session. Watch for the graph no longer fits notice.

3. 3

   Pick ef_search from recall

   Sample real queries, run the recall check, and raise ef_search until the average passes your target.

4. 4

   Test your real filters

   Turn on relaxed_order, add B-trees on tenant columns and read EXPLAIN for each common filter.

5. 5

   Decide with numbers

   Only if memory, filters or write load fail these tests, run the same checks on a dedicated store.

Frame it as reuse or buy, then size it, then name the trigger that would make you switch. Interviewers want the trade-off, not a product name.

1. 1

   State the default

   Say you would start with pgvector because the data is already in Postgres, and one store keeps deletes and permissions consistent.

2. 2

   Size the index

   Estimate rows times about 4 KB for a 768-number HNSW index with default settings, and compare with one server's RAM.

3. 3

   Name the recall dial

   Explain that ef_search trades latency for recall and that you would measure recall against exact search.

4. 4

   Handle filters

   Mention post-filtering, iterative index scans, B-trees for selective tenant filters and partitions for isolation.

5. 5

   Give the switch trigger

   Say you would move to a dedicated store when the index outgrows one machine or search must scale on its own.

6. 6

   Mention the cost of switching

   Name the sync pipeline, eventual consistency on deletes, and a second system to back up and upgrade.