4 ms·
> There are several good analytical databases that will give you the same scan performance The notion that you will get approximately the same query performanc
by dunkelheit 7y ago
> There are several good analytical databases that will give you the same scan performance
The notion that you will get approximately the same query performance with all column stores is false. There can easily be an order of magnitude difference depending on the implementation. Take GROUP BY as a paradigmatic example of what OLAP stores do. Of course the way to implement GROUP BY is with a hash table but little tricks make all the difference and a lot of love went into the clickhouse implementation. Just to give you a taste: there is a custom hash table with specializations for different key types (e.g. it will store a precomputed hash for strings but not for integers and use it to speed up equality test). Variable length data is stored in arenas to reduce allocator pressure. Data will of course be aggregated in several hash tables in different threads and then merged together, but if there is a lot of keys each table will additionally be sharded so that the merge step can be performed in parallel too.
Of course you shouldn't trust random claims on the internet that clickhouse is fast and should do a small case study yourself. Then you'll appreciate how easy is to setup a clickhouse instance or a small cluster. It can easily slurp up most common formats. It is just a single binary with minimal dependencies that will run as-is on any modern linux. There is just a single node type (compare this to druid madness).
You are right that there is a lot of limitations and, how should I put it, quirks. This is resoundingly not a general-purpose database and someone used to the comforts of e.g. postgres will encounter some nasty surprises. Bugs are unfortunately common, especially in the newer functionality. But performance is its main feature and it makes many users of clickhouse put up with its limitations.
- jfim 7y ago> Of course the way to implement GROUP BY is with a hash table but little tricks make all the difference and a lot of love went into the clickhouse implementation. Actually, for low cardinality columns, _not_ using a hash table will speed up things. For example, a dictionary-encoded column for states might have a few values in its dictionary (1 => CA, 2 => FL, 3 => NY) and the data looks like an array of numbers (eg. [1, 1, 1, 2, 1, 3, 3, 3, 1, 3, 2]). The fastest way to aggregate is to actually use an array, as the dictionary index conveniently maps to an array index. Then, when it comes to merging several of those arrays, they're turned into hash tables. Combine enough of those optimizations and proper data layouts, and you end up with several orders of magnitude of performance differences between engines.
- hodgesrm 7y agoIn addition to excellent raw scan rates for reasons given above ClickHouse has materialized views, which can re-order and/or preaggregate data. This can speed up query response by 3 orders of magnitude over queries on the source data. See https://www.altinity.com/blog/clickhouse-materialized-views-illuminated-part-1 https://www.altinity.com/blog/clickhouse-materialized-views-... + ClickHouse docs for an intro. As the parent says, try it yourself on your own data. That's all that counts. Disclaimer: I wrote the blog article and we sell support for ClickHouse.
- georgewfraser 7y agoAll of the optimizations you describe are in every columnar analytical database built in the last 10 years, from Vertica to Snowflake.
- dunkelheit 7y agoProbably you are right about vertica and snowflake (where can I browse the source code to be sure?) but with these clickhouse competes on price.