System designCore8 min
OLTP vs OLAP & columnar storage
The database that records each sale is built to touch a few whole rows quickly. The questions a business asks afterwards touch a few columns of billions of rows. Storing data by column instead of by row turns those questions from hours of reading into seconds, and makes single-row writes expensive, which is why most companies run both kinds of database.
1 · Two workloads
Transactions and analysis ask opposite things of a database
A hardware store chain has 300 stores. Every checkout records a sale and its lines, looks up prices and lowers stock: small reads and writes of whole rows, thousands a minute, each needing an answer in milliseconds. That's online transaction processing (OLTP). Every Monday the buyers ask a different kind of question: revenue by product category and region, week by week, for the last two years. That's online analytical processing (OLAP).
Aspect | OLTP: the checkout | OLAP: the weekly report |
|---|---|---|
Rows per query | One sale and its few lines | Hundreds of millions of sale lines |
Columns per query | Most of each row | 4 of the table's 25 |
Writes | Constant, row by row | Bulk loads; old data rarely changes |
Users | Thousands of tills at once | A few analysts and dashboards |
What matters | Latency per request | Throughput of a scan |
The sale lines table has grown to 2 billion rows, about 400 GB. Running the report on the checkout database would read most of it, competing with the tills for disk and memory. The two are ends of a spectrum rather than strict categories, but the trade-off between them remains.
2 · Columns
A row store reads whole rows; a column store reads only the columns asked for
A row store keeps each row's values together on disk, so one write saves a whole sale line, and one read fetches it. That's ideal for the checkout. The weekly report needs only four columns: date, store, category and amount. In a row store, those values are interleaved with 21 columns it doesn't need, and the disk has to read them all.
A column store keeps each column in its own blocks: all dates together, all amounts together. A query reads only the blocks of the columns it names. The arithmetic: a query using 5 of 100 columns reads about 5% of the data. The report also filters to two of the five years stored, and column files keep the minimum and maximum value of each block, so whole blocks outside the date range are skipped unread:
Data read by the weekly report, before compression, assuming columns of similar size: 400 × 4/25 = 64 GB, then × 2/5 for the dates. Compression shrinks the column store's share much further.
Parquet, the common file format for columnar data, is built the same way: a file is split into row groups, each holding one contiguous chunk per column, with min and max statistics for each chunk and page. Columnar databases such as ClickHouse and Redshift store their tables the same way.
3 · Compression
A column holds one kind of value, so it compresses far better
A block of a row store mixes dates, names, prices and text. A block of a column store holds one type, often with few distinct values, often in order. Each column can then use a compression scheme chosen for its type. Parquet defines encodings that exploit this before any general compressor runs:
Encoding | How it works | Fits the column |
|---|---|---|
Dictionary | Store each distinct value once; the column holds small integer codes | Category: 40 values repeated 2 billion times |
Run length | Store a value and how many times it repeats | Store ID, when rows are sorted by store |
Delta | Store the difference from the previous value | Timestamps and IDs that grow steadily |
The category column for 2 billion rows. 40 categories fit in 6-bit codes (2⁶ = 64), so 2 × 10⁹ × 6 bits = 1.5 GB. A general compressor such as LZ4, ClickHouse's default, then runs on top.
Column stores get a further gain: the query engine can work on the compressed values directly, and decompress only when values are finally returned to the application.
4 · Fast scans
Sort order decides what a scan can skip
Column stores don't index every row the way a B-tree does. Instead, they sort the data and keep a small index over blocks. In ClickHouse, the table's primary key is its sort order, and a filter on it uses a binary search over blocks of 8,192 rows instead of scanning the column. The chain sorts sale lines by date and then store:
A ClickHouse table sorted for date-range reports
CREATE TABLE sale_lines (
sale_date Date,
store_id UInt16,
category LowCardinality(String), -- dictionary-encoded
sku UInt32,
quantity UInt16,
amount Decimal(10, 2)
-- ... 19 more columns
)
ENGINE = MergeTree
ORDER BY (sale_date, store_id);Sorting also feeds compression: rows of the same day and store sit together, so long runs of equal values appear. A query that filters on something else, such as one SKU across all dates, can't skip much; ClickHouse offers projections, extra copies of the table sorted another way, for a second common filter.
Execution is built for scans too. ClickHouse passes values between operators in batches rather than one row at a time, which uses CPU caches well and lets single instructions process several values at once (SIMD). Engines built this way are said to use vectorized execution.
5 · Writes
The price is paid on writes and updates
What makes reads cheap makes row operations dear. Inserting one sale line means appending to 25 separate column files; the cost of appending or modifying a row grows with the number of columns. Each insert also creates a new part on disk that background merges must later combine, so ClickHouse wants batches of at least 1,000 rows, ideally 10,000 to 100,000.
Updates and deletes are worse. A ClickHouse mutation rewrites every part of the table it touches, and a delete rewrites all columns. Lightweight deletes only mark rows as deleted, hide them from queries and remove them at a later merge. The design favors appending; changing old rows is expected to be rare. That's why the checkout never writes to the column store directly:
Each system does what its storage layout is good at. The cost is a pipeline between them and a delay: reports see sales a minute or so late.
6 · In practice
Run transactions on rows and analysis on columns, and connect them
Most companies end up with both: a row-oriented database for the application and a columnar warehouse or analytical database for questions, fed by a pipeline (Data warehouses, lakes & pipelines, Change data capture). Running two systems costs more to maintain, and databases are moving toward hybrids (HTAP) that handle both; the trade-off between the two layouts remains either way.
- Separate the workloads
Never run large scans against the database serving users.
- Store analytics by column
A columnar database or Parquet files on object storage.
- Sort by the common filter
Usually time first, so date ranges skip most blocks.
- Load in batches
Thousands of rows per insert, never one at a time.
- Treat old rows as fixed
Avoid updates; correct with new rows or rewrite partitions.
Check yourself
4 questions
Takeaways
Remember this
- OLTP reads and writes a few whole rows fast; OLAP scans a few columns of very many rows.
- Column stores read only the columns a query names and skip blocks using min and max values.
- One type per column makes dictionary, run-length and delta encodings very effective.
- Sort order is the main index of a column store: choose it for the most common filter, usually time.
- Single-row writes, updates and deletes are expensive, so feed analytical stores in batches from the OLTP database.