Access pattern decides the store

Lesson 1 of 5 in Choosing a Data Store.

There is a question that appears on this exam more often than any other, and it always looks like a shopping decision: four AWS stores, which one? Read it that way and you will reason about which store is best, compare features, and pick the one you know most about. That is the wrong operation.

The question is an elimination. A well-written stem contains one or two constraints that make three of the four options mechanically impossible, and the right answer is simply the one left standing. Your job is to find the constraint, not to rank the products. That changes what you look for: you stop reading the option list first and start reading the stem for the sentence that forecloses something — "the checkout page must render in under 50 ms", "analysts write their own SQL", "ten years of history, queried twice a year", "results must be ranked by relevance".

Behind every one of those sentences is the same four-part description of an access pattern, and once you can write those four lines about a workload the store chooses itself.

The predicate — what the request knows when it arrives. A primary key? A date range plus a status? A free-text phrase? The selectivity — one row out of a billion, or a hundred million rows collapsed into twelve numbers. The latency budget — milliseconds for a page a human is waiting on, seconds for a dashboard, minutes for a report nobody is watching. The write shape — one row at a time by key, or a million rows appended at once.

Work through the four axes one at a time, because each has a mechanical reason attached, and the reason is what you are being examined on.

Latency is set by what the store must do before it can answer. DynamoDB hashes a key and reads one partition — single-digit milliseconds, independent of table size. Aurora parses SQL, checks a buffer pool and probably reads one index page — low single-digit milliseconds. Amazon Redshift plans on a leader node and fans work out to every slice — hundreds of milliseconds at best, which is fine for a report and disastrous for a page render. Athena on a lake has to list prefixes, fetch partition metadata, open file footers and then start reading — so there is a floor of hundreds of milliseconds to seconds that no amount of tuning removes. A latency budget under about 50 ms eliminates every analytical store in one line.

Query shape is set by the physical layout. Row storage puts a whole row on one page, so fetching one row by key is one read and summing one column of a billion rows means touching all of them. Columnar storage puts each column together, so the sum reads one-fortieth of the bytes and fetching a single complete row means reassembling it from forty places. These are not tunings; they are opposite layouts, which is why no single store is excellent at both and why the standard architecture keeps two copies.

Cardinality decides whether a key is viable. A key-value store distributes by hashing the partition key, so a key with four possible values puts everything on four partitions and no amount of provisioned capacity helps. The same arithmetic applies to a Redshift distribution key and to a Spark join key: low cardinality means skew means one worker doing all the work.

Write shape decides the ingestion side. A million rows appended per minute wants an append-friendly store and large files; one row at a time by key wants a key-addressed store. And "I need both" is again the two-store answer — land writes where they are cheap, derive the analytical copy on a schedule. How that derivation is actually built — change streams, delivery streams, and the orchestration around them — is the subject of ingestion patterns.

Key terms: Access pattern, Selectivity, OLAP (online analytical processing), Data skew

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.