Part 0 · 1 chapters · ~8 min

OLTP versus OLAP

Transactional and analytical workloads compared, row and column storage, why reports on the primary hurt customers, read replicas and their limits, HTAP systems, and the shape of a typical data platform from source to dashboard.

Two workloads, two shapes

code

-- OLTP: one row by key, thousands per second
SELECT balance_kobo FROM accounts WHERE id = $1;
UPDATE accounts SET balance_kobo = balance_kobo - $2 WHERE id = $1 AND balance_kobo >= $2;

-- OLAP: millions of rows, three columns, a few times an hour
SELECT date_trunc('day', created_at) AS day, currency, sum(amount_kobo) / 100 AS volume
FROM transfers WHERE status = 'completed' AND created_at >= now() - interval '90 days'
GROUP BY 1, 2 ORDER BY 1;

code

a typical data platform
  sources (Postgres, Kafka events, SaaS APIs)
    → ingestion (CDC with Debezium, Fivetran or Airbyte for SaaS)
    → storage (warehouse: BigQuery, Snowflake, Redshift, ClickHouse; or a lake: S3 + Iceberg)
    → transformation (dbt models, tested)
    → consumption (dashboards: Metabase, Superset, Looker; notebooks; ML features; reverse ETL)

HTAP systems (TiDB, SingleStore, AlloyDB's columnar engine) try to serve both workloads in one product; they help, but most companies still separate them for isolation.

OLTP AND OLAP

two workloads with opposite shapes

swipe the figure sideways, or tap expand for full screen

1/4

OLTP

Online transaction processing: many small, concurrent reads and writes by key, with strict consistency. Row stores keep each row together so one lookup reads one page.

many small keyed operationsrow stores