Chapter 12 · Workload Modeling and Access Patterns
Analytical Access Patterns and Large Scans
Understand analytical access patterns built around large scans, aggregation, dimensional filters, historical ranges, and why OLTP schemas often need separate analytical structures.
Learning outcomes
Analytical workloads answer questions across large data sets: trends, aggregates, historical comparisons, cohorts, utilization, and revenue. They often favor sequential scans, columnar execution, partitions, materialized summaries, and star-like structures rather than the point-lookup indexes preferred by OLTP.
Recognize analytical scan and aggregation patterns.
Understand why indexes may not help when most rows are read.
Separate operational source-of-truth design from analytical projections.
Model historical dimensions and fact grain carefully.
Typical analytical questions
How many repairs were completed each month?Which part categories drive the highest cost?What is median repair duration by asset model?Which technicians have the highest utilization?How has customer revenue changed year over year?Analytical query shape
Example:
SELECT date_trunc('month', opened_at) AS month, status_code, COUNT(*) AS work_ordersFROM work_orderWHERE opened_at >= :year_startGROUP BY 1, 2ORDER BY 1, 2;This may read millions of rows. A full or partition-pruned scan can be more efficient than millions of random index lookups.
Large scans are not automatically bad
For analytics, scanning a compressed columnar structure sequentially can be exactly the correct plan.
OLTP schema versus analytical schema
WorkshopHub's OLTP model is normalized around entities and transactions. An analytical model may organize data around facts and dimensions:
FactWorkOrderFactPartUsageDimCustomerDimAssetDimTechnicianDimPartDimDateFact table grain
Before building a fact table, state its grain precisely:
one row per WorkOrderone row per PartUsage eventone row per Technician assignment intervalMixing grains produces double counting.
Dimensions provide descriptive context
Dimensions commonly contain:
- customer segment;
- asset model/manufacturer;
- part category;
- technician team;
- calendar attributes.
Historical dimensions matter
If a Customer changes segment, should historical revenue move to the new segment or remain attributed to the old segment? Analytical modeling must define temporal semantics explicitly.
Slowly changing dimensions preview
A common warehouse pattern versions dimension rows so facts can retain the historical classification in effect when the event occurred. Chapter 14 covers temporal modeling more generally.
Partition pruning
Large historical tables are often partitioned by time. A query:
WHERE event_date >= '2026-01-01' AND event_date < '2026-02-01'can scan only relevant partitions if the partition key and predicate align.
Columnar storage
Analytical systems often read a small subset of columns from a huge number of rows. Columnar storage can reduce I/O by reading only needed columns and compressing similar values efficiently.
Materialized aggregates
If a dashboard repeatedly computes the same expensive monthly totals, a materialized summary can shift work from request time to refresh time:
MonthlyRepairSummary( month, status_code, work_order_count, avg_duration)Freshness versus cost
Analytical data can often lag:
real-time5 minuteshourlydailyThe allowed freshness delay strongly influences architecture.
Do not overload the primary OLTP database casually
Long scans can:
- evict hot pages from cache;
- consume CPU and I/O;
- increase replication lag;
- compete with user-facing queries.
Read replicas, warehouses, or dedicated analytical systems may isolate the workload.
Analytical joins can be different
Normalized OLTP joins preserve source semantics. Analytical models may intentionally duplicate descriptive attributes to reduce join complexity and improve scan efficiency.
Keep a well-defined source of truth, then build analytical projections for scan-heavy workloads rather than distorting the operational model prematurely.
WorkshopHub analytical examples
| Question | Likely grain |
|---|---|
| Repair count by month | WorkOrder fact |
| Parts cost by category | PartUsage fact |
| Technician utilization | Assignment interval fact |
| Revenue trend | Invoice/payment fact if modeled |
Practice: choose the structure
Monthly dashboard
A dashboard runs every minute and recomputes 12 months of WorkOrder counts and average duration across 200 million rows. What alternatives should you consider?
Review answer
Partition pruning can reduce scan scope, but a materialized aggregate or analytical replica/warehouse is likely more appropriate if the same aggregation repeats constantly. Decide refresh frequency from freshness requirements.
Summary and next lesson
Analytical workloads favor scans, aggregates, historical dimensions, partitions, columnar structures, and precomputed summaries. They should often be served from dedicated projections rather than forcing the OLTP schema to serve incompatible goals. The next lesson compares read-heavy, write-heavy, and mixed workloads directly.
References
- Ralph Kimball and Margy Ross, The Data Warehouse Toolkit.
- Martin Kleppmann, Designing Data-Intensive Applications.
- Database vendor documentation on partitioning and analytical execution.