DEV Community: Gleb Otochkin

Activating Your Data Layer for Production-Ready AI

Gleb Otochkin — Mon, 05 Oct 2026 19:39:51 +0000

When discussing applications and systems using generative AI and the new opportunities they present, one component of the ecosystem is irreplaceable - data. Specifically, the data that companies gather, hold, and use daily. This data serves as the backbone for applications, analytics, knowledge bases, and much more. We use databases to store and work with this data, and most, if not all, AI-driven initiatives and new applications are going to use that data layer.

But how can we start to use the data in our AI systems? Let me introduce you to some of the labs showing how to prepare and use the data with AI models in Google databases.

Semantic Search: Text Embeddings in Database

Our journey starts by preparing our data for semantic search and running first tests to augment the Gen AI model's response by grounding it with your semantic search results. The grounding data is the basis for RAG (Retrieval Augmented Generation). Then, you can improve the performance of your search by indexing your embeddings using the latest indexing techniques.

One of the options is the Google AlloyDB database, which has direct integration with AI models and supports the most demanding workloads. The following lab guides us through all the steps, starting from creating an AlloyDB cluster, loading sample data, and generating embeddings, to using those embeddings to generate an augmented response from the Gen AI model.

Go to the lab!

Lab: Getting started with Vector Embeddings with AlloyDB AI

Objective: Create an AlloyDB cluster, generate vector embeddings for sample data, and perform semantic search to ground a Gen AI model's response.

AI integration is not limited to AlloyDB. All Google Cloud databases have AI integration and are capable of generating and using embeddings for semantic search. For example, if you are using Cloud SQL, you can also generate and use embeddings for semantic search directly within your existing PostgreSQL or MySQL instances.

The next two labs are very similar to the previous one, but instead of Google AlloyDB for PostgreSQL, we are using Cloud SQL for PostgreSQL and Cloud SQL for MySQL to use semantic search as the grounding engine for the model's response. Some steps are of course different due to variations in SQL language and different database engines, but the main idea stays the same: use our data to ground the model response and improve output.

Go to the labs!

Lab: Getting started with Vector Embeddings in Cloud SQL for PostgreSQL

Objective: Create Cloud SQL for PostgreSQL instance with AI integration, generate vector embeddings for sample data, and perform semantic search to ground a Gen AI model's response.

Lab: Getting started with Vector Embeddings in Cloud SQL for MySQL

Objective: Create Cloud SQL for MySQL instance with AI integration, generate vector embeddings for sample data, and perform semantic search to ground a Gen AI model's response.

Semantic search using text data is one of the cornerstones and important features making responses much more reliable and useful, but Google Gen AI models can offer much more. Let's talk about multimodal search.

Multimodal Embeddings: Bring Images to the Search

In real life, of course, we use all our senses, including vision, to evaluate the world around us. The Google multimodal embedding models bring an additional layer of understanding, improving search by using embeddings not only for text but also for images.

In the following lab, we use a catalog of products placed in AlloyDB and supplemented by images in Google Cloud Storage. In the lab, we show how we can use both text descriptions and images for semantic search, supplementing and replacing each other, naturally incorporating search based on image input into our response.

Go to the lab!

Lab: Multimodal Embeddings in AlloyDB

Objective: Deploy an AlloyDB cluster, import data and use text and image semantic search using multimodal embeddings, try hybrid search approach.

Preparing the data and making first steps are important for a general understanding of RAG and tools available for the search, but Google has other cases when direct AI integration can help with your data analysis without any data preparations.

AlloyDB AI Functions and Reranking

Google AlloyDB database comes with additional AI integrations that help you use some AI capabilities without data preparation. For example, the AI.IF function can perform semantic search on the fly, evaluating sentiment or comparing data in columns with a natural language query, returning results filtered by the query condition. Also, you can apply a ranking function to the search output, improving the final result. You can try some of the new functionality using the following lab and let us know if it can help in your use case.

Go to the lab!

Lab: AlloyDB AI Operators and Reranking

Objective: Deploy AlloyDB cluster and enable AI query functions, use AI query functions to filter data and ranking function to improve the search results.

But what if somebody is not particularly savvy with SQL or not familiar with the data structure in your database? The QueryData for AlloyDB can help you with that.

Generate SQL using QueryData for AlloyDB

QueryData lets you interact with the data in your database using conversational language and build data agents. You provide database-specific information in the form of context sets to improve generator accuracy.

The QueryData context add a layer describing your data schema and providing templates, facets and value search options to make your generated SQL predictable and production ready. The following lab helps you to start with the new features and generate your first queries based on your data schema.

Go to the lab!

Lab: QueryData for AlloyDB using Gemini Data Analytics

Objective: Use QueryData for AlloyDB to generate accurate and predictable SQL statements from natural language input in agentic applications

From Tests to Production

Those labs are part of the From Data Foundations to Advanced RAG module of our Production-Ready AI with Google Cloud program. Check the other modules and see if they can help you to adopt the AI capabilities provided by our Google Cloud services and tools. The end game goal is a high quality application using the full potential of modern technologies.

And stay tuned on release notes for ALloyDB and Cloud SQL - the engineering team is busy working on new features and improvements. Happy testing.

Sub-second semantic filtering in PostgreSQL: Integrating the ultra-fast Jev model with AlloyDB

Gleb Otochkin — Tue, 29 Sep 2026 17:00:53 +0000

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!

Inline vs. Separate Tables for Vectors in Postgres: Measuring the Join Overhead

Gleb Otochkin — Fri, 25 Sep 2026 04:29:18 +0000

Testing semantic search, filtered queries, and multi-table joins in AlloyDB to measure the true cost of decoupling your vectors.

Introduction

If you are working with vector embeddings you probably already know the embedding models are evolving and you need to refresh your embeddings with a new version from time to time. In the last post we discussed the bloating problem in the tables, TOAST segments and indexes related to the embeddings refresh. As an alternative to storing embeddings in the same table with your data I proposed a different layout where the embeddings would be placed in a dedicated table. In this post I show what performance impact it might have in comparison with storing embeddings in the same table.

How I tested it

To test queries I needed sample data with real embeddings. The reasons were explained in one of my previous posts — it could have a visible impact on results when an ANN index is used. I prepared a dataset with 30k rows of sample products, built embeddings on product descriptions, and used them to fill a dedicated embedding table. For all my tests I was using an AlloyDB Omni database. It was coming with AI integration out of the box and that helped me to build the embeddings, vector indexes, and generate search embeddings by converting search phrases to the vector variables.

Here are the tables used in my tests:

-- ecomm.products table

demodb=# \d ecomm.products
                              Table "ecomm.products"
         Column | Type | Collation | Nullable | Default
------------------------+------------------------+-----------+----------+---------
 id | bigint | | not null |
 cost | numeric | | |
 category | character varying(255) | | |
 name | character varying(255) | | |
 brand | character varying(255) | | |
 retail_price | numeric | | |
 department | character varying(255) | | |
 sku | character varying(255) | | |
 distribution_center_id | bigint | | |
 product_description | text | | |
 product_image_uri | text | | |
 embedding | vector(768) | | |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
    "fk_products_distribution_center_23" btree (distribution_center_id)
    "idx_products_brand" btree (brand)
    "idx_products_category" btree (category)
    "idx_products_retail_price" btree (retail_price)
    "idx_products_sku" btree (sku)
    "idx_products_vector" hnsw (embedding vector_cosine_ops)
Foreign-key constraints:
    "fk_products_distribution_center" FOREIGN KEY (distribution_center_id) REFERENCES ecomm.distribution_centers(id)
Referenced by:
    TABLE "ecomm.inventory_items" CONSTRAINT "fk_inventory_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
    TABLE "ecomm.order_items" CONSTRAINT "fk_order_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
    TABLE "ecomm.product_reviews" CONSTRAINT "fk_reviews_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE

-- ecomm.product_embeddings table
demodb=# \d ecomm.product_embeddings
             Table "ecomm.product_embeddings"
   Column | Type | Collation | Nullable | Default
------------+-------------+-----------+----------+---------
 product_id | bigint | | not null |
 embedding | vector(768) | | not null |
Indexes:
    "product_embeddings_pkey" PRIMARY KEY, btree (product_id)
    "idx_product_embeddings_vector" hnsw (embedding vector_cosine_ops)

-- ecomm.inventory_items table 
demodb=# \d ecomm.inventory_items
                                 Table "ecomm.inventory_items"
             Column | Type | Collation | Nullable | Default
--------------------------------+-----------------------------+-----------+----------+---------
 id | bigint | | not null |
 product_id | bigint | | |
 created_at | timestamp without time zone | | |
 sold_at | timestamp without time zone | | |
 cost | numeric | | |
 product_category | character varying(255) | | |
 product_name | character varying(255) | | |
 product_brand | character varying(255) | | |
 product_retail_price | numeric | | |
 product_department | character varying(255) | | |
 product_sku | character varying(255) | | |
 product_distribution_center_id | bigint | | |
Indexes:
    "inventory_items_pkey" PRIMARY KEY, btree (id)
    "fk_inventory_items_distribution_center_8" btree (product_distribution_center_id)
    "fk_inventory_items_product_7" btree (product_id)
Foreign-key constraints:
    "fk_inventory_items_distribution_center" FOREIGN KEY (product_distribution_center_id) REFERENCES ecomm.distribution_centers(id)
    "fk_inventory_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
Referenced by:
    TABLE "ecomm.order_items" CONSTRAINT "fk_order_items_inventory_item" FOREIGN KEY (inventory_item_id) REFERENCES ecomm.inventory_items(id)

-- ecomm.product_reviews table
demodb=# \d ecomm.product_reviews
                                Table "ecomm.product_reviews"
   Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+------------------------------
 id | bigint | | not null | generated always as identity
 user_id | bigint | | not null |
 product_id | bigint | | not null |
 rating | integer | | not null |
 review_text | text | | not null |
 created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Indexes:
    "product_reviews_pkey" PRIMARY KEY, btree (id)
    "idx_product_reviews_created_at" btree (created_at DESC)
    "idx_product_reviews_product_id" btree (product_id)
    "idx_product_reviews_user_id" btree (user_id)
Check constraints:
    "product_reviews_rating_check" CHECK (rating >= 1 AND rating <= 5)
Foreign-key constraints:
    "fk_reviews_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE
    "fk_reviews_user" FOREIGN KEY (user_id) REFERENCES ecomm.users(id) ON DELETE CASCADE

The ecomm.products and ecomm.product_embeddings are connected by the primary key and both tables have HNSW index built on top of the embedding vectors.

Test products semantic search

I started the tests from a simple case with a cosine semantic search using only the products table and then comparing it with the join of the products and product_embeddings table. For the search I converted a phrase ‘lightweight waterproof high quality jacket’ to a vector embedding and passed it as test_vec variable. That removed the unpredictable model response time and allowed me to compare the query execution times more precisely.

-- Set the test_vec variable in psql
SELECT (google_ml.embedding(
    model_id => 'text-embedding-005',
    content => 'lightweight waterproof high quality jacket'
)::public.vector(768))::text AS test_vec \gset

In the same psql session I ran the search with EXPLAIN ANALYZE to see the execution plan and timings. Every test was repeated multiple times.

-- Embeddings in ecomm.products
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.id,
    p.name,
    p.brand,
    p.retail_price
FROM ecomm.products p
ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;

Here is the execution plan for “inline” embeddings vector search:

 Limit (cost=1213.90..1240.56 rows=10 width=86) (actual time=1.112..1.200 rows=10.00 loops=1)
   Buffers: shared hit=792
   -> Index Scan using idx_products_vector on products p (cost=1213.90..78826.40 rows=29120 width=86) (actual time=1.110..1.197 rows=10.00 loops=1)
         Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
         Index Searches: 1
         Buffers: shared hit=792
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.107 ms
 Execution Time: 1.224 ms

From the plan it was visible the query was using the HNSW index to search through the embeddings and order them by similarity. The response time in total to return the results was around 2.1 ms. The response time was bigger than the pure execution time from the execution plan because it added overhead like planning, network time and client software time to show the results.

Then I put embeddings to the ecomm.product_embeddings table and did the same search:

-- Embeddings in ecomm.product_embeddings
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.id,
    p.name,
    p.brand,
    p.retail_price
FROM ecomm.product_embeddings pe
JOIN ecomm.products p ON p.id = pe.product_id
ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;

Here is the execution plan with join of products and product_embeddings tables using primary keys:

 Limit (cost=219.82..250.39 rows=10 width=86) (actual time=1.188..1.351 rows=10.00 loops=1)
   Buffers: shared hit=852
   -> Nested Loop (cost=219.82..89223.27 rows=29120 width=86) (actual time=1.187..1.348 rows=10.00 loops=1)
         Buffers: shared hit=852
         -> Index Scan using idx_product_embeddings_vector on product_embeddings pe (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.126..1.141 rows=10.00 loops=1)
               Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
               Index Searches: 1
               Buffers: shared hit=752
         -> Index Scan using products_pkey on products p (cost=0.29..1.01 rows=1 width=78) (actual time=0.007..0.007 rows=1.00 loops=10)
               Index Cond: (id = pe.product_id)
               Index Searches: 10
               Buffers: shared hit=50
 Planning:
   Buffers: shared hit=21
 Planning Time: 0.274 ms
 Execution Time: 1.381 ms

