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

41–50 of 100 posts

Re: Using ClickHouse to scale an events engine

#41

Post by darthShadow »

joshstrange wrote: post #19
nsguy wrote: post #15

Earlier quoted context omitted.

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.

You have already gotten excellent options from the other comments, but here's another one that's not been mentioned yet.

You may want to consider adjusting your partition key (if feasible) as a function of datetime so you can just drop a complete partition when required, rather than needing separate delete queries.

In my experience, it has proven to be a very quick and clean way to clear out older data.

Re: Using ClickHouse to scale an events engine

#42

Post by drewda »

This change may make sense for Lago as a hosted multi-tenant service, as offered by Lago the company.

Simultaneously this change may not make sense for Lago as an open-source project self-hosted by a single tenant.

But that may also mean that it effectively makes sense for Lago as a business... to make it harder to self host.

I don't at all fault Lago for making decisions to prioritize their multi-tenant cloud offering. That's probably just the nature of running open-source SaaS these days.

Re: Using ClickHouse to scale an events engine

#43

Post by mathnode »

hipadev23 wrote: post #3
mathnode wrote: post #2

And if you use MariaDB, just enable columnstore. Why not treat yourself to s3 backed storage while you are there? It is extremely cost effective when you can scale a different workload without migrating.

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.

For multi-tb or pb needs I would not stray from mariadb. Especially when using columnstore. I have taken the pepsi challenge, even after trying vertica and netezza. Not HANA though; one has had enough of SAP.

Re: Using ClickHouse to scale an events engine

#44

Post by mdaniel »

kapilvt wrote: post #12
mritchie712 wrote: post #7

Earlier quoted context omitted.

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

Don't feel bad, lots of people get bitten by not reading all the way down to the bottom of their readme: https://github.com/hydradatabase/hydra/blob/v1.1.2/README.md... While Hydra may very well license their own code Apache 2, they ship the AGPLv3 columnar which to my very best IANAL understanding taints the whole stack and AGPLv3's everything all the way through https://github.com/hydradatabase/hydra/blob/v1.1.2/columnar/...

Re: Using ClickHouse to scale an events engine

#47

Post by unixhero »

joshstrange wrote: post #19
nsguy wrote: post #15

Earlier quoted context omitted.

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.

Tableau

Re: Using ClickHouse to scale an events engine

#48

Post by citrin_ru »

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…

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

Almost all clients (client libraries) allow a configurable timeout. In server settings there is a max query time settings which can be adjusted if necessary: https://clickhouse.com/docs/en/operations/settings/query-com...

Re: Using ClickHouse to scale an events engine

#49

Post by myrloc »

joshstrange wrote: post #19
nsguy wrote: post #15

Earlier quoted context omitted.

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.

Sounds like you need to use a ReplacingMergeTree + final keyword.

Re: Using ClickHouse to scale an events engine

#50

Post by bigger_cheese »

nsguy wrote: post #34
joshstrange wrote: post #19

Earlier quoted context omitted.

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.

You can always use different databases for different use cases. There are many applications that require extremely high insertion rates (millions of records per second), very large total number of rows (billions, trillions) and flexible/fast querying/aggregation with high read rates (100's of millions or higher rows/s) and that's sort of the sweet spot IMO for ClickHouse and where you'll be pressed to find alternativ…

>You can always use different databases for different use cases.

Unfortunately this is not always realistic, especially in large organizations, I know where I am there is a big push from top (i.e the IT budget people) to standardize everything they want to simplify licenses, support contracts etc.

I may not be doing cutting edge stuff (I work at an Industrial plant) but we do have mixed data use cases where it could be beneficial to use different dbs but realistically I don't see it happening.

Post reply on HN