Introduction
If you are working with semantic search, vector embeddings and AI models, you probably already know the process with either building embeddings using direct AI integration like in AlloyDB or through application layer and pipelines. Embeddings and direct access to LLM give you options to do classifications, sentiment analysis and many other things.
But what if you want to run these evaluations directly on your operational data without the latency overhead of generative LLMs? In AlloyDB for PostgreSQL, thanks to model endpoint management, we aren’t restricted only to standard generative models. We can connect specialized, ultra-low-latency models designed specifically for high-speed logical constraints — like TypeSafe AI’s Jev model.
By integrating Jev, which is heavily optimized for returning structural evaluations (like booleans and scores) in milliseconds rather than generating conversational text, we can execute high-throughput semantic queries at database speeds using native functions like ai.if and ai.analyze_sentiment.
In this post, I will walk you through a complete setup: configuring the cluster and primary instance, adding secrets, creating the necessary PL/pgSQL translation layers, and leveraging Jev’s parallel batching capabilities to see how fast AI evaluations can be without preparing the embeddings in advance. Let’s dive in.
Adding Jev model to the cluster
If you already have an AlloyDB cluster you can use it right away or you can create one using Google documentation. Make sure you’ve enabled the AI integration following the guide and enabled the following database parameters:
- google_ml_integration.enable_model_support=on
- google_ml_integration.enable_ai_query_engine=on
- google_ml_integration.enable_ai_function_acceleration=on
- google_ml_integration.enable_cost_optimized_ai_functions=on
The combination of all those parameters is what makes AlloyDB AI function to be snappy and give the best performance.
Also check your primary instance network settings. We need an outbound public IP to be able to reach the Typesafe AI model endpoint. If your instance doesn’t have it you are going to get timeout trying to reach the model.
If you haven’t already got an API key from https://typesafe.ai/ then you need to register there and generate the key. It will be used to access the model from AlloyDB.
The next step is to add the API key to the Google Cloud secret manager. Here is a simple example how to do that:
gcloud secrets create typesafe-jev-api-key --replication-policy="automatic"
echo -n "<YOUR_TYPESAFE_API_KEY>" | gcloud secrets versions add typesafe-jev-api-key --data-file=-
Now it is time to register the model in your database. You can use any of your databases with a sample dataset to test it out. I was using a database with a sample e-commerce dataset with 30 thousand different products.
In the AlloyDB Studio choose your database and a user who has enough privileges to work with google_ml_integration extension and AI integration functionality. I was doing my tests using ai.if and ai.analyze_sentiment functions with the default model and with the TypeSafe AI’s Jev model.
TypeSafe AI’s System One API accepts structured JSON with distinct reasoning primitives (Noul for boolean constraints, Choice for multi-class classification). You can read about the primitives in the TypeSafe AI’s documentation, but here is a very basic explanation:
You have 3 different question types — Choice, Score or Noul. Depending on the type of the question you get different output. For example, for the type of question Noul you are getting 0 or 1 or, in other words, true or false.
AlloyDB’s standard ai.* functions need to be translated into this specific format. We use four transformation functions to bridge the gap. Here is the scalar input transform that handles Noul and Choice types for both ai.if and ai.analyze_sentiment:
CREATE OR REPLACE FUNCTION public.jev_model_input_transform(
model_id VARCHAR(100),
input_text TEXT,
generation_config JSON,
system_instruction TEXT
) RETURNS JSON
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
full_instruction TEXT;
enum_json JSON;
criteria_obj JSONB := '{}'::JSONB;
elem TEXT;
q_obj JSON;
BEGIN
-- Combine optional system instructions with the input prompt
IF system_instruction IS NOT NULL AND length(trim(system_instruction)) > 0 THEN
full_instruction := system_instruction || E'\n\n' || input_text;
ELSE
full_instruction := input_text;
END IF;
-- Inspect generation_config to determine whether caller expects a multi-class choice or boolean
enum_json := COALESCE(
generation_config->'generationConfig'->'responseSchema'->'enum',
generation_config->'responseSchema'->'enum'
);
IF enum_json IS NOT NULL AND json_typeof(enum_json) = 'array' THEN
-- Multi-class classification (ai.analyze_sentiment) -> Jev choice primitive
FOR elem IN SELECT json_array_elements_text(enum_json) LOOP
criteria_obj := criteria_obj || jsonb_build_object(elem, 'Category: ' || elem);
END LOOP;
q_obj := json_build_object(
'type', 'choice',
'instructions', full_instruction,
'criteria', criteria_obj::JSON
);
ELSE
-- Boolean filtering (ai.if) -> Jev noul primitive
q_obj := json_build_object(
'type', 'noul',
'instructions', full_instruction
);
END IF;
RETURN json_build_object(
'model', 'jev-latest',
'state', COALESCE(generation_config->>'state', 'Evaluate the question accurately based on the provided text.'),
'questions', json_build_object('q_1', q_obj)
);
END;
$$;
We also need a scalar output transform to unpack Jev’s JSON response back into a PostgreSQL TEXT result:
CREATE OR REPLACE FUNCTION public.jev_model_output_transform(
model_id VARCHAR(100),
response_json JSON
) RETURNS TEXT
LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
q_ans JSON := response_json->'answers'->'q_1';
ans_type TEXT := q_ans->>'type';
BEGIN
IF ans_type = 'choice' THEN
RETURN q_ans->>'choice';
ELSIF ans_type = 'noul' THEN
-- Convert probability score (0.00 to 1.00) to boolean text
RETURN CASE WHEN (q_ans->>'noul')::FLOAT >= 0.50 THEN 'true' ELSE 'false' END;
ELSE
RAISE EXCEPTION 'Unexpected Jev response payload: %', response_json::TEXT;
END IF;
END;
$$;
For a batched input and output you should use a modified set of functions which understand input and output as an array of values. All the functions are available in the codelab created by my colleague Paul Ramsey.
Now we bind the transformation functions, secret credential, and the external REST endpoint together:
CALL google_ml.create_model(
model_id => 'jev-model',
model_request_url => 'https://api.typesafe.ai/v1/systemone',
model_provider => 'custom',
model_type => 'llm',
model_qualified_name => 'jev-latest',
model_auth_type => 'secret_manager',
model_auth_id => 'jev_api_key',
model_in_transform_fn => 'jev_model_input_transform',
model_out_transform_fn => 'jev_model_output_transform',
model_batch_in_transform_fn => 'jev_model_batch_input_transform',
model_batch_out_transform_fn => 'jev_model_batch_output_transform'
);
You’ve noticed the model is using the Google secret manager as authentication provider with the auth key as the secret name.
Basic Semantic Testing
With jev-model registered, we can execute semantic operations directly inside standard PostgreSQL queries.
First, let’s test a simple boolean constraint using ai.if:
SELECT ai.if(
prompt => 'Is the product "North Face Waterproof Gore-Tex Hiking Jacket ($249)" suitable for rainy outdoor conditions?',
model_id => 'jev-model'
) AS is_suitable;
And getting expected result in just few millliseconds:
is_suitable
-------------
true
(1 row)
Now, let’s test multi-class classification using ai.analyze_sentiment on a customer review:
SELECT ai.analyze_sentiment(
input => 'The stitching on this jacket tore on the second day and the zipper jammed completely.',
model_id => 'jev-model'
) AS sentiment;
And the result in ms:
sentiment
-----------
negative
(1 row)
High-Throughput Array Batching on Tables
Usually, executing an HTTP call for thousands of rows row-by-row is painfully slow. However, by using PostgreSQL’s row_number() window function and array batching, we can evaluate dozens of items in a single HTTP round-trip without generative degradation.
Here is an example evaluating a 50-row batch of real catalog items to find cold-weather gear:
EXPLAIN (ANALYZE, BUFFERS)
WITH numbered AS (
SELECT
id, name, category, product_description,
((row_number() OVER (ORDER BY id) - 1) / 50) AS batch_id
FROM ecomm.products
ORDER BY id DESC
LIMIT 50
),
batched AS (
SELECT
batch_id,
array_agg(id ORDER BY id) AS ids,
array_agg(name ORDER BY id) AS names,
ai.if(
prompts => array_agg('Is "' || name || '" designed for cold weather winter outerwear?' ORDER BY id),
model_id => 'jev-model'
) AS decisions
FROM numbered
GROUP BY batch_id
),
unrolled AS (
SELECT
b.batch_id, u.id, u.name, u.decision AS cold_weather_gear
FROM batched b
CROSS JOIN LATERAL unnest(b.ids, b.names, b.decisions) AS u(id, name, decision)
)
SELECT id, name, cold_weather_gear
FROM unrolled
WHERE cold_weather_gear = TRUE
LIMIT 5;
Here are the results:
id | name | cold_weather_gear
-------+--------------------------------------------------------------+-------------------
29071 | Winter Striped Beanie with Pom | true
29077 | Knit Collegiate Rugby Stripe Winter Scarf & Beanie Hat Set | true
29083 | Junction Finds Men's Muscle Wool Blend Trapper Hat | true
29086 | Mens Soft Thermal Insulated Wrist Length Leather Gloves | true
29090 | Long Beanie-Red W16S24E | true
(5 rows)
And here is the execution plan for the query:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=6515.00..506515.19 rows=5 width=41) (actual time=369.386..369.391 rows=5.00 loops=1)
Buffers: shared hit=2479
-> Nested Loop (cost=6515.00..25006524.75 rows=250 width=41) (actual time=369.385..369.389 rows=5.00 loops=1)
Buffers: shared hit=2479
-> GroupAggregate (cost=6514.99..25006516.74 rows=50 width=104) (actual time=369.368..369.370 rows=1.00 loops=1)
Group Key: numbered.batch_id
Buffers: shared hit=2479
-> Sort (cost=6514.99..6515.11 rows=50 width=67) (actual time=17.838..17.840 rows=31.00 loops=1)
Sort Key: numbered.batch_id, numbered.id
Sort Method: quicksort Memory: 29kB
Buffers: shared hit=2440
-> Subquery Scan on numbered (cost=6512.95..6513.58 rows=50 width=67) (actual time=17.819..17.826 rows=50.00 loops=1)
Buffers: shared hit=2440
-> Limit (cost=6512.95..6513.08 rows=50 width=615) (actual time=17.816..17.819 rows=50.00 loops=1)
Buffers: shared hit=2440
-> Sort (cost=6512.95..6585.75 rows=29120 width=615) (actual time=17.815..17.817 rows=50.00 loops=1)
Sort Key: products.id DESC
Sort Method: top-N heapsort Memory: 37kB
Buffers: shared hit=2440
-> WindowAgg (cost=4890.43..5545.61 rows=29120 width=615) (actual time=8.715..14.386 rows=29120.00 loops=1)
Window: w1 AS (ORDER BY products.id ROWS UNBOUNDED PRECEDING)
Storage: Memory Maximum Storage: 17kB
Buffers: shared hit=2440
-> Sort (cost=4890.41..4963.21 rows=29120 width=59) (actual time=8.700..9.204 rows=29120.00 loops=1)
Sort Key: products.id
Sort Method: quicksort Memory: 3014kB
Buffers: shared hit=2440
-> Seq Scan on products (cost=0.00..2731.20 rows=29120 width=59) (actual time=0.014..3.791 rows=29120.00 loops=1)
Buffers: shared hit=2440
-> Function Scan on u (cost=0.01..0.11 rows=5 width=41) (actual time=0.013..0.015 rows=5.00 loops=1)
Filter: decision
Rows Removed by Filter: 15
The execution plan shows that PostgreSQL sorts and caps the dataset to 50 rows, aggregates their prompt strings into an array within GroupAggregate, and invokes ai.if once. This consolidates 50 model evaluations into a single network payload — taking ~351 ms — before unnesting and filtering the results, drastically reducing HTTP roundtrips and connection overhead compared to row-by-row execution.
The execution time was consistently around 180 ms. You can compare it with a default model and non-batched approach. My tests for the non-batched approach showed around 5600 ms using the same Jev model and 19500 ms with the default model. So the batching improves performance more than 30 times.
The same approach worked for the ai.analyze_sentiment function where I was taking user’s reviews and passing multiple reviews as arguments to the function capturing the output.
Summary
Comparing the traditional ETL-to-AI pipeline with AlloyDB’s native Model Endpoint Management shows massive improvements in architectural simplicity and performance. It worked really well with incredible speed.
Here are some thoughts after testing the TypeSafe AI’s Jev directly in AlloyDB:
Model Speed: Generative LLMs are relatively slow because they stream text token-by-token. Jev bypasses this entirely, returning structured logical primitives (like noul and score) in milliseconds, making it uniquely suited for synchronous database operations.
Flexibility: You are not locked into one provider. You can securely authenticate with Secret Manager and use custom SQL transforms to adapt standard ai.* functions to any external REST API format.
Database Performance: Using PostgreSQL window functions and array batching maps perfectly to Jev’s parallel API design, allowing you to process dozens of questions simultaneously inside a single HTTP round-trip. The database optimizer combines data before calling the AI model, minimizing overhead.
You can test the presented scenario with an e-commerce dataset using the published codelab and maybe expand your testing comparing the performance and doing sentiment scoring.
Happy testing!
Sub-second semantic filtering in PostgreSQL: Integrating the ultra-fast Jev model with AlloyDB was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/sub-second-semantic-filtering-in-postgresql-integrating-the-ultra-fast-jev-model-with-alloydb-1b226469b28e?source=rss—-e52cf94d98af—4
About the Author