I was getting top 10 vectors using the HNSW index on ecomm.product_embeddings and joining that with the ecomm.products table using the primary key. The execution time was consistently around 2.2 ms. Yes, the execution plan is a bit more complicated but the execution itself is taking only 0.1 ms longer. When I increased limit from the top 10 to top 1000 it increased the response time only by around 0.3 ms. PostgreSQL joins with primary keys are fast.

Filtered semantic search

In real life we rarely see queries using only one search condition. In most cases it involves additional parameters. I tested it adding filter on category and retail_price columns:

-- With two filters and embeddings in ecomm.products
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.id,
    p.name,
    p.brand,
    p.retail_price,
    p.category
FROM ecomm.products p
WHERE p.category = 'Outerwear & Coats'
  AND p.retail_price BETWEEN 30 AND 150
ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;

The execution plan had changed adding the filter on results:

 Limit (cost=1213.90..1760.50 rows=10 width=97) (actual time=6.353..6.522 rows=10.00 loops=1)
   Buffers: shared hit=817
   -> Index Scan using idx_products_vector on products p (cost=1213.90..78829.95 rows=1420 width=97) (actual time=6.342..6.509 rows=10.00 loops=1)
         Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
         Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))
         Rows Removed by Filter: 7
         Index Searches: 1
         Buffers: shared hit=799
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.157 ms
 Execution Time: 1.308 ms

The response time was consistently around 2.2 ms — almost the same as without the filter.

Then I executed it with embeddings in a separate table:

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.id,
    p.name,
    p.brand,
    p.retail_price,
    p.category
FROM ecomm.product_embeddings pe
JOIN ecomm.products p ON p.id = pe.product_id
WHERE p.category = 'Outerwear & Coats'
  AND p.retail_price BETWEEN 30 AND 150
ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;

I got one extra step with the filter in the execution plan:

 Limit (cost=219.82..1346.89 rows=10 width=97) (actual time=1.167..1.370 rows=10.00 loops=1)
   Buffers: shared hit=894
   -> Nested Loop (cost=219.82..89370.85 rows=791 width=97) (actual time=1.166..1.367 rows=10.00 loops=1)
         Buffers: shared hit=894
         -> Index Scan using idx_product_embeddings_vector on product_embeddings pe (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.110..1.153 rows=17.00 loops=1)
               Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
               Index Searches: 1
               Buffers: shared hit=759
         -> Index Scan using products_pkey on products p (cost=0.29..1.02 rows=1 width=89) (actual time=0.006..0.006 rows=0.59 loops=17)
               Index Cond: (id = pe.product_id)
               Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))
               Rows Removed by Filter: 0
               Index Searches: 17
               Buffers: shared hit=85
 Planning:
   Buffers: shared hit=21
 Planning Time: 0.347 ms
 Execution Time: 1.406 ms

The response time for the query was all the time between 2.3 and 2.4 ms. Again it was slower than the query with embeddings in the same table but the difference was not significant.

Multi-tables join

If you have a data schema where different parts and attributes of your business information are stored in different tables you use joins of multiple tables to get what you need. For tests I used a join with ecomm.product_reviews with aggregations for product ratings.

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    top.id,
    top.name,
    top.brand,
    top.retail_price,
    top.category,
    top.dist,
    COUNT(pr.id) AS review_count,
    COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating
FROM (
    SELECT 
        p.id,
        p.name,
        p.brand,
        p.retail_price,
        p.category,
        (p.embedding <=> :'test_vec'::public.vector(768)) AS dist
    FROM ecomm.products p
    WHERE p.category = 'Outerwear & Coats'
      AND p.retail_price BETWEEN 30 AND 150
    ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
    LIMIT 10
) top
LEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id
GROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist
ORDER BY top.dist;

The query was consistently returning results in 2.6–2.7 ms. Then I modified the query to use embeddings stored in the ecomm.product_embeddings table:

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    top.id,
    top.name,
    top.brand,
    top.retail_price,
    top.category,
    top.dist,
    COUNT(pr.id) AS review_count,
    COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating
FROM (
    SELECT 
        p.id,
        p.name,
        p.brand,
        p.retail_price,
        p.category,
        (pe.embedding <=> :'test_vec'::public.vector(768)) AS dist
    FROM ecomm.product_embeddings pe
    JOIN ecomm.products p ON p.id = pe.product_id
    WHERE p.category = 'Outerwear & Coats'
      AND p.retail_price BETWEEN 30 AND 150
    ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
    LIMIT 10
) top
LEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id
GROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist
ORDER BY top.dist;

The difference was again about 0.2 ms. The second query was returning results in 2.8–2.9 ms. I am skipping the individual execution plans for this case — they are longer and more difficult to read in a short blog post like this one, but here is a comparison diagram:

For the second query I had an extra join for our product_embeddings and products tables (which was expected), but apart from that it showed the similar execution path.

Summary

Here is summary table with the test results:

+--------------------------+-----------------------+-----------------------+---------------------+
| Test Scenario | Inline Embeddings | Dedicated Table | Overhead / Delta |
+--------------------------+-----------------------+-----------------------+---------------------+
| 1. Pure Semantic Search | Execution: ~1.22 ms | Execution: ~1.38 ms | +0.16 ms (Exec) |
| (LIMIT 10) | Total: ~2.10 ms | Total: ~2.20 ms | +0.10 ms (Total) |
+--------------------------+-----------------------+-----------------------+---------------------+
| 2. Filtered Search | Execution: ~1.31 ms | Execution: ~1.41 ms | +0.10 ms (Exec) |
| (Category + Price) | Total: ~2.20 ms | Total: 2.3 - 2.4 ms | +0.10 - 0.20 ms |
+--------------------------+-----------------------+-----------------------+---------------------+
| 3. Multi-Table Join | | | |
| (Reviews Aggregation) | Total: 2.6 - 2.7 ms | Total: 2.8 - 2.9 ms | +0.20 ms (Total) |
+--------------------------+-----------------------+-----------------------+---------------------+

Comparing queries performance for embeddings stored in the table along with the source data and in the decoupled dedicated table showed relatively minor difference in performance. In most of the cases response time didn’t exceed 0.2 ms. Of course it might be different depending on table structures, data and individual queries.

I would recommend testing and considering the layout with dedicated embedding tables. It might help you in the future models maintenance and model evaluations when you just add another table with your future model and run your queries using the newly generated embeddings. With a good schema design the overhead potentially can be minimal. In my experience other factors like the model response time introduces much more variations to the response time than a join with an embedding table based on primary keys.

Embedding versions management, TOAST and bloating in PostgreSQL

Gleb Otochkin — Sat, 12 Sep 2026 00:05:49 +0000

If you are working with vector embeddings you probably already know the AI models including embedding models have their own lifecycle. When a new model is released by a service provider the old version will be eventually deprecated and removed from the service. You might have better control with models deployed on your own infrastructure like EmbeddingGemma in this codelab but eventually you might want the new model just because it works better.

What is the impact of refreshing all the vector embedding in your AlloyDB or PostgreSQL database? Let’s dive in.

Prepare the tests data

Let us prepare a test environment. In my tests I am going to use AlloyDB Omni since it is fully PostgreSQL compatible and in this case behaves exactly like any PostgreSQL database.

We create a test table with the vector data type, add some indexes, and generate 20k rows.

-- Install extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Create a function building random vectors
CREATE OR REPLACE FUNCTION random_vector(dim integer)
 RETURNS vector
LANGUAGE sql VOLATILE AS $$
SELECT array_agg(random()::real)::vector FROM generate_series(1, dim);
$$;

-- Create a demo table
DROP TABLE IF EXISTS demo_documents CASCADE;
CREATE TABLE demo_documents (
    id BIGSERIAL PRIMARY KEY,
    category_id INT NOT NULL,
    status VARCHAR(32) NOT NULL,
    title VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- Large text column (> 2.5 KB) to demonstrate TOAST interaction with uncompressed/compressed text
    content TEXT NOT NULL,
    -- 768-dim float vector (~3.08 KB) -> pushed to TOAST
    embedding vector(768)
);

-- Pause autovacuum temporarily
ALTER TABLE demo_documents SET (
    autovacuum_enabled = false
);

-- Add a few B-tree indexes
CREATE INDEX idx_docs_category_created ON demo_documents (category_id, created_at);
CREATE INDEX idx_docs_status ON demo_documents (status);
CREATE INDEX idx_docs_title ON demo_documents (title);

-- Create HNSQ index on the vectores
CREATE INDEX idx_docs_embedding_hnsw ON demo_documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Generate some data
INSERT INTO demo_documents (category_id, status, title, created_at, content, embedding)
SELECT
    (random() * 50)::int + 1,
    (ARRAY['draft', 'published', 'archived', 'review'])[(random() * 3)::int + 1],
    'Document title #' || g,
    now() - (g || ' minutes')::interval,
    -- ~3,000 bytes of repetitive text (> 2 KB TOAST threshold)
    repeat('PostgreSQL TOAST and vector storage internals demo ' || g || ' ', 50),
    -- 768-dim vector
    random_vector(768)
FROM generate_series(1, 20000) AS g;

Check the size of all segments

Let’s establish a clear baseline for comparison. We will run an initial vacuum analyze and check all our relations.

-- Run an initial vacuum to start with clean baseline metrics
VACUUM ANALYZE demo_documents;

-- Check all the segments
SELECT
    c.relname AS object_name,
    CASE c.relkind
        WHEN 'r' THEN 'table'
        WHEN 't' THEN 'toast table'
        WHEN 'i' THEN 'index'
    END AS kind,
    pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relname = 'demo_documents'
   OR c.oid IN (SELECT indexrelid FROM pg_index WHERE indrelid = 'demo_documents'::regclass)
   OR c.oid = (SELECT reltoastrelid FROM pg_class WHERE relname = 'demo_documents')
ORDER BY pg_relation_size(c.oid) DESC;

Here is what we get:

        object_name | kind | size
---------------------------+-------------+---------
 idx_docs_embedding_hnsw | index | 78 MB
 pg_toast_3177242 | toast table | 81 MB
 demo_documents | table | 4336 kB
 idx_docs_category_created | index | 1024 kB
 idx_docs_title | index | 1024 kB
 demo_documents_pkey | index | 512 kB
 idx_docs_status | index | 256 kB
(7 rows)

Our demo_documents table with indexes and TOAST occupies about 170 MB. It might be helpful also to check tuples (physical rows):

-- Dead/Live tuple stats
SELECT 'Main Table' AS component, table_len,tuple_count,tuple_percent,dead_tuple_count, free_percent FROM pgstattuple('demo_documents');
-- TOAST Table Dead/Live tuple stats
SELECT 'TOAST Table' AS component, table_len,tuple_count,tuple_percent,dead_tuple_count, free_percent FROM pgstattuple((SELECT reltoastrelid FROM pg_class WHERE relname = 'demo_documents'));

We see the main table and the TOAST have zero dead tuples and reasonable small free space percentage:

 component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
------------+-----------+-------------+---------------+------------------+--------------
 Main Table | 4440064 | 20000 | 90.92 | 0 | 1.57

  component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
-------------+-----------+-------------+---------------+------------------+--------------
 TOAST Table | 84492288 | 40000 | 74.52 | 0 | 21.82

Here is a basic graph to illustrate the layout:

Updating the vectors

We emulate the process of refreshing embeddings by updating all the vectors in the demo_documents table. For example we had all our embeddings created using Google text-embedding-004 model and we updated all of them using gemini-embedding-2 model. Google publishing life cycle for all its models in the documentation where you can see it and plan your maintenance in advance.

-- Update all vector embeddings
UPDATE demo_documents
SET embedding = random_vector(768);

After updating all the rows we can check the sizes of all objects again and see how they’ve changed:

        object_name | kind | size before update | size after update
---------------------------+-------------+--------------------+-------------------
 idx_docs_embedding_hnsw | index | 78 MB | 156 MB
 pg_toast_3177242 | toast table | 81 MB | 159 MB
 demo_documents | table | 4336 kB | 7376 kB
 idx_docs_category_created | index | 1024 kB | 2048 kB
 idx_docs_title | index | 1024 kB | 2048 kB
 demo_documents_pkey | index | 512 kB | 1024 kB
 idx_docs_status | index | 256 kB | 512 kB
(7 rows)

Almost all objects including table, TOAST and indexes doubled in size. The impact is most visible for the HNSW vector index and the demo_documents table itself. Here is information about dead tuples and space allocation for the main table and TOAST:

 component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
------------+-----------+-------------+---------------+------------------+--------------
 Main Table | 7585792 | 20000 | 47.84 | 20000 | 1.2

  component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
