[HN Gopher] Pg_ClickHouse: A Postgres extension for querying Cli...
___________________________________________________________________
Pg_ClickHouse: A Postgres extension for querying ClickHouse
Author : spathak
Score : 52 points
Date : 2025-12-10 17:14 UTC (2 days ago)
(HTM) web link (clickhouse.com)
(TXT) w3m dump (clickhouse.com)
| graovic wrote:
| This is pretty good. It will allow us to use PostgREST as an API
| endpoint to query the ClickHouse database directly
| justtheory wrote:
| Ooh, neat idea!
| saisrirampur wrote:
| Good idea! Btw, ClickHouse does provide a HTTP interface
| directly, too! https://clickhouse.com/docs/interfaces/http
| oulipo2 wrote:
| What 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 wrote:
| 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.
| onedognight wrote:
| The name of the project is a reference to P. G. Wodehouse[0] for
| those unaware.
|
| [0] https://www.gutenberg.org/ebooks/author/783
| justtheory wrote:
| LOL
| sevg wrote:
| 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.
| justtheory wrote:
| I will never un-see it now, tbh
| tempest_ wrote:
| This is nice because there are a lot of clickhouse fdw
| implementations and none of them are well maintained from what I
| can tell.
| saisrirampur wrote:
| 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 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-fi...
| All with the goal of enabling users to build fast analytics
| from the Postgres layer itself but still using the power of
| ClickHouse!
| oulipo2 wrote:
| 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 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 wrote:
| 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 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! :)
___________________________________________________________________
(page generated 2025-12-12 23:00 UTC)