🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

🗄️ धडा 04 — Data model आणि database ची निवड: आधी प्रश्नांचे model करा

📍 तुम्ही इथे आहात: 18 पैकी धडा 04 · मागे: lesson-03-api-design · पुढे: lesson-05-caching

📦 या ब्रँचमध्ये काय आहे

धडे 01–03, आणि भाग 1 ची शेवटची पायरी: data. Access patterns (app data ला विचारणार ते प्रश्न) यांची यादी करा, त्यांची उत्तरे देणारे table आणि index लिहा, आणि मगच store चा प्रकार निवडा — मूलतः relational, आणि key-value store, search engine, vector index, object storage किंवा time-series store फक्त एखाद्या access pattern ला त्याची गरज असेल तेव्हाच. design/designs.py मधील choose_store() गरजांचे store मध्ये रूपांतर करते; design/demo.py मधील data() सूचना फलकाची निवड छापते.

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

शाळेचे कार्यालय एक मोठी नोंदवही 📒 ठेवते. तिच्यासाठी कपाट विकत घेण्याआधी दीपिका विचारते: "लोक यात काय शोधणार आहेत?"

"वर्ग 3A च्या सूचना दाखवा, नव्या आधी." — दिवसाला लाखो वेळा विचारलेला.
"एक सूचना तिच्या क्रमांकाने दाखवा."
"कोणत्या सूचनांमध्ये 'क्रीडा दिन' आहे?"

पहिल्या प्रश्नासाठी ती पाने आधी वर्गानुसार, मग तारखेनुसार क्रमाने लावते, आणि प्रत्येक वर्गासाठी एक खूण-पट्टी 📑. आता कारकून 3A ची पट्टी उघडते आणि वरच्या 20 वाचते. ती पट्टी म्हणजे index.

"क्रीडा दिन" प्रश्नासाठी साधी नोंदवही वाईट — कारकुनाला प्रत्येक पान वाचावे लागेल. म्हणून लोक तो खरोखर खूप विचारत असतील, तर दीपिका पुस्तकाच्या मागे एक वेगळी शब्दसूची जोडते: "क्रीडा दिन → पाने 12, 88, 301". तो म्हणजे search engine.

Photos नोंदवहीत अजिबात जात नाहीत. ते एका कोठीच्या खोलीत 🗃️ जातात, आणि नोंदवहीत फक्त "photo box 17 मध्ये" एवढेच लिहिले जाते.

दीपिका पहिल्याच दिवशी पाच कपाटे घेत नाही. योग्य खूण-पट्ट्यांची एक चांगली नोंदवही जवळजवळ सगळ्या प्रश्नांची उत्तरे देते. एखाद्या प्रश्नाला गरज असेल तेव्हाच ती खास कपाट जोडते.

🗺️ आकृती

flowchart TB
    q["❓ access patterns first<br/>notices of class 3A, newest first<br/>one notice by id · my children's feed"]
    q --> rel["🐘 relational (PostgreSQL / Aurora)<br/>the safe default<br/>joins · transactions"]
    rel --> t["notices(id, class_id, author, title,<br/>text, urgent, created_at)<br/>index (class_id, created_at DESC)"]
    q -.->|"huge scale + key lookups"| kv["🔑 key-value<br/>DynamoDB / Cassandra"]
    q -.->|"full-text search"| se["🔎 search<br/>OpenSearch"]
    q -.->|"similar meaning"| vx["🧭 vector<br/>pgvector / k-NN"]
    q -.->|"photos, files"| ob["🪣 object storage<br/>S3"]
    q -.->|"metrics over time"| ts["⏱️ time-series<br/>Timestream / Prometheus"]

🗺️ काढलेली आवृत्ती + एक lab: https://school-edh.pages.dev/system-design/lesson-diagrams.html#l04

❓ काय

