Topic 0.2
Workloads: OLTP, OLAP, HTAP and Their Shapes
In one line
OLTP runs many small, latency-sensitive transactions on a few rows each; OLAP scans and aggregates millions of rows for analysis; HTAP tries to do both on one system. Classifying a workload as read-heavy or write-heavy, latency- or throughput-oriented, interactive or batch drives every later decision.
Think of it like this
A supermarket. The checkout tills are OLTP (thousands of quick, exact transactions a minute); the head-office report "sales by region by month" is OLAP (one huge scan over everything); a manager's live dashboard showing today's takings is HTAP-ish.
Key ideas
- 01
OLTP: point reads and writes by key, short transactions, high concurrency, strict correctness, row-oriented storage (PostgreSQL, MySQL, DynamoDB). Latency target: single-digit milliseconds.
- 02
OLAP: large scans, aggregations and joins over history, few concurrent users, columnar storage and compression (BigQuery, Snowflake, Redshift, ClickHouse). Latency target: seconds are fine; throughput of rows scanned matters.
- 03
HTAP (TiDB, SingleStore, AlloyDB columnar engine) keeps a row store and a column store in sync so analytics run on fresh data without hurting transactions. Often the simpler answer is OLTP → CDC → warehouse with minutes of lag.
- 04
Read-heavy (catalogues, profiles): replicas, caches, denormalised read models. Write-heavy (events, metrics, logs): append-friendly engines (LSM), partitioning by time, batching. Latency-sensitive paths need predictable p99; throughput-oriented batch jobs care about total rows per hour.
- 05
Always quantify: reads/sec, writes/sec, read:write ratio, row size, data growth per day, p99 latency target. These numbers decide the design far more than technology preference.
Code & diagrams
-- OLTP: touch a handful of rows by key, in a short transaction
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 42;
UPDATE accounts SET balance = balance + 500 WHERE id = 77;
COMMIT;
-- OLAP: scan and aggregate history (belongs in a warehouse at scale)
SELECT region, date_trunc('month', created_at) AS month, sum(amount)
FROM orders
WHERE created_at >= now() - interval '2 years'
GROUP BY 1, 2
ORDER BY 1, 2;Interview problem
The problem
Classify and place four workloads
An e-commerce company has: checkout (5K orders/sec peak), a product catalogue (200K reads/sec, 50 writes/sec), clickstream events (1M events/sec), and finance's monthly revenue reports over 5 years. Classify each and suggest where it lives.
When it breaks
Running analytics on the primary OLTP database
What you see
Long scans compete for I/O and buffer cache; checkout p99 spikes; long-running snapshots stop VACUUM from cleaning dead rows, causing bloat.
Fix & prevent
Move reports to a replica with hot_standby_feedback considered carefully, or better to a warehouse via CDC; set statement_timeout for ad-hoc users.
Explain it without notes
What distinguishes OLTP from OLAP storage layouts, and why?
Practice
For an app you know, write down reads/sec, writes/sec, row size and growth per day for its three busiest tables.
Trade-offs
- ↔
One database for everything is simple until workloads interfere; splitting by workload adds pipelines and eventual consistency.
Done when you can
I can classify a workload and name the storage shape that suits it.