pg-jev - Jev judgements as SQL functions

A WHERE clause can express structure - dates, ranges, foreign keys - and it cannot express judgement. “The customer sounds frustrated” is not a column you can index. pg-jev is a PostgreSQL extension that puts that judgement inside the clause anyway: jev(reviews, 'the customer sounds frustrated') sends each row to TypeSafe’s Jev model and returns true or false, so a plain SELECT can filter, rank, classify or score rows in natural language. There is no index, no embedding column, no ANN structure - every row that reaches the function is judged by the model, and every row that reaches the function is sent to an external API. Both halves of that sentence are the product.

Installing it

pg-jev needs a self-hosted PostgreSQL 14, 15, 16 or 17 with plpython3u, and a superuser to load it. plpython3u is untrusted - it runs with the server’s operating-system privileges - which is why managed hosts do not offer it: Supabase, Neon, RDS and Aurora are all excluded unless you control the server. Check before installing:

SHOW server_version;
SELECT * FROM pg_available_extensions WHERE name = 'plpython3u';

On Debian and Ubuntu the language is the postgresql-plpython3-NN package. The extension itself comes from PGXN or source:

pip install pgxnclient          # once
pgxn install jev                # or: make install from a clone

CREATE EXTENSION jev CASCADE;   -- CASCADE pulls in plpython3u
SELECT jev_version();

CASCADE is required: the control file declares plpython3u, and PostgreSQL only auto-creates prerequisite extensions when you ask. The licence is the PostgreSQL License.

Configuration, and the API key

All settings are ordinary GUCs. The one to treat carefully is jev.api_key: the documentation shows SET jev.api_key = 'your-key' and ALTER ROLE analyst SET jev.api_key = 'your-key', and falls back to the TYPESAFE_API_KEY environment variable of the server process - not your psql client. Prefer the environment variable or a role setting. A key set with SET lives in the session and can surface in pg_stat_activity or statement logging if you have log_statement = all; a key in postgresql.conf is a secret sitting in a file that gets backed up and diffed. A key is only sent to *.typesafe.ai hosts; point jev.api_url at a local Jev-compatible server such as stuntd and no key is needed - and no row leaves your network.

The function surface

row is the table or view alias. kind is 'noul', 'choice' or 'score'.

-- filter
SELECT * FROM reviews
WHERE jev(reviews, 'the customer sounds frustrated', 0.8);

-- rank
SELECT subject, jev_prob(tickets, 'the customer is angry') AS p
FROM tickets ORDER BY p DESC LIMIT 20;

-- classify
SELECT jev_choice(tickets, 'which team should handle this?',
                  ARRAY['billing', 'technical', 'security', 'sales']) AS team,
       count(*)
FROM tickets GROUP BY 1;

-- score
SELECT name,
       jev_score(products, 'how luxurious is this product?',
                 ARRAY['budget', 'mid-range', 'premium', 'luxury']) AS luxury
FROM products ORDER BY luxury DESC;

Choosing a threshold from the distribution rather than guessing:

SELECT width_bucket(jev_prob(tickets, 'wants a refund'), 0, 1, 10) AS bucket,
       count(*)
FROM tickets GROUP BY 1 ORDER BY 1;

Ambiguous rows cluster near 0.5; the docs recommend a high bar (0.8-0.9) when you want only the clear cases. Because threshold changes hit the session cache, this exploration costs nothing after the first scan.

Cost, latency and the planner

The mechanism, per the documentation: a read-ahead streams the table in physical order (TID range scans; OFFSET pages for views), rows go out in batches of jev.batch_size with one Noul question per row, up to twice jev.concurrency requests stay in flight over persistent connections, and answers cache per backend session keyed by row content. The vendor’s numbers: roughly 175 input tokens per row at batch size 20 versus about 435 for a row alone (a shared ~270-token request overhead is amortised); cost is input tokens at $0.042 per million (jev-1.13 list price, output free); a 2,000-row table took about 3.5 seconds and $0.012 from Europe, 50 ms from the cache on re-run. jev_stats() reports the running total as estimated_cost_usd. These are the vendor’s own measurements, not independent benchmarks.

What that means in practice - the analysis part, not the docs: jev() is an opaque function to the planner. No index can serve it, and PostgreSQL’s selectivity estimate for it is a guess. Every row the executor reaches is an API call, so the cost of a query is governed by rows scanned before the predicate, not rows returned. The saving graces are documented: cheaper predicates evaluated first skip rows before jev() sees them, and LIMIT stops the read-ahead once the in-flight window is enough. Rows arriving through a subquery or CTE (anonymous record) cannot be read ahead and are judged one request at a time - put jev() on a base table or a view. On shared deployments, jev.max_rows_per_statement and jev.max_chars_per_statement are the documented spend guards; both default to off. The docs do not document planner cost constants for the functions, so EXPLAIN will not warn you what a query will spend - jev_stats() is the meter you check after the fact.

-- shrink the set in SQL first; the model only sees survivors
SELECT * FROM tickets
WHERE created_at >= CURRENT_DATE - 7
  AND status = 'open'
  AND jev(tickets, 'the customer is angry');

SET jev.max_rows_per_statement = 2000;   -- abort rather than pay for a full scan

What can go wrong

• Data leaves the database. Every judged row goes to TypeSafe. If the data may not be shared, that ends the evaluation - or jev.api_url goes to a local server. This is the caveats page’s first warning, and it deserves to be yours.
• Full scans by design. A bare WHERE jev(t, ...) on a large table is a metered sequential scan. The guard GUCs abort the statement rather than cap it - an aborted statement is also a failure mode to plan for.
• A pool with many backends warms many caches. The cache lives in PL/Python’s per-session GD; jev_cache_clear() clears only the session that calls it.
• Results can move under you. jev.model defaults to the jev-latest alias; when the alias advances, the same predicate can return different rows. Pin a version once thresholds are tuned.
• Determinism inside a session, not across sessions. The session cache makes a repeated query stable within a session; nothing guarantees the same answer tomorrow, from another connection, or after a model update. Do not materialise jev() answers into data you cannot recompute.
• CREATE EXTENSION fails on managed hosts - required extension "plpython3u" is not installed, or no superuser to create it. That is where the evaluation ends on those platforms.
• Batch accuracy has a ceiling. The docs’ own ground-truth check: batches of 1-20 scored 100% correct, 40 dropped to 92-98%, 80 to 77-94%. jev.batch_size defaulting to 20 is an accuracy choice, not just throughput.

What this is not for

• Similarity search. “Rows like this one” is an embedding problem - use pgvector. pg-jev judges against a written condition; it does not measure distance between rows.
• Anything a deterministic rule can say. If the condition decomposes to columns and operators, write SQL. A model call for age > 40 is cost without benefit - and it is also wrong, because the model can disagree with arithmetic.
• Latency-critical paths and private data. Per-row API round trips and rows leaving the cluster rule it out, whatever the cache does.
• Facts you must defend. The probability is a judgement calibrated in aggregate, not evidence about a specific row. The Jev page covers what calibration does and does not promise.