18.1 Using Workloads and Invariants to Select Data Systems

Data-system selection translates business commitments into verifiable storage constraints. A product comparison without workloads, failure models, and team constraints has little value, however many logos it contains.

First Layer: Which State Is Authoritative?

First, identify the system of record: which system's state can resolve disputes, reconstruct derived views, and withstand audit.

Order database       -> Authority of transactional facts
Outbox / Event log   -> Record of committed changes propagation
Search index         -> Reconstructable query projection
Redis cache          -> Optional acceleration layer that may be lost
Analytics warehouse -> Batch or stream-derived analytical copies

If all systems are called "the master database," there is no arbiter when conflicts arise. While caches, search indexes, and analytics systems can be critical, they should generally not override transactional facts without a clear and agreed-upon protocol.

Second Layer: Express Invariants and Transaction Boundaries

Turn the abstract notion of "consistency is important" into concrete, actionable statements:

• A single coupon can be successfully redeemed at most once;
• Account balances must never go below zero due to concurrent deductions;
• Creating an order and its pending OrderCreated outbox record must be atomic; external publication can retry after commit;
• Search results may lag behind the authoritative database by up to 30 seconds;
• Recommendation statistics can be recomputed, allowing duplicate events but not permanent data loss.

Label each invariant with respect to its data scope: single row, single partition, multiple rows, or cross-system. Design the data model so that the most critical invariants are aligned with native atomic boundaries provided by the database.

Third Layer: Describe Workload Distribution, Not Fabricated "Typical Numbers"

Collect at least the following dimensions:

Dimension	What to Measure
Read-write ratio	Average, peak, and sustained burst durations
Access scope	Point lookups, range scans, full-table scans, multi-hop graph traversals
Data distribution	Key cardinality, hotspots, tenant skew, temporal locality
Transactions	Number of rows per read/write operation, cross-partition ratio, conflict rate
Latency	p50, p95, p99 values and timeout budgets, never just the average
Growth	Amplification of raw data, indexes, versions, logs, and replicas
Recovery	RPO, RTO, full restore time, and replay speed

Statements like "OLTP processes tens of rows per operation" or "OLAP must scan billions of rows" can serve as intuition, but they cannot form valid capacity conclusions. Input for selection should come from production traces, representative samples, or clearly stated assumptions.

OLTP, OLAP, and Stream Processing Serve Different Execution Goals

OLTP

Short transactions, selective point queries, and limited updates prioritize constraints, isolation, lock contention, log commits, and index maintenance. Row-oriented storage and B+ trees are well-suited for these access patterns, though not the only viable implementations.

OLAP

Large-scale scans, projections, filters, joins, and aggregations emphasize column pruning, partition pruning, compression, vectorization, parallelism, and spill behavior. Queries may read from authoritative database snapshots, analytical replicas, tables in data lakes, or local files.

Stream Processing

Continuously consumes unbounded events, updating results based on event time, watermarks, and state. Key challenges include out-of-order events, late data, checkpoints, state recovery, and sink idempotency. It should not be simply categorized as "real-time OLAP."

HTAP

HTAP aims to serve both transactional and analytical workloads within a short time frame. Implementation approaches may include dual layouts in the same engine, columnar replicas, log-driven projections, or resource isolation. It does not imply that all queries on the same physical data or shared resource pool operate independently or without interference.

Use Capability Matrices, Not Brand Matrices

Requirement	Preferred Capability	Common Supplements
Strong constraints and cross-entity transactions	Relational/transactional databases	Outbox, read replicas
Low-latency primary keys and simple atomic operations	KV / in-memory data structures	Authority stores, persistence and hot-key governance
Arbitrary full-text search and relevance scoring	Inverted index systems	CDC synchronization, rebuild workflows
Large-scale analytical scanning	Columnar analytics systems / data lakes and warehouses	ETL/ELT, data quality
Multi-hop path queries	Graph databases or graph processing engines	Authority relationship projections
Replayable event propagation	Persistent logs	Schema registry, idempotent sinks

Choose capabilities before evaluating products. Product evaluation must also consider versioning, licensing, hosting models, regional availability, and team experience.

A Minimum Viable Selection Process

1. Define SLOs, invariants, RPO/RTO, and compliance boundaries;
2. Map read/write paths and authoritative data flows;
3. Identify the system’s provable bottlenecks, starting by ruling out query, indexing, or capacity configuration issues;
4. Select two to three candidates and construct representative data and hotspots;
5. Perform stress testing under the target reliability configuration, injecting slow nodes, restarts, and network failures;
6. Test backup recovery, scaling, upgrades, and data export, rather than steady-state TPS alone;
7. Document an ADR (Architecture Decision Record) to explain the rationale for selection, which assumptions hold, and when re-evaluation is needed.

Consider a hypothetical evaluation requiring one-time coupon redemption, p99 below 80 ms, and RPO=0 for acknowledged writes. Candidate A averages 5 ms but can lose an acknowledged write; B averages 12 ms and passes the required durability and concurrency checks. A fails a hard requirement before performance scoring: speed cannot average away data loss. B still needs p99, timeout, and recovery tests. These are assumed evaluation criteria, not product benchmark results.

Count failures and retries in the request population and hold data size, hotspots, concurrency, and acknowledgment settings constant. Measuring only successful cache hits produces an attractive curve with little capacity-planning value.

Common Signals of Poor Selection Decisions

• Introducing a new database for a single report without assigning ownership or responsibility for data synchronization;
• Testing benchmarks only on empty tables or uniformly random keys, while production environments face hotspots from large tenants;
• Claiming exactly-once semantics without implementing idempotent keys to handle external side effects;
• Only accounting for machine costs, ignoring backup expenses, cross-zone traffic, on-call responsibilities, and migration overhead;
• Lacking an exit strategy, leaving data solely accessible via slow, private APIs for gradual export;
• Choosing a system designed for future scalability without budgeting for the complexity of current-scale workloads.

Section Checkpoint

• Can identify authoritative state and all reconstructable derived states.
• Can express business correctness as clear invariants and atomic boundaries.
• Can evaluate a system using distribution, tail latency, and fault recovery, rather than brand reputation.

The next section turns selection into an evolvable architecture: how caching, search, and analytics replicas are built around the authoritative database, and how they safely migrate and retire.