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

81–90 of 100 posts

Re: Using ClickHouse to scale an events engine

#81

Post by breadchris »

ClickHouse is awesome, but as the post shows, some code is involved in getting the data there.

I have been working on Scratchdata [1], which makes it easy to try out a column database to optimize aggregation queries (avg, sum, max). We have helped people [2] take their Postgres with 1 billion rows of information (1.5 TB) and significantly reduce their real-time data analysis query time. Because their data was stored more efficiently, they saved on their storage bill.

You can send data as a curl request and it will get batch-processed and flattened into ClickHouse:

curl -X POST "http://app.scratchdata.com/api/data/insert/your_table?api_ke..." --data '{"user": "alice", "event": "click"}'

The founder, Jay, is super nice and just wants to help people save time and money. If you give us a ring, he or I will personally help you [3].

[1] https://www.scratchdb.com/ [2] https://www.scratchdb.com/blog/embeddables/ [3] https://q29ksuefpvm.typeform.com/to/baKR3j0p?typeform-source...

Re: Using ClickHouse to scale an events engine

#82

Post by doo_daa »

alooPotato wrote: post #76

We use BigQuery a lot for internal analytics and we've been super happy. I don't see a lot of love for BigQuery on HN and I wonder why. Tons of features, no hassle and easy to throw a bunch of TB at it. I guess maybe the cost?

We are lucky enough to be able to run BigQuery with flat rate billing. It's incredibly powerful and it's a really good example of SaaS and Serverless done right. It just works.

Re: Using ClickHouse to scale an events engine

#83

Post by alooPotato »

wodenokoto wrote: post #78
RadiozRadioz wrote: post #77

Earlier quoted context omitted.

Probably also because it is proprietary and only exists in one cloud platform.

No, it’s because it’s google and HN are certain it will get cancelled at any moment.

seems unlikely, I think its the most popular google cloud product

Re: Using ClickHouse to scale an events engine

#84

Post by alooPotato »

mnahkies wrote: post #80
alooPotato wrote: post #76

We use BigQuery a lot for internal analytics and we've been super happy. I don't see a lot of love for BigQuery on HN and I wonder why. Tons of features, no hassle and easy to throw a bunch of TB at it. I guess maybe the cost?

I'm a big fan of big query as well, but the cost can be problematic if you're not careful. Generally speaking I've found it manageable if you make good use of partitioning and do incremental aggregation (we use dbt, though you have to do some macro gymnastics to make the partition key filter eligible for pruning due to restrictions on use of dynamic values https://docs.getdbt.com/docs/build/incremental-models ) It's…

yeah between partitioning, clustering, materialized views, and smart tuning it seems like there are enough knobs to control costs.

Re: Using ClickHouse to scale an events engine

#85

Post by hodgesrm »

joshstrange wrote: post #36
hipadev23 wrote: post #35

Earlier quoted context omitted.

It sounds like you’re probably using Clickhouse Cloud? If so, I was not impressed. Overly pushy sales people, pricing isn’t competitive, and they’re trying to cater to the snowflake/databricks crowd without smoothing any rough edges (like the default timeout being enabled on a GUI). Overall I’d say CH isn’t as tolerant or forgiving as BigQuery, Snowflake, or Databricks. You can write the worst SQL possible and BQ wil…

We are using Altinity. I believe it's a 3-server cluster and we have 2 clusters (our prod one and another one we are trying to promote to production once our data integrity checks pass, at which point we will spin the other down).

Hi Josh, sorry to hear about the issues. Sounds like things that should be solvable. ClickHouse does require thinking a bit differently from other stores like BigQuery. I sent you an email. Feel free to contact me at the email posted on my HN account. Looking forward to helping you bend ClickHouse to your will.

Re: Using ClickHouse to scale an events engine

#86

Post by Mortiffer »

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…

so Paradedb and Hydra are using same codebase or just similar approach ?

Re: Using ClickHouse to scale an events engine

#87

Post by riku_iki »

snihalani wrote: post #69
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…

Have you seen: https://benchmark.clickhouse.com/

that benchmark is very weak, they used just 100M rows which is laughable, also no joins have been tested.

Re: Using ClickHouse to scale an events engine

#88

Post by wiredfool »

breadchris wrote: post #81

ClickHouse is awesome, but as the post shows, some code is involved in getting the data there. I have been working on Scratchdata [1], which makes it easy to try out a column database to optimize aggregation queries (avg, sum, max). We have helped people [2] take their Postgres with 1 billion rows of information (1.5 TB) and significantly reduce their real-time data analysis query time. Because their data was stored…

My first big win for clickhouse was replacing a 1.2tb, billion + row postgresql DB with clickhouse. It was static data with occasional full replacement loads. We got the DB down to ~ 60GB, with query speeds about 45x faster.

Now, the postgres schema wasn't ideal, and we could have saved ~ 3x on it with corresponding speed increases for queries with a refactor similar to the clickhouse schema, but that wasn't really enough to move the needle to near real-time queries.

Ultimately, the entire clickhouse DB was smaller than the original postgres primary key index. The index was too big to fit in memory on an affordable machine, so it's pretty obvious where the performance is coming from.

Re: Using ClickHouse to scale an events engine

#89

Post by riku_iki »

philippemnoel wrote: post #71
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…

ParadeDB founder here. You can see how we compare to other Postgres-based analytical offerings on ClickBench here: https://blog.paradedb.com/pages/introducing_analytics

[flagged]

Re: Using ClickHouse to scale an events engine

#90

Post by ddorian43 »

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…

I don't think `tembo` is working on it though, probably just hosting an existing extension.
Post reply on HN