Databases — SQL & NoSQL

1. What a Database Really Is

What you're really paying a database to do

• Durability — once it says "saved", it survives a power cut.
• Concurrency — hundreds of people reading and writing at once without corrupting each other.
• Fast lookup — find one row among 50 million without reading all 50 million.
• Integrity — refuse data that breaks your rules (an order with no customer).
• Querying — answer questions you didn't plan for, without rewriting your app.

2. Relational vs Non-Relational

Side by side

3. How to Actually Choose

Start relational unless you have a reason not to

This is the honest default, and a strong interview answer. Postgres handles JSON, scales further than most apps ever need, and gives you transactions and constraints for free. Most "we need NoSQL" decisions are made before the app has any users.

Questions that actually decide it

Using both is normal

Postgres  → users, orders, payments      (needs correctness and joins)
MongoDB   → product catalogue             (every category has different fields)
Redis     → sessions, cache, rate limits  (speed, short-lived)
Elastic   → full-text search              (searching descriptions properly)

This is called polyglot persistence — using the right store for each job. The cost is more systems to run and back up, so don't do it until one database genuinely stops fitting.

4. Tables, Rows & Columns

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,       -- auto-numbering unique id
  name       VARCHAR(100) NOT NULL,    -- must have a value
  email      VARCHAR(255) UNIQUE NOT NULL,
  age        INTEGER CHECK (age >= 0),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Why the fixed structure helps

• The database rejects bad data — you can't save a user with no email.
• Queries are predictable — every row definitely has that column.
• Storage is compact — the shape is stored once, not on every record.
• The structure documents itself — reading the table tells you what a user is.

And where it hurts

Adding a column to a table with 100 million rows can lock it. Storing things with genuinely different shapes means either lots of empty columns or many small tables. This is exactly the pain document databases were built to remove.

5. Keys & Relationships

CREATE TABLE orders (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total   NUMERIC(10,2) NOT NULL
);

REFERENCES users(id) tells the database: this must match a real user. Try to insert an order for user 999 who doesn't exist, and it refuses. That's referential integrity — the database protecting you from orphaned data.

ON DELETE options

The three relationship shapes

6. Normalization

-- ✗ Not normalized: the same facts repeated on every row
orders: id | customer_name | customer_email | customer_city | product | price
        1  | Ada           | a@b.com        | London        | Laptop  | 999
        2  | Ada           | a@b.com        | London        | Mouse   | 25

-- ✓ Normalized: each fact lives once
customers: id | name | email   | city
           1  | Ada  | a@b.com | London
orders:    id | customer_id | product | price
           1  | 1           | Laptop  | 999
           2  | 1           | Mouse   | 25

The forms, in plain terms

• 1NF — no lists crammed into one cell. "red,blue,green" in a column should be three rows.
• 2NF — every column depends on the whole key, not part of it.
• 3NF — no column depends on another non-key column. Storing city and country where country is determined by city breaks this.

In practice: aim for 3NF and stop. The higher forms rarely change a real design.

When to deliberately break it

Denormalization means copying data on purpose, to avoid a join. It's a trade: faster reads, but now you must keep the copies in sync.

-- Store a running count instead of counting rows every time
posts: id | title | comment_count

-- Fast to read. But every new comment must update it,
-- and if that update fails the number is silently wrong.

7. SQL Basics

SELECT name, email FROM users WHERE age > 18 ORDER BY name LIMIT 10;

INSERT INTO users (name, email) VALUES ('Ada', 'a@b.com') RETURNING id;

UPDATE users SET name = 'Ada L' WHERE id = 1;

DELETE FROM users WHERE id = 1;

Filtering

WHERE age BETWEEN 18 AND 65
WHERE city IN ('London', 'Pune')
WHERE name LIKE 'A%'              -- starts with A
WHERE email IS NULL               -- NOT "= NULL" — that never matches
WHERE active = true AND (role = 'admin' OR verified = true)

NULL behaves strangely — and it's a classic question

NULL = NULL        → NULL (not true!)
NULL + 5           → NULL
WHERE x != 'a'     → skips rows where x is NULL

-- Use IS / IS NOT
WHERE x IS NULL
WHERE x IS DISTINCT FROM 'a'      -- treats NULL as a real value
COALESCE(x, 'default')            -- replace NULL with something

