What is a Columnar Database?
One of the biggest challenges in business analytics is the slow performance when working in traditional row-oriented databases.
Because of its ability to highly compress large amounts of data, the columnar database has been a catalyst for faster business analytics.
A columnar database writes and reads data much more efficiently than row-oriented databases and can move very quickly to and from data sources, accelerating the time it takes to retrieve a query.
The columnar database is one of the key building blocks of big data analytics.
The Benefits of Using a Columnar Database
Storing data by the column, instead of by the row, makes many of your queries run much faster because instead of looking row by row, you can skip multiple fields and hone in on only the most relevant data.
For example, if you wanted to look up the balance paid to all suppliers, you can navigate directly to the ‘balance’ field, skipping over the invoice, address and other irrelevant data that would only slow you down.
Most organizations have a large, growing number of disparate data sources. As your dataset grows, a columnar database becomes even more important for maintaining speed and agility in your business analytics.
For technology and software development expert, Dennis Forbes, the biggest benefit of using a columnar database is its ability to compress large datasets:
“Column-oriented databases are very cool, and they have a role to play in data that is much larger than available RAM, or where naive aggregates or stream processing is paramount. They’re often found for specific purposes in the financial industry, and there are some absolutely fantastic products.”
The Drawbacks of Using a Columnar Database
Some queries rely on detailed row data. For example, if you wanted to look up the name, address and balance of each of your suppliers, and you needed to do this frequently, you may be better off with a row-oriented database.
Another drawback is that writing new data takes more time in a columnar database because each column needs to be entered individually, compared to a row-oriented database where you can insert new data. This is why organizations who use online transaction processing (OLTP) applications are wise to use row-oriented databases. Most transactions involve reading and writing a small number of records at a time.
It’s also why many organizations prefer to use a row-oriented database like Postgres or MySQL running in the backend and a columnar database for front-end BI needs.
Unless you have a big demand for row-specific data or OLTP, columnar databases are the best option for fast, dynamic business analytics.
You can read more about the benefits of using a columnar database here – OLAP.