Chapter 01 · Data, Databases, DBMSs, and SQL
Database Workloads: OLTP, OLAP, HTAP, and Streaming
Learn to describe database workloads through reads, writes, concurrency, latency, history, freshness, and continuous event processing.
Learning outcomes
A database technology should be selected for a workload, not for its popularity. Workload describes the pattern of reads, writes, concurrency, latency, history, freshness, and failure handling that the system must sustain.
Distinguish OLTP, OLAP, HTAP, and streaming workloads by access pattern and service objective.
Explain why the same dataset may need different physical representations for operations and analytics.
Identify the metrics that matter for point transactions, scans, aggregations, and continuous event processing.
Run operational and analytical queries against the same SQLite schema and inspect a query plan.
Workload is behavior, not a product category
A system is not automatically “OLTP” because it uses PostgreSQL, or “OLAP” because it uses Spark. The classification comes from what users and applications repeatedly ask the system to do.
OLTP
Many short, concurrent transactions that read or change a small number of current records.
OLAP
Fewer but larger scans, joins, and aggregations over substantial historical data.
HTAP
Operational and analytical work with low data-copy delay, usually with isolation between resource patterns.
Streaming
Continuous processing of an unbounded sequence of events as they arrive or soon afterward.
“Create an order,” “authorize a payment,” “calculate monthly revenue,” and “alert when temperature exceeds a threshold for five minutes” reveal more architecture than the generic phrase “store a lot of data.”
OLTP: online transaction processing
OLTP systems run the operational state of a business or application. Typical requests affect one entity or a small connected set: create an account, reserve inventory, post a payment, change an address, or retrieve a recent order.
| Dimension | Typical OLTP preference |
|---|---|
| Request shape | Point lookup or small range; short insert, update, or delete |
| Concurrency | High; many independent users or services |
| Latency | Predictable milliseconds are often more important than maximum scan throughput |
| Data state | Current authoritative state with integrity constraints |
| Model | Often normalized to reduce inconsistent updates |
| Reliability | Transactions, durability, recovery, idempotency, and clear error handling |
An OLTP query should touch as little data as practical. Indexes support selective lookups; transactions keep related changes atomic; constraints reject invalid states. Long scans and large aggregations can interfere with operational traffic by consuming CPU, memory, cache, I/O, and locks.
OLAP: online analytical processing
OLAP systems answer questions across many records: revenue by region and quarter, retention by cohort, average sensor behavior before failure, or the top paths through a website. The system spends more time scanning, joining, grouping, sorting, and calculating than changing individual rows.
| Dimension | Typical OLAP preference |
|---|---|
| Request shape | Large scans, multi-table joins, grouping, windows, and complex expressions |
| Concurrency | Often lower than OLTP, but each query may consume substantial resources |
| Latency | Seconds or minutes may be acceptable; throughput and cost per query matter |
| Data state | Historical, append-heavy, integrated from multiple sources |
| Model | Dimensional, denormalized, columnar, partitioned, or pre-aggregated |
| Reliability | Reproducible pipelines, lineage, data quality, snapshot consistency, and recoverability |
Columnar storage is attractive because an analytical query may read only a few columns from billions of rows. Compression improves when similar values are stored together. Partition pruning avoids scanning time ranges or categories that cannot match.
One business fact, two physical paths
The logical facts are related, but the physical layouts and service objectives differ.
Copying operational data into an analytical system is not pointless duplication. It isolates workloads and allows each representation to optimize for a different access pattern. The challenge is freshness: every copy introduces delay, transformation logic, reconciliation, and governance.
A simple freshness measure is:
A nightly warehouse may have hours of lag; change-data capture may reduce it to seconds. “Real-time” should always be replaced by a measurable requirement.
HTAP: hybrid transactional and analytical processing
HTAP aims to support operational and analytical access with very low movement delay. Implementations vary: a single engine may maintain row and column representations; replicas may serve analytics; memory-optimized structures may isolate scans; or a lakehouse may receive continuous changes.
HTAP does not repeal resource contention. A system must still decide:
- Which copy or representation is authoritative?
- How fresh must analytical results be?
- Can a large query slow a checkout or payment?
- How are schema changes applied across representations?
- What consistency level does the analytical reader observe?
“HTAP” is an architectural capability, not a guarantee that one cluster can run every workload without isolation, testing, capacity planning, or cost control.
Streaming: processing unbounded event sequences
A stream is conceptually unbounded: events continue to arrive. Instead of waiting for a completed dataset, a streaming job updates results incrementally, routes events, detects patterns, or triggers actions.
| Concept | Meaning |
|---|---|
| Event time | When the event occurred in the source domain |
| Processing time | When the processing system handled the event |
| Window | A finite grouping such as five minutes or one hour over an unbounded stream |
| Watermark | A policy for how late events are expected and when a window may be finalized |
| State | Remembered information needed for joins, counts, sessions, or pattern detection |
| Delivery semantics | How duplicates, loss, retries, and side effects are controlled |
Event streaming platforms such as Kafka durably publish and subscribe to events. Processing engines such as Flink or Spark Structured Streaming transform streams, maintain state, and emit results. A streaming system often writes to an operational database, analytical table, search index, or object store; it does not make those serving systems unnecessary.
Rate is one basic sizing dimension:
Compare the four workload families
| Question | OLTP | OLAP | HTAP | Streaming |
|---|---|---|---|---|
| Dataset boundary | Current records | Finite snapshot/history | Current plus near-current analysis | Unbounded sequence |
| Typical unit | Transaction | Query/job | Both | Event/window |
| Primary pressure | Concurrency and latency | Scan throughput and cost | Isolation and freshness | Rate, lateness, state, recovery |
| Example | Place order | Revenue by cohort | Live inventory dashboard | Fraud alert pipeline |
Lab: run two workloads against one table
The example deliberately uses SQLite so you can observe the shape of the requests without installing a distributed system.
CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, ordered_at TEXT NOT NULL, status TEXT NOT NULL, total_amount NUMERIC NOT NULL CHECK (total_amount >= 0)); CREATE INDEX idx_orders_customer_time ON orders (customer_id, ordered_at); -- OLTP-shaped request: one customer's recent orders.SELECT order_id, ordered_at, status, total_amountFROM ordersWHERE customer_id = 42ORDER BY ordered_at DESCLIMIT 10; -- OLAP-shaped request: aggregate a large period.SELECT substr(ordered_at, 1, 7) AS month, status, COUNT(*) AS order_count, SUM(total_amount) AS revenueFROM ordersGROUP BY month, statusORDER BY month, status; The first query is selective. Its ideal access path uses the composite index to find one customer and return recent rows in order. The second query must inspect a large portion of the table because it summarizes all matching records by month and status.
EXPLAIN QUERY PLANSELECT order_id, ordered_at, status, total_amountFROM ordersWHERE customer_id = 42ORDER BY ordered_at DESCLIMIT 10; Read the plan as evidence, not as a score. The point is to connect query shape to physical work. Later lessons cover indexes, statistics, cardinality estimation, and detailed plans.
Populate ten rows, then one hundred thousand rows. Compare point lookup time and aggregation time. Add an index on ordered_at, rerun the monthly aggregation, and explain why an index may or may not help a query that reads most rows.
Workload discovery checklist
- Operations: What exact reads and writes occur?
- Volume: How many records and bytes exist now and after three years?
- Velocity: What are average and peak arrival rates?
- Concurrency: How many requests or jobs overlap?
- Latency: Which percentile must meet which target?
- Freshness: How stale may a result be?
- Consistency: Which anomalies are unacceptable?
- Retention: What must be kept, deleted, or archived?
- Failure: What happens on retry, duplicate delivery, partial execution, or node loss?
- Cost: Which resources and operational skills are available?
Common mistakes
Running unrestricted analytics on the production primary
A correct query can still harm an operational service. Use limits, timeouts, replicas, workload management, or a separate analytical representation.
Calling every near-immediate result “real-time”
Define an end-to-end freshness objective and measure source time, ingestion time, processing time, and serving time.
Using streaming for a naturally finite daily job
Streaming adds state, checkpointing, ordering, and operational complexity. Batch is often simpler when minutes or hours of delay are acceptable.
Choosing technology before documenting queries
Architecture should follow access patterns, guarantees, and constraints. Product-first design frequently creates expensive mismatches.
Checkpoint and practice
Concept check
- Why can an analytical query be harmful to an OLTP service even when it only reads data?
- What is the difference between event time and processing time?
- Why might an organization copy order data into a warehouse?
- What must an HTAP design isolate?
Review the answers
Large reads consume shared CPU, memory, cache, I/O, and sometimes locking resources. Event time belongs to the source event; processing time belongs to the processing system. A warehouse isolates scans and supports historical layouts. HTAP must isolate transactional latency from analytical resource consumption while defining freshness and consistency.
Architecture exercise
For an online shop, classify these requirements: place an order, show current order status, calculate yesterday’s revenue, update a dashboard within 30 seconds, and alert on five failed payments for one account within ten minutes. State the workload family and one measurable service objective for each.
Summary and next lesson
OLTP optimizes short concurrent transactions; OLAP optimizes scans and aggregations; HTAP reduces the delay between operations and analysis while managing contention; streaming continuously processes events and state. The next lesson establishes the formal vocabulary behind relational systems: relations, tuples, attributes, domains, keys, and relational operations.