NULL doesn't mean zero or empty — it means unknown. Is an unknown value equal to another unknown value? The database refuses to guess, so the answer is also unknown.

Execution order — why an alias fails in WHERE

-- ✗ fails: total_price doesn't exist yet at WHERE
SELECT price * qty AS total_price FROM items WHERE total_price > 100;

-- ✓ repeat the expression, or use a subquery
SELECT price * qty AS total_price FROM items WHERE price * qty > 100;

-- ✓ ORDER BY runs after SELECT, so the alias is fine there
SELECT price * qty AS total_price FROM items ORDER BY total_price DESC;

8. Joins

-- Only users who have placed orders
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- Every user, including those with none (their total is NULL)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Find users with NO orders — a very common pattern
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

The mistake that silently changes your results

-- ✗ this turns a LEFT JOIN back into an INNER JOIN
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';        -- NULL rows fail this test and disappear

-- ✓ put the condition in the JOIN instead
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';

Filtering the right-hand table in WHERE removes the NULL rows that LEFT JOIN just carefully kept. Conditions on the joined table belong in ON; conditions on the main table belong in WHERE.

Self join

-- Employees and their managers, from one table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

9. Grouping & Aggregates

SELECT COUNT(*), SUM(total), AVG(total), MIN(total), MAX(total) FROM orders;

-- One row per user
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS spent
FROM orders
GROUP BY user_id;

WHERE vs HAVING

SELECT user_id, SUM(total) AS spent
FROM orders
WHERE created_at > '2024-01-01'    -- filters ROWS, before grouping
GROUP BY user_id
HAVING SUM(total) > 1000;           -- filters GROUPS, after grouping

Look back at the execution order diagram in section 7: WHERE runs at step 3 (before grouping), HAVING at step 5 (after). That's why you can't use an aggregate in WHERE — the groups don't exist yet.

The COUNT trap

COUNT(*)           -- counts rows
COUNT(column)      -- counts rows where that column is NOT NULL
COUNT(DISTINCT x)  -- counts unique values

In a LEFT JOIN, COUNT(*) gives 1 for a user with no orders (their NULL row still counts). COUNT(o.id) correctly gives 0.

Window functions — aggregates that keep the rows

-- Each row keeps its detail AND gets a running total
SELECT name, total,
       SUM(total) OVER (ORDER BY created_at) AS running_total,
       RANK()     OVER (ORDER BY total DESC) AS rank
FROM orders;

-- Top 3 per category, without a messy subquery
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) t WHERE rn <= 3;

GROUP BY collapses rows; a window function calculates across a set of rows but leaves every row intact. Worth knowing — it's a common mid-level interview topic.

10. Subqueries & CTEs

-- Subquery: a query inside a query
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 500);

-- CTE (WITH): the same thing, but readable
WITH big_spenders AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, b.spent
FROM users u
JOIN big_spenders b ON b.user_id = u.id
ORDER BY b.spent DESC;

EXISTS is usually the better check

-- EXISTS stops at the first match — often faster than IN
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- NOT IN is dangerous with NULLs: if the subquery returns ANY null,
-- the whole thing returns no rows. NOT EXISTS is safe.
SELECT name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Recursive CTEs — for tree-shaped data

-- Every employee under a manager, however deep
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;

This is one thing relational databases do genuinely well that document databases struggle with — walking a hierarchy of unknown depth in a single query.

11. Constraints & Data Types

CREATE TABLE products (
  id       SERIAL PRIMARY KEY,
  sku      VARCHAR(50) UNIQUE NOT NULL,
  price    NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock    INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  status   VARCHAR(20) NOT NULL DEFAULT 'draft'
           CHECK (status IN ('draft','live','archived'))
);

A UNIQUE constraint is also how you avoid a race

-- ✗ two simultaneous signups can both pass this check
const exists = await db.query("SELECT 1 FROM users WHERE email = $1", [email]);
if (!exists) await db.query("INSERT INTO users …");

-- ✓ let the database decide, and handle the error
try { await db.query("INSERT INTO users …"); }
catch (e) { if (e.code === "23505") return res.status(409).json({ error: "Email taken" }); }

Check-then-insert always has a gap between the two steps. A UNIQUE constraint has no gap — the database guarantees it.

Types worth getting right

12. The Four Types

What they share

