When to leave pgvector for a dedicated vector database

Quick answer: Leave when a limit you wrote down first fails, after you have tried the pgvector designs below. There is no universal first limit: whichever of index residency, write rate and filtering your workload hits is the one that decides. This page measures all three on one disclosed bench so you can see the shape, then tells you what to measure on yours.

What is the question?

“Is pgvector good enough” is not answerable in general, because three different things run out at different scales and for different reasons:

• Residency. Whether the index, the heap, the metadata indexes and every other embedding index stay in memory together at your concurrency.
• Write rate. Whether maintaining the approximate index keeps up with your inserts, your metadata updates and your re-embedding cadence.
• Filtering. Whether a filtered search returns enough rows, in the right order, at your recall target and your latency budget.

Which one goes first depends on the corpus, the hardware, the embedding model, the index parameters and the service level you promised. The decision therefore needs variables, not a verdict. Write down these values before you test anything:

variable	what to write down
corpus	total vectors, dimensions, number of embedding models, number of indexes, how much of it must be cache-resident
write shape	inserts, metadata-only updates, vector rewrites, deletes, burst rate, batch window
query service level	recall target, k, p50, p95 and p99 latency, concurrency, warm against cold cache
filter shape	tenant isolation, predicate selectivity, predicates per query, whether SQL joins are essential
operations	WAL volume, replication lag, backup and restore time, vacuum and reindex windows, availability during an index build
consistency	whether the vectors and the rows they describe may ever disagree

“Leave pgvector” should mean that, after an appropriate schema, index and operational design, pgvector still misses one of those numbers.

The bench

Everything below ran on 2026-09-19 and 2026-09-20 on PostgreSQL 17.11 with pgvector 0.8.6 in a pgvector/pgvector:0.8.6-pg17 container, on a host with 8 vCPUs and 62 GB of RAM running Linux 6.17.0-1022-azure under Docker 29.1.3, at the image’s default server settings.

Those two dates are two series, not one, and every number below says which it belongs to. The corpus, the index sizes and the recall numbers were measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container. The write-cost table and everything taken from those runs were measured on 2026-09-20 on that same container, as five runs one after another on the same fixture.

The corpus loaded into that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container on 2026-09-19 is 100,000 rows of vector(768). It is synthetic and its shape is the first thing to distrust: 100 cluster centres, each dimension drawn from a standard normal distribution, and each row its centre plus normal scatter with a standard deviation of 0.4 per dimension. The tenant and status columns were assigned at random and independently of the cluster, so a metadata filter is a real filter and not a disguised cluster selection.

An earlier version of this corpus was uniform on [0, 1] in every dimension. That one dimension of the design broke two different results, which is why the generator is disclosed rather than just the row count. Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, every vector in it quantized to the same 768 one bits, so binary quantization was meaningless, and the exact top 10 were near-ties, so recall measured the tie-break and not the index.

Every index on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container on 2026-09-19 is m = 16, ef_construction = 64, built with maintenance_work_mem = '2GB' and max_parallel_maintenance_workers = 0. The defaults observed on that image were 64MB and 2, and the second one matters in a container: parallel maintenance workers at this maintenance_work_mem exhaust a 64 MB /dev/shm.

Recall is measured against exact ground truth on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container on 2026-09-19. The 200 query vectors come from the same generator as the corpus and are not corpus rows, so nothing is guaranteed a distance-zero hit. The truth set for each is the exact top 10 by L2 distance at full precision, from a sequential scan with enable_indexscan and enable_bitmapscan off. Recall@10 is the fraction of those 2,000 exact neighbours the approximate scan also returned.

Residency: measure the size, do not extrapolate a rule

Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, the sizes were:

what	size	build time
heap	7528 kB
TOAST, holding the vectors	395 MB
table, heap plus TOAST	402 MB
HNSW over vector(768)	391 MB	76.3 s
HNSW over the halfvec(768) expression	195 MB	66.5 s
HNSW over the binary_quantize bit(768) expression	38 MB	30.8 s
IVFFlat over vector(768), lists = 316	392 MB	9.6 s
primary key	2208 kB

The first row is easy to miss. Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, the average vector(768) was 3076 bytes, which is over the TOAST threshold, so the vectors live out of line and the heap holds pointers. “A 402 MB table” is pg_table_size, not a heap you can reason about page by page. This affects how you read both the residency question and the update results further down.

Do not turn the 391 MB against 402 MB into a rule. HNSW index size depends on m, on ef_construction, on dimensions and on the data itself, and the ratio that came out near 1 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container on 2026-09-19 is a result from this corpus at these parameters. Measure yours with pg_relation_size.

SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
ORDER BY pg_relation_size(c.oid) DESC;

Nor is “a few GB fits in memory” the residency question. Your working set is the index plus the heap plus the primary key plus every metadata index plus every other embedding index plus whatever else the database is doing. What matters is latency at your concurrency, not whether one number is smaller than RAM. An HNSW index does not have to be resident, although queries are faster when it is.

Halve the index before you migrate

halfvec stores 16-bit floats, so the raw payload goes from 4 bytes per dimension plus overhead to 2. You do not need a second stored column for it: an expression index over the cast keeps one full-precision source of truth.

The table used by the examples below has the two metadata columns described by the bench above:

CREATE TABLE items (
  id        bigserial PRIMARY KEY,
  tenant    int  NOT NULL DEFAULT 1,
  status    text NOT NULL DEFAULT 'active',
  embedding vector(768)
);

CREATE INDEX items_hnsw_half ON items
  USING hnsw ((embedding::halfvec(768)) halfvec_l2_ops)
  WITH (m = 16, ef_construction = 64);

The query has to use the matching expression or the index is not usable:

SELECT id
FROM items
ORDER BY (embedding::halfvec(768)) <-> (SELECT embedding FROM items WHERE id = 1)::halfvec(768)
LIMIT 10;

Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, recall@10 over the 200 queries against the exact truth set:

ef_search	vector recall@10	halfvec recall@10
40	0.911	0.942
100	0.978	0.983
400	0.993	0.988

Consider the size of those differences, not their direction. Half precision came out ahead at two settings and behind at one, on separately built graphs. That looks like graph-to-graph variation rather than a precision effect. On this corpus, half precision cost nothing detectable for half the index. Whether it costs nothing on your relevance set depends on your embeddings, your distance metric and your product’s relevance target, and you can answer it in an afternoon.

Binary quantization is not the same bargain

Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, an HNSW index over binary_quantize(embedding)::bit(768) was 38 MB, a tenth of the full index, and returned recall@10 of 0.067 at ef_search 400. Taking its top 100 candidates and reranking them by exact distance, which is the pattern pgvector documents, brought that to 0.368. That is still far below the full index on this corpus.

SELECT c.id
FROM (
  SELECT id, embedding
  FROM items
  ORDER BY binary_quantize(embedding)::bit(768) <~>
           (SELECT binary_quantize(embedding)::bit(768) FROM items WHERE id = 1)
  LIMIT 100
) c
ORDER BY c.embedding <-> (SELECT embedding FROM items WHERE id = 1)
LIMIT 10;

Treat it as a trade. One sign bit per dimension throws away most of the information in data whose dimensions are not cleanly separated, and how much that costs depends on your embedding model.

IVFFlat is the other build-cost lever

Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, the IVFFlat index built in 9.6 seconds against 76.3 for HNSW at roughly the same size. pgvector states the trade in its own documentation.

“It has better query performance than IVFFlat (in terms of speed-recall tradeoff), but has slower build times and uses more memory.” (pgvector README, read on 2026-09-19)

If your workload rebuilds the index on a schedule rather than maintaining it row by row, that eight-fold build difference may matter more than the query difference.

Build memory is another lever, and pgvector is explicit about it.

“Indexes build significantly faster when the graph fits into maintenance_work_mem.” (pgvector README, read on 2026-09-19)

Writes: the limit update-heavy workloads meet

This number needs the whole method to be useful.

Measured on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, each run below rebuilt the table from items, added the primary key, built the stated indexes at maintenance_work_mem = '2GB' with parallel maintenance off, ran VACUUM (ANALYZE), reset the table’s statistics, ran a CHECKPOINT, and then ran one UPDATE of the 10,000 rows with id <= 10000 under EXPLAIN (ANALYZE, BUFFERS, WAL). No run inherited another run’s bloat or page layout, and the checkpoint before each one is what makes the write-ahead log columns comparable, because the first write to a page after a checkpoint carries a copy of the whole page.

