OLTP vs. OLAP and Columnar Databases

Not all databases are built for the same purpose. The way you store and query data for a banking transaction is fundamentally different from how you analyse years of sales data.

OLTP — Online Transaction Processing

OLTP databases handle high volumes of small, real-time transactions. Every time you click “Buy Now” on an e-commerce site, an OLTP database records the order.

Characteristics:

• High concurrency (thousands of small queries per second)
• Row-oriented storage (fast reads/writes for individual records)
• ACID compliant (strict consistency)
• Normalised schema (reduces redundancy)

Examples: PostgreSQL, MySQL, SQL Server, Oracle.

OLAP — Online Analytical Processing

OLAP databases handle complex queries over large historical datasets. Data analysts run reports like “total revenue by region over the last 5 years.”

Characteristics:

• Low concurrency (few but heavy queries)
• Column-oriented storage (fast aggregation over specific columns)
• Eventually consistent (tolerates stale data)
• Denormalised schema (star/snowflake schemas for fast joins)

Examples: Redshift, Snowflake, BigQuery, ClickHouse.

Row vs. Columnar Storage

The fundamental difference is how data is physically stored on disk.

Aspect	Row-Oriented	Column-Oriented
Storage	Entire row stored together	Each column stored separately
Best for	Many columns per row, frequent inserts	Few columns per row, large scans
Read Pattern	SELECT * where id = X	SELECT SUM(sales) by region
Compression	Poor (mixed data types per block)	Excellent (same type per block)
Indexing	B-Tree, Hash	Zone maps, min/max sketches

Example: A table with 100 columns. Row storage reads all 100 columns even if you only need 3. Columnar storage reads only the 3 columns you need — massively less I/O for analytical queries.

The Row-Oriented Query

-- OLTP: fetch a single order by ID (row storage excels)
SELECT * FROM orders WHERE order_id = 12345;

The Columnar Query

-- OLAP: aggregate millions of rows (columnar excels)
SELECT region, SUM(revenue)
FROM sales
WHERE year = 2024
GROUP BY region;

HTAP — Hybrid Transactional/Analytical Processing

Modern databases like PostgreSQL (with Citus) and MySQL HeatWave blur the line between OLTP and OLAP. They aim to handle both workloads in a single system, though there are always trade-offs.

Choosing the Right System

Use Case	Best Fit
E-commerce checkout	OLTP (PostgreSQL, MySQL)
Monthly financial reports	OLAP (Snowflake, Redshift)
Real-time dashboards	OLAP with streaming (ClickHouse)
Mixed workload (mostly OLTP)	PostgreSQL with read replicas for analytics
Fraud detection (real-time scans)	Columnar with low-latency (Druid, Pinot)