• No fixed schema — records don't have to match each other.
• Built to spread across machines from the start, rather than as an afterthought.
• Fewer guarantees by default — often trading strict correctness for speed and availability.
• No standard language — what you learn for MongoDB doesn't transfer to Cassandra.

13. Document Databases

{
  _id: ObjectId("..."),
  name: "Ada",
  email: "a@b.com",
  addresses: [                        // nested array, no second table
    { type: "home", city: "London" },
    { type: "work", city: "Pune" }
  ],
  preferences: { theme: "dark" }      // nested object
}

Where the flexibility genuinely helps

// A product catalogue where every category has different attributes
{ name: "T-shirt", size: "M", colour: "blue" }
{ name: "Laptop",  ram: "16GB", cpu: "M3", screen: 14 }
{ name: "Book",    author: "…", pages: 320, isbn: "…" }

In a table you'd need either dozens of mostly-empty columns, or a separate table per category, or a messy attribute-value table. Here it's natural.

Where it hurts

• Nothing stops bad data. A typo in a field name creates a new field instead of an error. You need validation in your app — or MongoDB's schema validation, which most people don't enable.
• Joins are awkward. $lookup exists but is slower and clumsier than a SQL join.
• Duplicated data drifts. If you copy a product name into 10,000 orders, renaming the product means updating 10,000 documents.
• Unplanned queries are harder — you design the shape around how you'll read it, so a new question may not fit.

14. Embed vs Reference — the central decision

The decision rules

// ✓ Embed — addresses belong to this user and are read with them
{ name: "Ada", addresses: [{ city: "London" }] }

// ✗ Don't embed — comments grow forever and hit the 16MB document limit
{ title: "Post", comments: [ …50,000 of them… ] }

// ✓ Reference instead
posts:    { _id: 1, title: "Post" }
comments: { postId: 1, text: "…" }

// ✓ The hybrid — embed a copy of what you display, reference for the rest
{ postId: 1, author: { id: 7, name: "Ada" }, text: "…" }
// Shows the name without a second query. If Ada renames herself,
// old comments keep the old name — which for a comment is often correct.

15. MongoDB in Practice

db.users.insertOne({ name: "Ada", age: 36 });
db.users.find({ age: { $gt: 30 } });
db.users.updateOne({ _id: id }, { $set: { name: "Ada L" } });
db.users.deleteOne({ _id: id });

Query operators

{ age: { $gt: 30, $lte: 65 } }            // greater than, less than or equal
{ city: { $in: ["London", "Pune"] } }
{ name: { $regex: /^A/i } }                // starts with A, case-insensitive
{ $and: [{ active: true }, { age: { $gt: 18 } }] }
{ "address.city": "London" }               // reach into nested fields
{ tags: "urgent" }                         // matches if the ARRAY contains it

Update operators — never replace the whole document

{ $set:   { name: "Ada" } }        // change a field
{ $inc:   { views: 1 } }            // add to a number, atomically
{ $push:  { tags: "new" } }         // append to an array
{ $pull:  { tags: "old" } }         // remove from an array
{ $unset: { temp: "" } }            // delete a field

The aggregation pipeline

MongoDB's answer to GROUP BY and joins. Data flows through stages, each transforming what the last produced.

db.orders.aggregate([
  { $match:  { status: "paid" } },                        // like WHERE — put it first
  { $group:  { _id: "$userId", total: { $sum: "$amount" } } },  // like GROUP BY
  { $sort:   { total: -1 } },                             // like ORDER BY
  { $limit:  10 },
  { $lookup: {                                            // like a LEFT JOIN
      from: "users", localField: "_id",
      foreignField: "_id", as: "user"
  }}
]);

Put $match as early as possible. Filtering 1,000 documents then grouping is far cheaper than grouping a million and filtering after — and it lets the stage use an index.

Transactions exist now

const session = client.startSession();
await session.withTransaction(async () => {
  await accounts.updateOne({ _id: a }, { $inc: { balance: -500 } }, { session });
  await accounts.updateOne({ _id: b }, { $inc: { balance:  500 } }, { session });
});

MongoDB has supported multi-document transactions since 4.0. The old "NoSQL has no transactions" line is out of date — but they're slower than in Postgres, and needing them often means the data should have been embedded together.

Schema validation — worth turning on

db.createCollection("users", {
  validator: { $jsonSchema: {
    required: ["email"],
    properties: { email: { bsonType: "string", pattern: "@" } }
  }}
});

