OLTP vs OLAP: warehouses and columnar storage

OLTP (online transaction processing) is the work an application does for its users: many short transactions that each read or change a handful of rows by key. OLAP (online analytical processing) is the work analysts and dashboards do: a few large queries that scan millions or billions of rows but only a few columns. The two want opposite storage designs, which is why serious systems keep a row store for transactions and a separate column store, a warehouse, for analytics.

Picture it first: the counter and the accountant

A busy kirana shop has a billing counter. Each customer takes seconds: look up the price of one item, add it to one bill, take payment, next. The counter must never stall, because a queue forms instantly.

At month end the owner’s accountant wants “sales of each brand, week by week, for the whole year”. She doesn’t need anyone’s name or address, only two things from every bill: brand and amount. If she sits at the counter flipping through a year of bills during the evening rush, the queue goes out of the door. So the owner photocopies the day’s bills every night and copies just the brand and amount into two long registers, one per column, for her to total at leisure.

Map it back: the counter is OLTP, the accountant is OLAP, the nightly copy is ETL or CDC, and the two registers are columnar storage.

Two workloads, by the numbers

Take a food-delivery platform. At dinner peak its order service might run 20,000 transactions a second, each touching 5 to 20 rows by primary key, with a p99 budget of a few tens of milliseconds. Meanwhile an analyst asks for average delivery time by area and hour over the last year: one query that scans a billion order rows but reads only four columns, and nobody minds if it takes ten seconds.

Why analytics hurts the primary

Running that billion-row query on the database that takes orders hurts in three ways:

• It competes for I/O and CPU. A scan of hundreds of gigabytes uses the same disks and cores as checkout. Engines protect their caches (PostgreSQL reads big sequential scans through a small 256 KB ring of buffers; InnoDB inserts new pages mid-way down its LRU list), but nothing protects the disk bandwidth or the CPU.
• It holds back cleanup. Under MVCC, a query that runs for 40 minutes keeps a snapshot open, so row versions updated or deleted after it started cannot be removed, and tables bloat (vacuum and bloat).
• It is the wrong shape anyway. A row store must read every column of every row it scans (unless an index happens to cover the query), as the next section shows.

The usual path out has three steps.

A read replica is the cheap first move: dashboards query a streaming standby, and checkout never notices. The catch is a conflict PostgreSQL’s hot standby documentation spells out. Replaying the primary’s cleanup can remove row versions a long replica query still needs, so the standby must pick one: cancel the query (after max_standby_streaming_delay, 30 seconds by default), delay replay so the replica falls behind, or turn on hot_standby_feedback, which makes the primary keep those versions, so it bloats as if the query ran there. And the replica is still a row store.

A separate analytics store is the real fix. Changes flow out of the primary, either as periodic extracts (ETL: extract, transform, then load; or ELT: load raw, transform inside the warehouse with SQL) or continuously through change data capture into Kafka and on to the warehouse. Whether nightly batches are enough or you need minutes-fresh streams is the batch vs stream processing question.

Row vs column storage: the arithmetic

Take an orders table with 1 billion rows and 100 columns. Assume columns average 8 bytes (ids, amounts, timestamps and short codes), so a row is about 800 bytes and the table about 1,000,000,000 × 800 B = 800 GB. Now run:

SELECT city, SUM(amount) FROM orders GROUP BY city;

• Row store: pages hold whole rows, so the scan reads all 800 GB. At 1 GB/s of sequential reads that is about 800 s, over 13 minutes.
• Column store: each column is stored separately, so the scan reads two columns: 1,000,000,000 × (8 + 8) B = 16 GB, about 16 s at the same speed. That is 800 ÷ 16 = 50× less I/O before any compression.
• With compression (next section), city shrinks to about 3 bits per value and amount to perhaps 4 bytes: 0.375 GB + 4 GB ≈ 4.4 GB, roughly 180× less than the row store.

The C-Store paper (Stonebraker and colleagues, 2005) named the trade: a row store is write-optimized, since one write puts a whole record on disk, while a column store is read-optimized. The flip side is equally sharp. Fetching one complete order is a single page read in a row store, but up to 100 separate reads in a column store, one per column, and inserting one row means appending to 100 places.

Why columns compress so well

A column holds values of one type and often very few distinct ones, which gives encodings far more to work with than a row of mixed fields. The common ones (all in Parquet’s encodings, and in some form in every column store):

