At this stage it may be possible to build one's entire application stack inside of postgres extensions.
Pg_ClickHouse: A Postgres extension for querying ClickHouse
31–40 of 47 posts
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#32Earlier quoted context omitted.
You skip the backend entirely and query from the frontend. PostgREST and Postgres is your backend. If you want extra sauce on top you route those paths to an application that does whatever extra imperative operations you need.
So a kind of "mini-Firebase" ? and then you have security through row-based security? But this also means your users can generate their own queries, possibly doing some weird stuff taking down the db, so I assume it's more for "internal tools"?
Yeah definitely not for public facing things of any capacity.
No matter your size unless you have a trivial amount of data, if you expose a full SQL query language you can be hit be a DOS attack pretty trivially.
This ignores that row level security is also not enough on its own to implement an even moderately capable level of access controls.
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#33Earlier quoted context omitted.
Appreciate you chiming in! We evaluated almost all the FDWs and landed on clickhouse_fdw (built by Ildus) as the most mature option. However, it hadn’t been maintained since 2020. We used it as the base, and the goal is to take it to the next level. Our main focus is comprehensive pushdown capabilities. It was very surprising to see how much the Postgres FDW framework has evolved over the years and the number and typ…
>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.
Totally! Making things way easier on the app and query side is very important, which is why we plan to invest heavily in this going forward.
With respect to data replication, it gets really hard and has its challenges as data sizes grow - reliably moving tens of terabytes at speed, handling intricate quirks around replication slots, enterprise-grade observability etc. PeerDB/ClickPipes is designed to solve these problems. I wrote a blog post covering this in more detail here: https://clickhouse.com/blog/postgres-cdc-year-in-review-2025
That said, point taken - we will ensure query and app migration is seamless as well and reduce friction in integrating Postgres and ClickHouse. pg_clickhouse is a step in that direction! :)
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#34The name of the project is a reference to P. G. Wodehouse[0] for those unaware. [0] https://www.gutenberg.org/ebooks/author/783
That was my first impression as well.
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#35I'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…
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 readings) from CH with some business data (eg the user owning the device) from my Postgres? Would I replicate that business data to CH to do the join there, or would that be typically the exact use-case for pg_clickhouse?
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#36Earlier quoted context omitted.
I'm indeed already using Timescaledb, I was wondering if I would really gain something from adding clickhouse
I was using Timescale for a small project of mine and eventually switched to Clickhouse. While there was a 2-4x disk space reduction, the major benefits have operational (updates & backups). The documentation is much better since Timescale's mixes their cloud product documentation in, really muddying the water. Despite that, man it is really nice to be able to join your non-timeseries data in your queries (perhaps th…
Can you tell me more about why timescale doesn't perform in your opinion? My use case for timescale would be to gather my IoT telemetry data (perhaps 20/100 points per second) and store eg 1 year worth of it to do some analysis and query some past data, then offload that to parquet files on S3 for older data
I'd like to be able to use that for alert detection, etc, and some dashboard metrics, so I was thinking that it was the kind of perfect use-case for timescale, but because I haven't been using it yet "at scale" (not deployed yet) I don't know how it will behave
How do you do JOINs with business data for Clickhouse then? Do you have to do some kind of weird process where you query CH, then query Postgres, then join "manually" in your backend?
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#37The name of the project is a reference to P. G. Wodehouse[0] for those unaware. [0] https://www.gutenberg.org/ebooks/author/783
Hmm, no. It’s just like all the other postgres extensions named “pg_foo”, and the clear and obvious choice for “foo” in this case is “clickhouse”. Unless this is some bad joke that has flown over my head.
definitely a joke, not even that bad
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#38This is nice because there are a lot of clickhouse fdw implementations and none of them are well maintained from what I can tell.
(Note: we work closely with the clickhouse team so this is not to intended to detract from their launch, simply to point out maintained options.)
Our CH wrapper is actively maintained, with push down, parameterized views, and async streaming: https://supabase.github.io/wrappers/catalog/clickhouse/
We see a lot of companies choosing CH with PG - it’s fantastic
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#39Earlier quoted context omitted.
I was using Timescale for a small project of mine and eventually switched to Clickhouse. While there was a 2-4x disk space reduction, the major benefits have operational (updates & backups). The documentation is much better since Timescale's mixes their cloud product documentation in, really muddying the water. Despite that, man it is really nice to be able to join your non-timeseries data in your queries (perhaps th…
Can you tell me more about why timescale doesn't perform in your opinion? My use case for timescale would be to gather my IoT telemetry data (perhaps 20/100 points per second) and store eg 1 year worth of it to do some analysis and query some past data, then offload that to parquet files on S3 for older data I'd like to be able to use that for alert detection, etc, and some dashboard metrics, so I was thinking that i…
I was a little unclear, I think Timescale performs quite well. Just that in my (very limited) experience, Clickhouse performs better on the same data.
I actually have a blogpost on my experience with it here: https://www.wkrp.xyz/a-small-time-review-of-timescaledb/ that goes into a bit more detail as to my use case and issues I experienced. I'm actually half-way through writing the follow up using Clickhouse.
As detailed in the blog post, my data is all MMO video game stats such as item drops. With Timescale, I was able to join an "items" table with information such as the item name and image url in the same query as the "item_drops" table. This way the data includes everything needed for presentation. To accomplish the same in clickhouse, I create an "items" table and an "items_dict" dictionary (https://clickhouse.com/docs/sql-reference/dictionaries) that contains the same data. The Clickhouse query then JOINs the item_dict against item_drops to achieve the same thing.
If you know the shape of your data, you can probably whip up some quick scripts for generating fake versions and inserting into Timescale to get a feel for storage and query performance.
Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse
#40This is nice because there are a lot of clickhouse fdw implementations and none of them are well maintained from what I can tell.
(Note: we work closely with the clickhouse team so this is not to intended to detract from their launch, simply to point out maintained options.) Our CH wrapper is actively maintained, with push down, parameterized views, and async streaming: https://supabase.github.io/wrappers/catalog/clickhouse/ We see a lot of companies choosing CH with PG - it’s fantastic
Thank you, Paul! Great to see Supabase wrappers evolve. I really love the async streaming feature. It helps address use cases involving (reliably) moving larger datasets from ClickHouse to Postgres for supporting (stricter) transactional workloads.
Very excited to continue working closely to further integrate these amazing open source database technologies and make it easier for users. :)