Access pattern — app data ला विचारतो तो एक प्रश्न, त्याच्या वारंवारतेसह आणि वेगाच्या उद्दिष्टासह: "एका वर्गाच्या 20 नव्या सूचना, सरासरी सेकंदाला 1,157 वेळा, p99 < 200 ms". Tables च्या आधी त्यांची यादी करा.
Entities आणि relations — सूचना फलकात schools, classes, parents, children (मूल एका वर्गात असते; पालकाला मुले असतात) आणि notices (सूचना एका वर्गाची असते) आहेत. पालकाचे feed = तिच्या मुलांच्या वर्गांच्या सूचना.
Relational database (PostgreSQL, MySQL, Aurora) — tables, joins, transactions, constraints, आणि पुढे नव्या प्रश्नांची उत्तरे देऊ शकणारे SQL. सुरक्षित default. एक primary सेकंदाला हजारो writes हाताळतो; आपल्याला 11.6 लागतात.
Index — काही columns ची क्रमवार प्रत, जिच्यामुळे database प्रत्येक row वाचण्याऐवजी seek करू शकतो. (class_id, created_at DESC) 20 index नोंदी वाचून "3A च्या नव्या सूचना" देतो. Indexes reads जलद आणि writes थोडे हळू करतात, आणि ते disk घेतात.
दुसरा store जोडणे केव्हा योग्य (प्रत्येकासाठी खरे कारण):
- key-value / wide-column (DynamoDB, Cassandra) — प्रचंड scale आणि आधीच माहीत असलेले साधे key lookups (sessions, carts, inboxes). Joins तुमची जबाबदारी बनतात.
- search engine (OpenSearch) — full-text search, टायपिंगच्या चुका सांभाळणे, ranking.
- vector index (pgvector, OpenSearch k-NN) — "सारखाच अर्थ असलेल्या सूचना शोधा" (AI शाळा आणि इथला धडा 18 पाहा).
- object storage (S3) — photos, PDFs, videos. Database फक्त key ठेवतो.
- time-series (Timestream, Prometheus) — काळानुसार आकडे: metrics, sensor readings.
Denormalise — एखाद्या गरम प्रश्नासाठी data ची प्रत दुसऱ्या आकारात ठेवा (प्रत्येक पालकाचे feed, धडा 08). वाचायला जलद, आणि प्रती एकमेकांशी जुळवून ठेवाव्या लागतात.

🤔 का

कारण data code पेक्षा जास्त काळ टिकतो. आज वाईट रचलेले table वर्षानुवर्षे काळजीपूर्वक migrate करावे लागेल. आणि कारण "कोणता database?" हा पहिला प्रश्नच चुकीचा आहे. योग्य पहिला प्रश्न आहे "कोणते प्रश्न, किती वेळा, किती जलद?" — store त्यातून ठरतो. प्रत्येक जास्तीचा store म्हणजे चालवायला, backup घ्यायला, सुरक्षित ठेवायला आणि जुळवून ठेवायला आणखी एक system. 4 जणांच्या team ने (धडा 01) एखाद्या access pattern ला स्पष्टपणे दुसरा लागत नाही तोपर्यंत एकच चालवावा.

🔧 कसे (या repo मध्ये)

design/designs.py मधील choose_store(needs) नियमांची एक यादी तपासते — {transactions, joins}, {key-lookup, huge-scale}, {search}, {similarity}, {files}, {time-series} — आणि ज्याचे सगळे शब्द needs मध्ये आहेत असा पहिला नियम परत देते. कोणताच जुळला नाही, तर ते relational परत देते: "access pattern वेगळे सांगेपर्यंत सुरक्षित default". हा चर्चा सुरू करण्यासाठीचा शिकवणीचा नियम आहे, कायदा नाही. design/demo.py मधील data() गरजांचे सहा संच वापरून पाहते आणि सूचना फलकाचे table आणि index छापते.

🧪 करून पाहा

python3 design/demo.py data
python3 - <<'EOF'
import sys; sys.path.insert(0, "design"); from designs import choose_store
for needs in ({"time-series"}, {"search"}, {"search", "transactions", "joins"}, {"key-lookup"}, {"key-lookup", "huge-scale"}, {"transactions"}):
    print(f"{', '.join(sorted(needs)):<30} → {choose_store(needs)}")
EOF

✅ तपासा — तुम्हाला काय दिसायला हवे

data छापते:

── model the data from the questions you will ask it (access patterns), then pick the store
   joins, transactions          → relational (PostgreSQL / Aurora)
   huge-scale, key-lookup       → key-value / wide-column (DynamoDB / Cassandra)
   search                       → search engine (OpenSearch)
   similarity                   → vector index (pgvector / OpenSearch k-NN)
   files                        → object storage (S3)
   (nothing special)            → relational (PostgreSQL) — the safe default until an access pattern says otherwise
   notice board: notices(id, class_id, author, title, text, urgent, created_at) · index (class_id, created_at DESC)
   one table, one index, one question answered fast — add stores only when a new access pattern needs one

