Semantic Filtering in Postgres Without Vector Indexes: pg-jev Hands-On

Traditional SQL WHERE clauses handle only exact conditions like age > 40, but cannot express semantic predicates such as "is this email threatening to unsubscribe." The usual workaround moves rows to the application layer, calls a model, parses output, and writes results back. pg-jev compresses this flow into Postgres.

It is an open-source extension that wraps TypeSafe's Jev package as SQL functions. Jev does not generate text; it returns calibrated probabilities over a given set of options. No vector columns or embedding indexes are required.

Four Core Functions

-- Used as a WHERE condition
SELECT * FROM people WHERE jev(people, 'the name is European');

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

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

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

jev()

is a regular boolean function that can be combined freely with AND age > 40, joins, GROUP BY, LIMIT, and ORDER BY.

Mechanism

Rows are batched in groups of 20, sent to Jev along with the question, and the returned results participate directly in SQL filtering and sorting. Answers are cached in the current session, so repeated runs, threshold changes, and probability-based ordering are nearly free.

Performance Benchmarks

On a 2,000-row table, the first query takes about 3.5 seconds (100 requests, ~296k input tokens, ~$0.012). The second run takes ~50 ms. Changing the condition within the same session with LIMIT 3 takes ~0.6 seconds.

Important Caveats

Full-Scan Design

The executor sends every row it examines to the API. Cheap predicates run first, and LIMIT can stop early, but this is not a replacement for indexes. The setting jev.max_rows_per_statement can cap the row count to prevent accidental full scans.

Label-Set Drift

Adding a valid option invalidates all previous thresholds. These probabilities are conditional — adding a label changes the probabilities of all other labels even if the model and prompt remain unchanged. The truly deployable unit is the combination of model, prompt, tokenizer, label set, and threshold, all versioned together.

Cross-Backend Non-Portability

Probabilities are not directly comparable across different models or quantizations. The same question can yield very different scores on two backends. The author (Avi) is working on a calibration layer.

Installation Restrictions

Requires PostgreSQL 14–17, plpython3u, and superuser privileges. Managed services like Supabase, Neon, and RDS do not provide plpython3u, so pg-jev cannot run there. Docker is the relatively straightforward path. By default data is sent to the TypeSafe API; if data cannot leave the network, jev.api_url can point to a local compatible service.

GitHub repository:

https://github.com/realZachi/pg-jev