The Postgres Predicate That Reads Your Table Twice

SnackOnAI Engineering | Senior AI Systems Researcher | Technical Deep Dive | October 4, 2026

SELECT * FROM tickets WHERE jev(tickets, 'the customer threatens to cancel') works, composes with every other thing SQL does, and costs about a third of a cent per thousand rows.

This issue shows you the machinery underneath that sentence, measured on a real cluster, and the three settings that decide whether your next exploratory query costs one row or six hundred and forty.

What this covers: the extension's architecture at v0.2.1, the exact request it puts on the wire, the read-ahead and its spend behavior, the session cache, and five experiments run locally against an instrumented stand-in for the API. What this excludes: Jev's own model quality, which we took apart last week, and the agent-skill install path.

On the numbers: every row count, request count and wall time below came from a PostgreSQL 16 cluster running pg-jev 0.2.1 against a local stand-in for the TypeSafe API with a fixed 190ms delay per request, matching the round trip the maintainer measured from Europe. Row and request counts are exact. Token counts and dollars come from the stand-in's bytes-over-four estimate at Jev's list price of $0.042 per million input tokens, so treat the ratios as solid and the absolute dollars as close. Answer quality was not under test: the stand-in answers deterministically.

pg-jev is a PostgreSQL extension, jev on PGXN, under the PostgreSQL License. CREATE EXTENSION jev CASCADE installs it, and it gives you a handful of plain SQL functions:

No index. No embeddings. No vector column. The judgment happens at the API, and the extension's whole job is to make one-row-at-a-time SQL semantics survive an HTTP round trip.

Caption: The hosting line is the real adoption ceiling. PL/Python is untrusted, so this is a self-managed-Postgres extension, and the managed hosts where most teams keep application data cannot run it.

Caption: Focus on the read-ahead box. A scalar function that sees one row at a time opens its own scan of the same table so it can batch rows the executor has not reached yet. Everything good and everything surprising about this extension comes from that.

Three decisions carry the design, ranked:

One, batching inside a per-row function. A naive implementation sends one request per row: 2,000 rows, 2,000 requests, one round trip each. pg-jev instead starts a second scan in physical order, batches twenty rows per request, keeps up to thirty-two in flight, and answers the executor's row from the batch result. Measured on 2,000 rows: 100 requests, 1.70 seconds of wall clock against 23.1 seconds of cumulative API time, a 13.6x overlap.

Two, the answer cache is keyed by row content, not row identity. sha1(to_json(row)) means two identical rows cost one judgment, an UPDATE invalidates exactly the rows it touched, and re-running the query, moving the threshold or sorting by probability is free. Measured: the second run of the same query took 46ms against 1,697ms cold, and ORDER BY jev_prob(...) DESC LIMIT 3 on the same condition took 47ms and sent nothing.

Three, everything lives in the backend session. The cache, the jobs, the thread pool and the keep-alive connections all sit in PL/Python's GD dictionary. That is why there is no shared cache, no background worker, and no persistence: a connection pooler with fifty sessions warms fifty caches. It is also the source of the strangest behavior in this issue.

Captured from a real run, three rows, jev.batch_size = 3:

Caption: The condition is written once into the shared state and every question is the same sentence with a different array index. That is the entire batching trick, and it is also the ceiling on batch size.

The questions differ only by rows[i], so the model has to resolve a position in an array. The maintainer's ground-truth tests against structured columns, 400 rows each, put accuracy at 100 percent up to twenty rows per request, 92 to 98 percent at forty, and 77 to 94 percent at eighty. Wider rows at twenty made no difference and naming rows instead of indexing them did not help, which says the limit is positional reference, not context length.

Here is what that costs. Same 400-row table, same question, only jev.batch_size changed:

Caption: Going from forty rows per request down to twenty cost one percent more tokens in this run, four percent in the maintainer's. Correctness above the cliff is nearly free, which is why the default moved from forty to twenty in 0.2.0.

Caption: A plain blocking wait inside PL/Python makes the backend deaf to cancellation. The no-op SPI query is what turns an un-killable query into one that respects statement_timeout, and the same call is what lets the worker threads run at all.

That second clause matters more than it looks. Postgres is not a Python process. The PL/Python interpreter holds the GIL whenever the backend is inside it, and the worker threads can only advance when the main thread is executing Python. Keep that in mind for Insight Two.

