OLTP vs OLAP — Row Stores vs Column Stores

TL;DR

Two workload shapes, two storage layouts:

• OLTP (Online Transaction Processing) — many small, point-lookup reads; many small writes; transactions matter. Powered by row stores (Postgres, MySQL, SQL Server, Oracle). The unit of I/O is the row.
• OLAP (Online Analytical Processing) — fewer queries, each scanning millions to billions of rows; mostly aggregations; writes are bulk/batch. Powered by column stores (Snowflake, BigQuery, Redshift, ClickHouse, DuckDB). The unit of I/O is the column.

Both speak SQL. Both look like “tables”. The internal representation is fundamentally different, and so are the queries each is good at.

The right tool for ML feature engineering is almost always a column store (warehouse). The right tool for serving features online with millisecond latency is almost always a row store (Postgres, key-value).

The picture in your head

A row store stores one row’s bytes contiguously on disk:

disk layout (row store):
[id=1, name=Alice, country=US, total=19.99]
[id=2, name=Bob,   country=US, total=42.00]
[id=3, name=Carol, country=UK, total=99.00]
...

To read WHERE id = 2, the engine seeks to one row, reads it. Fast for point lookups. Bad for SELECT AVG(total) — to read just the total column, it must touch every row’s full bytes (because they’re interleaved).

A column store stores each column’s values contiguously:

disk layout (column store):
ids:       [1, 2, 3, ...]
names:     [Alice, Bob, Carol, ...]
countries: [US, US, UK, ...]
totals:    [19.99, 42.00, 99.00, ...]

To read WHERE id = 2 SELECT *, the engine has to assemble the row from multiple column files — costly. To compute SELECT AVG(total), it reads only the totals column — sequential, cache-friendly, vectorizable, compressible.

Column stores also compress aggressively (many columns have low cardinality — country has ~250 distinct values for billions of rows; dictionary encoding crushes it). Real-world column stores are 10–100× smaller on disk than the equivalent row store.

Workload comparison

The same query, two engines

Same data: 1 billion rows in events(user_id, ts, kind, payload).

Query. “Daily click count for the last 30 days.”

SELECT date_trunc('day', ts)::date AS day, COUNT(*)
FROM   events
WHERE  ts >= CURRENT_DATE - INTERVAL '30 days' AND kind = 'click'
GROUP  BY day
ORDER  BY day;

Postgres (row store):

• Without an index: full scan. 1B rows × ~100 bytes each = 100GB read. Probably 10+ minutes.
• With idx (ts, kind): scans the relevant slice but still reads each full row to materialize the count. Maybe 30 seconds.
• The right answer is partition by ts (see SQL 305), then it’s seconds.

Snowflake/BigQuery/DuckDB (column store):

• Reads only the ts and kind columns. ~5GB. Compressed, ~500MB.
• Vectorized scan + group, parallelized across many cores.
• Sub-10-second response, no indexes needed.

For analytics, the column store wins by an order of magnitude with no tuning. For point lookups (WHERE event_id = 12345), Postgres wins.

Hybrid systems

Real architectures usually combine both:

A typical ML pipeline: events flow through Kafka into a warehouse (Snowflake), feature SQL transforms run there (dbt models), the resulting feature table is exported to a key-value store (Redis) for online serving. Each layer is the right tool for its workload.

ClickHouse / Druid / Pinot — the in-between

Some systems are columnar but designed for sub-second latency on real-time data — they sit between traditional row-store OLTP and warehouse OLAP.

• ClickHouse — open-source, scales to PB; the go-to for high-volume analytics with sub-second query times. Used by Cloudflare, Uber, Yandex.
• Apache Druid — pre-aggregates time-series; great for dashboards.
• Apache Pinot — similar; LinkedIn’s analytics layer.

Use these when warehouse latency (10s of seconds) is too slow for the use case — interactive dashboards over event streams, real-time monitoring.

DuckDB — the local column store

DuckDB is a single-file embedded column store, like SQLite for analytics. Reads CSV/Parquet/Arrow directly:

SELECT COUNT(*) FROM 'events.parquet' WHERE kind = 'click';

For local notebooks, ETL prototyping, and “I have a CSV and want to query it without standing up a database”, DuckDB is the right tool. Beats Pandas on most operations once the data is over a few hundred MB.

Common pitfalls

• Using a warehouse for transactional workloads. Snowflake on a per-request hot path is slow and expensive. Use Postgres.
• Using Postgres for warehouse-scale analytics. A 100M-row GROUP BY on Postgres tries; a column store does it in seconds.
• SELECT * on a column store. You pay for every column read. BigQuery literally bills per byte scanned. Always name columns.
• Row-by-row INSERT into a column store. Catastrophically slow. Bulk-load (COPY, MERGE, INSERT … SELECT) instead.
• Writing OLTP-style queries against an OLAP store. Frequent point lookups (WHERE id = ?) on a column store are slower than the equivalent on Postgres. The column layout doesn’t help.

Production patterns for ML

1. Two-tier feature pipeline. Train in the warehouse, serve from key-value:

Snowflake: nightly SQL builds user_features table
              |
            Export
              |
              v
Redis / DynamoDB: feature blob keyed by user_id
              |
              v
Online prediction service: 1ms feature lookup, fast model inference

The same SQL builds both the training set (full historical pull from Snowflake) and the serving features (per-user latest snapshot exported to Redis).

2. Notebook → DuckDB → warehouse promotion. Prototype features in a notebook with DuckDB on a Parquet sample. Once the SQL is right, promote it to a dbt model that runs in Snowflake on the full dataset. Same syntax, two scales.

Resources

• Designing Data-Intensive Applications, ch. 3 — dataintensive.net — storage and retrieval.
• ClickHouse — column-oriented databases — clickhouse.com/docs
• DuckDB documentation — duckdb.org/docs
• Snowflake documentation — docs.snowflake.com
• BigQuery documentation — cloud.google.com/bigquery/docs
• PostgreSQL documentation — postgresql.org/docs