-1

Why do so many people prefer columnar databases? To my understanding, people prefer columnar databases because they are faster for querying due to the fact that the cpu would be able to cache the next values predictably.

3 Answers 3

2

Generally, there are three main reasons columnar data storage is preferred for data warehousing and analytics workloads:

  1. Column storage typically compresses better than row storage because values are all of the same data type and often have similar values. Improved compression can benefit both in-memory and persistent storage.

  2. Queries often select only a few columns from a table. Rather than fetching the entire row from storage into memory, only the necessary columns are loaded. This further reduces I/O and memory usage.

  3. Modern CPUs are very much faster than main memory. Significant performance improvements can be found by making better use of the relatively small, but high-speed, on-chip caches, and using vector CPU instructions like SIMD on similar data. Populating cache lines with column data is usually more efficient than loading entire rows, where many of the values are not needed by the query.

Row storage is preferred for transactional workloads, where small numbers of records can be found quickly with b-tree seeks. There are also write benefits because a single row can be written all at once, whereas columnar storage needs to update multiple structures separately and usually has a higher CPU overhead for compression.

1

To my understanding, people prefer columnar databases because they are faster for querying due to the fact that the cpu would be able to cache the next values predictably.

My understanding in this area, is that columnar databases are faster for certain access patterns, due to a single column of data being sequential in storage (i.e. on disk).

A specific case I've heard being more efficient, is when the primary workload is aggregating subranges of time-series data - as might be typical of certain kinds of financial analysis.

I don't understand all the details of implementations or their areas of application, but I suspect there isn't one simple and decisive reason why columnar databases are faster for certain workloads, but a number of marginal improvements across different layers and functions of the database implementation when under concurrent load.

Columnar databases are definitely for special applications however, and are not faster in general for OLTP loads.

0

How are columnar databases faster for querying?

They're not, holistically speaking. They just offer a different way of storing data, and when used properly, can be efficient for specific use cases. Non-columnar specific databases can be used in an equally efficient manner when properly used.

Why do so many people prefer columnar databases?

In over a decade of my career, I've rarely come across anyone who had any specific preference to columnar databases over non-columnar specific databases. (Note I keep using this term specifically because there are rowstore focused database systems that also offer columnar data storage on top of the rowstore table*, such as Microsoft SQL Server.)

To my understanding, people prefer columnar databases because they are faster for querying due to the fact that the cpu would be able to cache the next values predictably.

I don't believe that's correct. I've never heard of that before and honestly am having a hard time conceptualizing the meaning of that.

If the speedup comes from caching, wouldn't row-based databases be as effective for querying?

Sure. Typical rowstore database systems leverage Memory to automatically cache commonly used data to improve performance.

The few people I've spoken to who were interested in columnar specific databases were people who didn't seem to understand how databases work so well and were trying to migrate their OLTP workflows to something like Snowflake, which is silly. I suspect you've talked to similarly minded people. Don't get me wrong, there's nothing explicitly wrong with a columnar database, but they aren't magically more performant than a rowstore database system, just the same as NoSQL isn't automatically faster either. And when used improperly, you'll have a bad time, regardless of which type of system you choose.


* SQL Server 2014 and later also offer columnar storage as primary storage ('clustered columnstore') where no underlying rowstore is used). From 2016 SP1, all editions support columnstore. Before that, Enterprise Edition was required.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.