• Dictionary encoding: store each distinct value once and replace values with small integer codes.
• Bit-packing: store codes in as few bits as they need; 8 distinct values fit in 3 bits.
• Run-length encoding (RLE): store “Pune × 402,118” instead of the value 402,118 times. It needs repetition in a row, which sorting provides.
• Delta encoding: store the difference from the previous value. Sorted timestamps a few milliseconds apart need a byte or two each, not eight; C-Store’s example turns 1, 4, 7, 7, 8, 12 into 1, 3, 3, 0, 1, 4.

Compression pays twice: fewer bytes come off disk, and engines can often evaluate predicates directly on codes (the C-Store paper argues for operating on compressed data “whenever possible”). The catch is that sorting by one column helps that column’s runs most; C-Store answered by storing overlapping projections, the same columns sorted different ways.

Reading less, and reading faster

Column stores add three more tricks on top of layout and compression.

Zone maps and predicate pushdown. Data is split into chunks, and each chunk’s metadata records the minimum and maximum of every column. A filter is checked against those first, and chunks that cannot match are never read. Amazon Redshift keeps min and max for every 1 MB block; with five years of data sorted by date, a one-month query can skip up to 98% of blocks. Parquet does the same at several levels: a file has row groups, each holding one column chunk per column, divided into pages, with statistics in the footer and an optional page index.

Zone maps only work when similar values sit together, so the sort key matters as much as an index does in OLTP. ClickHouse makes this explicit: each part is sorted by the table’s ORDER BY key, and a sparse primary index stores one entry per granule of 8,192 rows, small enough to stay in memory.

Vectorized execution. Instead of passing one row at a time between operators, engines pass batches of a column’s values, which keeps CPU caches warm and lets one SIMD instruction process several values at once. ClickHouse describes its engine this way, and DuckDB calls itself a columnar-vectorized engine.

Late materialization. Rows are stitched together as late as possible. In C-Store, a select operator outputs a bitstring of matching positions rather than rows, and a mask operator uses it to pull only those positions from the next column, so the filter on city decides which amount values are ever touched.

Writes: the price of columns

Everything that makes reads cheap makes small writes expensive. One inserted row touches every column file; an update must find a value inside compressed, sorted blocks. So column stores buffer writes and reorganise in the background, much like an LSM tree:

• C-Store paired a small writeable store for inserts with a large read-optimized store, joined by a tuple mover that migrates batches in bulk; updates became a delete plus an insert.
• ClickHouse’s MergeTree writes each insert as a new sorted part and merges parts in the background. Its docs recommend batches of at least 1,000 rows, ideally 10,000 to 100,000; thousands of tiny inserts produce too many parts.
• SQL Server columnstore indexes collect small inserts in a B-tree deltastore until a rowgroup of up to 1,048,576 rows is ready, then a tuple-mover compresses it.

The rule for design: load in batches, prefer appends, and treat updates and deletes as occasional bulk operations.

Data warehouses and dimensional modelling

A data warehouse is an analytical database built to answer business questions across sources. Its standard shape comes from Ralph Kimball’s dimensional modelling, which the normalization vs denormalization page introduces; here is the working detail.

• Fact table: one row per measurement event, holding numeric measures plus a foreign key to each dimension. Facts are long and narrow; this is where the billions of rows live.
• Dimension tables: the who, what, where and when, as wide, flat tables of descriptive attributes that queries filter and group by.
• Grain: what exactly one fact row represents. Kimball calls declaring it the pivotal step, done before choosing dimensions or facts, and advises the most atomic grain, since summaries presuppose the questions.
• Additivity: revenue sums across every dimension; a wallet balance sums across customers but not across days (semi-additive); a ratio sums across nothing, so store its parts.

A typical question then reads naturally:

-- Veg revenue by restaurant city and week, last 12 weeks
SELECT r.city, d.week, SUM(f.item_amount) AS revenue
FROM fact_order_line f
JOIN dim_date d       ON d.date_key = f.date_key
JOIN dim_restaurant r ON r.restaurant_key = f.restaurant_key
JOIN dim_dish x       ON x.dish_key = f.dish_key
WHERE x.is_veg
  AND d.date >= CURRENT_DATE - 84
GROUP BY r.city, d.week
ORDER BY r.city, d.week;

Star or snowflake? A snowflake normalizes dimensions into hierarchies (restaurant → city → state). You save a little space and get one place to rename a state; you pay with more joins for every query and a model that is harder for analysts to navigate. Dimensions are tiny next to facts, so the saving rarely matters; stars are the default.

