Row-Oriented vs. Columnar Storage: Architecture of Transactional and Analytical Engines

Every relational database engine organizes structured tables into physical byte arrays on persistent disk pages and in-memory buffers. The fundamental axis of database physical layout design is whether data is organized by row (N-ary Storage Model / NSM) or by column (Decomposed Storage Model / DSM).

Row-oriented storage is optimal for Online Transaction Processing (OLTP) workloads that insert, update, or retrieve individual, full records by primary key. Conversely, Column-oriented storage is essential for Online Analytical Processing (OLAP) systems that scan millions of rows to compute aggregations (`SUM`, `AVG`, `COUNT`) across only a handful of specific columns.

Physical Layout Comparison: NSM vs. DSM vs. PAX

1. N-ary Storage Model (Row-Store / NSM)

Attributes of a single tuple are stored contiguously on a disk page: `[Row1_ColA, Row1_ColB, Row1_ColC], [Row2_ColA, Row2_ColB, ...]`.

• Strengths: Single-row inserts, point lookups (`SELECT * FROM users WHERE id = 42`), and atomic in-place updates require touching only a single disk page or cache line.
• Bottlenecks for OLAP: An aggregation query like `SELECT AVG(age) FROM users` forces the engine to read every single byte of every column (names, bios, addresses) from disk into RAM, wasting up to 95% of memory and I/O bandwidth.

2. Decomposed Storage Model (Column-Store / DSM)

Values of a single column across all rows are stored contiguously in memory and on disk: `[Row1_ColA, Row2_ColA, Row3_ColA, ...], [Row1_ColB, Row2_ColB, ...]`.

• Strengths: Analytical queries load strictly the required columns from storage, maximizing I/O efficiency. Because identical data types reside next to each other, compression ratios reach 5x to 10x higher than row stores.
• Bottlenecks for OLTP: Inserting a single row requires mutating multiple physically separated files or page blocks across all columns, making individual OLTP writes prohibitively expensive.

3. Partition Attributes Across (PAX Hybrid Model)

Introduced by Anastassia Ailamaki et al., PAX retains traditional row-based page partitioning to avoid multi-file insert locks, but organizes data within each page into columnar minipages. This provides columnar cache-locality within the page while keeping single-page transaction boundaries intact (used widely in Apache Parquet and ORC file formats).

Columnar Compression Primitives

Because columnar layouts group data of identical types and similar domains together, engines exploit specialized lightweight compression algorithms that execute without costly decompression overhead:

1. Run-Length Encoding (RLE): Replaces repeated contiguous values with a `(value, run_length)` pair (e.g., `['CA', 'CA', 'CA', 'NY']` becomes `('CA', 3), ('NY', 1)`), reducing low-cardinality sorted columns to minimal memory.
2. Dictionary Encoding: Maps repeated strings to small integer identifiers (e.g., `uint8_t` or `uint16_t`) and stores a compact lookup array.
3. Bit-Packing & Frame of Reference (FoR): Subtracts a base reference minimum value and packs the residual delta values using only the exact minimum number of bits needed (e.g., packing numbers under 15 into 4 bits instead of 32-bit integers).
4. Delta / Gorilla Compression: Stores consecutive XOR or numerical differences between successive timestamps or floating-point numbers, used extensively in time-series engines.

Execution Engine Paradigms: Volcano vs. Vectorized (SIMD)

The storage format directly dictates the CPU execution model of the database engine:

1. Volcano Iterator Model (Tuple-at-a-time)

Traditional row engines execute queries via nested `next()` method calls per row. This incurs massive virtual function call overhead, frequent CPU branch mispredictions, and poor CPU L1 instruction cache utilization.

2. Vectorized Engine Model (Block-at-a-time / MonetDB / DuckDB Style)

Operators process contiguous flat arrays of values (typically 1,024 to 2,048 elements at a time). This allows the C++ compiler to auto-vectorize tight loops using CPU SIMD (Single Instruction, Multiple Data) instructions (such as AVX-512 and ARM NEON), computing filters and sums across multiple data points per single CPU clock cycle.

C++ Conceptual Simulation Blueprint (Vectorized Columnar Scan vs Row Scan)

#include <iostream>
#include <vector>
#include <numeric>
#include <cstdint>

// 1. Row-Oriented Representation (NSM)
struct UserRow {
    uint64_t id;
    uint32_t age;
    double balance;
    char name[64];
};

// 2. Column-Oriented Representation (DSM / Vectorized Chunk)
struct UserColumnChunk {
    std::vector<uint64_t> id;
    std::vector<uint32_t> age;
    std::vector<double> balance;
    // Name column omitted from memory during pure numerical scans
};

class ColumnarAggregationEngine {
public:
    // Vectorized aggregation operating on contiguous primitive arrays
    static double computeAverageAge(const std::vector<uint32_t>& ages) {
        if (ages.empty()) return 0.0;
        uint64_t sum = 0;
        size_t n = ages.size();
        
        // Contiguous memory layout allows auto-vectorization (SIMD)
        #pragma GCC ivdep
        for (size_t i = 0; i < n; ++i) {
            sum += ages[i];
        }
        return static_cast<double>(sum) / n;
    }
};

Real-World Systems and Production Implementations

1. Cloud Data Warehouses (Snowflake, Google BigQuery, Amazon Redshift): Utilizing columnar micro-partitions to process petabyte-scale analytical queries with high compression and data pruning.
2. Embedded OLAP (DuckDB): Modern in-process vectorized columnar database designed for analytical pipelines, interactive dashboards, and Python data frames.
3. High-Throughput Analytics (ClickHouse, Apache Doris): Utilizing Column-Oriented MergeTree engines and SIMD instructions to scan billions of rows per second per server.
4. Open Columnar File Standards (Apache Parquet, Apache ORC, Apache Arrow): Serving as the interchange formats for distributed computing frameworks (Apache Spark, Trino, Presto, Polars).