🧮Columnar Database

NEW

Also known as: Column-Oriented Database, Column Store, OLAP Database

A database that stores table data by columns rather than rows to accelerate analytical queries and compression.

DESCRIPTION

Columnar databases organize data by columns, enabling efficient scan, compression, and predicate pushdown for analytics. This layout benefits aggregations and selective projections by reading only relevant columns. Vectorized execution exploits CPU caches and SIMD for speed. Many cloud warehouses (e.g., BigQuery, Redshift) use columnar storage. Trade-offs include slower single-row writes and transaction-heavy workloads compared to row stores. Hybrid formats (Parquet, ORC) bring columnar benefits to data lakes. Design choices include sort keys, encoding types, and partitioning. Column stores often power OLAP, BI, and large-scale reporting.

ALTERNATIVE DEFINITIONS

Wikipedia

A column-oriented DBMS is a database management system that stores data tables by column rather than by row.

States the defining storage orientation difference succinctly.

Source

Google Cloud

BigQuery stores data in a columnar format for high-performance analytics.

Connects columnar storage to a specific cloud data warehouse implementation.

Source

RELATED TERMS

OLAP Compression Vectorized Execution Parquet ORC Data Warehouse MPP Predicate Pushdown Columnar Storage

COMPETING TERMS

Row Store

RELATIONS

• Broader: Database

KNOWLEDGE AREAS

Data Storage & Operations Data Warehousing & BI Data Engineering

TARGET AUDIENCE

Data Engineer Data Architect Cloud Architect BI Specialist Data Analyst Software Developer

EXTERNAL SOURCES

• Wikipedia - Column-oriented DBMS
• Amazon Redshift - Redshift columnar storage details.
• Apache Parquet - Columnar file format for analytics.

TOP QUESTIONS

Why do Columnar Databases compress data so well?

Adjacent values in a column often share types and ranges, enabling run-length encoding, dictionary encoding, and other efficient compression schemes.

Category: Concepts

When should you prefer a Columnar Database?

Choose columnar for analytics with wide tables, aggregations, and selective queries. It excels in BI, reporting, and Ad-hoc analysis.

Category: Requirements

What are limitations of Columnar Databases?

They are less suited for high-frequency OLTP writes and point updates. Batch ingestion and append-only patterns are more efficient.

Category: Limitations

How do Columnar Databases accelerate queries?

They scan only requested columns, apply predicate pushdown, and execute vectorized operations optimized for CPU caches and SIMD.

Category: Mechanism

How do Parquet and ORC relate to Columnar Databases?

They are columnar file formats bringing similar benefits to data lakes, often queried by engines like Spark and Trino.

Category: Relationship

What is a hot topic around Columnar Databases?

Convergence with lakehouse architectures and hardware acceleration using vectorized processing and GPUs is gaining momentum.

Category: Topic

What is a common mistake with Columnar Databases?

Ignoring sort/cluster keys and encoding choices can degrade performance significantly. Tune based on query patterns and data distribution.

Category: Mistakes

How does a Columnar Database compare to a Row Store?

Row stores favor transactional writes and point lookups; column stores favor scans, aggregations, and analytical workloads.

Category: Comparison

How to start with Columnar Databases?

Profile workloads, model tables for analytics, choose a warehouse or engine, and test compression and partitioning strategies.

Category: Getting started