Topic 3.5
Access-Pattern-First Design
In one line
Before creating physical tables and indexes, list every important read and write with its frequency, filters, sort order, pagination, expected latency, and consistency requirement. That inventory drives table shape, indexes, denormalization and the choice of store, and it's the backbone of NoSQL modeling where you can't join later.
Think of it like this
Designing a kitchen. You place the fridge, stove and sink by how you actually cook (the workflow), not by how the appliances look in the catalogue.
Key ideas
- 01
For each query write: name, type (read/write), frequency (per second at peak), filters (equality vs range), sort, page size and pagination style, joins, aggregation, latency target (p99), consistency (must see latest write?).
- 02
Hot queries (top 10 by frequency × cost) deserve a dedicated index or read model; rare ad-hoc queries can scan or go to a replica or warehouse.
- 03
Equality filters, then sort, then range: the inventory directly gives composite index column order (Phase 7.2).
- 04
Relational databases let the logical model come first and tune physically later; DynamoDB and Cassandra require the access-pattern list first because tables are designed per query (Phase 11).
Code & diagrams
| # | Query | Type | Peak/s | Filters | Sort | Latency | Consistency |
|---|-------------------------------|-------|--------|--------------------------|-----------------|---------|-------------|
| 1 | Get order by id | read | 3,000 | id = | - | 5 ms | latest |
| 2 | My orders (paginated) | read | 1,200 | customer_id = | created_at desc | 20 ms | own writes |
| 3 | Place order | write | 400 | - | - | 50 ms | atomic |
| 4 | Merchant: open orders today | read | 200 | merchant_id =, status = | created_at | 50 ms | ~5 s stale |
| 5 | Finance: revenue by month | read | 1/day | created_at range | - | minutes | stale ok |
-> #2: index (customer_id, created_at DESC), keyset pagination
-> #4: partial index (merchant_id, created_at) WHERE status = 'open'
-> #5: warehouse / summary table, not the OLTP primaryInterview problem
The problem
Derive indexes from an access-pattern table
For a support ticket system: (1) get ticket by id, (2) agent's open tickets sorted by priority then age, (3) customer's tickets newest first, (4) search tickets by text, (5) weekly SLA report. Propose the physical design.
When it breaks
Indexes added by guesswork
What you see
Twenty indexes, half never used, each slowing every insert; the one query that matters still does a sort because column order is wrong.
Fix & prevent
Tie each index to a named access pattern; check pg_stat_user_indexes.idx_scan and drop unused ones (Phase 7.3).
Explain it without notes
Why is the access-pattern list mandatory for DynamoDB but only recommended for PostgreSQL?
Practice
Write the access-pattern table for a URL shortener (create, redirect, stats).
Trade-offs
- ↔
Designing to access patterns optimises known queries; it can make unanticipated queries expensive, especially in NoSQL.
Done when you can
I can produce an access-pattern table and derive indexes and read models from it.