3 ms·
One thing I've seen about other columnar databases (e.g. Clickhouse) is that they're bad at k/v style lookups. Why is that? Your explanation would make me think
by ollien 3y ago
One thing I've seen about other columnar databases (e.g. Clickhouse) is that they're bad at k/v style lookups. Why is that? Your explanation would make me think otherwise, as we would be able to quickly scan the columns using our sequential reads, ideally.
- smallerfish 3y ago1) If they're only using on disk indices, it'll be slower to look up any one record than it would be if the index was in memory (particularly true if a good chunk of the data was in memory also) 2) The operation of retrieving a full row of data is more expensive in a column based db than in a row based db, because in row based the data for the row is contiguous on disk. So row based is likely optimal if your data size works with that architecture. (And, to be clear, row based _can_ work with billions of rows, if you're thoughtful about what kind of querying you'll be doing against such tables, and how you maintain them.)
- NortySpock 3y agoThink of columnar data as being run-length compressed. (If you have 100 rows of "1 Fizz" followed by 200 rows of "1 Buzz", followed by one "1 Fizz" you can store that as "100:1:Fizz;200:1:Buzz;1:1:Fizz".) Then, a mere 30 bytes of IO + a quick sum tells you that you sold 101 Fizz and 200 Buzz from the widget factory this quarter. Maybe the first Fizz is how many you sold in the USA and the second Fizz is how many you sold in Canada, so it becomes easy to do by-region reporting. However, unpacking the column that has all your individual transaction IDs and linking it to the individual single sale of one Fizz takes a lot of lookups. So untangling individual transactions becomes slow (though not impossible).