OLTP vs OLAP: row stores, column stores, warehouses

Placing an order reads and writes a handful of rows by key and must finish in milliseconds; revenue per city over the last 30 days reads 3 columns of 100,000 orders. The first is OLTPOLTP (online transaction processing): The work behind every click: many small reads and writes by key, each touching a few rows of current data and expected to finish in milliseconds., online transaction processing: the work behind every click, thousands of small queries a second on current data. The second is OLAPOLAP (online analytical processing): The work behind reports and dashboards: a few large queries that scan and aggregate many rows of history, in seconds to minutes., online analytical processing: the work behind reports and dashboards, a few large queries that scan and aggregate history.

The two want opposite storage layouts. A row storerow store: A storage layout that keeps all the columns of a row side by side in a page, so reading or writing one whole row touches one place. It suits lookups and writes by key. keeps each row together, which suits OLTP; a column storecolumn store: A storage layout that keeps each column separately, so a query reads only the columns it names and similar values sit together, where they compress well. keeps each column together, which suits OLAP, and builds compression, data skipping and batch execution on that layout. Running both workloads on one database lets a report slow the checkout, so systems keep them apart and move data from one to the other.

Row stores suit lookups, column stores suit scans

Set the two workloads side by side and they differ on every line:

A quick test sorts most queries: count the rows a query touches and how often it runs. A few rows found by key, thousands of times a second, is OLTP. Millions of rows, a few times an hour, is OLAP. The same business question can take both forms: “is this restaurant open?” is an OLTP lookup on every page view, while “which restaurants close early most often?” is an OLAP question over a year of history. Look at what each layout puts in a page:

A row store puts all of a row’s columns side by side in a page, so reading one order is one page read, and an OLTP query that wants a few whole rows reads little. An aggregation that needs 3 of 12 columns reads every page anyway, because every page holds whole rows. ClickHouse’s documentation describes the other side: a columnar database stores each column separately, so a query reads only the columns it uses, while operations on whole rows become more expensive (ClickHouse). The script lays the same 100,000 orders out both ways in 8 KiB pages and runs one aggregation on each:

# One table of 100,000 orders with 12 columns, laid out two ways in 8 KiB pages: row by row, and column by column.
# An analytical query (revenue per city over the last 30 days) runs on both layouts; each run counts the pages it
# has to read. The answers are the same; the bytes read are not.
import random
import struct

N, PAGE = 100_000, 8_192
CITIES = ["Pune", "Delhi", "Mumbai", "Kochi", "Indore", "Jaipur", "Surat", "Nagpur"]
STATUSES = ["delivered", "cancelled", "refunded"]
# name: struct format (fixed-width fields: text is padded, as fixed-width storage would hold it)
COLUMNS = {"id": "q", "created_at": "q", "user_id": "q", "restaurant_id": "q", "rider_id": "q", "city": "16s",
           "status": "12s", "payment": "12s", "amount": "q", "tip": "q", "distance_m": "q", "rating": "i"}

rng = random.Random(4)
start = 1_750_000_000
rows = []
for i in range(N):
    rows.append((i + 1, start + i * 78, rng.randrange(1, 40_000), rng.randrange(1, 3_000), rng.randrange(1, 9_000),
                 rng.choice(CITIES).encode(), rng.choice(STATUSES).encode(), b"card", rng.randrange(100, 2_000),
                 rng.randrange(0, 60), rng.randrange(300, 12_000), rng.randrange(1, 6)))
cutoff = start + N * 78 - 30 * 86_400  # the last 30 days of the data

# Row layout: whole rows, one after another.
row_format = struct.Struct("<" + "".join(COLUMNS.values()))
row_store = b"".join(row_format.pack(*r) for r in rows)

# Column layout: each column in its own run of pages.
column_store = {}
for j, (name, fmt) in enumerate(COLUMNS.items()):
    column_store[name] = b"".join(struct.pack("<" + fmt, r[j]) for r in rows)


def pages(nbytes):
    return -(-nbytes // PAGE)  # whole pages, rounded up


def query_rows():
    totals = {}
    for (_, created_at, *_, city, status, payment, amount, tip, dist, rating) in row_format.iter_unpack(row_store):
        if created_at >= cutoff:
            name = city.rstrip(b"\0").decode()
            totals[name] = totals.get(name, 0) + amount
    return totals, pages(len(row_store))  # every page holds whole rows, so every page is read


def query_columns():
    needed = ("created_at", "city", "amount")
    created = [x for (x,) in struct.iter_unpack("<q", column_store["created_at"])]
    city = [x.rstrip(b"\0").decode() for (x,) in struct.iter_unpack("<16s", column_store["city"])]
    amount = [x for (x,) in struct.iter_unpack("<q", column_store["amount"])]
    totals = {}
    for c, name, a in zip(created, city, amount):
        if c >= cutoff:
            totals[name] = totals.get(name, 0) + a
    return totals, sum(pages(len(column_store[n])) for n in needed)  # only three columns are read


by_rows, row_pages = query_rows()
by_columns, column_pages = query_columns()
assert by_rows == by_columns
print(f"{N:,} orders, {len(COLUMNS)} columns, {row_format.size} bytes per row")
print("Revenue per city, last 30 days: " + ", ".join(f"{c} {v:,}" for c, v in sorted(by_rows.items())))
print(f"row layout:    reads {row_pages:>5,} pages ({row_pages * PAGE / 1e6:.1f} MB), every column of every row")
print(f"column layout: reads {column_pages:>5,} pages ({column_pages * PAGE / 1e6:.1f} MB), 3 of the 12 columns")
print(f"The column layout reads {column_pages / row_pages:.0%} of the bytes for the same answer.")

100,000 orders, 12 columns, 108 bytes per row
Revenue per city, last 30 days: Delhi 4,466,753, Indore 4,263,998, Jaipur 4,392,555, Kochi 4,289,533, Mumbai 4,379,126, Nagpur 4,425,615, Pune 4,506,963, Surat 4,259,669
row layout:    reads 1,319 pages (10.8 MB), every column of every row
column layout: reads   392 pages (3.2 MB), 3 of the 12 columns
The column layout reads 30% of the bytes for the same answer.

The script checks that both layouts give the same revenue for every city. The three columns the query names, created_at, city and amount, take 32 of each row’s 108 bytes, so the column layout read 392 pages where the row layout read all 1,319: 30 % of the bytes, before any compression. Change the query to name more columns and the gap closes; a query that wants whole rows is where the row layout wins.

Analytical data usually gets a home of its own, such as a data warehousedata warehouse: A column store with SQL, usually managed, loaded from an organisation's sources and used for shared reporting with governed access.: a managed SQL service built on a column store and loaded from the operational databases. Reports leave the primary for a read replicaread replica: A copy of a database that follows its primary's changes and serves read-only queries, so that reads such as reports move off the primary., or for a warehouse fed by nightly exports or a change stream. Its alternatives are a lakehouselakehouse: Columnar files in object storage with a table format on top, so that several engines can query very large or long-kept history as tables. of columnar files in object storage, an embedded engine such as DuckDB, and HTAPHTAP (hybrid transactional and analytical processing): A database product that keeps row and column copies of the same data, so analytics run on fresh operational data without a separate pipeline., one database holding both layouts.

Columns compress because neighbouring values are alike Pro: opens with a Pro, Premium or Ultimate pass