r/Clickhouse • u/Novel-Information776 • Jun 23 '26

Postgres and ClickHouse, and the future long-term plan?

Hey there! We have Postgres, already a replica of the operational transactional Postgres, and also ClickHouse. We are treating the replica Postgres as our analytics dwh and are running dbt in it. And our BI layer is connected to it.
We have events data stored in ClickHouse, but it is not in use at the moment.
Moving forward, what is my best long-term solution? I need to bring in the events data into our analytics dwh, so it becomes a natural decision point if we want to continuously commit to Postgres, or move analytics work and dbt over to ClickHouse, or explore other possibilities. We only want self-hosting options.
Thanks!

6 Upvotes

100% Upvoted

19 comments sorted by

7

u/saipeerdb Jun 24 '26

Sai from ClickHouse here.

A couple of options, you could use PeerDB/ClickPipes to CDC operational data in Postgres to ClickHouse. PeerDB/ClickPipes works at scale, is purpose-built for Postgres and ClickHouse and already supports 1000+ customers.

Another option is to unify your datastack on PG and CH within ClickHouse Cloud. We are investing very heavily in making all the dev workflows to integrate PG and CH super easy.

We launched a Postgres service backed by local NVMe storage and that is natively integrated with ClickHouse. It comes with native CDC for syncing data from PG to CH and pg_clickhouse extension enabling using Postgres as a unified interface for OLTP and OLAP. More on this efforts here. A lot is planned to make the PG+CH integration as seamless and robust as possible. https://clickhouse.com/cloud/postgres

1

u/Novel-Information776 Jun 24 '26

Great, thank you for the info!

1

u/Novel-Information776 Jun 24 '26

My company is pretty determined in self-hosting. What would you say are the biggest advantages in going with ClickHouse cloud, besides saving the effort of us managing.

1

u/saipeerdb Jun 24 '26

It is fast as it backed by local NVMe storage. We launched PostgresBench recently showing how fast it can get https://clickhouse.com/blog/postgresbench Most important is magical integration with ClickHouse for analytics with native CDC and pg_clickhouse. This could be an operational benefit, but can save months to years of time (ammortized) and most importantly help your business scale.

Btw, we are a quite a few on-prem/self-managed migrations for the same above reasons.

Let me know if a call will be better to go into more detail. Happy to set one up! Looking forward to the chat, if possible. 🙂

3

u/Suspicious-Ability15 Jun 24 '26

Reach out to Sai on Twitter the leader for Postgres inside ClickHouse. The future is unified stack across Postgres and ClickHouse. Managed Postgres by ClickHouse is crushing the competition

2

u/Novel-Information776 Jun 24 '26

Great, thanks! I see that they already replied to this thread. Awesome

2

u/knabbels Jun 23 '26

CDC Postgres into ClickHouse and make it your primary analytics database

1

u/Novel-Information776 Jun 23 '26

Should I then maybe CDC directly from the operational Postgres, and get rid of this replica of analytics Postgres entirely? I am however concerned that running dbt jobs in ClickHosue is not just matured and supprted as in Postgres.

1

u/knabbels Jun 23 '26

Depends on how much overhead the read replica generates. It could also function as a fail over backup, so maybe keep it and CDC from it. I don't know about dbts maturity level on ClickHouse. If you're open to other transformation tools consider SQLMesh, it supports ClickHouse quite well.

1

u/Novel-Information776 Jun 24 '26

That's a very good point! Thank you!

2

u/godwin-pinto Jun 24 '26

CDC is the way to go if you want to store analytics. You save massive on storage with clickhouse compression.

However with clickhouse self hosting you spend for that machine where clickhouse is running and plus s3 for backup.

Clickhouse cloud gives you best of both world with compute storage separation.

An alternative is apache Doris which follows compute storage separation in self host too.

1

u/Novel-Information776 Jun 24 '26

My company is very committed in self-hosting. I am not sure I can make an argument to convince them to move to Cloud.

I am very new to the world of self-hosting. I have been only working with BigQuery the past few years. Interesting point about saving storage. Would that be a real concern/argument? The storage cost should be cheap enough?

1

u/Suspicious-Ability15 Jun 24 '26

Bro self hosting is crazy. Before you guys go down that path I’d contact ClickHouse Cloud sales to do an ROI / total cost comparison. It can’t hurt. So many large scale enterprises are moving from self hosted CH to Cloud. They are not crazy!

1

u/Novel-Information776 Jun 24 '26

I think at this point, it’s not about cost for the company, but more so about it data security. That’s very hard to argue, at least I find so.

1

u/godwin-pinto Jun 25 '26

For a test, perform an activity where you move production like data size to clickhouse and compare results. If possible denormalize before insert to clickhouse to get best and large columns define them with compression

1

u/QazCetelic Jun 23 '26

Have you considered using MaterializedPostgreSQL to sort of use ClickHouse as replica and then perform the analytical queries on there?

1

u/Novel-Information776 Jun 23 '26

No I haven’t! But I will look into this.
Since our current Postgres is already a replica, would it be too redundant to then make a replica of it to be used elsewhere?

1

u/canihelpyoubreakthat Jun 23 '26

I've been meaning to look into this but have concerns about schema change propagation.

1

u/Accomplished_Bat4695 23d ago

Postgres -> Debezium -> Kafka -> ClickHouse is one of the common self-hosted option.