You said you hadn't benchmarked the HNSW case yet, so I ran your mem_select policy as-is on PG 17.12 + pgvector 0.8.7. 100k random 128-dim rows, 3 tenants, alice has 10 private rows plus 20 shared ones in a 33k-row tenant, queried as a NOBYPASSRLS role with your set_config(..., true) binding, top 10.
The part that surprised me: the plan doesn't change with the user, it changes with the driver. With a plain query the planner uses the (tenant_id, user_id) B-tree, RLS filters the tenant's 33k rows down to alice's 30 and the sort is exact. 50/50 queries correct, ~24 ms. Once the statement is prepared though (psycopg 3 does that by itself after 5 executions, asyncpg always), Postgres switches to a generic plan after 5 custom ones, and the generic plan for ORDER BY embedding <=> $1 LIMIT $2 is the HNSW index scan with the whole policy as a post-filter:
Index Scan using memories_embedding_idx on memories (actual rows=0 loops=1)
Filter: ((tenant_id = current_setting('app.tenant_id'::text, true)) AND ...
Rows Removed by Filter: 40
On a prepared connection, 45 of 50 queries came back empty. relaxed_order got recall@10 to 0.30 (45/50 still short) at ~60 ms, and only max_scan_tuples = 200000 got back to 1.00, at ~300 ms, slower than the exact plan.
The planner can't see who app.user_id is, only the tenant estimate, so a light user in a big tenant is the worst case. With 500 small tenants the prepared plan stayed on the B-tree and everything was fine.
It also lines up with your ADK point: an empty result here looks exactly like "this user has no memories yet". Since current_setting() hides the user from the planner anyway, a SET LOCAL plan_cache_mode = force_custom_plan in the same transaction as the identity binding fixed it in my run, or per-tenant partitions like you said.
Backend and AI-security engineer in Bengaluru. I build database-enforced isolation for AI agent memory: adk-memshield and adk-leak-arena. Software Engineer at Hertzwave.
This is a great catch, thank you for running it properly instead of guessing. The generic-plan switch is the part I missed: memshield’s search runs through a psycopg 3 pool, so it prepares after five executions, and anyone who adds an HNSW index walks straight into this. You’re also right that the README’s relaxed_order advice isn’t enough on its own.
The nice thing, if there is one, is that the policy still holds. Nothing leaks, it just goes quiet, which is exactly the “this user has no memories yet” failure I’d rather catch than ship.
I’ll bind SET LOCAL plan_cache_mode = force_custom_plan in the same transaction as the identity, add a light-user-in-a-big-tenant case to the benchmarks, and update the README. Mind if I credit you and cite your numbers there?
One thing I checked after your reply, since memshield runs on a pool: the generic plan sticks to the connection. On one prepared connection I ran 10 searches without the fix (10 rows for the first 5, then 0 from the 6th on), then 40 with SET LOCAL plan_cache_mode = force_custom_plan on that same connection. All 40 came back full, so it overrides a generic plan that's already cached. Once the transaction ended and the SET wasn't there, it went straight back to 0 rows.
So for the benchmark case I'd run the light user more than 5 times on the same connection. A single query per fresh connection always gets a custom plan and passes. And any search path that skips the SET LOCAL will go quiet again, so putting it next to the set_config call in one helper is safer than per query.
Backend and AI-security engineer in Bengaluru. I build database-enforced isolation for AI agent memory: adk-memshield and adk-leak-arena. Software Engineer at Hertzwave.
Thanks, and the same-connection detail is the part I would have missed. A test that opens a fresh connection per query always gets a custom plan, so it passes for the wrong reason. The regression case will pin one connection and run the light user well past 5 searches, with and without the fix, so it can actually fail.
Agreed on placement too. Every memshield query, including custom SQL, already goes through one rls_transaction helper, so plan_cache_mode gets bound in the same statement as the set_config calls. No search path can skip it.
It ships in 0.2.0, with your numbers and a credit in the README and changelog.
Backend and AI-security engineer in Bengaluru. I build database-enforced isolation for AI agent memory: adk-memshield and adk-leak-arena. Software Engineer at Hertzwave.
Shipped in adk-memshield 0.2.0. plan_cache_mode is now bound in the same statement as the identity, so no search path can skip it, and the regression test pins one connection, runs well past the threshold and has a negative control that must see the collapse. Your name is in the README and the changelog. While benchmarking it I hit a second empty case: at 100k rows the HNSW scan can starve a light user on the very first execution, under a custom plan, which pinning can't fix but iterative_scan does. Thanks again for running it properly.
We're a place where coders share, stay up-to-date and grow their careers.
You said you hadn't benchmarked the HNSW case yet, so I ran your
mem_selectpolicy as-is on PG 17.12 + pgvector 0.8.7. 100k random 128-dim rows, 3 tenants, alice has 10 private rows plus 20 shared ones in a 33k-row tenant, queried as a NOBYPASSRLS role with yourset_config(..., true)binding, top 10.The part that surprised me: the plan doesn't change with the user, it changes with the driver. With a plain query the planner uses the
(tenant_id, user_id)B-tree, RLS filters the tenant's 33k rows down to alice's 30 and the sort is exact. 50/50 queries correct, ~24 ms. Once the statement is prepared though (psycopg 3 does that by itself after 5 executions, asyncpg always), Postgres switches to a generic plan after 5 custom ones, and the generic plan forORDER BY embedding <=> $1 LIMIT $2is the HNSW index scan with the whole policy as a post-filter:On a prepared connection, 45 of 50 queries came back empty.
relaxed_ordergot recall@10 to 0.30 (45/50 still short) at ~60 ms, and onlymax_scan_tuples = 200000got back to 1.00, at ~300 ms, slower than the exact plan.The planner can't see who
app.user_idis, only the tenant estimate, so a light user in a big tenant is the worst case. With 500 small tenants the prepared plan stayed on the B-tree and everything was fine.It also lines up with your ADK point: an empty result here looks exactly like "this user has no memories yet". Since
current_setting()hides the user from the planner anyway, aSET LOCAL plan_cache_mode = force_custom_planin the same transaction as the identity binding fixed it in my run, or per-tenant partitions like you said.This is a great catch, thank you for running it properly instead of guessing. The generic-plan switch is the part I missed: memshield’s search runs through a psycopg 3 pool, so it prepares after five executions, and anyone who adds an HNSW index walks straight into this. You’re also right that the README’s relaxed_order advice isn’t enough on its own.
The nice thing, if there is one, is that the policy still holds. Nothing leaks, it just goes quiet, which is exactly the “this user has no memories yet” failure I’d rather catch than ship.
I’ll bind SET LOCAL plan_cache_mode = force_custom_plan in the same transaction as the identity, add a light-user-in-a-big-tenant case to the benchmarks, and update the README. Mind if I credit you and cite your numbers there?
Sure, go ahead and cite it.
One thing I checked after your reply, since memshield runs on a pool: the generic plan sticks to the connection. On one prepared connection I ran 10 searches without the fix (10 rows for the first 5, then 0 from the 6th on), then 40 with SET LOCAL plan_cache_mode = force_custom_plan on that same connection. All 40 came back full, so it overrides a generic plan that's already cached. Once the transaction ended and the SET wasn't there, it went straight back to 0 rows.
So for the benchmark case I'd run the light user more than 5 times on the same connection. A single query per fresh connection always gets a custom plan and passes. And any search path that skips the SET LOCAL will go quiet again, so putting it next to the set_config call in one helper is safer than per query.
Thanks, and the same-connection detail is the part I would have missed. A test that opens a fresh connection per query always gets a custom plan, so it passes for the wrong reason. The regression case will pin one connection and run the light user well past 5 searches, with and without the fix, so it can actually fail.
Agreed on placement too. Every memshield query, including custom SQL, already goes through one rls_transaction helper, so plan_cache_mode gets bound in the same statement as the set_config calls. No search path can skip it.
It ships in 0.2.0, with your numbers and a credit in the README and changelog.
Shipped in adk-memshield 0.2.0. plan_cache_mode is now bound in the same statement as the identity, so no search path can skip it, and the regression test pins one connection, runs well past the threshold and has a negative control that must see the collapse. Your name is in the README and the changelog. While benchmarking it I hit a second empty case: at 100k rows the HNSW scan can starve a light user on the very first execution, under a custom plan, which pinning can't fix but iterative_scan does. Thanks again for running it properly.