real-time data replication & its caveats

While building a postgres connector and learning what was actually happening under the hood of feldera, an incremental computation engine, I stumbled on artie.

Their tagline said:

real-time data replication, without the infrastructure.

I didn't really know what that meant. The architecture diagram looked simple enough. A source database on one side, a destination on the other, and one confident arrow in the middle doing the replication. What actually caught my attention was that middle part. What was hiding behind that arrow?

I didn't know much about it, so I did what any reasonable person does. I opened seventeen tabs and didn't sleep.

sorry mom.

The tagline is honest about the product. It is silent about the physics. This post is the physics part: what it actually takes to move one changed row from a source database to a destination, and every place that trip quietly goes wrong.

what does "real-time" actually mean

Nobody needs real-time. They need fresh-enough, and fresh-enough is a number somebody picked for a use case. Fraud scoring wants sub-second. An internal dashboard tolerates five minutes. Artie's own pitch is sub-minute latency for AI workloads, which is a specific budget for a specific customer, not a law of nature.

The second trap is measuring freshness in the wrong place. A change is fresh when a reader at the destination can see it. Not when the connector read it, and not when it left the process. A pipeline that is 200 milliseconds at the connector and 40 minutes behind at the table is not real-time in any sense that pays for anything.

The third trap is treating latency as a number instead of a distribution. Steady state can look beautiful while the tail is measured in hours, because the tail is where backfills, catch-up bursts and retries live. Average lag is a vanity metric. The budget has to hold at the worst moment, not the average one.

Watch lag in two units, seconds and bytes, because each one lies alone. Seconds mislead when the write rate spikes: three seconds of lag during a burst is a lot of bytes. Bytes mislead when the source is idle: a large byte distance can mean nothing is happening at all.

So "real-time" is a budget. Somebody chose it, and the pipeline's job is to hold it under the worst case. Everything after this section is the list of things that break the budget in ways that don't show up in a demo.

how do you know what changed

Before the middle can move anything, it has to know a change happened. There are four ways to find out, and three of them are approximations.

Timestamps. Poll for rows newer than the last thing you saw:

SELECT * FROM orders WHERE updated_at > :last_seen

The hidden assumptions are everywhere. The application has to maintain the column, two updates inside one clock tick can share a timestamp, a delete removes the row so you never learn it happened, and the poll is either a full scan or an index nobody promised to maintain. Every one of these produces a quietly wrong warehouse, eventually.

Triggers. Write every change into a changelog table inside the transaction. This works and it is honest, but it taxes every OLTP write, and the changelog is a second log that will eventually drift from the first one in some column nobody watches.

Diffs. Periodically compare the whole table. Reading every row to find the changed ones is the most expensive way to learn almost nothing, and without keys you cannot even tell an update from a delete plus an insert.

The log. postgres already records every change to the write-ahead log before the change becomes visible to any reader. That is the price of durability, and it is being paid whether anyone reads the log or not. Reading it costs the source almost nothing extra.

Only the log carries the change as it actually happened, in commit order, inside its transaction. The database keeps a perfect ledger of itself. CDC at its best is reading the ledger instead of interrogating the shelves.

wal, logical replication, and cdc

The write-ahead log is an append-only stream of records, written before the change is visible to readers, and it is the thing that makes COMMIT mean something. If it is not in the WAL, it did not happen.

Physical replication ships those bytes to a standby that replays them exactly. Perfect for a hot copy of the same database. Useless for a destination with a different shape, because pages are not rows.

Logical decoding reads the same WAL and emits change events per table: insert, update, delete. A replication slot is a bookmark in that decoded stream, and a publication picks which tables flow through it.

CDC, change data capture, is the umbrella term for the whole pattern: capture changes from the source's log and deliver them somewhere else. The caveat is that pgoutput, postgres' logical decoding format, is internal to postgres. It does not leave the database on its own. Something has to sit there, hold the protocol open, and read it out.

That something is the middle. Which brings us to the arrow.

postgres doesn't magically talk to kafka

