Query-based CDC over WAL: keeping a CQRS Postgres → ClickHouse read model fresh

Query-based CDC over WAL: keeping a CQRS Postgres → ClickHouse read model fresh

On GovFun we run a textbook CQRS split: Postgres is the write model, ClickHouse is the read model, and one sync service is the projection builder that keeps them in agreement. "CDC" — change data capture — is just the mechanism we use to move changes from the write model to the read model. About making that mechanism correct, and about a decision I'm oddly proud of: deliberately not reaching for the fashionable option.

The decision: query-based CDC, not log-based WAL

The original RFC wanted log-based CDC — tail the Postgres write-ahead log (WAL) through a logical replication slot and stream every change into ClickHouse. It's the "proper" answer in most CDC blog posts. A code audit killed it, for now, for three concrete reasons:

1. End-user latency isn't set by the sync. All 31 ClickHouse materialized views are timer-refreshed on a 1–12h cadence, not INSERT-triggered. Streaming changes in milliseconds to feed a dashboard that redraws hourly buys nothing.
2. Hard deletes never happen in prod on these tables.
3. The cost is a shared-primary outage vector. A stalled replication slot doesn't drop rows — it silently retains WAL until the primary's disk fills. That's a brand-new way to take down the write database, added to serve reads that refresh hourly.

So I hardened the query-based CDC we already had — updatedAt watermark polling — and kept log-based CDC documented as an escalation for the day we actually need sub-5-minute freshness or real deletes. Boring, safe, correct.

Making query-based CDC actually correct

Query-based CDC has well-known holes. The work was closing each one.

Capture completeness — every write must move updatedAt

Watermark polling only sees a row if its updatedAt advanced. One job, activeStatusRepair, issued a raw UPDATE that bypassed the timestamp — a live staleness bug where repaired rows never re-synced. Fix: an explicit "updatedAt" = NOW() plus BEFORE UPDATE triggers on the contracts and entities tables, so the database guarantees the watermark moves. It's no longer something a future query can silently skip.

Late commits — a trailing re-read window

A transaction can read at time T but commit later carrying an older updatedAt, so a naive "give me everything since my last high-watermark" misses it. An OVERLAP_MINUTES window (default 30) re-reads a trailing slice each run to catch those stragglers.

Deletes — reconcile by key-diff, not truncate

Query-based CDC can't observe a delete; there's no row left to poll. The old answer was to truncate the whole ClickHouse table and rebuild. Reconcile v2 instead does a key-diff: compare the key sets on both sides and issue a lightweight DELETE for just the orphans. On a 100M-row table it alerts instead of truncating — a blunt full-table rebuild has no business being the routine path.

Idempotency — insert-only onto ReplacingMergeTree

Re-reading the overlap window means re-inserting rows, so inserts have to be safe to repeat. The base tables become ReplacingMergeTree(updatedAt): a re-insert is just another version, and background merges collapse each key to the latest updatedAt. That lets the sync drop delete-then-insert-each-run for an insert-only path.

Dedup without the OPTIMIZE FINAL anti-pattern

The tempting way to force dedup is a scheduled OPTIMIZE FINAL — expensive, and it fights ClickHouse's own merge scheduler. The insert-only path drops it entirely and leans on background merges (min_age_to_force_merge_seconds) plus the read-side dedup the queries already do.

Freshness — measure staleness, not "did the job run"

The old health signal answered "did the batch run?" — the wrong question. A job can succeed and still leave the read model hours stale. The new CH_FRESHNESS_LAG check pages on PG MAX(updatedAt) − CH MAX(updatedAt): the actual lag between write model and read model, which is the number a user feels.

Nothing changes on merge

The whole thing ships flag-gated to today's behavior: CDC_V2_INSERT_ONLY=0, RECONCILE_V2=0, and every migration is written but not run — it goes through the normal migration-approval flow. Nothing touches a replication slot or the WAL, so nothing here can affect the primary's availability. Cutover is later, canary-first, and reversible.

What I deliberately deferred

Flipping insert-only on needs FINAL/argMax dedup validated against a live ClickHouse on the ~26 aggregating refreshable MVs — parity I won't fake without the real engine. Micro-batch cadence (15–30 min) is coupled to that. And log-based CDC stays gated until there's a real trigger for it: a sub-5-minute freshness need, real deletes, or scope growth.

The lesson I keep relearning: the interesting engineering usually lives in the correctness properties and the safety model — not in adopting the most impressive-sounding tool.