-------------+-----------+-------------+---------------+------------------+--------------
 TOAST Table | 166387712 | 40000 | 37.84 | 40000 | 22.21

Here is a diagram:

So, roughly half of the space is occupied by dead tuples. When I discuss it with some developers they mention the vacuuming process and at least some of them had expectations that it should fix the bloating. Let’s do some vacuuming and see if it changes it.

VACUUM (VERBOSE, ANALYZE) demo_documents;

The verbose output provides enough information about what was done during the vacuum process and everybody who is curious can check it in detail. We can have a look into the main stats about tuples:

 component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
------------+-----------+-------------+---------------+------------------+--------------
 Main Table | 7585792 | 20000 | 47.84 | 0 | 50.16

  component | table_len | tuple_count | tuple_percent | dead_tuple_count | free_percent
-------------+-----------+-------------+---------------+------------------+--------------
 TOAST Table | 166387712 | 40000 | 37.84 | 0 | 60.17

The vacuum process cleared up the dead tuples leaving the space allocation intact and it was exactly what we expected. We have free space which can be used by the new rows but the allocation on the disk didn’t change.

        object_name | kind | size before vacuum | size after vacuum
---------------------------+-------------+--------------------+-------------------
 pg_toast_3177242 | toast table | 159 MB | 159 MB
 idx_docs_embedding_hnsw | index | 156 MB | 156 MB
 demo_documents | table | 7408 kB | 7408 kB
 idx_docs_category_created | index | 2048 kB | 2048 kB
 idx_docs_title | index | 2048 kB | 2048 kB
 demo_documents_pkey | index | 1024 kB | 1024 kB
 idx_docs_status | index | 512 kB | 512 kB

Alternative layout

What if the bloating is a significant issue for your workload? In such a case you might consider a different layout and place the vectors in a separate table connecting to the main table using primary keys. Then you can use either a view or simple table join to work with the data. And when the vectors are required to be updated — create a new table with a new vector version.

Such an approach can save you some vacuum overhead and bloating. But of course the join of two (or more) tables might perform slightly differently than the single flat table approach. You need to test it with your data and with your vectors.

Conclusion

The final layout of your table might depend on multiple factors. In real life I very rarely saw a case when the search was performed only in one single table. In most cases it was always a combination of 2,3,4, or more tables with the resulting dataset. Considering other parts like filters on non-vector columns or full-text search combined with the semantic search, the real case might be much more complicated than a demo with one flat table where all the data is stored in one segment. And for a high-loaded scalable environment, the right relational model can be a key for success and requires vigorous testing.

In the next post I will try to evaluate the performance overhead of keeping the vectors in a separate table vs having them in the same table. Stay tuned!

Evaluating AI models with SQL in Google Cloud databases

Gleb Otochkin — Wed, 02 Sep 2026 02:56:01 +0000

Introduction

Do you need to evaluate AI models with your data or cases? Yes, absolutely. Should it be done using complex and elaborate pipelines — it depends. Every time a new version of a model comes out, it behaves differently. You have to be able to evaluate each case with the model and see the differences. Sometimes you might realize that a lightweight, maybe cheaper model can work for your case as good as the more expensive one. In Google Cloud we have Gemini Enterprise Agent Platform which provides agent evaluation tools and you might have your own workflow going through sophisticated and elaborate tests. But what if you want to quickly check the new model or compare two different models available in your Cloud SQL for PostgreSQL or AlloyDB? You can use plain SQL and create a simple, quick evaluation procedure directly in your database. Let me show you an example of a single-answer point scoring evaluation.

AI Model Evaluation Components

What do you need for this evaluation? You need sample requests, registered model endpoint(s) you want to evaluate, a scale to quantify the performance, and evaluation criteria.

Here is a set of my sample requests:

'Explain Model Context Protocol (MCP) in 50 words or less.',
'Explain write-ahead logging (WAL) in PostgreSQL in 50 words or less.',
'Explain database connection pooling in 50 words or less.',
'Explain the difference between primary keys and unique constraints in 50 words or less.',
'Explain database normalization and its 3NF form in 50 words or less.',
'Explain transaction isolation levels like Serializable in 50 words or less.',
'Explain vector embeddings and cosine similarity in 50 words or less.',
'Explain database indexes (B-Tree vs Hash) in 50 words or less.',
'Explain the purpose of Foreign Keys in relational schemas in 50 words or less.',
'Explain optimistic vs pessimistic locking in databases in 50 words or less.'

Of course, all my questions were about databases. And since we were working in database — I put all my questions into the table:

CREATE TABLE IF NOT EXISTS benchmark_prompts (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category TEXT NOT NULL,
    prompt TEXT NOT NULL,
    max_words INT DEFAULT 50,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT clock_timestamp()
);
-- Index for fast active prompt lookups
CREATE INDEX IF NOT EXISTS idx_benchmark_prompts_active 
ON benchmark_prompts (is_active, category);

INSERT INTO benchmark_prompts (category, prompt, max_words) VALUES
('Protocols', 'Explain Model Context Protocol (MCP) in 50 words or less.', 50),
('Architecture', 'Explain write-ahead logging (WAL) in PostgreSQL in 50 words or less.', 50),
('Networking', 'Explain database connection pooling in 50 words or less.', 50),
('Data Modeling','Explain the difference between primary keys and unique constraints in 50 words or less.', 50),
('Data Modeling','Explain database normalization and its 3NF form in 50 words or less.', 50),
('Concurrency', 'Explain transaction isolation levels like Serializable in 50 words or less.', 50),
('Vector Search','Explain vector embeddings and cosine similarity in 50 words or less.', 50),
('Storage', 'Explain database indexes (B-Tree vs Hash) in 50 words or less.', 50),
('Data Modeling','Explain the purpose of Foreign Keys in relational schemas in 50 words or less.', 50),
('Concurrency', 'Explain optimistic vs pessimistic locking in databases in 50 words or less.', 50);

Next, I registered three different models in my AlloyDB database. The models were Gemini 3.5 Flash and Gemini 3.5 Flash Lite. All three models were available in AlloyDB, I just needed to register them. Here is an example of how you can register the Gemini 3.5 Flash Lite model:

CALL google_ml.create_model(
model_id => 'gemini-3.5-flash-lite',
model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.5-flash-lite:generateContent',
model_provider => 'google',
model_type => 'llm'
);

Don’t forget to replace the PROJECT_ID placeholder with your real project id if you use the procedure to register a model in your database.

The list of evaluation criteria depends on the nature of your data and how you use the AI models. In my case, I wanted to put some basic quality criteria for the model’s response:

1. Accuracy: Is the response technically accurate regarding the subject described in the prompt?
2. Word compliance: Is the response 50 words or less?
3. Quality: How well does it answer the question?

I wanted all my evaluations to be scaled from 1 to 10, where 10 would be the best response.

How did I evaluate the responses? I registered one more model in my database just for the evaluation. I used the Gemini 3.1 Pro Preview as the Pro model should provide higher-quality evaluations. But nothing stops you from using any other models. For example, you can test it with our partner models from Anthropic or Meta which are available in our Model Garden.

To save some tokens and time I put a limit of 50 words for each response to a question.

And last but not least, I count the total tokens for each request and average them at the end because cost is important and can help to choose the right model for each case.

SQL Function for Evaluation

Here is an example of a SQL function with all the evaluation conditions directly in the SQL code. The function takes three parameters: model to evaluate, model which will be doing evaluation and number of executions:

CREATE OR REPLACE FUNCTION benchmark_model_quality(
    p_target_model TEXT,
    p_evaluator_model TEXT,
    p_num_runs INT DEFAULT 10
)
RETURNS TABLE(
    total_runs INT,
    avg_quality_score NUMERIC,
    median_quality_score NUMERIC,
    min_quality_score NUMERIC,
    max_quality_score NUMERIC,
    avg_tokens_per_prompt NUMERIC
) AS $$
DECLARE
    v_all_prompts TEXT[];
    v_selected_prompts TEXT[] := '{}';
    v_current_prompt TEXT;
    v_predict_result JSON;
    v_target_response TEXT;
    v_tokens INT;
    v_total_tokens INT := 0;
    v_eval_prompt TEXT;
    v_eval_raw TEXT;
    v_score INT;
    v_scores INT[] := '{}';
    v_pool_size INT;