The classic answer to "how does the log get to the warehouse" is a stack, not a component. Debezium running inside Kafka Connect on the source side, a Kafka cluster in the middle, a sink consumer on the destination side. Each of those is a deployable system with its own configuration, its own failure modes and its own clock.

The connector speaks the replication protocol on one side and acts as a Kafka producer on the other. Connect records its progress as offsets stored in Kafka, restarts tasks, and rebalances when workers come and go. Kafka itself is not a pipe. It is a distributed log: topics split into partitions, ordering exists only inside a partition, and retention runs on time or size.

This is the machinery that "without the infrastructure" is referring to. Teams have stood up and operated a Kafka cluster so that a database could talk to a warehouse. Artie's pitch, as I read it, is that they become the middle and you don't run any of it.

Which is a real trade, and often a good one. But the caveats don't disappear when the middle is someone else's. They just stop being your pager's problem and stay being your data's problem. The rest of this post is the list.

when is a change actually safe

This is the question I built an entire connector around, so I will keep it short here. When is it safe to tell postgres that a byte of WAL can be forgotten?

An acknowledgement is a promise about the future: you will never need to send this again. Both directions of getting it wrong are bad, and they are not equally loud.

Ack too early, and a crash inside the window leaves the change gone from both sides. Nothing errors. The number is just wrong forever. This is the worst failure mode in the whole field, silent loss, and it is invisible in every test that doesn't crash the process in exactly the right place.

Ack too late, and the source holds WAL on the destination's behalf. Disk pressure on the primary. The source pays rent for the destination's slowness, and eventually evicts.

"Done" is defined by the destination's durability, not by anything accepting bytes. In my connector the delivery boundary was a completion token from feldera: an HTTP 200 meant accepted, a token meant processed and written. Those are different moments, and only one of them may move the bookmark.

lsns, kafka offsets, and why they are different

An LSN is a byte position in one append-only sequence, printed like 0/14D08C8. It orders everything the cluster has committed, across every table and every transaction. One sequence, total order.

A Kafka offset is an index inside one partition of one topic. Partition three has its own counter and partition seven has its own, and they cannot be compared with each other at all.

Push the WAL through a partitioned topic and the total order is gone. Partition by primary key and you keep per-row order, which is enough for many sinks, but you lose cross-row order and you lose transaction atomicity. One transaction's events spread across partitions can be observed half-applied.

The LSN does not survive that trip on its own. The middle has to carry it through, in the event envelope or in headers, or the mapping is lost forever. You cannot reconstruct "which WAL position was this row" from an offset after the fact.

They are not even the same unit. An LSN advances with bytes written. An offset advances with events produced. Ask "how far behind are we" and you get two answers that disagree, which is why a lag dashboard showing one number is usually showing the flattering one.

crashes, retries, and duplicate events

Every hop in the diagram above is a place to die. The middle is a cache of unacknowledged work, and every crash window inside it resolves to one of two outcomes: replay, which is a duplicate, or skip, which is a loss. Everything serious chooses replay.

That is why at-least-once is the honest contract and not an apology. A pipeline that cannot duplicate is a pipeline that can lose.

The windows are not symmetric. Dying before the apply is cheap and clean. Dying between apply and ack guarantees a duplicate, which is fine if the sink is built for it. Acknowledging before the apply is the only unforgivable version, and it is the one that green test suites hide best.

Whether a duplicate is harmless is the destination's decision, not the pipeline's. An upsert by primary key absorbs a replayed event without noticing. An append-only log counts it twice, and now the revenue numbers are fiction. Same event, different truth.

exactly-once is not as simple as it sounds

People conflate three different claims. Exactly-once delivery means the transport will not resend. Exactly-once processing means the computation will not repeat. Exactly-once effect means the destination ends up in the state it would have reached if everything happened once. Only the third one pays anyone's salary, and it is the hardest one.

Kafka does a lot inside itself. The idempotent producer gives each producer an id and sequence numbers so broker-side retries don't duplicate records. Transactions write to multiple partitions atomically, and read-committed consumers only see committed output. Within a Kafka cluster, that story is solid.

