Understanding Workload Types

Before comparing engines, describe the workload in properties that determine physical behaviour. Six properties do most of the work.

1. Read/write ratio and shape

Not just how many, but what shape. A thousand point lookups per second and one query per second scanning a billion rows are both "1001 queries per second" and have nothing else in common.

• Point access — retrieve by primary key. Cost is a few index lookups regardless of table size.
• Range access — retrieve a contiguous slice by a sort key. Cheap when the storage order matches the range; expensive when it does not.
• Scan and aggregate — read many rows to produce few. Dominated by bytes read, which is why column stores exist.
• Read-modify-write — read a value, change it, write it back. Needs a transaction or an atomic primitive; this pattern is where wide-column stores hurt most.

2. Working set versus dataset

The dataset is everything you store. The working set is what is actually touched in a typical window — often a few percent of the total.

Performance falls off a cliff when the working set stops fitting in memory, not when the dataset outgrows RAM. A 4 TB database with a 40 GB working set runs well on a 64 GB machine. The same machine will struggle with a 400 GB database if queries touch all of it uniformly.

3. Write pattern

• Append-only — new rows, no updates. The friendliest pattern there is: it compresses well, partitions cleanly by time, and never fragments.
• Update-in-place — mutable rows. Costs vary enormously by engine: MVCC engines create new row versions and need vacuum; LSM engines write tombstones and need compaction.
• Delete-heavy — the most underestimated pattern. Deletes in PostgreSQL create bloat, in Cassandra create tombstones, and in ClickHouse trigger part rewrites. If you delete a lot, design retention as a partition drop instead.

4. Cardinality and skew

Cardinality is the number of distinct values in a key. Skew is how unevenly rows distribute across those values.

Skew is what turns a well-designed distributed system into a single-node system. One tenant generating 40% of traffic means one shard doing 40% of the work, regardless of how many nodes you add. See Hot Partitions.

5. Latency objective

State it as a percentile with a threshold: "p99 under 50 ms at 2000 requests per second". Averages hide everything that matters — a p50 of 3 ms with a p99 of 4 seconds is a bad system that looks fine on a dashboard.

The objective determines architecture more than the throughput does. A p99 of 10 ms rules out cross-region consensus on the write path, whatever the vendor promises.

6. Consistency requirement

Ask what happens if a read is 500 ms stale. For a dashboard, nothing. For an inventory check before charging a customer, something expensive. Requirements usually differ per operation within one application, which is why engines with per-query consistency settings — Cassandra's consistency levels, MongoDB's read and write concerns — are useful.

Writing it down

A workload description that is good enough to choose an engine from looks like this:

Order service
  dataset          ~800 GB, growing 25 GB/month
  working set      ~60 GB (last 90 days of orders)
  reads            ~4k/s point lookups by order_id and customer_id
                   ~50/s range scans (a customer's last 50 orders)
  writes           ~600/s inserts, ~200/s status updates
  cardinality      ~9M customers; top customer is 0.4% of traffic
  latency          p99 < 40 ms for reads, p99 < 100 ms for writes
  consistency      a customer must see their own order immediately
  retention        7 years, but only 90 days queried interactively

That description already excludes several categories and points clearly at a single-node relational engine with a read replica and time-based partitioning — a conclusion no feature matrix would have produced.