How Lore restored indexed search under PostgreSQL RLS
A Lore search change split indexed candidate discovery from the caller’s authorized read, then checked that the results stayed identical.
Lore’s earlier database-wave work reduced the number of network waits in a memory request. A different bottleneck remained inside PostgreSQL: lexical search could scan visible chunks even though the search columns had GIN indexes. In a same-database benchmark with 76,000 chunks, the old lexical-only search had a 10.7-second median. The merged search change brought that median to 128 milliseconds. Those are local PostgreSQL 18 measurements from the PR, not production request latency.
Why an index was not enough
Lore uses PostgreSQL row-level security (RLS) to decide which memories a caller may read. Its lexical channels search text vectors, entity aliases, and CJK grams with operators including @@, @>, and LIKE. Under this RLS policy, PostgreSQL could not push the relevant non-leakproof predicates into the index scans. It instead applied the policy while scanning the workspace’s visible chunks. The PR documents the plans and comparison.
The architecture problem was therefore about where candidate discovery happens. Making the query shorter would not make an index condition usable under the same security context. Removing RLS from the whole search path would change the authorization boundary.
Split candidate discovery from result visibility
The change added a narrowly scoped lore.lexical_candidates function. It runs as a security definer, applies the equivalent workspace and actor visibility predicate, and returns chunk IDs and per-channel ranks, not memory content. Four workspace-leading GIN indexes support the lexical channels, including an expression index for CJK grams. Their definitions are in the next migration.
- 01Indexed candidate searchDefiner function applies the visibility predicate across lexical channels.
GIN → IDs + ranks - 02Authorized readbackEngine joins chunks and memories under the caller’s RLS policy.
IDs → visible rows - 03Rank fusionOnly rows returned by the authorized read enter reciprocal-rank fusion.
visible rows → results
The readback is a real boundary, not just a comment on the function. Before rank fusion, the engine joins candidate IDs back to memory_chunks and memories under the caller’s RLS context. A test substitutes an over-answering candidate function and checks that the extra row does not appear in the final result.
That readback does not make arbitrary definer code safe. The function also needs its own admission check because it executes with elevated privileges. The migration pins its search_path, qualifies its base tables, and grants execution only to the application role. The tests cover missing actor context, revoked access, function grants, and temporary-relation shadowing. These details keep the candidate stage within the intended authorization contract. The migration and security tests show the checks.
Measure speed and equivalence together
The PR’s same-database comparison ran lexical-only queries on PostgreSQL 18. At 76,000 chunks, median time fell from 10.7 seconds to 128 milliseconds; the slowest 5% went from 15.2 seconds to 336 milliseconds. A smaller 1,500-memory case went from 99 to 31 milliseconds median. Across 80 queries and 780 returned rows, the comparison reported identical memory IDs, scores to 12 decimal places, and evidence.
The result has limits. The worst measured large-dataset query still took 483 milliseconds, and this change did not address dense vector search or its separate RLS and HNSW questions. It shows that a faster candidate stage can preserve the existing result contract when the elevated function, caller-policy readback, and equivalence tests are designed together.