🏘️ धडा 11 — NoSQL आणि इतर खोल्या: प्रश्नासाठी योग्य खोली निवडणे

📍 तुम्ही इथे आहात: 18 पैकी धडा 11 · मागील: lesson-10-scaling · पुढील: lesson-12-performance-ops

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

धडे 01–10, आणि शाळा बांधू शकेल अशा इतर प्रकारच्या खोल्या — document, key-value, columnar, graph, vector — प्रत्येक कशाचे उत्तर चांगले देते, प्रत्येक काय सोडून देते, आणि निवडीचा एकच नियम.

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

रेकॉर्ड रूम ही relational खोली आहे: नोंदवह्या, keys, नियम, JOINs, transactions. ती शाळेच्या जवळजवळ प्रत्येक प्रश्नाचे उत्तर चांगले देते. पण काही प्रश्न तिथे अवघड जातात, म्हणून शाळा इतर खोल्या बांधतात:

Document खोली 📄 (MongoDB): प्रत्येक विद्यार्थी म्हणजे एक folder, ज्यात सगळे आत आहे — nested, लवचिक, ठरलेले headings नाहीत. प्रत्येक record पूर्ण वाचला जातो तेव्हा उत्तम; अनेक folders मध्ये प्रश्न विचारताना अवघड.
Key-value खोली 🔑 (Redis, DynamoDB): एक label, एक उत्तर, microseconds मध्ये. Sessions, counters, धडा 10 मधला cache.
Columnar खोली 📊 (BigQuery, ClickHouse): नोंदवह्या column नुसार साठवलेल्या, म्हणून "दहा वर्षांची हजेरी बेरीज करा" झटक्यात होते; एक ओळ बदलणे हळू. Analytics साठी, रोजच्या counter साठी नाही.
Graph खोली 🕸️ (Neo4j): "एकाच वर्गात असलेल्या मैत्रिणींच्या मैत्रिणी" — नोंदवह्यांमधले JOINs नाहीत, तर गोष्टींमधल्या उड्या (hops).
अर्थाचे सभागृह 🗺️ (vector databases — VectorDB शाळा): अर्थाने सर्वात जवळचे, search आणि RAG साठी.

नियम: खोली प्रश्नाच्या मागे येते. relational खोलीतून सुरुवात करा; ती ज्या प्रश्नाचे उत्तर वाईट देते त्यासाठीच दुसरी खोली बांधा — आणि सत्य एकाच ठिकाणी ठेवा.

🗺️ आकृती

flowchart TB
    rel["🗄️ relational — the default room<br/>rules, JOINs, transactions"]
    doc["📄 document<br/>one folder per thing, flexible shape"]
    kv["🔑 key-value<br/>one label → one answer, µs"]
    col["📊 columnar<br/>sum a billion rows"]
    gr["🕸️ graph<br/>hops between things"]
    vec["🗺️ vector<br/>nearest by meaning"]
    q["🧭 the room follows the question"] --> rel
    rel -.->|"a question it answers badly"| doc & kv & col & gr & vec

❓ काय

Document: JSON records, schema-on-read, nested arrays; fields वर indexes; documents पलीकडचे transactions कमकुवत (सुधारत आहेत). जेव्हा record हेच कामाचे एकक असते (product page, profile) तेव्हा जिंकते.
Key-value: hash lookups; TTLs; साध्या data structures (Redis lists, sets). प्रश्नांसाठी नाही ("सर्व keys जिथे…").
Columnar / warehouse: मुख्यतः append, batch loads, aggregate queries; transactional खोलीपासून वेगळी (ETL/ELT तिला data पुरवते).
Graph: nodes आणि edges; जे traversals सहा self-JOINs झाले असते.
Vector: embeddings + approximate nearest neighbour; RAG चा पान शोधणारा. मध्यम प्रमाणासाठी Postgres कडे pgvector आहे.
Time-series, search (Elasticsearch/OpenSearch) — अधिक प्रश्नांसाठी अधिक खोल्या; नियम तोच.
CAP / consistency: distributed खोल्या partition च्या वेळी availability साठी consistency सोडतात; "eventually consistent" म्हणजे तुमचा read जुना असू शकतो — प्रत्येक प्रश्नासाठी ते चालेल का हे ठरवा.

🤔 का

कारण मोजलेल्या प्रश्नाशिवाय "scale साठी NoSQL" केल्याने अनेक teams नी त्यांचे constraints, JOINs आणि transactions गमावले — आणि ते मोठी किंमत देऊन परत विकत घेतले. आणि उलटी चूकही खरी आहे: analytics किंवा अर्थ-search transactional खोलीतून जबरदस्तीने चालवणे. आधी प्रश्नाला नाव द्या; मग खोलीला.

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