Slowly changing dimensions. A customer moves from Pune to Bengaluru. Type 1 overwrites the city: simple, but history is rewritten, and last year’s Pune orders now report as Bengaluru. Type 2 adds a new dimension row with a new surrogate key and marks the old one expired, so old facts keep pointing at the Pune version:

-- Type 2 change: expire the current row, add a new version
UPDATE dim_customer SET valid_to = DATE '2026-10-27', is_current = false
WHERE customer_id = 'c_881' AND is_current;
INSERT INTO dim_customer (customer_key, customer_id, city, valid_from, valid_to, is_current)
VALUES (90412, 'c_881', 'Bengaluru', DATE '2026-10-27', DATE '9999-12-31', true);

When several fact tables (orders, deliveries, refunds) share the same date, restaurant and customer dimensions, Kimball calls them conformed, and that is what lets one report line them up side by side.

The modern landscape

MPP warehouses spread data across many nodes and run each query on all of them in parallel (massively parallel processing).

• Google Dremel and BigQuery. Dremel (Melnik and colleagues, 2010) combined columnar storage for nested records with a serving tree: a root server rewrites the query, intermediate servers aggregate partial results, and thousands of leaf servers scan in parallel. On an 85-billion-record table, reading columns instead of records cut the data read from 87 TB to about 0.5 TB. Google later described those ideas as the foundation of BigQuery.
• Snowflake keeps data as compressed columnar micro-partitions in cloud object storage and runs queries on virtual warehouses, independent compute clusters that don’t share resources, so the finance team’s month-end job can’t slow the product dashboard. Its docs call this a hybrid of shared-disk and shared-nothing (database architectures explains both).
• Amazon Redshift stores columns in 1 MB blocks across node slices. You choose a distribution style (KEY co-locates rows that join, ALL copies a table to every node, EVEN spreads round-robin, AUTO picks) and a sort key for zone maps.

Real-time OLAP. ClickHouse targets dashboards over fresh event data: continuous batched inserts and fast aggregates for many concurrent users. It suits user-facing analytics such as ad-click aggregation or web analytics.

Lake, warehouse, lakehouse. A data lake is files on object storage, usually Parquet or ORC: cheap, open, readable by many engines, but just files, with no transactions or schema enforcement. A warehouse manages its own storage with full SQL semantics. A lakehouse adds a table format such as Apache Iceberg on top of the lake: each change writes new immutable data files and a new metadata file swapped in atomically, every snapshot is a consistent version of the table, readers never see a half-done write, and per-file column statistics in the manifests allow pruning. You gain warehouse-like tables on open files that several engines can query; you pay with a catalog to run and compaction jobs for small files.

HTAP and embedded OLAP. Hybrid transactional/analytical processing runs both on one system. SQL Server, for example, can put a columnstore index on a transactional table for “operational analytics”, with the deltastore absorbing small writes. You gain fresh analytics without a pipeline; you pay with both workloads sharing one server’s resources. At the small end, DuckDB runs columnar queries inside your process, ideal for a notebook or a single service (database architectures).

Choosing where analytics runs

In an interview, name the split and the pipe: “Orders live in PostgreSQL. Debezium-style CDC streams changes to Kafka; one consumer loads ClickHouse for the live restaurant dashboard, another lands Parquet in an Iceberg table for the warehouse. Analysts never touch the primary.”

Going deeper: how analytics systems fail

Too many small files or parts. Streaming every event as its own insert gives ClickHouse thousands of tiny parts to merge, and gives a lake millions of tiny Parquet files whose metadata dwarfs the data. Batch on the way in, and schedule compaction.

Deletes and updates. A privacy deletion request means rewriting compressed files or parts that contain that customer. Iceberg’s format version 2 added row-level deletes so engines can record deletions without rewriting at once, but someone still compacts later. Design partitions so that most deletes drop whole partitions (table partitioning).

Skewed distribution. Distribute a fact table on brand_id and one giant chain’s orders all land on one slice, which does most of the work while the others idle. Pick high-cardinality, evenly spread keys. Redshift’s docs even note that replicating small dimensions with ALL brings little benefit, because redistributing a small table at query time is cheap.

SELECT * on a column store. It reads every column, throwing away the main advantage, and on warehouses that bill by data scanned it costs real money. Select only the columns you need, filter on the sort or partition key.

Pipelines that drift. CDC carries updates and deletes, not just inserts, so the warehouse needs MERGE logic, handling for late or duplicate events, and schema changes that don’t break loads. Reconcile daily totals against the source.