state of the table	the UPDATE	time, ms	rows in place	WAL records	full-page images	WAL bytes
HNSW, fillfactor 100	SET touched = now()	33,569.316	0 of 10,000	44,677	7,246	52,382,317
HNSW, fillfactor 70	SET touched = now()	19,533.675	4,365 of 10,000	37,986	4,957	36,009,519
HNSW plus a btree on status, fillfactor 70	SET status = 'reindexed'	40,360.251	0 of 10,000	58,544	7,806	56,744,254
HNSW, fillfactor 70	SET embedding = (a different row's vector)	38,418.874	0 of 10,000	108,389	13,308	125,553,071
no vector index, fillfactor 100	SET touched = now()	50.800	0 of 10,000	30,139	127	3,489,342

All five rows are one series, run one after another on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container. Row four is not from a different day.

Read the records column before the bytes column. Full-page images make up most of the byte count in every row that touches the graph, and they depend on how recently the page was checkpointed rather than on the update itself. The records column counts index work: 37,986 when 4,365 of the 10,000 updates stayed on their page, and 58,544 when none of them could.

The mechanism is heap-only tuple updates, and PostgreSQL states both conditions:

“The update does not modify any columns referenced by the table’s indexes, not including summarizing indexes.” (PostgreSQL 17, Heap-Only Tuples)

“There is sufficient free space on the page containing the old row for the updated row.” (PostgreSQL 17, Heap-Only Tuples)

Row one fails the second condition. The table was bulk loaded at the default fillfactor of 100, so pages are packed, and measured on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container not one of the 10,000 updates stayed on its page. Every one needed a new entry in the graph, producing 44,677 write-ahead log records against 37,986 in the next row for the identical statement.

Row three fails the first condition, and it is the row to read carefully. Measured on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, at the same fillfactor, on the same 10,000 rows, with the same headroom on the page: changing a column that an ordinary btree covers took heap-only updates from 4,365 to 0 and the time from 19,533.675 ms to 40,360.251 ms. The blocked column does not have to be the vector. Any indexed column will do it, including one covered by an expression index or by the predicate of a partial index. The exception PostgreSQL names is summarizing indexes, which in core means BRIN.

Note too that PostgreSQL compares values rather than columns touched, and that case is worth running rather than assuming. On the row-three table, with the btree on status in place, UPDATE u SET status = status WHERE id <= 10000 measured on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container gave 4,365 heap-only updates of 10,000 in 25,128.713 ms, a sixth run built the same way as the five in the table. The heap-only count is 4,365, the result from row two, while row three, which has the same pair of indexes and writes a different value, gave 0. The time sits between the two rows and nothing here establishes why. Naming an indexed column in SET does not block a heap-only update; writing a different value into it does. That is also the trap in reproducing this: UPDATE items SET embedding = embedding does not test re-embedding at all, because the value has not changed and the update stays heap-only. Row four replaces each vector with a different one, taken from another row.

Row four is the re-embedding case, and it is the one to price if you change embedding models. Measured on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container it had full page headroom and still not one of the 10,000 updates stayed in place, because the column being written is the one the graph indexes. It also generated the most write-ahead log of any run at 108,389 records and 125,553,071 bytes, which is the volume that reaches your replicas and your backups.

fillfactor is a real lever, but it is not a cure. PostgreSQL describes what it does.

“When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page.” (PostgreSQL 17, CREATE TABLE)

So a fillfactor of 70 reserves about 30 percent of each page at load time, per that same PostgreSQL 17 documentation read on 2026-09-19. It does not permanently cost 30 percent, because that space is what the updates then consume, and page occupancy moves with churn and vacuuming. The same documentation says this is a probability, not a guarantee.

“This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page, and makes heap-only tuple updates more likely.” (PostgreSQL 17, CREATE TABLE)

The counter to watch is n_tup_hot_upd against n_tup_upd:

SELECT n_tup_upd, n_tup_hot_upd, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'items';

What the elapsed time does not include

Foreground update latency is only part of the cost. Every one of those 10,000 updates also created a dead tuple, and measured on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container each run left 10,000 dead tuples behind, plus dead entries in the graph. Cleaning that up has its own cost, and pgvector says so.

“Vacuuming can take a while for HNSW indexes. Speed it up by reindexing first.” (pgvector README, read on 2026-09-19)

A write-capacity model therefore needs WAL generated, dead tuple growth, autovacuum lag, index and table bloat, checkpoint behaviour and replica impact. A single elapsed time is only the beginning.

Before you leave: the designs to try, in order

The gap between “HNSW updates are slow” and “we need a different database” has several options. Roughly in order of effort:

1. Do not update what you do not have to. An unnecessary metadata write on a vector table is the most expensive kind.
2. Preserve heap-only updates. Keep mutable columns out of every index, and set fillfactor from the measured ratio rather than from a default.
3. Isolate the embeddings. A separate table keyed to the parent means unrelated updates never touch a page the vector index covers.
4. Load first, index second. Bulk COPY then CREATE INDEX beats maintaining the graph row by row, with maintenance_work_mem raised for the build and CREATE INDEX CONCURRENTLY where availability requires it.
5. Swap partitions instead of mutating rows. Build the next partition offline, validate it, attach it. This turns a nightly re-embed from row by row graph mutation into a build and a cutover.
6. Change the representation. halfvec through an expression index first, then binary quantization with reranking if the recall survives it.
7. Change the index. IVFFlat when build cost dominates query cost.
8. Index the filter, not just the vector. Ordinary, multicolumn and partial indexes, covered below.
9. Scale PostgreSQL. Read replicas for query load, and sharding approaches for index size.

Only after those should a dedicated system be compared, and then on equivalent recall, filters, consistency, durability, write rate and operational burden.

Filtering: the limit that surprises people

With an approximate index, the filter is applied to the candidates the index returned, so a filtered search can return fewer rows than requested. pgvector puts a number on this:

“With approximate indexes, filtering is applied after the index is scanned. If a condition matches 10% of rows, with HNSW and the default hnsw.ef_search of 40, only 4 rows will match on average.” (pgvector README, read on 2026-09-19)

That prediction held. Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, with a predicate matching 5,074 of the 100,000 rows and the same 200 queries, a LIMIT 10 returned a mean of 2.03 rows:

setting	mean rows returned	fewest	queries that got all 10
iterative_scan off, ef_search 40	2.03	0	0 of 200
iterative_scan off, ef_search 400	10.00	9	199 of 200
relaxed_order, ef_search 40	10.00	10	200 of 200
strict_order, ef_search 40	10.00	10	200 of 200

Iterative scans address this and are opt-in. Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container the defaults were hnsw.iterative_scan off, hnsw.max_scan_tuples 20000 and hnsw.scan_mem_multiplier 1. Installing pgvector 0.8 does not turn them on:

SET hnsw.iterative_scan = strict_order;

The two modes differ in what they promise.

“Strict ensures results are in the exact order by distance.” (pgvector README, read on 2026-09-19)

“Relaxed allows results to be slightly out of order by distance, but provides better recall.” (pgvector README, read on 2026-09-19)

Both still stop at a budget. hnsw.max_scan_tuples bounds how far the scan will go, so a predicate selective enough can still exhaust it and return fewer rows.

The approximate path is not the only plan

“A vector index chooses candidates before your WHERE clause” describes the common plan, not every plan. Measured on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, four ways of answering one query: LIMIT 10 with tenant = 7, which matches 5,074 rows, run over the same 200 query vectors, with the mean of those 200 runs reported:

design	mean rows for LIMIT 10	recall@10	mean time
full HNSW index, iterative_scan off, ef_search 40	2.03	0.196	3.26 ms
full HNSW index, strict_order, ef_search 40	10.00	0.921	5.83 ms
full HNSW index, relaxed_order, ef_search 40	10.00	0.970	4.86 ms
a partial HNSW index on tenant = 7, ef_search 40	10.00	1.000	0.35 ms

Every row is the same predicate, the same 200 queries and the same day, so the last row is a comparison rather than a coincidence: the partial index is the fastest of the four and the only one that was exact.

Measured on 2026-09-20 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, an HNSW index over only those 5,074 rows was 20 MB against 391 MB for the full index and built in 2.23 seconds:

CREATE INDEX items_hnsw_t7 ON items
  USING hnsw (embedding vector_l2_ops)
  WITH (m = 16, ef_construction = 64)
  WHERE tenant = 7;

The planner will also decline the approximate path when the filter is selective enough. Measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container, a different predicate matching 269 rows, 0.27% of the table, with no index on the filter column, gave a sequential scan plus a top-N heapsort at 18.9 ms: exact, all 10 rows, no vector index used. Confirm that plan rather than assuming it, because it depends on statistics, selectivity and the indexes that exist.

pgvector’s filtering guidance is a ladder rather than a single answer: an ordinary index on the filter column, a multicolumn index, a partial index for a few discrete values, and partitioning for many. A shared index across tenants also couples one tenant’s recall and latency to another’s, which is a reason to partition or separate.

An approximate index can also return fewer rows for reasons unrelated to filters: NULL vectors are not indexed, zero vectors are not indexed for cosine distance, and dead tuples can constrain what comes back.

What a dedicated vector database offers

These are things to evaluate, not measurements, because none of them was run here and a comparison assembled from vendor pages is not evidence:

• Sharding the index across machines. pgvector’s index lives on one primary.
• Quantization schemes beyond half precision, with the reranking and calibration machinery around them.
• Filtering built into the search rather than applied to its output.
• Operational tooling shaped for this workload, including rebuilds that do not block.

Evaluate one on your own embeddings and your own filters, against the same recall, filter semantics, consistency, durability, write rate and operational burden you are holding pgvector to.

The reason to use your own data is shown here. Changing nothing but the corpus generator moved recall@10 at ef_search 40 from 0.24 to 0.911 on the same index parameters, measured on 2026-09-19 on that PostgreSQL 17.11 pgvector/pgvector:0.8.6-pg17 container. A benchmark on synthetic data measures the generator.

What staying buys you

Staying keeps the vectors in one system, with one transaction, one backup and one point-in-time recovery. The vector and the row it describes are written together and cannot disagree. A separate vector store introduces a permanent synchronisation problem that does not appear in any benchmark: dual writes or an outbox, reconciliation, lag, and a second failure model.

It also means the vector search is visible to the same tooling as the rest of your database: the same EXPLAIN, the same statistics, the same monitoring.

How do I decide?

Write the exit criteria down before you test, in your own numbers:

• Stay while p99 query latency is under your target at your recall target, and re-embedding completes inside your window.
• Redesign when the heap-only update ratio, the WAL volume or the index bloat crosses a line you set, and work down the list above.
• Evaluate leaving when the best pgvector design you have actually tested still misses one of those numbers.

Then measure three things on your own data, in this order: the write cost of your real update pattern with the index present and dropped; the resident size of everything, not just the vector index; and the filtered recall of your actual queries with your actual WHERE clauses, counting rows returned and not only milliseconds.

Leaving on a general feeling that PostgreSQL is not a vector database costs you a second system and buys you nothing you measured.

How DBGorilla helps

DBGorilla can help

DBGorilla connects read-only and gives the three numbers above somewhere to live: table and index size metrics over time alongside topology snapshots of the schema, so index growth is a curve rather than a reading; workload by query fingerprint with duration and buffer counters, so the vector search and the updates that maintain it are separate lines; and plan-only EXPLAIN against the live database. All of that was verified in a live session.

Start free

FAQ

• What actually limits pgvector?

  There is no universal first limit. For update-heavy workloads it is usually write rate. Measured on 2026-09-20 on PostgreSQL 17.11 with pgvector 0.8.6 in a pgvector/pgvector:0.8.6-pg17 container, updating 10,000 rows of a 100,000 row table took 33,569.316 ms with an HNSW index present and 50.800 ms with no vector index, on the same rows. For read-heavy workloads the first limit is usually whether the index stays resident, and for multitenant queries it is usually filtering.

• How large can a pgvector table get before it is a problem?

  Measure rather than extrapolate. Measured on 2026-09-19 on PostgreSQL 17.11 with pgvector 0.8.6 in a pgvector/pgvector:0.8.6-pg17 container, 100,000 rows of vector(768) came to 402 MB of table, almost all of it TOAST, with a 391 MB HNSW index over it. That ratio is a result from one corpus at one set of index parameters, not a sizing rule, and your working set includes the heap, the metadata indexes and every other embedding index too.

• Can I halve the index without losing quality?

  Often, and it is cheap to test. Measured on 2026-09-19 on PostgreSQL 17.11 with pgvector 0.8.6 in a pgvector/pgvector:0.8.6-pg17 container, an HNSW index over a halfvec(768) expression was 195 MB against 391 MB, with recall@10 over 200 queries of 0.942 against 0.911 at ef_search 40 and 0.988 against 0.993 at 400. Differences that small are variation between two independently built graphs, so test it on your own relevance set.

• Does binary quantization shrink the index for free?

  No. Measured on 2026-09-19 on PostgreSQL 17.11 with pgvector 0.8.6 in a pgvector/pgvector:0.8.6-pg17 container, an HNSW index over binary_quantize was 38 MB against 391 MB, and recall@10 was 0.067, rising to 0.368 when the top 100 candidates were reranked by exact distance. On this corpus that is a large loss for the space. It is a trade to measure, not a free win.

• What does a dedicated vector database give me that pgvector does not?

  Mainly sharding the index across machines, filtering built into the search rather than applied to its output, and quantization schemes beyond half precision. None of those were measured here. Before comparing one, work through the pgvector designs on this page: expression indexes, partial indexes, partitioning, opt-in iterative scans, fillfactor, and bulk load followed by an index build.

• What is the strongest reason to stay?

  The vectors sit in the same transaction, the same backup and the same point-in-time recovery as the rows they describe. A separate vector store means two systems that can disagree about what exists, and reconciling them is permanent work that never appears in the benchmark you chose it on.

Related

• Why is my pgvector HNSW index taking forever to build?