SQL 113 🛢️ Columnar Storage vs Row Storage for OLAP

The difference between row storage and columnar storage is a single physical decision—whether the values of a row are stored together or the values of a column are stored together—and that decision cascades into every performance characteristic of an analytical query. A row store writes each tuple contiguously, so fetching one complete record is a single page read. A column store writes each attribute contiguously, so scanning one attribute across millions of rows is a single sequential stream. For OLAP, where queries typically touch three to six columns out of tables that may have fifty or more, the column store reads a fraction of the bytes the row store must read to answer the same question .

The compression difference compounds the I/O difference. Columns contain values of the same type, often with low cardinality or monotonic ordering, which makes them compressible with dictionary encoding, delta encoding, run-length encoding, and bit-packing. Row stores interleave heterogeneous types, defeating most of these schemes. The practical result is that columnar storage compresses five to twenty times tighter than row storage on analytical data, and the combination of fewer bytes read and fewer bytes stored produces the performance gap that defines the OLAP storage decision .

This chapter covers the storage layout mechanics, the compression and vectorization advantages that follow from columnar layout, the write-path penalties that make column stores unsuitable for OLTP, and the hybrid architectures that attempt to serve both workloads from a single system. It assumes familiarity with basic SQL aggregation and the distinction between transactional and analytical query patterns.

Key point: Row storage is optimized for writing and retrieving complete records; columnar storage is optimized for scanning and aggregating a subset of attributes across many records. OLAP queries are the second pattern, which is why columnar storage dominates analytical databases. The cost is the write path: single-row updates in a column store are expensive because they touch multiple column files.

---

Why the storage layout determines OLAP performance

Analytical queries are narrow and wide. A typical OLAP query selects three to six columns from a table that may have fifty to five hundred columns, aggregates over millions of rows, and filters on one or two attributes. The row store must read every column of every row it touches to extract the three to six values the query needs. The column store reads only the column files the query references, skipping the rest entirely . On a fifty-column table, a three-column query reads roughly six percent of the bytes a row store reads before compression, and the gap widens after compression because columnar data compresses tighter .

Compression is a property of homogeneous values. A column of timestamps increases monotonically, so delta encoding stores consecutive differences that are small and compress further with bit-packing. A column of status codes has three distinct values, so dictionary encoding replaces strings with small integers. A sorted column of country codes compresses to almost nothing with run-length encoding. A row store interleaves strings, integers, timestamps, and floats in the same page, which defeats type-specific encoding and forces the engine to fall back to general-purpose compression with a fraction of the ratio .

Vectorized execution requires columnar layout. Modern analytical engines process data in batches of 1,024 to 8,192 values and apply SIMD instructions that operate on multiple values per CPU cycle. This is only possible when the values are stored contiguously and have the same type. A column store provides exactly this layout; a row store forces the engine to gather values from scattered offsets before it can vectorize . The combination of columnar storage and vectorized execution is what produces the order-of-magnitude performance difference between analytical engines and row-based databases running the same query.

The trade-off. The columnar layout is wrong for transactional workloads. Fetching a complete row requires opening every column file and stitching the values together, which is far more expensive than reading one contiguous tuple. Updating a single row requires writing to every column file that contains a changed value, which is far more expensive than rewriting one page. The storage decision is a workload decision: columnar for scans and aggregations, row for point lookups and frequent single-row mutations.

---

a. The layout mechanics and the I/O math

A row store writes each tuple as a contiguous block. In PostgreSQL, the default page size is 8 KB, and tuples are packed into pages with a B-tree index per key. Fetching a row by primary key is one B-tree walk and one page read, which is sub-millisecond on cached data. Updating one column in one row rewrites one tuple on one page .

A column store writes each attribute as a separate contiguous stream. A query that aggregates SUM(amount) reads only the amount stream. The I/O is proportional to the columns the query touches, not the width of the table. The ClickHouse engineering documentation quantifies this: on a fifty-column table where each column averages eight bytes per row, one million rows occupy roughly 400 MB. A three-column query reads 400 MB in a row store and roughly 24 MB in a column store before compression . The sixteen-fold reduction is the baseline, and compression typically widens it to fifty- to one-hundred-fold.

