"a database that keeps each record's details together can't take advantage of that: to read two columns, it has to read all five."
My understanding is that modern databases will take advantage of that if you create an index of those fields - ie it'll read from the index, not the records.
We generally refer to an index that holds the data it indexes at its leaves a "clustered index". But this only means that the tuples are organized according to the ordering of the index: you still generally store the entire record (all columns/attributes) together.
For example, if I cluster on my sorted primary key, that just means that the index can jump to the tuple with the correct primary key quickly (log time), but you will still end up reading* the entire tuple in the same way I explain in the blog. And if you only store a record ID that points to the other fields instead of the entire tuple at the leaves of the index, then that wouldn't be considered a clustered index.
I will note, however, that DBMSs should be smart enough to know that an aggregation on only an indexed column does not need to fetch the rest of the columns, only the data at the leaves of the index.
Well, yes. One of the things an index can do is column-oriented storage.
Not really modern either.. Oracle/Sybase/SQL Server have been doing that already in the 90s.. including indexed views for aggregation
What they recently (and some of these were added in late 2000s) added is columnstore indexes... with compression and also in memory (Sybase Hana, SQL Server In Memory aka Hekaton, Oracle IM column store)
One can argue each view of a database is merely a derivate of the WAL, and therefore every view is an index. The major benefit of columnar databases is the data stored being continuous in memory. Avoid all serialize/deserialize and control flow logic. Most core index data structures in relational DBs do not support this kind of tight vector view, Arrow[1]is a format that does.
Theoretically one can have a custom index extension which stores the data in a column-oriented manner, yet then one has major duplication, which is a problem for petabyte-scale databases, and is an even bigger problem in the Cloud.
KDB+ is the best implementation of a columnar DB in my opinion. Simple C, and one can understand how the data is stored down to the DRAM cells. Durability is just MMAP, which is fine as OLAP DBMSs tend not to be ACID.
In my personal opinion one shall learn from the best. The best in the world in massively parallel vectorized computation are the people at OpenAI and Anthropic, and the people who built PyTorch. Much of their work can be applied to analytical DBMSs.
(Draft, of course the topic is much more nuanced)
[1] https://arrow.apache.org/