Caption: Measured at the API, not estimated: 1,958 rows and $0.005238 in 1.69 s, against 50 rows and $0.000133 in 0.28 s. The view also trims columns, which is the reason the README gives for using one. The spend is the bigger reason.

Input: a tickets table, 2,000 rows, every fiftieth row an angry one. The question: the customer threatens to cancel. Defaults throughout: batch_size 20, concurrency 16, threshold 0.5.

Step one, three rows, notices on, so you can see what the extension tells you:

Caption: One request, 197ms, three probabilities. The per-statement summary with tokens and estimated cost is the best habit in this codebase: the bill is visible in the psql output, not in a dashboard the next morning.

Step two, the full scan.

Caption: 23.1 seconds of API time compressed into 1.70 seconds of wall clock on sixteen pooled connections. 63 tokens per row, a third of a cent for the table.

Step three, the same query again, and a different use of the same answers.

Caption: 37x faster and free, because the cache is keyed by question plus row content, not by the statement. This is why you explore with jev_prob() first and pick a threshold afterwards.

Step four, the number that should change how you use it. A fresh session, a fresh condition, one result wanted:

Caption: One row of output. Six hundred and forty rows judged at the default. That is 32 percent of what the entire 2,000-row table costs, for a query that returned one id, and it is eight times the same query at concurrency 2.

It works because the read-ahead turns a per-row API into a streaming one without changing SQL semantics at all. jev() stays an ordinary boolean function, so AND, joins, GROUP BY, ORDER BY and LIMIT keep working, and the planner is free to run cheap predicates first. Version 0.2.0 is where this landed: the maintainer's measurements against the live API went from 8.5 s to 3.5 s on 2,000 rows and from 8.4 s to 0.6 s for a LIMIT 3, with 12 percent fewer input tokens.

It works a second time because the small decisions are unusually well made. Keep-alive connections cut a request from 880ms to 300ms from Europe, because a TLS handshake per request dominated. Dropping the generic criteria block from Noul questions removed 16 percent of all input tokens and changed no answers. Rows requested out of physical order, as in a backward index scan, get batched with the most recently skipped rows rather than judged alone.

What it trades away:

It is a full scan by design. Every row the executor asks about goes to a third-party API, and as the experiments show, so do a lot of rows it never asks about. There is no index and no way to prune by meaning.

Row contents leave your database. The README says it plainly. For regulated data this ends the conversation, and jev.max_chars_per_statement is a spend guard, not a privacy control.

The cache is per session. PgBouncer in transaction mode will scatter your cache across backends, and nothing survives a reconnect.

Superuser and an untrusted language. plpython3u functions run with the server's OS privileges. That is a real security review, not a checkbox, and it is why the managed hosts are out.

Anonymous records cannot be read ahead. Put jev() on a subquery or CTE alias and the extension has no relation to scan. Measured: the same 400 rows through a subquery alias took 451 requests and 93 seconds, against 23 requests and 0.73 seconds on the base table. Same work, 127x slower.

The idea is not a moat. Semantic predicates over tables are an active research area, and LOTUS and friends have been doing semantic filters over dataframes for a year.

The scheduler is the interesting part. Speculative read-ahead that keeps a fixed window in flight, content-addressed caching, skip-and-rebatch for out-of-order access, and interruptible waits are the kind of thing you only write after watching the naive version fail. It is also 566 lines, which means a determined team could port it.

The portability is a deliberate anti-moat. Since 0.2.1 the extension sends no Authorization header when jev.api_url points anywhere other than *.typesafe.ai, so a local Jev-compatible server such as stuntd runs the whole thing with no external calls and no per-row billing. The extension is bound to an API shape, not to a vendor.

Insight One: the in-flight window is the floor on spend, and concurrency is a spend knob, not a speed knob.

The README says a LIMIT stops the read-ahead after the in-flight window. True, and the window is bigger than most people will guess: 2 × jev.concurrency requests of jev.batch_size rows, which at defaults is 640 rows. The table above measures exactly that, and the project's own regression test asserts the same bound.

The second half is sharper. Cheap predicates do let the executor skip rows, but only rows the read-ahead has not already submitted. Same table, a predicate matching one row in 500:

