seon99il

Data Engineering

Column-oriented Store: What's The Benefit?

7 min read

In this post, we take a detailed look at row vs column in-memory storage layouts, the internals of the technologies that implement them (Apache Arrow Format, Parquet, Polars), and what actually happens when data is modified.

Row-oriented vs Column-oriented

  • Row-oriented
    • A whole row is stored contiguously → good for OLTP (transactions)
    • Efficient when reading or modifying an entire row
  • Column-oriented
    • Values of each column are stored contiguously → good for OLAP (analytics)
    • Fast reads of specific columns, highly efficient compression, vectorized operations

Review: Row-oriented

In a row-oriented layout, each row is stored contiguously in memory. Databases we all know, such as MySQL and PostgreSQL, store data this way.

# Row Table ex.) Memory
┌─────────┬───────────────────────────────┬───────────────────────────────┐
│ row id  │ row buffer                    │ description                   │
├─────────┼───────────────────────────────┼───────────────────────────────┤
│ 0       │ [1, "Alice", 25, "Seoul"]     │ row0: id, name, age, city     │
│ 1       │ [2, "Bob", 30, "Busan"]       │ row1: id, name, age, city     │
│ 2       │ [3, "Carol", 27, "Seoul"]     │ row2: id, name, age, city     │
│ 3       │ [4, "David", 22, "Daegu"]     │ row3: id, name, age, city     │
└─────────┴───────────────────────────────┴───────────────────────────────┘

All values of a row (id, name, age, city) are stored contiguously, and reading or writing an entire row at once is advantageous in OLTP environments.

What is Columnar Storage?

A method that groups and stores data by column rather than by row

This approach is efficient for data analysis and aggregation, where only specific columns need to be read. Since values of the same data type are stored together, it also offers a high compression ratio and faster queries.

# Table
| id | name  | age | city  |
| -- | ----- | --- | ----- |
| 1  | Alice | 25  | Seoul |
| 2  | Bob   | 30  | Busan |
| 3  | Carol | 27  | Seoul |

For example, given the user table above, Row-oriented storage looks like [1, 'Alice', 25, 'Seoul'], [2, 'Bob', 30, 'Busan'], …, whereas Column-oriented storage lays the data out as follows.

id → [1, 2, 3], name → ['Alice', 'Bob', 'Carol'], age → [25, 30, 27], city → ['Seoul', 'Busan', 'Seoul']

This layout gives a performance advantage for queries that scan only specific columns.

Read Optimization

Only the required columns are read, so unnecessary data is not loaded from disk

Better Compression

Because values of the same data type are contiguous, encodings such as Run-Length Encoding (RLE) and Dictionary Encoding work well.

region: [Seoul, Seoul, Seoul, Busan, Busan]
→ RLE: (Seoul × 3), (Busan × 2)
  • Run-Length Encoding (RLE): Compactly represents consecutive repeated values (e.g., A × 100) → [A, A, A, A, B, B] → [(A,4),(B,2)]
  • Dictionary Encoding: Replaces a list of unique values with indexes → ["Seoul", "Busan", "Seoul"] → [0,1,0] + dictionary ["Seoul","Busan"]
  • Delta Encoding: Stores the difference from the previous value in an integer sequence → [100,102,105] → [100,2,3]
  • Bit-Packing: Packs multiple values densely using a small number of bits
  • Page compression: Compresses each page with Snappy/ZSTD, etc.

Vectorization (SIMD) & Cache Locality

Since column values are contiguous, the CPU can load many values into cache at once and process them together

Because fixed-length values form a contiguous array, the CPU’s SIMD instructions can process many values at once. ex.) processing 8 ints in a single instruction

Also, since values of the same column sit next to each other in memory, the CPU L1/L2 cache hit rate is high.

Reading a Columnar Data Format

Metadata-based optimization is possible without reading the entire file

  1. Receive the query (SQL/DataFrame)
  2. Read metadata → file-level pruning
  3. Select columns/RowGroups/pages
  4. Read the selected bytes (disk → memory)
  5. Decompress & decode
  6. Load into the Arrow memory structure (zero-copy possible)
  7. Execute vectorized operations (filter/aggregate/join)
  8. Serialize/return the result

Apache Arrow, Polars and Parquet

There are many methods, platforms, and formats for taking advantage of columnar structures. Here we introduce the following three.

  • Apache Arrow: A standard format for columnar structures
  • Apache Parquet: A columnar format for disk storage
  • Polars: An Arrow-based DataFrame engine

Apache Arrow — The Standard for In-Memory Columnar Formats

A standard format for columnar structures in memory

Apache Arrow — The universal columnar format and multi-language toolbox for fast data interchange and in-memory analytics

One of the problems that arises when different data systems use APIs to share data with each other is that the overhead of serialization and deserialization is too high.

Arrow standardizes how data is stored in memory by column. Data is defined in a columnar structure in memory regardless of platform, so the CPU and GPU can read and write memory quickly.

In other words, once one system loads data into memory in the Apache Arrow format, another system does not copy the data when loading it; instead it references the memory address of the original data (Zero-Copy).

If you load a 10GB file into memory in the Apache Arrow format and another system then uses that data, memory usage remains 10GB rather than 10GB + 10GB, because only the memory address is passed to the other system.

