Lab 08 · Embedding lifecycle ops inside OLTP

Vikram inherited Ananya's semantic search three months after launch, and the first support ticket reads: "search returns the old description's meaning." Nobody is maintaining the embeddings — the merchandising team updates product copy daily, and every update silently orphans a vector. This is the lifecycle problem every engine-side vector deployment hits: embeddings are derived data, and derived data without a refresh pipeline and a drift detector is a slowly corrupting cache that never throws an error.

Time & tier

~90 minutes hands-on · Core tier (maps to module M4 · The AI-Ready Database Estate — Vectors in Your Engine).

Setup

Continue from Lab 07: the vector-lab container, shopdb, 5,000 embedded products, and the local all-MiniLM-L6-v2 model. First, simulate the three months of unmanaged production Vikram inherited — a "merchandising bulk import" that rewrote copy with no re-embedding, seed_drift.py:

import random, psycopg2
random.seed(21)
conn = psycopg2.connect("host=localhost port=5434 dbname=shopdb user=postgres password=lab")
cur = conn.cursor()
drifted = random.sample(range(1, 5001), 120)
for pid in drifted:
    cur.execute("""update products
        set description = 'CLEARANCE: heavily discounted. ' || description
        where product_id = %s""", (pid,))
# a nastier subset: meaning inverted, not just prefixed
for pid in drifted[:30]:
    cur.execute("""update products
        set description = replace(replace(description,'waterproof','not waterproof'),
                                  'winter','summer')
        where product_id = %s""", (pid,))
conn.commit()
print("bulk import complete:", len(drifted), "rows rewritten, 0 re-embedded")

Run it. The database now contains 120 rows whose vectors describe text that no longer exists — and nothing anywhere records which ones. That amnesia is the first problem to solve.

Guided steps

1. 1.

   Make staleness detectable forever after. You cannot query "which vectors are stale" today because the schema never recorded what text each vector was computed from. Fix the schema debt:

   Copy

   alter table products
     add column embedded_hash text,
     add column embedded_at   timestamptz,
     add column model_version text;
   

   Backfill honestly — you know only when the pipeline believes vectors were last valid, which for the un-drifted rows is Lab 07's embed run. Set embedded_hash = md5(name || '. ' || description), model_version = 'all-MiniLM-L6-v2' only after step 3 identifies the liars — for now leave all three NULL. Recording a hash you have not verified would launder the drift.

2. 2.

   Build the re-embed queue and trigger. New updates must never orphan a vector again:

   Copy

   create table embed_queue (
     product_id int primary key,
     enqueued_at timestamptz not null default now());
   
   create or replace function enqueue_reembed() returns trigger
   language plpgsql as $$
   begin
     if new.name is distinct from old.name
        or new.description is distinct from old.description then
       insert into embed_queue (product_id) values (new.product_id)
       on conflict (product_id) do update set enqueued_at = now();
     end if;
     return new;
   end $$;
   
   create trigger trg_products_reembed
     after update on products
     for each row execute function enqueue_reembed();
   

   Design notes to write down: the trigger compares text columns only (price changes must not trigger embedding work), uses is distinct from (NULL-safe), and the queue upserts so a row edited five times embeds once. Test it: update one product's description, confirm one queue row; update its price, confirm the queue is unchanged.

3. 3.

   Hunt the seeded drift. The trigger protects the future; the reconciliation query finds the past. Since pre-drift text is gone, reconcile vectors against current text by re-embedding a fingerprint — but at 5,000 rows you can afford the direct approach: re-encode every row's current text and compare to the stored vector, find_drift.py:

   Copy

   from sentence_transformers import SentenceTransformer
   import psycopg2, numpy as np
   model = SentenceTransformer("all-MiniLM-L6-v2")
   conn = psycopg2.connect("host=localhost port=5434 dbname=shopdb user=postgres password=lab")
   cur = conn.cursor()
   cur.execute("select product_id, name || '. ' || description, embedding from products")
   stale = []
   for pid, text, emb in cur.fetchall():
       v = model.encode([text], normalize_embeddings=True)[0]
       stored = np.array(eval(emb))
       if float(np.dot(v, stored)) < 0.999:   # same text re-encodes to ~1.0
           stale.append(pid)
   print("stale vectors:", len(stale))
   

   Expected: exactly 120 stale rows. Spot-check five: their descriptions carry the CLEARANCE: prefix or the inverted phrases; their neighbors in search still reflect the old meaning. Enqueue all 120 into embed_queue.

