What Is a Columnar Database?

A columnar database stores data in columns, rather than in the more common row-based database format. Storing data in columns results in performance gains for analytics queries and storage, due to the querying mechanism. Columnar databases are suitable for online analytical processing (OLAP) workloads and are a common core component of data infrastructure within modern organizations.

A columnar database groups columns of data together and stores them on the disk. Columns are categories of data, such as name, gender, and email. Traditional databases store the values of each category in sequence. Columnar databases store all the values associated with a specific column to maximize retrieval efficiency. Below, we share key characteristics of columnar data storage.

Column storage

Consider the following example of customer data.

Name	Age	Occupation
John	24	Marketer
Mary	53	CEO
Steph	36	Teacher

A column database stores the data by columns, like below:

[John, Mary, Steph, 24, 53, 36, Marketer, CEO, Teacher]

Each column occupies a contiguous block of storage. Meanwhile, a traditional row-based database stores one row of information after another. This results in various types of data placed sequentially one after another.

[John, 24, Marketer, Mary, 53, CEO, Steph, 36, Teacher]

Compression

Column-based databases group homogeneous data types together, resulting in efficient compression. For example, you can apply encoding to reduce storage space when a column contains primarily numbers, text, or Boolean values. This allows organizations to enable efficient data storage, especially for big data processing.

Vectorized query execution

Columnar databases enable modern CPUs to support large-scale data processing using the Single Instruction, Multiple Data (SIMD) instructions. By accessing data in batches rather than row-by-row, data analysts can achieve higher throughput when performing analytical queries.

Projection and late materialization

Unlike in a row-based database, applications can retrieve only referenced values rather than entire rows. For example, if a row contains 10 columns, the columnar storage format allows you to retrieve only 1 column when performing calculations. Unless necessary, the database will not retrieve the data for the entire row. This results in low input/output (I/O) usage and faster retrieval.

Database architects use different types of column-oriented databases for analytical applications.

Pure columnar

Pure columnar databases are designed to specifically ingest, organize, and store data in columns. They support OLAP applications such as machine learning and predictive analytics. For example, Amazon Redshift is a pure columnar database. Moderna, a biotechnology company, uses Redshift to securely access and analyze large amounts of data in real time.

Hybrid

Hybrid databases combine the characteristics of a traditional row and columnar database. For example, the Partition Attributes Across (PAX) storage is a hybrid database. It creates several horizontal partitions. Then, it groups data by columns within each partition.

Wide-column stores

Wide-column stores are databases that consist of several column families. Unlike columnar databases, a wide column store stores data in rows within each column family.

Columnar databases deliver exceptional performance for real-time analytics across large volumes of data.

Faster performance

Column-based storage retrieves only the columns required for aggregation. For example, you get the sum or median of customer spending without retrieving the entire row of data. By reducing the number of data points retrieved, you reduce the time required for analytics.

Higher compression

Columnar databases support advanced compression techniques because of their homogeneous arrangement of sequence data. With compression, adding more data to the storage doesn’t always result in an equivalent increase in space usage. Compared to relational databases, columnar storage delivers optimal performance even under heavy load.

Better cache utilization

Instead of processing data across multiple CPU cycles, modern CPUs can maximize computation performance using sequential columns. This allows organizations to achieve sub-second analytics, which are important for mission-critical applications.

Parallel processing at scale

Columnar database technologies allow organizations to scale petabyte-sized data processing across complex cloud environments. By storing data by column rather than row, columnar databases enable rapid querying of specific data attributes. Additionally, column-oriented storage resolves contention issues, where multiple applications access the same data page to retrieve different data.

Columnar databases excel in selective analytics across a narrow type of data. However, they might not perform equally well in certain scenarios.

Performance limitations for transactional workloads

A columnar database is not suitable for online transaction processing (OLTP) workloads that require frequent access to the entire row. Moreover, not all columnar databases support ACID properties, which is important for banking, retail, and other transactional applications. Atomicity, Consistency, Isolation, and Durability (ACID) is a database principle that ensures data integrity of every transaction.

