How PostgreSQL 14.9 Handles Partitioned Index Validation
In PostgreSQL, a partitioned table stores its rows in separate tables called partitions. A partitioned index is a parent index that represents…
When you add semantic search to a Cloud Spanner database, you need to turn existing text records into numbers that a search query can compare. A machine-learning model creates an embedding, a vector (an ordered list of numbers) that represents the text’s meaning. Spanner stores each vector so vector-search functions can find records with similar meaning.
For a large backfill, use Spanner’s integration with a model hosted on Gemini Enterprise Agent Platform, then store the returned vectors with partitioned DML. Partitioned DML divides a database update into pieces that Spanner can run across partitions. Because you are calling an external model, run the update in partitioned-DML mode, account for model quotas and text limits, and check which rows received vectors. Google’s bulk embedding procedure shows the core pattern.
First choose the exact text to embed. The documented workflow accepts text stored as STRING or JSON. If a row contains JSON, select or construct the text you want the model to represent rather than sending an entire JSON object without considering its contents.
In GoogleSQL, register a model reference so Spanner knows which Agent Platform endpoint to call for embeddings. The endpoint calculates the embedding, and Spanner stores the result. Use the location and model version that match your deployment.
CREATE MODEL embedding_model
INPUT (
content STRING(MAX)
)
OUTPUT (
embeddings STRUCT<values ARRAY>
)
REMOTE OPTIONS (
endpoint =
'//aiplatform.googleapis.com/projects/PROJECT_ID/locations/LOCATION/publishers/google/models/TEXT_EMBEDDING_MODEL@VERSION',
default_batch_size = 5
);
Replace the project, location, model name, and version placeholders with your endpoint’s values. The default_batch_size value comes from Google’s example; it is not a setting for every model. Check how many values the selected model returns before creating a fixed-length vector column or vector index. The embedding model documentation lists each model’s dimensions and limits.
Add a column to store the vectors. A second column can record the embedding model version used for each row.
ALTER TABLE Products
ADD COLUMN desc_embed ARRAY;
ALTER TABLE Products
ADD COLUMN desc_embed_model_version INT64;
The example uses an ARRAY<FLOAT32> column, which does not set a fixed length for the array. If you plan to create a vector index, check that the model returns the number of values the index requires. A later index and query use the vectors stored by the backfill to search for similar records. See the Spanner vector-search overview.
Run a prediction on a few rows before updating the full table. This checks that the model reference works and that the returned fields and data types match what your SQL expects.
SELECT embeddings.values
FROM SAFE.ML.PREDICT(
MODEL embedding_model,
(SELECT description AS content FROM Products LIMIT 10)
);
SAFE.ML.PREDICT returns NULL for a failed prediction and lets the query continue. This helps a large update keep running, but a successful DML statement does not prove that every row received an embedding. Check for missing vectors after the backfill.
Use a WHERE condition to select only rows without vectors. If the update is interrupted, run it again to target the rows that remain unfilled.
UPDATE Products
SET
desc_embed = (
SELECT embeddings.values
FROM SAFE.ML.PREDICT(
MODEL embedding_model,
(SELECT Products.description AS content)
) @{remote_udf_max_rows_per_rpc=200}
),
desc_embed_model_version = 3
WHERE desc_embed IS NULL;
The value 200 is the remote-call setting in Google’s example. Treat it as a starting point for testing, not a recommendation for every model or text size. The value 3 is an example version marker; store a value that identifies the model version you actually used.
Run the statement with a method that executes partitioned DML. For example, a Java client can submit it with executePartitionedUpdate(). A regular DML call uses a different mode. Pasting the SQL into Spanner Studio does not run it as partitioned DML. Google’s partitioned DML documentation explains the execution modes and how they behave.
Partitioned DML commits work per partition, not as one atomic update across the whole table. If you cancel the statement or it fails, Spanner keeps changes from completed partitions. Spanner can retry work, so write the update to be safe to run again. The WHERE desc_embed IS NULL condition prevents the update from recomputing rows that already have vectors. Model output can change between versions, so record the model version for each vector and run a planned backfill when you migrate models.
For PostgreSQL-dialect databases, the model call uses spanner.ML_PREDICT_ROW and supplies the endpoint in the function call. The returned embedding values also need conversion to the Spanner array type. Use the dialect-specific statement in Google’s backfill instructions; the GoogleSQL expression above is not portable to PostgreSQL.
Remote-call settings control how many rows Spanner sends to the model in each request. Partitioned-DML parallelism controls how much database work Spanner runs at once. Increasing either setting can put more load on the model endpoint. Increasing parallelism also uses more Spanner CPU and memory.
Start with a representative sample of your text. Adjust the request size and parallelism while watching for endpoint errors and database load. The remote_udf_max_rows_per_rpc=200 setting in the example does not guarantee that every request will fit. Longer text uses more tokens, and limits vary by model version. Google’s separate embedding API documentation lists limits for that API. Those figures do not apply universally to every Spanner integration and model combination. If a request fails with INVALID_ARGUMENT or a request-too-large error, reduce the batch size and check the selected model’s limits.
Partitioned DML can send enough requests at once to hit Agent Platform quotas. According to the backfill documentation, Spanner retries throttled requests up to 20 times in partitioned-DML transaction mode. If quota errors continue, check the endpoint’s quota and reduce parallelism or request a quota change. Google also recommends using a separate endpoint project for backfills when you need to protect production endpoint capacity.
When possible, schedule the update during a lower-write period. Concurrent writes can compete with the backfill and affect database latency. No documented throughput or completion-time guarantee applies to a given row count. Benchmark with representative records and monitor both the endpoint and Spanner load.
After the update, count rows that still lack embeddings. A nonzero result identifies rows that need investigation or another pass.
SELECT COUNT(*)
FROM Products
WHERE desc_embed IS NULL;
Before retrying, review the text in those rows and any model errors. A prediction that returns NULL leaves a row without a vector. This can happen when the input exceeds a model limit or the endpoint rejects the request. If the source text has changed, check whether the stored vector still represents the current text before treating the row as complete.
Use Spanner System Insights to track endpoint requests, latency, and network bytes. The SPANNER_SYS.ACTIVE_PARTITIONED_DMLS table reports partition counts, progress, and rows processed. Spanner updates these counters at partition boundaries, so the progress value can stay unchanged while the statement continues running. A large backfill can exceed Spanner’s default one-hour gRPC timeout. Configure a longer timeout if the backfill is expected to take longer.
Track which model version produced each embedding. A model update does not refresh vectors already stored in Spanner. Record the version used for each row and plan a new backfill when you change models. If the new model returns a different number of values, store its vectors in a new column. After the backfill, switch readers to that column instead of overwriting a column whose vector length no longer matches what they expect.
Spanner does not charge an additional fee for the integration itself. Agent Platform online-prediction and inter-service data-transfer charges may still apply. Placing the endpoint near the Spanner instance can reduce transfer charges. The backfill also depends on endpoint availability and quota. The Spanner integration overview explains these dependencies.
Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.
Take a look at vroni.com