3 ms·

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 s

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.

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.