High volume writes

When writing to a columnar database, all data for a column is written before moving to the next column. This means that write-heavy workloads will experience delays.

Unsuitable for frequent full-row retrievals

A columnar database doesn’t perform well for row-based operations. If you need to perform insert, update, or delete operations frequently, a row-based database is better.

Row-oriented databases store data sequentially on a disk. Writing, updating, and deleting records on a row-based data storage is fast. Development teams use row-oriented storage for applications that require heavy transactional processing. Meanwhile, columnar databases store columns of data in contiguous blocks.

Unlike row-oriented databases, columnar storage is designed for analytical workloads such as business intelligence (BI), machine learning, and real-time analytics. Because of their structure, columnar databases allow more effective data compression than row-oriented databases. A columnar database must read entire rows even if it only needs a few columns. Meanwhile, column-based databases read the exact columns they require.

Organizations use columnar databases to provide low-latency, high-throughput analytics capabilities for various use cases. We share common ones below.

Data warehousing

Data warehouses store massive amounts of data that organizations use to derive business insights. Columnar data storage allows connected applications to access large-scale historical data for complex data analysis. It performs optimally for queries that read only a few columns from a large number of rows.

Business intelligence and analytics

BI tools use a columnar database to run ad hoc queries against billions of rows of data. They can perform aggregations, filters, and group segregation to make predictions, trend analysis, and inform decision-making.

Log and event analytics

Columnar databases handle time-series and periodic events well. For example, a columnar data store can ingest Internet of Things (IoT) sensor data with very few fields. Organizations use the columnar data to identify irregular patterns, signal alerts, and predict future outcomes.

Machine learning feature stores

Training a machine learning model requires data preprocessing and feature extraction. Columnar databases expedite the ML workflow by grouping similar features in respective columns. Instead of retrieving the entire dataset, the model performs a batch read for a specific feature.

Financial and scientific analytics

Financial and scientific applications often ingest data that has a large number of columns. With a column data store, these applications can process attributes of interest without experiencing significant throughput limitations. For example, you can run risk modeling, genomics analysis, and regulatory reporting by integrating the analytics software with a cloud-based columnar database.

Database architects apply these practices to improve the performance of columnar data storage.

Choose keys aligned with common query filters

A misaligned key can negatively impact query performance. When choosing sort keys, make sure they’re aligned with columns that you frequently access. This way, you can skip entire blocks that don’t consist of the desired column.

Partition large tables by date or similar

To prevent excessive I/O usage, segregate columnar data by date. You can partition data by months or days, depending on data velocity. This allows a query to retrieve the specific partition instead of entire data blocks.

Use appropriate compression per column

Different data types experience optimal compression with specific encoding methods. For example, use dictionary encoding on string values and run-length encoding for status flags.

Bulk load by batch ingestion

Columnar databases are not made for frequent individual writes. Attempting to do so will degrade query throughput. Instead, copy large sequences of records into a file and load it into the database to prevent fragmented storage.

Vacuum and analyze tables regularly for query performance

Columnar databases grow in size over time as they ingest more data. Some data blocks might have drifted out of order, which increases read latency. Run regular audits and remove unnecessary data to improve read efficiency.

Design schemas for read performance

When running analytical queries, a columnar database performs better with a denormalized schema, such as a star or snowflake schema. It can better support join operations with a centralized table and surrounding dimension tables.

AWS offers a range of columnar database and analytics services designed to fit your use cases:

• Amazon Athena is an interactive query service that simplifies data analysis in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to set up or manage, and you only pay for the resources your query needs to run. Use Athena to process logs, perform data analytics, and run interactive queries.

• Amazon Redshift is a managed columnar warehouse that powers modern data analytics at scale with SQL for your data lakehouses. Query Amazon S3 data in open formats, removing data movement between lakes and warehouses.

Get started with columnar databases on AWS by creating a free account today.