Chapter 12 · Workload Modeling and Access Patterns
Designing from Queries, Commands, and Reports
Turn application queries, business commands, and reports into explicit access patterns that guide schema shape, indexes, projections, and transaction boundaries.
Learning outcomes
A production database should be designed from both data semantics and workload semantics. The same correct relational model can perform very differently depending on which queries, commands, and reports dominate. Workload modeling makes those access patterns explicit before physical tuning begins.
Separate queries, commands, and reports as distinct workload categories.
Document filters, joins, sort order, result size, frequency, and latency targets.
Connect access patterns to indexes, transaction boundaries, and projections.
Create a workload catalogue for WorkshopHub.
What is an access pattern?
An access pattern describes how the application interacts with data. It should answer more than “we query WorkOrder.” A useful pattern specifies:
- operation name;
- tables/entities involved;
- predicates;
- join path;
- ordering;
- expected result size;
- frequency;
- latency target;
- consistency requirement.
Queries, commands, and reports
Three categories are especially useful:
| Category | Purpose | Typical characteristics |
|---|---|---|
| Query | Read operational state | Selective, low latency, small result |
| Command | Change business state | Transactional, invariant-sensitive |
| Report | Summarize/analyze | Large scans, aggregation, broader time range |
WorkshopHub query examples
Q1 GetWorkOrderByNumberQ2 ListWorkOrdersForAssetQ3 ListActiveAssignmentsForTechnicianQ4 FindPartBySKUQ5 SearchOpenWorkOrdersForCustomerWorkshopHub command examples
C1 OpenWorkOrderC2 AssignTechnicianC3 RecordPartUsageC4 CompleteAssignmentC5 CloseWorkOrderThese are not just write statements; each command has a transaction boundary and invariants from Chapter 10.
WorkshopHub report examples
R1 Monthly work orders by statusR2 Parts consumed by categoryR3 Technician utilization by weekR4 Average repair duration by asset modelR5 Revenue by customer and monthRecord predicate shape
Instead of “list work orders,” document:
WHERE asset_id = :asset AND opened_at >= :from AND opened_at < :toORDER BY opened_at DESCLIMIT 50This immediately informs composite-index design.
Result size matters
A query returning one row and a report returning five million rows have fundamentally different physical needs even if both filter on the same table.
Frequency matters
A query executed 20,000 times/minute deserves more tuning attention than a monthly administrative report—unless that report blocks critical operations for hours.
Latency target matters
Document service-level expectations:
Q1 p95 < 30 msQ2 p95 < 100 msC3 p95 < 150 msR4 completion < 60 sDifferent targets justify different design effort.
Consistency requirement matters
An operational command may require the latest committed state. A dashboard may tolerate a 30-second lag. A historical financial report may require a stable snapshot. These differences affect replication, caching, and isolation choices.
Access-pattern table
| ID | Pattern | Frequency | Rows | Target |
|---|---|---|---|---|
| Q1 | WorkOrder by number | Very high | 1 | <30 ms |
| Q2 | Asset history | High | 20–100 | <100 ms |
| C3 | Record part usage | High | few writes | <150 ms |
| R2 | Parts by category/month | Daily | large scan | <30 s |
Access patterns should influence indexes
Q2 suggests:
(asset_id, opened_at DESC)while Q1 suggests a unique index on work_order_number.
Access patterns can expose missing entities
If every query asks for “ownership at a historical date” but the schema stores only current owner on Asset, the issue is not indexing—it is missing temporal modeling.
Access patterns can reveal semantic gaps. Do not solve a modeling problem with an index.
Access patterns can expose over-normalization
If one hot operational read requires 12 joins only because one stable concept was split across many artificial 1:1 tables, review the logical design before adding indexes everywhere.
Access patterns can justify projections
A normalized source-of-truth model may remain intact while a materialized view or read model supports a hot dashboard.
Practice: document one pattern
Technician schedule
Write an access-pattern entry for “show a technician's assignments for the next seven days.” Include predicates, ordering, expected row count, consistency requirement, and likely index.
Review answer
Filter by technician_id and started_at range, sort by started_at, expect perhaps tens of rows, require near-current operational consistency, and consider (technician_id, started_at).
Summary and next lesson
Workload modeling turns vague usage into concrete queries, commands, and reports. Once predicate shape, frequency, result size, latency, and consistency are explicit, index and schema decisions become evidence-driven. The next lesson focuses on the most latency-sensitive part of many systems: OLTP hot paths.
References
- Martin Kleppmann, Designing Data-Intensive Applications.
- Markus Winand, SQL Performance Explained.
- Database vendor documentation on query planning and performance diagnostics.