It cannot reach into the destination. End to end, exactly-once effect is one of two things. Either the effect is idempotent, an upsert by key or a dedup against a monotonic sequence per key, or the offset commit and the destination write happen inside one transaction at the destination. The second needs a transactional destination that can hold both.

This is the end-to-end argument from 1984 saying the same thing in general: the ends have to participate in the property. The middle cannot deliver it alone, no matter how many flags the middle exposes.

So when a landing page says exactly-once delivery, read it as a claim about a whole path: capture, transport, apply, and what happens when each of those dies. The useful question is where the dedup lives. "The warehouse upserts by primary key" is a completely fine answer. It is also the primary key doing the real work, which is exactly where the next section starts.

deletes, replica identity, and unchanged values

Inserts arrive whole. Updates and deletes do not. What the old row looks like on the wire is a configuration decision called replica identity, and the settings have different bills.

DEFAULT sends only the primary key columns of the old row. A table with no primary key has nothing usable at all. FULL sends the entire old row, at roughly double the WAL cost of every update. NOTHING sends nothing, and postgres refuses updates and deletes on a published table outright. I measured that refusal on my 16.15 cluster, after an earlier note of mine wrongly claimed a silent drop.

Then there is TOAST. Large values live out of line, and when an update does not touch them, they are not sent. The column arrives as an unchanged marker. The destination has to merge it with what it already holds, or the column quietly freezes at an old value while looking perfectly alive.

And a key-changing UPDATE under DEFAULT can arrive carrying only the identity tuple: the new row, the old key, and nothing else of the old row. If the middle doesn't translate that into delete plus insert, the destination keeps the old identity forever. My connector produced exactly that ghost row, and it took a live crash test to see it.

This section is where replicas quietly drift. A row deleted at the source that is never retracted at the destination. A column frozen in time. Nothing errors, nothing lags, nothing pages. Drift is invisible until someone counts both sides, and nobody counts both sides until something is already wrong.

what happens when the destination can't keep up

The source does not slow down when the destination is busy. postgres commits at the speed of its own writers, and the log grows at that speed regardless of who is reading. Lag is arrival rate minus apply rate, accumulated over time. It does not self-heal. It only drains when the destination applies faster than the source commits, for as long as the backlog lasts.

The main dial is batching, and it trades throughput against freshness. Bigger batches amortize the destination's round trips, which is where most of the cost lives. A linger deadline keeps fresh data moving when traffic is sparse. In my connector the linger ran from the oldest buffered event, not the newest, because a newest-based deadline is unreachable under a steady trickle.

Catch-up is adversarial by construction. Right after an outage, the replay arrives as a burst, exactly when the destination is at its coldest: caches empty, merges queued, connections freshly opened. A pipeline that held its budget at steady state can blow it entirely during recovery, which is the exact moment everyone is watching the dashboard.

The destination's write path is heavier than it looks, too. An upsert is a lookup plus a write. A warehouse merge is a real operation with planning and locking, not the row insert it resembles. The number that matters is sustained apply rate under realistic keys, not burst throughput into an empty table.

replication slots, kafka retention, and growing backlogs

There are two retention systems in a replication pipeline, and they fail in opposite directions.

The slot. postgres holds WAL until the consumer confirms, with no time limit, on the source's disk. A slot whose consumer nobody is watching never releases anything. Not on its own. Not ever.

The log. kafka drops data on a schedule, seven days by default, or when a size cap is hit. What it drops is gone forever.

So the two cliffs point different ways. The slot's cliff is a full disk on the primary, which takes the OLTP application down with the analytics pipeline. The log's cliff is a permanent gap in the replica. And a gap doesn't just miss events. It misses deletes, which a later sync will not repeat, so the destination keeps rows the source threw away. Ghost rows, forever, unless someone resyncs with retracts.

Monitoring, then: lag in bytes and in seconds, oldest retained WAL against disk size, consumer offset age against retention. Alert on slope, not just value. A lag that grows linearly is an outage with a countdown timer on it, and the countdown is visible days in advance.

