When to trust a relational database — and when to walk away

1 The strengths of SQL / relational databases

A relational database stores data in tables: rows (records) and columns (fields), like a spreadsheet. You talk to it using SQL (Structured Query Language). The big-name examples are PostgreSQL, MySQL, Oracle, and SQL Server. Their power comes from four pillars: ACID transactions, normalization, a fixed schema, and joins backed by indexes. Let's take each one.

Pillar 1 — ACID transactions

A transaction is a group of operations that must succeed or fail as a single unit. ACID is the promise the database makes about them:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- debit Alice
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- credit Bob
COMMIT;

Transaction 1: Khushboo -- $1M --> Vishudh   Transaction 2: Khushboo -- $1M --> Nandani
  balance = get_balance(Khushboo)             balance = get_balance(Khushboo)
  assert balance >= $1M                        assert balance >= $1M
  new = balance - $1M                          new = balance - $1M
  set_balance(Khushboo, new)                   set_balance(Khushboo, new)
  rb = get_balance(Vishudh)                    rb = get_balance(Nandani)
  set_balance(Vishudh, rb + $1M)               set_balance(Nandani, rb + $1M)

Khushboo started with $1M
  T1: steps 1..3
  T2: steps 1..3
  T1: step 4   --> Khushboo's new balance = 0
  T2: step 4   --> Khushboo's new balance = 0   (overwrites!)
  T1: steps 5..6
  T2: steps 5..6

Pillar 2 — Normalization

