Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Pg_ClickHouse: A Postgres extension for querying ClickHouse

clickhouse.com

Post reply on HN
Print view View original

41–47 of 47 posts

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#41

Post by saisrirampur »

oulipo2 wrote: post #35
saisrirampur wrote: post #14

Earlier quoted context omitted.

Great question! If you’re starting a greenfield application, pg_clickhouse makes a lot of sense since you’ll be using a unified query layer for your application. Now, coming to your question about replication: you can use PeerDB (acquired by ClickHouse https://github.com/PeerDB-io/peerdb ), which is laser-focused and battle-tested at scale for Postgres-to-ClickHouse replication. Once the data is replicated into Click…

Nice! Right now I'm using Timescaledb, do you think it makes sense to move to a Postgres+CH setup instead? or only if I hit the limit of timescaledb? Also what would be the benefit for me of querying clickhouse from Postgres, rather than directly through my backend via an ORM/SDK? is that because it would allow me to do JOINs? What would be the typical setup if I want to JOIN analytical data (eg my IoT device reading…

Great questions! ClickHouse is a purpose-built analytical database with thousands of optimizations for analytics, which is why it’s typically faster and more scalable than TimescaleDB. Here’s a post that covers real scenarios where users have moved workloads from Timescale to ClickHouse: https://clickhouse.com/blog/timescale-to-clickhouse-clickpip...

If your operational (OLTP) tables are reasonably big, the recommended approach is to replicate them into ClickHouse and let ClickHouse handle the joins. This avoids cross-database joins and lets the execution be pushed fully into ClickHouse. You can use ClickPipes/PeerDB to make that super easy. https://clickhouse.com/docs/integrations/clickpipes/postgres... https://clickhouse.com/docs/integrations/clickpipes/postgres

Where pg_clickhouse fits: If you’re already using Postgres for OLTP and want to offload analytics to ClickHouse without rewriting your app, the pg_clickhouse extension helps. It lets you run OLTP and OLAP queries from Postgres, while pushing the analytical queries—and their joins—down to ClickHouse, where the replicated data lives. Going native i.e. querying ClickHouse directly for OLAP will be the most optimal and is recommended if your analytics is advanced/complex. We will be evolving pg_clickhouse over the coming months to support pushdown for more and more complex/advanced queries :)

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#42

Post by oulipo2 »

saisrirampur wrote: post #41
oulipo2 wrote: post #35

Earlier quoted context omitted.

Nice! Right now I'm using Timescaledb, do you think it makes sense to move to a Postgres+CH setup instead? or only if I hit the limit of timescaledb? Also what would be the benefit for me of querying clickhouse from Postgres, rather than directly through my backend via an ORM/SDK? is that because it would allow me to do JOINs? What would be the typical setup if I want to JOIN analytical data (eg my IoT device reading…

Great questions! ClickHouse is a purpose-built analytical database with thousands of optimizations for analytics, which is why it’s typically faster and more scalable than TimescaleDB. Here’s a post that covers real scenarios where users have moved workloads from Timescale to ClickHouse: https://clickhouse.com/blog/timescale-to-clickhouse-clickpip... If your operational (OLTP) tables are reasonably big, the recommend…

