Part 3 of 8 · 2 min read
Start from the access pattern
Writing down every read and write, with rates, before choosing anything.
Think of designing a kitchen. A café that makes 500 coffees an hour needs the machine within arm's reach. A restaurant that cooks complex dishes needs long counters. The layout follows what is done most often.
A database choice follows the same rule.
Access pattern
An access pattern is a description of one way the application reads or writes data: what it asks for, by which field, how often and how fast.
Example: "Fetch a user's last 20 messages, newest first, 8,000 times a second, in under 20 milliseconds".
A short table for each feature makes the choice visible.
| Access pattern | Type | Rate | Needs |
|---|---|---|---|
| Get a user by id | Read | 5,000/s | One key lookup |
| Add an order with several lines | Write | 300/s | All lines saved together or none |
| List a user's orders, newest first | Read | 800/s | Filter by user, sort by time |
| Orders with a total over 5,000 in a city | Read | 5/s | Flexible filter, a report |
| Count page views per product | Write | 20,000/s | Fast increments |
Reading the table:
- Rows 1 and 5 are plain key operations. A key-value or wide-column store does them well.
- Row 2 needs several rows saved together, a multi-row rule (section 3).
- Row 3 needs an index on user and time. Almost any store can do it.
- Row 4 is a flexible query. It is natural in a relational store and painful in a store designed for fixed key lookups.
A worked example. A shop expects 300 order writes a second at the peak and 800 order list reads, 5,000 user reads and 20,000 view counts, all per second.
- Everything except the view counts is 6,100 operations per second, a modest load for one well-sized relational server (assumed 10,000 simple operations per second, made up for the example).
- The view counts alone are 20,000 per second, which is more than the assumed capacity of the node. They are simple increments by key. A key-value or wide-column store, or a counter kept in memory and flushed in batches, is a better home.
- A sensible split: orders and users in the relational database; view counts in a key-value store.
This is the shape of most real answers. The data is divided by access pattern, and each part goes to the store that does it cheaply.
Read it as
List the reads and writes with rates first. A store is good if it makes the common ones cheap, and bad if it makes any important one impossible.
Check yourself:
- What are the three parts of an access pattern worth writing down besides what is asked?
- In the worked example, why do view counts leave the relational node?