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

11–20 of 47 posts

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#11

Post by oulipo2 »

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?

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#12

Post by oulipo2 »

graovic wrote: post #2

This is pretty good. It will allow us to use PostgREST as an API endpoint to query the ClickHouse database directly

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)

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#13

Post by lillecarl »

oulipo2 wrote: post #12
graovic wrote: post #2

This is pretty good. It will allow us to use PostgREST as an API endpoint to query the ClickHouse database directly

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)

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.

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#14

Post by saisrirampur »

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…

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! :)

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#15

Post by justtheory »

sevg wrote: post #10
onedognight wrote: post #5

The 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.

I will never un-see it now, tbh

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#16

Post by mritchie712 »

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…

We released a meltano target for DuckLake[0]. dlt has one now too. Pretty easy to sync pg -> ducklake.

I've been really happy with DuckLake, happy to answer any questions about it.

DuckDB has always felt easier to use vs. Clickhouse for me, but both are great options. If I were you, I'd try both options for a few hours with your use case and pick the one that feels better.

0 - https://www.definite.app/blog/target-ducklake

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#17

Post by jascha_eng »

lillecarl wrote: post #13
oulipo2 wrote: post #12

Earlier quoted context omitted.

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)

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.

This 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.

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#18

Post by saisrirampur »

mritchie712 wrote: post #16
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…

We released a meltano target for DuckLake[0]. dlt has one now too. Pretty easy to sync pg -> ducklake. I've been really happy with DuckLake, happy to answer any questions about it. DuckDB has always felt easier to use vs. Clickhouse for me, but both are great options. If I were you, I'd try both options for a few hours with your use case and pick the one that feels better. 0 - https://www.definite.app/blog/target-duc…

I love DuckDB from a product perspective and appreciate the engineering excellence behind it. However, DuckDB was primarily built for seamless for in-process analytics, data science, data-preparation/ETL workloads than real-time customer facing analytics.

ClickHouse’s bread and butter is real-time analytics for customer-facing applications, which often come with demanding concurrency and latency requirements.

Ack, totally makes sense that both are amazing technologies - you could try both and test them at the scale your real-time application may reach, and then choose the technology that best fits your needs. :)

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#19

Post by jascha_eng »

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…

Depending on your workload you might also be able to use Timescale to have very fast analytical queries inside postgres directly. That avoids having to replicate the data altogether.

Note that I work for the company that built timescale (Tiger Data). Clickhouse is cool though, just throwing another option into the ring.

Tbf in terms of speed Clickhouse pulls ahead on most benchmark, unless you want to join a lot with your postgres data directly then you might benefit from having everything in one place. And of course you avoid the sync overhead.

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#20

Post by oulipo2 »

lillecarl wrote: post #13
oulipo2 wrote: post #12

Earlier quoted context omitted.

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)

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"?

Post reply on HN