SQLite प्रामाणिकपणे दोन खोल्या बनते: relational (प्रत्येक धड्यात) आणि, JSON column सह, एक छोटी document खोली — खालचा सराव. शेजारचे अर्थाचे सभागृह म्हणजे VectorDB शाळेचे vectordb/vectordb.py.

🧪 करून पाहा

python3 - <<'EOF'
import sqlite3, json; c = sqlite3.connect("db/school.db")
c.execute("CREATE TABLE IF NOT EXISTS profiles (student_id INTEGER PRIMARY KEY REFERENCES students(id), doc TEXT NOT NULL)")
c.execute("INSERT OR REPLACE INTO profiles VALUES (2, ?)", (json.dumps({"clubs": ["chess", "robotics"], "guardian": {"name": "R. Rao", "phone": "555-0102"}}),))
c.commit()
print(c.execute("SELECT s.name, json_extract(p.doc, '$.guardian.name'), json_extract(p.doc, '$.clubs[0]') FROM profiles p JOIN students s ON s.id = p.student_id").fetchall())
print(c.execute("SELECT s.name FROM profiles p JOIN students s ON s.id=p.student_id, json_each(p.doc, '$.clubs') j WHERE j.value = 'robotics'").fetchall())
EOF
# paper exercise: for each question, name the room — (a) 'how many students were present each month for 10 years?'
# (b) 'is the session token valid?' (c) 'which students share two clubs with Katrina?' (d) 'find the policy paragraph closest in meaning to this question' (e) 'transfer Aishwarya to 3B and update the counts'

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

Document सराव relational row मधल्या JSON मधून कतरिनाचे पालक आणि पहिला club छापतो, आणि json_each ने robotics चे सदस्य शोधतो — रेकॉर्ड रूमच्या आत एक document खोली, key मात्र अजूनही relational. कागदावर: (a) columnar, (b) key-value, (c) graph (किंवा या आकारात self-JOIN), (d) vector, (e) relational — transaction.

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

खोल्या प्रश्नानुसार निवडल्या जातात, आणि relational खोली आपल्या keys न सोडता एक छोटी document खोली सामावू शकते.

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

fashion किंवा "काय scale होते" यावरून खोली निवडणे — प्रश्न मोजा
एकच सत्य दोन खोल्यांमध्ये, मालक कोणीच नाही — त्या एकमेकांशी जुळणार नाहीत
सकाळी 9 वाजता transactional खोलीवर analytics चालवणे
पैसे किंवा जागांसाठी "eventually consistent" चालेल असे गृहीत धरणे
"schema सतत बदलतो" म्हणून document store — ही modelling ची समस्या आहे (धडा 05), खोलीची नाही

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: बहुतेक खऱ्या systems म्हणजे Postgres + Redis + एक analytics खोली, आणि product ला गरज पडल्यावर search किंवा vector खोली. Interview मधला प्रश्न कधीच "कोणती सर्वोत्तम" नसतो — तो असतो "कोणता प्रश्न, आणि ती खोली काय सोडून देते".

⏭️ पुढे

शेवटचा टप्पा: performance आणि operations — हळू queries ची तपासणी, N+1, आणि रेकॉर्ड रूमसाठी on-call चेकलिस्ट.

git checkout lesson-12-performance-ops

🏘️ Lesson 11 — NoSQL & other rooms: choosing the room for the question

📍 You are here: Lesson 11 of 18 · Previous: lesson-10-scaling · Next: lesson-12-performance-ops

📦 What's in this branch

Lessons 01–10, plus the other kinds of room a school might build — document, key-value, columnar, graph, vector — what each answers well, what each gives up, and the one rule for choosing.

🧒 Explain like I'm 5

The record room is the relational room: registers, keys, rules, JOINs, transactions. It answers almost every school question well. But some questions are awkward there, so schools build other rooms:

The document room 📄 (MongoDB): each student is one folder with everything inside — nested, flexible, no fixed headings. Great when each record is read whole; awkward when you ask across folders.
The key-value room 🔑 (Redis, DynamoDB): one label, one answer, in microseconds. Sessions, counters, the cache from lesson 10.
The columnar room 📊 (BigQuery, ClickHouse): registers stored column by column, so "sum attendance over ten years" flies; editing one line is slow. Analytics, not the daily counter.
The graph room 🕸️ (Neo4j): "friends of friends who share a class" — hops between things, not JOINs across registers.
The meaning hall 🗺️ (vector databases — the VectorDB school): nearest-by-meaning, for search and RAG.

