Chapter 12 · Workload Modeling and Access Patterns
OLTP Access Patterns and Hot Paths
Design for OLTP workloads by identifying hot paths, short transactions, contention points, point lookups, selective ranges, and latency-sensitive operational queries.
Learning outcomes
Online Transaction Processing (OLTP) workloads consist of many relatively small reads and writes that support day-to-day operations. Their design priorities are low latency, high concurrency, predictable transactions, and reliable integrity under contention.
Identify OLTP hot paths and their contention boundaries.
Design selective indexes and short transactions for operational workflows.
Recognize N+1 queries, over-fetching, and chatty database access.
Protect latency-sensitive commands without sacrificing integrity.
Typical OLTP characteristics
- many concurrent users;
- small result sets;
- point lookups and selective ranges;
- frequent inserts/updates;
- short transactions;
- strict integrity requirements;
- latency measured in milliseconds.
Hot path definition
A hot path is an operation that dominates user experience or system throughput. Examples:
open work orderload work-order pageassign technicianrecord part usageclose work orderHot path: load work-order page
The page may need:
- WorkOrder core row;
- Asset and Customer summary;
- active Assignments;
- recent PartUsage;
- Status history.
That does not automatically mean one giant join is best.
Avoid accidental row multiplication
If WorkOrder has 4 assignments and 8 part-usage rows, joining both child collections directly creates 32 combinations. Fetching independent collections separately may be clearer and faster.
N+1 query problem
Bad application pattern:
SELECT 50 work orders;for each row: SELECT asset ... SELECT customer ... SELECT status ...This turns one page load into hundreds of round trips.
Fix N+1 deliberately
Options include:
- appropriate joins;
- batch queries using IN;
- ORM eager-loading;
- read projections for hot views.
Short transactions reduce contention
An OLTP command should generally:
- begin;
- read only required rows;
- validate invariants;
- write;
- commit promptly.
Avoid holding locks while rendering UI, calling external APIs, or waiting for user input.
Point lookups need key access
WHERE work_order_number = ?should normally resolve through a unique index rather than a scan.
Selective history ranges
WHERE asset_id = ?ORDER BY opened_at DESCLIMIT 50is a classic OLTP history pattern. A composite index can provide both filtering and ordering.
Hot writes require restraint
A heavily written table should not carry dozens of speculative indexes. Every index increases command latency and write amplification.
Optimize the small number of critical read/write paths; do not turn the primary operational schema into a reporting index warehouse.
Contention points
Common contention boundaries include:
- inventory row for a scarce Part;
- WorkOrder parent row during close/assignment operations;
- sequential counters;
- one “current” row per entity;
- tenant-wide summary rows.
Hot counters can serialize workload
Updating one row for every event:
UPDATE global_counterSET value = value + 1;can create a bottleneck. Consider whether the counter must be transactionally exact at all times.
Pagination matters
Offset pagination:
OFFSET 100000 LIMIT 50may become expensive. Keyset/cursor pagination based on indexed order can scale better:
WHERE opened_at < :last_seenORDER BY opened_at DESCLIMIT 50Keep rows narrow on hot tables
Large rarely used blobs or JSON payloads can reduce cache density. Splitting cold, optional, or bulky data into a secondary table can be justified when lifecycle and access patterns support it.
Prepared statements and plan stability
Repeated OLTP queries often benefit from parameterization and prepared execution, while still requiring awareness of skew and parameter-sensitive plans.
OLTP integrity still comes first
Do not remove unique constraints or foreign keys merely to make writes faster unless you replace the guarantee with an equally robust design. Performance without correctness is not a successful OLTP system.
WorkshopHub hot-path table
| Operation | Physical concern |
|---|---|
| Find WorkOrder | Unique lookup index |
| Assign Technician | Short transaction + active-assignment constraint |
| Record PartUsage | Atomic inventory update + minimal index overhead |
| Asset history | (asset_id, opened_at) |
| Close WorkOrder | Lock/isolation around invariant boundary |
Practice: spot the OLTP anti-patterns
Page request
A page loads 100 WorkOrders, then issues 100 Asset queries, 100 Customer queries, and 100 Status queries. The transaction remains open while an external warranty API is called. Name two problems.
Review answer
The request has an N+1/chattiness problem and an unnecessarily long transaction that holds database resources while waiting on an external service. Batch/join the reads and move external I/O outside the critical database transaction.
Summary and next lesson
OLTP design prioritizes short, selective, concurrency-safe operations. Index hot paths, avoid N+1 access, keep transactions short, minimize write amplification, and use appropriate pagination and contention control. The next lesson shifts to the opposite workload shape: analytical queries that scan and aggregate large portions of data.
References
- Martin Kleppmann, Designing Data-Intensive Applications.
- Markus Winand, SQL Performance Explained.
- PostgreSQL and other DBMS documentation on query planning and locking.