5 ms·

The basic techniques for implementing a fast column-store data warehouse have been well-known for 10 years. There are several excellent commercial and open-sour

The basic techniques for implementing a fast column-store data warehouse have been well-known for 10 years. There are several excellent commercial and open-source implementations of these techniques: - BigQuery - Snowflake - Redshift - Presto ClickHouse is not one of them. It doesn't have: - Transactions - Distributed joins - Separate compute from storage - UPDATE - User management I don't mean to be a jerk, I'm just trying to save people some time. Columnar DBs is well-trod territory and ClickHouse is way behind.

bretthoerner 8y ago
ClickHouse stable has both UPDATE and DELETE.
- ehfeng 8y ago
  Hi Brett
ehfeng 8y ago
I wouldn't call Redshift "excellent", nor would I call ClickHouse "way behind". ClickHouse was the best choice for my last employer's use case (https://twitter.com/zeeg/status/987009550501928960 https://twitter.com/zeeg/status/987009550501928960), after many other solutions were tested and benchmarked. Just because a tool doesn't have a specific feature checklist doesn't mean you should categorically rule it out, particularly if you don't have experience using/running/deploying it.
mamcx 8y ago
> The basic techniques for implementing a fast column-store Pointers to what them are?
- manigandham 8y ago
  Recent post: https://news.ycombinator.com/item?id=18076547 https://news.ycombinator.com/item?id=18076547
manigandham 8y ago
Redshift doesn't separate compute from storage either unless you're using Spectrum. Presto isn't a database at all and can read from many data stores. The rest are all cloud-hosted with lots of moving parts. MemSQL, Vertica, Actian, Greenplum, and SQL Server are better comparisons. ClickHouse is a column-oriented db and actually one of the most advanced, focusing on performance at all costs with lots of table storage engines that provide flexibility for your exact use-case. It also supports distributed joins and deletes but has some limitations they are working on. It can definitely use better tooling and compatibility though, but that's the tradeoff the core team made, and it seems to be working well for the companies that can afford the time and talent.
- georgewfraser 8y ago
  My point is there’s just no advantage to ClickHouse. The things that make it fast are in every column store. There’s other options that do everything it does and more.
  - manigandham 8y ago
    Speed is the advantage. It's very fast for a self-hosted system and probably only beaten by BigQuery for throughput. You're right though that most people don't need it and can get 90% of the speed with better usability with the other options.
  - bsg75 8y ago
    By this definition we should all be running Oracle.
dikei 8y ago
I wouldn't dismiss ClickHouse so quickly. There's no one-size-fit-all solution for data warehouse, everything has its own quirks.
theshadowknows 8y ago
We’re looking into Snowflake at my current job. Have you used it before?
- manigandham 8y ago
  Snowflake is great for smaller data volumes, but slow at supersized datasets. It has the best UX out of all the cloud-hosted options though, and the best JSON handling. Interesting billing model based on running time that's a good fit for focused data querying sessions using BI tools where you can scale up while in use and turn off after. Tables are stored as S3 data making sharing, cloning, and snapshot queries easy. Major missing features are lack of streaming (although they have a auto loading option from S3 files) and no stored procedures. Transactions are also tricky and client drivers are sometimes neglected because everything is a wrapper around their HTTP/JSON interface.
PeterZaitsev 8y ago
There is quite a difference between theoretical technologies and stable high-performance implementation. Majority of things ClickHouse does are very well known it just does them Here is example Performance comparison we did at Percona https://www.percona.com/blog/2017/02/13/clickhouse-new-opensource-columnar-database/ https://www.percona.com/blog/2017/02/13/clickhouse-new-opens...

bretthoerner 8y ago

ClickHouse stable has both UPDATE and DELETE.

ehfeng 8y ago
Hi Brett

ehfeng 8y ago

I wouldn't call Redshift "excellent", nor would I call ClickHouse "way behind". ClickHouse was the best choice for my last employer's use case (https://twitter.com/zeeg/status/987009550501928960 https://twitter.com/zeeg/status/987009550501928960), after many other solutions were tested and benchmarked. Just because a tool doesn't have a specific feature checklist doesn't mean you should categorically rule it out, particularly if you don't have experience using/running/deploying it.

mamcx 8y ago

> The basic techniques for implementing a fast column-store Pointers to what them are?

manigandham 8y ago
Recent post: https://news.ycombinator.com/item?id=18076547 https://news.ycombinator.com/item?id=18076547

manigandham 8y ago

Redshift doesn't separate compute from storage either unless you're using Spectrum. Presto isn't a database at all and can read from many data stores. The rest are all cloud-hosted with lots of moving parts. MemSQL, Vertica, Actian, Greenplum, and SQL Server are better comparisons. ClickHouse is a column-oriented db and actually one of the most advanced, focusing on performance at all costs with lots of table storage engines that provide flexibility for your exact use-case. It also supports distributed joins and deletes but has some limitations they are working on. It can definitely use better tooling and compatibility though, but that's the tradeoff the core team made, and it seems to be working well for the companies that can afford the time and talent.

georgewfraser 8y ago
My point is there’s just no advantage to ClickHouse. The things that make it fast are in every column store. There’s other options that do everything it does and more.
- manigandham 8y ago
  Speed is the advantage. It's very fast for a self-hosted system and probably only beaten by BigQuery for throughput. You're right though that most people don't need it and can get 90% of the speed with better usability with the other options.
- bsg75 8y ago
  By this definition we should all be running Oracle.

dikei 8y ago

I wouldn't dismiss ClickHouse so quickly. There's no one-size-fit-all solution for data warehouse, everything has its own quirks.

theshadowknows 8y ago

We’re looking into Snowflake at my current job. Have you used it before?

manigandham 8y ago
Snowflake is great for smaller data volumes, but slow at supersized datasets. It has the best UX out of all the cloud-hosted options though, and the best JSON handling. Interesting billing model based on running time that's a good fit for focused data querying sessions using BI tools where you can scale up while in use and turn off after. Tables are stored as S3 data making sharing, cloning, and snapshot queries easy. Major missing features are lack of streaming (although they have a auto loading option from S3 files) and no stored procedures. Transactions are also tricky and client drivers are sometimes neglected because everything is a wrapper around their HTTP/JSON interface.

PeterZaitsev 8y ago

There is quite a difference between theoretical technologies and stable high-performance implementation. Majority of things ClickHouse does are very well known it just does them Here is example Performance comparison we did at Percona https://www.percona.com/blog/2017/02/13/clickhouse-new-opensource-columnar-database/ https://www.percona.com/blog/2017/02/13/clickhouse-new-opens...