4. 4.

   Demonstrate the damage before repairing it. Pick one of the 30 meaning-inverted rows (waterproof → not waterproof). Run the Lab 07 search for "waterproof jacket for monsoon camping" — expected: the inverted product still ranks high, because search sees its old vector. Screenshot or paste the result; this is the ticket Vikram received, reproduced.

5. 5.

   Drain the queue with a batch worker. worker.py: pull up to 200 queue rows, re-embed, update embedding, embedded_hash, embedded_at, model_version, delete the queue rows — one transaction per batch, ordered by enqueued_at. Run until empty (one pass here). Re-run find_drift.py — expected: stale vectors: 0. Re-run the step 4 search — the inverted product drops out. Now backfill embedded_hash/embedded_at/model_version for the remaining untouched rows: they just passed verification, so the hash is now earned, not assumed.

6. 6.

   Make the drift check cheap and schedulable. Full re-encoding is a 5k-row luxury. The production detector is the hash: select count(*) from products where embedded_hash is distinct from md5(name || '. ' || description). Run it — expected 0. Then prove it catches a bypass: update products set description = description || ' UPDATED' where product_id = 42; — the trigger queues it, and the hash check reports 1 until the worker drains. Also state the residual hole the hash cannot see: direct writes that also forge the hash, and COPY/ETL paths that bypass triggers — which is why the deep check (step 3) still runs monthly on a sample.

7. 7.

   Write the model-upgrade runbook. reembed-runbook.md, the artifact that outlives the lab. Required sections: Cost — full re-embed of 5,000 rows took your measured wall-clock in step 5's worker; scale it to 500k rows and to an API-priced model (state $/1k tokens assumption) — the point is a number, not a shrug. Batching plan — batch size, transactions per batch, off-peak window, vacuum expectations from 500k updates (every re-embed is a dead tuple), and why the queue table (not a where model_version <> ... scan) drives the worker. Mid-migration behavior — with one embedding column, a half-migrated table serves mixed-model results, and cross-model cosine scores are not comparable: state your chosen strategy (dual-column with cutover, or per-row model_version filter with two indexes, or downtime window) and its search behavior at 10%, 50%, 90% done. Rollback — what you keep (old column / old vectors) until the new model's recall is re-measured against Lab 07's ground truth.

Verify

• •
  The trigger enqueues on text change, ignores price-only updates, and deduplicates repeat edits.
• •
  find_drift.py reported exactly 120 before repair and 0 after; the step 4 search shows the inverted product before and after.
• •
  The hash check returns 0, then 1 after the bypass test, then 0 after draining.
• •
  reembed-runbook.md contains a computed 500k-row cost, a batching plan with vacuum note, an explicit mid-migration serving strategy, and a rollback condition tied to re-measured recall.

Self-grading rubric (pass = 7/10): schema debt fixed with hash/at/version columns, backfilled only after verification (0–2); trigger correct on all three design points (0–2); all 120 drifted rows found, damage demonstrated, repair proven (0–3); cheap detector built and its blind spots named (0–1); runbook quantitative on cost and explicit on mid-migration behavior (0–2).

Stuck?

• find_drift.py reports far more than 120your threshold is too tight for re-encoded floats round-tripped through Postgres text format; 0.999 assumes normalized embeddings — confirm normalize_embeddings=True in both Lab 07's embed and the drift check.
• eval(emb) feels wrongit is; it works because pgvector's text format is a valid Python list literal, but the robust form is np.array(json.loads(emb)) or the pgvector psycopg adapter. Either is fine for the lab.
• The trigger fires on price updatesyou compared the whole row; the function must test only name and description with is distinct from.