DEV Community

Sunnatulla
Sunnatulla

Posted on Originally published at sunnatulla.hashnode.dev Fully Autonomous

Your RAG app forgets small customers: I measured filtered pgvector search in 12 framework stores

Most multi-tenant RAG apps I see store every customer's chunks in one table with one HNSW index, and search like this:

SELECT * FROM documents
WHERE metadata->>'user_id' = $1
ORDER BY embedding <=> $2
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

It looks correct, and for your biggest customer it mostly is. For a customer who uploaded one PDF, it often returns nothing.

I wanted numbers instead of a hunch, so I built a benchmark on real PostgreSQL 17.12 with pgvector 0.8.7, then read the pgvector store code of 12 open-source AI frameworks and replayed each one's actual query against the same table. This post is what came out.

The setup

One table, 200,000 rows, 128-dim vectors, about 2,100 tenants. Most tenants are background noise with Zipf-distributed sizes (a few big ones, a long tail of small ones). On top of that I added probe tenants that own exactly 1, 10, 100, 1,000 and 10,000 rows, and ran 60 queries per size. The index is pgvector's default HNSW (m=16, ef_construction=64), k = 10.

Ground truth is exact cosine search over the tenant's own rows, computed in numpy and checked against a Postgres sequential scan every run. HNSW builds are random, so every number below is the mean of 3 separate index builds. Unfiltered recall@10 on this table is 0.91 to 0.92, so the index itself is fine.

The vectors are synthetic (a 32-dim latent space projected to 128 dims, with shared topics and a per-tenant offset). I'll come back to that in the limits section.

The headline numbers

With the tenant inside JSONB metadata, the shape most frameworks use, here is recall@10 by tenant size:

tenant rows recall@10 queries that returned 0 rows p50
1 0.10 90% 1.2 ms
10 0.08 67% 1.0 ms
100 0.54 18% 0.9 ms
1,000 0.78 6% 1.0 ms
10,000 0.88 2% 1.1 ms

Nine out of ten searches for a one-document customer came back empty. They were fast and empty, which is why nobody notices: there is no error, the chatbot just says it doesn't know.

The cause is how HNSW and WHERE interact. The index walks the graph and hands back the ef_search nearest rows (40 by default) across the whole table. Postgres applies the filter afterwards. If your tenant owns 10 rows out of 200,000, the odds that any of them are among the 40 nearest rows globally are small. Postgres can't fall back to something smarter here, because metadata->>'user_id' has no statistics, so the planner assumes a generic selectivity and picks the HNSW index for every tenant, tiny or huge.

The good news is that results never leaked across tenants. Every strategy I tested only ever returned the tenant's own rows. The failure is always missing rows.

What the frameworks actually send

I read the current source of each store and copied its search query, then replayed it on the same table with the indexes the store creates. Here is what came out for 1-, 10- and 100-row tenants:

store filter it sends recall 1 / 10 / 100
langchain-postgres PGVector (v1) jsonb_path_match(cmetadata, ...) 0.10 / 0.08 / 0.54
langchain-postgres PGVectorStore (v2) langchain_metadata->>'user_id' = $1 0.10 / 0.08 / 0.54
llama-index PGVectorStore metadata_->>'user_id' = 'u1' 0.10 / 0.08 / 0.54
llama-index with indexed_metadata_keys same, plus a B-tree on the key 1.00 / 1.00 / 1.00
haystack PgvectorDocumentStore (HNSW mode) meta->>'user_id' = %s 0.10 / 0.08 / 0.54
mem0 payload->>%s = %s 0.10 / 0.08 / 0.54
Supabase vecs metadata -> 'user_id' = '"u1"'::jsonb 0.10 / 0.08 / 0.54
Supabase docs match_documents() metadata @> filter in plpgsql 0.19 / 0.08 / 0.54
langchainjs / n8n PGVector node metadata->>'user_id' = $1 0.10 / 0.08 / 0.54
Spring AI PgVectorStore metadata::jsonb @@ '$."user_id" == "u1"' 0.10 / 0.08 / 0.54
Semantic Kernel (Python) tenant_id = %s (a real column) 0.10 / 0.08 / 0.54

The identical rows aren't a copy-paste mistake on my side. The plans are identical: HNSW with ef_search = 40 returns the same 40 candidates however the filter is spelled, and the filter throws most of them away.

A replay is only as good as the copy, so for one store I also ran the library itself: langchain-postgres' own asimilarity_search_by_vector with a user_id filter, on the same rows. It got 0.05 to 0.12 for 1-row tenants over two index builds, and 88 to 95% of those searches came back empty, the same as the replay.