Alibaba Cloud’s AnalyticDB documentation describes the same trade-off in product terms. Row-oriented tables use the PostgreSQL heap architecture and are the default. They are suited for OLTP workloads, dimension tables that change frequently, and point lookups by key. Column-oriented tables must be append-optimized (appendonly=true, orientation=column) and are suited for large fact tables loaded in batches and queried by aggregating a small number of columns across many rows . The product forces the storage choice at table creation, which reflects the reality that the two layouts serve incompatible access patterns.

---

b. Compression, vectorization, and the performance cascade

The performance advantage of columnar storage is not a single optimization but a cascade. The columnar layout enables type-specific compression, which reduces the bytes that must be read from disk. Fewer bytes means less I/O and less memory pressure. The compressed columnar layout also enables vectorized execution, which reduces the CPU cycles per value processed. The combination is what produces the benchmarks: Alibaba Cloud’s PolarDB for PostgreSQL reports that its columnar engine delivers more than sixty times the query performance of its row-oriented engine on a 100 GB TPC-H dataset with a 32-core, 256 GB cluster .

The compression toolkit is standardized across columnar engines. Dictionary encoding maps distinct values to small integers and works on low-cardinality columns like status enums and country codes. Run-length encoding stores value-count pairs and works on sorted or near-sorted columns. Bit-packing uses only enough bits to represent the observed range of values in a block. Delta encoding stores consecutive differences and works on monotonic columns like timestamps and auto-increment IDs. Frame-of-reference subtracts a per-block minimum before bit-packing. These techniques compose: Parquet’s DELTA_BINARY_PACKED applies delta encoding, then frame-of-reference, then bit-packing in a single pass .

Vectorized execution is the CPU-side counterpart. The classic Volcano iterator model calls next() for every tuple, paying virtual function call overhead, branch misprediction, and pointer chasing per value. Vectorized execution processes batches of values in a tight loop that fits in L1 or L2 cache, and SIMD instructions operate on multiple values per cycle. The performance literature cites ten to one hundred times CPU reduction versus tuple-at-a-time processing on analytical queries . ClickHouse, DuckDB, Snowflake, Databricks Photon, and Meta’s Velox all use vector-at-a-time execution.

---

c. The write-path penalty and hybrid architectures

The columnar layout is a read-optimized structure, and the write path pays for it. Inserting a single row into a column store means writing to every column file. Updating one attribute means rewriting the block that contains the old value and the block that contains the new value, depending on the engine’s copy-on-write or merge-on-read strategy. Deleting a row means marking it in every column file or rewriting the blocks that contain it. The AnalyticDB documentation is explicit: column-oriented tables are “less efficient for frequent single-row inserts, updates, or deletes” and recommend bulk loading with COPY .

SAP HANA’s performance guide describes the same trade-off from the perspective of a system that defaults to columnar storage. The guide recommends columnar for most scenarios because it handles both OLAP and OLTP reasonably well, but identifies three cases where row storage is better: very small tables (up to a few thousand records) where compression does not matter, tables with a high-volume transactional update workload on a limited set of records, and tables accessed by extremely frequent highly selective point lookups that return the entire row . The guide also warns that cross-engine joins between row and column tables are less efficient than same-engine joins, which is the cost of a hybrid architecture.

Hybrid systems attempt to serve both workloads by maintaining a row-oriented write path and a columnar read path. PolarDB for PostgreSQL’s IMCI feature stores data in a row-oriented table first, then synchronizes it to a columnar index asynchronously by parsing write-ahead log records. The synchronization has a latency of milliseconds to seconds depending on write load and imposes less than three percent overhead on the row-oriented engine . The query optimizer chooses between the row engine and the columnar engine based on query cost. This is the architectural pattern behind the “HTAP” (hybrid transactional/analytical processing) category: one system, two storage representations, and a cost-based decision about which representation to use for a given query.

The Lund University thesis on analytical infrastructure under resource constraints found that a columnstore-indexed OLTP schema performed comparably to a dimensional columnstore schema for many analytical queries, with some join-intensive workloads favoring the dimensional schema . The finding suggests that the storage layout matters more than the schema shape for many OLAP queries, and that hybrid architectures can be suitable when the organizational constraints justify them. But the thesis also notes that separated OLAP architectures remain advantageous where workload or organizational requirements justify them.

