Keeping Embeddings in Sync With Your Database: Outbox, CDC, and Partial Reindex

A vector index is a derived store: every write to a source row must durably produce a re-embed task, and there are three ways to produce one, a transactional outbox, change data capture off the write-ahead log, or a scheduled sweep over a content hash. None is an embedding call inside the transaction that wrote the row.

Two stores, one truth

Staleness in a vector index has no error page: retrieval returns its nearest neighbours whether or not those neighbours are still true. A price changed on Tuesday. The row is correct, the embedded chunk still carries last week's number, and the model answers from that chunk and cites it as current. Cache entries expire; vectors have no expiry, so drift only accumulates.

Why embedding inside the write transaction is the wrong fix

Calling an embedding API inside the transaction that writes the row makes another company's error rate your write availability. It is a network round trip that fails the way third parties fail, 429 at a rate limit, 503 when the provider is overloaded, and then users cannot save a document.

The second cost is transaction lifetime: locks and the snapshot are held while the call is in flight, blocking vacuum and queueing writers behind an endpoint you do not operate. At real concurrency that surfaces as connection pool exhaustion, which is why long-running transactions are known-bad.

Pattern 1: the transactional outbox

The outbox makes "embed this row" a row, so the intent to re-embed commits atomically with the data that changed. A separate process sends it later (microservices.io). Nothing here is AI-specific: the message says "re-embed document 4471" (the interview version).

CREATE TABLE embedding_outbox (
  id           bigserial PRIMARY KEY,
  document_id  bigint NOT NULL,
  content_hash text   NOT NULL,
  op           text   NOT NULL CHECK (op IN ('upsert', 'delete')),
  enqueued_at  timestamptz NOT NULL DEFAULT now(),
  attempts     int    NOT NULL DEFAULT 0,
  claimed_at   timestamptz,
  completed_at timestamptz
);
 
CREATE INDEX embedding_outbox_pending_idx
  ON embedding_outbox (claimed_at NULLS FIRST, id)
  WHERE completed_at IS NULL;

FOR UPDATE SKIP LOCKED lets a worker claim a batch without a broker:

WITH claimed AS (
  SELECT id FROM embedding_outbox
  WHERE completed_at IS NULL
    AND (claimed_at IS NULL
         OR claimed_at < now() - interval '5 minutes')
  ORDER BY id
  LIMIT 32
  FOR UPDATE SKIP LOCKED
)
UPDATE embedding_outbox o
   SET claimed_at = now(), attempts = attempts + 1
  FROM claimed c
 WHERE o.id = c.id
RETURNING o.id, o.document_id, o.content_hash, o.op;

SKIP LOCKED steps over rows another transaction holds, so ten workers take disjoint batches; a claim older than five minutes falls back into the predicate, so a worker that dies mid-batch strands nothing. The worker sets completed_at = now() once the vector write commits, and rows still null there are your queue depth. Delivery is at-least-once (delivery guarantees), so that write must be idempotent: key the upsert on (document_id, content_hash) and a duplicate overwrites an identical vector.

Pattern 2: change data capture

CDC moves the trigger out of your application entirely, because the write-ahead log already knows the row changed. Postgres logical decoding streams committed changes through a replication slot: a cluster-unique, single-database, crash-safe stream that outlives the connection reading it (logical decoding). Debezium consumes it and emits a message per row change with no application code on the write path, given wal_level=logical plus max_wal_senders and max_replication_slots headroom, a REPLICATION-privileged user, and a publication (Debezium).

Two details cost money. plugin.name defaults to decoderbufs, not pgoutput, so on managed Postgres, where you cannot install a decoding plugin, set plugin.name=pgoutput explicitly. And CDC delivers every column update, so the consumer needs a hash comparison before spending a call on a changed last_seen_at.

The real bill is the slot. A slot holds resources whether or not anyone reads it: WAL cannot be recycled and catalog rows cannot be vacuumed while it needs them, and the docs warn this can "cause the database to shut down to prevent transaction ID wraparound". max_slot_wal_keep_size (PG13 and later) defaults to -1, unlimited, so a paused consumer is unbounded disk growth. Put retained WAL per slot on a dashboard with an alert.

Pattern 3: the scheduled hash-diff reindex

A content hash per document turns "what needs re-embedding" into an ordinary SQL predicate, with no new infrastructure at all. Store the hash of the text as it is now, as a generated column so it cannot drift, alongside the hash the current vector was built from. Rows that disagree are the queue.

