Cheat sheet · Data & storage
SQL and NoSQL
The problem
One shop, several kinds of data. Orders need joins and transactions, an order page wants one read, page views arrive tens of thousands a second, a cart is fetched by one key. How to choose a store by the way the data is read and written, and what happens when the questions change.
What you learn
- What a relational database gives you: joins, transactions, new questions without redesign
- Document stores: the whole object in one read, shaped for one access pattern
- Wide-column stores: huge write rates, but one table per question
- Key-value stores: the fastest lookup, and nothing else
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_client(["Shop app"])
n_api["API"]
n_sql[("Relational DB")]
n_docs[("Document store")]
n_events[("Wide-column store")]
n_kv{{"Key-value store"}}
n_client --> n_api
n_api --> n_sql
n_api --> n_docs
n_api --> n_events
n_api --> n_kvMermaid: request sequence
sequenceDiagram
participant n_api as API
participant n_sql as Relational DB
participant n_docs as Document store
participant n_events as Wide-column store
participant n_kv as Key-value store
Note over n_api,n_sql: Relational: data in tables, joined on read
n_api->>n_sql: SELECT o.id, o.created_at, p.name, i.qty
n_sql-->>n_api: 3 rows in 4 ms
Note over n_api,n_sql: A transaction: all or nothing
n_api->>n_sql: BEGIN#59;
n_sql-->>n_api: COMMIT
Note over n_api,n_docs: A document: the whole order in one read
n_api->>n_docs: db.orders.findOne({ _id: #34;o_901#34; })
n_docs-->>n_api: 1 document
Note over n_api,n_docs: A new question the model was not built for
n_api->>n_docs: db.orders.find({ #34;items.sku#34;: #34;p_17#34; })
n_docs-->>n_api: stage: COLLSCAN
Note over n_api,n_sql: The same question in SQL
n_api->>n_sql: SELECT product_id, SUM(qty)
n_sql-->>n_api: Index Scan using order_items_product_idx
Note over n_api,n_sql: Page views flood the relational database
n_api->>n_sql: INSERT INTO page_views (user_id, page, ts)
n_sql-->>n_api: ERROR: sorry, too many clients already
Note over n_api,n_events: A wide-column store for the event stream
n_api->>n_events: INSERT INTO views_by_user (user_id, ts, page)
n_events-->>n_api: Applied
Note over n_api,n_events: Asking by a column that is not the key
n_api->>n_events: SELECT user_id FROM views_by_user
n_events-->>n_api: InvalidRequest: Cannot execute this query
Note over n_api,n_events: One table per question
n_api->>n_events: BEGIN BATCH
n_events-->>n_api: Applied
Note over n_api,n_kv: A key-value store for the cart
n_api->>n_kv: HGETALL cart:u_42
n_kv-->>n_api: 1) #34;p_17#34;D2
direction: right
n_client: "Shop app" {
shape: rectangle
}
n_api: "API" {
shape: rectangle
}
n_sql: "Relational DB" {
shape: cylinder
}
n_docs: "Document store" {
shape: cylinder
}
n_events: "Wide-column store" {
shape: cylinder
}
n_kv: "Key-value store" {
shape: hexagon
}
n_client -> n_api
n_api -> n_sql
n_api -> n_docs
n_api -> n_events
n_api -> n_kvPlantUML: request sequence
@startuml
participant "API" as n_api
database "Relational DB" as n_sql
database "Document store" as n_docs
database "Wide-column store" as n_events
collections "Key-value store" as n_kv
note over n_api, n_sql : Relational: data in tables, joined on read
n_api -> n_sql : SELECT o.id, o.created_at, p.name, i.qty
n_sql --> n_api : 3 rows in 4 ms
note over n_api, n_sql : A transaction: all or nothing
n_api -> n_sql : BEGIN;
n_sql --> n_api : COMMIT
note over n_api, n_docs : A document: the whole order in one read
n_api -> n_docs : db.orders.findOne({ _id: "o_901" })
n_docs --> n_api : 1 document
note over n_api, n_docs : A new question the model was not built for
n_api -> n_docs : db.orders.find({ "items.sku": "p_17" })
n_docs --> n_api : stage: COLLSCAN
note over n_api, n_sql : The same question in SQL
n_api -> n_sql : SELECT product_id, SUM(qty)
n_sql --> n_api : Index Scan using order_items_product_idx
note over n_api, n_sql : Page views flood the relational database
n_api -> n_sql : INSERT INTO page_views (user_id, page, ts)
n_sql --> n_api : ERROR: sorry, too many clients already
note over n_api, n_events : A wide-column store for the event stream
n_api -> n_events : INSERT INTO views_by_user (user_id, ts, page)
n_events --> n_api : Applied
note over n_api, n_events : Asking by a column that is not the key
n_api -> n_events : SELECT user_id FROM views_by_user
n_events --> n_api : InvalidRequest: Cannot execute this query
note over n_api, n_events : One table per question
n_api -> n_events : BEGIN BATCH
n_events --> n_api : Applied
note over n_api, n_kv : A key-value store for the cart
n_api -> n_kv : HGETALL cart:u_42
n_kv --> n_api : 1) "p_17"
@endumlPlantUML: components
@startuml
left to right direction
agent "Shop app" as n_client
component "API" as n_api
database "Relational DB" as n_sql
database "Document store" as n_docs
database "Wide-column store" as n_events
stack "Key-value store" as n_kv
n_client --> n_api
n_api --> n_sql
n_api --> n_docs
n_api --> n_events
n_api --> n_kv
@endumlRecap
Choose the store by how the data is used.
1) Relational: joins, transactions, and new questions without redesign; the default for business data. 2) Document: one object in one read, when the access pattern is known. 3) Wide-column: very high write rates, one table per question. 4) Key-value: lookup by key and nothing else. One system often uses several.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Query p95 | 28 ms | 4 ms |
| Rows read per query | 3 | 1 |
| Writes accepted | 400/s | 40 000/s |
Steps
- Case Relational: data in tables, joined on read
Orders, order lines and products live in separate tables.
- Case A transaction: all or nothing
Placing an order is two writes: create the order and take the lamps off the stock.
- Case A document: the whole order in one read
A document store keeps the order as one JSON object, lines and product names inside it.
- Problem A new question the model was not built for
The business asks: “which orders contain lamp
p_17?”. - Fix The same question in SQL
In the relational database order lines are already a table with an index on
product_id. - Problem Page views flood the relational database
A new feature records every page view: 40 000 inserts a second.
- Fix A wide-column store for the event stream
Page views move to a wide-column store.
- Problem Asking by a column that is not the key
Now someone asks “who viewed this order page?”.
- Fix One table per question
The fix is to store the same view twice: once split by user, once split by page.
- Case A key-value store for the cart
The cart is read and written only by the user's ID, many times per visit.
- Recap Recap
Choose the store by how the data is used.
Terms
- NoSQL (document, wide-column, key-value)
- Non-relational stores: a document store returns a whole object in one read, a wide-column store takes huge write rates, a key-value store looks up by key only. Each is shaped for a known access pattern, while a relational database stays open to new questions.