DEV Community

Discussion on: Shared Agent Memory Leaks. Enforce the Boundary in PostgreSQL

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

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
Enter fullscreen mode Exit fullscreen mode

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.

Collapse
 
aalishan001 profile image
Aalishan •

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?

Collapse
 
sunnatulla_dev profile image
Sunnatulla •

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.

Thread Thread
 
aalishan001 profile image
Aalishan •

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.

Thread Thread
 
aalishan001 profile image
Aalishan •

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.