Skippr Docs

Appearance

Postgres CDC to ClickHouse Final State Guide ​

June 2026

Postgres to ClickHouse pairs a strict WAL source with a destination that converges through background merges.

Short Answer ​

Postgres CDC to ClickHouse works by streaming inserts, updates, and deletes from PostgreSQL logical replication and writing those row versions into ClickHouse tables that use ReplacingMergeTree semantics. Skippr adds a _skippr_order_token String column so ClickHouse can keep the version with the highest token during merges, and it records deletes in tombstone tables while applying ALTER TABLE ... DELETE WHERE.

For example, if public.accounts id 31 changes plan from "starter" to "team", PostgreSQL emits an update with the full row after image and an LSN-derived order token. ClickHouse receives the newer version for id 31, but readers may still need FINAL right after the write because background merges have not necessarily converged yet.

Why Teams Struggle with This ​

The source and destination have different ideas about immediacy. PostgreSQL gives a strict committed order through WAL and LSNs, while ClickHouse reaches its final deduplicated answer through background merge work. That makes the integration correct but worth explaining carefully to query consumers.

  • PostgreSQL CDC requires wal_level = logical, a replication slot, and a replication-capable user.
  • Updates arrive with full row after images, which ClickHouse needs because it keeps whole-row versions rather than partial patches.
  • Recent duplicate versions may be visible until ReplacingMergeTree merges them.
  • Deletes still rely on tombstones so an older replayed insert does not restore a removed key.

How Skippr Handles It ​

Skippr makes the contract explicit instead of pretending ClickHouse behaves like a row store. It stores the committed PostgreSQL LSN, writes ordered versions into ClickHouse, creates the tombstone table automatically, and documents that FINAL is the query-time tool for point-in-time consistency on fresh data.

That distinction is helpful in practice. You can separate ingestion correctness from immediate query visibility and still explain both using the same order token.

  • PostgreSQL logical replication with durable LSN-based resumes.
  • ClickHouse _skippr_order_token columns for version ordering.
  • ReplacingMergeTree convergence toward the highest-token row per key.
  • Tombstone tables plus delete application for removed rows.

What the First Useful Version Looks Like ​

This integration is a good fit when ClickHouse is the analytics destination and the team understands its merge model.

The habit that keeps it healthy is telling readers when to trust the raw table and when to add FINAL for just-written keys.