---

Complete Example Session

-- ============================================
-- PART 1: ROW STORE — THE POINT LOOKUP
-- ============================================
-- Row store layout: one page per tuple group.
SELECT * FROM orders WHERE order_id = 12345;

-- One B-tree walk, one page read.
-- Sub-millisecond on cached data.
-- Efficient because the entire row is contiguous.

-- ============================================
-- PART 2: COLUMN STORE — THE AGGREGATION
-- ============================================
-- Column store layout: separate stream per column.
SELECT region, SUM(amount)
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY region;

-- Reads only order_date, region, amount.
-- Skips customer_id, product_id, notes, etc.
-- On a 50-column table, reads ~6% of the bytes.
-- Aggregation operates on contiguous typed arrays.

-- ============================================
-- PART 3: THE I/O MATH
-- ============================================
-- 50 columns × 8 bytes × 1M rows = 400 MB.
-- Query touches 3 columns.
--
-- Row store: reads ~400 MB (all columns).
-- Column store: reads ~24 MB (3/50 of 400 MB).
-- Ratio: ~16× before compression.
-- After compression (5-10× tighter): 50-100×.

-- ============================================
-- PART 4: COMPRESSION TOOLKIT
-- ============================================
-- Dictionary: status ('active','inactive') → 0,1.
-- Delta: timestamps [1000,1001,1002] → [1000,1,1].
-- RLE: sorted country codes → ('US',10000).
-- Bit-pack: values 0-100 → 7 bits each.
--
-- Row store interleaves types; encoding fails.
-- Column store groups same type; encoding works.

-- ============================================
-- PART 5: VECTORIZED EXECUTION
-- ============================================
-- Batch of 1,024 values fits in L1/L2 cache.
-- SIMD instruction: 16 INT32 additions per cycle.
-- Hot loop over dense array; no pointer chasing.
--
-- Requires contiguous same-type values.
-- Columnar layout provides exactly that.

-- ============================================
-- PART 6: THE WRITE PENALTY
-- ============================================
-- INSERT one row into column store:
--   Write to every column file.
-- UPDATE one attribute:
--   Rewrite the affected block per column.
-- DELETE one row:
--   Mark or rewrite in every column file.
--
-- Row store: rewrite one page. Much cheaper.

-- ============================================
-- PART 7: HYBRID — POLARDB IMCI
-- ============================================
-- Write path: row-oriented heap (OLTP).
-- Read path: columnar index (OLAP).
-- Sync: WAL parsing → columnar index (async).
-- Overhead: <3% on write path.
-- Latency: milliseconds to seconds.
-- Optimizer: cost-based engine selection.
-- Benchmark: 60× on TPC-H 100GB vs row engine.

-- ============================================
-- PART 8: SAP HANA — WHEN ROW IS BETTER
-- ============================================
-- Very small tables (< few thousand rows).
-- High-volume single-row updates.
-- Frequent select * by highly selective key.
-- Cross-engine joins are less efficient.

-- ============================================
-- PART 9: THE DECISION MATRIX
-- ============================================
-- OLAP, large fact tables, batch load:
--   Columnar.
-- OLTP, frequent single-row mutations:
--   Row.
-- Mixed workload, single system:
--   Hybrid (row write path + columnar read path).
-- Small dimension tables:
--   Row (compression irrelevant).

-- ============================================
-- PART 10: THE COMPLETE FLOW
-- ============================================
-- 1. Workload shape determines layout.
-- 2. OLAP: narrow scan, wide table → columnar.
-- 3. OLTP: point lookup, mutation → row.
-- 4. Columnar compresses 5-10× tighter.
-- 5. Columnar enables vectorized execution.
-- 6. Write path pays for read optimization.
-- 7. Hybrid systems maintain both layouts.

---

Quick Reference

Row vs Columnar

