Incremental Embedding Pipeline Design for Postgres CDC Streams

Postgres now runs under more than 55% of all developers surveyed and 58% of professional developers, per Stack Overflow's own numbers; it's already the system of record for most teams bolting AI features onto an existing application. That has a consequence people don't think through until it bites them: the vectors in pgvector are derived data, and derived data drifts from its source unless something forces it to stay in sync. The default pattern, an application layer that calls the embedding API whenever it happens to write a row, doesn't do that. It creates a structural gap between the Postgres row (the truth) and the embedding (a stale guess about the truth), and that gap produces degraded search results and RAG systems that confidently hallucinate against outdated content downstream.

The failure isn't exotic. Content gets updated, the embedding refresh job doesn't fire or fires late, and the vector index keeps serving a stale representation of the row it's supposed to describe. Teams patch this with background workers, ad hoc queues, retry loops glued together outside any transaction boundary, and none of it holds up under restart or partial failure. What's missing is an authoritative trigger, a signal the pipeline can trust completely, so that re-embedding happens exactly when it should and not on a guess. That signal already exists inside Postgres. It's called the write-ahead log, and change data capture built on top of it is the only reliable way to keep pgvector honest.

What the WAL gives you and what it demands in return

Logical decoding reads row-level changes directly out of the WAL, no full-table scans required, no polling for "what changed since last time." It needs one thing turned on: wal_level = logical. Once that's set, every change to a monitored table appears as a discrete event with real structure.

Each event carries an operation type (insert, update, delete, or snapshot read), along with before and after images of the row (one of which may be null, depending on the operation) and source metadata: table name, transaction ID, log sequence number, timestamp. That LSN lets a consumer resume from an exact position rather than replaying from the beginning or guessing at overlap.

When a connector first attaches to a database, it takes a consistent snapshot of existing rows, then hands off to streaming at the precise WAL position where that snapshot completed. Done correctly, there's no gap where a row could be missed and no window where the same row gets processed twice. Done incorrectly, it's exactly one of those two failures. The mechanism that hands off between snapshot and stream deserves more attention than most CDC tutorials give it.

The replication slot as the single most dangerous component in the pipeline

The replication slot is a server-side cursor, and it does exactly one job: it tells Postgres how far the consumer has read, so the database knows which WAL segments are safe to recycle. That job is also the source of the single worst failure mode in the entire pipeline. When the consumer stops advancing (connector crash, a bad restart, a network partition that lasts longer than anyone expected), Postgres can't reclaim the WAL behind that slot. It just keeps it. All of it. Until disk fills up and the database goes down.

The exposure scales with write volume in a way that's easy to underestimate. A system producing 1 GB of WAL per hour with a connector down for 24 hours is sitting on 24 GB of WAL it cannot recycle, and that's a moderate workload. One production team running only two replication slots watched Debezium's consumption fall behind badly enough that WAL logs piled up into the hundreds of gigabytes before anyone caught it.

Zalando hit a version of this in production: replication slots failed to advance when there was no table activity to push them forward, and the WAL backed up toward disk exhaustion. The fix wasn't exotic, either, pinning Debezium 2.7.4 to pgjdbc 42.7.2 resolved it, and that combination ran for close to two years processing billions of events with zero detected data loss attributable to the mechanism. Debezium is durable in practice. It's that a replication slot is a resource with a failure curve, and it needs monitoring with the same seriousness as disk space or replication lag on a standby, because by the time someone notices the symptom, the WAL has already piled up.

Three topologies for consuming CDC events and generating embeddings

There isn't one correct shape for this pipeline. There are three, and the right one depends mostly on what infrastructure a team already runs.

Topology 1 runs the full Kafka-mediated chain: Postgres to a Debezium connector, into a Kafka topic, out through an embedding worker, and finally an upsert into pgvector. Four separate components, each with its own deploy cycle, its own failure surface, its own dashboard someone has to actually look at. A workable reference build connects a Kafka topic to a stream processor that consumes events and feeds vectors into the store at whatever pace it can absorb them. As of August 2026, the versions to pin are pgvector 0.8.x, Debezium 3.6.x, and Kafka 4.3.x, and Kafka 4.0 dropped ZooKeeper entirely: KRaft is mandatory now, not an option. The failure mode that actually bites in this topology is usually a replication slot or offset-handling problem rather than a slow embedding API. It's consumer lag combined with non-atomic offset commits, which produces duplicate or stale vectors quietly, long before anyone notices the embedding service was ever slow. The right response to lag is not to drop events, it's to let Kafka's retention window absorb the backlog and alert loudly on lag instead. Zalando runs this pattern at real scale, hundreds of WAL-backed event streams through Debezium on Kubernetes, processing hundreds of thousands of events, which is a reasonable proof that the topology holds up outside a whiteboard. It fits teams that already operate Kafka Connect and have a platform team willing to own slot-lag alerting as a real job, not an afterthought.

A second approach skips tailing the raw WAL and writes the change event to an outbox table inside the same transaction as the business row, then relays it afterward. Both this and raw CDC solve the dual-write race, the classic problem of a row and its event getting out of sync because they're written in two separate operations. They just solve it from opposite ends of the stack. The outbox couples event production to the application transaction, so application engineers own the contract, and ordering and deduplication get easier to reason about because the event's shape is something the application defined on purpose. Raw CDC couples event production to the WAL itself, so a platform team owns Kafka Connect and slot-lag alerting, and application code never has to know CDC exists. For embeddings specifically, the outbox pattern is useful when only some column changes should trigger re-embedding, since the application can decide that explicitly rather than making the pipeline interpret raw WAL operations after the fact. A workable idempotency setup here: write the domain change and the outbox event in one transaction, emit with an idempotent producer and a deterministic partition key, then have the consumer write a processed_events marker after a successful apply, so a restart just replays a no-op instead of a duplicate write.