Caption: Turning concurrency down made the same query 6.3x cheaper. Skipping only pays when surviving rows are farther apart than the window, so the knob that makes dense scans fast makes sparse scans expensive.

Two rules fall out. Explore with SET jev.concurrency = 2 and raise it when you commit to a full scan. And when a WHERE clause is selective, move the filter into a view and judge the view, as the measured 39x above.

Insight Two: the worker threads only run while the backend is inside PL/Python, so jev_stats() undercounts what you were billed for.

After a LIMIT 1 at defaults, 32 requests are in flight and the statement ends. The requests have already been sent. What happens next depends on something that has nothing to do with Postgres:

Caption: Identical work, identical time, different results. Python's GIL is held by the backend's main thread, and it only becomes available when something re-enters PL/Python. An idle session freezes its own background threads.

The practical consequences are worth spelling out. First, jev_stats() right after an abandoned LIMIT reported 80 rows and $0.000209 while the API had actually received 400 to 640 rows: the counters only advance as answers are collected, so the session's own cost estimate is a floor, not the bill. The spend guard jev.max_rows_per_statement is not affected, because it counts at submit time, which is the right design. Second, answers that arrive for a frozen session land in the cache anyway once you call any jev function again, so a follow-up query gets them. Third, this is a known shape of problem, not a bug in this extension: it is what you get when you run a thread pool inside a process that was never designed to host one.

A predicate that reads one row at a time quietly reads the whole table. In the run above, WHERE id % 50 = 0 AND jev(...) sent 1,958 of 2,000 rows to the API when only 50 rows passed the filter, while the same filter inside a view sent 50.

The mental model that fails here is "the model only sees the rows that reach it." What actually decides your bill is how far ahead the read-ahead has run and how far apart your surviving rows are. Those are both settings, not facts.

So the operating manual is three lines. Filter in a view, not in the WHERE clause next to jev(). Keep jev.concurrency low while exploring and raise it for committed scans. And read the per-statement NOTICE, because it is the only place the cost shows up while you are still looking at it.

Databases are very good at questions with crisp answers. Find every order over fifty dollars, every account created last Tuesday, every row where a column equals a value. They are hopeless at questions that need reading comprehension, like which support tickets sound like someone is about to quit.

The usual workaround is to export the rows, loop over them in Python, call an AI model on each one, and load the results back. That is a pipeline to build and maintain for what was really just a filter.

pg-jev puts the filter back in the database. You write a sentence where a condition would go, and the database hands each row to a model that answers yes or no with a probability. The result is an ordinary true or false, so it combines with everything else you already know how to write.

The clever part is hidden. Asking about rows one at a time would be unbearably slow, like phoning a colleague once per row. So the extension quietly runs ahead of the query, gathers rows into groups of twenty, and asks about a whole group in one call, keeping several calls in the air at once.

That trick is also the catch. Running ahead means asking about rows the query might never want, and you pay for those. The lesson generalizes beyond this one extension: when a database starts calling an external model, the thing to understand is not the model but the scheduling around it.

pg-jev answers a question most teams have quietly had for two years: can a semantic filter be a first-class part of a query instead of a pipeline. The answer is yes, in 566 lines, and the engineering around the API call is better than the engineering most teams do around their own.

What it cannot do is make the cost model intuitive. A boolean function that costs money, reads ahead speculatively, caches per session and depends on the GIL for progress is not something SQL has prepared anyone for. Treat the settings as the real interface: filter in a view, keep concurrency low while exploring, cap spend per statement, and read the notices. Then the sentence in the WHERE clause is worth it.

The habit from this issue: before you let a model into a WHERE clause, ask what the layer around it will fetch on your behalf. Then measure it at the API, not in the tool's own counters.

SnackOnAI runs this teardown weekly on the systems engineers actually deploy: new model classes, database extensions, agent frameworks and serving stacks, with the numbers reproduced rather than quoted. No announcements, no press release summaries. Subscribe at snackonai.com and join 10,000+ engineers reading it.

Forward this to whoever on your team is about to put an AI call inside a query that runs on a big table.

Every customer signal is captured in one shared context layer, always current and compounding. Agents and workflows build pipeline, chase every buying signal, and move deals forward, an always-on revenue engine running alongside your team.

With Attio, you’ll get:

Teams like Parallel, Turbopuffer, and Wordsmith build on Attio. Are you one of them?

Get started now