Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Using ClickHouse to scale an events engine

github.com

Post reply on HN
Print view View original

11–20 of 100 posts

Re: Using ClickHouse to scale an events engine

#11

Post by riku_iki »

mritchie712 wrote: post #8

> Recently, the most interesting rift in the Postgres vs OLAP space is [Hydra]( https://www.hydra.so ), an open-source, column-oriented distribution of Postgres that was very recently launched (after our migration to ClickHouse). Had Hydra been available during our decision-making time period, we might’ve made a different choice. There will likely be a good OLAP solution (possibly implemented as an extension) in Post…

> 0 - https://www.paradedb.com/

this looks like repackaging of datafusion as PG extension?..

Re: Using ClickHouse to scale an events engine

#12

Post by kapilvt »

mritchie712 wrote: post #7
dangoodmanUT wrote: post #5

deleting this comment because apparently jokes are not received well here

> Recently, the most interesting rift in the Postgres vs OLAP space is [Hydra]( https://www.hydra.so ), an open-source, column-oriented distribution of Postgres that was very recently launched (after our migration to ClickHouse). Had Hydra been available during our decision-making time period, we might’ve made a different choice. There will likely be a good OLAP solution (possibly implemented as an extension) in Post…

for others curious

ParadeDB - AGPL License https://github.com/paradedb/paradedb/blob/dev/LICENSE

Hydra - Apache 2.0 https://github.com/hydradatabase/hydra/blob/main/LICENSE

also hydra seems derived from citusdata's columnar implementation.

Re: Using ClickHouse to scale an events engine

#13

Post by nsguy »

riku_iki wrote: post #10
hipadev23 wrote: post #3

Earlier quoted context omitted.

This is no shade to postgres or maria, but they don’t hold a candle to the simplicity, speed, and cost efficiency of clickhouse for olap needs.

I have tons of OOMs with clickhouse on larger than RAM OLAP queries. While postgres works fine (even it is slower, but actually returns results)

There are various knobs in ClickHouse that allow you to trade memory usage for performance. ( https://clickhouse.com/docs/en/operations/settings/query-com... e.g.)

But yes, I've seen similar issues, running out of memory during query processing, it's a price you pay for higher performance. You need to know what's happening under the hood and do more work to make sure your queries will work well. I think postgres can be a thousand or more times slower, and doesn't have the horizontal scalability, so if you need to do complex queries/aggregations over billions of records then "return result" doesn't cut it. If postgres addresses your needs then great- you don't need to use ClickHouse...

Re: Using ClickHouse to scale an events engine

#14

Post by joshstrange »

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse.

* Timeouts (only 30s???) unless I used the cli client

* Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns

* Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time

Again, I feel like we must be doing something wrong but we are paying an arm and a leg for that "privilege".

Re: Using ClickHouse to scale an events engine

#15

Post by nsguy »

joshstrange wrote: post #14

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse. * Timeouts (only 30s???) unless I used the cli client * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns * Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time Again, I feel like we mu…

What is your use case? If you're deleting rows that already feels like maybe it's not the intended use case. I think about clickhouse as taking in a firehose of immutable data that you want to aggregate/analyze/report on. Let's say a million records per second. I'll make up an example, the orientation, speed and acceleration of every Tesla vehicle in the world in real time every second.

Re: Using ClickHouse to scale an events engine

#16

Post by mritchie712 »

riku_iki wrote: post #11
mritchie712 wrote: post #8

> Recently, the most interesting rift in the Postgres vs OLAP space is [Hydra]( https://www.hydra.so ), an open-source, column-oriented distribution of Postgres that was very recently launched (after our migration to ClickHouse). Had Hydra been available during our decision-making time period, we might’ve made a different choice. There will likely be a good OLAP solution (possibly implemented as an extension) in Post…

> 0 - https://www.paradedb.com/ this looks like repackaging of datafusion as PG extension?..

yes, that's a succinct way to put it.

Re: Using ClickHouse to scale an events engine

#17

Post by shin_lao »

joshstrange wrote: post #14

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse. * Timeouts (only 30s???) unless I used the cli client * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns * Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time Again, I feel like we mu…

It's meant to store immutable data, and isn't great if you need low-latency updates. Also it's quirky in some ways.

Re: Using ClickHouse to scale an events engine

#18

Post by riku_iki »

nsguy wrote: post #13
riku_iki wrote: post #10

Earlier quoted context omitted.

I have tons of OOMs with clickhouse on larger than RAM OLAP queries. While postgres works fine (even it is slower, but actually returns results)

There are various knobs in ClickHouse that allow you to trade memory usage for performance. ( https://clickhouse.com/docs/en/operations/settings/query-com... e.g.) But yes, I've seen similar issues, running out of memory during query processing, it's a price you pay for higher performance. You need to know what's happening under the hood and do more work to make sure your queries will work well. I think postgres can…

> There are various knobs in ClickHouse that allow you to trade memory usage for performance.

but what knobs to use and what values to use in each specific case? Query just usually fails with some generic OOM message without much information.

Re: Using ClickHouse to scale an events engine

#19

Post by joshstrange »

nsguy wrote: post #15
joshstrange wrote: post #14

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse. * Timeouts (only 30s???) unless I used the cli client * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns * Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time Again, I feel like we mu…

What is your use case? If you're deleting rows that already feels like maybe it's not the intended use case. I think about clickhouse as taking in a firehose of immutable data that you want to aggregate/analyze/report on. Let's say a million records per second. I'll make up an example, the orientation, speed and acceleration of every Tesla vehicle in the world in real time every second.

It's to power all our analytics. We ETL data into it and some data is write-once so we don't have updates/deletes but a number of our tables have summary data ETL'd into them which means cleaning up the old rows.

I'm sure CH shines for insert-only workloads but that doesn't cover all our needs.

Re: Using ClickHouse to scale an events engine

#20

Post by joshstrange »

shin_lao wrote: post #17
joshstrange wrote: post #14

I feel like with all the Clickhouse praise on HN that we /must/ be doing something fundamentally wrong because I hate every interaction I have with Clickhouse. * Timeouts (only 30s???) unless I used the cli client * Cancelling rows - Just kill me, so many bugs and FINAL/PREWHERE are massive foot-guns * Cluster just feels annoying and fragile don't forget "ON CLUSTER" or you'll have a bad time Again, I feel like we mu…

It's meant to store immutable data, and isn't great if you need low-latency updates. Also it's quirky in some ways.

> It's meant to store immutable data

I don't disagree, I feel like we might be using it wrong. We were trying to replace ES with it but it just doesn't feel like it fits our needed usecase.

Post reply on HN