Database-managed embedding refresh moves vector generation from an external ETL script into the database boundary. Instead of exporting changed rows, calling an embedding model from application code, and writing vectors back through a separate pipeline, the database stores the source text, embedding column, model configuration, progress state, and refresh policy together. The result is a vector-search table whose embeddings are treated as maintained derived data, much like a materialized view or generated index.
This pattern addresses one of the most common failure modes in RAG and semantic-search systems: stale vectors. A product description, support article, or user review changes in the operational database, but the vector index still reflects the old text because the embedding job was missed, delayed, or failed halfway through. When the database owns the refresh plan, the system can initialize embeddings for all existing rows, refresh changed rows incrementally, monitor progress in metadata tables, and choose whether updates should happen transactionally or through deferred batch jobs.
The source article uses AlloyDB AI as the concrete implementation: SQL procedures initialize embeddings for a table column, maintain an embedding column, support transactional and manual incremental refresh modes, and expose faster batch processing for high-volume refreshes. The transferable idea is broader than one product. Any SQL database with vector columns, model invocation functions, triggers, job scheduling, and progress metadata can implement the same control plane.
The engineering decision is not simply “generate embeddings in the database.” It is deciding where consistency, latency, throughput, retries, and observability should live. Transactional refresh gives the freshest vectors but adds write-path latency and model-service dependency to commits. Manual or scheduled refresh protects OLTP writes but introduces a staleness window. Bulk and cursor-style processing improve throughput for large tables but require progress tracking, quotas, and restart-safe batches.
An embedding column is not primary user data. It is a derived representation of a source column under a specific model, model version, dimensionality, and preprocessing policy. Treating it as maintained derived data changes the operational model: the database must know which source column feeds which embedding column, which model generates the vector, what dimension the vector must have, and whether each row is current.
This is similar to maintaining a secondary index. The user updates the source field; the system decides when and how to update the derived structure. The key difference is that the refresh operation calls an external or embedded ML model, so failures include quota exhaustion, model timeouts, schema mismatches, token limits, and model version drift.
Transactional refresh updates the embedding as part of the write that changed the source content. This provides strong freshness: once the transaction commits, semantic search can use a vector generated from the committed text. It is best suited for low-frequency writes, high-value records, or workflows where stale search results are unacceptable.
Deferred refresh records the need for an embedding update but performs the model call later. This can be implemented with an explicit refresh procedure, a scheduled job, a trigger plus queue table, or a background worker. Deferred refresh is usually the better default for write-heavy tables because embedding calls are slow, rate-limited, and operationally less predictable than ordinary database writes.
Embedding maintenance has three different workloads. Initialization generates vectors for an existing table. Incremental maintenance keeps new or changed rows current. Re-embedding regenerates vectors when the model, dimensionality, preprocessing, or chunking policy changes. These workloads should not share the same operational assumptions.
Initialization and re-embedding are bulk jobs. They need batching, progress tables, retry state, and throttle controls. Incremental maintenance is latency-sensitive and should minimize the impact on foreground writes. A mature system exposes both paths explicitly instead of hiding all embedding work behind one trigger.
The refresh mode tells downstream search consumers what they can assume. Transactional mode means vector freshness follows row freshness. Manual or scheduled mode means consumers must tolerate a bounded staleness window and should expose refresh lag as an operational metric.
The database needs an embedding column with a fixed dimensionality and a configuration record that maps source data to the model. In AlloyDB this is exposed through SQL procedures such as ai.initialize_embeddings() and metadata tables used for progress and settings. In a portable PostgreSQL-style design, the same information can live in an explicit configuration table.
CREATE TABLE product_reviews (
id bigserial PRIMARY KEY,
product_id bigint NOT NULL,
review_text text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now(),
review_embedding vector(768) DEFAULT NULL
);
CREATE TABLE embedding_refresh_config (
table_name text NOT NULL,
source_column text NOT NULL,
embedding_column text NOT NULL,
model_id text NOT NULL,
dimensions integer NOT NULL,
refresh_mode text NOT NULL CHECK (refresh_mode IN ('transactional', 'manual')),
batch_size integer NOT NULL DEFAULT 50,
PRIMARY KEY (table_name, embedding_column)
);The configuration table makes the embedding lifecycle inspectable. Operators can see which table uses which model, whether refresh is transactional or deferred, and what batch size the refresh worker should use. This prevents hidden application code from becoming the only source of truth for vector maintenance.
In transactional mode, a trigger or database-managed hook updates the vector when the source column changes. This keeps search fresh but makes the write path depend on the embedding model. The trigger should be used selectively and should avoid unnecessary refreshes when the source text did not change.
CREATE OR REPLACE FUNCTION refresh_review_embedding_txn()
RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' OR NEW.review_text IS DISTINCT FROM OLD.review_text THEN
NEW.review_embedding := embed_text('text-embedding-005', NEW.review_text);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER product_reviews_embedding_txn
BEFORE INSERT OR UPDATE OF review_text ON product_reviews
FOR EACH ROW
EXECUTE FUNCTION refresh_review_embedding_txn();This style is simple and consistent, but it moves model latency into the transaction. If the model endpoint slows down, writes slow down. If quota is exhausted, writes may fail unless the system has a fallback policy. For most high-volume tables, transactional mode should be reserved for narrow, low-write workloads or records whose search freshness is business-critical.
Deferred refresh separates the commit of source data from the embedding call. The write transaction records that a row needs refresh; a worker or scheduled SQL job processes pending rows in batches. This pattern keeps the OLTP path fast and makes model failures retryable without rolling back user writes.
CREATE TABLE embedding_refresh_queue (
table_name text NOT NULL,
row_id bigint NOT NULL,
embedding_column text NOT NULL,
enqueued_at timestamptz NOT NULL DEFAULT now(),
attempts integer NOT NULL DEFAULT 0,
last_error text,
PRIMARY KEY (table_name, row_id, embedding_column)
);
CREATE OR REPLACE FUNCTION enqueue_review_embedding_refresh()
RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' OR NEW.review_text IS DISTINCT FROM OLD.review_text THEN
INSERT INTO embedding_refresh_queue (table_name, row_id, embedding_column)
VALUES ('product_reviews', NEW.id, 'review_embedding')
ON CONFLICT (table_name, row_id, embedding_column)
DO UPDATE SET enqueued_at = now();
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;The queue table is idempotent: repeated changes to the same row collapse into one pending refresh. The refresh worker can then fetch rows in primary-key order, call the embedding model in batches, update vectors, and delete queue entries only after the vector write succeeds.
Large tables should not be embedded row by row. Batch model APIs amortize network overhead and often improve throughput by one or two orders of magnitude. The source article describes a database function with a batch_size parameter and notes that very large jobs should avoid unbounded in-memory arrays by using cursor-style processing.
-- Initialize all missing embeddings in bounded batches.
CALL ai.initialize_embeddings(
model_id => 'text-embedding-005',
table_name => 'product_reviews',
content_column => 'review_text',
embedding_column => 'review_embedding',
batch_size => 50,
incremental_refresh_mode => 'manual'
);
-- Later, refresh only rows that need maintenance.
CALL ai.refresh_embeddings(
table_name => 'product_reviews',
embedding_column => 'review_embedding',
batch_size => 50
);A cursor-based worker follows the same shape without requiring every pending row to fit in memory. It opens a snapshot of pending work, fetches a bounded number of rows, embeds that batch, records progress, and commits. If the job crashes, progress metadata tells the next run where to resume or which rows still need retry.
E-commerce catalogs, marketplace listings, and content libraries often keep source data in a relational database while adding semantic search on top. Database-managed refresh lets the same table hold title, description, inventory metadata, and vector representations. Product updates can enqueue embedding refresh work automatically, while search queries combine structured filters with vector similarity.
The practical benefit is reduced synchronization glue. Teams do not need a separate data export pipeline just to keep vectors current. They still need observability and model quota management, but the database becomes the coordination point for text, vector, and refresh state.
RAG systems frequently store documents, chunks, and embeddings in different systems. A database-managed refresh design keeps chunk text and embedding state together. When a chunk changes, the refresh queue records the affected row. When an embedding model changes, the system can add a new embedding column or rebuild the existing one as a controlled backfill job.
This is especially useful when the table also stores tenant IDs, ACLs, source timestamps, or workflow status. SQL constraints and transactions protect the relationship between source chunks and their vectors, while vector indexes make the same rows retrievable for semantic search.
Changing embedding models is operationally risky because the old and new vectors live in different vector spaces. Database-managed refresh makes migration explicit: add a second vector column with the new dimension, initialize it in bulk, build or update the vector index, compare retrieval quality, then switch reads to the new column. The old column can remain available for rollback until the migration is complete.
This two-column strategy avoids corrupting an active search path with partially refreshed vectors. It also lets teams measure model-specific recall and latency before changing production traffic.
Embedding refresh should expose state as queryable data. A progress table can track the target table, embedding column, job type, cursor position, rows completed, rows failed, current status, and last error. Operators should be able to answer: Is the initial embedding job complete? How many rows are stale? Which model quota is slowing refresh? Which row failed repeatedly?
CREATE TABLE embedding_refresh_progress (
job_id uuid PRIMARY KEY,
table_name text NOT NULL,
embedding_column text NOT NULL,
job_type text NOT NULL,
status text NOT NULL,
rows_total bigint,
rows_done bigint NOT NULL DEFAULT 0,
rows_failed bigint NOT NULL DEFAULT 0,
last_row_id bigint,
last_error text,
updated_at timestamptz NOT NULL DEFAULT now()
);
-- A worker updates this row after every committed batch, not only at job end.Restart-safe batches commit vectors and progress together. If a worker embeds 50 rows and crashes before updating progress, it may repeat the batch. That is acceptable if writes are idempotent. If it advances progress before writing vectors, rows can be skipped permanently. The safe order is compute, write vectors, update progress, then acknowledge work.
Applications should know whether semantic results may include stale vectors. A table can store embedding_updated_at, source_updated_at, and embedding_model_id. Queries can filter out stale rows for strict use cases or allow stale rows when recall matters more than freshness.
SELECT id, review_text
FROM product_reviews
WHERE review_embedding IS NOT NULL
AND embedding_updated_at >= updated_at
ORDER BY review_embedding <=> embed_text('text-embedding-005', $1)
LIMIT 20;For user-facing search, it is often better to return slightly stale semantic results than to hide records completely. For compliance, support, or financial workflows, stale vectors may be unacceptable. The query layer should make that policy explicit.
Embedding models are usually rate-limited by requests, tokens, or both. The refresh worker should throttle itself based on model quota, table priority, and observed error rates. A low-priority backfill should not starve incremental updates for recent user edits.
A practical scheduler uses separate queues or priorities for initialization, incremental refresh, and re-embedding. It reserves capacity for incremental work, processes bulk jobs opportunistically, and reduces batch size when model requests exceed token limits or memory limits.