तुमचा snippet छापतो:

time-series                    → time-series store (Timestream / Prometheus)
search                         → search engine (OpenSearch)
joins, search, transactions    → relational (PostgreSQL / Aurora)
key-lookup                     → relational (PostgreSQL) — the safe default until an access pattern says otherwise
huge-scale, key-lookup         → key-value / wide-column (DynamoDB / Cassandra)
transactions                   → relational (PostgreSQL) — the safe default until an access pattern says otherwise

🏁 तुम्ही आत्ताच काय सिद्ध केले

फक्त "key lookups" हे key-value store साठी पुरेसे कारण नाही — PostgreSQL key lookups चांगले करतो. Scale आणि key lookups मिळून ते कारण बनते. आणि app ला joins आणि search दोन्ही लागत असतील, तर नियम आधी relational निवडतो: मूळ नोंदींची system (system of record) relational राहते, आणि search त्याच्या शेजारी एक प्रत म्हणून जोडले जाते (events मधून भरलेले, धडा 08) — त्याच्या जागी नाही.

⚠️ नेहमीच्या चुका

आधी database निवडणे ("आपण MongoDB वापरतो") आणि प्रश्न नंतर
reports आणि ऐनवेळच्या प्रश्नांनी भरलेल्या app साठी key-value store — प्रत्येक नवा प्रश्न म्हणजे नवे table
चार जणांच्या team साठी पाच stores
सर्वात गरम प्रश्नासाठी index नाही — किंवा "असू दे" म्हणून प्रत्येक column वर index
database मध्ये blobs म्हणून photos; ते object storage मध्ये ठेवा आणि key साठवा
search engine हीच data ची एकमेव प्रत — तो एक index आहे, तो database मधून पुन्हा बांधा

🏭 प्रत्यक्ष वापरात

सूचना फलकाचे पहिले table आणि त्याचा एक गरम index, PostgreSQL मध्ये:

CREATE TABLE notices (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  class_id    text        NOT NULL REFERENCES classes(id),
  author_id   bigint      NOT NULL REFERENCES teachers(id),
  title       text        NOT NULL,
  body        text        NOT NULL,                   -- "text" in the demo
  urgent      boolean     NOT NULL DEFAULT false,
  photo_key   text,                                   -- the S3 object key, not the photo
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX notices_class_newest ON notices (class_id, created_at DESC, id DESC);

EXPLAIN SELECT id, title FROM notices WHERE class_id = '3A' ORDER BY created_at DESC, id DESC LIMIT 20;
-- look for: Index Scan using notices_class_newest (not Seq Scan)

On a real account — पुढे एखाद्या access pattern ला key-value scale लागला (उदाहरणार्थ key ने वाचल्या जाणाऱ्या 100 million पालक-inbox rows), तर त्या एका प्रश्नासाठी रचलेले DynamoDB table:

aws dynamodb create-table --table-name parent-inbox \
    --attribute-definitions AttributeName=parent_id,AttributeType=S AttributeName=created_at,AttributeType=S \
    --key-schema AttributeName=parent_id,KeyType=HASH AttributeName=created_at,KeyType=RANGE \
    --billing-mode PAY_PER_REQUEST

अधिक खोलात: Database शाळा (normal forms, joins, indexes आणि EXPLAIN, transactions, replicas, partitioning, NoSQL) आणि Vector DB शाळा.

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: access patterns ची यादी design doc मध्ये schema च्या शेजारी लिहा. कोणी विचारले "DynamoDB का नाही?" तर उत्तर म्हणजे त्या यादीतली एक ओळ, भावना नव्हे.

⏭️ पुढे

भाग 1 पूर्ण झाला: कागद, आकडे, करार, data. आकडे म्हणाले प्रत्येक write मागे 100 reads. भाग 2 त्यासाठीच्या पहिल्या बांधणीच्या ठोकळ्याने सुरू होतो: वाचणाऱ्याजवळ प्रती ठेवणे.

git checkout lesson-05-caching

🗄️ Lesson 04 — Data model & database choice: model the questions first

📍 You are here: Lesson 04 of 18 · Previous: lesson-03-api-design · Next: lesson-05-caching

📦 What's in this branch

Lessons 01–03, plus the last step of Part 1: the data. List the access patterns (the questions the app will ask the data), write the table and the index that answers them, and only then pick the kind of store — relational by default, and a key-value store, search engine, vector index, object storage or time-series store only when an access pattern needs one. choose_store() in design/designs.py turns needs into a store; data() in design/demo.py prints the notice board's choice.

🧒 Explain like I'm 5

The school office keeps a big register 📒. Before Dipika buys a cupboard for it, she asks: "What will people look up in it?"

"Show me the notices of class 3A, newest first." — asked millions of times a day.
"Show me one notice by its number."
"Which notices mention 'sports day'?"

For the first question, she puts the pages in order by class, then by date, with a tab for each class 📑. Now the clerk opens the 3A tab and reads the top 20. That tab is an index.

For the "sports day" question, a normal register is bad — the clerk would read every page. So if people really ask it a lot, Dipika adds a separate word index at the back of the book: "sports day → pages 12, 88, 301". That is a search engine.

Photos do not go in the register at all. They go in a store room 🗃️, and the register only writes "photo in box 17".

Dipika does not buy five cupboards on day one. One good register with the right tabs answers almost everything. She adds a special cupboard only when a question needs it.

🗺️ Diagram

flowchart TB
    q["❓ access patterns first<br/>notices of class 3A, newest first<br/>one notice by id · my children's feed"]
    q --> rel["🐘 relational (PostgreSQL / Aurora)<br/>the safe default<br/>joins · transactions"]
    rel --> t["notices(id, class_id, author, title,<br/>text, urgent, created_at)<br/>index (class_id, created_at DESC)"]
    q -.->|"huge scale + key lookups"| kv["🔑 key-value<br/>DynamoDB / Cassandra"]
    q -.->|"full-text search"| se["🔎 search<br/>OpenSearch"]
    q -.->|"similar meaning"| vx["🧭 vector<br/>pgvector / k-NN"]
    q -.->|"photos, files"| ob["🪣 object storage<br/>S3"]
    q -.->|"metrics over time"| ts["⏱️ time-series<br/>Timestream / Prometheus"]

🗺️ Drawn version + a lab: https://school-edh.pages.dev/system-design/lesson-diagrams.html#l04

❓ What

Access pattern — one question the app asks the data, with its frequency and its speed target: "the 20 newest notices of one class, 1,157 times a second at the average, p99 < 200 ms". List them before the tables.
Entities and relations — the notice board has schools, classes, parents, children (a child is in a class; a parent has children) and notices (a notice belongs to a class). A parent's feed = notices of the classes of her children.
Relational database (PostgreSQL, MySQL, Aurora) — tables, joins, transactions, constraints, and SQL that can answer new questions later. The safe default. One primary handles thousands of writes a second; ours needs 11.6.
Index — a sorted copy of some columns that lets the database seek instead of reading every row. (class_id, created_at DESC) answers "newest notices of 3A" by reading 20 index entries. Indexes make reads fast and writes a little slower, and they take disk.
When another store is worth adding (each with a real reason):
- key-value / wide-column (DynamoDB, Cassandra) — huge scale and simple key lookups you know in advance (sessions, carts, inboxes). Joins become your job.
- search engine (OpenSearch) — full-text search, typo tolerance, ranking.
- vector index (pgvector, OpenSearch k-NN) — "find notices that mean the same" (see the AI school and lesson 18 here).
- object storage (S3) — photos, PDFs, videos. The database keeps only the key.
- time-series (Timestream, Prometheus) — numbers over time: metrics, sensor readings.
Denormalise — keep a copy of data in a second shape for a hot question (a per-parent feed, lesson 08). It is faster to read, and you must keep the copies in step.

🤔 Why

Because the data outlives the code. A table you design badly today will be migrated, with care, for years. And because "which database?" is the wrong first question. The right first question is "which questions, how often, how fast?" — the store follows from that. Every extra store is one more system to run, back up, secure and keep in step. A team of 4 (lesson 01) should run one unless an access pattern clearly needs a second.

🔧 How (in this repo)

choose_store(needs) in design/designs.py walks a list of rules — {transactions, joins}, {key-lookup, huge-scale}, {search}, {similarity}, {files}, {time-series} — and returns the first rule whose words are all in needs. If none match, it returns relational: "the safe default until an access pattern says otherwise". It is a teaching rule to start a discussion, not a law. data() in design/demo.py tries six sets of needs and prints the notice board's table and index.

🧪 Try it

python3 design/demo.py data
python3 - <<'EOF'
import sys; sys.path.insert(0, "design"); from designs import choose_store
for needs in ({"time-series"}, {"search"}, {"search", "transactions", "joins"}, {"key-lookup"}, {"key-lookup", "huge-scale"}, {"transactions"}):
    print(f"{', '.join(sorted(needs)):<30} → {choose_store(needs)}")
EOF

✅ Verify — what you should see

data prints:

── model the data from the questions you will ask it (access patterns), then pick the store
   joins, transactions          → relational (PostgreSQL / Aurora)
   huge-scale, key-lookup       → key-value / wide-column (DynamoDB / Cassandra)
   search                       → search engine (OpenSearch)
   similarity                   → vector index (pgvector / OpenSearch k-NN)
   files                        → object storage (S3)
   (nothing special)            → relational (PostgreSQL) — the safe default until an access pattern says otherwise
   notice board: notices(id, class_id, author, title, text, urgent, created_at) · index (class_id, created_at DESC)
   one table, one index, one question answered fast — add stores only when a new access pattern needs one

Your snippet prints:

time-series                    → time-series store (Timestream / Prometheus)
search                         → search engine (OpenSearch)
joins, search, transactions    → relational (PostgreSQL / Aurora)
key-lookup                     → relational (PostgreSQL) — the safe default until an access pattern says otherwise
huge-scale, key-lookup         → key-value / wide-column (DynamoDB / Cassandra)
transactions                   → relational (PostgreSQL) — the safe default until an access pattern says otherwise

🏁 What you just proved

"Key lookups" alone do not justify a key-value store — PostgreSQL does key lookups well. Scale plus key lookups does. And when an app needs joins and search, the rule picks relational first: the system of record stays relational, and search is added next to it as a copy (fed by events, lesson 08) — not instead of it.

⚠️ Common mistakes

choosing the database first ("we use MongoDB") and the questions later
a key-value store for an app full of reports and ad-hoc questions — every new question is a new table
five stores for a team of four
no index for the hottest question — or an index on every column "just in case"
photos inside the database as blobs; keep them in object storage and store the key
a search engine as the only copy of the data — it is an index, rebuild it from the database

🏭 In production

The notice board's first table and its one hot index, in PostgreSQL:

CREATE TABLE notices (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  class_id    text        NOT NULL REFERENCES classes(id),
  author_id   bigint      NOT NULL REFERENCES teachers(id),
  title       text        NOT NULL,
  body        text        NOT NULL,                   -- "text" in the demo
  urgent      boolean     NOT NULL DEFAULT false,
  photo_key   text,                                   -- the S3 object key, not the photo
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX notices_class_newest ON notices (class_id, created_at DESC, id DESC);

EXPLAIN SELECT id, title FROM notices WHERE class_id = '3A' ORDER BY created_at DESC, id DESC LIMIT 20;
-- look for: Index Scan using notices_class_newest (not Seq Scan)

On a real account — if one access pattern later needs key-value scale (for example 100 million per-parent inbox rows read by key), a DynamoDB table designed for that one question:

aws dynamodb create-table --table-name parent-inbox \
    --attribute-definitions AttributeName=parent_id,AttributeType=S AttributeName=created_at,AttributeType=S \
    --key-schema AttributeName=parent_id,KeyType=HASH AttributeName=created_at,KeyType=RANGE \
    --billing-mode PAY_PER_REQUEST

Go deeper: the Database school (normal forms, joins, indexes and EXPLAIN, transactions, replicas, partitioning, NoSQL) and the Vector DB school.

🏭 Why this matters in production: write the access-pattern list into the design doc next to the schema. When someone asks "why not DynamoDB?" the answer is a line in that list, not a feeling.

⏭️ Next

Part 1 is done: the sheet, the numbers, the contract, the data. The numbers said 100 reads for every write. Part 2 starts with the first building block for that: keeping copies close to the reader.

git checkout lesson-05-caching

← Previousapi design Next →caching

This page is the lesson's README from the lesson-04-data-model branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.