OLTP vs OLAP: row store, or column store?

OLTP (online transaction processing) is optimized for many small, fast transactions — place an order, log in, update a row — using row-oriented, normalized storage. OLAP (online analytical processing) is optimized for complex aggregate queries over huge historical datasets — revenue by region by month — using column-oriented, often denormalized (star/snowflake schema) storage.

Virtually every real company runs both: an OLTP database for the live app, and a continuous ETL or CDC (change data capture) pipeline replicating that data into an OLAP warehouse for analytics. This isn't redundancy — it's specifically so heavy analytical queries never compete for resources with, or slow down, the live transactional workload the actual product depends on.

An OLTP row store lays a full row contiguously on disk, so "fetch order #4471" is one cheap, localized read — exactly what a live application constantly needs. But "sum the revenue column across 50 million orders" on that same row store means reading every row's ENTIRE contents — every column, not just revenue — just to touch the one field you actually wanted, because the storage engine can't skip the columns you don't need when a row is stored as one contiguous chunk.

An OLAP column store inverts this: each column is stored contiguously instead of each row, so that same aggregate query reads ONLY the revenue column's bytes across all rows, skipping everything else entirely — and because a single column tends to hold far fewer distinct values than a full row (a "country" column repeats a small set of values millions of times), column-wise storage also compresses dramatically better. This single storage decision is the mechanical reason you can't just run serious analytics on your production Postgres at scale — it's not a licensing or feature limitation, it's that the row-oriented layout is doing exactly the wrong amount of I/O for an aggregate query by construction.

An online retailer's checkout flow hits Postgres — OLTP — for every "place order," needing ACID guarantees and sub-100ms responses, one row (this order) at a time. This is precisely the row-store shape: small, frequent, individual-record transactions where consistency under concurrent writes matters more than anything else.

Every night, a CDC pipeline streams those same order rows into a Snowflake warehouse — OLAP — where the finance team runs a query aggregating revenue by product category by region across three years of history. That exact query, run directly against the live Postgres instance, would table-scan a huge portion of production data and risk degrading checkout for real customers — but it's precisely what the column-oriented warehouse, built to skip everything except the columns the aggregate actually needs, handles as routine.