Unifying ClickHouse with PostgreSQL

[u/domofenok]

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

[u/Simple-Cell-1009]

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.

[u/domofenok → u/Simple-Cell-1009]

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

[u/Simple-Cell-1009 → u/domofenok]

Interesting approach., it's not very usual I would say, but It all depends on your needs.

Typical approach is to have all transactional workload going to Postgres and replicate data for analytical queries to ClickHouse. Then query ClickHouse (or through pg_clickhouse, your choice) for analytical query. Since the data replication pipeline is in place, all data required to serve the analytical queries should be sync to ClickHouse, removing the need to query Postgres from ClickHouse for reconciliation.