Hi, I'm Kristina

After fetching a large amount of data, the next question is how to efficiently use it. My case has a twist that I think will become common: the consumer is an AI agent that writes its own SQL. That forces two requirements that pull in opposite directions. Access has to be restricted (the agent must never see rows outside its allowed scope, some data is tenant-private) and flexible (you can't predict which questions it will ask; that's the whole point of letting it write queries).

The schema side of flexible is the easy part: one big append-only table of facts, fixed columns only for the universal identifiers, and a jsonb "identifiers" bag for everything source-specific. New data sources bring new keys without schema changes.

The trap is that restricted and flexible collide inside the query planner, and they only collide at scale. We enforced row visibility with a security-barrier view, the standard way to guarantee the filter runs before anything the untrusted query wrote. What I didn't know (I had never even heard the term before this week): Postgres will only push a filter below that barrier if it's built from "leakproof" operators (ones provably unable to reveal a value, not even inside an error message), and JSON operators aren't on that list. So every query touching the jsonb keys was silently forbidden from using its indexes and fell back to scanning the whole table. With a small table nobody notices. Tens of gigabytes later, everything times out. The confusing part is that the indexes exist and work perfectly; the planner just isn't allowed to use them.

The fix that finally felt right: stop making the planner arbitrate between untrusted SQL and the security rule. Take the one hot access pattern, "find rows where identifier X equals value Y", and put it in a small trusted SQL function whose body applies the visibility rule itself. Nothing untrusted ever runs inside it, so it's free to narrow via the index first and check visibility on the handful of matching rows, instead of checking visibility on millions of rows first. The agent calls it like a table and keeps composing its own SQL around it, so the flexibility survives.

And the part I want to remember, the GIN trick (GIN indexes being another concept I learned this week): instead of one expression index per JSON key (a migration every time a source introduces a new key), a single GIN index over the whole jsonb column. It works like the index at the back of a book: every key/value pair of every row, filed once. A key that doesn't exist yet today is automatically covered the moment the first row containing it lands. Restricted, flexible, and fast stop being a pick-two.

Takeaway: security filters and query flexibility interact in non-obvious ways, and the failure only shows up once the data is big. The answer wasn't a bigger machine or a longer timeout. It was moving the trust boundary so the security check lives in code you wrote, not in rules the planner enforces against code you didn't. I didn't design this table originally, so understanding it deep enough to see that took me a while, but this is exactly the kind of thing I couldn't have learned without the data getting big enough to break something.