Apache Parquet — A Columnar Format for Disk Storage

Parquet is the on-disk version of Arrow

Documentation — The Apache Parquet Website

Parquet is a format that stores data compressed by column on disk, and most data lake systems (Spark, Redshift, BigQuery, etc.) support it natively.

# Structure

Parquet file
 ├── File Footer (schema, stats)
 ├── RowGroup #1
 │    ├── ColumnChunk: id
 │    ├── ColumnChunk: name
 │    └── ColumnChunk: age
 └── RowGroup #2 ...
  • RowGroup: A block of thousands to tens of thousands of rows
  • ColumnChunk: A chunk of data for a single column
  • Statistics: min/max/null count → used for predicate pushdown

Polars — An Arrow-based DataFrame Engine

Polars is a next-generation DataFrame engine that uses the Arrow format and can play the same role as Pandas, DuckDB, and the like.

Polars — DataFrames for the new era

Polars is a DataFrame engine written in Rust. Its internal memory layout is the same as Arrow’s, and it is a DataFrame library that is more memory-efficient and faster than Pandas.

When processing GB-scale data, it can offset the weaknesses of both Spark and Pandas. ex.) Spark: cost efficiency, Pandas: weak parallel processing

import polars as pl

df = pl.DataFrame({
    "id": [1, 2, 3],
    "value": ["A", "B", "C"]
})

# Save as Parquet
df.write_parquet("data.parquet")

# Lazy Load
lazy_df = pl.scan_parquet("data.parquet")
result = (
    lazy_df
    .filter(pl.col("id") > 1)
    .select("value")
    .collect()
)

print("Lazy Result:")
print(result)

# Eager Load (read immediately, then process)
df = pl.read_parquet("data.parquet")
# Filter by condition
eager_result = df.filter(pl.col("id") > 1).select("value")

print("Eager Result:")
print(eager_result)

You can use pl.scan_*() to perform lazy execution, in which case unnecessary operations can be pushed down.

You can also use pl.read_* to read immediately and then process.

  • Lazy Load: scan_* → only builds a query plan → reads and processes the actual data when .collect() is called. Unnecessary operations can be pushed down.
  • Eager Load: read_* → reads the file into memory right away → DataFrame operations are available immediately

Both approaches give the same result, but Lazy can optimize I/O and memory on large datasets.

Drawbacks of Columnar Storage

Like any technology, Columnar Storage has trade-offs.

Problem 1: Update/Delete is not possible (Immutable)

Files based on Parquet, ORC, and Arrow have an immutable structure: once a file is written, in-place updates to existing data are not possible.

If you want to UPDATE or DELETE just one row in a Parquet file holding 1 million records, you must read the entire file, create a new Parquet file reflecting the modified/deleted content, and then delete (or overwrite) the existing file.

Problem 2: Small File Problem

If data is streamed in real time and 100 records are saved to a Parquet file every minute, 6,000 Parquet files are created per hour. File I/O overhead then grows explosively and query performance degrades.

Problem 3: No ACID transactions

If a write job fails midway, some data is written and some is not, leaving “dirty data”, and consistency cannot be guaranteed when reading and writing at the same time.

Delta Lake + Compaction

Delta Lake does not modify Parquet files directly. Instead, it creates Delta files, which solves the problems above efficiently.

Delta Lake Structure

Home | Delta Lake — Delta Lake is an open-source storage framework that enables building a format agnostic Lakehouse architecture with…

Solution 1: Efficient UPDATE/DELETE (Copy-on-Write)

Using the Delta log, you can logically modify/delete data through the transaction log without touching the original large files.

  • DELETE:
    1. Leave the original Parquet file (A) as is.
    2. Record in _delta_log that “certain rows of file A are considered deleted.”
  • UPDATE: (internally Delete + Insert)
    1. Record in _delta_log that “certain rows of file A are considered deleted.”
    2. Write the modified (new) data as a separate small Parquet file (B).
    3. Record in _delta_log that “file B has been newly added.”

Solution 2: Fixing the Small File Problem with Compaction (OPTIMIZE)

If the process in 1 above (update/delete) or streaming data keeps accumulating, the small file problem eventually returns. (Small files B, C, D… holding the changes keep being created.)

This is when Compaction is needed. In Delta Lake, this is done with the OPTIMIZE command.

  • What Compaction (OPTIMIZE) does:
    1. Reads the hundreds or thousands of small Parquet files that make up the Delta Lake table.
    2. Merges the data and rewrites it as a small number of large, optimized Parquet files (e.g., 1GB).
    3. Records in _delta_log that “from now on, use the large file Z instead of the small files A, B, C…”.

The number of files the query engine has to read drops dramatically, so the “small file problem” is resolved and queries become fast again.

Solution 3: ACID Guarantees via the Transaction Log

  1. _delta_log atomically records every change to the data (add, delete, update, compaction).
  2. If a write fails midway, the log file itself is not created (commit fails), so the data stays in its previous consistent state.
  3. Every reader always references the latest version of _delta_log, so it can read a consistent snapshot even while writes are happening (Isolation).

Previous posts

View all posts →

Comments