Normalization means storing each fact exactly once, in one place, and linking to it instead of copying it. You split data into focused tables and connect them with foreign keys (a column that points at another table's primary key).

Pillar 3 — Fixed schema

A schema is the blueprint: which tables exist, what columns they have, what type each column is, and what rules apply. In SQL the schema is fixed and enforced — the database rejects anything that doesn't fit (a letter in a number column, a missing required field). It's a strict contract, which makes the data predictable and safe to rely on.

Pillar 4 — Joins (backed by indexes)

Because data is split across normalized tables, you need a way to stitch it back together. A join combines rows from two tables wherever a column matches. To do this fast, the database uses an index — a sorted lookup structure (usually a B-tree) that finds matching rows in roughly O(log n) time instead of scanning every row.

CREATE TABLE customers (
  id     SERIAL PRIMARY KEY,
  name   TEXT NOT NULL,
  email  TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
  id           SERIAL PRIMARY KEY,
  customer_id  INT REFERENCES customers(id),  -- foreign key
  total_cents  INT NOT NULL,
  created_at   TIMESTAMPTZ DEFAULT now()
);

-- index so the join below is fast
CREATE INDEX idx_orders_customer ON orders(customer_id);

SELECT c.name, SUM(o.total_cents) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY spent DESC;

More strengths — and one more thing about the schema

Beyond the four pillars, the fixed schema is well-defined (you know exactly which tables exist, which columns, and each column's data type — which lets the DB allocate a fixed amount of space per row on disk), static (it doesn't change row-to-row, and changing it is discouraged and hard), and enforced (a row that violates the schema is rejected — the write won't succeed).

Relational DBs also have an enormous feature set: powerful querying (joins, filtering by column, aggregate calculations, grouping, nested queries, recursive queries via CTEs) plus extras like full-text search, geospatial queries, and JSON. They offer pretty much every feature any other database in the world provides — just at low scale. And they are extremely mature: relational database theory predates modern computer hardware, and they've been battle-tested across millions of companies and thousands of scenarios.

2 Why sharding breaks SQL's benefits

Recall from earlier sessions that a single database server eventually hits a ceiling — too much data, too many requests. Sharding (also called horizontal partitioning) is the fix: split the rows of a table across multiple machines, called shards, so each shard holds a slice of the data and handles a slice of the load. The trouble is that the four SQL pillars from Topic 1 quietly assumed all the data lives in one place. Spread it out and they crack.

What breaks, and why

SELECT c.name, SUM(o.total_cents)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

Why ACID needs a single server

SQL databases give ACID guarantees only within a single server, because everything ACID needs is easy there but hard once you spread out:

Providing ACID guarantees across shards is possible — but very slow and very complex. In short: sharding nullifies ACID.

Strong schema breaks too: the Amazon product-listing problem

What if the data is inherently unstructured? Consider modeling product listings for Amazon: 10 million+ products across 10,000+ categories, where every category has a different attribute set:

• T-shirts: Brand, Price, Color, Fabric, Neck-shape, Sleeve length
• Laptops: Brand, Price, Screen Size, RAM, CPU, GPU, OS
• Notebooks: Brand, Price, Page thickness, Number of Pages, Ruled

There are four ways to force this into SQL, and all of them hurt at scale:

Approach 1 — one giant table with all columns.

products: id, name, brand, price, Color, Fabric, Neck-shape, Sleeve length,
          Screen Size, RAM, CPU, GPU, OS, Page thickness, Number of Pages, Ruled, ...

10,000 categories × ~10 unique cols each ⇒ ~100,000 columns. Every row only fills ~10 of them; the other 100,000 are NULL. And NULL is itself bad because it's ambiguous — it can mean missing data (the value exists, we just don't know it) or undefined (what's the page-thickness of a t-shirt?). Worse, SQL pre-allocates space for the entire row including the NULL columns, so you waste 99.99% of the disk space.

Approach 2 — multiple tables, one per product type.

products:   id, name, brand, price
tshirts:    product_id, Color, Fabric, Neck-shape, Sleeve length
laptops:    product_id, Screen Size, RAM, CPU, GPU, OS
notebooks:  product_id, Page thickness, Number of Pages, Ruled
... 10,000 such tables

Now a query like "find the top 10 most expensive products that are color red" must join across 10,000 tables. SQL can handle lots of rows and terabytes of data (few tables, lots of data in them) — but it cannot handle lots of "schema" (many tables).

Approach 3 — an attribute list (EAV).

products:            id, name, brand, price
product_attributes:  product_id, attribute_name, attribute_value

Notice product 1 has both RAM and Fabric — schema enforcement is lost: a laptop can now have a fabric. You could enforce attributes in the application layer, but that breaks separation of concerns (validation now lives partly in the DB and partly in the app). Also, fetching a single entry is expensive — to show one laptop you must fetch all its attribute rows and join them into an array.

Approach 4 — JSON columns. Modern relational DBs (Postgres, MySQL) have first-class JSON support, so can't we just use that? Yes and no. It works, but it still isn't designed to handle scale.

3 SQL vs NoSQL — and the four NoSQL families

NoSQL is an umbrella term for databases that drop some relational rules to scale out across many machines more easily. They typically favour horizontal scaling (add cheap machines) over the SQL habit of vertical scaling (buy one bigger machine), and many offer eventual consistency — after a write, replicas converge to the same value "eventually" rather than instantly — in exchange for staying available. There are four main families.

SQL vs NoSQL in one breath

The four families at a glance

• Key-value — the simplest: store a value under a key and fetch it back by that key. Blazing fast, but you can only look things up by the key. Examples: Redis, DynamoDB, Riak. Great for caches, sessions, feature flags.
• Document — store self-contained documents (usually JSON). Each document can have its own shape (a flexible schema), and you can query on fields inside it. Examples: MongoDB, Couchbase, Firestore. Great for catalogs, user profiles, content.
• Wide-column — rows are identified by a key, but each row can hold a huge, sparse set of columns grouped into families; built to write and read enormous volumes across many nodes. Examples: Cassandra, HBase, Bigtable. Great for time-series, logs, write-heavy feeds.
• Graph — data is nodes (things) connected by edges (relationships); you traverse the connections directly. Examples: Neo4j, Amazon Neptune. Great for social networks, fraud rings, recommendations — anything where the relationships are the point.

-- SQL row (fixed schema)
INSERT INTO customers (name, email) VALUES ('Mia', 'mia@x.com');

// Document store (e.g. MongoDB)
{ "name": "Mia", "email": "mia@x.com" }
{ "name": "Leo", "email": "leo@x.com",
  "address": { "city": "Pune" }, "tags": ["vip"] }

# Key-value store (e.g. Redis)
SET customer:42 '{"name":"Mia","email":"mia@x.com"}'

SQL vs NoSQL — the comparison

BASE — the NoSQL counterpart to ACID

Where SQL gives ACID, NoSQL gives BASE:

• Basically Available — the system as a whole stays available, even if some services are unavailable to a small fraction of users for a short time. High availability for the whole system, not for every individual service/user.
• Soft State — unlike ACID's all-or-nothing atomicity, transactions can sit in a partial state for some time (even weeks!) — but eventually the data becomes consistent and atomic. (We'll see this in the last HLD class: distributed transactions via the Saga pattern.)
• Eventually Consistent — there may be stale reads, but eventually (if we wait long enough) every write is reflected across all replicas.

Horizontally scalable by design

SQL databases require manual sharding — they don't provide built-in sharding (most modern SQL DBs do have built-in replication, but not sharding). To shard a SQL DB you either use a 3rd-party extension, do it manually (write the LB code, manage server state), or use a managed cloud service like Amazon RDS. NoSQL DBs, by contrast, are automatically sharded: you get a bunch of servers, install the database, and it figures out load balancing, autoscaling, sharding, data distribution, replication, and fault tolerance itself.

Denormalization & replication, on purpose

SQL discourages denormalization & redundancy to remove anomalies. NoSQL embraces the opposite, because it realises that (1) the frontend is going to show denormalized data anyway, and (2) to prevent data loss you're going to keep multiple copies anyway. So NoSQL DBs encourage storing data in a denormalized, semi-structured (sometimes even schemaless) manner.

4 Choosing a sharding key

When you shard (Topic 2), you must decide: for a given row, which shard does it live on? That decision is driven by the sharding key (also called the partition key) — a column whose value is run through a function to pick a shard, e.g. shard = hash(key) % number_of_shards. The sharding key decides how data gets distributed across DB servers, and also how queries get routed to find the data. This single choice quietly decides whether your system stays fast and balanced or falls over.

Primary key vs sharding key

These are two different things that coexist:

• Primary key (PK) — uniquely identifies an item (a row). It's what you're talking about.
• Sharding key (SK) — tells you how to distribute data: what data goes to what server. It's where to find the data.

users(id, name, gender)               user_posts(id, user_id, title, content)
  A, Akshay,   Male                     1, A, Hi,       Hello World
  B, Balaji,   Male                     2, A, Bye,      Going to sleep
  C, Chandani, Female                   3, B, Wassup,   What's everyone doing
                                        4, C, Context?, Who are you guys?
user_friendship(id, user_id, friend_id, affinity)
  1, A, B, 100%   2, B, A, 100%   3, A, C, 50%   4, C, A, 50%

Shard 1 (user A)                  Shard 2 (user B)            Shard 3 (user C)
  users: A, Akshay, Male            users: B, Balaji, Male      users: C, Chandani, Female
  posts: 1,A,Hi / 2,A,Bye           posts: 3,B,Wassup           posts: 4,C,Context?
  friends: 1,A,B / 3,A,C            friends: 2,B,A              friends: 4,C,A

Now, what makes a sharding key good? Several goals guide it.

Goal 1 — Equal data & load distribution

Data and traffic should spread roughly equally across shards — all key values should be equally likely. If some values are more likely than others, you get hot shards. Examples of skew:

• Age: ages 0–5 and 50+ are less likely, 15–30 most likely, and almost nobody is 100+. Sharding by age gives poor distribution.
• Gender: depending on the app, you get hot shards — e.g. most Scaler students are male (gender disparity in higher technical studies).
• User id: some users post a lot, some post zero — but since each server houses 100,000s of users, the normal users and influencers average out, so distribution stays even. (Caveat: not always true — the celebrity problem, especially for notification systems.)

Goal 2 — High cardinality

Cardinality is the count of possible distinct values. It doesn't define how many servers you have, but it caps how many you can ever scale to:

• Age {0..123} → ~124 possibilities max. With 125 servers, one gets nothing — you're limited to ~124 shards.
• Gender {Male, Female, LGBTQ+} → 3 possibilities. With 4 servers, one is idle; max scaling is 3 shards, even with a billion users.
• User_Id (64-bit) → ~16 quintillion possibilities. No practical limit — 10 million servers? No problem. (We won't actually have quintillions of servers; one server houses millions of users.)

Goal 3 — Avoid hotspots & support your common queries (no fan-outs)

A hotspot is one shard getting far more traffic than the others, becoming a bottleneck while the rest sit idle. Relatedly, the SK should be part of every read/write request — otherwise you can't route, and you fan-out to every shard. The fastest query hits a single shard; the rule is that the most frequent queries should hit only 1 (at most 2) shards, and no frequent query should fan out. (Rare queries are okay to fan out — you can't optimize everything.)

SELECT * FROM user_profiles WHERE user_id = 1234;

SELECT * FROM user_profiles
WHERE user_id = 1234 AND gender = 'MALE';

Goal 4 — Immutable

The SK value should never change for a row. If it changes, you must re-shuffle that row to a different server. Example: age — what happens on the user's birthday? The age changes, so you'd have to move them to a different server. (Timestamps have the same problem, plus they create write hotspots — see below.)

-- every brand-new post has "now" as its key →
-- all writes hammer the single newest shard = HOTSPOT.
-- and "this user's posts" are scattered by time across ALL shards.
shard = hash(created_at) % N   -- ✗ skewed writes, scatter-gather reads

-- millions of distinct users → high cardinality → even spread.
-- one user's posts all land on ONE shard → single-shard read.
shard = hash(user_id) % N      -- ✓ balanced writes, fast reads

Composite keys — getting both balance and locality

Sometimes one column isn't enough. A composite key combines columns, commonly a partition part (decides the shard) and a sort part (orders rows within that shard). This is the heart of wide-column stores like Cassandra and DynamoDB.

-- partition by chat_id (spreads chats across shards),
-- sort by created_at (messages within a chat stay ordered).
PARTITION KEY = chat_id
SORT KEY      = created_at

5 Choosing a sharding key — worked examples

The same theory applied to four real systems. For each, list the most frequent operations first, then test candidate keys against the four goals.

Example 1 — Banking system

Users can have active bank accounts across cities. Most frequent operations:

• Balance query (user_id, account_id)
• Fetch transaction history (user_id, account_id, date_range)
• Fetch a user's list of accounts (user_id)
• Create transaction (sender_id, receiver_id, amount) — note sender_id and receiver_id are composite (user_id, account_id)

Example 2 — IRCTC (Indian railway ticket booking)

Requirements: prevent double-booking of seats (at least for confirmed, non-RAC tickets), and handle peak Tatkal load (20×+ the average). Core call:

book_ticket(user_id, train_id, date_of_journey, class,
            seat_preference, meal_preference, ...) => ticket: {id, details}

Example 3 — Facebook Messenger (1-1 chat)

Operations: sendMessage(sender_id, recipient_id, message) → Ack/Failure and viewMessages(user_id, other_person_id) → list of messages.

• user_id ✓ — all messages Sanjana sends and receives live in her shard. To view her conversation with Sachin, only her shard is hit. A "Sanjana → Sachin" message is stored in both Sanjana's and Sachin's shards — that's 2 shards, not a fan-out.
• message_id — created in the response (not in the request). Even if generated client-side, sharding by it scatters messages across all servers, so "all messages Sanjana sent Sachin" becomes a fan-out.

Example 4 — Slack (1-1 and group chat)

Groups can have up to 100,000 participants. Consider an "Announcements" group of 100,000 people where Sanjana sends "Hi".

6 How storage works — sequential vs random access

Storage comes in layers — Magnetic (HDD), Solid State (SSD), and RAM — each faster and pricier than the last (see the videos in References). The key idea spans all of them: sequential access is far faster than random access. Let's prove it with HDD math.

7200 rpm = 7200/60 = 120 rotations/sec
time for 1 rotation = 1 / 120 s = ~8 ms
time to move the spindle (read head, "seek") = ~10 ms

10 ms  to move the spindle to the correct track
 8 ms  to spin the disk once
----
bandwidth = 100 MB / 18 ms = 100 MB / 0.018 s = ~5.5 Gbps

10 ms  to move the spindle to the correct track
 8 ms  to spin the disk once
----
bandwidth = 4 KB / 18 ms = ~222 kbps

★ Putting it all together

This session was really one continuous story about scale and trade-offs. Here it is in a paragraph:

Quick self-check

📚 References & Further Reading

Class material

• 📄 Original class notes / handout (Google Doc) — open the shared class material for this session.
• Class handout: "[SST-2028] Sql vs NoSql + Sharding Key".

Papers, docs & deep dives

• 📄 PostgreSQL docs — Transactions — how ACID transactions work in a real relational database.
• 📄 MongoDB — Data Modeling — modeling documents and denormalization in a NoSQL document store.
• 📄 NoSQL Databases Explained (MongoDB) — overview of the NoSQL families and when to use them.
• 📘 Designing Data-Intensive Applications (Kleppmann) — the definitive deep dive on replication, partitioning (sharding), and consistency.