Columnar Storage
1. Overview
A. Definition
Columnar storage (column-oriented storage) is a storage model (DSM, Decomposition Storage Model) that physically lays out a table column by column rather than row by row, gathering the values of the same attribute together so they can be stored, compressed, and scanned as a unit. It stands in contrast to the traditional row-oriented storage (NSM, N-ary Storage Model) that stores all attributes of a row contiguously, and it is a structure optimized for OLAP and analytical workloads that aggregate and analyze only a few columns over large volumes of data.
B. Background and necessity
Traditional relational DBMSs were designed on the premise of online transaction processing (OLTP). For work such as inserting a single order or retrieving an entire record for a specific customer, the row-oriented approach of keeping all columns of a row in one place on disk is advantageous, because a single page read yields the whole record needed. After the 2000s, however, the spread of data warehouses and business intelligence (BI) fundamentally changed the nature of the workload. Analytical queries such as "total sales by region over the past three years" — which scan hundreds of millions of rows but actually use only two or three columns — became dominant.
Processing such queries on row-oriented storage causes severe waste. Even if only the sales column is needed, the disk is read in page units, so all the unnecessary columns packed into that page — customer name, address, product description, and so on — must be pulled in as I/O. For a query that uses 3 columns out of a 100-column table, in theory 97% of the I/O is wasted. In large-scale analytics where disk bandwidth and the memory bus are the bottleneck, this waste translates directly into response time and cost.
Columnar storage tackles this problem head-on. By reading only the columns that appear in the query (projection pushdown) it minimizes I/O; because values of the same domain and type are physically adjacent, compression ratios rise dramatically; and by vectorized, SIMD execution that processes arrays of values at once it further boosts CPU cache efficiency. As a result, analytical query performance improves by several to tens of times on the same hardware, and today almost every analytical engine (Redshift, BigQuery, Snowflake, ClickHouse, DuckDB) adopts columnar storage.
The idea itself is not new. In academia it originated in decomposition storage model (DSM) research in the 1980s, and in the early 2000s the Netherlands' CWI MonetDB and Michael Stonebraker's team's C-Store (later developed into the commercial product Vertica) demonstrated the performance potential of columnar storage, driving its full-scale spread into industry. In other words, columnar storage is less a recent fad than a case of long-standing research results resurfacing as mainstream technology as workloads shifted from transactions to analytics.
C. Characteristics
- Selective column reads (I/O reduction): reads only the columns a query references from disk, eliminating unnecessary data transfer.
- High compression ratio: because same-type, similar values are adjacent, dictionary, RLE, and delta encoding fit well, typically compressing 2–10x versus row storage.
- Vectorization/SIMD friendliness: since a column's values form a contiguous array, repetitive operations are processed in vector units, using the CPU pipeline and cache efficiently.
- Aggregation optimization: strong at full-column scans and aggregation such as SUM, AVG, COUNT, but weak at single-row insert/update/delete (OLTP).
- Statistics-based skipping: stores per-chunk statistics such as min/max, so data blocks that do not match a condition are skipped without being read at all.
2. Overall structure: row-oriented vs. column-oriented
The difference between row-oriented and column-oriented lies in "how the logically identical table is spread out on disk." The logical table is the same, but as the physical layout changes, I/O, compression, and computation characteristics diverge entirely. The structure diagram below shows how a table with four columns is serialized in each of the two models.
flowchart TB
T["Logical table(rows R1~R3 × columns A~D)"]
T --> NSM["Row-oriented(NSM)"]
T --> DSM["Column-oriented(DSM)"]
subgraph NSM_L["Row-oriented physical layout"]
N1["Block: (R1.A R1.B R1.C R1.D)"]
N2["Block: (R2.A R2.B R2.C R2.D)"]
N3["Block: (R3.A R3.B R3.C R3.D)"]
end
subgraph DSM_L["Column-oriented physical layout"]
D1["Column A: (A1 A2 A3)"]
D2["Column B: (B1 B2 B3)"]
D3["Column C: (C1 C2 C3)"]
D4["Column D: (D1 D2 D3)"]
end
NSM --> NSM_L
DSM --> DSM_L
In the diagram, row-oriented storage keeps A~D of a row together in one block, so it is strong for a "give me all of R2" request. Column-oriented storage, on the other hand, gathers only the values of column A separately, so it finishes a "sum A across all rows" request with a single sequential scan. This layout difference is precisely what determines workload fit.
Unpacking each component a little further: a column chunk is the physical unit that gathers a column's values, and encoding and compression are applied to it. Besides the values themselves, a definition level / null bitmap indicating which rows are NULL, and ordinal position information for tracing which logical row each value belongs to, are managed together. Because column-oriented storage does not store rows whole, tuple reconstruction — combining several columns to revive the original row — is separately needed, and how to minimize its cost becomes the core challenge of columnar engine design.
Let us illustrate with actual numbers how this physical layout difference manifests. Suppose we compute "the sum of the sales column (8 bytes)" on a one-billion-row table with 50 columns and an average of 500 bytes per row (about 500GB). Row-oriented storage must in principle scan the entire 500GB, but column-oriented storage reads only the sales column, so it needs to access only about 8GB (before compression), which dictionary and delta encoding reduce to around 2GB. The data that must be read at the same disk bandwidth shrinks by two orders of magnitude, and this is the fundamental reason columnar storage holds an overwhelming advantage in analytical queries.
Column-oriented storage is also flexible for schema evolution. Because columns are stored independently, adding a new column requires only appending a new column file without rewriting existing data, and partial optimization such as rewriting only a specific column with a different encoding is possible. Row-oriented storage, by contrast, is relatively heavy because adding or changing a column affects the entire row-record structure. Thus a single choice of physical layout cascades into not only I/O, compression, and computation but even the way schema is operated.
| Category | Row-oriented (NSM) | Column-oriented (DSM) |
|---|---|---|
| Physical layout | all columns of a row contiguous | all values of a column contiguous |
| Favorable queries | single-record lookup/insert (OLTP) | bulk scan/aggregation (OLAP) |
| Compression ratio | low (mixed heterogeneous types) | high (homogeneous values adjacent) |
| Update cost | low | high (touch many column files) |
| Representative systems | MySQL, PostgreSQL, Oracle | Redshift, ClickHouse, DuckDB |
3. Core techniques: encoding, compression, and execution
The performance of columnar storage comes not simply from "gathering columns together," but is completed by the lightweight encoding and vectorized execution layered on top. The detailed diagram below represents the pipeline in which raw column values are stored after passing through encoding and compression, and are processed at query time via data skipping and late materialization.
flowchart LR
RAW["Raw column values"] --> ENC["Lightweight encoding(dictionary·RLE·delta)"]
ENC --> CMP["General compression(LZ4·Zstd)"]
CMP --> STORE[("Column chunk store(+ zone map)")]
Q["Analytical query"] --> SKIP["Data skipping(min/max zone map)"]
STORE --> SKIP
SKIP --> SCAN["Vectorized scan(SIMD)"]
SCAN --> LATE["Late materialization"]
LATE --> RESULT["Result aggregation"]
A. Lightweight encoding. The first reason columnar storage achieves high compression is the homogeneity of values. Dictionary encoding substitutes repeated string or categorical values with integer codes, reducing storage size and lowering CPU burden by letting filters run on integer comparisons alone. RLE (Run-Length Encoding) compresses sorted or highly repetitive columns as "value + repeat count," while delta encoding stores only the difference from the previous value in columns with a clear increasing trend, such as timestamps or serial numbers. Combining bit-packing with these uses only the minimum number of bits matching the actual value range. For example, a country-code column (hundreds of distinct values) shrinks to under 10% of the original via dictionary encoding, and log timestamps increasing by the second are heavily compressed by delta plus bit-packing.
Encoding techniques are chosen to match a column's data characteristics. The table below summarizes representative encodings, the column types they fit well, and the expected effects. Real engines look at per-column statistics to select encodings automatically (e.g., dictionary encoding when cardinality is low) or combine several techniques in a cascade.
| Encoding | Suitable column | Principle | Expected effect |
|---|---|---|---|
| Dictionary | low-cardinality categorical/string | value→integer code substitution | size reduction + integer-comparison filters |
| RLE | sorted/highly repetitive columns | represent as value + repeat count | extreme compression of repeated runs |
| Delta | increasing numbers/timestamps | store only difference from previous value | reduction to a narrow value range |
| Bit-packing | integers with a narrow value range | use only the minimum bits needed | efficient integer storage |
B. Combination with general compression. Applying general compression such as LZ4, Snappy, or Zstandard again to the byte stream first tidied by lightweight encoding raises the compression ratio further. In practice, according to the trade-off between "decompression cost at query time" and "storage/transfer savings," teams tier codecs — fast-decompressing LZ4/Snappy for hot data and high-ratio Zstd for cold archives. Compression reduces not only storage cost but the very amount of disk and network I/O, so it often yields a net gain even after offsetting the CPU spent on decompression.
C. Data skipping (zone maps). By storing per-chunk zone maps and statistics such as minimum/maximum values and NULL counts, a filter like WHERE sales > 100M can skip without reading entire chunks whose maximum is at or below 100M. This predicate pushdown and data skipping multiplies columnar storage's I/O savings, and the effect grows the more the data is sorted and clustered by the relevant column.
D. Vectorized execution and late materialization. Because column values form a contiguous array, the engine processes thousands of values in batches rather than one row at a time. This vectorized model spreads out function-call overhead and parallelizes computation with SIMD instructions to raise CPU efficiency. If traditional row-based execution is the "Volcano iterator model" that calls an operator for each tuple, vectorization reduces that call to once per batch, amortizing interpreter overhead. Also, late materialization processes only the columns needed for filtering and aggregation first, and defers as long as possible the tuple reconstruction that returns to the original row form, avoiding unnecessary data joins. Conversely, early materialization, which restores rows at the start of a query, is simpler to implement but loses out in bulk scans. The lower the filter selectivity — that is, the more rows are filtered out — the greater the benefit of late materialization.
E. Limits and unsuitable workloads. Conversely, there are clearly cases where columnar storage is disadvantageous. Queries that demand all columns, like SELECT *, must reassemble column files back into rows and are therefore actually a loss; OLTP with frequent single-row inserts/updates, or point lookups that find a few rows exactly by key and fetch all their attributes, likewise favor row-oriented storage and B+Tree indexes. Columnar storage should therefore be understood not as an "all-purpose replacement" but as a tool specialized for analytical workloads, and the correct design perspective is to place it in a division of roles with transactional systems.
4. Comparison and application cases
Row-oriented and column-oriented are not a matter of superiority but of workload alignment. The fundamental cause of the difference is "the proportion of data read in a single I/O that the query actually consumes." OLTP frequently handles entire specific rows, so row-orientation raises that proportion, while OLAP scans a few columns over the full range, so column-orientation raises it. Update characteristics also diverge. Updating one row in column-oriented storage requires touching all the scattered column files, so most columnar systems, instead of individual UPDATEs, adopt the approach of appending a new version to immutable files and compacting.
Looking a little more closely at update handling, many columnar engines absorb writes with an idea similar to the [[lsm-tree]]. Newly arriving rows are first gathered in a small memory/row buffer (or small files), and once a certain threshold is reached they are flushed to immutable files in columnar form, with multiple fragments compacted into one in the background. Deletes and updates, rather than removing the row immediately, are recorded as a tombstone or a new version, and are filtered at read time (merge-on-read) or applied at compaction time (merge-on-write). Thanks to this structure, bulk-load throughput is high, but if compaction falls behind, small files pile up and scan performance drops, so managing the compaction policy becomes the core of operations.
Because of this trade-off, modern systems combine the two models. A representative one is the hybrid HTAP approach, a structure in which recent data is received in row storage (fast inserts) and older data is converted to column storage (fast analytics). For example, ClickHouse processes tens of billions of rows per second in aggregation on top of columnar storage and is used for large-scale log and event analysis; Amazon Redshift combines columnar storage with zone maps and sort keys to run petabyte-scale warehouses; and Google BigQuery provides serverless analytics with the Capacitor columnar format and the Dremel execution engine. In embedded analytics, DuckDB, which runs as a single file, increasingly performs large-scale CSV and Parquet analysis within seconds even in a notebook environment with its columnar, vectorized engine.
As a concrete application case, consider observability log analysis. In an environment where thousands of servers pour out millions of logs and metrics per second, putting these into a row-oriented RDBMS and running "aggregate the error rate by service over the past hour" causes the response to exceed tens of seconds due to the full-scan burden. Loading the same data into a columnar engine like ClickHouse or Druid and sorting it by the time and service columns processes the same query within a second via zone-map skipping and vectorized aggregation. As another case, in a telecom's call and billing detail record (CDR) analysis, only a few charge-related columns among hundreds are repeatedly aggregated, so after switching to columnar storage the storage cost is reduced several-fold by compression and the month-end settlement batch time is reported to be greatly shortened. The more the pattern is "repeatedly aggregating a few columns over wide (many columns), deep (many rows) data," the greater the benefit of columnar storage.
| Perspective | Row-oriented (OLTP) | Column-oriented (OLAP) |
|---|---|---|
| Representative query | SELECT * WHERE id=? |
SELECT SUM(x) GROUP BY y |
| Insert/update | fast | slow (append·compaction) |
| Scan/aggregation | slow | fast |
| Compression | limited | excellent |
| Index dependence | high (B+Tree) | low (scan + skipping) |
In sum, the criterion for choosing between the two models converges to a single question: "is the query row-oriented or column-oriented?" If it handles a few rows across broad columns, row storage is the answer; if it aggregates many rows over a few columns, column storage is. Real systems, rather than unifying the two into a single store, are often better off — in both total cost of ownership (TCO) and performance — composing storage layers with divided roles and connecting them with data pipelines.
5. Deep dive: storage-format standardization and the lakehouse
Columnar storage started as an internal database-engine technology, but today's trend is standardization into open file formats. Pure column storage (fully separating all values into per-column files) has the weakness of high tuple-reconstruction cost, so most practical formats adopt a PAX (Partition Attributes Across)-style hybrid. That is, the table is first divided into row groups of tens to hundreds of thousands of rows, and values are gathered by column only within each row group. This preserves the benefit of column scans while allowing related columns to be locally reconstructed within a single row group, improving I/O locality.
The representative formats that standardized this hybrid design are Apache Parquet and Apache ORC. Parquet is organized in a row group → column chunk → page hierarchy and adopts the Dremel model that expresses nested data with definition levels and repetition levels; it is used as the de facto standard format across the big-data ecosystem including Spark. ORC is widely used in the Hive lineage with its stripe-unit structure and built-in indexes and statistics. In the memory domain, Apache Arrow has established itself as the in-memory columnar standard for exchanging data across languages and engines without copying or serialization, lowering the cost of data exchange between heterogeneous systems.
Commercial cloud warehouses have also baked the same principle inside. Snowflake divides data into immutable micro-partitions of tens to hundreds of MB and stores and compresses column by column within them, keeping metadata such as min/max and distinct counts per partition to prune. This is a commercial-scale implementation of the PAX hybrid and zone-map skipping seen above. In sum, columnar storage — which began with academic concepts (C-Store, PAX) — runs as a single lineage through open file formats (Parquet, ORC), an in-memory standard (Arrow), and commercial engines (Snowflake, BigQuery, Redshift), permeating analytical infrastructure as a whole today.
The most recent trend is the lakehouse. On top of Parquet and ORC files accumulated in object storage (such as S3), table formats like Delta Lake, Apache Iceberg, and Apache Hudi layer transaction logs, schema evolution, time travel, and ACID, combining the reliability of a data warehouse with a cheap data lake. Here the column files (Parquet) serve as the physical layer holding the data, while the table format serves as the metadata layer describing which files constitute the table at a given point in time. Thanks to this separation, batch, streaming, BI, and ML can simultaneously access a single copy of data with different engines while maintaining consistency. This is solidifying into a standard decoupled storage-compute architecture in which storage is unified into a single open columnar format and multiple query engines (Spark, Trino, Snowflake, DuckDB) access it on top. Understanding the related concepts together with [[data-warehouse-olap]] and [[btree-bplus-index]] brings the whole picture of storage, indexing, and analytics into focus.
6. Considerations and implications
Columnar storage is not one feature of a particular product but a choice that forms the foundation of the data architecture, so before adoption one must weigh workload characteristics together with the long-term operational burden.
In particular, a professional engineer's answer should evenly address the following five axes — workload separation, compression trade-offs, update/regulatory response, open formats, and operations/monitoring — discussing application strategy together with trade-offs, outlook, and related technologies.
- Workload separation strategy (HTAP): rather than forcibly unifying transactions and analytics into a single storage model, a hybrid that tiers row storage (OLTP) + column storage (OLAP) and synchronizes them via CDC (Change Data Capture) and ETL/ELT is realistic. Recent HTAP engines evolve toward maintaining both representations internally while managing consistency and latency.
- Compression/performance trade-off: a high compression ratio reduces storage and I/O but demands decompression CPU at query time. Applying encoding/compression codecs and sort keys differentially by data temperature (hot/cold), and physically designing the layout to sort and cluster data by frequently filtered columns so the zone-map effect is realized, determines performance.
- Update/delete and regulatory response: columnar, immutable-file structures make individual deletion difficult. Responding to the right to erasure under GDPR and personal-information protection law, and the small-file explosion caused by frequent small updates, must be managed with periodic compaction, partition design, and merge-on-read/write strategies.
- Open formats and avoiding lock-in: adopting open formats and table formats such as Parquet and Iceberg reduces dependence (lock-in) on a specific vendor engine, and storage-compute separation secures the flexibility to swap and run engines in parallel. This is also an important selection criterion from a multi-cloud and cost-optimization (FinOps) perspective.
- Operations/monitoring perspective: because the performance of a columnar storage system is heavily governed by partition and sort-key design and compaction cadence, it requires the operational capability to continuously observe metrics such as small-file count, compaction lag, and skipping efficiency (the ratio of chunks actually read versus scanned) and to iteratively tune the physical design.
- Outlook: the scope of columnar storage is expanding to GPU-based vector processing, the integration of real-time streaming and batch, and analytical platforms that also hold vector embeddings ([[vector-database]]) and AI workloads. The standards competition among open table formats (a convergence trend centered on Iceberg) is also expected to be a key variable in future data-architecture choices.
References
- Apache Parquet, "File Format", https://parquet.apache.org/docs/file-format/
- Apache ORC, "ORC Specification", https://orc.apache.org/specification/
- Stonebraker et al., "C-Store: A Column-oriented DBMS", VLDB 2005, https://www.vldb.org/archives/website/2005/program/paper/thu/p553-stonebraker.pdf
- Ailamaki et al., "Weaving Relations for Cache Performance (PAX)", VLDB 2001, https://www.vldb.org/conf/2001/P169.pdf
- Apache Arrow, "Format", https://arrow.apache.org/docs/format/Columnar.html
In one line: Columnar storage is an analytics-optimal (OLAP) storage model that gathers data by column to realize selective reads, high compression, and vectorized execution, and through open hybrid formats such as Parquet and ORC and the lakehouse it has become the standard foundation of today's data-analytics infrastructure.