Aspect	Row Store	Column Store
Layout	Tuples contiguous	Columns contiguous
OLAP scan	Reads all columns	Reads only referenced columns
Point lookup	One page read	Multiple column files
Single-row update	One page rewrite	Rewrite affected blocks per column
Compression	1.5–3×	5–10×, up to 30×
Vectorization	Difficult (scattered)	Natural (contiguous)
Primary workload	OLTP	OLAP

Compression Techniques

Technique	Best For	Example
Dictionary	Low-cardinality strings	Status codes
Delta	Monotonic values	Timestamps, IDs
RLE	Sorted/repeated values	Country codes
Bit-packing	Small-range integers	Counts
Frame-of-reference	Clustered integers	Sensor readings

When to Use Columnar

Condition	Reason
Large fact tables (>100k rows)	Compression and scan efficiency
Batch loads (COPY)	Write path tolerates bulk
Aggregations over few columns	I/O proportional to columns touched
Rare updates	Write penalty avoided

When to Use Row

Condition	Reason
Frequent single-row mutations	One page rewrite vs per-column
Point lookups returning full row	Contiguous tuple read
Small tables (< few thousand rows)	Compression irrelevant
Mixed workload	Flexibility

---

Best Practices

✅ Do This:

-- Use columnar for fact tables and OLAP.                    // ✅
CREATE TABLE orders (…) WITH (appendonly=true, orientation=column);

-- Bulk load with COPY, not single-row INSERT.               // ✅
COPY orders FROM '/data/orders.csv' CSV;

-- Use row tables for dimension tables.                      // ✅
-- Small, frequently updated, point-looked-up.

-- Consider hybrid for mixed workloads.                      // ✅
-- Row write path + columnar read path (IMCI, HTAP).

-- Measure the query shape before choosing.                  // ✅
-- 3 columns of 50 → columnar. Select * by key → row.

❌ Don’t Do This:

-- Use columnar for high-frequency single-row updates.       // ❌
UPDATE orders SET status='shipped' WHERE id=42;  -- Expensive.

-- Use row storage for large aggregation queries.            // ❌
-- Reads all columns; no compression benefit.

-- Assume columnar is always faster.                         // ❌
-- Point lookups and writes are slower.

-- Ignore cross-engine join cost in hybrid.                  // ❌
-- Row-column joins are less efficient.

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Column store write bottleneck	Single-row updates touch every column	Bulk load; avoid frequent updates
Row store slow aggregations	Reads all columns to extract few	Use columnar for OLAP tables
Compression worse than expected	Mixed types defeat encoding	Verify column types and cardinality
Hybrid join performance	Cross-engine joins lack optimization	Keep joins within one engine
Small table on columnar	Compression irrelevant; overhead	Use row for small tables

---

Real-World Examples

1. Columnar Fact Table

CREATE TABLE sales (…) WITH (appendonly=true, orientation=column);

2. Row Dimension Table

CREATE TABLE customers (…);  -- Default heap storage.

3. Bulk Load into Columnar

COPY sales FROM '/data/sales.csv' CSV;

4. Aggregation on Columnar

SELECT region, SUM(amount) FROM sales GROUP BY region;

5. Point Lookup on Row

SELECT * FROM customers WHERE id = 42;

6. Delta Encoding

-- timestamps [1000,1001,1002] → [1000,1,1]

7. Dictionary Encoding

-- status ('active','inactive') → 0,1

8. PolarDB IMCI

-- Row write path + columnar index, async WAL sync.

9. SAP HANA Row Exception

-- High-frequency single updates on limited records.

10. Verify Query Shape

EXPLAIN SELECT region, SUM(amount) FROM sales GROUP BY region;

---

Visual: Storage Layout

┌──────────────────────────────────────────────┐
│  ROW STORE                                   │
│  Page: [id|name|amount|ts] [id|name|amount]  │
│         │                                    │
│         │ Select * WHERE id=42 → one page    │
│         │ SUM(amount) → read all columns     │
│                                              │
│  COLUMN STORE                                │
│  id:     [1,2,3,...]                         │
│  name:   [Alice,Bob,...]                     │
│  amount: [10,20,30,...]                      │
│         │                                    │
│         │ SUM(amount) → read amount only     │
│         │ Select * WHERE id=42 → all files   │
│                                              │
└──────────────────────────────────────────────┘