BEGIN
    -- Input validation
    IF p_num_runs <= 0 THEN
        RAISE EXCEPTION 'p_num_runs must be greater than 0';
    END IF;

    -- Load prompts directly from the table
    SELECT array_agg(prompt)
    INTO v_all_prompts
    FROM benchmark_prompts;

    v_pool_size := cardinality(v_all_prompts);

    IF v_pool_size IS NULL OR v_pool_size = 0 THEN
        RAISE EXCEPTION 'No prompts found in benchmark_prompts table';
    END IF;

    -- 1. Select exactly p_num_runs prompts WITH replacement from table pool
    SELECT ARRAY(
        SELECT v_all_prompts[floor(random() * v_pool_size + 1)::INT]
        FROM generate_series(1, p_num_runs)
    ) INTO v_selected_prompts;

    -- 2. Loop through each of the selected subjects and execute exactly ONCE
    FOREACH v_current_prompt IN ARRAY v_selected_prompts LOOP

        -- A. Query target model
        SELECT google_ml.predict_row(
            model_id => p_target_model,
            request_body => json_build_object(
                'contents', json_build_array(
                    json_build_object(
                        'role', 'user',
                        'parts', json_build_array(
                            json_build_object('text', v_current_prompt)
                        )
                    )
                )
            )
        ) INTO v_predict_result;

        -- B. Extract response text and tokens
        v_target_response := v_predict_result -> 'candidates' -> 0 -> 'content' -> 'parts' -> 0 ->> 'text';
        v_tokens := COALESCE((v_predict_result -> 'usageMetadata' ->> 'totalTokenCount')::INT, 0);
        v_total_tokens := v_total_tokens + v_tokens;

        -- C. Build adaptive judge prompt
        v_eval_prompt := format(
            'You are an expert technical evaluator. Evaluate the following generated response to the prompt: "%s"

Generated Response to Evaluate:
"%s"

Instructions:
Evaluate the response based on:
1. Accuracy: Is the response technically accurate regarding the subject described in the prompt?
2. Word compliance: Is the response 50 words or less?
3. Quality: How well does it answer the question?

You must respond ONLY with a raw JSON object conforming to the following schema, and no other text or Markdown formatting:
{
  "score": <integer from 1 to 10>,
  "reasoning": "<short description of why the score was given under 50 words>"
}', 
            v_current_prompt, 
            v_target_response
        );

        -- D. Query evaluator model
        SELECT google_ml.predict_row(
            model_id => p_evaluator_model,
            request_body => json_build_object(
                'contents', json_build_array(
                    json_build_object(
                        'role', 'user',
                        'parts', json_build_array(
                            json_build_object('text', v_eval_prompt)
                        )
                    )
                )
            )
        ) -> 'candidates' -> 0 -> 'content' -> 'parts' -> 0 ->> 'text' INTO v_eval_raw;

        -- E. Clean up markdown code blocks
        v_eval_raw := regexp_replace(v_eval_raw, '^```

json\s*', '', 'i');
        v_eval_raw := regexp_replace(v_eval_raw, '\s*

```$', '', 'i');
        v_eval_raw := trim(v_eval_raw);

        -- F. Parse and store score
        BEGIN
            v_score := (v_eval_raw::json ->> 'score')::INT;
            v_scores := array_append(v_scores, v_score);
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING 'Failed to parse JSON score from evaluator output: %', v_eval_raw;
        END;

    END LOOP;

    -- 3. Compute aggregate stats over successfully parsed scores
    RETURN QUERY
    WITH sorted_scores AS (
        SELECT s, 
               row_number() OVER (ORDER BY s) as rn, 
               count(*) OVER () as total_count
        FROM unnest(v_scores) AS s
    ),
    stats AS (
        SELECT 
            avg(s)::NUMERIC(10,2) as v_avg,
            min(s) as v_min,
            max(s) as v_max
        FROM unnest(v_scores) AS s
    ),
    median_stat AS (
        SELECT avg(s)::NUMERIC(10,2) as v_median
        FROM sorted_scores
        WHERE rn IN (floor((total_count + 1)/2.0), ceil((total_count + 1)/2.0))
    )
    SELECT 
        cardinality(v_scores),
        stats.v_avg,
        median_stat.v_median,
        stats.v_min::NUMERIC,
        stats.v_max::NUMERIC,
        (v_total_tokens::NUMERIC / NULLIF(cardinality(v_scores), 0))::NUMERIC(10,2)
    FROM stats, median_stat;
END;
$$ LANGUAGE plpgsql;

There are some potential improvements to the procedure and the method depending on use case. For example you can expand it with “Golden” responses for each question for evaluation. It is useful when you are working with your own domain knowledge and know exactly how the best answer should sound like.

Testing

I tried it on two different models and compared the results.

The first test was for the Gemini 3.5 Flash model. I set the test to run 45 times:

SELECT * FROM benchmark_model_quality('gemini-3.5-flash', 'gemini-3.1-pro-preview', 45);

The Gemini 3.5 Flash showed really good results:

 total_runs | avg_quality_score | median_quality_score | min_quality_score | max_quality_score | avg_tokens_per_prompt
------------+-------------------+----------------------+-------------------+-------------------+-----------------------
         45 | 9.96 | 10.00 | 8 | 10 | 916.27

Then I ran the same test for Gemini 3.5 Flash Lite:

 total_runs | avg_quality_score | median_quality_score | min_quality_score | max_quality_score | avg_tokens_per_prompt
------------+-------------------+----------------------+-------------------+-------------------+-----------------------
         45 | 9.82 | 10.00 | 7 | 10 | 77.11

For that sample set, Gemini 3.5 Flash Lite showed almost the same quality and cost much less.

If you compare the tokens per request, you can see an 11x reduction in token count. And those tokens are 3.5 times cheaper than Gemini 3.5 Flash. So, you can effectively pay 38 times less for the same quality response.

Conclusion

There are two things I want to say in this article. If you are working in AlloyDB or Cloud SQL and need to quickly evaluate some AI models you can try it using SQL function thanks to the direct integration with AI in Google Cloud databases.

And maybe you can use a less expensive model for your workload and get acceptable results saving some money.

Happy testing and let me know if you think such kind of simple recipes are useful for you.

7 Times Faster and Cheaper? Gemini 3.5 Flash Lite and Gemini 3.6 Flash in Google Cloud Databases

Gleb Otochkin — Sun, 26 Jul 2026 06:11:17 +0000

Only a couple of months ago, I wrote a blog post describing how to enable the latest (at the time) Gemini 3.5 Flash in Google Cloud databases. Now, it is time to introduce the newest model: Gemini 3.6 Flash. This model is fresher, with a knowledge cutoff date around the end of March this year, and it costs less. Let’s look at how it performs at first glance. I am particularly curious about its response times and how it compares to the previous model.

Cloud SQL

For Cloud SQL for Postgres, don’t forget to enable full AI integration via the google_ml extension to call modelsdirectly from a SQL query. If you are reading this AI integration overview right after the model announcement, you might need to register the model first. It wasn’t available by default when I tested it.

Here is the SQL statement to register the model. Don’t forget to replace the PROJECT_ID placeholder in the code with your actual Google Cloud project ID:

CALL google_ml.create_model(
    model_id => 'gemini-3.6-flash',
    model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.6-flash:generateContent',
    model_provider => 'google',
    model_type => 'generic',
    model_auth_type => 'cloudsql_service_agent_iam'
);

After that, your model is ready to be used:

SELECT google_ml.predict_row(
    model_id => 'gemini-3.6-flash',
    request_body => json_build_object(
        'contents', json_build_array(
            json_build_object(
                'role', 'user',
                'parts', json_build_array(
                    json_build_object('text', 'Explain MCP server for a relational database in 50 words or less.')
                )
            )
        )
    )
) ->'candidates' -> 0 -> 'content' -> 'parts' -> 0 -> 'text' AS ai_response;

To measure the latency, I’ve asked Google Antigravity to prepare a PL/pgSQL function to run that query a certain number of times and aggregate the results. It did a decent job, I needed only a few polishing and adjustments to make it working. Here are my benchmarking results for Gemini 3.6 Flash and Gemini 3.5 Flash:

quickstart_db=> SELECT * FROM benchmark_model_predictions('gemini-3.6-flash', 10);
 total_runs | avg_latency_ms | median_latency_ms | min_latency_ms | max_latency_ms 
------------+----------------+-------------------+----------------+----------------
         10 | 3694.53 | 3725.52 | 3313.01 | 4213.40
(1 row)

Time: 36947.554 ms (00:36.948)
quickstart_db=> SELECT * FROM benchmark_model_predictions('gemini-3.5-flash', 10);
 total_runs | avg_latency_ms | median_latency_ms | min_latency_ms | max_latency_ms 
------------+----------------+-------------------+----------------+----------------
         10 | 4918.86 | 5136.67 | 3936.69 | 5722.01
(1 row)

Time: 49190.861 ms (00:49.191)

It looks like, all other conditions being equal, Gemini 3.6 Flash was slightly faster than the previous model. Of course, this is a very crude measurement, and your results may vary. Since we are using a global endpoint, the routing might theoretically hit different regional endpoints. Furthermore, public model endpoints are shared services.

Next, I created a function to evaluate the quality of the responses and ran it for both models. The function uses the same query, receives the response, and sends the result to a more advanced model for evaluation. In this case, I used gemini-3.1-pro-preview as the judge:

quickstart_db=> SELECT * FROM benchmark_model_quality('gemini-3.6-flash', 'gemini-3.1-pro-preview', 5);
 total_runs | avg_quality_score | median_quality_score | min_quality_score | max_quality_score 
------------+-------------------+----------------------+-------------------+-------------------
          5 | 10.00 | 10.00 | 10 | 10
(1 row)

quickstart_db=> SELECT * FROM benchmark_model_quality('gemini-3.5-flash', 'gemini-3.1-pro-preview', 5);
 total_runs | avg_quality_score | median_quality_score | min_quality_score | max_quality_score 
------------+-------------------+----------------------+-------------------+-------------------
          5 | 9.80 | 10.00 | 9 | 10
(1 row)

quickstart_db=>

Both models delivered excellent results, but Gemini 3.6 Flash provided slightly better responses than its predecessor — at least according to the gemini-3.1-pro-preview judge.

What about AlloyDB? Let’s run the same performance benchmark on AlloyDB to see if the response times match our Cloud SQL results.

AlloyDB

Registering the new model in AlloyDB follows almost the same process, with the only difference being the ‘model_type’ parameter. In AlloyDB, we register it with the model type ‘llm’. Again, don’t forget to replace the PROJECT_ID placeholder with your actual project:

CALL google_ml.create_model(
model_id => 'gemini-3.6-flash',
model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.6-flash:generateContent',
model_provider => 'google',
model_type => 'llm'
);

For these tests, I used the exact same query and function as we did for Cloud SQL. AlloyDB is fully compatible with PostgreSQL and shares the same AI integration extension.

quickstart_db=> SELECT * FROM benchmark_model_predictions('gemini-3.5-flash', 10);
 total_runs | avg_latency_ms | median_latency_ms | min_latency_ms | max_latency_ms
------------+----------------+-------------------+----------------+----------------
         10 | 4899.00 | 4910.05 | 3631.22 | 5798.40
(1 row)

Time: 48992.149 ms (00:48.992)
quickstart_db=> SELECT * FROM benchmark_model_predictions('gemini-3.6-flash', 10);
 total_runs | avg_latency_ms | median_latency_ms | min_latency_ms | max_latency_ms
------------+----------------+-------------------+----------------+----------------
         10 | 4364.76 | 4368.65 | 3664.24 | 5008.42
(1 row)

Time: 43649.384 ms (00:43.649)
quickstart_db=>

This time, the results were much closer, and the response times were almost identical. It is hard to draw definitive conclusions here, considering we are using a shared service with global routing; there is no guarantee that consecutive requests are hitting the same physical endpoint. However, it appears that performance and response times are roughly equivalent for both models. Naturally, the quality of the responses remained consistent with our previous test, as quality depends directly on the models themselves.

Gemini 3.5 Flash Lite

But do you always need a “Pro” or “Flash” model for your queries? In many cases, you can get highly acceptable results from a “Lite” model like the newly released gemini-3.5-flash-lite. I tested its performance using the same benchmarking approach:

quickstart_db=> SELECT * FROM benchmark_model_predictions('gemini-3.5-flash-lite', 10);
 total_runs | avg_latency_ms | median_latency_ms | min_latency_ms | max_latency_ms
------------+----------------+-------------------+----------------+----------------
         10 | 827.08 | 882.05 | 499.95 | 1034.92
(1 row)

Time: 8272.929 ms (00:08.273)

It was about seven times faster than Gemini 3.6 Flash. But what about response quality?

quickstart_db=> SELECT * FROM benchmark_model_quality('gemini-3.5-flash-lite', 'gemini-3.1-pro-preview', 5);
 total_runs | avg_quality_score | median_quality_score | min_quality_score | max_quality_score
------------+-------------------+----------------------+-------------------+-------------------
          5 | 10.00 | 10.00 | 10 | 10
(1 row)

Time: 42923.480 ms (00:42.923)
quickstart_db=> SELECT * FROM benchmark_model_quality('gemini-3.5-flash-lite', 'gemini-3.1-pro-preview', 5);
 total_runs | avg_quality_score | median_quality_score | min_quality_score | max_quality_score
------------+-------------------+----------------------+-------------------+-------------------
          5 | 9.60 | 10.00 | 8 | 10
(1 row)

Time: 51722.997 ms (00:51.723)
quickstart_db=> SELECT * FROM benchmark_model_quality('gemini-3.5-flash-lite', 'gemini-3.1-pro-preview', 5);
 total_runs | avg_quality_score | median_quality_score | min_quality_score | max_quality_score
------------+-------------------+----------------------+-------------------+-------------------
          5 | 9.80 | 10.00 | 9 | 10
(1 row)

Time: 53252.325 ms (00:53.252)

I ran the same benchmark three times, and even the worst-performing run was highly scored. The average scores ranged from 9.6 to 10 (with 10 being a perfect score). I was impressed by both the quality and the speed.

Summary

The new Gemini 3.6 Flash model is less expensive than Gemini 3.5 Flash. Considering it offers comparable or better quality and the same or faster response times, it is definitely a win.

However, Gemini 3.5 Flash Lite is maybe even more impressive, delivering excellent quality while running seven times faster at a very attractive price. This means that many simpler, everyday tasks can be delegated to the Lite model to keep costs down, reserving more advanced models for complex problems that require deep reasoning and multi-step iterations.

How your sample data impact vector tests in PostgreSQL and AlloyDB.

Gleb Otochkin — Tue, 14 Jul 2026 04:44:34 +0000

Introduction

If you are using vector data in PostgreSQL on Cloud SQL or AlloyDB, you probably use some benchmark queries or functions to check your vector search performance. From time to time, I see people performing speed benchmarks or quality testing using generated, fully synthetic data. The main goal, after all, is to test, for example, performance with a particular data type and get some comparable numbers. Does it really matter what data I have in my vector column?

It really depends, and things can indeed go very wrong when you try to evaluate the index’s performance. I use synthetic data all the time for my tests. But it depends on what I am testing. In some cases, real vs. synthetic data might have a significant impact on the results. Let me show you how it can change the outcome of some tests for the vector data type in PostgreSQL.

Two datasets

Some tests can be done using fully synthetic data. For example, if you test inserts, updates, or deletes for vectors, then synthetic data works for you. In such cases, I usually create a fully synthetic dataset of totally random, normalized number arrays. Here is an example of a simple function and how to generate the dataset.

-- Create a function 
CREATE OR REPLACE FUNCTION generate_random_vector(dimensions integer)
RETURNS vector
LANGUAGE sql
AS $$
    SELECT array_agg(random()*2-1)::vector
    FROM generate_series(1, dimensions);
$$;
-- Creating the test table with vector data type 768 dimensions
CREATE TABLE tvectr768(
id BIGINT PRIMARY KEY,
d VECTOR(768)
);
-- Populate the table with 1M or fows
INSERT INTO tvectr768 (id, d)
SELECT
    i AS id,
    -- This function is now called for each row
    generate_random_vector(768) AS d
FROM
    generate_series(1, 1000000) AS i;

In the end, we have a table with 1 million vectors. Each vector dimension value is between -1 and 1. So, the vector is normalized and shows more or less the same behaviour for the DML operations as normally generated embedding for a piece of content.

But when I test recall quality or performance for vector search, I use one of the publicly available datasets with vectors created based on real data. You can find multiple datasets for embeddings on Hugging Face or other sources. For example, you can check the Cohere Labs datasets here: https://huggingface.co/CohereLabs/datasets.

Here are my 1M rows with the same dimensions and structure, but all the vectors in those rows are based on real data.

pgdata=# \d tvreal768
               Table "public.tvreal768"
 Column | Type | Collation | Nullable | Default
--------+-------------+-----------+----------+---------
 id | bigint | | |
 d | vector(768) | | |

pgdata=#

Both tables have the same size and structure.

pgdata=# SELECT
pgdata-# relname AS table_name,
pgdata-# pg_size_pretty(pg_table_size(relid)) AS table_size
pgdata-# FROM
pgdata-# pg_catalog.pg_statio_user_tables
pgdata-# WHERE
pgdata-# relname IN ('tvectr768', 'tvreal768');
 table_name | table_size
------------+------------
 tvectr768 | 4008 MB
 tvreal768 | 4008 MB

If you go a bit deeper, you can see that both tables are about 57 MB in heap size, plus 3950 MB in TOAST where the actual vector data is stored. It makes perfect sense, since the d column with vector(768) is larger than the default 2k threshold and is stored entirely in the TOAST segment.

pgdata-# relname AS table_name,
pgdata-# pg_size_pretty(pg_relation_size(oid)) AS heap_size,
pgdata-# pg_size_pretty(pg_table_size(oid) - pg_relation_size(oid)) AS toast_size
pgdata-# FROM pg_class
pgdata-# WHERE relname = ANY ('{"tvectr768" ,"tvreal768"}')
pgdata-# AND relkind = 'r';
 table_name | heap_size | toast_size
------------+-----------+------------
 tvectr768 | 57 MB | 3950 MB
 tvreal768 | 57 MB | 3950 MB

Let us do some performance tests and compare the results.

Building Vector Index

For my tests, I was using Postgres 18 on a Debian VM in Google Cloud, and AlloyDB Omni for ScaNN index tests on a VM of the same size. We are mostly interested in the difference between “real” and synthetic vector values, but this varies depending on the index. The first test is to build an IVFFLAT index on our vectors.

Here is an example for the IVFFLAT index on synthetic vector data.

drop index if exists tvectr768_d_idx;
CREATE INDEX ON tvectr768 USING ivfflat (d vector_cosine_ops) WITH (lists=1000);

You can see I was using default parameters, specifying only the number of lists. According to my tests, it takes about 271,068 ms to build the index on the synthetic tvectr768 table. And when it is built, the size of the index is roughly equal to the size of all the vectors (3912 MB), which makes total sense.

When we run the same for the tvreal768 table with real vectors, we get similar results: 271,788 ms. So far, there is no difference. And it is probably expected if we think about how the IVFFLAT index is being built with splitting to buckets according to the number of lists and then simply moving each vector to one or another bucket.

What about the HNSW index? Here is an example of a query used to build the HNSW index.

drop index if exists tvectr768_d_idx;
CREATE INDEX ON tvectr768 USING hnsw (d vector_cosine_ops);

And here we start to see the difference in performance. The HNSW is a graph-based index and it is much harder to find node connections for a totally random array of numbers. You can play with different build parameters for the index and see how it can impact the results.

For the building with default parameters it took on average 6601789 ms for synthetic data and 4174196 ms for the “real” vectors. That’s 1.5 faster for the real data and it is significant.

So, why is it happening? How does the randomness in the numbers in the vector impact the performance? It probably deserves a separate article but I will try to give a short explanation. The HNSW builds its graph incrementally. When it adds another vector to the index it has to search for the right place in the graph. It means to find the closest neighbors to connect the new vector to. HNSW relies on the assumption that your data contains some underlying structure and it means clusters, patterns, and dense neighborhoods. The embedding vectors have but the random vector most likely not. Because of that the build process has to evaluate more candidates to find neighbors. That process is mathematically expensive for distances like cosine distance and takes time. That’s a short explanation but I hope it helps to understand to some extent the reasons behind the difference in built time.

With the ScaNN index we have another case of striking difference between building time for index with fully synthetic vs “real” dataset. I was using the basic recommended parameters for the ScaNN index according to the guide for AlloyDB Omni.

BEGIN;
SET LOCAL scann.num_leaves_to_search = 1;
SET LOCAL scann.pre_reordering_num_neighbors=50;
drop index if exists tvreal768_d_idx;
CREATE INDEX tvreal768_d_idx ON tvreal768 USING scann (d cosine) WITH (num_leaves = 1000);
END;

It took 237094 ms to build the index on the synthetic data vs only 62673 ms on the “real” dataset. And the size of indexes was quite different because AlloyDB ScaNN was using internal index optimization for the real dataset. We have 1562 MB for the synthetic data index vs 275 MB for the “real” one.

So, when you start to evaluate performance in building indexes then you probably should use proper data or you can get misleading results. I would recommend using vectors built on your actual data.

Vector Search Performance

Now that we have our ANN indexes built, we can evaluate how quickly you can find the top 5 similar vectors using the HNSW index. I am using a procedure that takes 100 samples from the table and uses some of those samples as predicates to find the 5 most similar vectors. I repeated this 11 times, discarding the first execution. This gives me the throughput in Queries Per Second (QPS) and the average execution time per query, along with the max and min times for all executions. You can check the procedure benchmark_vector_search in the Appendix chapter and the end of the article or download it from GitHub.

Here is a sample output from the procedure.

NOTICE: ------------------------------------------------------------------------
NOTICE: Benchmark finished successfully.
NOTICE: Total queries executed: 1000 (excluding warmup)
NOTICE: Total execution time: 2.145 seconds (excluding warmup)
NOTICE: Throughput (overall): 466.1 QPS (excluding warmup)
NOTICE: ------------------------------------------------------------------------
NOTICE: Latency Stats (excluding warmup):
NOTICE: Average: 2.125 ms
NOTICE: Minimum: 1.389 ms
NOTICE: Maximum: 3.888 ms
NOTICE: ------------------------------------------------------------------------

When we run our tests, it shows 4.173 ms on average for synthetic data vs. 2.102 ms for our “real” vectors using HNSW index. Interestingly enough, for AlloyDB ScaNN, the difference is not so big — only 5.958 ms vs. 5.161 ms if we use 1,000 lists. But if we use an index with only 100 lists, we can see a significant difference in performance, where the response time for synthetic data is 7.239 ms vs. 4.249 ms for the “real” ones.

The reasons behind the difference in performance have the same nature but slightly different consequences. Since the random vector lacks clusters and dense neighborhoods it goes through more steps to find the closest neighbours. Instead, for example going through 32 nodes to find the closest neighbour it has to traverse 256 or more and even in such a case the quality might be still low.

So, if you are testing HNSW, which is widely used in the community, the 2x difference is big enough and fully justifies the effort of getting a real dataset.

Vector Search Quality

We already established that synthetic data can mislead you about build time or vector search speed for your vector indexes. What about quality?

Usually, when we talk about vector search quality, we use the term “recall,” which represents the percentage of exact matches returned by an ANN index compared to the list of hits returned by a KNN exact search. For example, we do a KNN search returning the top 10 similar vectors. Then, we run exactly the same query with an ANN index and compare how many of the 10 vectors returned by the ANN search match the KNN results. If it is 9 out of 10, then it is 90% recall.

In my procedure evaluate_vector_recall, I use 100 random samples from the dataset and then iterate on those samples to get the recall for each of them, averaging the results at the end. I tested it with the top 10 similar vectors (k_param=>10). The procedure is provided in the Appendix and on GitHub.

Here is a sample output of the procedure.

pgdata=# call evaluate_vector_recall('tvreal768',768,k_param=>10);
NOTICE: Starting vector similarity search recall evaluation...
NOTICE: Target table: tvreal768, Expected dimensions: 768
NOTICE: Parameters - Vector Column: d, ID Column: id, K: 10, Sample Size: 100
NOTICE: Selecting 100 random vectors for sampling from tvreal768...
NOTICE: ------------------------------------------------------------------------
NOTICE: Recall Evaluation Finished.
NOTICE: Total queries evaluated: 100
NOTICE: Average Recall@10: %91.80
NOTICE: Minimum Recall@10: %0.00
NOTICE: Maximum Recall@10: %100.00
NOTICE: ------------------------------------------------------------------------
CALL

According to my test results, the recall for synthetic data is quite low. On average for the 100 samples, I am getting only 3.8% recall using HNSW, while for “real” data, the recall is 91.8%. This means our search on synthetic data is not only slow but also hugely inaccurate. When we try it using the AlloyDB ScaNN index, it shows 16.4% recall for synthetic data vs. 91.4% for the “real” data. Only IVFFLAT didn’t show itself good with the real dataset giving only about 51.9% recall on average. But the quality with synthetic data was still much worse than that — only 10.6%

Of course, your test results can and most likely will be different, but the trend is clear. Even if ScaNN shows slightly better results for a synthetic vector search, it is still quite misleading and inaccurate.

You also can opt-in to use the evaluate_query_recall procedure coming with the AlloyDB Omni and it gives you the option to measure recall. Read more in the official guide on how to use the procedure.

Conclusion

Here is my short summary for all the tests and results

Synthetic vectors can be used for pure performance DML tests where the vectors are not used as predicates and are only used to fill up space. For example, for inserts, updates, or deletes of rows filtered by non-vector column data.
Synthetic vector data shows incorrect results for index build times and can mislead testers by giving inaccurate results. So far, in most of my tests, synthetic data required more time to build the index compared to realistic vector data.
An ANN vector search executed against a synthetic dataset can be 1.5 times slower than for a similar dataset with realistic vectors.
Recall quality for an ANN vector search cannot be measured against synthetic vectors. In my tests, the recall quality ranged from 3.8% to 16.4%, depending on the index type and index parameters. The real dataset showed more than 90% recall for most of the tests.
Get a real dataset and use it for all your performance and quality testing for ANN indexes.

Appendix

Here are some procedures I used for testing. I don’t pretend they are perfect in any way but should be clear enough for everyone who tries to use it. I used some hardcoded values to make it simpler to read. Both procedures are available on GitHub.

Here is the procedure to check response time for vector search. It is using cosine distance for measurement and takes the first 100 vectors from the table as a sample to be used later.

CREATE OR REPLACE PROCEDURE benchmark_vector_search(
    n_repetitions integer,
    table_name_param text,
    vector_dimensions integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- Array to hold the 100 vectors sampled for the benchmark run
    sample_vectors vector[];
    -- Variable to hold the current vector being searched for
    current_vector vector;
    -- Variables for timing
    overall_start_time timestamptz;
    overall_end_time timestamptz;
    repetition_start_time timestamptz;
    q_start timestamptz;
    q_end timestamptz;
    q_diff double precision; -- in milliseconds

    -- Metrics tracked across the entire benchmark
    global_min_latency double precision := 1e9;
    global_max_latency double precision := 0;
    global_sum_latency double precision := 0;
    overall_total_queries integer := 0;

    -- Variable for dimension validation
    actual_dimensions integer;
    actual_sample_size integer;

    -- Loop counter
    i int;
BEGIN
    RAISE NOTICE 'Starting vector similarity search benchmark...';
    RAISE NOTICE 'Target table: %, Repetitions: %, Expected dimensions: %', table_name_param, n_repetitions, vector_dimensions;

    -- Validate that the vector column "d" exists and dimensions match
    BEGIN
        EXECUTE format('SELECT vector_dims(d) FROM %I WHERE d IS NOT NULL LIMIT 1', table_name_param)
        INTO actual_dimensions;

        IF actual_dimensions IS NULL THEN
            RAISE EXCEPTION 'Could not find any non-NULL vectors in table %. The table might be empty or column "d" contains only NULLs.', table_name_param;
        END IF;

        IF actual_dimensions != vector_dimensions THEN
            RAISE EXCEPTION 'Vector dimension mismatch. Expected dimension %, but found dimension % in table %.', vector_dimensions, actual_dimensions, table_name_param;
        END IF;
    EXCEPTION
        WHEN undefined_table THEN
            RAISE EXCEPTION 'Table "%" does not exist.', table_name_param;
        WHEN undefined_column THEN
            RAISE EXCEPTION 'Table "%" does not have a column named "d".', table_name_param;
    END;

    RAISE NOTICE 'Dimension validation successful.';

    -- Fetch 100 random vectors to use as search queries
    RAISE NOTICE 'Selecting 100 random vectors for sampling from %...', table_name_param;
    EXECUTE format('SELECT array_agg(d) FROM (SELECT d FROM %I WHERE d IS NOT NULL ORDER BY random() LIMIT 100) AS random_sample', table_name_param)
    INTO sample_vectors;

    IF sample_vectors IS NULL OR COALESCE(array_length(sample_vectors, 1), 0) = 0 THEN
        RAISE EXCEPTION 'Could not retrieve any sample vectors. The table % might be empty or all vectors are NULL.', table_name_param;
    END IF;

    actual_sample_size := array_length(sample_vectors, 1);
    IF actual_sample_size < 100 THEN
        RAISE WARNING 'Could not retrieve 100 sample vectors. Using % available sample vectors instead.', actual_sample_size;
    END IF;

    RAISE NOTICE 'Starting main benchmark loop...';
    overall_start_time := clock_timestamp();

    -- The main outer loop that repeats the entire test (warmup + N repetitions)
    FOR i IN 0..n_repetitions LOOP
        repetition_start_time := clock_timestamp();

        -- Reset overall_start_time to start of repetition 1 to exclude warmup duration from QPS/Totals
        IF i = 1 THEN
            overall_start_time := clock_timestamp();
        END IF;

        DECLARE
            rep_min_latency double precision := 1e9;
            rep_max_latency double precision := 0;
            rep_sum_latency double precision := 0;
            rep_queries integer := 0;
            rep_duration double precision;
        BEGIN
            -- The inner loop that iterates through each of the sampled vectors
            FOREACH current_vector IN ARRAY sample_vectors
            LOOP
                q_start := clock_timestamp();
                -- Perform the core operation (limiting to top 5)
                EXECUTE format('SELECT 1 FROM %I ORDER BY d <=> $1 LIMIT 5', table_name_param)
                USING current_vector;
                q_end := clock_timestamp();

                q_diff := extract(epoch from (q_end - q_start)) * 1000.0; -- milliseconds

                -- Accumulate local statistics
                IF q_diff < rep_min_latency THEN rep_min_latency := q_diff; END IF;
                IF q_diff > rep_max_latency THEN rep_max_latency := q_diff; END IF;
                rep_sum_latency := rep_sum_latency + q_diff;
                rep_queries := rep_queries + 1;
            END LOOP;

            -- Calculate total time for this repetition
            rep_duration := extract(epoch from (clock_timestamp() - repetition_start_time));

            IF i = 0 THEN
                -- Warmup iteration: report it but do not add to global statistics
                RAISE NOTICE 'Warmup Repetition: Avg Latency = % ms (Min: % ms, Max: % ms) | QPS: %', 
                             round((rep_sum_latency / rep_queries)::numeric, 3), 
                             round(rep_min_latency::numeric, 3), 
                             round(rep_max_latency::numeric, 3),
                             round((rep_queries / rep_duration)::numeric, 1);
            ELSE
                -- Accumulate global statistics
                IF rep_min_latency < global_min_latency THEN global_min_latency := rep_min_latency; END IF;
                IF rep_max_latency > global_max_latency THEN global_max_latency := rep_max_latency; END IF;
                global_sum_latency := global_sum_latency + rep_sum_latency;
                overall_total_queries := overall_total_queries + rep_queries;

                RAISE NOTICE 'Repetition %/%: Avg Latency = % ms (Min: % ms, Max: % ms) | QPS: %', 
                             i, n_repetitions, 
                             round((rep_sum_latency / rep_queries)::numeric, 3), 
                             round(rep_min_latency::numeric, 3), 
                             round(rep_max_latency::numeric, 3),
                             round((rep_queries / rep_duration)::numeric, 1);
            END IF;
        END;
    END LOOP;

    overall_end_time := clock_timestamp();

    -- Log overall summary metrics
    DECLARE
        total_benchmark_time double precision;
        overall_qps double precision;
    BEGIN
        total_benchmark_time := extract(epoch from (overall_end_time - overall_start_time));
        overall_qps := overall_total_queries / total_benchmark_time;

        RAISE NOTICE '------------------------------------------------------------------------';
        RAISE NOTICE 'Benchmark finished successfully.';
        RAISE NOTICE 'Total queries executed: % (excluding warmup)', overall_total_queries;
        RAISE NOTICE 'Total execution time: % seconds (excluding warmup)', round(total_benchmark_time::numeric, 3);
        RAISE NOTICE 'Throughput (overall): % QPS (excluding warmup)', round(overall_qps::numeric, 1);
        RAISE NOTICE '------------------------------------------------------------------------';
        RAISE NOTICE 'Latency Stats (excluding warmup):';
        RAISE NOTICE ' Average: % ms', round((global_sum_latency / overall_total_queries)::numeric, 3);
        RAISE NOTICE ' Minimum: % ms', round(global_min_latency::numeric, 3);
        RAISE NOTICE ' Maximum: % ms', round(global_max_latency::numeric, 3);
        RAISE NOTICE '------------------------------------------------------------------------';
    END;
END;
$$;

And here is the procedure where I measure recall quality. It takes recall from each sample and then calculates an average recall quality for the entire execution.

CREATE OR REPLACE PROCEDURE evaluate_vector_recall(
    table_name_param text,
    vector_dimensions integer,
    vector_col_param text DEFAULT 'd',
    id_col_param text DEFAULT 'id',
    k_param integer DEFAULT 5,
    sample_size_param integer DEFAULT 100
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- Array to hold the vectors sampled for the recall evaluation
    sample_vectors vector[];
    -- Variable to hold the current vector being searched for
    current_vector vector;
    -- Arrays to hold the result IDs from exact and approximate searches
    exact_ids text[];
    approx_ids text[];
    -- Metrics variables
    overlap_count integer;
    vector_recall double precision;
    sum_recall double precision := 0.0;
    min_recall double precision := 1.0;
    max_recall double precision := 0.0;
    actual_dimensions integer;
    actual_sample_size integer;
    total_queries integer := 0;
BEGIN
    RAISE NOTICE 'Starting vector similarity search recall evaluation...';
    RAISE NOTICE 'Target table: %, Expected dimensions: %', table_name_param, vector_dimensions;
    RAISE NOTICE 'Parameters - Vector Column: %, ID Column: %, K: %, Sample Size: %', 
                 vector_col_param, id_col_param, k_param, sample_size_param;

    -- Validate vector column exists and dimension matches
    BEGIN
        EXECUTE format('SELECT vector_dims(%I) FROM %I WHERE %I IS NOT NULL LIMIT 1', 
                       vector_col_param, table_name_param, vector_col_param)
        INTO actual_dimensions;

        IF actual_dimensions IS NULL THEN
            RAISE EXCEPTION 'Could not find any non-NULL vectors in column % of table %. The table might be empty or contains only NULLs.', 
                            vector_col_param, table_name_param;
        END IF;

        IF actual_dimensions != vector_dimensions THEN
            RAISE EXCEPTION 'Vector dimension mismatch. Expected dimension %, but found dimension % in table %.', 
                            vector_dimensions, actual_dimensions, table_name_param;
        END IF;
    EXCEPTION
        WHEN undefined_table THEN
            RAISE EXCEPTION 'Table "%" does not exist.', table_name_param;
        WHEN undefined_column THEN
            RAISE EXCEPTION 'Column "%" does not exist in table "%".', vector_col_param, table_name_param;
    END;

    -- Validate ID column exists
    BEGIN
        EXECUTE format('SELECT %I FROM %I LIMIT 1', id_col_param, table_name_param);
    EXCEPTION
        WHEN undefined_column THEN
            RAISE EXCEPTION 'ID column "%" does not exist in table "%". Cannot measure recall.', 
                            id_col_param, table_name_param;
    END;

    -- Select random sample vectors (ignoring nulls)
    RAISE NOTICE 'Selecting % random vectors for sampling from %...', sample_size_param, table_name_param;
    EXECUTE format('SELECT array_agg(%I) FROM (SELECT %I FROM %I WHERE %I IS NOT NULL ORDER BY random() LIMIT $1) AS random_sample', 
                   vector_col_param, vector_col_param, table_name_param, vector_col_param)
    INTO sample_vectors
    USING sample_size_param;

    IF sample_vectors IS NULL OR COALESCE(array_length(sample_vectors, 1), 0) = 0 THEN
        RAISE EXCEPTION 'Could not retrieve any sample vectors. The table % might be empty or all vectors are NULL.', table_name_param;
    END IF;

    actual_sample_size := array_length(sample_vectors, 1);
    IF actual_sample_size < sample_size_param THEN
        RAISE WARNING 'Could not retrieve % sample vectors. Using % available sample vectors instead.', 
                      sample_size_param, actual_sample_size;
    END IF;

    -- Iterate through sampled vectors to calculate recall
    FOREACH current_vector IN ARRAY sample_vectors
    LOOP
        -- 1. Find exact nearest neighbors (ground truth) by forcing sequential scan
        SET LOCAL enable_indexscan = off;
        SET LOCAL enable_bitmapscan = off;
        EXECUTE format('SELECT array_agg(%I::text) FROM (SELECT %I FROM %I ORDER BY %I <=> $1 LIMIT $2) AS exact_search', 
                       id_col_param, id_col_param, table_name_param, vector_col_param)
        INTO exact_ids
        USING current_vector, k_param;

        -- 2. Find approximate nearest neighbors (allowing index scan)
        SET LOCAL enable_indexscan = on;
        SET LOCAL enable_bitmapscan = on;
        EXECUTE format('SELECT array_agg(%I::text) FROM (SELECT %I FROM %I ORDER BY %I <=> $1 LIMIT $2) AS approx_search', 
                       id_col_param, id_col_param, table_name_param, vector_col_param)
        INTO approx_ids
        USING current_vector, k_param;

        -- 3. Calculate overlap
        IF exact_ids IS NOT NULL AND approx_ids IS NOT NULL THEN
            SELECT COUNT(*) INTO overlap_count
            FROM unnest(approx_ids) a
            JOIN unnest(exact_ids) e ON a = e;

            vector_recall := overlap_count::double precision / k_param;
            sum_recall := sum_recall + vector_recall;

            IF vector_recall < min_recall THEN min_recall := vector_recall; END IF;
            IF vector_recall > max_recall THEN max_recall := vector_recall; END IF;

            total_queries := total_queries + 1;
        END IF;
    END LOOP;

    -- Print recall report
    IF total_queries > 0 THEN
        RAISE NOTICE '------------------------------------------------------------------------';
        RAISE NOTICE 'Recall Evaluation Finished.';
        RAISE NOTICE 'Total queries evaluated: %', total_queries;
        RAISE NOTICE 'Average Recall@%: %%%', k_param, round(((sum_recall / total_queries) * 100.0)::numeric, 2);
        RAISE NOTICE 'Minimum Recall@%: %%%', k_param, round((min_recall * 100.0)::numeric, 2);
        RAISE NOTICE 'Maximum Recall@%: %%%', k_param, round((max_recall * 100.0)::numeric, 2);
        RAISE NOTICE '------------------------------------------------------------------------';
    ELSE
        RAISE WARNING 'No recall queries were successfully evaluated.';
    END IF;
END;
$$;

Migrating to Antigravity CLI from Gemini CLI with MCP for Google Cloud Databases

Gleb Otochkin — Tue, 30 Jun 2026 05:31:23 +0000

Time to Move

If you are following Google news closely you are probably aware about Antigravity CLI announced on the Google I/O 2026 and gradual move to the new tools ecosystem from the previous generation of AI assistants. In short, it’s time to move from Gemini CLI to Antigravity CLI. In this article I’ll explain how to set up your essential MCP servers for Google Cloud in the Antigravity CLI.

Gemini CLI MCP Configuration

Everybody has their own set of extensions and tools for Gemini CLI. In my case, I work extensively with Google Cloud databases and, as such, I have a few “must-have” extensions for Gemini CLI.

Here is my minimum Gemini CLI configuration for MCP servers.

gleb@db-connect:~$ gemini extension list
Ignore file not found: /home/gleb/.geminiignore, continue without it.
✓ alloydb (1.0.0)
 ID: dffab4e5e5d86ea81431cad2bf77fc027c0042d54fc4414c67145bfa255ee6cf
 name: dffab4e5e5d86ea81431cad2bf77fc027c0042d54fc4414c67145bfa255ee6cf
 Path: /home/gleb/.gemini/extensions/alloydb
 Enabled (User): true
 Enabled (Workspace): true
 MCP servers:
  AlloyDB MCP Server

✓ cloud-sql (1.0.0)
 ID: fe4d062a1349d2dddc18bd27899586f9c5a6ea524a95c44ec45257cbd18e9ed9
 name: fe4d062a1349d2dddc18bd27899586f9c5a6ea524a95c44ec45257cbd18e9ed9
 Path: /home/gleb/.gemini/extensions/cloud-sql
 Enabled (User): true
 Enabled (Workspace): true
 MCP servers:
  Cloud SQL MCP Server

✓ developer-knowledge (0.1.0)
 ID: 7b4042e11865c674ad1b7007c3040ba4a2a277c16c8d9bda9ba10ac7a70d2eec
 name: 47ba5f7123468475fe1edc973bf455cabe8eb51061f1e892d0bb9e1867db98b9
 Path: /home/gleb/.gemini/extensions/developer-knowledge
 Source: https://github.com/gemini-cli-extensions/developer-knowledge (Type: git)
 Enabled (User): true
 Enabled (Workspace): true
 Context files:
  /home/gleb/.gemini/extensions/developer-knowledge/GEMINI.md
 MCP servers:
  developer-knowledge

✓ spanner (1.0.0)
 ID: eb1ea9d72fe0c79269a8dc2b047ddfb531b004ed19afb45eaef9e9a38cccf351
 name: eb1ea9d72fe0c79269a8dc2b047ddfb531b004ed19afb45eaef9e9a38cccf351
 Path: /home/gleb/.gemini/extensions/spanner
 Enabled (User): true
 Enabled (Workspace): true
 MCP servers:
  Spanner MCP Server

There are three extensions for different Google Cloud databases and the Developer Knowledge MCP. For some projects I have additional MCP servers depending on the nature of the project, but for this tutorial, we will limit ourselves to just those four extensions. Our goal is to move everything to Antigravity CLI and get them working.

Install Antigravity CLI

The full instructions on how to install Antigravity CLI are in the documentation, but here is a short version. I am showing this on a Debian Linux box ,but the steps are generally the same for a Mac installation and migration.

Run the installation script in a terminal window on your Mac or Linux machine.

curl -fsSL https://antigravity.google/cli/install.sh | bash

At the end of the installation, it should show something like this:

On Linux, it automatically adds an alias for the binary, so you can start the tool using the agy alias. On the first launch, it will ask you to authenticate either with your Google account or by using a specific project.

And if you’ve chosen a project path then after authentication it will ask to put the project ID:

Next, it will ask about your location and then prompt you to choose a color scheme. On the color scheme screen, there’s a checkbox at the bottom that allows you to import all your existing Gemini CLI extensions into Antigravity CLI.

And as you can see, all my existing extensions are listed there. Can we wrap up here and call it a day? We could, but what if we missed the checkbox and clicked ‘Next’ without importing our extensions? As a result, we wouldn’t have any of our old MCPs in our Antigravity CLI.

Is it possible to migrate them afterward? Yes, of course, and in the next section, we’ll do exactly that.

Migrating Extensions to Antigravity Plugins

The full guide for migrating Gemini CLI extensions is in the documentation and you can take a look there if something isn’t working or if you want the latest information and updates. Here is my short version, along with a few fixes I had to apply.

First, exit Antigravity and run the following command in the terminal:

agy plugin import gemini

The command should find all existing extensions, skills, hooks, and MCP servers and convert them into Antigravity plugins. You should see something like this:

Now you can start the Antigravity CLI and check your MCP servers using the /mcp command. All of your MCP servers should be visible in the output.

At first glance, it looks like everything is fine. But you’ll notice that only the Developer Knowledge MCP shows its tools, while none of the database plugins show any tools at all. If that happens, you might need to fix your configuration.

Have a look into the tools definitions in the ~/.gemini/antigravity-cli/mcp directory — the tools don’t have parameters.

gleb@db-connect:~$ cat .gemini/antigravity-cli/mcp/AlloyDB\ MCP\ Server/list_clusters.json | jq
{
  "name": "list_clusters",
  "description": "List all clusters",
  "parameters": null
}

Also, if we check the Antigravity CLI log at ~/.gemini/antigravity-cli/cli.log, we can see that the database MCP servers are not authenticating correctly.

W0618 18:12:18.733858 86343 mcp_auth.go:634] OAuth setup failed for Spanner MCP Server: OAuth client ID required: server does not support dynamic client registration
W0618 18:12:18.734186 86343 mcp_auth.go:634] OAuth setup failed for AlloyDB MCP Server: OAuth client ID required: server does not support dynamic client registration
W0618 18:12:18.740350 86343 mcp_auth.go:634] OAuth setup failed for Cloud SQL MCP Server: OAuth client ID required: server does not support dynamic client registration
W0618 18:12:18.917512 86343 mcp_auth.go:634] OAuth setup failed for developer-knowledge: OAuth client ID required: server does not support dynamic client registration

And when we look into the plugin config for the imported AlloyDB MCP extension, we can see it doesn’t have an authentication section. Also, the plugin’s name consists of several words separated by spaces.

gleb@db-connect:~$ cat .gemini/config/plugins/alloydb/mcp_config.json 
{
  "mcpServers": {
    "AlloyDB MCP Server": {
      "command": "",
      "args": null,
      "cwd": "",
      "env": null,
      "serverUrl": "https://alloydb.googleapis.com/mcp"
    }
  }
}

The authProviderType parameter is missing, and the name for the MCP server should be fixed. This is likely why it isn’t working correctly. Let’s rename the MCP server and add the missing parameter. Here is the fixed configuration for the AlloyDB MCP plugin:

{
  "mcpServers": {
    "AlloyDB": {
      "command": "",
      "args": null,
      "cwd": "",
      "env": null,
      "serverUrl": "https://alloydb.googleapis.com/mcp",
      "authProviderType": "google_credentials"
    }
  }
}

Now, if we start the Antigravity CLI again and run the /mcp command, we can see all the tools associated with the remote AlloyDB MCP.

And when we test the MCP, we can confirm that it works correctly now.

We now have a new subdirectory for the AlloyDB MCP server, and the tools have all the necessary parameters and required information there.

gleb@db-connect:~$ cat .gemini/antigravity-cli/mcp/AlloyDB/list_clusters.json | jq
{
  "name": "list_clusters",
  "description": "List all clusters",
  "parameters": {
    "description": "Message for requesting list of Clusters",
    "properties": {
      "filter": {
        "description": "Optional. Filtering results",
        "type": "string"
      },
      "orderBy": {
        "description": "Optional. Hint for how to order the results",
        "type": "string"
      },
      "pageSize": {
        "description": "Optional. Requested page size. Server may return fewer items than requested. If unspecified, server will pick an appropriate default.",
        "format": "int32",
        "type": "integer"
      },
      "pageToken": {
        "description": "A token identifying a page of results the server should return.",
        "type": "string"
      },
      "parent": {
        "description": "Required. The name of the parent resource. For the required format, see the comment on the Cluster.name field. Additionally, you can perform an aggregated list operation by specifying a value with the following format: * projects/{project}/locations/-",
        "type": "string"
      }
    },
    "required": [
      "parent"
    ],
    "type": "object"
  }
}

The old subdirectory with the incomplete tools can now be removed from the MCP directory.

gleb@db-connect:~$ rm -rf .gemini/antigravity-cli/mcp/AlloyDB\ MCP\ Server/

We can fix the rest of our MCP servers for Cloud SQL and Spanner in the same way. For the Developer Knowledge MCP, however, we only need to add the authProviderType, its name didn’t have any spaces and was processed correctly.

In the end, you should see something like this when you list your MCP servers in the Antigravity CLI:

And a couple of words about the Developer Knowledge MCP. I think it’s one of the “must-have” MCPs in any configuration. It provides up-to-date information directly from the Google documentation and is one of the tools I use every single day.

You can also test the Developer Knowledge MCP by asking it to prepare a tutorial on a specific product option. For example, you can ask: ‘Prepare a tutorial with options on how I can connect to an AlloyDB instance.’ The Developer Knowledge MCP will use the search_documents tool to find the information and then create the tutorial based on the latest version of the Google documentation.

Try it and let us know

I recommend Antigravity CLI to everyone who likes command-line tools and is more comfortable with a terminal than a GUI. But keep in mind that Antigravity also has a GUI application and IDE, so you have a choice.

If you need to customize your Antigravity setup, it supports skills, plugins, rules, hooks, and MCP servers.

Happy migration, and let us know how it works for you!

Gemini 3.5 Flash in Google Cloud Databases

Gleb Otochkin — Sat, 23 May 2026 09:41:05 +0000

I hope you’ve been able to attend or at least watch the IO 26 keynote. Among other exciting announcements Google introduced the new Gemini 3.5 Flash model, and I immediately went to test it in Cloud SQL and AlloyDB. It worked really well, returning faster and more accurate responses in my particular use case compared to Gemini 2.5 Flash or even Gemini 3 Flash Preview. I encourage you to try it out for yourself and test it with your own workloads. Let me show you how I tested it.

Cloud SQL

Cloud SQL has full AI integration via the google_ml extension and can call models directly from a SQL query. It has several preregistered models, but Gemini 3.5 Flash was not there yet when I tested it. To work with the model, I registered it using the following call. Replace the PROJECT_ID placeholder in the code by your Google project id.

CALL google_ml.create_model(
    model_id => 'gemini-3.5-flash',
    model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.5-flash:generateContent',
    model_provider => 'google',
    model_type => 'generic',
    model_auth_type => 'cloudsql_service_agent_iam'
);

If you look at the model_request_url parameter, you’ll notice I’ve put the full URL path there with the global location. And I have a reason for that. Even if Cloud SQL and AlloyDB allow you to use a short variant for the URL, it might not work with the new models. If you try to use something like publishers/google/models/gemini-3.5-flash:generateContent, you will most likely get an error, since by default it tries to use a regional path — which is not available for Gemini 3.5 models.

After the registration, the model can be used in a query or in a function. I tested it with the following query:

SELECT google_ml.predict_row(
    model_id => 'gemini-3.5-flash',
    request_body => json_build_object(
        'contents', json_build_array(
            json_build_object(
                'role', 'user',
                'parts', json_build_array(
                    json_build_object('text', 'Explain MCP server for a relational database in 50 words or less.')
                )
            )
        )
    )
) ->'candidates' -> 0 -> 'content' -> 'parts' -> 0 -> 'text' AS ai_response;

It took about 4.1 seconds to get the response from the Gemini 3.5 Flash model. Then I registered the Gemini 2.5 Flash and Gemini 3 Flash Preview models and repeated the same query using their endpoints. For Gemini 2.5 Flash, it took 5.3 seconds on average, and 12.3 seconds for Gemini 3 Flash Preview. I expected version 3.5 to be faster than the preview version, but it was even faster than version 2.5.

Then we have to look into the quality of the response. Gemini 3.5 Flash provided an answer that aligns better with how the term “MCP” is used today when compared to the response from Gemini 2.5 Flash.

Here is an example of the response from Gemini 2.5 Flash:

“MCP server isn’t a standard term in relational databases. It *could* refer to a Master Control Program in legacy systems, acting as a central process coordinating database operations. It’s not a common component in modern database architectures.”

It looks like Gemini 2.5 Flash talks about an OS for mainframes. I am not sure how relevant that is for a general audience. Now, let’s compare it with the response from Gemini 3.5 Flash.

“An MCP (Model Context Protocol) server is a secure bridge connecting AI models to a relational database. It translates the AI’s natural language requests into SQL commands, allowing the model to safely inspect schemas, query data, and perform database operations in real-time.”

That makes more sense and especially now when everybody uses AI and agents.

Here is the summary table for the models performance:

Model Version | Response Time | Response Quality
-----------------------|---------------|------------------
Gemini 3.5 Flash | 4.1 seconds | Excellent
Gemini 2.5 Flash | 5.3 seconds | Average
Gemini 3 Flash Preview | 12.3 seconds | Good

After the tests with Cloud SQL, I moved to my AlloyDB cluster to see how it worked there.

AlloyDB

I used a very similar procedure to register the new model in the AlloyDB database, with the only difference being the model_type parameter. In AlloyDB, we can register it with the model type “llm”. Again — don’t forget to replace the PROJECT_ID placeholder by your project id.

CALL google_ml.create_model(
model_id => 'gemini-3.5-flash',
model_request_url => 'https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3.5-flash:generateContent',
model_provider => 'google',
model_type => 'llm'
);

To test the model in AlloyDB, I used a different function — ai.generate. The ai.generate function is much more convenient to use — you don’t need to prepare your JSON input, and it returns the output as plain text right out of the box.

SELECT ai.generate(
    'Explain MCP server for a relational database in 50 words or less.',
    model_id => 'gemini-3.5-flash'
) AS ai_response;

In AlloyDB, the response from Gemini 3.5 Flash took the same 4.2 seconds on average, with the same high-quality results.

The Gemini 2.5 Flash model was already there out of the box, and I registered Gemini 3 Flash Preview using the same approach as above. After testing the query using ai.generate, I got 5.4 seconds for Gemini 2.5 Flash and 13.1 seconds on average for Gemini 3 Flash Preview. The quality of the response was the same as in my Cloud SQL tests. And the ai.generate on AlloyDB was performing with the same speed as google_ml.predict_row on Cloud SQL. I tested it with both regional and global endpoints getting the same performance.

The new model is more expensive but considering speed and quality of the result it might pay off especially for agentic workload where low quality response can lead to more turns for an agent and more consumption in the end.

Summary

My experience so far with the new Gemini 3.5 Flash model is faster and better responses over the previous Gemini 2.5 Flash model, and it is much faster than the Gemini 3 Flash Preview we have been testing previously. I think it is the new ‘workhorse’ for most of my workloads. Try it out for yourself, and read about the new Gemini model and all the other I/O ’26 announcements in the Google blog.

Demystifying max connections limit in Cloud SQL for PostgreSQL

Gleb Otochkin — Thu, 30 Apr 2026 22:43:25 +0000

Introduction

If you’ve worked with Google Cloud SQL for PostgreSQL, you’re likely aware that it sets a maximum number of connections for your instance at creation. This number isn’t static. It depends on the size — or rather, the machine type of the instance. For example, if you choose the db-f1-micro (the smallest available tier), you are capped at 25 connections by default.

And it might happen that you’ve already experienced an early wakeup call when your application suddenly ran out of connection and triggered an error like C: 53300: remaining connection slots are reserved…. That means you have run out of connections.

Let’s discuss connections, database parameters, and the logic behind these limits. My goal is to clear up some of the common questions I hear in the community and in private conversations with developers. I’ve structured this post as a Q&A to address the most frequently asked questions.

Max Connections in Cloud SQL for Postgres

Is the connection number a hard limit?

In chats and conversations with developers, I often hear about how people try to solve the problem when they hit the max number of connections. Quite often, there is a general assumption that 25 connections for a db-f1-micro is a hard limit, and the only ways to tackle it are to change the shape of the instance or implement connection pooling.

I don’t have anything against both approaches — they might be fully justified. But it is not a hard limit, and you can change it by updating the max_connections database flag using either the Google Cloud Console or the gcloud SDK. Keep in mind that the change requires a short downtime to apply. Read more in the documentation.

Will it automatically change if the instance is resized?

If you change your Cloud SQL instance size and haven’t manually set the max_connections database flag, the value will change automatically according to your instance size. For example, if you have a db-f1-micro instance with 25 connections and increase the size to db-g1-small, your max_connections will increase to 50.

What if I set up a custom max_connections flag?

If you define your preferred max_connections as a database flag, it will stay the same even through instance reconfigurations like changing the size. This can play a trick on someone who upgrades the instance size in anticipation of a higher max_connections value, only to find that it hasn’t changed.

What are the factors impacting the max_connections?

The main factor is the instance memory. In PostgreSQL, you have shared memory for the data pages you work with, and each session also has its own individual memory. This is a bit of a simplified description, but it is probably enough to explain the memory impact. Let’s go down to the memory allocations.

You need shared buffers to work with your data — each page of data from a table or index is copied to this area so it can be accessed by your session. The more data you have and the more you need to work with, the more shared buffers you’ll need for better performance. Otherwise, you will be constantly moving data to and from the disk.

When you connect to the instance, your session allocates roughly 2 MB of memory. Then, as you work with data, the memory allocation depends on your operations and the value of the work_mem parameter. By default, it is 4 MB. If you have a sorting or hashing operation in your query (like an ORDER BY), your session will allocate those 4 MB for that operation. However, that is per operation — a single query could potentially run multiple sorting or hashing tasks, multiplying the memory allocation.

Additionally, background processes like vacuuming and logical replication require memory too.

I will live out some other details like temporary buffers and others, operation system processes and caches. You can check all that in the PostgreSQL documentation. But considering all of this, the 25 max_connections limit for a db-f1-micro instance with only 600 MB of memory doesn’t look so small anymore.

And if we look at a graph of default max_connections values relative to memory, we can see it isn’t linear. Different factors have different impacts as the instance grows in size and the number of potential connections increases.

Can I set up max_connections to a higher or lower value?

Yes, you can, but you must consider all factors regarding potential memory allocation. If you run out of available memory, your connection will be terminated by an Out of Memory (OOM) error. Additionally, keep in mind that once you set max_connections as a database flag, it will no longer change dynamically based on the instance size; you will need to update the value manually if you decide to resize the instance.

How can I handle thousands of connections?

If your application requires hundreds or thousands of concurrent connections, the best approach is not to increase the max_connections flag.

Instead, I recommend using connection pooling. Cloud SQL offers Managed Connection Pooling (MCP), which is available in the Enterprise Plus edition. Alternatively, you can set up your own using tools like PgBouncer. Connection pooling solutions act as a proxy between your application and the database. They allow thousands of lightweight client connections- from sources like serverless scripts — to share a small, fixed number of heavy backend server connections. Read more about managed connection pooling in documentation.

Summary

The default max_connections value is tied to the instance memory and adjusts automatically as you resize the instance — unless you have manually defined it as a database flag.

It’s important to remember that the default value is based on best practices and serves as guidance, not a hard limit. However, if you choose to set a custom value, you are responsible for managing it. You’ll need to remember to adjust it manually if you ever resize your instance.

If your application is designed to use a high volume of connections, your best bet might be to implement a connection pooling solution.

Setting up an AlloyDB Instance with a Public IP in Minutes

Gleb Otochkin — Tue, 14 Apr 2026 18:35:08 +0000

Introduction

Sometimes when you’re building a proof of concept or a quick demo, you just need a simple database backend with a public IP address. What if I told you that you can create an AlloyDB instance with a public IP without having to dive into private IP network configuration? Let me show you how to do exactly that.

Using gcloud CLI

AlloyDB is an enterprise-grade, fully PostgreSQL-compatible database with tons of unique features, making it a Swiss Army knife of a data backend for any kind of application. As such, it comes with only a private IP by default. This makes it more secure, but at the same time, requires additional actions at the network level. However, as I mentioned in the intro, sometimes you just want something quick and dirty to verify functionality or run a demo. So, how do you create an AlloyDB instance with a public IP enabled in a few quick steps?

I am going to use a command-line approach and show you how to create the smallest possible AlloyDB instance with a public IP without configuring a private network. I am using a brand-new project with a default network.

If this is a brand-new project, we still need to enable the minimum required APIs. Run the following command in Google Cloud Shell or from your Mac terminal. I am assuming you already have all the required privileges in the project.

gcloud services enable alloydb.googleapis.com \
                       compute.googleapis.com \
                       servicenetworking.googleapis.com

And then run the command to create your AlloyDB cluster.

export PGPASSWORD="MyVeryStrictPassword123+"
echo $PGPASSWORD
export REGION=us-central1
export ADBCLUSTER=alloydb-aip-01
gcloud alloydb clusters create $ADBCLUSTER \
    --region=$REGION \
    --password=$PASSWORD \
    --enable-private-service-connect

Did you notice the --enable-private-service-connect parameter? This creates a Private Service Connect (PSC) enabled AlloyDB cluster. Once the cluster is created, run the following command to create the primary instance. For my tests, when they don’t require a large cache or heavy CPU power, I usually opt for the C4A machine type with a single CPU — it is enough to demonstrate functionality and costs less than other configurations.

gcloud alloydb instances create $ADBCLUSTER-pr \
    --instance-type=PRIMARY \
    --machine-type=c4a-highmem-1 \
    --cpu-count=1 \
    --availability-type=ZONAL \
    --database-flags=password.enforce_complexity=on \
    --assign-inbound-public-ip=ASSIGN_IPV4 \
    --region=$REGION \
    --cluster=$ADBCLUSTER

And that’s it — after 5–8 minutes, you will have an AlloyDB primary instance running with only a public IP enabled. From there, you can use gcloud to connect to the instance, create a database, and run your queries. For more details on that step, take a look at one of my previous posts where I explain how to use gcloud to connect to AlloyDB.

gcloud beta alloydb connect $ADBCLUSTER-pr --cluster=$ADBCLUSTER --region=$REGION --public-ip

Using Terraform

Of course, you can also use Terraform Google provider to automate this process. Below is the alloydb-poc.tf configuration file:

terraform {
  required_providers {
    google = {
      source = "hashicorp/google"
      version = ">= 7.0.0"
    }
  }
}
provider "google" {
  project = var.project_id
  region = var.region
}
resource "google_alloydb_cluster" "default" {
  cluster_id = var.cluster_id
  location = var.region
  psc_config {
    psc_enabled = true
  }
  initial_user {
    user = "postgres"
    password = var.db_password
  }
}
resource "google_alloydb_instance" "primary" {
  cluster = google_alloydb_cluster.default.name
  instance_id = var.instance_id
  instance_type = "PRIMARY"
  availability_type = "ZONAL"
  machine_config {
    machine_type="c4a-highmem-1"
    cpu_count = 1
  }
  database_flags = {
    "password.enforce_complexity" = "on"
  }
  network_config {
    enable_public_ip = true
  }
}
variable "project_id" {
  description = "The GCP Project ID"
  type = string
}
variable "region" {
  description = "The GCP Region (e.g., us-central1)"
  type = string
  default = "us-central1"
}
variable "cluster_id" {
  description = "The name of the AlloyDB cluster"
  type = string
  default = "alloydb-aip-01"
}
variable "instance_id" {
  description = "The name of the primary instance"
  type = string
  default = "alloydb-aip-01-pr"
}
variable "db_password" {
  description = "Password for the default postgres user (must meet complexity requirements!)"
  type = string
  sensitive = true
}
output "alloydb_public_ip" {
  description = "The Public IP address assigned to the AlloyDB primary instance"
  value = google_alloydb_instance.primary.public_ip_address
}

Run your Terraform deployment using the commands below, but remember to replace the YOUR_PROJECT_ID placeholder with your actual Google Cloud project ID and put your own password:

terraform init
terraform apply \
  -var="project_id=YOUR_PROJECT_ID" \
  -var="db_password=MyVeryStrictPassword123+"

In just a few minutes, you’ll have an AlloyDB instance up and running with a public IP.

I hope this makes your life easier and serves as a handy ‘hack’ for your daily workflow, whether you’re building a quick proof of concept or jumping into a vibe coding session. By the way, you can use this method for some codelabs like this one when you prefer to use your local machine instead of the Google Cloud shell.

AlloyDB easy connection using gcloud

Gleb Otochkin — Sat, 11 Apr 2026 05:17:47 +0000

Introduction

For those who work with Google AlloyDB for PostgreSQL on a daily basis, connectivity is likely not an issue. As a developer or administrator, you probably already have a preferred set of tools and connection methods, or you use AlloyDB Studio to run quick queries.

However, when starting a new project or cluster — or if you are new to AlloyDB and looking for a straightforward way to connect via the command line with proper mTLS encryption — there is a new method available. You can now connect to an AlloyDB instance using the Google Cloud SDK (gcloud). Let me introduce you to how it works.

Setting up

The gcloud CLI is available out of the box on Google Cloud Shell and on Google Cloud Compute Engine VMs created with Google-provided templates. But, if you are running it from your laptop or a custom VM, you may need to install it by following the official documentation.

You also need two additional components to get everything working: a PostgreSQL client and the AlloyDB Auth Proxy. If you are using Google Cloud Shell, both components come preinstalled. However, if you are using your own laptop or VM, you will need to add them manually.

Let’s assume you’ve already installed the gcloud CLI. But before moving forward check the version using the versioncommand:

gcloud --version

It should return version 563 or higher.

my-mac:~ $ gcloud --version
Google Cloud SDK 564.0.0
...

If the version is lower then you need to update it:

gcloud components update

The next step is to get the PostgreSQL client software. The latest PostgreSQL client software can be downloaded from the official website, or installed via a package manager for Linux or the brew utility for macOS. Detailed instructions can be found in our documentation. Here is how to install version 18 using the Homebrew utility on a Mac:

brew install postgresql@18

After installing you can verify it by checking the version:

psql --version

The final component is the AlloyDB Auth Proxy. The gcloud CLI uses this proxy to create an mTLS-encrypted connection and establish a link to the instance. On a Mac, run the following commands:

URL="https://storage.googleapis.com/alloydb-auth-proxy/v1.14.2"
curl -o alloydb-auth-proxy "$URL/alloydb-auth-proxy.darwin.arm64"
chmod +x alloydb-auth-proxy
mkdir $HOME/bin
mv alloydb-auth-proxy $HOME/bin/
export PATH="$HOME/bin:$PATH"

See the AlloyDB Auth proxy documentation on how to install it for other platforms. By the way if your gcloud CLI cannot find the AlloyDB Auth Proxy in your system’s PATH, it will automatically provide instructions on how to install it.

Once all components are installed, you can run the gcloud beta alloydb instances connect command to access your database via the mTLS encryption provided by the proxy. Additionally, if you are connecting using an AlloyDB public IP, you do not need to add your personal public IP to the authorized networks; the proxy handles this automatically. Here is how I connect to my AlloyDB instance while testing a codelab:

REGION=us-central1
CLUSTER_NAME=alloydb-aip-01
INSTANCE_NAME=alloydb-aip-01-pr
gcloud beta alloydb connect $INSTANCE_NAME --cluster=$CLUSTER_NAME --region=$REGION --public-ip

Then you type your password for the user postgres and you are in.

my-mac:~ $ REGION=us-central1
my-mac:~ $ CLUSTER_NAME=alloydb-aip-01
my-mac:~ $ INSTANCE_NAME=alloydb-aip-01-pr
my-mac:~ $ gcloud beta alloydb connect $INSTANCE_NAME --cluster=$CLUSTER_NAME --region=$REGION --public-ip
Starting the AlloyDB Auth Proxy...
Running command:
 alloydb-auth-proxy projects/gleb-genai-002/locations/us-central1/clusters/alloydb-aip-01/instances/alloydb-aip-01-pr --port 9471 --public-ip

Connecting to the AlloyDB Auth Proxy...
Running command:
 psql -h 127.0.0.1 -p 9471 -U postgres -d postgres
Password for user postgres:
psql (18.0 (Postgres.app), server 16.11)
Type "help" for help.

postgres=>

A few final notes: You must be authenticated with an account that has the proper permissions to connect to AlloyDB instances, specifically the roles/alloydb.cloent and roless/serviceUsageConsumer roles. Additionally, if you want to use IAM authentication, ensure it is enabled on your AlloyDB cluster, that you have the roles/alloydb.databaseUser role, and that you have created the IAM user within the cluster itself.

As of the time of writing, this feature is still in Preview. And once more — you may need to update your Google Cloud SDK to version 563.0.0 or higher. For more details on available flags and configurations, refer to the official documentation regarding connecting via the gcloud CLI.

Happy testing! You can try it with some of our latest AlloyDB codelabs.