Columnar Database VS Relational Database

Columnar databases and relational databases represent two major approaches for storing and querying data. While both enable accessing related data through queries, they use fundamentally different storage layouts and data processing engines.

Columnar databases store data by column rather than by row and are optimized for analytical workloads. Relational databases use a row layout suitable for transactional applications.

Let’s compare them in detail across various aspects.

Columnar Databases vs Relational Databases

1- Storage Layout

Columnar Storage

• Stores each column separately instead of rows
• Only data for queried columns are retrieved
• Achieves high compression due to data similarity
• New indexes can be added without affecting data

Relational Row Storage

• Stores complete rows containing all columns
• Entire rows are retrieved even if only few columns queried
• Lower compression due to heterogeneous data
• Adding indexes directly affects the base tables

Query Performance

Column Stores

• Analytic queries run faster due to column-wise storage
• Aggregations performed on compressed data
• Vectorized query processing minimizes interpreting overhead
• Distributed querying can scale to massive datasets

Relational Databases

• Optimized for fast lookups and point queries
• Row-wise storage efficient for transactional access
• Advanced optimizations for join processing
• MVCC provides snapshot isolation for transactions

Updates

Columnar Databases

• Updates affect only related columns, but still complex
• Deletes require additional tombstones
• Batch inserts easier than row-wise databases
• Not optimized for single row updates

Relational Databases

• In-place updates provided, single row modifications easy
• MVCC provides transactional atomicity
• Table-level locking enables concurrency
• Optimized for OLTP workloads

Compression

Columnar Compression

• Highly effective due to data similarity within columns
• Simple dictionary and run-length encoding very efficient
• Compression rates of 10x or higher versus row storage
• Analytics done on compressed data without decompressing

Relational Database Compression

• Lower redundancy across heterogeneous row data
• General compression algorithms like LZ4 or ZLIB used
• Typical compression ratios of 2x – 5x
• Queries require decompressing data first

Data Integrity

Column Stores

• Integrity enforced through schema and metadata
• Lack of multi-row transactions affects consistency
• Denormalization common to avoid expensive joins
• No declarative constraints like foreign keys

Relational Databases

• Declarative constraints like PK, FK enforce integrity
• ACID transactions ensure consistency
• joins provide flexibility without denormalization
• MVCC prevents dirty reads

Use Cases

Columnar Databases

• Analytic applications like BI and reporting
• Building aggregated data warehouses
• Running analytics on Big Data
• Applications needing high query performance

Relational Databases

• Transactional systems and OLTP
• Applications requiring row-level atomicity
• Low latency queries for operational apps
• Constraint and referential integrity needs

Query Language

Columnar Databases

• Support SQL in most implementations like BigQuery, Redshift
• Some use proprietary query languages
• Can also support NoSQL APIs for key-value access

Relational Databases

• SQL is the standard language for queries
• Advanced SQL standards support transactions, joins, constraints
• Stored procedures and triggers extend SQL functionality

Examples

Column Stores

• Google BigQuery
• Amazon Redshift
• Apache Kudu
• Apache Parquet
• Cassandra wide column store

Relational Databases

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle
• IBM DB2
• Sqlite

Architectural Differences

Fundamentally, columnar and row-oriented databases have very different architectures.

Columnar Architectures

• Separated column storage and processing
• Vectorized query execution
• Column-specific compression algorithms
• Column metadata for row membership
• Distributed storage and parallel processing

Relational Database Architectures

• Tables stored as rows on disk pages
• Row-at-a-time query processing
• Buffer pool caching frequently accessed rows
• B+ tree indexing to find rows fast
• Transactions and locking for consistency

Advantages of Columnar Databases

• Analytic queries faster, up to 100x or more
• High compression reduces storage needs
• Easy to add new analytic indices
• Scales to massive data volumes across clusters
• Good fit for BI/analytics, “data lake” use cases

Advantages of Relational Databases

• Fast point queries and single row lookups
• ACID compliant transactions
• Row-level locking enables concurrency
• SQL standards for queries, integrity
• Optimal for OLTP applications

Here is a comparison table summarizing the key differences between columnar databases and relational databases:

Parameter	Columnar Databases	Relational Databases
Storage Model	Column-oriented	Row-oriented
Query Performance	Very fast for analytics	Fast for transactions
Compression	Very high due to column similarity	Moderate due to row heterogeneity
Updates	Slow, entire columns affected	Fast, single row updates
Joins	Require sortedness or indexes	Fast due to row storage
Transactions	No multi-row transactions	Full ACID compliance
Use Cases	Analytics & BI	Transactions & OLTP
Examples	BigQuery, Redshift, Vertica	MySQL, PostgreSQL, SQL Server

Bottom Line

Columnar and row-oriented relational databases are optimized for different workloads. Column stores like BigQuery and Redshift are designed for high performance analytics across huge datasets. They store data by column to maximize compression and analytic query speeds.

Relational databases like MySQL are better suited for transactional applications needing fast inserts, updates and accurate point queries. Their row storage, indexing, locking, and SQL standards enable fast OLTP processing.