NoSQL — Key-Value, Document, Wide-Column, Graph

NoSQL — চারটি প্রধান family

Read: ~45 min Medium 12 practice problems 4 database flavours

1. Why NoSQL Exists

For three decades after Codd's 1970 paper, "database" meant exactly one thing: a relational database with SQL on top. That changed in the late 2000s when web-scale companies — Amazon, Google, Facebook — hit workloads that the classical relational model could not serve cheaply: billions of small writes per day, petabytes of mostly-flat data, schemas that drifted faster than DBAs could ALTER TABLE, and graph traversals that dissolved into thousand-table joins. Their internal answers (Bigtable 2006, Dynamo 2007, Cassandra 2008) inspired a wave of open-source databases that explicitly broke from SQL — collectively branded "NoSQL", originally meaning "non-relational" and later softened to "Not Only SQL".

১৯৭০ সালে Codd-এর পেপার থেকে প্রায় তিন দশক "database" মানেই ছিল relational + SQL। ২০০৭-০৮ সালে Amazon, Google, Facebook-এর মতো কোম্পানিগুলো এমন workload পেল যেখানে relational model দামি বা slow হয়ে যাচ্ছিল — billions of small writes, schema-less data, graph traversal। তখনই Bigtable, Dynamo, Cassandra-র জন্ম। সেই থেকে শুরু হয় NoSQL movement — মানে relational-নয় বা "Not Only SQL"।

Today NoSQL is not one thing — it is four distinct families, each optimised for a different shape of data and access. This module walks through them with real syntax and Bangladesh-flavoured examples.

Figure 38.1 — The four canonical NoSQL families, each with its own data model and query style.

2. Key-Value Stores — The Simplest Idea, Brutally Optimised

A key-value store is a giant HashMap<String, Bytes>. Put a key, get back the value; that is the entire API. Because the model is so small, the engineering is free to be extreme: Redis lives entirely in RAM and answers in microseconds; DynamoDB shards across thousands of machines and gives single-digit-ms latency at any scale; ScyllaDB pushes a million reads per second per node.

Use cases you will instantly recognise: session caches, OTP store, rate limiting, leaderboards, real-time counters. The bKash login OTP that arrives on your phone almost certainly lives for 5 minutes in a Redis key like otp:01710-123456.

Key-value store মানে বিশাল একটি HashMap<String, Bytes> — শুধু put এবং get। API এত ছোট বলেই engineering চরম optimised: Redis পুরো RAM-এ থাকে (microsecond latency), DynamoDB হাজার machine-এ shard হয়ে single-digit-ms দেয়। কাজে লাগে — session cache, OTP store, rate limiter, leaderboard, real-time counter। আপনার bKash login OTP সম্ভবত একটি Redis key-তে ৫ মিনিট-এর জন্য বসে থাকে।

redis.txt

# Redis CLI — runs on the server, not in this browser.

# 1) OTP that auto-expires after 5 minutes
SET otp:01710-123456 478912 EX 300
GET otp:01710-123456
TTL otp:01710-123456

# 2) Rate limit — 100 requests per minute per IP
INCR ratelimit:103.92.10.5:202505101430
EXPIRE ratelimit:103.92.10.5:202505101430 60

# 3) Leaderboard for an online quiz
ZADD quiz:may10 95 "Rahim"
ZADD quiz:may10 88 "Sumi"
ZADD quiz:may10 99 "Tanvir"
ZREVRANGE quiz:may10 0 9 WITHSCORES

# 4) Hash — store a small object atomically
HSET user:42 name "Karim" city "Khulna" tier "gold"
HGETALL user:42

2.1 Strengths and weaknesses

✓ Great at	✗ Bad at
Sub-ms point lookups	Range queries on values
Atomic counters & locks	Joins, multi-key transactions across shards
Caching, ephemeral data	Reporting / analytics
Massive horizontal scale	Complex queries by anything other than the key

We can simulate a key-value store inside SQLite to feel its API:

kv_in_sqlite.sql

-- Tiny KV "store" backed by SQLite — illustrative only.
CREATE TABLE kv(k TEXT PRIMARY KEY, v TEXT, ttl INTEGER);