Three more stores didn't go through HNSW at all. Letta stores 4096-dim embeddings, above pgvector's 2,000-dim index limit, so search is exact (its tag filter runs in Python after the LIMIT, which is a different way to come back short). Semantic Kernel .NET defaults to a flat index, which means no vector index. And the Vercel AI SDK RAG guide sorts by 1 - (embedding <=> q) DESC, which EXPLAIN shows never uses the HNSW index the guide tells you to create. That one is correct, just slow.

None of the 12 sets hnsw.iterative_scan on its main branch. honcho has an open PR that turns it on.

The prepared statement trap

Semantic Kernel is the interesting row. It filters a real tenant_id column, which has statistics, so Postgres should see that a tenant is small and do a sequential scan plus exact sort. With plain queries it does exactly that: recall 1.00.

Then the driver starts preparing statements. psycopg 3 prepares a query after 5 executions, the PostgreSQL JDBC driver does the same by default, and asyncpg prepares everything. A prepared statement gets 5 custom plans, then Postgres switches to one generic plan if it looks cheaper. Here it does, and the generic plan uses HNSW for every tenant. Recall drops to the same 0.10 / 0.08.

I found this by accident. My first run used psycopg, and a few queries in, small tenants suddenly started coming back empty. It took me a while to realise the data hadn't changed, the plan had. The same thing happens inside plpgsql functions like Supabase's match_documents(), because plpgsql caches its plans the same way. That 0.19 is only the first few calls.

What fixes it

All numbers under the generic plan, since that's where most apps end up:

fix 1 row 10 rows 100 rows 10,000 rows p50, small tenant
nothing 0.10 0.08 0.54 0.88 1.2 ms
ef_search = 1000 0.30 0.33 0.92 1.00 12 ms
iterative_scan = relaxed_order 0.52 0.59 1.00 0.93 50 ms
relaxed + max_scan_tuples = 100000 0.87 0.84 1.00 0.93 327 ms
B-tree on the tenant key 1.00 1.00 1.00 1.00 0.15 ms

Recall@10 by tenant size under a generic plan: HNSW defaults, iterative_scan relaxed_order, relaxed + max_scan_tuples 100k, and a B-tree on tenant_id

Iterative scans help, but for the smallest tenants the default max_scan_tuples of 20,000 stops the scan before it reaches their rows about half the time, and by then it has spent more time than a sequential scan would. Raising the limit gets you to 0.87 at a third of a second per query.

An index on the filter key is the fix that works for every size under the generic plan. CREATE INDEX ON documents (tenant_id), or for JSONB CREATE INDEX ON documents ((metadata->>'user_id')), or a GIN jsonb_path_ops index if you filter with @>. Postgres reads only the tenant's rows and sorts them exactly. Small tenants got 0.15 ms and a 10,000-row tenant 5.4 ms.

With custom plans the planner still sends the bigger tenants through HNSW, so the index alone leaves them where they were (10,000-row tenants stay at 0.88). Supabase's match_documents() with a GIN index shows both sides: 1.00 for 1- and 10-row tenants (up from 0.19 and 0.08), 0.98 at 100 rows, and 0.89 / 0.88 for the big ones. That is where iterative_scan earns its place, and why I'd turn on both.

There is one catch, and mem0 hits it. mem0 binds the JSON key as a parameter: payload->>%s. An expression index on payload->>'user_id' works while plans are custom, but once psycopg prepares the statement, the key is $1, the index no longer matches, and recall goes back to 0.10. Inline the key as a literal (after validating it) and the index matches every time. A closed PR on mem0, #7607, did exactly that, and with it every tenant size got 1.00.

Hash partitioning by tenant alone didn't save small tenants on a generic plan (0.26 and 0.32); with relaxed_order added it reached 1.00 at 15 to 21 ms.

What I'd do in a real app

  1. Put the tenant in a real column, or at least index the exact JSON expression your store sends.
  2. Run EXPLAIN on your search query with plan_cache_mode = force_generic_plan. If the plan is an HNSW scan for a filter that matches a few rows, you have this bug.
  3. Turn on hnsw.iterative_scan too. It's cheap for big tenants and catches the sizes the planner sends through HNSW anyway (1,000-row tenants went from 0.78 to 0.98, 10,000-row ones from 0.88 to 0.93).
  4. Test with your smallest customer, not your biggest.

Limits

One machine (2 vCPU, 4 GB RAM), one table size, 128 dimensions, synthetic vectors. Real text embeddings cluster differently, and I'd expect the exact numbers to move. I'd expect the mechanism to hold, since it comes from the plan: 40 candidates out of 200,000 rows rarely contain a 10-row tenant. Latencies compare strategies on this box; they aren't capacity numbers.

The harness, every framework query with the file and line it came from, and the raw CSVs are here: https://github.com/atokim4/pgvector-tenant-recall

I wrote this with help from AI tools. The benchmark, the framework replays and every number above come from the code in the repo, which I ran myself.

Top comments (0)