Run the Embedding Lifecycle Inside OLTP

Tier: Advanced · Workflow area: Vector & AI Features · ~45 minutes

Semantic search that was accurate at launch decays as products get renamed, tickets get resolved and descriptions get rewritten — because the vector still encodes last quarter's text. Lifecycle management is the difference between operating vector search and having once installed it. The pattern is pure DBA craft: change capture, an outbox queue, a reconciliation check and a rehearsed migration plan.

When to use

• Any vector column over mutable dataif the source text can be UPDATEd, you need this from day one, not after the first "search feels worse" ticket.
• Before an embedding-model upgradenew model means every vector must be regenerated, in one coordinated migration. The runbook here is written before you need it.
• When search quality has degraded and nobody knows whythe drift-detection query in step 4 is the diagnostic: it tells you in one query whether stale vectors are the cause.

When NOT to use

• Append-only, immutable corporaarchived documents, closed-period records. Embed once at insert; the refresh machinery is dead weight.
• When re-embed volume exceeds what triggers should carrythousands of text updates per second belongs on CDC (logical decoding, Debezium) feeding a worker fleet, not row triggers. Same lifecycle, different transport.
• As a substitute for fixing a bad embed-text templaterefreshing garbage produces fresh garbage. If pb-29's eval scores were never good, fix the template before automating its refresh.

The recipe

1. 1.

   Make staleness detectable: store a hash of what was embedded. The single highest-leverage design move — alongside every vector, record a fingerprint of the exact text it encodes:

   Copy

   ALTER TABLE products
     ADD COLUMN embedded_text_hash text,
     ADD COLUMN embedded_at timestamptz,
     ADD COLUMN embedding_model text;
   

   Drift is now a comparison, not a mystery: current text hash vs. stored hash.

2. 2.

   Capture changes with a trigger writing to a re-embed queue. Never call a model from inside a trigger — enqueue instead, keeping the transaction fast:

   Copy

   CREATE TABLE embed_queue (
     id bigint PRIMARY KEY,          -- product id; PK dedupes bursts
     enqueued_at timestamptz NOT NULL DEFAULT now()
   );
   
   CREATE OR REPLACE FUNCTION enqueue_embed() RETURNS trigger AS $$
   BEGIN
     IF (NEW.name, NEW.category, NEW.description) IS DISTINCT FROM
        (OLD.name, OLD.category, OLD.description) THEN
       INSERT INTO embed_queue (id) VALUES (NEW.id)
       ON CONFLICT (id) DO UPDATE SET enqueued_at = now();
     END IF;
     RETURN NEW;
   END $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trg_products_embed
     AFTER UPDATE ON products
     FOR EACH ROW EXECUTE FUNCTION enqueue_embed();
   

   Note the guard: only text-relevant column changes enqueue. A price update must not trigger a re-embed.

3. 3.

   Drain the queue with a worker that claims rows safely. The worker pattern is FOR UPDATE SKIP LOCKED — ask AI to draft it, then review for the failure cases:

   Copy

   Write a worker loop (Python + psycopg): claim up to 200 ids from
   embed_queue with SELECT ... FOR UPDATE SKIP LOCKED; fetch the rows'
   embed_text; call Ollama for embeddings; update embedding,
   embedded_text_hash (sha256 of embed_text), embedded_at,
   embedding_model; delete claimed queue rows; commit. Handle: model
   endpoint down (leave queued, back off), row deleted since enqueue
   (drop silently), text changed again mid-flight (hash check decides).
   

   The third failure case is the subtle one: if the text changed after the worker read it, the stored hash will not match current text, and step 4 catches it next cycle — self-healing by design.

4. 4.

   Run drift reconciliation on a schedule — trust nothing, verify weekly. Triggers can be dropped, workers can die, backfills can race. The reconciliation query is the safety net:

   Copy

   SELECT count(*) AS drifted
   FROM products p
   WHERE p.embedding IS NOT NULL
     AND p.embedded_text_hash IS DISTINCT FROM
         encode(sha256(convert_to(
           'Name: ' || name || '. Category: ' || category ||
           '. Description: ' || coalesce(description, ''), 'UTF8')), 'hex')
     AND NOT EXISTS (SELECT 1 FROM embed_queue q WHERE q.id = p.id);
   

   Expected result: drifted = 0. Anything else is an incident with a known remediation: enqueue the drifted ids. Alert on it like replication lag.

5. 5.

   Write the model-upgrade runbook before the upgrade exists. A new embedding model invalidates every vector — old and new vectors are not comparable. Have AI draft the runbook, then pressure-test the numbers:

   Copy

   Draft a model-upgrade runbook for 4.2M embedded rows, ~40 rows/sec
   embedding throughput on our worker. Cover: total re-embed wall time,
   batch plan and off-peak windows, dual-column strategy (embedding_v2
   populated in background, index built, one-transaction cutover,
   old column dropped later), search behavior mid-migration, rollback
   point, and an eval-set gate (pb-29 queries) before cutover.
   

   Check the arithmetic: 4.2M rows ÷ 40/sec ≈ 29 hours of embedding — that number drives the whole plan. The dual-column pattern means search never serves mixed-model results; the cutover is the atomic swap of which column the query reads.

6. 6.

   Budget the lifecycle in money and load. Count last month's text-changing updates; multiply by embedding cost (API pricing, or local GPU time) and write the figure down. Re-embedding is a permanent operating cost of the feature — surface it before finance discovers it.

Make it yours

• CDC instead of triggersat high write rates, logical replication into a stream (Debezium/Kafka) feeds the same worker logic; the hash-reconciliation query is unchanged and still the safety net.
• SQL Server 2025Service Broker or change tracking plays the queue role; the hash column and reconciliation pattern port directly.
• Oracle 23aiwith in-database ONNX embedding, refresh can be a scheduled job entirely inside the engine — but keep the hash column; in-database does not mean cannot-drift.
• ERP master datamaterial and customer texts change through governed processes; hook re-embedding into the MDG/change-request completion event rather than raw row triggers, and log it as part of the change record.

Quality check

• Drift reconciliation runs on a schedule and alertsa lifecycle without a verification loop is a promise, not a system. Zero-drift is a monitored invariant.
• The trigger enqueues only on embed-relevant column changesmeasured by checking queue volume against text-update volume. A queue inflated by price updates burns embedding budget for nothing.
• The upgrade runbook has real throughput numbers and an eval gatewall-time from measured rows/sec, and cutover blocked until the new model beats the old one on the pb-29 eval set.
• Queue depth and worker lag are on a dashboardstaleness has an SLO (say, 95% re-embedded within 15 minutes) and someone is paged when it breaks.