INSERT INTO kv VALUES
    ('otp:01710-123456', '478912', 300),
    ('session:u42',      'JWT-abc...', 3600),
    ('rate:103.92.10.5', '17',       60);

-- 'GET'
SELECT v FROM kv WHERE k = 'session:u42';

-- 'SCAN' all rate-limit keys
SELECT * FROM kv WHERE k LIKE 'rate:%';

3. Document Stores — JSON as a First-Class Citizen

A document database stores a tree of nested fields per record — typically as BSON (MongoDB) or JSON (Couchbase, Firestore). Unlike a key-value blob, the engine actually understands the document and can index inner fields, query by address.district = "Sylhet", partially update one field without rewriting the whole record, and aggregate across millions of documents.

The schema is flexible: documents in the same collection can have different fields. That is liberating during rapid product iteration but dangerous in the long run if no discipline is applied — JSON Schema validation, careful migration scripts, and code-side typing become essential at scale.

Document database প্রতিটি record-কে একটি nested JSON tree-হিসেবে রাখে। MongoDB BSON ব্যবহার করে — engine শুধু blob-হিসেবে দেখে না, ভিতরের field বুঝে। তাই address.district = "Sylhet"-এ index লাগানো যায়, একটি field partially update করা যায়। schema flexible — দ্রুত product iteration-এ helpful, কিন্তু discipline না থাকলে long-term-এ chaos তৈরি হয়।

mongo.js

// MongoDB shell — runs in mongosh, not in this page

// Insert a few rider documents (Pathao-style)
db.riders.insertMany([
  { _id: "R-101", name: "Rahim", city: "Dhaka",
    vehicle: { type: "bike", cc: 125 }, rating: 4.8 },
  { _id: "R-102", name: "Karim", city: "Sylhet",
    vehicle: { type: "car",  cc: 1500 }, rating: 4.6 },
  { _id: "R-103", name: "Tanvir", city: "Dhaka",
    vehicle: { type: "bike", cc: 150 }, rating: 4.9 }
]);

// Query nested field — engine uses an index if you've created one
db.riders.find({ city: "Dhaka", "vehicle.type": "bike" });

// Aggregation pipeline — average rating per city
db.riders.aggregate([
  { $group: { _id: "$city", avgRating: { $avg: "$rating" } } },
  { $sort:  { avgRating: -1 } }
]);

// Partial update — set one nested field, leave the rest alone
db.riders.updateOne(
  { _id: "R-101" },
  { $set: { "vehicle.cc": 155 }, $inc: { trips: 1 } }
);

SQLite has had JSON1 built in since 2015 and a true JSON column type (with subtype-checking) since 3.45. We can build a tiny document store with it:

json_in_sqlite.sql

CREATE TABLE riders(id TEXT PRIMARY KEY, doc TEXT);

INSERT INTO riders VALUES
 ('R-101', json('{"name":"Rahim","city":"Dhaka","vehicle":{"type":"bike","cc":125},"rating":4.8}')),
 ('R-102', json('{"name":"Karim","city":"Sylhet","vehicle":{"type":"car","cc":1500},"rating":4.6}')),
 ('R-103', json('{"name":"Tanvir","city":"Dhaka","vehicle":{"type":"bike","cc":150},"rating":4.9}'));

-- Query inside the document like Mongo does
SELECT id,
       json_extract(doc,'$.name')         AS name,
       json_extract(doc,'$.vehicle.type') AS vehicle
FROM   riders
WHERE  json_extract(doc,'$.city') = 'Dhaka'
  AND  json_extract(doc,'$.vehicle.type') = 'bike';

-- Aggregate — average rating per city
SELECT json_extract(doc,'$.city') AS city,
       AVG(json_extract(doc,'$.rating')) AS avg_r
FROM riders
GROUP BY city
ORDER BY avg_r DESC;

4. Wide-Column Stores — Tables, but Distributed and Sparse

A wide-column database (Cassandra, ScyllaDB, HBase, Bigtable) looks superficially like a relational table — you have rows, you have columns — but the layout and query model differ deeply. Each row is identified by a partition key that decides which node owns it; within a partition, rows are sorted by a clustering key. Columns are sparse: a given row may have any subset of the defined columns; missing columns cost zero bytes.

