Hacker News Reader

Comment by amelius | Hacker News Reader

I was wondering what is a columnar data store and found this on Wikipedia:

> by storing data in columns rather than rows, the database can more precisely access the data it needs to answer a query rather than scanning and discarding unwanted data in rows. Query performance is increased for certain workloads.

This seems true only in very specific circumstances. And why can't a database figure out the best way to store data, rather than let the user make the decision upfront (which they may later regret when requirements change)?

View on HN

Columnar data stores tend to be used in applications where performance is the primary concern. Abstractions tend to be leaky, but especially so when it comes to performance.

This is maybe comparable to the way high-performance applications are still usually written in C instead of higher-level languages. You'd think that at this point we could write compilers smart enough to generate more performant code than something written by a human hand, but in practice that often doesn't turn out to be true. Not yet anyway. And even when this is possible, due to their inevitably complexity, those abstractions tend to misbehave in unexpected ways, with unpredictable performance hiccups.

Columnar DBS are mostly used for analytical workloads, where you normally have wide tables (analytical data with lots of attributes) and want to query only some fields. You end up scanning most of the data, but because it is store in columns you only fetch the cols you need. In a regular DB you would fetch the complete wide row and then discard the unused cols.

Regular(oltp) databases are probably optimized for certain use cases, and changing a storage layout midway may not be feasible if the DB has to keep supporting lots of online txs. I'm no DB expert but that's my educated guess.

No, it's generally true for broad categories - OLTP vs. OLAP. These patterns, along with different data structure needs to serve their intended use cases, are why data warehouses are almost always separate from transactional systems.

Re: auto-optimizing databases: the DB would need to understand query patterns before it could optimize storage format. Is it possible? Almost certainly, to an extent. But not upfront.

Reply on news.ycombinator.com