Read-Heavy, Write-Heavy, and Mixed Workloads

Learning outcomes

Database design changes when reads dominate, writes dominate, or both are heavy. There is no universally optimal index count, normalization depth, caching policy, or materialization strategy. Workload balance determines which costs matter most.

Read-heavy workload

Characteristics:

• many reads per write;
• frequent repeated query patterns;
• latency-sensitive retrieval;
• relatively modest update volume.

Read-heavy design tendencies

• more composite/covering indexes may be justified;
• materialized views can pay off;
• caching can provide high hit rates;
• replicas can scale read traffic;
• denormalized read projections can be attractive.

Write-heavy workload

Characteristics:

• frequent inserts/updates/deletes;
• append-heavy events or telemetry;
• high index-maintenance cost;
• contention around hot rows or keys.

Write-heavy design tendencies

• keep index set minimal;
• favor append patterns where possible;
• avoid wide covering indexes on hot tables;
• partition large event streams carefully;
• move expensive analytics elsewhere.

Mixed workload

Most production systems are mixed: user-facing reads, transactional writes, dashboards, exports, background jobs, and integrations all compete.

One table can have conflicting needs

WorkOrder may need:

• fast insertion;
• fast lookup by number;
• fast asset history;
• fast operational status queue;
• monthly reports.

You cannot optimize every dimension independently without cost.

Read/write ratio is not enough

Also consider:

• row size;
• working-set size;
• burstiness;
• contention;
• query complexity;
• freshness requirement;
• data retention period.

Hot-key problem

A system may have enormous throughput but still bottleneck on one key, such as:

inventory row for one popular partsingle sequence allocatorone tenant summary rowone global counter

Append-heavy data

AuditEvent and StatusHistory are naturally append-oriented. This is often easier to scale than constant updates to one giant mutable summary row.

Cache-friendly reads

Reference data such as WorkOrderStatus or Currency changes rarely and can often be cached safely with explicit invalidation/versioning.

Cache-unfriendly reads

Highly dynamic inventory availability may be difficult to cache if stale reads cause incorrect decisions.

Materialization trades writes for reads

Storing a precomputed summary:

customer.open_work_order_count

makes reads cheap but every relevant WorkOrder transition must maintain the duplicate value.

CQRS-style separation

When write and read models have very different needs, one architecture is:

normalized write model        |      events        |denormalized read projection

This can simplify each side while accepting eventual consistency for the read projection.

Replication for read scaling

Read replicas can reduce pressure on the primary, but replication lag means the newest write may not be immediately visible. Route operations according to consistency needs.

WorkshopHub scenarios

Do not optimize from stereotypes

“Read-heavy means denormalize” and “write-heavy means no indexes” are oversimplifications. Constraints, business semantics, and measured bottlenecks still govern design.

Practice: design for two workloads

Summary and next lesson

Read-heavy systems can spend more on indexes and materialization; write-heavy systems need disciplined physical structures and append-friendly designs; mixed systems often benefit from workload separation. The final lesson of Chapter 12 shows how to decide what to change through measurement rather than guesswork.

References

• Martin Kleppmann, Designing Data-Intensive Applications.
• Pat Helland, writings on distributed state and data ownership.
• Database vendor documentation on replication and workload monitoring.