The query language CQL looks like SQL with critical restrictions: you can only filter on the partition key (and optionally clustering key), because anything else would require a full cluster scan. This forces schema-by-query-pattern: design your table around the queries you actually need.

Wide-column store (Cassandra, HBase, Bigtable) দূর থেকে relational table-এর মতো লাগে — কিন্তু গভীরে অনেক আলাদা। প্রতিটি row-এর একটি partition key থাকে যা সিদ্ধান্ত করে এটি কোন node-এ থাকবে; partition-এর ভিতরে row-গুলো clustering key অনুসারে sorted। Column sparse — কোনো column missing থাকলে byte খরচ হয় না। CQL দেখতে SQL-এর মতো হলেও filter শুধু partition key-তে — এই কারণে schema design হয় query-pattern অনুযায়ী, theoretical normalization দিয়ে নয়।

cassandra.cql

-- Cassandra CQL — runs in cqlsh, not in this page.
-- Use case: time-series of GPS pings from Uber/Pathao bikes.

CREATE TABLE gps_pings (
    rider_id   TEXT,
    day        DATE,
    ts         TIMESTAMP,
    lat        DOUBLE,
    lon        DOUBLE,
    speed      INT,
    PRIMARY KEY ((rider_id, day), ts)   -- partition + clustering
) WITH CLUSTERING ORDER BY (ts DESC);

INSERT INTO gps_pings (rider_id,day,ts,lat,lon,speed)
VALUES ('R-101', '2025-05-10', toTimestamp(now()), 23.7806, 90.4193, 42);

-- Allowed: filter by full partition key
SELECT * FROM gps_pings
WHERE  rider_id = 'R-101' AND day = '2025-05-10'
LIMIT 100;

-- Range scan within a partition (clustering key)
SELECT * FROM gps_pings
WHERE  rider_id = 'R-101' AND day = '2025-05-10'
  AND  ts >= '2025-05-10 09:00' AND ts < '2025-05-10 10:00';

-- Forbidden in production: filter by non-PK column without ALLOW FILTERING.
-- Cassandra refuses, because it would touch every node.
-- SELECT * FROM gps_pings WHERE speed > 80;     -- ERROR

4.1 Partition + clustering — the mental model

Figure 38.2 — A partition is the unit of distribution and locality. Range scans inside one partition are fast; cross-partition queries are not.

5. Graph Databases — When Joins Become a First-Class Operation

Some data is naturally a graph: friend-of-friend networks, fraud rings, supply chains, knowledge graphs, recommendation engines. In SQL, "find friends of friends of friends of Rahim who live in Sylhet" expands into a self-join three levels deep — slow and unreadable. A graph database stores nodes and edges natively, indexes adjacency, and answers such queries in time proportional to the answer size, not the graph size.

Neo4j is the canonical example, with a query language called Cypher that draws ASCII-art patterns: (a)-[:KNOWS]->(b). AWS Neptune, JanusGraph, ArangoDB, and TigerGraph occupy similar niches.

কিছু data স্বাভাবিকভাবেই graph — friend-of-friend network, fraud ring, supply chain, recommendation। SQL-এ "Rahim-এর তিন স্তরের বন্ধু যারা Sylhet-এ থাকে" — তিনটা self-join, slow। Graph database (Neo4j) node ও edge-কে নিজস্ব structure-হিসেবে রাখে, adjacency-তে index দেয়, এবং এমন query-র উত্তর দেয় answer-এর size-এর সমানুপাতিক time-এ। Query language: Cypher।

neo4j.cypher

// Cypher — runs in Neo4j browser / cypher-shell, not here.
// Use case: a fraud-detection ring among bKash agents.

// Create a few nodes
CREATE (:Agent { id: "A-1", name: "Rahim",  city: "Dhaka"  }),
       (:Agent { id: "A-2", name: "Karim",  city: "Khulna" }),
       (:Agent { id: "A-3", name: "Sumi",   city: "Sylhet" }),
       (:Agent { id: "A-4", name: "Tanvir", city: "Dhaka"  });