I/O Comparison

┌──────────────────────────────────────────────┐
│  50-COLUMN TABLE, 3-COLUMN QUERY             │
│                                              │
│  Row store: reads ~400 MB (all columns).     │
│  Column store: reads ~24 MB (3 columns).     │
│  Ratio: ~16× before compression.             │
│  After compression: 50–100×.                 │
│                                              │
│  The gap widens with table width.            │
│                                              │
└──────────────────────────────────────────────┘

Write Path Penalty

┌──────────────────────────────────────────────┐
│  UPDATE one row, one column                  │
│                                              │
│  Row store:                                  │
│    One page rewrite. Cheap.                  │
│                                              │
│  Column store:                               │
│    Rewrite block in one column file.         │
│    (Or mark old + write new, merge-on-read.) │
│                                              │
│  INSERT one row:                             │
│    Row: one page.                            │
│    Column: every column file. Expensive.     │
│                                              │
└──────────────────────────────────────────────┘

Hybrid Architecture

┌──────────────────────────────────────────────┐
│  POLARDB IMCI / HTAP                         │
│                                              │
│  Write path: row-oriented heap (OLTP).       │
│       │                                      │
│       │ WAL parsing (async)                  │
│       ▼                                      │
│  Read path: columnar index (OLAP).           │
│       │                                      │
│       ▼                                      │
│  Optimizer chooses engine by cost.           │
│                                              │
│  Overhead: <3% on writes.                    │
│  Sync latency: ms to seconds.                │
│  Benchmark: 60× on TPC-H vs row engine.      │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Row store layout	Tuples contiguous
Column store layout	Columns contiguous
OLAP I/O advantage	Reads only referenced columns (~6% on 3/50)
Compression ratio	Row: 1.5–3×; Column: 5–10×, up to 30×
Vectorization	Requires contiguous same-type values
Write penalty	Column: per-column writes; Row: one page
Hybrid pattern	Row write path + columnar read path
PolarDB IMCI benchmark	60× TPC-H vs row engine
SAP HANA row exceptions	Small tables, high-update, point lookups

Key takeaways:

• The storage layout determines the I/O. A row store reads every column of every row it touches; a column store reads only the columns the query references. On a fifty-column table with a three-column query, the column store reads roughly six percent of the bytes before compression, and the gap widens after compression .
• Compression is a property of homogeneous values. Columns group values of the same type, enabling dictionary, delta, run-length, and bit-packing encodings. Row stores interleave types and defeat these schemes. Typical columnar compression is five to ten times tighter than row storage .
• Vectorized execution requires columnar layout. Modern analytical engines process batches of values with SIMD instructions, which is only possible when values are contiguous and same-typed. This is why columnar engines achieve order-of-magnitude CPU reductions versus tuple-at-a-time processing .
• The write path is the cost. Single-row inserts and updates touch every column file in a column store. This is why column stores are recommended for batch-loaded fact tables and row stores for frequently mutated dimension tables .
• Hybrid architectures serve both workloads. PolarDB’s IMCI maintains a row write path and a columnar read path synchronized via WAL parsing, with less than three percent write overhead and a cost-based optimizer that chooses between engines .
• SAP HANA identifies the row exceptions. Very small tables, high-volume single-row updates, and extremely frequent point lookups returning full rows are the cases where row storage outperforms columnar even in a columnar-default system .
• The decision is workload-driven. OLAP with narrow scans and wide tables uses columnar. OLTP with point lookups and mutations uses row. Mixed workloads use hybrid or separate systems.

Remember: The row-versus-column decision is a physical layout decision with cascading performance consequences. Row storage puts a record’s values together, making point lookups and single-row mutations cheap. Columnar storage puts an attribute’s values together, making scans and aggregations cheap, compressing tighter, and enabling vectorized execution. OLAP queries are scans and aggregations over a subset of columns, which is why columnar storage dominates analytical databases. The cost is the write path: every insert or update touches multiple column files. Hybrid architectures maintain both layouts and choose per query, but they add synchronization complexity and cross-engine join penalties. The layout should match the workload, not the other way around.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!