Comparison
Columnar formatvsOLAP
Columnar format
the query reads three columns out of two hundred and only touches the bytes for those three.
A file layout that stores all the values of one column together rather than storing rows contiguously. Two things follow, and they are why analytics runs on it: you can skip columns entirely, and values of the same type sitting next to each other compress far better. The cost is on the other side — reading or writing a single whole row touches every column's region, which is why these formats are wrong for transactional workloads.
Full entry →OLAP
your query reads three columns across two billion rows and nobody minds if it takes forty seconds.
The workload shape of analytics: few columns, enormous row counts, aggregation rather than point lookup. Columnar storage serves it because it reads only the columns referenced and compresses each one well. Mixing it onto the transactional database is the most common cause of an unexplained latency spike on the write path.
Full entry →