ALTER TABLE documents
  ADD COLUMN content_hash text
    GENERATED ALWAYS AS (md5(coalesce(title, '') || ' ' ||
                             coalesce(body, ''))) STORED,
  ADD COLUMN embedded_hash text,
  ADD COLUMN embedded_at   timestamptz;
 
SELECT id, title, body
FROM documents
WHERE embedded_hash IS DISTINCT FROM content_hash
ORDER BY embedded_at NULLS FIRST
LIMIT 500;

IS DISTINCT FROM folds in the never-embedded case: null is distinct from any hash, and embedded_at NULLS FIRST drains those rows first. The coalesce calls are load bearing: md5(null || ' ' || body) is null, null stays distinct from the stored hash forever, and that row is re-embedded on every pass. A STORED generated column also rewrites the table under ACCESS EXCLUSIVE: on a large documents, use a plain text column plus a trigger, or take the rewrite in a window.

One rule matters: hash exactly the string you send the provider, template and chunk boundaries included. Hash the raw body while embedding a titled, chunked version and the sweep misses real edits, or re-embeds the corpus the day the template changes.

For 200,000 documents where editors touch a few hundred a day, a ten-minute cron is the whole answer. A partial index on the predicate keeps it an index scan past a few million rows; batch sizing belongs to the ingestion pipeline.

Keep the sweep even after the outbox or CDC is live: it is the reconciler, the only thing that notices a dropped message, a worker that died between the API call and the write, or a backfill with the wrong template.

Deletes and tombstones, the failure nobody tests

A deleted row whose vector survives keeps answering questions about data you were asked to forget, which makes it a compliance problem rather than a bug. Weeks later the assistant quotes it to a different user.

Hard delete with vectors in the same database is the cheap case: have doc_chunks reference documents with ON DELETE CASCADE and the vectors go inside the same transaction, no window.

Soft delete plus a filter keeps history but taxes every query, and the sharper trap sits underneath: a deleted_at IS NULL predicate is applied after the approximate index scan, so a filtered query quietly returns fewer rows than its LIMIT (why, and the two fixes). A partial index excluding deleted rows sidesteps it entirely.

A separate vector store turns the delete into a call that can fail after your transaction committed, which is what the outbox is for: enqueue op = 'delete' with the row change and retry until the store acknowledges (vector store trade-off). Debezium does this by default: each delete is followed by a tombstone, same key, null value (tombstones.on.delete, default true), and a table with no primary key needs REPLICA IDENTITY FULL or the delete event carries no before image. Then write the test: delete a row, assert retrieval no longer returns it.

Changing embedding models means rebuilding the index

Vectors from two models do not share a coordinate space, so changing models is a full re-embed rather than an in-place migration. The shape: a second column, its index built without blocking writes, a batched backfill through the sweep you already have, dual writes for new rows, a measured cutover, then a drop.

ALTER TABLE doc_chunks ADD COLUMN embedding_v2 halfvec(3072);
 
CREATE INDEX CONCURRENTLY chunks_embedding_v2_idx
  ON doc_chunks USING hnsw (embedding_v2 halfvec_ip_ops);

That halfvec (pgvector 0.7.0 and later) matters: a vector column stores up to 16,000 dimensions but an HNSW or IVFFlat index on it caps at 2,000, so a 3072-dimension model is not indexable as vector. halfvec raises the ceiling to 4,000; asking the provider for fewer dimensions is the other exit (why the index has a ceiling). pgvector's README says to build indexes concurrently and rebuild with REINDEX INDEX CONCURRENTLY (pgvector). CONCURRENTLY cannot run in a transaction block, leaves an invalid index behind on failure, and on partitioned tables you build each partition's index concurrently, then the parent non-concurrently.

Dual writes belong to the worker, not the outbox: while the migration flag is on it embeds each drained row with both models and writes both columns in one statement, so the outbox row needs no model column. Cut over on numbers, not a release date: recall against a golden set is the gate (RAG evaluation metrics). Batch the backfill for the discount (OpenAI's Batch API covers /v1/embeddings at 50% off within 24 hours). Keep the old column and its index until the new one has served real traffic: rollback is then a config flip.

The decision table

Freshness is the axis these patterns differ on, and every second shaved off lag is paid for in operational surface.

Start with the outbox, keep the sweep behind it as the reconciler, and reach for CDC only when someone is already paid to watch slot lag. The outbox turns staleness into a queue depth you can alert on, and the sweep catches what the queue never hears about. Write the delete test: a surviving vector for a deleted row is the one failure here with no error to raise.

Sources & further reading: Postgres logical decoding · Debezium PostgreSQL connector · Transactional outbox pattern · pgvector