Data building blocks
Access patterns
An access pattern is one concrete query your system runs, with its lookup shape, rate and latency target. Write the list first, and let the choice of store follow from it.
Most database mistakes start with picking a store before knowing the queries. An access pattern is one concrete query: what it looks up by, what it returns, how often it runs, and how fast it must answer. Write the list down before you draw any boxes.
Concept
Before you start. p99 latency is the time that 99 out of 100 requests finish within. An OLTP workload is many small reads and writes that need transactions. An OLAP workload scans and aggregates many rows, and runs less often.
The big idea
Think of a shopping list written before you walk into the store. You do not wander the aisles hoping to find what you need. You plan the trip from the list.
The list breaks down when the questions change. That is why the list needs a review each quarter, and why new questions can still arrive later.
Walking through it
Here is the access pattern list for an e-commerce order system.
| # | Access pattern | Rate | Latency target |
|---|---|---|---|
| 1 | Get an order by ID, with its line items | 2,000 per second | p99 under 20 ms |
| 2 | List a customer's orders, newest first, paginated | 500 per second | p99 under 50 ms |
| 3 | Create an order and decrement stock atomically | 200 per second | p99 under 100 ms |
| 4 | List orders pending shipment for one warehouse | 5 per second | p99 under 500 ms |
| 5 | Revenue by category per day, last 90 days | 10 per hour | seconds is fine |
| 6 | Search products by free text | 1,000 per second | p99 under 100 ms |
Patterns 1 to 4 are OLTP. They are small, frequent and indexed, and they need transactions. Pattern 5 is OLAP. It is rare but heavy, and it scans millions of rows. Pattern 6 needs relevance ranking, so it goes to a search index.
For each pattern, record these:
- Lookup shape: point lookup, range scan, join, full-text, aggregation, graph traversal or nearest neighbor.
- Read/write ratio and absolute rates.
- Consistency needs: must a read see a write that just happened?
- Data size: 50 GB fits in memory on one machine. 50 TB does not.
Common mistakes
- Picking a store first. You then fit the queries to the store, not the other way round.
- Writing "list orders" with no rate or latency. Without those numbers, you cannot choose an index or a store.
- Skipping consistency needs. A read that must see its own write needs different handling from a dashboard.
Check yourself
- Which pattern in the table is OLAP, and why? Pattern 5. It aggregates many rows over 90 days, so it is heavy, but it runs only 10 times an hour.
- Why record the latency target with each pattern? It decides whether a scan is acceptable, or whether you need an index or a separate store.
Key takeaways
- An access pattern is a concrete query with a rate, a latency target and a consistency need.
- Write the list before choosing a database.
- Most patterns are OLTP, and a few are OLAP. Keep them apart in your design.
- Review the list when the product's questions change.
Further learning
Next, see how indexes make these patterns fast, and what they cost, in indexing strategy.
For the wider context, see databases and data modeling.
Related
Databases and data modeling
Choose between relational, document, wide-column, key-value, graph and analytical stores by listing your access patterns first, then model the data to fit them.
Indexing strategy
An index is a sorted copy of some columns that lets the database jump to matching rows. Column order, covering and partial indexes decide how useful it is, and every index slows every write.
Query-first modeling
Key-value and wide-column stores have no joins, so you name the partition key for every query before you store anything. DynamoDB and Cassandra both work this way.