Before diving in, here’s what each file does \u2014 so you know what you’re running:<\/p>\n\n\n
\nlab\/05_embedding_versioning\/\n\u251c\u2500\u2500 schema.sql # DDL: tables, triggers, indexes\n\u251c\u2500\u2500 worker.py # Embedding worker (claims queue items, calls OpenAI, writes vectors)\n\u251c\u2500\u2500 change_detector.py # Compares new vs old embeddings to decide SKIP or EMBED\n\u251c\u2500\u2500 freshness_monitor.py # Generates a full health report on embedding staleness\n\u2514\u2500\u2500 examples\/\n \u251c\u2500\u2500 simulate_document_changes.py # Generates a realistic mix of article mutations\n \u251c\u2500\u2500 targeted_mutations.py # Applies specific change types to specific articles\n \u251c\u2500\u2500 demo_skip_locked.py # Demonstrates concurrent worker queue distribution\n \u251c\u2500\u2500 demo_trigger_flow.py # End-to-end: UPDATE \u2192 trigger \u2192 queue \u2192 embed\n \u2514\u2500\u2500 demo_quality_drift.py # Simulates declining search quality + automatic re-queuing\n\n<\/pre><\/div>\n\n\nEvery script connects to the local wikipedia<\/code> database and uses the same embedding queue. They’re designed to run sequentially \u2014 each step builds on the state left by the previous one.<\/p>\n\n\n\n
\n\n\n\nThe Starting Point<\/h2>\n\n\n\n
My lab environment runs PostgreSQL 17.6 with pgvector 0.8.0 and pgvectorscale (DiskANN). The articles<\/code> table already has 25,000 Wikipedia articles with dense and sparse embeddings from the previous labs (the sparsevec(30522)<\/code> column holds SPLADE sparse vectors \u2014 30,522 is the BERT WordPiece vocabulary size):<\/p>\n\n\n\nwikipedia=# \\d articles\n Table "public.articles"\n Column | Type | Collation | Nullable | Default\n------------------------+------------------+-----------+----------+---------\n id | integer | | not null |\n url | text | | |\n title | text | | |\n content | text | | |\n title_vector | vector(1536) | | |\n content_vector | vector(1536) | | |\n vector_id | integer | | |\n content_tsv | tsvector | | |\n title_content_tsvector | tsvector | | |\n content_sparse | sparsevec(30522) | | |\n title_vector_3072 | vector(3072) | | |\n content_vector_3072 | vector(3072) | | |\nIndexes:\n "articles_pkey" PRIMARY KEY, btree (id)\n "articles_content_3072_diskann" diskann (content_vector_3072)\n "articles_sparse_hnsw" hnsw (content_sparse sparsevec_cosine_ops)\n "articles_title_vector_3072_diskann" diskann (title_vector_3072)\n "idx_articles_content_tsv" gin (content_tsv)\n "idx_articles_title_content_tsvector" gin (title_content_tsvector)\nTriggers:\n tsvectorupdate BEFORE INSERT OR UPDATE ON articles FOR EACH ROW ...\n tsvupdate BEFORE INSERT OR UPDATE ON articles FOR EACH ROW ...\n\n<\/pre><\/div>\n\n\nNo content_hash<\/code>, no updated_at<\/code>, no versioned embeddings. This is the reality of most existing deployments \u2014 you need to retrofit versioning without breaking what’s already working.<\/p>\n\n\n\n
\n\n\n\nStep 1: Apply the Versioning Schema<\/h2>\n\n\n\nWhat schema.sql<\/code> does<\/h3>\n\n\n\nThe schema file adapts the generic pattern from Part 1 to the existing articles<\/code> table. It runs inside a single transaction and performs these operations in order:<\/p>\n\n\n\n\n- Adds two columns<\/strong> to
articles<\/code>: content_hash TEXT<\/code> and updated_at TIMESTAMPTZ DEFAULT now()<\/code><\/li>\n\n\n\n- Creates a BEFORE trigger<\/strong> (
trg_content_hash<\/code>) that automatically computes md5(content)<\/code> before every INSERT or UPDATE of the content<\/code> column \u2014 this is our change detection fingerprint<\/li>\n\n\n\n- Backfills<\/strong>
content_hash<\/code> for all 25,000 existing articles with UPDATE articles SET content_hash = md5(content)<\/code><\/li>\n\n\n\n- Creates
article_embeddings_versioned<\/code><\/strong> \u2014 the versioned embeddings table with model_name<\/code>, model_version<\/code>, source_hash<\/code>, is_current<\/code>, and a partial DiskANN index on WHERE is_current = true<\/code><\/li>\n\n\n\n- Creates
embedding_queue<\/code><\/strong> \u2014 the work queue with status<\/code>, content_hash<\/code>, change_type<\/code>, claimed_at<\/code>, and retry tracking<\/li>\n\n\n\n- Creates
embedding_change_log<\/code><\/strong> \u2014 records every SKIP\/EMBED decision with similarity scores for audit<\/li>\n\n\n\n- Creates
retrieval_quality_log<\/code><\/strong> \u2014 for the quality feedback loop (Step 9b)<\/li>\n\n\n\n- Creates an AFTER trigger<\/strong> (
trg_queue_embedding<\/code>) that fires on INSERT OR UPDATE OF content<\/code> and inserts a queue entry automatically<\/li>\n<\/ol>\n\n\n\nKey differences from the “clean” schema in Part 1:<\/p>\n\n\n\n
\n- No generated column for
content_hash<\/code><\/strong>: GENERATED ALWAYS AS (md5(content)) STORED<\/code> would rewrite the entire 25K-row table. The BEFORE trigger achieves the same result without a table rewrite \u2014 important for large production tables.<\/li>\n\n\n\n- Column-targeted trigger<\/strong>:
AFTER UPDATE OF content<\/code> instead of AFTER UPDATE<\/code>. The trigger only fires when the content<\/code> column is touched \u2014 title-only or metadata-only updates are ignored at the PostgreSQL level, not inside application code.<\/li>\n\n\n\n- Table naming<\/strong>:
article_embeddings_versioned<\/code> (not document_embeddings<\/code>) to match the existing articles<\/code> table naming convention.<\/li>\n<\/ul>\n\n\n\npsql -d wikipedia -f lab\/05_embedding_versioning\/schema.sql\n\n<\/pre><\/div>\n\n\nBEGIN\nALTER TABLE\nCREATE FUNCTION\nDROP TRIGGER\nCREATE TRIGGER\nUPDATE 25000\nCREATE TABLE\nCREATE INDEX\nCREATE INDEX\nCREATE INDEX\nCREATE TABLE\nCREATE INDEX\nCREATE INDEX\nCREATE TABLE\nCREATE TABLE\nCREATE FUNCTION\nDROP TRIGGER\nCREATE TRIGGER\nCOMMIT\n\n<\/pre><\/div>\n\n\nLet me walk through the important lines:<\/p>\n\n\n\n
\nALTER TABLE<\/code><\/strong> \u2014 adds content_hash<\/code> and updated_at<\/code> columns<\/li>\n\n\n\nCREATE FUNCTION<\/code> + CREATE TRIGGER<\/code><\/strong> (first pair) \u2014 the BEFORE trigger that computes md5(content)<\/code><\/li>\n\n\n\nUPDATE 25000<\/code><\/strong> \u2014 the backfill. This is the most expensive line: PostgreSQL computes MD5 for every article and writes the hash. On 25K rows it takes a few seconds; on millions of rows, plan a maintenance window<\/li>\n\n\n\nCREATE TABLE<\/code> + CREATE INDEX<\/code> (\u00d73)<\/strong> \u2014 the versioned embeddings table with its partial DiskANN index, version lookup index, and staleness detection index<\/li>\n\n\n\nCREATE FUNCTION<\/code> + CREATE TRIGGER<\/code><\/strong> (second pair) \u2014 the AFTER trigger that queues embedding work<\/li>\n<\/ul>\n\n\n\nAfter applying, the table now has versioning infrastructure:<\/p>\n\n\n
\nwikipedia=# \\d articles\n Column | Type | Nullable | Default\n------------------------+--------------------------+----------+---------\n ...existing columns...\n content_hash | text | |\n updated_at | timestamp with time zone | | now()\nReferenced by:\n TABLE "article_embeddings_versioned" CONSTRAINT ... FOREIGN KEY (article_id) ...\n TABLE "embedding_queue" CONSTRAINT ... FOREIGN KEY (article_id) ...\nTriggers:\n trg_content_hash BEFORE INSERT OR UPDATE OF content ON articles ...\n trg_queue_embedding AFTER INSERT OR UPDATE OF content ON articles ...\n tsvectorupdate BEFORE INSERT OR UPDATE ON articles ...\n tsvupdate BEFORE INSERT OR UPDATE ON articles ...\n\n<\/pre><\/div>\n\n\nTwo new triggers alongside the existing tsvector triggers. They coexist without conflict because trg_content_hash<\/code> is BEFORE (updates the hash) and trg_queue_embedding<\/code> is AFTER (queues the embedding work using the already-computed hash).<\/p>\n\n\n\nFive new tables: article_embeddings_versioned<\/code>, embedding_queue<\/code>, embedding_change_log<\/code>, retrieval_quality_log<\/code>, and the queue’s indexes.<\/p>\n\n\n\n
\n\n\n\nStep 2: Test the Trigger Manually<\/h2>\n\n\n\n
Before running anything complex, verify the trigger actually works. This is just a sanity check \u2014 one UPDATE, then look at the queue:<\/p>\n\n\n
\nwikipedia=# SELECT id, title, content_hash FROM articles WHERE id = 1;\n id | title | content_hash\n----+-------+----------------------------------\n 1 | April | 47761052aee1158134fc07f3f7337952\n\nwikipedia=# UPDATE articles SET content = content || ' [test trigger]' WHERE id = 1;\nUPDATE 1\n\nwikipedia=# SELECT id, article_id, status, content_hash, change_type, queued_at\n FROM embedding_queue ORDER BY queued_at DESC LIMIT 5;\n id | article_id | status | content_hash | change_type | queued_at\n----+------------+---------+----------------------------------+----------------+-------------------------------\n 1 | 1 | pending | 59e5ebe6fa9fce7ab87beccf6523dda6 | content_update | 2026-02-18 14:38:01.626792+00\n\n<\/pre><\/div>\n\n\nWhat happened here, step by step:<\/strong><\/p>\n\n\n\n\n- We checked article 1 (“April”) \u2014 its
content_hash<\/code> was 4776...<\/code><\/li>\n\n\n\n- We appended
' [test trigger]'<\/code> to its content<\/li>\n\n\n\n- The BEFORE trigger<\/strong> (
trg_content_hash<\/code>) fired first, recomputing content_hash<\/code> to 59e5...<\/code> (the new MD5)<\/li>\n\n\n\n- The AFTER trigger<\/strong> (
trg_queue_embedding<\/code>) fired next, inserting a row into embedding_queue<\/code> with the new hash and change_type = 'content_update'<\/code><\/li>\n\n\n\n- The queue entry has
status = 'pending'<\/code> \u2014 nothing has processed it yet<\/li>\n<\/ol>\n\n\n\nThe change_type<\/code> column is important: it’s how we’ll later distinguish content-triggered re-embeddings from quality-triggered ones (Step 9b).<\/p>\n\n\n\n
\n\n\n\nStep 3: Simulate 50 Document Mutations<\/h2>\n\n\n\n
Real knowledge bases don’t get 1 change at a time. The simulate_document_changes.py<\/code> script generates a realistic mix of changes to random articles.<\/p>\n\n\n\nWhat the script does<\/h3>\n\n\n\n
The script picks 50 random articles from the database and applies one of five mutation types to each, chosen by a weighted random distribution that mimics real-world editing patterns:<\/p>\n\n\n\n
\ntypo_fix<\/code><\/strong> (most common): appends a period or fixes a word \u2014 the kind of minor edit that shouldn’t trigger re-embedding<\/li>\n\n\n\nparagraph_add<\/code><\/strong>: appends a substantial paragraph (3-5 sentences) \u2014 new information that changes the semantic content<\/li>\n\n\n\nsection_rewrite<\/code><\/strong>: replaces a portion of the article with new text \u2014 significant semantic shift<\/li>\n\n\n\nmajor_rewrite<\/code><\/strong>: rewrites most of the article \u2014 entirely new embedding needed<\/li>\n\n\n\nmetadata_only<\/code><\/strong>: changes only the title (not the content) \u2014 should NOT trigger the embedding pipeline at all<\/li>\n<\/ul>\n\n\n\npython examples\/simulate_document_changes.py --count 50\n\n<\/pre><\/div>\n\n\nMutation Summary:\n----------------------------------------\n major_rewrite 3\n metadata_only 6\n paragraph_add 15\n section_rewrite 4\n typo_fix 22\n TOTAL 50\n\n<\/pre><\/div>\n\n\nThis distribution is realistic: most changes are minor fixes, a smaller portion adds new content, and a few are major rewrites. The 6 metadata_only<\/code> changes simulate edits to fields other than content<\/code> \u2014 think correcting a title or updating a URL.<\/p>\n\n\n\nNow check the queue:<\/p>\n\n\n
\nwikipedia=# SELECT status, count(*) FROM embedding_queue GROUP BY status;\n status | count\n---------+-------\n pending | 44\n\n<\/pre><\/div>\n\n\n50 mutations, but only 44 queue entries.<\/strong> Where did the other 6 go?<\/p>\n\n\n\nThe 6 metadata_only<\/code> mutations changed only the title (not content), so the trigger \u2014 which fires on UPDATE OF content<\/code> \u2014 didn’t fire for them<\/strong>. Those 6 changes never reached the embedding pipeline. This is the first cost optimization, and it happens at the PostgreSQL trigger level with zero application code, zero API calls, zero overhead.<\/p>\n\n\n\n\nWhy this matters<\/strong>: In a real knowledge base, a meaningful fraction of updates are metadata-only \u2014 tags, categories, status flags, author fields (in some orgs, 30-50% of all UPDATEs). Filtering them at the trigger level means your embedding worker never even sees them.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\nStep 4: Change Detection Without a Baseline<\/h2>\n\n\n\n
Now let’s run the change detector to see which items should be embedded vs skipped.<\/p>\n\n\n\n
What change_detector.py<\/code> does<\/h3>\n\n\n\nThe change detector is the “smart filter” in our pipeline. For each pending queue item, it:<\/p>\n\n\n\n
\n- Fetches the article’s current content<\/strong> from the
articles<\/code> table<\/li>\n\n\n\n- Looks up the most recent embedding<\/strong> for that article in
article_embeddings_versioned<\/code><\/li>\n\n\n\n- If no previous embedding exists<\/strong>: marks the item as EMBED (similarity = 0.0) \u2014 there’s nothing to compare against<\/li>\n\n\n\n
- If a previous embedding exists<\/strong>: generates a new embedding for the current content via OpenAI, computes the cosine similarity<\/strong> between old and new embeddings, and applies the threshold:\n
\n- Similarity \u2265 0.95 \u2192 SKIP<\/strong> (the semantic meaning barely changed, re-embedding would be wasteful)<\/li>\n\n\n\n
- Similarity < 0.95 \u2192 EMBED<\/strong> (the meaning shifted enough to warrant a new embedding)<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Logs every decision<\/strong> to
embedding_change_log<\/code> with the similarity score \u2014 this is your audit trail<\/li>\n<\/ol>\n\n\n\nMulti-chunk articles<\/strong>: When an article has multiple chunks (like “Dean Martin” with 3), the detector compares against chunk_index = 0<\/code> only \u2014 the lead section, which concentrates the article’s core topic. This is a deliberate tradeoff: it’s fast (one comparison, not N), and for Wikipedia-style content where the introduction summarizes the whole article, it’s a reliable proxy. For corpora where meaning is spread more evenly across chunks, you’d want a centroid approach (average the L2-normalized chunk vectors) or max pairwise similarity across corresponding chunks. The threshold may need recalibration depending on which strategy you choose.<\/p>\n\n\n\nThe --analyze-queue<\/code> flag tells it to analyze all pending items without actually embedding anything. Think of it as a dry run that records decisions.<\/p>\n\n\n\npython change_detector.py --analyze-queue\n\n<\/pre><\/div>\n\n\n2026-02-18 14:43:22 [DETECTOR] INFO Analyzing 44 pending queue items (threshold=0.95)\n2026-02-18 14:43:22 [DETECTOR] INFO Article 6607: EMBED (similarity=0.0000)\n2026-02-18 14:43:22 [DETECTOR] INFO Article 36870: EMBED (similarity=0.0000)\n...all 44 show similarity=0.0000...\n2026-02-18 14:43:22 [DETECTOR] INFO Results: 44 EMBED, 0 SKIP\n\n<\/pre><\/div>\n\n\nEvery single article shows similarity=0.0<\/code>. Why?<\/p>\n\n\n\nBecause article_embeddings_versioned<\/code> is empty<\/strong>. There are no previous embeddings to compare against. The change detector hit step 3 for every article: “no previous embedding exists \u2192 must EMBED.”<\/p>\n\n\n\nThis is an important operational insight<\/strong>: the change detector needs a baseline to work. On the very first run \u2014 or when you deploy to a new system \u2014 everything must be embedded. The SKIP optimization only kicks in on subsequent<\/strong> changes, after embeddings exist to compare against. If you’re migrating from a system that already has embeddings in a different format, you’d need to populate the source_hash<\/code> column from those existing embeddings first to bootstrap the comparison.<\/p>\n\n\n\n
\n\n\n\nStep 5: Create Baseline Embeddings<\/h2>\n\n\n\n
Now we need to establish that baseline. Let’s run the worker for one small batch.<\/p>\n\n\n\n
What worker.py<\/code> does<\/h3>\n\n\n\nThe worker is the component that actually calls the OpenAI API and writes embeddings to PostgreSQL. Here’s its internal flow:<\/p>\n\n\n\n
\n- Claim items from the queue<\/strong> using
SELECT ... FOR UPDATE SKIP LOCKED<\/code> \u2014 this is the concurrency primitive from Part 1. Multiple workers can run simultaneously, and each gets a non-overlapping set of items.<\/li>\n\n\n\n- For each claimed item<\/strong>: fetch the article content, split it into chunks (2000-character windows with overlap), and call the OpenAI
text-embedding-3-small<\/code> API to generate a 1536-dimensional vector for each chunk.<\/li>\n\n\n\n- Write the embeddings<\/strong> to
article_embeddings_versioned<\/code> with is_current = true<\/code>, model_name<\/code>, model_version<\/code>, and source_hash<\/code> (the content’s MD5 at the moment of embedding).<\/li>\n\n\n\n- Mark old embeddings<\/strong> for the same article as
is_current = false<\/code> (soft delete \u2014 they’re kept for rollback).<\/li>\n\n\n\n- Update the queue item<\/strong> to
status = 'completed'<\/code> with processed_at = now()<\/code>.<\/li>\n<\/ol>\n\n\n\nThe --once<\/code> flag means “process one batch and exit” (instead of running in an infinite polling loop). The --batch-size 10<\/code> flag means “claim up to 10 items at a time.”<\/p>\n\n\n\npython worker.py --once --batch-size 10\n\n<\/pre><\/div>\n\n\n2026-02-18 14:45:58 [8526] INFO Worker worker-once claimed 10 items\n2026-02-18 14:46:02 [8526] INFO Article 6607: embedded 1 chunks\n2026-02-18 14:46:02 [8526] INFO Article 36870: embedded 1 chunks\n2026-02-18 14:46:05 [8526] INFO Article 19078: embedded 1 chunks\n2026-02-18 14:46:05 [8526] INFO Article 7947: embedded 1 chunks\n2026-02-18 14:46:05 [8526] INFO Article 75802: embedded 2 chunks\n2026-02-18 14:46:05 [8526] INFO Article 5150: embedded 1 chunks\n2026-02-18 14:46:06 [8526] INFO Article 55579: embedded 1 chunks\n2026-02-18 14:46:06 [8526] INFO Article 92697: embedded 1 chunks\n2026-02-18 14:46:06 [8526] INFO Article 49417: embedded 3 chunks\n2026-02-18 14:46:06 [8526] INFO Article 70595: embedded 1 chunks\nProcessed 10 items\n\n<\/pre><\/div>\n\n\nReading the output:<\/strong><\/p>\n\n\n\n\nclaimed 10 items<\/code> \u2014 the worker took 10 items from the queue using SKIP LOCKED. If another worker ran simultaneously, it would get different items.<\/li>\n\n\n\n