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

21–30 of 47 posts

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#21

Post by oulipo2 »

jascha_eng wrote: post #19
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…

I'm indeed already using Timescaledb, I was wondering if I would really gain something from adding clickhouse

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#22

Post by saisrirampur »

oulipo2 wrote: post #21
jascha_eng wrote: post #19

Earlier quoted context omitted.

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…

I'm indeed already using Timescaledb, I was wondering if I would really gain something from adding clickhouse

More on use-cases involving TimescaleDB replication/migration to ClickHouse https://clickhouse.com/blog/timescale-to-clickhouse-clickpip...

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#23

Post by oulipo2 »

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…

Nice, what would be your typical setup?

You keep like 1 year's worth of data in your "business database", and then archive the rest in S3 with parquet and query with DuckDB ?

And if you want to sync everything, even "current data", to do datascience/analytics, can you just write the recent data (eg the last week of data or whatever) in S3 every hours/days to get relatively up-to-date data? And doesn't that cause the S3 data to grow needlessly (eg does it replace, rather than store an additional copy of recent data each hour?)

Do you have kind of "starter project" for a Postgres + DuckLake integration that I could look at to see how it's used in practice, and how it makes some operations easier?

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#24

Post by Ritewut »

saisrirampur wrote: post #18
mritchie712 wrote: post #16

Earlier quoted context omitted.

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, to…

I tested DuckDB and even Motherduck and this was my takeaway. Square hole, round peg situation.

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#25

Post by mritchie712 »

oulipo2 wrote: post #23
mritchie712 wrote: post #16

Earlier quoted context omitted.

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…

Nice, what would be your typical setup? You keep like 1 year's worth of data in your "business database", and then archive the rest in S3 with parquet and query with DuckDB ? And if you want to sync everything, even "current data", to do datascience/analytics, can you just write the recent data (eg the last week of data or whatever) in S3 every hours/days to get relatively up-to-date data? And doesn't that cause the…

Once you have meltano installed, it's just be:

    ```
    meltano run tap-postgres target-ducklake
    ```
Setting up meltano would be a bit more involved[0]

0 - https://www.notion.so/luabase/Postgres-to-DuckLake-example-2...

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#27

Post by DetroitThrow »

saisrirampur wrote: post #8
tempest_ wrote: post #7

This is nice because there are a lot of clickhouse fdw implementations and none of them are well maintained from what I can tell.

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.

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#28

Post by wkrp »

oulipo2 wrote: post #21
jascha_eng wrote: post #19

Earlier quoted context omitted.

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…

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 the fdw will allow this for clickhouse? I need to look into that). If you don't have to deal with the operations side too much and performance isn't a problem, Timescale is really nice.

Re: Pg_ClickHouse: A Postgres extension for querying ClickHouse

#30

Post by __s »

DetroitThrow wrote: post #27
saisrirampur wrote: post #8

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

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
Post reply on HN