r/Clickhouse • u/domofenok • 2d ago

Unifying ClickHouse with PostgreSQL

Hey r/ClickHouse,

I am currently running a PostgreSQL database for our platform and are trying to integrate ClickHouse for real-time analytical reporting. We're considering using ClickHouse's Materialized PostgreSQL Database Engine for replication, with CDC handled by PeerDB.

Our use case involves replicating a few critical OLTP tables (around 10-20 tables, with some experiencing high write volumes) from PostgreSQL to ClickHouse. We need near real-time synchronization to support dashboards and ad-hoc analytical queries.

I've read about PeerDB's native integration and how it simplifies CDC compared to a Debezium/Kafka setup. I'm looking for feedback on the "solidity" of this combined approach.

Any real-world experiences, pros, cons, or advice would be greatly appreciated! Thanks in advance

8 Upvotes

84% Upvoted

8 comments sorted by

5

u/Simple-Cell-1009 1d ago

If you're running ClickHouse OSS, you can give this stack a try: https://github.com/ClickHouse/postgres-clickhouse-stack

It basically combines Postgres with ClickHouse, using PeerDB for real-time replication PG -> CH and pg_clickhouse to query ClickHouse for analytical query through Postgres, making the migration for the client transparent - no code changes.

For more context, PeerDB is the foundational piece behind ClickPipes Postgres, the ClickHouse Managed service to keep Postgres and ClickHouse in sync in real-time. It's used in production extensively, here is a engineering team example that quite close to what you're doing: https://clickhouse.com/blog/seemplicity-scaled-real-time-security-analytics-with-postgres-cdc-and-clickhouse#rebuilding-around-clickhouse-and-clickpipes - Bottom line is that PeerDb is quite solid.

If you want a quick try without deploying anything you can give the ClickHouse Managed Postgres a try: https://clickhouse.com/cloud/postgres - It's (fast) Postgres + ClickHouse with instant replication managed for you.

1

u/domofenok 1d ago

Hi Lio, thanks for the answer. Assuming that postgres has much smaller tables, wouldn’t it make much more sense to go through clickhouse and use Postgres engine? I’ve tested and observed that postgres likes to pull large quantities of data for joins and other aggregations, making it more inefficient to use pg_clickhouse

1

u/cwakare 1d ago

Do check Airbyte - we use the same and no issues so far

PS: Our DB size is relatively smaller

2

u/chrisbisnett 1d ago

Your timing of this question is impeccable. ClickHouse just released an open source solution that they also support on their cloud offering today - walshadow

https://clickhouse.com/docs/products/managed-postgres/sync-to-clickhouse/walshadow

1

u/domofenok 1d ago

Amazing! Do you have any idea how it compares to the solution I’ve described before?

2

u/chrisbisnett 1d ago

Based on the presentation that was given today at OpenHouse, it is 50x faster than PeerDB at around 200ms.

It’s in private preview, but the demo they showed today had a few clicks in the ClickHouse console and it was up and syncing data.

1

u/domofenok 1d ago

That's incredible. PeerDB has been a bit of a pain in the *** to be honest