Flexible doesn't have to mean unchecked. Most teams that regret MongoDB regret not enabling this.

16. Key-Value & Redis

SET user:1 "{\"name\":\"Ada\"}"
GET user:1
SET session:abc "userId=7" EX 3600     # expires automatically after an hour
DEL user:1
INCR page:views                         # atomic — safe with many clients

What Redis is actually used for

# Rate limiting in two commands
INCR    rate:user:7
EXPIRE  rate:user:7 60        # if the count exceeds your limit, reject

# A lock only one process can hold
SET lock:job "worker1" NX EX 30

The thing to remember

Memory is limited and not permanent. Redis can save to disk, but it's designed for data you can afford to lose — a cache you can rebuild, a session someone can re-create by logging in. Never make it the only copy of something important.

17. Wide-Column & Graph

Wide-column (Cassandra)

-- You design the table around ONE query pattern
CREATE TABLE messages_by_room (
  room_id   UUID,
  sent_at   TIMESTAMP,
  message   TEXT,
  PRIMARY KEY (room_id, sent_at)     -- partition key, then clustering key
) WITH CLUSTERING ORDER BY (sent_at DESC);

The partition key decides which machine the row lives on; the clustering key decides the order within it. Get the partition key wrong and one machine takes all the traffic — the "hot partition" problem.

It's normal here to store the same data several times, once per query pattern. That would be a design error in SQL; here it's the intended approach.

Graph (Neo4j)

// Friends of friends, two hops out
MATCH (me:Person {name: "Ada"})-[:FRIEND]->()-[:FRIEND]->(fof)
WHERE NOT (me)-[:FRIEND]->(fof)
RETURN fof.name;

In SQL that's a self-join per hop, and it gets painful fast. Six degrees of separation would be six joins. A graph database walks the connections directly, so depth barely costs anything.

18. Indexes

-- SQL
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_sku ON products(sku);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);  -- composite

-- MongoDB
db.users.createIndex({ email: 1 });
db.orders.createIndex({ userId: 1, createdAt: -1 });

What to index

• Columns in WHERE, JOIN conditions and ORDER BY.
• Every foreign key — most databases don't index these automatically, and it makes joins slow.
• Anything you look up by: email, username, slug.

Why not index everything

Every index must be updated on every write, and takes disk and memory. Five indexes means an insert does six writes. It's a straight trade: faster reads, slower writes.

Composite indexes and the leftmost rule

CREATE INDEX idx ON orders(user_id, status, created_at);

WHERE user_id = 1                                  ✓ uses it
WHERE user_id = 1 AND status = 'paid'              ✓ uses it
WHERE user_id = 1 AND status = 'paid' AND created_at > …  ✓ fully
WHERE status = 'paid'                              ✗ can't — skipped the first column

Think of a phone book sorted by (surname, first name). You can find "Smith", and "Smith, John" — but not everyone called "John", because the sorting starts with surname.

Things that stop an index working

WHERE LOWER(email) = 'a@b.com'     ✗ function on the column
WHERE email = 'a@b.com'             ✓
-- or build the index to match: CREATE INDEX ON users(LOWER(email));

WHERE name LIKE '%ada%'             ✗ leading wildcard — can't use sort order
WHERE name LIKE 'ada%'              ✓

19. Transactions & ACID

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;        -- or ROLLBACK, and neither change happened

ACID

How durability actually works

Before changing the data, the database writes what it's about to do to a write-ahead log. If the power fails mid-write, it reads the log on restart and finishes or undoes the work. That's also what makes replication possible — the log can be streamed to another server.

Rules for using them

• Keep them short. A transaction holds locks; long ones block other users.
• Never call an external API inside one. You'd hold a database lock while waiting on someone else's server.
• Always release the connection in a finally block, whatever happens.

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE …");
  await client.query("UPDATE …");
  await client.query("COMMIT");
} catch (e) {
  await client.query("ROLLBACK");
  throw e;
} finally {
  client.release();          // ALWAYS
}

20. Isolation & Race Conditions

Three ways to fix it

-- 1. Atomic update — the database does the check. Simplest, use when you can.
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
-- if 0 rows were affected, it was out of stock

-- 2. Pessimistic lock — claim the row, make others wait
BEGIN;
  SELECT * FROM products WHERE id = 1 FOR UPDATE;   -- others block here
  UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 3. Optimistic lock — detect the clash instead of preventing it
