6 ms·
Pg_ClickHouse: A Postgres extension for querying ClickHouse
- graovic 10mo agoThis is pretty good. It will allow us to use PostgREST as an API endpoint to query the ClickHouse database directly
- justtheory 10mo agoOoh, neat idea!
- saisrirampur 10mo agoGood idea! Btw, ClickHouse does provide a HTTP interface directly, too! https://clickhouse.com/docs/interfaces/http https://clickhouse.com/docs/interfaces/http
- deleted 10mo ago[deleted]
- oulipo2 10mo agoWhat are the typical uses of PostgREST? is it just when you want to make your database accessible to various languages over HTTP because you don't want to use an ORM and connect to your db? But besides that, for an entreprise solution, why would you use PostgREST to develop your backend rather than, say, use an ORM in your language and make direct queries? (honest question)
- lillecarl 10mo agoYou 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.
- jascha_eng 10mo agoThis always sounds super messy to me but I guess supabase is kind of the same thing and especially for side projects it seems like a very efficient setup.
- oulipo2 10mo agoSo 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"?
- charrondev 10mo agoYeah 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.
- onedognight 10mo agoThe name of the project is a reference to P. G. Wodehouse[0] for those unaware. [0] https://www.gutenberg.org/ebooks/author/783 https://www.gutenberg.org/ebooks/author/783
- justtheory 10mo agoLOL
- sevg 10mo agoHmm, 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.
- justtheory 10mo agoI will never un-see it now, tbh
- __s 10mo agodefinitely a joke, not even that bad
- DetroitThrow 10mo ago"I am never wrong, sir" -onedognight
- yayitswei 10mo agoThat was my first impression as well.
- tempest_ 10mo agoThis is nice because there are a lot of clickhouse fdw implementations and none of them are well maintained from what I can tell.
- saisrirampur 10mo agoAppreciate 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 types of hooks it now provides for push down. This is why we decided to lean into FDW than build an extension bottoms up. But we may still do that within pg_clickhouse for a few features, wherever FDW framework becomes a restriction. We’ve made notable progress over the last few months, including support for pushdown of custom aggregations and SEMI JOINs/basic subqueries. Fourteen of twenty-two TPCH queries are now fully pushdownable. We’ll be doubling down to add pushdown support for much more complex queries, CTEs, window functions, and more. More on the future here - https://github.com/ClickHouse/pg_clickhouse?tab=readme-ov-file#road-map https://github.com/ClickHouse/pg_clickhouse?tab=readme-ov-fi... All with the goal of enabling users to build fast analytics from the Postgres layer itself but still using the power of ClickHouse!
- DetroitThrow 10mo ago>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.
- __s 10mo agoYou'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
- oulipo2 10mo agoI'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 should I do analytics differently? Also, would you have some kind of tutorial / sample setup of a typical business application in Postgres and kind of replication in clickhouse to make analytics queries? so I can see how Clickhouse would be typically used?
- saisrirampur 10mo agoGreat 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 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 ClickHouse, you can start querying those tables from within Postgres using pg_clickhouse. In ClickHouse Cloud, we offer ClickPipes for Postgres CDC/replication, which is a managed service version of PeerDB and is tightly integrated with ClickHouse. Now there could be non-transcational tables that you can directly ingest to ClickHouse and still query using pg_clickhouse. So TL;DR: Postgres for OLTP; ClickHouse for OLAP; PeerDB/ClickPipes for data replication; pg_clickhouse as the unified query layer. We are actively working on making this entire stack tightly integrated so that building real-time apps becomes seamless. More on that soon! :)
- oulipo2 10mo agoNice! 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?
- Olshansky 10mo agoAdded to https://github.com/Olshansk/postgres_for_everything https://github.com/Olshansk/postgres_for_everything.
- N_Lens 10mo agoAt this stage it may be possible to build one's entire application stack inside of postgres extensions.
- justtheory 10mo agoYes. YES! That's the idea. :-)