A materialized view nobody had refreshed in five months would get flagged in the first review. A table of embeddings in the same state usually passes, because every query against it still comes back with ten results and plausible scores.
An embedding is a function of two inputs: the source text and the model that read it. Change either one and the stored vector describes something that no longer exists. The usual ingest script computes the vector once and records neither input, so there’s no way to even ask which rows have gone bad.
I’d treat vectors like any other derived table. Record what each one was derived from, let source changes schedule the recompute, and put a lag number where people can see it.
What five months of drift adds up to
Rookwood’s help center is fiction, and so is every figure that follows. It holds 48,000 articles, split into 310,000 chunks and embedded once, the week search launched.
Five months later someone audits the index. 11.8 percent of the chunks, about 36,600, were embedded from text that has since been edited. Another 2,140 vectors belong to articles that were deleted, and search still returns them. One is a refund policy the company retired, which the support assistant has been quoting to customers.
No job failed in those five months. A stale vector doesn’t raise an error. Depending on where the chunk text is read from, an edited chunk either returns its old wording or gets matched on what it used to say.
Two columns turn staleness into a query
CREATE EXTENSION IF NOT EXISTS vector; -- pgvector 0.5.0 or later, for HNSW
-- The source table already exists: article_chunks (chunk_id bigint PRIMARY KEY, body text, ...)
ALTER TABLE article_chunks
ADD COLUMN body_hash text GENERATED ALWAYS AS (md5(body)) STORED;
CREATE TABLE embedding_settings (
only_row boolean PRIMARY KEY DEFAULT true CHECK (only_row),
current_model text NOT NULL
);
INSERT INTO embedding_settings (current_model) VALUES ('text-embedding-ada-002');
CREATE TABLE chunk_embeddings (
chunk_id bigint NOT NULL,
model text NOT NULL,
source_hash text NOT NULL, -- body_hash of the text that was embedded
embedding vector(1536) NOT NULL,
embedded_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (chunk_id, model)
);
CREATE INDEX chunk_embeddings_ada_002 ON chunk_embeddings
USING hnsw (embedding vector_cosine_ops)
WHERE model = 'text-embedding-ada-002';
CREATE TABLE embedding_queue (
chunk_id bigint PRIMARY KEY,
enqueued_at timestamptz NOT NULL DEFAULT now(),
visible_at timestamptz NOT NULL DEFAULT now(),
attempts int NOT NULL DEFAULT 0
);
The hash only has to detect a change, so md5 is enough. The key includes the model because vectors from two models can’t be compared, and they shouldn’t share an index either. That’s why the index is partial. The search query has to carry the same model = '...' filter, or the planner won’t use it.
The queue is keyed on the chunk. Ten edits in a minute collapse into one pending row, and that row keeps its first enqueued_at, which is the honest number for lag.
One trigger for insert, update and delete
CREATE FUNCTION sync_chunk_embedding() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
DELETE FROM chunk_embeddings WHERE chunk_id = OLD.chunk_id;
DELETE FROM embedding_queue WHERE chunk_id = OLD.chunk_id;
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
IF NEW.body_hash IS NOT DISTINCT FROM OLD.body_hash THEN
RETURN NEW; -- the text didn't change, so the vector is still right
END IF;
END IF;
INSERT INTO embedding_queue (chunk_id) VALUES (NEW.chunk_id)
ON CONFLICT (chunk_id) DO NOTHING;
RETURN NEW;
END;
$$;
CREATE TRIGGER article_chunks_embedding_sync
AFTER INSERT OR UPDATE OR DELETE ON article_chunks
FOR EACH ROW EXECUTE FUNCTION sync_chunk_embedding();
The update branch compares hashes so a view-count bump or a retag doesn’t cost an API call.
Deletes skip the queue. The vector goes in the same transaction as its chunk, so there’s no window in which a retired policy is still searchable. That only works because the vectors sit in the same database as the source, which is one more reason I’d keep them in Postgres below a few million chunks. With a separate vector store this branch becomes a queued removal, and I’d test it harder than any other path.
Change capture here is a trigger. It adds a small write to every insert, delete and body edit, and you need permission to change that schema. If you don’t have it, a log reader such as Debezium can fill the same queue table and nothing downstream changes.
A worker that can lose a race
-- Step 1: claim a batch in a short transaction.
WITH batch AS (
SELECT chunk_id
FROM embedding_queue
WHERE visible_at <= now()
ORDER BY enqueued_at
LIMIT 100
FOR UPDATE SKIP LOCKED
)
UPDATE embedding_queue q
SET visible_at = now() + interval '5 minutes',
attempts = q.attempts + 1
FROM batch b
WHERE q.chunk_id = b.chunk_id
RETURNING q.chunk_id;
-- Step 2: read body and body_hash for the claimed chunks, call the API
-- outside any transaction, then run this once per chunk.
-- $1 chunk_id, $2 model, $3 the body_hash that was embedded, $4 the vector
WITH src AS (
SELECT chunk_id, body_hash
FROM article_chunks
WHERE chunk_id = $1 AND body_hash = $3
FOR SHARE
), saved AS (
INSERT INTO chunk_embeddings (chunk_id, model, source_hash, embedding)
SELECT chunk_id, $2::text, body_hash, $4::vector FROM src
ON CONFLICT (chunk_id, model) DO UPDATE
SET source_hash = EXCLUDED.source_hash,
embedding = EXCLUDED.embedding,
embedded_at = now()
)
DELETE FROM embedding_queue q
USING src
WHERE q.chunk_id = src.chunk_id;
SKIP LOCKED lets several workers claim without waiting on each other. The API call happens outside any transaction.
Step 2 is a single statement, and FOR SHARE is the part I wouldn’t drop. It holds the chunk row against edits and deletes until the vector and the queue delete commit together. If the chunk was edited while the call was in flight, the hash no longer matches, nothing is written, the queue row stays, and it becomes visible again five minutes later.
Lag, measured the way you’d measure any derived table
SELECT
count(*) FILTER (WHERE e.chunk_id IS NULL) AS not_on_current,
count(*) FILTER (WHERE e.source_hash <> c.body_hash) AS stale_text,
(SELECT count(*) FROM chunk_embeddings x
WHERE NOT EXISTS (SELECT 1 FROM article_chunks a
WHERE a.chunk_id = x.chunk_id)) AS orphans,
(SELECT count(*) FROM embedding_queue) AS pending,
(SELECT now() - min(enqueued_at) FROM embedding_queue) AS oldest_pending
FROM article_chunks c
CROSS JOIN embedding_settings s
LEFT JOIN chunk_embeddings e
ON e.chunk_id = c.chunk_id AND e.model = s.current_model;
I’d alert on oldest_pending. A stale row with nothing in the queue means a change got past the trigger. orphans should sit at zero, because the trigger and the row lock close that path between them. I count it anyway. A bulk load with triggers disabled goes around both.
With the queue in place at Rookwood, the median gap between an edit and its fresh vector is 40 seconds. The 99th percentile is 6 minutes, and it comes from bulk edits that touch a few thousand chunks at once.
A model change goes through the same queue
Moving Rookwood from ada-002 to text-embedding-3-small needs no second pipeline. Add a partial index for the new model, insert every chunk_id into the queue, and point the worker at the new model. Its vectors build up under their own key while search keeps reading the old ones. When the new model covers every chunk, update current_model and delete the old rows.
That’s about 124 million tokens, at roughly 400 a chunk. At 3-small’s list price of $0.02 per million it comes to about $2.50, or half that through OpenAI’s Batch API if a day’s turnaround is fine. The money hardly matters. What the queue buys is that an edit made halfway through the backfill isn’t lost, and progress is a count you can read.
It has costs. Two sets of vectors double the storage and index memory until the old rows go. Edits during the backfill are embedded under the new model only, so the old vectors drift for that day, which I’d accept. And the column is 1,536 wide, which both of these models fit. text-embedding-3-large returns 3,072 dimensions, past the 2,000 that pgvector will index, so you’d shorten it with the API’s dimensions parameter.
If your index was built without hashes, the staleness query has nothing to compare and the true answer is that every row is unknown. For that index the full re-embed is where I’d start.