UPDATE products SET stock = 0, version = version + 1
WHERE id = 1 AND version = 5;
-- 0 rows affected means someone else got there first → retry or show a conflict

Pessimistic suits high-contention data (concert tickets). Optimistic suits low contention and user-facing edits, where blocking would feel worse than occasionally saying "someone else changed this".

Isolation levels

• Dirty read — seeing changes another transaction hasn't committed (and may undo).
• Non-repeatable read — reading the same row twice and getting different values.
• Phantom read — running the same search twice and finding new rows.

Higher isolation means more correctness and less concurrency. Serializable behaves as if transactions ran one at a time — safest, slowest.

Deadlocks

A holds row 1 and wants row 2; B holds row 2 and wants row 1. Neither can continue. The database detects this and kills one. Prevent it by always locking rows in a consistent order (e.g. ascending id) and keeping transactions short.

21. Query Performance

EXPLAIN — ask the database what it plans to do

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';

Seq Scan on users  (cost=0.00..2341.00 rows=1 width=123)   ← reading EVERY row
  Filter: (email = 'a@b.com')

-- after CREATE INDEX:
Index Scan using idx_users_email  (cost=0.29..8.31 rows=1)  ← jumping straight there

Seq Scan on a large table is the signal to add an index. ANALYZE actually runs the query and shows real timings, so you can see where the time went.

The N+1 problem — the most common real-world bug

// 1 query for posts, then 1 MORE per post = 101 queries
const posts = await Post.find();
for (const p of posts) p.author = await User.findById(p.userId);

// ✓ SQL — let the database join
SELECT p.*, u.name FROM posts p JOIN users u ON u.id = p.user_id;

// ✓ ORM — eager load
await prisma.post.findMany({ include: { author: true } });
await Post.find().populate("author");            // Mongoose

// ✓ Or batch it yourself — 2 queries, whatever the count
const users = await User.find({ _id: { $in: posts.map(p => p.userId) } });
const byId  = new Map(users.map(u => [String(u._id), u]));
posts.forEach(p => { p.author = byId.get(String(p.userId)); });

It works fine with 10 test rows and collapses with 10,000 real ones. Log your queries in development — one page firing 200 queries is easy to spot and easy to miss.

Other things that matter

• Select only what you need. SELECT * on a table with a large text column moves far more data than you use.
• Always paginate. Any list that grows needs LIMIT.
• Keyset pagination beats OFFSET. OFFSET 100000 makes the database count through 100,000 rows to skip them.

-- ✗ gets slower the deeper you go
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 100000;

-- ✓ same speed at any depth — jumps straight in via the index
SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 20;

22. Connections & Pooling

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                       // this process will open at most 20
  idleTimeoutMillis: 30000,
});

const { rows } = await pool.query("SELECT * FROM users WHERE id = $1", [id]);

In serverless environments each function invocation can open its own connection, which exhausts the limit fast. That's what connection proxies (PgBouncer, Prisma Accelerate, RDS Proxy) exist to solve.

23. Replication

Replication lag — the bug it causes

// User saves their profile → goes to the PRIMARY
await db.write("UPDATE users SET name = 'Ada L' WHERE id = 1");

// Immediately redirected, and the read hits a REPLICA
const user = await db.read("SELECT * FROM users WHERE id = 1");
// → may still show the OLD name. The user thinks the save failed.

The fix: read your own writes from the primary for a short window after a write, or wait for the replica to catch up. This is a real production bug people hit and don't understand.

Failover

If the primary dies, a replica is promoted to take its place. Managed databases do this automatically in seconds. It's the main reason to run replicas even if you don't need the read capacity.

24. Sharding & Partitioning

Shard 1: users with id 1 – 1,000,000
Shard 2: users with id 1,000,001 – 2,000,000
Shard 3: users with id 2,000,001 – 3,000,000

Choosing the shard key is the whole game

What you lose

• Joins across shards become application work, or impossible.
• Transactions across shards are slow and complicated.
• Unique constraints across shards can't be enforced by the database.
• Rebalancing when you add a shard means moving data around while live.

Partitioning — sharding's simpler cousin

-- One database, but the table is stored in separate physical chunks
CREATE TABLE logs (…) PARTITION BY RANGE (created_at);
CREATE TABLE logs_2024_01 PARTITION OF logs
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Queries that filter by date only touch the relevant partitions, and dropping last year's data becomes instant instead of a huge DELETE. All within one machine — much less painful than sharding.

