OLTP vs OLAP

The pattern: two workload shapes that need opposite storage organizations. OLTP (transactional): many small concurrent reads/writes by primary key; row-oriented; strict ACID. OLAP (analytical): few large scans, big aggregations across columns; column-oriented; eventual consistency usually fine; schema-on-read often acceptable. Mixing them on one engine usually means one workload suffers.

The trade-off: row vs. column — the most fundamental decision in data infrastructure. Row stores keep all of a record’s fields together (good for “give me this user”). Column stores group all values of a field together (good for “average revenue across 100M users”). Hybrid stores (HTAP — Hybrid Transactional/Analytical Processing — like SingleStore, TiDB) try both, with compromises.

First encounter in Year 1 Phase 3 (Postgres as OLTP). Reaches DEEP in Year 3 Phase 15 when the same dataset runs on Postgres + DuckDB + Iceberg-via-Trino inside basecamp and the row/column gap shows up concretely.

Related patterns

• lsm-vs-btree — OLTP picks B-tree; LSM serves write-heavy OLTP and some OLAP ingest paths.
• schema-on-read-vs-write — the discipline OLAP can usually relax and OLTP cannot.
• materialized-views — how OLAP hides aggregate cost from interactive consumers.
• batch-processing — OLAP’s natural execution model.