// Create directed transfers between them
MATCH (a:Agent { id: "A-1" }), (b:Agent { id: "A-2" })
CREATE (a)-[:SENT { amt: 5000, ts: "2025-05-08" }]->(b);

// Friend-of-friend (2 hops)
MATCH (start:Agent { id: "A-1" })-[:SENT*1..2]->(other)
RETURN DISTINCT other.name, other.city;

// Detect a cycle of length 3 (potential laundering ring)
MATCH p = (a:Agent)-[:SENT]->(b)-[:SENT]->(c)-[:SENT]->(a)
RETURN a.id, b.id, c.id, length(p);

We can simulate a graph in SQLite using the recursive CTE that we learned earlier — exactly what relational engines do internally for shallow graph queries:

graph_in_sqlite.sql

CREATE TABLE agent(id TEXT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE sent(src TEXT, dst TEXT, amt INTEGER);

INSERT INTO agent VALUES
 ('A-1','Rahim','Dhaka'),
 ('A-2','Karim','Khulna'),
 ('A-3','Sumi', 'Sylhet'),
 ('A-4','Tanvir','Dhaka');

INSERT INTO sent VALUES
 ('A-1','A-2',5000),
 ('A-2','A-3',4500),
 ('A-3','A-1',4000),
 ('A-2','A-4',2000);

-- 2-hop reachability from A-1 (friend-of-friend)
WITH RECURSIVE reach(node, depth) AS (
    SELECT 'A-1', 0
    UNION
    SELECT s.dst, r.depth+1
    FROM reach r JOIN sent s ON s.src = r.node
    WHERE r.depth < 2
)
SELECT DISTINCT a.name, a.city, MIN(r.depth) AS hops
FROM   reach r JOIN agent a ON a.id = r.node
WHERE  r.depth > 0
GROUP BY a.id;

6. When NoSQL Beats SQL — and When It Doesn't

Despite the marketing, NoSQL is not a strict upgrade over SQL — it is a different set of trade-offs. The honest engineering answer is that most apps should still start with PostgreSQL or MySQL; move only the parts of the workload that genuinely need NoSQL into NoSQL.

✅ NoSQL wins when…

The workload is already key-shaped (cache, session, OTP)
Petabytes of mostly-flat data with no joins (event logs, IoT)
Document shapes drift faster than schemas can keep up
Native graph traversal > 3 hops
Need linear horizontal scale without sharding the application

⚠️ SQL still wins when…

You need real ACID transactions across multiple records
Ad-hoc analytics with joins, aggregates, window functions
Strong referential integrity is part of the domain (money, identity)
Data fits comfortably on one beefy machine — most apps do
You want a single, well-understood query language for everyone

One more thing: PostgreSQL today already has JSONB, range types, full-text search, geographic data via PostGIS, and even a graph extension (Apache AGE). For many "NoSQL" problems Postgres is the answer. Don't reach for a separate database until you have measured a real bottleneck.

NoSQL মানে SQL-এর "upgrade" না — বরং ভিন্ন trade-off। সৎ engineering উত্তর: বেশিরভাগ app PostgreSQL/MySQL দিয়েই শুরু করুন; workload-এর যে অংশ সত্যিই NoSQL দাবি করে শুধু সেই অংশ NoSQL-এ সরান। আজকের PostgreSQL-এ JSONB, full-text search, PostGIS, এমনকি Apache AGE দিয়ে graph — সবই আছে। আগে measure করুন, তারপর নতুন database আনুন।

7. Multi-Model Databases — One Engine, Many Shapes

Picking one of the four families is a binary choice; multi-model databases try to dodge it. ArangoDB speaks document, graph and key-value in one engine. CosmosDB on Azure exposes Mongo-API, Cassandra-API, Gremlin-API and SQL-API on the same underlying store. PostgreSQL with JSONB plus pgvector plus PostGIS is, for many practical purposes, a multi-model database too. The promise: one operational footprint, fewer cross-database joins, easier consistency. The catch: each model is usually a touch less optimised than a specialist would be.

Engine	Models supported	Notes
PostgreSQL	relational + JSONB + key-value (hstore) + graph (AGE) + vector (pgvector)	The "Swiss army knife" — a default safe bet.
ArangoDB	document + graph + key-value	Native multi-model; one query language (AQL).
Azure Cosmos DB	document + key-value + wide-column + graph	Multi-API on top of one storage layer.
OrientDB / FaunaDB	document + graph	Smaller niches.

8. Practice Problems

For each scenario, name the family (KV / Document / Wide-Column / Graph) and one real product. Where useful we provide a runnable SQLite simulation.

Scenario: Cache the JWT session for every active bKash user, expiring after 1 hour.

প্রতিটি active bKash user-এর JWT session ১ ঘণ্টা পর expire করে cache করতে হবে।

✨ Show Answer

Family: Key-Value. Product: Redis (with TTL via SET … EX 3600) or DynamoDB with TTL attribute. Sub-ms latency, exactly the right shape.
Scenario: Store 50 million product catalog entries where each product has wildly different attributes (sarees vs phones vs tools).

৫ কোটি product catalog — প্রতিটির attribute আলাদা (saree, phone, tool)।

✨ Show Answer

Family: Document. Product: MongoDB or Couchbase. Or PostgreSQL with JSONB if you also need joins to orders / inventory in the same database.
Scenario: Time-series of 10 million IoT sensor readings per day from smart meters across the country, queried mostly as "show me the last 24 hours for meter X".

প্রতিদিন ১ কোটি smart meter reading; সাধারণত query "এই meter-এর গত ২৪ ঘণ্টার data"।

✨ Show Answer

Family: Wide-Column or specialised time-series. Products: Cassandra/ScyllaDB (partition key = meter_id, clustering key = ts DESC), TimescaleDB (a PostgreSQL extension), or InfluxDB.
Scenario: Detect rings of 3-7 fraudulent bKash agents who all transfer money in a closed loop.

বন্ধ chain-এ ৩-৭ জন bKash agent টাকা ঘোরাচ্ছে — এমন ring detect করা।

✨ Show Answer

Family: Graph. Product: Neo4j or AWS Neptune. The query is a single Cypher pattern MATCH p = (a)-[:SENT*3..7]->(a) RETURN p; equivalent SQL needs deeply recursive CTEs and gets expensive past 4-5 hops.
Using the JSON1 functions in SQLite, write a query that returns all riders whose vehicle CC is greater than 130.

SQLite-এর JSON1 ব্যবহার করে এমন rider বের করুন যাদের vehicle CC > ১৩০।
✨ Show Answer
ans5.sql
```
SELECT id,
       json_extract(doc,'$.name') AS name,
       json_extract(doc,'$.vehicle.cc') AS cc
FROM r
WHERE json_extract(doc,'$.vehicle.cc') > 130;
```
Why does Cassandra forbid WHERE city = 'Dhaka' on a non-PK column unless you add ALLOW FILTERING?

Cassandra-তে non-PK column-এ filter করতে গেলে কেন ALLOW FILTERING দরকার হয়?

✨ Show Answer

Answer: Without the partition key, Cassandra has no way to know which node owns the matching rows — the query would have to fan out to every node and stream every partition's worth of rows back. ALLOW FILTERING is the database explicitly making you sign that you know it is doing a cluster-wide scan. In production it is almost always a sign of a bad table design.
Write a Redis sequence (no SQLite needed) that records that user 42 viewed product 99, and shows the most recent 5 products user 42 viewed.

Redis-এ user 42 product 99 দেখেছে — record করুন এবং সর্বশেষ ৫টি দেখা product দেখান।
✨ Show Answer
```
LPUSH viewed:42 99
LTRIM viewed:42 0 49        # keep only last 50
LRANGE viewed:42 0 4        # most recent 5
                        
```
LPUSH prepends; LTRIM caps the list size so it doesn't grow forever; LRANGE reads the head.
In Cassandra you design a table that stores chat messages. Pick a partition key and clustering key for "load the last 50 messages of conversation X".

Cassandra-তে chat messages-এর জন্য partition key ও clustering key বাছাই করুন — যাতে "conversation X-এর শেষ ৫০টি message" দ্রুত আসে।

✨ Show Answer

Answer: Partition key = conversation_id; clustering key = ts DESC. Then SELECT … WHERE conversation_id = 'C-9' LIMIT 50 hits exactly one partition, walks the clustering index in already-sorted order, and stops after 50 rows. Sub-millisecond per query, scales to millions of conversations across many nodes.
List two scenarios where PostgreSQL is good enough that you would choose it over MongoDB, and one where MongoDB clearly wins.

দুটি scenario বলুন যেখানে PostgreSQL MongoDB-র চেয়ে ভালো এবং একটি যেখানে MongoDB-ই স্পষ্ট winner।

✨ Show Answer

Postgres wins: (1) any application with strong relational invariants (orders ↔ customers ↔ payments) — referential integrity and ACID across many tables. (2) Ad-hoc analytics — joins, window functions, CTEs are first-class. Mongo wins: a fast-moving product catalog or content management system where each item has wildly different shapes, and the team iterates on the schema weekly.
Use a recursive CTE in SQLite to count the number of agents reachable from A-1 within at most 3 hops in the graph from §5.

SQLite-এর recursive CTE দিয়ে §5-এর graph-এ A-1 থেকে ৩ hop-এর মধ্যে কতজন agent reachable — সেটি বের করুন।
✨ Show Answer
ans10.sql
```
WITH RECURSIVE reach(node, depth) AS (
    SELECT 'A-1', 0
    UNION
    SELECT s.dst, r.depth+1
    FROM reach r JOIN sent s ON s.src = r.node
    WHERE r.depth < 3
)
SELECT COUNT(DISTINCT node) - 1 AS others_reachable
FROM reach;
```
Define "schema-on-read" vs "schema-on-write" and say which family belongs to which.

"Schema-on-read" বনাম "schema-on-write" — সংজ্ঞা দিন এবং কোন family কোনটি বলুন।

✨ Show Answer

Answer: Schema-on-write: the engine validates every record against the declared schema at insert time — relational, Cassandra (CQL has a schema), Spanner. Schema-on-read: the engine accepts arbitrary data, the application interprets shapes at read time — most document stores by default, raw key-value, data lakes. The trade-off is when you want to pay: at write or at read.
A startup says: "we will use Cassandra so we can run any SQL we like". What is wrong with that sentence?

এক startup বললো — "আমরা Cassandra ব্যবহার করবো যাতে যেকোনো SQL চালাতে পারি।" বাক্যটিতে ভুল কী?

✨ Show Answer

Answer: CQL looks like SQL but only allows queries that fit the table's partition / clustering keys. Arbitrary joins, ad-hoc filters, GROUP BY across the cluster — none of these are supported (or they require ALLOW FILTERING with a heavy cost). Cassandra forces you to design a table per query pattern. If the startup wants ad-hoc SQL, they want PostgreSQL or a real data warehouse — not Cassandra.

Summary — Module 38

NoSQL is not one thing — it is four shapes. Key-value stores (Redis, DynamoDB) optimise for the smallest possible API and the fastest possible point lookups. Document stores (MongoDB, Couchbase) treat each record as a queryable JSON tree. Wide-column stores (Cassandra, ScyllaDB, HBase, Bigtable) distribute sparse tables over many nodes with partition + clustering keys. Graph databases (Neo4j, Neptune) make multi-hop traversal a first-class operation. None of them replaces SQL — they augment it. The mature engineering instinct is to start with PostgreSQL for almost everything and add a NoSQL layer only when measurement proves it is needed.

NoSQL মানে এক জিনিস না — চারটি ভিন্ন family। Key-value দ্রুততম point lookup-এর জন্য (Redis, DynamoDB)। Document JSON tree query-র জন্য (MongoDB)। Wide-column বহু-node-এ sparse table-এর জন্য (Cassandra)। Graph multi-hop traversal-এর জন্য (Neo4j)। কোনোটিই SQL-এর "replacement" না — সবগুলো একসাথে কাজ করে। Mature engineer-এর শুরু PostgreSQL দিয়ে; measure করে যে অংশ চাহিদা দেখায়, শুধু সেই অংশে NoSQL আনে।

Next Module → Security, Backups & Real-World Operations — production database engineering.

Previous Next: Security, Backups & Ops