Cheat sheet

How to say it in the interview

“OLTP and OLAP want opposite storage. Checkout does thousands of small keyed transactions a second, so orders stay in a normalized PostgreSQL row store with indexes. Analytics scans billions of rows but a few columns, so it belongs in a column store: reading 2 of 100 columns is 2% of the bytes, compression shrinks that several-fold, and zone maps skip blocks that can’t match. I won’t run those scans on the primary, since they compete for I/O and hold back vacuum. Instead CDC streams order changes to Kafka; ClickHouse serves the live restaurant dashboard within seconds, and a warehouse holds a star schema at order-line grain for analysts.”

Why are column stores fast for aggregates but slow for point updates? An aggregate reads only the columns it needs, compressed and in large sequential chunks, processed in vectorized batches. A point update must find one value inside compressed, sorted blocks across many column files and rewrite them, so column stores batch writes into a delta store or new parts and merge later.

Star or snowflake? Star by default: flat dimensions mean one join per dimension and a model analysts can read. Snowflaking normalizes dimension hierarchies, which saves little space because dimensions are small, and adds joins to every query.

Can’t I just add indexes for analytics? Indexes help selective queries that touch a few rows. An aggregate over most of the table still reads most of it (index scan vs full table scan), and every extra index slows every write on the OLTP path. A covering index is really a partial column copy; past a point, a real column store is cheaper.

ETL or ELT? ETL transforms data before loading, so the warehouse only ever sees clean, modelled tables, at the cost of a separate transform tier to run. ELT loads raw data first and transforms with SQL inside the warehouse: simpler to change and replay, paid for in warehouse compute and raw data to govern.

Where to go next

• SQL vs NoSQL and database types: where columnar warehouses sit among database families.
• Database architectures: shared-disk, shared-nothing and separated storage and compute.
• Normalization vs denormalization: why the source stays normalized and the warehouse doesn’t.
• Reads and writes: the row-store engines on the OLTP side.
• Change data capture: moving changes out of the primary reliably.
• Batch vs stream processing: nightly loads or continuous streams.
• Design ad-click aggregation and design web analytics: OLAP stores inside full designs.

References and further reading

1. C-Store: A Column-oriented DBMS (Stonebraker et al., VLDB 2005)cs.umd.eduRow stores as write-optimized and column stores as read-optimized, sorted projections, dense packing and encodings, operators on compressed data, and the writeable store, read-optimized store and tuple mover.
2. Dremel: Interactive Analysis of Web-Scale Datasets (Melnik et al., VLDB 2010)static.googleusercontent.comColumnar storage for nested records, the multi-level serving tree, and the experiment where columnar reads cut 87 TB to about 0.5 TB.
3. Snowflake documentation: Key concepts and architecturedocs.snowflake.comHybrid shared-disk and shared-nothing design, compressed columnar micro-partitions in cloud storage, and independent virtual warehouses.
4. Apache Parquet documentation: Encodingsparquet.apache.orgDictionary encoding with an RLE and bit-packing hybrid for the indices, and delta binary packing for integers; the file-format and concepts pages give row groups, column chunks and pages.
5. ClickHouse documentation: MergeTree table engineclickhouse.comInserts create sorted parts that background merges combine, a sparse primary index over 8,192-row granules, and one file per column in wide parts.
6. Amazon Redshift documentation: Sort keysdocs.aws.amazon.com1 MB column blocks with min and max values in metadata, and skipping up to 98% of blocks for one month out of five years sorted by date.
7. Kimball Group: Dimensional modeling techniqueskimballgroup.comFact and dimension table structure, declaring the grain first, additive and semi-additive facts, conformed dimensions, and slowly changing dimension types 1 and 2.
8. Apache Iceberg table specificationiceberg.apache.orgA table format over immutable Parquet, Avro or ORC files: snapshots, manifests with per-file metrics, atomic metadata swaps and serializable isolation for readers.
9. Microsoft Learn: Columnstore indexes overview (SQL Server)learn.microsoft.comColumnstore indexes for operational analytics on a transactional workload, rowgroups of up to 1,048,576 rows, and a B-tree deltastore drained by the tuple-mover.
10. PostgreSQL documentation: Hot standbypostgresql.orgQuery conflicts on a standby: cancel the query after max_standby_streaming_delay, delay replay, or use hot_standby_feedback and accept bloat on the primary.