The rule: the room follows the question. Start in the relational room; build another only for a question it answers badly — and keep the truth in one place.

🗺️ Diagram

flowchart TB
    rel["🗄️ relational — the default room<br/>rules, JOINs, transactions"]
    doc["📄 document<br/>one folder per thing, flexible shape"]
    kv["🔑 key-value<br/>one label → one answer, µs"]
    col["📊 columnar<br/>sum a billion rows"]
    gr["🕸️ graph<br/>hops between things"]
    vec["🗺️ vector<br/>nearest by meaning"]
    q["🧭 the room follows the question"] --> rel
    rel -.->|"a question it answers badly"| doc & kv & col & gr & vec

❓ What

Document: JSON records, schema-on-read, nested arrays; indexes on fields; weaker cross-document transactions (improving). Wins when the record is the unit of work (a product page, a profile).
Key-value: hash lookups; TTLs; simple data structures (Redis lists, sets). Not for questions ("all keys where…").
Columnar / warehouse: append-heavy, batch loads, aggregate queries; separate from the transactional room (ETL/ELT feeds it).
Graph: nodes and edges; traversals that would be six self-JOINs.
Vector: embeddings + approximate nearest neighbour; the RAG page finder. Postgres has pgvector for modest scale.
Time-series, search (Elasticsearch/OpenSearch) — more rooms for more questions; the same rule applies.
CAP / consistency: distributed rooms trade consistency for availability under partition; "eventually consistent" means your read may be stale — decide per question whether that is acceptable.

🤔 Why

Because "NoSQL because scale" without a measured question cost many teams their constraints, their JOINs and their transactions — and they bought them back at great expense. And because the opposite mistake is real too: forcing analytics or meaning-search through a transactional room. Name the question; then name the room.

🔧 How (in this repo)

SQLite plays two rooms honestly: the relational one (every lesson) and, with a JSON column, a small document one — the exercise below. The meaning hall next door is the VectorDB school's vectordb/vectordb.py.

🧪 Try it

python3 - <<'EOF'
import sqlite3, json; c = sqlite3.connect("db/school.db")
c.execute("CREATE TABLE IF NOT EXISTS profiles (student_id INTEGER PRIMARY KEY REFERENCES students(id), doc TEXT NOT NULL)")
c.execute("INSERT OR REPLACE INTO profiles VALUES (2, ?)", (json.dumps({"clubs": ["chess", "robotics"], "guardian": {"name": "R. Rao", "phone": "555-0102"}}),))
c.commit()
print(c.execute("SELECT s.name, json_extract(p.doc, '$.guardian.name'), json_extract(p.doc, '$.clubs[0]') FROM profiles p JOIN students s ON s.id = p.student_id").fetchall())
print(c.execute("SELECT s.name FROM profiles p JOIN students s ON s.id=p.student_id, json_each(p.doc, '$.clubs') j WHERE j.value = 'robotics'").fetchall())
EOF
# paper exercise: for each question, name the room — (a) 'how many students were present each month for 10 years?'
# (b) 'is the session token valid?' (c) 'which students share two clubs with Katrina?' (d) 'find the policy paragraph closest in meaning to this question' (e) 'transfer Aishwarya to 3B and update the counts'

✅ Verify — what you should see

The document exercise prints Katrina's guardian and first club from JSON inside a relational row, and finds robotics members with json_each — a document room inside the record room, with the key still relational. Paper: (a) columnar, (b) key-value, (c) graph (or a self-JOIN at this size), (d) vector, (e) relational — the transaction.

🏁 What you just proved

Rooms are chosen per question, and the relational room can host a small document room without giving up its keys.

⚠️ Common mistakes

picking a room by fashion or by "what scales" — measure the question
two rooms holding the same truth with no owner — they will disagree
running analytics on the transactional room at 9 am
assuming "eventually consistent" is fine for money or seats
a document store because "the schema keeps changing" — that is a modelling problem (lesson 05), not a room problem

🏭 Why this matters in production: most real systems are Postgres + Redis + one analytics room, with a search or vector room when a product needs it. The interview question is never "which is best" — it is "which question, and what does the room give up".

⏭️ Next

The last mile: performance and operations — slow-query triage, N+1, and the on-call checklist for the record room.

git checkout lesson-12-performance-ops

← Previousscaling Next →performance ops

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