“Bad programmers worry about the code. Good programmers worry about data structures and their relationships.”Linus Torvalds · Mesaj pe lista de discuții git · 2006 · Mesaj pe lista de discuții git, 27 iunie 2006
Do not optimise the query; lay the data out so the query has nothing to read.
The B-tree index is the king of transactional databases: it finds a row in a few steps. Columnar warehouses work on a different principle: they do not look for a row, they skip blocks. Every column block carries a min and a max (zone map); if the filter asks for "March" and the block covers "June–July", the block is not read at all. Hence two levers. Partitioning — usually by date — eliminates whole directories before the query starts. Clustering (or the sort key) groups nearby values into the same blocks, so the zone maps have something to skip; without it, every block contains a bit of everything and none can be skipped. The design rules follow the filters, not the arrival of data: partition on what queries filter by, cluster on the second column in the WHERE. Too many partitions mean small files and metadata more expensive than the data; compact them. For equality on high-cardinality columns, Bloom filters; for repeated aggregates, materialised views; and fresh statistics remain the condition for any of this to be used. Torvalds was talking about code, but the rule holds: do not optimise the query; lay the data out so the query has nothing to read.
Everything so far explained how partitioning and sorting cut blocks. The next step is less obvious: these two levers are paid for at write time, not just at read time. When you choose the partition and the sort key, you are really deciding the order in which data lands on disk, permanently. An orders table partitioned by month and sorted by customer will be fast for monthly per-customer reports, but slow for any filter on product. That is why design works backwards: you look first at the questions that will be asked, only then at what the data looks like. A mundane example: you archive monthly invoices, but accountants always filter by supplier. If the sort key is the date, every supplier is scattered across all blocks. Change the sort key, and the same query reads a fraction of them. But what happens when data arrives unsorted, in a continuous stream, and you cannot sort it once?
In a stream, data arrives in arrival order, not in filter order. Each small file carries a wide range of values, so zone maps can no longer skip anything. The fix is not sorting at read time, but at write time, later: compaction. The system merges small files into large, sorted ones and rewrites the statistics. It is like tossing mail into a pile all day, then sorting it into folders at night. Queries running meanwhile pay for the pile, but queries after that read folders. A question remains: how often to compact, and how large should files be?
Why it matters The difference between a three-second query and a three-minute one on the same data is usually just the order in which it was written to disk.