25. CAP & Consistency

Eventual consistency

"Eventually consistent" means the copies will agree soon, just not instantly. For a like count or a view counter, that's completely fine. For an account balance, it isn't.

The useful question isn't "is this database ACID or eventually consistent" — it's "what happens if this particular piece of data is a few seconds stale?" Often the answer is "nothing", and then you can take the faster option.

26. Caching

// Cache-aside — the pattern you'll use most
async function getUser(id) {
  const cached = await redis.get(`user:${id}`);
  if (cached) return JSON.parse(cached);          // hit

  const user = await db.getUser(id);              // miss
  await redis.set(`user:${id}`, JSON.stringify(user), "EX", 300);
  return user;
}

The two hard parts

• Invalidation. When the row changes, the cached copy is wrong. Either delete the key on write (DEL user:7) or accept staleness until the TTL expires. Deleting is more correct; TTL is simpler and usually enough.
• Stampede. A popular key expires and 500 requests miss at once, all hitting the database together. Fix with a short lock so one request refills while the rest wait, or vary the expiry times so keys don't all die at once.

What's worth caching

Data read far more often than it changes, and expensive to produce. Caching a fast query that changes constantly adds complexity and a new class of bug for no gain.

27. Migrations & ORMs

// migrations/001_add_users.sql
CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL);

// migrations/002_add_phone.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

The tool records which have run, so each applies exactly once, in order, on every environment.

Safe migrations on a live database

• Adding a nullable column is safe and fast.
• Adding NOT NULL with a default can rewrite the whole table and lock it — add the column, backfill in batches, then add the constraint.
• Dropping a column breaks any running old code. Deploy code that stops using it first, then drop it later.
• Adding an index locks writes — use CREATE INDEX CONCURRENTLY in Postgres.
• Always write the rollback before you run the migration.

ORMs

28. Security & Backups

SQL injection — and why parameters actually work

// ✗ the input becomes part of the query text
db.query(`SELECT * FROM users WHERE email = '${email}'`);
// input: '; DROP TABLE users; --

// ✓ the value travels separately
db.query("SELECT * FROM users WHERE email = $1", [email]);

It's not a magic escape. The database parses and plans the query first, with $1 as a placeholder, and only then binds the value. There is no point at which your data could be read as a command.

NoSQL injection — the one people miss

// Attacker sends: { "email": "a@b.com", "password": { "$gt": "" } }
await User.findOne({ email, password });        // $gt matches ANY password

// ✓ check the types first
if (typeof password !== "string") return res.status(400).json({ error: "Invalid" });

Document databases take objects as queries, so an object arriving where you expected a string becomes a query operator. Validate types before querying.

Other essentials

• Least privilege — your app user should not be able to DROP TABLE.
• Encrypt in transit (TLS) and at rest.
• Never store passwords you can read back — hash with bcrypt or argon2.
• Don't log query parameters that contain personal data.
• Soft delete (deleted_at) for anything a user might want back.

Backups

pg_dump mydb > backup.sql          # logical — portable, slower to restore
# plus continuous archiving for point-in-time recovery

Interview Q&A — quick answers

Choosing and comparing

• SQL vs NoSQL? Relational stores fixed-shape rows in linked tables with enforced rules and joins; NoSQL covers flexible documents, key-value, wide-column and graph stores that trade some guarantees for flexibility and easier horizontal scaling.
• When would you pick MongoDB over Postgres? Records with genuinely different shapes, naturally nested data read as one unit, or very high write volume with simple access patterns. Not "because it scales" — Postgres scales further than most apps need.
• When would you pick Postgres? Nearly always by default: connected data, transactions that must be safe, unplanned queries and reporting. It also has JSONB if you need flexible fields.
• What is polyglot persistence? Using different databases for different jobs in one system — Postgres for orders, Redis for sessions, Elasticsearch for search.

Relational