the historical data problem: backfills and live cdc

A slot only holds WAL written after it was created. Every row that already existed when the pipeline was set up will never arrive as a change event. The stream is not an archive.

So a first sync is two processes, a snapshot and a stream, and they have to meet exactly once, at one point. The trick postgres provides is the consistent point: create the slot with an exported snapshot, read the table through that snapshot, then stream from the slot's LSN. Rows committed before the point are in the snapshot. Rows committed after ride the stream. No gap, no overlap, no third window.

The wrong order, copy the table and then create the slot, loses every row committed in between, permanently and silently. I built both modes into my connector so the loss could be measured instead of asserted.

Meanwhile the backfill itself is not free. It is a full scan of the biggest tables on the source, competing with live traffic, while the live stream piles up behind it. Catch-up time after the snapshot is part of the freshness budget, and it is the part that never appears on a landing page.

Debezium has an entire incremental snapshotting mechanism, watermarks and chunks and snapshot windows, built mostly to make the two halves cooperate without blocking each other. That is how much engineering the seam takes when you refuse to block.

And a delete that lands in the gap is the nastiest case. A row deleted while the pipeline is hours behind never gets retracted, because the snapshot that would mention its absence was taken before the delete. The destination holds a ghost until someone resyncs with retracts, which my connector had to learn to do the hard way.

what changes when this runs in production

Schema drift. postgres reports a relation with a new column while the destination still has the old shape, and logical decoding doesn't carry DDL events at all, so the connector finds out from the relation message, mid-stream. Someone has to run the destination DDL, in the right order, while the stream is live. Artie markets automatic schema evolution as a feature line. It is not a feature. It is a subsystem.

Deploys. The middle is a stateful service pretending to be stateless. Its state is "position", and it has to survive every restart, upgrade and crash. A deploy that loses its offset either replays a burst or skips a range, and both of those are the sections above.

Blips. Reconnect with backoff and full jitter, because every consumer of one primary loses it at the same instant. And reset the backoff on progress, not on liveness. A loop that reconnects, fails at the same transaction, and drops again forever is up, busy, and permanently at zero progress, and nothing that watches "is the process alive" catches it.

Hygiene. A connector for an environment someone deleted will pin WAL on the primary until someone notices a disk graph. Slots are cheap to create and expensive to forget. Dropping unused ones is on-call work, forever.

The deepest problem is that silent failure is the default. A replica that is slowly going wrong doesn't page anyone. It serves reads. Dashboards render. The way you find divergence before a customer does is checking on purpose: sampled counts by key at both ends, checksums on hot tables, on a schedule.

And the cost is real even when everything works. The source pays a walsender and decoding CPU. The destination pays upserts, merges and compaction. The network pays the entire backfill, twice if the first attempt crashed. "Without the infrastructure" means without you running it. It does not mean it isn't there.

real-time is a correctness problem before it is a speed problem

The speed part is genuinely easy now. Batch, linger, parallelize, tune the batch size. Anyone can move rows fast. Vendors compete on latency numbers because latency numbers are easy to publish and easy to improve.

Every caveat in this post was a correctness caveat. When to acknowledge. What a duplicate does at the destination. Where an offset lives and what it can be compared to. What a delete carries. Where a gap hides and who retracts the rows it swallows. None of them show up in a latency benchmark, and every one of them can make the replica wrong.

A late replica is honest. It looks late, so nobody trusts it past its freshness label. A wrong replica is a liability with a dashboard on it, because wrong data is the only failure mode that looks like success.

For founders, the read on a tagline like artie's is not cynicism. Managed middles are a good trade, often the right one, and "someone else carries the caveats" is a perfectly sound way to buy data infrastructure. The thing to know is that that is the trade. You are not buying the absence of the caveats. You are buying somebody else's engineering, and somebody else's on-call, for the caveats.

After all seventeen tabs, the arrow finally made sense to me. It is simple to draw because everything behind it was somebody's problem to solve. The word real-time is earned one section at a time, and a pipeline gets to say it about itself only after every one of them is answered.

references