A third approach drops the WAL consumer and the Kafka cluster. Database triggers enqueue embedding jobs into pgmq on INSERT and UPDATE, pg_cron batches those jobs on an interval, pg_net dispatches them to an external embedding API, and the result gets written back to the vector column. Nothing to monitor outside Postgres itself. The embedding call runs asynchronously: the trigger fires a job rather than blocking the write path on an API round-trip, so a slow embedding provider never stalls a transaction. The trade-off is that the pg_cron batch interval sets a hard floor on how fresh an embedding can ever be, and failure handling lives inside the database scheduler rather than in a dedicated consumer with its own offset tracking. This fits teams with no existing Kafka footprint who'd rather keep the whole thing in SQL, or teams on a managed Postgres platform like Supabase where pgmq, pg_cron, and pg_net are available extensions.

Exactly-once delivery and idempotent upsert as the embedding correctness contract

Nearly every CDC tool on the market, Debezium included, guarantees at-least-once delivery. Duplicates aren't an edge case worth a footnote, they're the baseline condition the pipeline has to be built around from day one.

For a normal event consumer, a duplicate is wasted work. For an embedding pipeline, it's worse: a duplicate event can trigger a full re-embedding call against a paid API for content that never actually changed, burning money and rate-limit budget on nothing. And when Debezium and its downstream consumer aren't checkpointed together atomically, LSN misalignment on restart can cause the pipeline to miss a window of events or reprocess one. In the worst cases, teams have had to re-snapshot entire databases after a replication slot failure, and for a dataset in the hundreds of gigabytes, that's not a quick fix. That's downtime and a large compute bill.

The fix lives at the vector layer, not the transport layer, and it's a discipline more than a tool. Every embedding write should key on the source row's primary key paired with a content hash or the LSN of the change. Idempotent upsert on that key means the same event landing twice produces the identical vector, no divergence, no drift. And if an UPDATE event arrives but the content hash hasn't moved, because only some untracked column changed, the pipeline should skip the embedding call outright rather than burning an API round-trip on a row that reads the same as it did a moment ago.

Tooling that operationalises these patterns: pgai Vectorizer and Debezium in production

Theory is fine, but two tools actually carry these patterns into production today, and they sit at opposite ends of the complexity spectrum.

pgai Vectorizer, from Timescale, treats the embedding pipeline as something closer to a declarative index definition than a piece of infrastructure someone has to babysit. A single call to ai.create_vectorizer() specifies the source table (say, documents), the content column (content), the destination table (document_embeddings), the embedding model (text-embedding-3-small ), and a chunking strategy such as a recursive character text splitter. Behind that one call sits a background worker that processes changes through internal queues and triggers, with retry logic already built in for model failures, rate limits, and latency spikes, so nobody has to write that logic themselves. Since April 2025, it's shipped as a Python library usable against any PostgreSQL instance, not just Timescale Cloud. It runs against Amazon RDS, Supabase, and self-hosted Postgres alike. It's the production-ready answer to the brokerless, SQL-centric approach, batching and retry logic included rather than left as an exercise for whoever builds the pipeline.

Debezium sits at the other end. It's open-source, reads through either the pgoutput or wal2json logical decoding plugin, and needs a specific Postgres configuration to run at all: wal_level = logical, the right shared_preload_libraries, max_wal_senders = 4, max_replication_slots = 4. RisingWave reworked WAL parsing and memory handling for large transactions, tightened LSN persistence for precise recovery without triggering a full re-snapshot, improved schema parsing, and integrated with RisingWave's own log store that keeps snapshot and stream perfectly aligned. The incremental snapshot mechanism Debezium adopted from the DBLog design is the right answer to a question every team eventually asks: how does a pipeline bootstrap embeddings against an existing table without pausing the world for a full re-snapshot.

Choosing between the two comes down to what else consumes the data. Debezium plus Kafka earns its complexity when the pipeline feeds multiple downstream systems beyond pgvector, or when a Kafka Connect platform already exists and needs no new justification. pgai Vectorizer earns its simplicity when pgvector is the only consumer and the team would rather stay inside SQL than stand up a streaming broker for one job. Neither tool erases the operational burden discussed above. Both still need slot management, idempotent upsert logic, and a clear answer for DELETE propagation. The tooling automates the happy path. It does not automate the failure modes.

Operational requirements that must be designed in, not bolted on

None of the three topologies survives contact with production if the failure modes above get treated as edge cases to handle later. Slot lag monitoring has to exist before the first connector goes live, not after the first disk-exhaustion incident. Idempotent upsert keyed on primary key plus content hash has to be the default write path, applied before anyone notices duplicate vectors polluting a similarity search. DELETE propagation needs an explicit answer: a deleted Postgres row has to result in a deleted (or tombstoned) vector, and that mapping doesn't happen automatically just because CDC is running.

Freshness requirements should be stated as a number. The pg_cron interval in the brokerless topology is a real floor on how current an embedding can be, and that number needs to be a deliberate choice weighed against API cost, not a default nobody looked at twice. Topology 1's consumer lag needs the same treatment: a lag threshold that triggers an alert before the WAL backlog turns into a disk problem, not after.

The database gives every one of these pipelines the same underlying guarantee, a WAL, a replication slot, and an exact position in a change stream. Designing for the operational failure modes on day one keeps pgvector honest as a system, while discovering them later in an incident review lets the pipeline silently rot instead.