• Primary vs foreign key? Primary uniquely identifies a row; foreign points at another table's primary key and is enforced by the database.
• What is normalization? Storing each fact once to avoid duplication and update anomalies. Aim for 3NF, then denormalize deliberately if you measure a problem.
• INNER vs LEFT JOIN? INNER keeps only matching rows; LEFT keeps all rows from the left table with NULLs where there's no match.
• Why did my LEFT JOIN behave like an INNER JOIN? A WHERE condition on the right table filters out the NULL rows. Move it into the ON clause.
• WHERE vs HAVING? WHERE filters rows before grouping; HAVING filters groups after. That's why aggregates can't appear in WHERE.
• Why can't I use a SELECT alias in WHERE? WHERE executes before SELECT, so the alias doesn't exist yet. It works in ORDER BY, which runs after.
• What's special about NULL? It means unknown, so NULL = NULL is not true. Use IS NULL, and beware NOT IN with nullable columns.
• What is a CTE? A named temporary result (WITH) that makes complex queries readable and supports recursion for tree data.
• DELETE vs TRUNCATE vs DROP? Delete rows (can be filtered, rolled back) / empty the table fast / remove the table entirely.

Non-relational

• Embed or reference? Embed when read together, owned by one parent and bounded in size. Reference when shared, unbounded, or frequently changed.
• MongoDB's document size limit? 16MB — which is why unbounded arrays like comments must be referenced, not embedded.
• Does MongoDB have transactions? Yes, multi-document since 4.0. They work but are slower; needing them often signals the data should have been modelled together.
• What is Redis used for? Caching, sessions, rate limiting, job queues, leaderboards and locks — anything where speed matters and losing the data is survivable.
• Why is a graph database better for "friends of friends"? Each extra hop is another self-join in SQL; a graph database walks the connections directly, so depth costs little.

Core concepts

• What is an index and what does it cost? A sorted structure making lookups O(log n) instead of scanning every row. It costs disk, memory, and slower writes since every index updates on every write.
• Why isn't my index being used? A function wrapped around the column, a leading wildcard in LIKE, or a composite index whose first column you skipped.
• Explain ACID. Atomicity (all or nothing), Consistency (rules hold), Isolation (no half-done work visible), Durability (committed survives a crash).
• What is a race condition, and how do you fix it? Two operations interleaving on the same data. Fix with an atomic update, a pessimistic lock (FOR UPDATE), or optimistic locking with a version column.
• Isolation levels? Read Uncommitted → Read Committed → Repeatable Read → Serializable, each preventing more anomalies at the cost of concurrency.
• What is a deadlock? Two transactions each holding what the other wants. Prevent by locking in a consistent order and keeping transactions short.
• What is the N+1 problem? One query for a list plus one per item. Fix with a join, eager loading, or one batched query.
• Why a connection pool? Connections are slow to create and limited in number, so a fixed set is reused.

Scale

• Replication vs sharding? Replication copies the whole database for redundancy and read capacity; sharding splits it so each machine holds different data, which is what helps with writes.
• What is replication lag? Replicas trail the primary slightly, so a just-written value may not appear on a read. Read from the primary briefly after writing.
• Explain CAP. During a network partition you must choose consistency or availability. Partition tolerance isn't optional, so it's really a two-way choice — and only applies during a failure.
• What is eventual consistency? Copies converge shortly rather than instantly. Fine for like counts, not for balances.
• How do you speed up a slow query? EXPLAIN ANALYZE first. Then: add the right index, fix N+1, select fewer columns, paginate by keyset, and only then consider caching.

Learning Path

1. SQL basics — SELECT, WHERE, ORDER BY, LIMIT on a real dataset. Type the queries; don't just read them.
2. Design a small schema — users, posts, comments. Get the keys and relationships right on paper first.
3. Joins until they're automatic — especially LEFT JOIN and the "find rows with no match" pattern.
4. Grouping and aggregates — GROUP BY, HAVING, and the difference from WHERE.
5. Indexes — run EXPLAIN before and after adding one. Seeing Seq Scan become Index Scan makes it click.
6. Transactions — write a money transfer, then deliberately break it halfway and watch the rollback.
7. Connect from code — a pool, parameterised queries, and a migration tool. Build a small CRUD API.
8. Race conditions — build a stock counter, hit it with concurrent requests, watch it break, then fix it three ways.
9. MongoDB — the same app modelled as documents. Feel the embed-vs-reference decision for yourself.
10. Redis — add caching and rate limiting to that API. Measure the difference.
11. Performance — seed 100,000 rows, find the slow queries, fix the N+1s, add keyset pagination.
12. Then — replication and read replicas, backup and restore drills, and window functions and CTEs for reporting.