mufassall

Cheat sheet · Data & storage

SQL and NoSQL

13 min · Beginner · mufassall.dtwin.uz/en/l/sql-nosql/

← Open the lesson

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
Final scene
45%40%15%20%65%

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_kv

Mermaid: 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_kv

PlantUML: 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"
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Query p9528 ms4 ms
Rows read per query31
Writes accepted400/s40 000/s

Steps

  1. Case Relational: data in tables, joined on read

    Orders, order lines and products live in separate tables.

  2. Case A transaction: all or nothing

    Placing an order is two writes: create the order and take the lamps off the stock.

  3. 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.

  4. Problem A new question the model was not built for

    The business asks: “which orders contain lamp p_17?”.

  5. Fix The same question in SQL

    In the relational database order lines are already a table with an index on product_id.

  6. Problem Page views flood the relational database

    A new feature records every page view: 40 000 inserts a second.

  7. Fix A wide-column store for the event stream

    Page views move to a wide-column store.

  8. Problem Asking by a column that is not the key

    Now someone asks “who viewed this order page?”.

  9. Fix One table per question

    The fix is to store the same view twice: once split by user, once split by page.

  10. Case A key-value store for the cart

    The cart is read and written only by the user's ID, many times per visit.

  11. 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.