Very interesting! So right now I'm developing the backend, so I can still move analytics to CH, but I'm still wondering whether it would make sense because it might not be so large that it requires it (eg 50G/year of data I'd say)

And on the other hand, I can imagine that there could be plenty of footguns with replication to another database (not instant, what about schema changes, backfills, what if some database is shutdown for update while replicating, etc), so I'm a bit cautious about having a complex setup right now

Would you have some basic examples of a "mini-backend" Postgres+Clickhouse replication, using docker-compose + Typescript/Python or something, so I could play with it and take a look at what could be the operational complexity?

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#43

Post by saisrirampur »

oulipo2 wrote: post #42
saisrirampur wrote: post #41

Earlier quoted context omitted.

Great questions! ClickHouse is a purpose-built analytical database with thousands of optimizations for analytics, which is why it’s typically faster and more scalable than TimescaleDB. Here’s a post that covers real scenarios where users have moved workloads from Timescale to ClickHouse: https://clickhouse.com/blog/timescale-to-clickhouse-clickpip... If your operational (OLTP) tables are reasonably big, the recommend…

Very interesting! So right now I'm developing the backend, so I can still move analytics to CH, but I'm still wondering whether it would make sense because it might not be so large that it requires it (eg 50G/year of data I'd say) And on the other hand, I can imagine that there could be plenty of footguns with replication to another database (not instant, what about schema changes, backfills, what if some database is…

You should just give it a shot in 10-15 min and see how it looks with ClickHouse. We made it that simple with ClickPipes :). Don’t intend to sell here, but it is as simple as signing up for trial on ClickHouse Cloud and clicking a few buttons and start seeing PG data getting synced.

In regards to footguns with replication, totally understand you being cautious. Last 2 years at PeerDB/ClickPipes was laser focused on just Postgres CDC to provide a dead simple yet highly reliable experience. The product has 100s of features, addresses 100s of footguns and actively being enhanced. Sharing some customers using this production https://clickhouse.com/blog/postgres-cdc-year-in-review-2025... You should give it a shot to see how easy it is. :)

In regards to sample apication, here is one, https://github.com/ClickHouse/HouseClick It showcases PG + CH stack. We just merged a PR to integrate pg_clickhouse too. The good news is that, there is a blog planned in a couple of weeks which showcases a tightly integrated experience PG +CH with CDC and pg_clickhouse, all in OSS. It will have docker-compose too. Your question adds up to what we are thinking next, I couldn’t resist myself to reveal it. ;) :)

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#44

Post by oulipo2 »

saisrirampur wrote: post #43
oulipo2 wrote: post #42

Earlier quoted context omitted.

Very interesting! So right now I'm developing the backend, so I can still move analytics to CH, but I'm still wondering whether it would make sense because it might not be so large that it requires it (eg 50G/year of data I'd say) And on the other hand, I can imagine that there could be plenty of footguns with replication to another database (not instant, what about schema changes, backfills, what if some database is…

You should just give it a shot in 10-15 min and see how it looks with ClickHouse. We made it that simple with ClickPipes :). Don’t intend to sell here, but it is as simple as signing up for trial on ClickHouse Cloud and clicking a few buttons and start seeing PG data getting synced. In regards to footguns with replication, totally understand you being cautious. Last 2 years at PeerDB/ClickPipes was laser focused on j…

Nice! Looking forward to reading it!

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#45

Post by DetroitThrow »

__s wrote: post #30
DetroitThrow wrote: post #27

Earlier quoted context omitted.

>All with the goal of enabling users to build fast analytics from the Postgres layer itself but still using the power of ClickHouse! That would be incredible! So many times I want to reach for ClickHouse but whatever company I'm at has so much inertia built into PG. Pleease add CTE support. And yes I'm aware of PeerDB or whatever that project is called. This is still or even more helpful.

You're replying to the CEO of PeerDB. We recognize CDC is only one tool in the integration toolbox, which is why we're prioritizing this

I shouldn't be so flippant on here, of course I'm talking to the guy who wakes up and hears this every day.

I really appreciate the work that he and y'all are doing on both sides of the equation, it's great for every org that wants to use ClickHouse but can't.

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#47

Post by exagolo »

oulipo2 wrote: post #11

I'm using Postgres as my base business database, and thinking now about linking it to either DuckDb/DuckLake or Clickhouse... what would you recommend and why? I understand part of the interest of pg_clickhouse is to be able to use "pre-existing Postgres queries" on an analytical database without having to change anything, so if I am building my database now and have no legacy, would pg_clickhouse make sense, or shou…

If not having to adjust queries is a major driver for your considerations, then I would highly recommend looking at SQLGlot (https://github.com/tobymao/sqlglot), a transpiler that makes you (more) independent of query dialects. They already support 30 dialects (big vendors such as Snowflake, Databricks, BigQuery, but also loads of the specialists such as ClickHouse, SingleStore or Exasol). Repo is maintained extremely well.

Picking the best solution for your concrete workload (and your future demands) should be equally important to the implementation effort, to avoid that you run into walls later on. At least as long as data volume, query complexity or concurrency scalability can be challenges.

Post reply on HN