Chapter 12 · Workload Modeling and Access Patterns
Measuring Before Changing the Schema
Measure before changing the schema using query plans, latency distributions, row counts, throughput, lock waits, cache behavior, and controlled experiments.
Learning outcomes
Database tuning should be an experimental discipline. Before changing indexes, denormalizing, partitioning, or rewriting schemas, capture evidence. Otherwise you risk making writes slower, adding complexity, and solving a bottleneck that was never important.
Establish a baseline before schema changes.
Use execution plans and runtime metrics together.
Measure latency distributions rather than averages only.
Run controlled before/after experiments and keep rollback paths.
Start with the symptom
Examples:
p95 API latency increasedCPU is saturatedI/O queue is highlock waits increasedreplication lag grewone report now takes 12 minutesEach symptom suggests different investigation paths.
Baseline metrics
Capture:
- query latency p50/p95/p99;
- throughput;
- rows scanned versus returned;
- buffer/cache hit behavior;
- CPU and I/O;
- lock wait time;
- deadlocks/serialization retries;
- index size and usage;
- table growth rate.
Averages hide tail latency
A query averaging 20 ms can still have a p99 of 2 seconds. User experience and queueing often depend on tail behavior.
Execution plans
Inspect whether the engine chooses:
- sequential scan;
- index scan;
- bitmap access;
- nested-loop join;
- hash join;
- merge join;
- explicit sort;
- parallel execution.
Estimated versus actual rows
A large mismatch between estimated and actual cardinality can indicate stale statistics, skew, correlated columns, or predicates the optimizer models poorly.
Rows scanned versus rows returned
If a query scans 50 million rows to return 20, there may be an indexing, partitioning, or predicate problem. If a report returns 40 million rows, a scan may be unavoidable and the architecture should reflect that.
Measure the write side too
After adding an index, measure:
- insert latency;
- update latency;
- WAL/log volume;
- replication lag;
- storage growth;
- checkpoint/background maintenance pressure.
Every read optimization should be evaluated for its write and operational cost.
Controlled experiment workflow
- Define one target query/workload.
- Capture baseline plan and metrics.
- Make one bounded change.
- Re-run under representative data volume.
- Compare read and write metrics.
- Test concurrency if relevant.
- Deploy carefully and observe production.
Representative data matters
An index that looks excellent on 10,000 uniform test rows may behave differently on 500 million skewed production rows.
Representative concurrency matters
A query can be fast alone but create severe lock or cache contention under 500 concurrent users.
Query capture
Use database-native statistics/logging to identify:
- most total time;
- most frequent;
- highest average/p95 latency;
- largest I/O consumers;
- largest temporary-sort users.
Prioritize total impact
A 1-second query run once/day contributes less total load than a 5-ms query run 100 million times/day. Prioritize by business impact and resource consumption.
Schema change candidates
Evidence may justify:
- new/changed index;
- dropping redundant index;
- partitioning;
- materialized view;
- read projection;
- query rewrite;
- different transaction boundary;
- actual data-model correction.
Do not jump to denormalization first
Before duplicating data, check:
- is the query written correctly?
- are statistics current?
- is an index missing?
- is row multiplication causing excess work?
- can a materialized projection solve the read without changing source-of-truth semantics?
Measure after deployment
A successful staging benchmark is not the end. Production data distribution and concurrency may differ. Keep dashboards and rollback criteria.
WorkshopHub experiment example
Problem:Asset history p95 = 800 msBaseline:8M WorkOrderssequential scanaverage 14 rows returnedChange:index(asset_id, opened_at DESC)After:p95 = 35 mswrite latency +3%index size = 1.2 GBNow the decision can be evaluated quantitatively.
Negative result is useful
If a new index improves nothing because the optimizer correctly prefers a scan, remove it. Experiments that disprove a hypothesis prevent long-term complexity.
Document why an index exists
For critical physical structures, record:
supports Q2 AssetHistorycreated because p95 exceeded 500 msexpected result size: <=100 rowsowner: Service Operationsreview date: quarterlyChapter 12 final checkpoint
What would you measure?
A developer proposes denormalizing Customer.name into WorkOrder because a page is “slow.” What evidence should you collect first?
Review answer
Capture the exact query, execution plan, row counts, join cardinalities, indexes, latency distribution, frequency, and resource cost. Determine whether Customer lookup is actually the bottleneck. Test appropriate indexing or query changes before introducing duplicated customer names and synchronization rules.
Chapter 12 synthesis
Workload-aware design follows a disciplined loop:
Summary and next chapter
Chapter 12 expanded physical design from indexes to the full workload. You can now model queries, commands, and reports; distinguish OLTP and analytical patterns; adapt to read/write balance; and measure before changing the schema. Chapter 13 moves into deliberate denormalization, caching, materialized summaries, source-of-truth ownership, and testing duplicated data structures.
References
- Martin Kleppmann, Designing Data-Intensive Applications.
- Markus Winand, SQL Performance Explained.
- Database vendor documentation on EXPLAIN, runtime statistics, locks, and query monitoring.
- Ralph Kimball and Margy Ross, The Data Warehouse Toolkit.