10 ms·

Using ClickHouse to scale an events engine

237 points by wyndham 2y ago

mathnode 2y ago
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.
- hipadev23 2y ago
  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.
  - flessner 2y ago
    And I mean why should they? They work great for what they are made for and that is all that matters!
  - riku_iki 2y ago
    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)
    - nsguy 2y ago
      There are various knobs in ClickHouse that allow you to trade memory usage for performance. ( https://clickhouse.com/docs/en/operations/settings/query-complexity#settings-max_bytes_before_external_group_by 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...
      - riku_iki 2y ago
        > 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.
        alright2565 2y ago
        It's not actually so esoteric. The two main knobs are - max_concurrent_queries, since each query uses a certain amount of memory - max_memory_usage, which is the max per-query memory usage Here's my full config for running clickhouse on a 2GiB server without OOMs. Some stuff in here is likely irrelevant, but it's a starting point. diff --git a/clickhouse-config.xml b/clickhouse-config.xml index f8213b65..7d7459cb 100644 --- a/clickhouse-config.xml +++ b/clickhouse-config.xml @@ -197,7 +197,7 @@  - <max_connections>4096</max_connections> + <max_connections>2000</max_connections>  <keep_alive_timeout>3</keep_alive_timeout> @@ -270,7 +270,7 @@ -->  - <max_concurrent_queries>100</max_concurrent_queries> + <max_concurrent_queries>4</max_concurrent_queries>  - <mark_cache_size>5368709120</mark_cache_size> + <mark_cache_size>805306368</mark_cache_size>  -   <default>  - <max_memory_usage>10000000000</max_memory_usage> + <max_memory_usage>536870912</max_memory_usage> + + <queue_max_wait_ms>1000</queue_max_wait_ms> + <max_execution_time>30</max_execution_time> + <background_pool_size>4</background_pool_size> + <!-- How to choose between replicas during distributed query processing. random - choose random replica from set of replicas with minimum number of errors
  - silisili 2y ago
    As a caveat, I'd probably say 'at large volumes.' For a lot of what people may want to do, they'd probably notice very little difference between the three.
  - mathnode 2y ago
    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.
  - philippemnoel 2y ago
    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
    - pradeepchhetri 2y ago
      > 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.
dangoodmanUT 2y ago
deleting this comment because apparently jokes are not received well here
- mritchie712 2y ago
  > Recently, the most interesting rift in the Postgres vs OLAP space is [Hydra](https://www.hydra.so 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 Postgres in the next year or so. Many companies are working on it (Hydra, Parade[0], etc.) 0 - https://www.paradedb.com/ https://www.paradedb.com/
  - kapilvt 2y ago
    for others curious ParadeDB - AGPL License https://github.com/paradedb/paradedb/blob/dev/LICENSE https://github.com/paradedb/paradedb/blob/dev/LICENSE Hydra - Apache 2.0 https://github.com/hydradatabase/hydra/blob/main/LICENSE https://github.com/hydradatabase/hydra/blob/main/LICENSE also hydra seems derived from citusdata's columnar implementation.
    - mdaniel 2y ago
      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#license 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/LICENSE https://github.com/hydradatabase/hydra/blob/v1.1.2/columnar/...
      - alright2565 2y ago
        the only additional requirement that the AGPL introduces is that if you modify the AGPL software, you have to provide people who can access it over the network the code. If you just use a pre-built package, the AGPL has the exact same requirements as the GPL.
samber 2y ago
I'm curious: how many rows Lago store in its CH cluster? Do they collect data for fighting fraud? PG can handle a billion rows easily.
- JosephRedfern 2y ago
  Reading between the lines, given they're talking > 1 million rows per minute, I'd guess on the order of trillions of rows rather than billions (assuming they retain data for more than a couple of weeks)
- jacobsenscott 2y ago
  PG can handle billions of rows for certain use cases, but not easily. Generally you can make things work but you definitely start entering "heroic effort" territory.
- didip 2y ago
  OLAP databases need to be able to handle billions of rows per hour/day. I super love PG but PG is too far away from that.
mritchie712 2y ago
> Recently, the most interesting rift in the Postgres vs OLAP space is [Hydra](https://www.hydra.so 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 Postgres in the next year or so. There are a few companies are working on it (Hydra, Parade[0], tembo etc.). 0 - https://www.paradedb.com/ https://www.paradedb.com/
- riku_iki 2y ago
  > 0 - https://www.paradedb.com/ https://www.paradedb.com/ this looks like repackaging of datafusion as PG extension?..
  - mritchie712 2y ago
    yes, that's a succinct way to put it.
- snihalani 2y ago
  Have you seen: https://benchmark.clickhouse.com/ https://benchmark.clickhouse.com/
  - iimblack 2y ago
    That’s cool. Clickhouse and Alloy’s performances are impressive.
  - riku_iki 2y ago
    that benchmark is very weak, they used just 100M rows which is laughable, also no joins have been tested.
    - mritchie712 2y ago
      no joins is heavily favoring clickhouse (the creator of the benchmark). I'm not sure it's gotten better since I've seriously looked at them, but CH's join performance was really bad.
joshstrange 2y ago
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".
- nsguy 2y ago
  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.
  - joshstrange 2y ago
    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.
    - mosen 2y ago
      Have you looked into the ReplacingMergeTree table engine? (Although we still needed to use FINAL with this one)
    - hipadev23 2y ago
      CH works just fine for cleaning up rows: Delete with mutations sync=1, or use optimize with deduplicate by, or use aggregate trees and optimize final, or query aggregate tables with final=1. Numerous ways to achieve removal of old/stale rows.
    - 2y ago
HermitX 2y ago
Is ClickHouse a suitable engine for analyzing events? Absolutely, as long as you're analyzing a large table, its speed is definitely fast enough. However, you might want to consider the cost of maintaining an OSS ClickHouse cluster, especially when you need to scale up, as the operational costs can be quite high. If your analysis in Postgres was based on multiple tables and required a lot of JOIN operations, I don't think ClickHouse is a good choice. In such cases, you often need to denormalize multiple data tables into one large table in advance, which means complex ETL and maintenance costs. For these more common scenarios, I think StarRocks (www.StarRocks.io) is a better choice. It's a Linux Foundation open-source project, with single-table query speeds comparable to ClickHouse (you can check Clickbench), and unmatched multi-table join query speeds, plus it can directly query open data lakes.
- jakearmitage 2y ago
  > consider the cost of maintaining an OSS ClickHouse cluster I mean... it is pretty straightforward. 40~60 line Terraform, Ansible with templates for the proper configs that get exported from Terraform so you can write the IPs so they can see each other, and you are done. What else could you possibly need? Backing up is built into it with S3 support: https://clickhouse.com/docs/en/operations/backup#configuring-backuprestore-to-use-an-s3-endpoint https://clickhouse.com/docs/en/operations/backup#configuring... Upgrades are a breeze: https://clickhouse.com/docs/en/operations/update https://clickhouse.com/docs/en/operations/update People insist that OMG MAINTENANCE I NEED TO PAY THOUSANDS FOR MANAGED is better, when in reality, it is not.
drewda 2y ago
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.
- config_yml 2y ago
  Exactly, I've seen this at Sentry where you now have to run Kafka, Clickhouse, Redis, PG, Zookeeper, memcached and what have you. I get it, but the amount of baggage to handle is a bit difficult.
stephen123 2y ago
How were they doing millions of events per minute with postgres. I'm struggling with pg write performance ATM and want some tips.
- unixhero 2y ago
  Turn off indexing and other optimizations done on a table level
  - stephen123 2y ago
    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.
    - phantompeace 2y ago
      Could replicating to a DB with indexing (purely for queries) work?
      - remram 2y ago
        If one can't keep up, the other one can't either. You could use partitions though.
    - unixhero 2y ago
      You said you struggled with writes... so I mentioned an advice on how to speed up writes... the internet know a lot more about this than me tho
    - lmz 2y ago
      Isn't that basically the idea behind the "lambda architecture"? Of course you typically don't use the same product for both the real time and the batch aspects.
    - ndriscoll 2y ago
      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 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.
Valerie_Wilson 2y ago
[dead]
andretti1977 2y ago
I have a tangentially related question since I don’t use an Olap db: is deleting data so hard to perform? Is it necessarily an immutable storage? If so, is it a gdpr compliant storage solution? I am asking it since gdpr compliance may require data deletion (or at least anonimization)
- FridgeSeal 2y ago
  Columnar Db’s want stuff to be contiguous on disk, and deletes cause the rest of the data in that “block” to be rewritten (imagine deleting a chunk out of the middle of an excel table: you’ve got to move everything else up). This in turn, creates read+write load. Modern OLAP db’s often support it, often via mitigating strategies to minimise the amount of extra work they incur: mark tainted rows, exclude them from queries, and clean up asynchronously; etc.
jackbauer24 2y ago
scale is becoming more and more important, not just for cost, but also as a key technology feature to help deal with unexpected traffic and reduce the cost of manual operations.
alooPotato 2y ago
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?
- RadiozRadioz 2y ago
  Probably also because it is proprietary and only exists in one cloud platform.
  - wodenokoto 2y ago
    No, it’s because it’s google and HN are certain it will get cancelled at any moment.
    - alooPotato 2y ago
      seems unlikely, I think its the most popular google cloud product
- wodenokoto 2y ago
  I was quite surprised that other clouds don’t have an easy to get started analytics data warehouse solution like big query.
- mnahkies 2y ago
  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 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.
  - 2y ago
breadchris 2y ago
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_key=xxx 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/ https://www.scratchdb.com/ [2] https://www.scratchdb.com/blog/embeddables/ https://www.scratchdb.com/blog/embeddables/ [3] https://q29ksuefpvm.typeform.com/to/baKR3j0p?typeform-source=www.scratchdb.com#source=hero https://q29ksuefpvm.typeform.com/to/baKR3j0p?typeform-source...
- wiredfool 2y ago
  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.
  - hodgesrm 2y ago
    This is a nice illustration of the effects of different choices for storage layout and use of compute. ClickHouse blows away single-threaded queries on row-based data for analytic questions. On the other hand PostgreSQL can offer far higher throughput and concurrency when updating a shopping cart.

237 points by wyndham 2y ago

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.

hipadev23 2y ago
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.
- flessner 2y ago
  And I mean why should they? They work great for what they are made for and that is all that matters!
- riku_iki 2y ago
  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)
  - nsguy 2y ago
    There are various knobs in ClickHouse that allow you to trade memory usage for performance. ( https://clickhouse.com/docs/en/operations/settings/query-complexity#settings-max_bytes_before_external_group_by 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...
    - riku_iki 2y ago
      > 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.
      - alright2565 2y ago
        It's not actually so esoteric. The two main knobs are - max_concurrent_queries, since each query uses a certain amount of memory - max_memory_usage, which is the max per-query memory usage Here's my full config for running clickhouse on a 2GiB server without OOMs. Some stuff in here is likely irrelevant, but it's a starting point. diff --git a/clickhouse-config.xml b/clickhouse-config.xml index f8213b65..7d7459cb 100644 --- a/clickhouse-config.xml +++ b/clickhouse-config.xml @@ -197,7 +197,7 @@  - <max_connections>4096</max_connections> + <max_connections>2000</max_connections>  <keep_alive_timeout>3</keep_alive_timeout> @@ -270,7 +270,7 @@ -->  - <max_concurrent_queries>100</max_concurrent_queries> + <max_concurrent_queries>4</max_concurrent_queries>  - <mark_cache_size>5368709120</mark_cache_size> + <mark_cache_size>805306368</mark_cache_size>  -   <default>  - <max_memory_usage>10000000000</max_memory_usage> + <max_memory_usage>536870912</max_memory_usage> + + <queue_max_wait_ms>1000</queue_max_wait_ms> + <max_execution_time>30</max_execution_time> + <background_pool_size>4</background_pool_size> + <!-- How to choose between replicas during distributed query processing. random - choose random replica from set of replicas with minimum number of errors
- silisili 2y ago
  As a caveat, I'd probably say 'at large volumes.' For a lot of what people may want to do, they'd probably notice very little difference between the three.
- mathnode 2y ago
  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.
- philippemnoel 2y ago
  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
  - pradeepchhetri 2y ago
    > 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.

dangoodmanUT 2y ago

deleting this comment because apparently jokes are not received well here

mritchie712 2y ago
> Recently, the most interesting rift in the Postgres vs OLAP space is [Hydra](https://www.hydra.so 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 Postgres in the next year or so. Many companies are working on it (Hydra, Parade[0], etc.) 0 - https://www.paradedb.com/ https://www.paradedb.com/
- kapilvt 2y ago
  for others curious ParadeDB - AGPL License https://github.com/paradedb/paradedb/blob/dev/LICENSE https://github.com/paradedb/paradedb/blob/dev/LICENSE Hydra - Apache 2.0 https://github.com/hydradatabase/hydra/blob/main/LICENSE https://github.com/hydradatabase/hydra/blob/main/LICENSE also hydra seems derived from citusdata's columnar implementation.
  - mdaniel 2y ago
    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#license 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/LICENSE https://github.com/hydradatabase/hydra/blob/v1.1.2/columnar/...
    - alright2565 2y ago
      the only additional requirement that the AGPL introduces is that if you modify the AGPL software, you have to provide people who can access it over the network the code. If you just use a pre-built package, the AGPL has the exact same requirements as the GPL.

samber 2y ago

I'm curious: how many rows Lago store in its CH cluster? Do they collect data for fighting fraud? PG can handle a billion rows easily.

JosephRedfern 2y ago
Reading between the lines, given they're talking > 1 million rows per minute, I'd guess on the order of trillions of rows rather than billions (assuming they retain data for more than a couple of weeks)
jacobsenscott 2y ago
PG can handle billions of rows for certain use cases, but not easily. Generally you can make things work but you definitely start entering "heroic effort" territory.
didip 2y ago
OLAP databases need to be able to handle billions of rows per hour/day. I super love PG but PG is too far away from that.

mritchie712 2y ago

> Recently, the most interesting rift in the Postgres vs OLAP space is [Hydra](https://www.hydra.so 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 Postgres in the next year or so. There are a few companies are working on it (Hydra, Parade[0], tembo etc.). 0 - https://www.paradedb.com/ https://www.paradedb.com/

riku_iki 2y ago
> 0 - https://www.paradedb.com/ https://www.paradedb.com/ this looks like repackaging of datafusion as PG extension?..
- mritchie712 2y ago
  yes, that's a succinct way to put it.
snihalani 2y ago
Have you seen: https://benchmark.clickhouse.com/ https://benchmark.clickhouse.com/
- iimblack 2y ago
  That’s cool. Clickhouse and Alloy’s performances are impressive.
- riku_iki 2y ago
  that benchmark is very weak, they used just 100M rows which is laughable, also no joins have been tested.
  - mritchie712 2y ago
    no joins is heavily favoring clickhouse (the creator of the benchmark). I'm not sure it's gotten better since I've seriously looked at them, but CH's join performance was really bad.

joshstrange 2y ago

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

nsguy 2y ago
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.
- joshstrange 2y ago
  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.
  - mosen 2y ago
    Have you looked into the ReplacingMergeTree table engine? (Although we still needed to use FINAL with this one)
  - hipadev23 2y ago
    CH works just fine for cleaning up rows: Delete with mutations sync=1, or use optimize with deduplicate by, or use aggregate trees and optimize final, or query aggregate tables with final=1. Numerous ways to achieve removal of old/stale rows.
  - 2y ago

HermitX 2y ago

Is ClickHouse a suitable engine for analyzing events? Absolutely, as long as you're analyzing a large table, its speed is definitely fast enough. However, you might want to consider the cost of maintaining an OSS ClickHouse cluster, especially when you need to scale up, as the operational costs can be quite high. If your analysis in Postgres was based on multiple tables and required a lot of JOIN operations, I don't think ClickHouse is a good choice. In such cases, you often need to denormalize multiple data tables into one large table in advance, which means complex ETL and maintenance costs. For these more common scenarios, I think StarRocks (www.StarRocks.io) is a better choice. It's a Linux Foundation open-source project, with single-table query speeds comparable to ClickHouse (you can check Clickbench), and unmatched multi-table join query speeds, plus it can directly query open data lakes.

jakearmitage 2y ago
> consider the cost of maintaining an OSS ClickHouse cluster I mean... it is pretty straightforward. 40~60 line Terraform, Ansible with templates for the proper configs that get exported from Terraform so you can write the IPs so they can see each other, and you are done. What else could you possibly need? Backing up is built into it with S3 support: https://clickhouse.com/docs/en/operations/backup#configuring-backuprestore-to-use-an-s3-endpoint https://clickhouse.com/docs/en/operations/backup#configuring... Upgrades are a breeze: https://clickhouse.com/docs/en/operations/update https://clickhouse.com/docs/en/operations/update People insist that OMG MAINTENANCE I NEED TO PAY THOUSANDS FOR MANAGED is better, when in reality, it is not.

drewda 2y ago

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.

config_yml 2y ago
Exactly, I've seen this at Sentry where you now have to run Kafka, Clickhouse, Redis, PG, Zookeeper, memcached and what have you. I get it, but the amount of baggage to handle is a bit difficult.

stephen123 2y ago

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

unixhero 2y ago
Turn off indexing and other optimizations done on a table level
- stephen123 2y ago
  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.
  - phantompeace 2y ago
    Could replicating to a DB with indexing (purely for queries) work?
    - remram 2y ago
      If one can't keep up, the other one can't either. You could use partitions though.
  - unixhero 2y ago
    You said you struggled with writes... so I mentioned an advice on how to speed up writes... the internet know a lot more about this than me tho
  - lmz 2y ago
    Isn't that basically the idea behind the "lambda architecture"? Of course you typically don't use the same product for both the real time and the batch aspects.
  - ndriscoll 2y ago
    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 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.

Valerie_Wilson 2y ago

[dead]

andretti1977 2y ago

I have a tangentially related question since I don’t use an Olap db: is deleting data so hard to perform? Is it necessarily an immutable storage? If so, is it a gdpr compliant storage solution? I am asking it since gdpr compliance may require data deletion (or at least anonimization)

FridgeSeal 2y ago
Columnar Db’s want stuff to be contiguous on disk, and deletes cause the rest of the data in that “block” to be rewritten (imagine deleting a chunk out of the middle of an excel table: you’ve got to move everything else up). This in turn, creates read+write load. Modern OLAP db’s often support it, often via mitigating strategies to minimise the amount of extra work they incur: mark tainted rows, exclude them from queries, and clean up asynchronously; etc.

jackbauer24 2y ago

scale is becoming more and more important, not just for cost, but also as a key technology feature to help deal with unexpected traffic and reduce the cost of manual operations.

alooPotato 2y ago

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?

RadiozRadioz 2y ago
Probably also because it is proprietary and only exists in one cloud platform.
- wodenokoto 2y ago
  No, it’s because it’s google and HN are certain it will get cancelled at any moment.
  - alooPotato 2y ago
    seems unlikely, I think its the most popular google cloud product
wodenokoto 2y ago
I was quite surprised that other clouds don’t have an easy to get started analytics data warehouse solution like big query.
mnahkies 2y ago
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 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.
- 2y ago

breadchris 2y ago

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_key=xxx 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/ https://www.scratchdb.com/ [2] https://www.scratchdb.com/blog/embeddables/ https://www.scratchdb.com/blog/embeddables/ [3] https://q29ksuefpvm.typeform.com/to/baKR3j0p?typeform-source=www.scratchdb.com#source=hero https://q29ksuefpvm.typeform.com/to/baKR3j0p?typeform-source...

wiredfool 2y ago
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.
- hodgesrm 2y ago
  This is a nice illustration of the effects of different choices for storage layout and use of compute. ClickHouse blows away single-threaded queries on row-based data for analytic questions. On the other hand PostgreSQL can offer far higher throughput and concurrency when updating a shopping cart.