The Problem

Modern systems require careful consideration of storage architecture. The choice between row-oriented and columnar storage fundamentally impacts performance, scalability, and cost. Yet many experienced engineers lack a clear understanding of OLTP vs OLAP—the underlying storage paradigms that drive these design decisions.

When designing distributed systems, microservices, or data-intensive applications, you need to understand how data storage format determines what operations are fast and what operations are slow. This isn’t just academic knowledge—it directly affects your system’s ability to scale, your infrastructure costs, and your team’s ability to deliver performant solutions.

This post explains the three main storage types: row storage (OLTP), columnar storage (OLAP), and hybrid storage (HTAP). We’ll focus on the architectural trade-offs and when each approach makes sense in production systems.

Row Storage (OLTP) - “The Transactional Workhorse” (PostgreSQL, MySQL, MongoDB, SQLite)

How It Works

Think of row storage like a filing cabinet. Each folder (row) contains all the information about one entity. When you need someone’s complete record, you grab their folder and everything is right there.

Schema (stored separately in metadata): ID | Name | Email | Age | City

---
title: "Rows Stored Contiguously on Disk"
---
packet
0-3: "1"
4-11: "Alice"
12-27: "alice@example.com"
28-31: "30"
32-35: "NYC"
36-39: "2"
40-47: "Bob"
48-63: "bob@example.com"
64-67: "25"
68-71: "SF"
72-75: "3"
76-83: "Carol"
84-99: "carol@example.com"
100-103: "35"
104-107: "LA"

In row storage, data is stored row-by-row. The schema (column names and types) is stored separately in the database metadata - it’s not repeated with each row. Each row contains only the actual values, stored contiguously on disk. This layout is optimized for reading entire records: when you request one row, the database reads a single disk page (typically 4KB or 8KB) and gets all the field values at once. Since hard drives read data in blocks, having all fields together means one disk read operation retrieves the complete record - that’s why row storage is fast for transactional queries that need entire records.

Why It’s Fast for Transactions

Example: Update Bob’s name and email

Imagine you need to update Bob’s name to “Robert” and change his email. With row storage:

  1. Find Bob’s row - Database locates Bob’s record (ID=2)
  2. Read one disk page - Gets the entire row: 2, Bob, bob@example.com, 25, SF (all fields together in one 4KB-8KB block)
  3. Update the row - Modify name and email in memory
  4. Write one disk page - Write the updated row back to disk

Total I/O: 1 page read + 1 page write = 2 disk operations

All of Bob’s data is in the same disk block, so you read and write everything together. This is why row storage excels at transactional operations - you’re working with complete records stored contiguously.

Other fast operations:

  • Look up a user by ID → 1 page read
  • Insert a new order → 1 page write
  • Delete a customer record → 1 page read + 1 page write

Row storage is perfect for transactional workloads where you’re frequently reading and writing individual records.

The Problem: What Row Storage Struggles With

Example: Calculate average age of all users

Now imagine you need to calculate the average age across all users. With row storage:

  1. Read every row - Database must read all rows: 1, Alice, alice@example.com, 30, NYC, then 2, Bob, bob@example.com, 25, SF, then 3, Carol, carol@example.com, 35, LA, etc.
  2. Extract age values - From each row, extract only the age column (30, 25, 35, …)
  3. Calculate average - Process the extracted ages

The problem: You’re reading entire rows just to use one column. If each row has 20 columns but you only need 1 (age), you’re reading 20x more data than necessary.

Disk I/O amplification: With 1 million users and 20 columns per row:

  • What you need: 1 million age values
  • What you read: 20 million values (all columns from all rows)
  • Waste: 95% of your I/O bandwidth is reading data you don’t need

This is why analytical queries on row storage databases slow down dramatically as data grows. You’re paying the cost of reading entire rows when you only need a few columns.

So how do we solve this? Enter columnar storage.

Columnar Storage (OLAP) - “The Analytics Powerhouse” (ClickHouse, Snowflake, Amazon Redshift, Google BigQuery)

How It Works

Columnar storage is like transposing a spreadsheet. Instead of storing all columns together for each row, it stores all values for each column together.

---
title: "Columnar Storage - All Values Per Column Stored Together"
---
packet
0-3: "1"
4-7: "2"
8-11: "3"
12-19: "Alice"
20-27: "Bob"
28-35: "Carol"
36-51: "alice@example.com"
52-67: "bob@example.com"
68-83: "carol@example.com"
84-87: "30"
88-91: "25"
92-95: "35"
96-99: "NYC"
100-103: "SF"
104-107: "LA"

Schema (stored separately in metadata): ID | Name | Email | Age | City

Notice how all ID values are stored together (1, 2, 3), then all Name values (Alice, Bob, Carol), then all Email values, etc. This is the opposite of row storage where all fields for one row are stored together. To read one complete row, you’d need to read from multiple column files and combine them.

When you query for the average age, the database:

  1. Reads only the age column file
  2. Skips all other columns entirely
  3. Processes the age values directly

This is dramatically more efficient for analytical queries.

Why It’s Fast for Analytics

Example: Calculate average age of all users

Imagine you need to find the average age across all users. With columnar storage:

  1. Read only the Age column - Database reads just the age values: 30, 25, 35, ... (all ages together)
  2. Skip all other columns - ID, Name, Email, City columns are never touched
  3. Process ages directly - Calculate average from the age values

Total I/O: Read only the Age column file = minimal disk operations

If you have 1 million users with 20 columns, you read 1 million age values, not 20 million values (all columns). You’re reading only what you need.

