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

71–80 of 100 posts

Re: Using ClickHouse to scale an events engine

#71

Post by philippemnoel »

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

Re: Using ClickHouse to scale an events engine

#72

Post by ndriscoll »

stephen123 wrote: post #55
unixhero wrote: post #46

Earlier quoted context omitted.

Turn off indexing and other optimizations done on a table level

What do you do to then query the data? I usually need indexes so queries are not slow. Perhaps I could insert into a staging table then bulk copy the data over to an indexed table, but that seems silly.

If your application language/framework allows, you can do the batching there. e.g. have your single request handler put work into an (in-memory) queue. Then another thread/async worker pull batches off the queue and do your db work in batch, and trigger the response to the original handler. In an http context, this is all synchronous from the client perspective, and you can get 2-10x throughput at a cost of like 2 ms latency under load.

I gave more detail with a toy example here: https://news.ycombinator.com/item?id=39245416

I've since played around with this a little more and you can do it pretty generically (at least make the worker generic where you give it a function `Chunk[A] => Task[Chunk[Result[B]]]` to do the database logic). I don't have that handy to post right now, but probably you're not using Scala anyway so the details aren't that relevant.

I've tried out a similar thing in Rust and it's a lot more finicky but still doable there. Should be similar in go I'd think.

Re: Using ClickHouse to scale an events engine

#73

Post by philippemnoel »

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.

That's true, but we're trying to change that at ParadeDB. Postgres is still way ahead of ClickHouse in terms of operational simplicity, ease of hiring for DBAs who are used to operating it at scale, ecosystem tooling, etc. If you can patch the speed and cost efficiency of Postgres for analytics to a level comparable to ClickHouse, then you get the best of both worlds

Re: Using ClickHouse to scale an events engine

#74

Post by Ozzie_osman »

stephen123 wrote: post #45

How were they doing millions of events per minute with postgres. I'm struggling with pg write performance ATM and want some tips.

If you're not already doing this: remove unnecessary indices, partition the table, batch your inserts/updates, or try COPY instead of INSERT.

Re: Using ClickHouse to scale an events engine

#75

Post by pradeepchhetri »

philippemnoel wrote: post #73
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.

That's true, but we're trying to change that at ParadeDB. Postgres is still way ahead of ClickHouse in terms of operational simplicity, ease of hiring for DBAs who are used to operating it at scale, ecosystem tooling, etc. If you can patch the speed and cost efficiency of Postgres for analytics to a level comparable to ClickHouse, then you get the best of both worlds

> Postgres is still way ahead of ClickHouse in terms of operational simplicity

Having served as both ClickHouse and Postgres SRE, I don't agree with this statement.

- Minimal downtime major version upgrades in PostgreSQL is very challenging.

- glibc version upgrade breaks postgres indices. This basically prevents from upgrading linux OS.

And there are other things which makes postgres operationally difficult.

Any database with primary-replica architecture is operationally difficult IMO.

Re: Using ClickHouse to scale an events engine

#77

Post by RadiozRadioz »

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?

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

Re: Using ClickHouse to scale an events engine

#78

Post by wodenokoto »

RadiozRadioz wrote: post #77
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?

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.

Re: Using ClickHouse to scale an events engine

#79

Post by wodenokoto »

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 was quite surprised that other clouds don’t have an easy to get started analytics data warehouse solution like big query.

Re: Using ClickHouse to scale an events engine

#80

Post by mnahkies »

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 also important to monitor your cost and watch for the point where switching from the per-tb queried pricing model to slots makes sense.

Post reply on HN