01
the decision most teams actually face
If your application already runs on Postgres and you're adding semantic search or RAG, the first architectural question is whether embeddings go into the database you have or into a separate vector store. The case for a separate store usually rests on raw nearest-neighbour speed. At the sizes most products run at, from tens of thousands to a few million chunks, that's rarely what decides it. What decides it is how your queries filter, whether the index fits in memory, and how much you value one transactional source of truth over a second system you have to keep in sync.
pgvector has been the default answer on the Postgres side for a while, and the 0.8 line fixed the weakness that made it a bad fit for multi-tenant apps. The rest of this post covers what that change does, a benchmark you can rerun on your own laptop in a few minutes, the limits around dimensions and memory that people hit in production, and the situations where we'd pick a dedicated engine instead.
Versions matter here. pgvector 0.8.0 shipped on 30 October 2024, and the current release at the time of writing is 0.8.7 from October 2026. The unreleased 0.9.0 in the changelog drops support for Postgres 13 and 14, so if you're still on those, plan the Postgres upgrade together with the extension.
02
what 0.8 changed for filtered queries
HNSW indexes arrived in pgvector 0.5.0, so 0.8 isn't about having a fast index. Its headline, per the changelog, is iterative index scans, plus better cost estimation when a query filters. Both are about the query every SaaS app writes sooner or later:
An approximate index doesn't know about your where clause. HNSW walks the graph, collects hnsw.ef_search candidates (40 by default) and only then does Postgres apply the filter. The pgvector README spells out the consequence: if the condition matches 10% of rows, you get about 4 rows back on average instead of 10. If it matches 0.1% of rows, as a tenant filter often does, you usually get nothing. The query doesn't fail, it just returns too few results, and a RAG pipeline downstream will happily answer from whatever came back.
Iterative scans make the index keep going until the filter has produced enough rows, or until it hits a limit:
strict_order returns results exactly sorted by distance, while relaxed_order allows slight reordering and gets better recall for it. If you need exact ordering on top of relaxed results, the README suggests wrapping the query in a materialized CTE and sorting again. IVFFlat has the same feature through ivfflat.iterative_scan and ivfflat.max_probes. The cost estimation change matters as much in practice: with a btree index on tenant_id, the planner can now see that a small tenant is cheaper to answer exactly, by fetching its few hundred rows and sorting them, than by walking the graph.
03
a benchmark you can rerun
Published vector benchmarks rarely match your data, your filters or your hardware, so the honest thing is to measure the one behaviour that matters here: what happens to result count and recall when you filter. The script below loads 200,000 synthetic 384-dimension vectors clustered around 1,000 random centroids, gives each row a kind that matches 10% of rows and a tenant_id that matches 0.1%, builds an HNSW index with default settings, and runs 50 queries per configuration. Recall@10 is measured against the exact answer from a sequential scan of the same query.
Start the database with docker run --rm -d -p 5432:5432 -e POSTGRES_PASSWORD=pg pgvector/pgvector:pg18 and run the script with uv run. This is the output from Postgres 18.6 with pgvector 0.8.7 in Docker on an Apple Silicon laptop, with shared_buffers at 1 GB and everything else default:
A few rows are trimmed for width; the script prints all of them. The 10% case reproduces the README's number almost exactly: about 4 rows out of 10 without iterative scans, and with relaxed_order you get full results at 0.98 recall for no measurable latency cost. Raising ef_search to 200 also works for a 10% filter, but it can't rescue a 0.1% filter, where only an iterative scan returns full results. Even then recall stays around 0.6 to 0.74 and latency jumps to about 40 ms, because the scan stops at the 20,000-tuple cap before it has seen every row the tenant owns. The last line is the real lesson: once a btree index on tenant_id exists, the planner skips the graph and answers each tenant exactly in well under a millisecond.
The data is synthetic, so treat the absolute recall numbers as a property of this distribution, not a prediction for your embeddings, and expect them to move a little between runs because HNSW builds aren't deterministic (an earlier run of the same script gave 0.972 unfiltered recall). The relationships between the rows are what transfer. For your own corpus, replace sample() with real vectors and real filters, and keep the exact-scan comparison.
04
what to do with that, per filter shape
For filters that match a small share of rows, such as tenant, user or project, a plain btree on the filter column is usually all you need, because exact search over a few thousand rows is fast and has perfect recall. Turn on hnsw.iterative_scan = relaxed_order anyway, since it costs nothing when the planner doesn't use the graph and saves you when it does. If a few tenants are big enough that exact search gets slow, the README's other two answers apply: a partial HNSW index per large tenant (create index ... using hnsw (embedding vector_cosine_ops) where tenant_id = 42) when there are only a handful of them, or list partitioning by tenant when there are many.
For broad filters that match 10% or more, such as document type, language or "current version only", iterative scans with the default settings are enough on their own. In between, measure. The script above takes a few minutes to adapt, and it will tell you more about your data than any vendor chart. Set these per transaction with set local if only some queries need them.
05
dimensions, halfvec and memory
pgvector's HNSW and IVFFlat indexes support the vector type only up to 2,000 dimensions. halfvec goes up to 4,000, bit to 64,000, and sparsevec HNSW indexes take up to 1,000 non-zero elements. That catches anyone who picks OpenAI's text-embedding-3-large, which returns 3,072 dimensions by default, and the error is blunt: column cannot have more than 2000 dimensions for hnsw index. You can store the full-precision vector and index a half-precision cast of it:
The query has to repeat the cast exactly or Postgres won't use the index. The alternative is the dimensions parameter in OpenAI's API, which returns shorter vectors you can index as plain vector, at some cost in quality that you should measure on your own evaluation set.
Memory is where self-hosted pgvector deployments actually struggle. Each vector takes 4 * dimensions + 8 bytes, so a 1,536-dimension vector is 6,152 bytes before any row overhead. Loading 100,000 random 1,536-dimension vectors into a table on the same setup shows the real footprint: the table with its TOAST data came to 795 MB, an HNSW index with default settings to 781 MB, and an HNSW index on the halfvec cast of the same column to 391 MB. That scales roughly linearly, so a million such chunks means around 8 GB of table plus 8 GB of index, or 4 GB with halfvec. HNSW search is fast when the index pages are in memory and much slower when they come from disk, so size shared_buffers and the machine's RAM for the index you'll actually query, and remember that every replica serving reads needs the same.
Index builds deserve the same planning. Build after the initial load rather than inserting into an indexed table, give the session enough maintenance_work_mem, and watch for pgvector's notice that the graph no longer fits into it, which the README says means the build will take significantly longer. Builds and inserts into a large HNSW index use CPU on the primary, which is a real cost if that primary also serves your transactional traffic.
06
what staying in postgres buys you, and when it stops being worth it
The biggest gain from keeping vectors in Postgres is consistency. A chunk and its embedding commit in the same transaction, a deleted document takes its vectors with it through a foreign key, and permission filters are a join against the tables that already define who can see what, including row-level security if you use it. With an external store, every write is a two-system write, and you need an outbox or reconciliation job to repair the cases where one side commits and the other doesn't. Backups, point-in-time recovery, access control and monitoring all stay as they are. Hybrid search is plain SQL too; the post on why RAG fails and how to fix it shows full-text and vector results combined with reciprocal rank fusion in one query.
A dedicated engine starts to win when the index no longer fits comfortably in the memory of one Postgres node and its replicas, when query volume needs horizontal scaling that read replicas don't give you cheaply, or when vector search load starts to hurt the transactional workload on the same primary. It also wins when your retrieval design depends on features that are built into those engines and would be hand-rolled in Postgres: Qdrant, for example, stores several named vectors per point, supports sparse vectors and multi-vector late-interaction documents natively, and offers quantization as a collection setting. If you need those, the extra system is earning its keep.
Moving later isn't a rewrite either. If chunks have stable IDs and retrieval sits behind one function in your code, migrating means exporting vectors, loading them elsewhere and running both in parallel for a while, which is the same dual-write and shadow-read process the post on rolling out a new embedding model in pgvector describes. Starting in Postgres and measuring keeps that option open without paying for it up front.