Columnar storage shines when:

  • You need to aggregate data (SUM, AVG, COUNT) across many rows
  • Your queries touch only a few columns
  • You’re reading large amounts of data

Compression benefits: Since all values in a column are the same type, compression works exceptionally well. Columnar storage achieves significantly better compression than row storage because similar values cluster together and compression algorithms work better on homogeneous data. This means:

  • Less disk I/O (reading compressed data)
  • More data fits in memory
  • Lower storage costs

Why It Struggles for Transactions

Example: Update Bob’s name and email

Now imagine you need to update Bob’s name to “Robert” and change his email. With columnar storage:

  1. Find Bob’s position - Database must locate Bob’s row position (row 2) in each column
  2. Read Name column - Read the entire Name column file to find Bob’s position
  3. Read Email column - Read the entire Email column file to find Bob’s position
  4. Update Name column - Write updated name to Name column file
  5. Update Email column - Write updated email to Email column file

Total I/O: Multiple column file reads + multiple column file writes = many more disk operations

Updating one row requires touching multiple column files. This is why columnar databases often use append-only designs or batch updates. They’re optimized for reads, not writes.

When to Use Columnar Storage

  • Complex analytical queries (reporting, business intelligence)
  • Aggregations over large datasets
  • Read-heavy workloads (data warehouses, analytics dashboards)
  • Historical data analysis where data doesn’t change frequently

When to Use Columnar Storage

  • Complex analytical queries (reporting, business intelligence)
  • Aggregations over large datasets
  • Read-heavy workloads (data warehouses, analytics dashboards)
  • Historical data analysis where data doesn’t change frequently

HTAP (Hybrid) - A Brief Note

HTAP (Hybrid Transactional/Analytical Processing) systems attempt to handle both OLTP and OLAP workloads in a single database. They typically maintain both row and columnar storage formats, routing queries to the appropriate engine.

Why they’re not popular: Most teams prefer separate OLTP and OLAP systems for operational simplicity. HTAP systems add complexity - managing dual workloads in one system, resource contention between analytical and transactional queries, and higher costs.

The standard approach: Enterprise systems typically use both storage types. An ETL/ELT pipeline asynchronously moves data from the operational database (OLTP) to the analytics database or data warehouse (OLAP). This keeps transactional workloads fast while enabling powerful analytics without impacting production systems.

When HTAP makes sense: Only when you need real-time analytics on live transactional data and can afford the operational complexity. Examples: TiDB, SingleStore, SAP HANA.

Why Storage Format Matters at Scale

I/O Amplification

The fundamental issue: row storage reads entire rows even when you need one column.

Imagine a table with 1 billion rows and 20 columns. To calculate the sum of one column:

  • Row storage: Reads 1 billion rows × 20 columns = 20 billion values from disk
  • Columnar storage: Reads 1 billion values (just the one column)

That’s a 20x difference in I/O. At scale, this becomes the difference between a query that takes seconds versus hours.

Compression

Columnar storage achieves significantly better compression because:

  • Values in a column are the same type (easier to compress)
  • Similar values cluster together (NULLs, repeated values compress well)
  • Compression algorithms work better on homogeneous data

Columnar storage achieves significantly better compression than row storage because similar values cluster together and compression algorithms work better on homogeneous data. This means:

  • Less storage cost
  • More data fits in memory (faster queries)
  • Less network bandwidth when reading data

Query Patterns

The storage format should match your access patterns:

Access PatternBest Storage
Read/write individual recordsRow storage (OLTP)
Aggregate many rows, few columnsColumnar storage (OLAP)
Both transactional and analyticalHTAP (or separate systems)

Mismatch Example: Using row storage for analytics means you’ll pay for expensive compute to read unnecessary columns. Using columnar storage for high-frequency transactions means slow writes and poor concurrency.

Cost Implications

Wrong storage = wasted money:

  • Compute costs: Reading unnecessary data wastes CPU cycles
  • Storage costs: Poor compression means more disk space
  • Network costs: More data transferred between storage and compute
  • Time costs: Slow queries delay decision-making

Choosing the Right Storage

Decision Framework

Transactional workloads → Row storage (OLTP)

  • User-facing applications
  • Order processing
  • Account management
  • Real-time operations

Analytical workloads → Columnar storage (OLAP)

  • Business intelligence
  • Reporting dashboards
  • Data warehousing
  • Historical analysis

Need both? → HTAP (if complexity acceptable) or separate systems

  • Real-time analytics on live data → HTAP
  • Prefer operational simplicity → Separate OLTP + OLAP

Real-World Pattern

Most companies use both storage types:

  • OLTP database (PostgreSQL, MySQL) for operational data
  • OLAP database (ClickHouse, Snowflake) for analytics
  • ETL/ELT pipeline that asynchronously moves data from OLTP to OLAP

This polyglot persistence approach recognizes that different workloads have different requirements. The ETL/ELT pipeline ensures transactional workloads stay fast while analytics run on optimized columnar storage. Trying to force everything into one storage type leads to compromises and poor performance.

Conclusion

Storage format is a fundamental architectural decision that impacts performance, cost, and scalability. There’s no “one size fits all” solution:

  • Row storage excels at transactional workloads but struggles with analytics
  • Columnar storage excels at analytics but struggles with frequent writes
  • HTAP attempts both but adds complexity and isn’t widely adopted

Understanding these concepts helps you:

  • Choose the right database for each workload
  • Avoid performance surprises at scale
  • Optimize costs by matching storage to access patterns
  • Make informed architectural decisions

Modern systems often combine multiple storage types, each optimized for its specific use case. The key is understanding when to use each one.

Questions or feedback? Reach out:

Further Reading: