DBMS

A Database Management System (DBMS) is software that sits between our application and the actual data stored on disk. It handles all the messy details — storing, retrieving, updating, and deleting data — so we don’t have to manage raw files ourselves.

Think of it like a librarian. We don’t walk into a library and randomly dig through shelves. We ask the librarian (DBMS), and they know exactly where everything is, who borrowed what, and how to keep things organized.

Why Not Just Use Files?

Before databases existed, people stored data in flat files — CSV files, text files, whatever. And it worked… until it didn’t. Here’s what goes wrong with flat files:

• No concurrent access — two users editing the same file at once? Chaos.
• No data integrity — nothing stops us from putting “banana” in an age field.
• No relationships — linking data across files is a nightmare of manual bookkeeping.
• No security — the whole file is either accessible or it isn’t. No fine-grained control.
• No crash recovery — if the system crashes mid-write, our data could be half-written garbage.

A DBMS solves all of these problems. It gives us structured storage, transactions, access control, crash recovery, and a query language to talk to our data.

How a DBMS Sits in the Architecture

Our application sends queries (SQL or API calls) to the DBMS. The DBMS figures out the most efficient way to read/write the data on disk, handles locking, caching, and returns the results. We never deal with file I/O directly.

Types of DBMS

Relational (RDBMS)

This is the classic. Data lives in tables with rows and columns. We use SQL to query it. Every row has a fixed schema — same columns, same data types.

Best for: structured data with clear relationships. Banking, e-commerce, anything where data integrity matters.

Popular ones: PostgreSQL, MySQL, SQLite, Oracle, SQL Server

NoSQL

A broad category for “anything that’s not a traditional relational table.” These databases trade some of the strictness of RDBMS for flexibility and scale.

• Document stores (MongoDB, CouchDB) — data as JSON-like documents
• Key-Value stores (Redis, DynamoDB) — simple key-to-value lookups, blazing fast
• Wide-Column stores (Cassandra, HBase) — rows can have different columns
• Graph databases (Neo4j, ArangoDB) — nodes and edges for relationship-heavy data

NewSQL

The best of both worlds — SQL interface and ACID guarantees, but with the horizontal scalability of NoSQL. These are newer and designed for modern distributed systems.

Popular ones: CockroachDB, TiDB, Google Spanner, YugabyteDB

The Big Players at a Glance

-- PostgreSQL: The "Swiss Army Knife" of databases
-- Open source, feature-rich, great for almost anything
-- Supports JSON, full-text search, extensions

-- MySQL: The most widely deployed open-source RDBMS
-- Powers WordPress, Facebook (with modifications), many web apps
-- Simple to set up, huge community

-- MongoDB: The go-to document database
-- Schema-flexible, stores BSON (binary JSON)
-- Great for rapid prototyping and semi-structured data

-- Redis: In-memory key-value store
-- Sub-millisecond reads, used as cache or session store
-- Also supports pub/sub, streams, and more

-- SQLite: Serverless, file-based database
-- No separate server process — the DB is just a file
-- Perfect for mobile apps, embedded systems, local dev

What a DBMS Actually Does Under the Hood

When we fire a query, here’s what happens inside:

1. Parser — checks if our SQL is syntactically correct
2. Query Optimizer — figures out the best execution plan (which indexes to use, join order, etc.)
3. Execution Engine — actually runs the plan
4. Storage Engine — reads/writes data from/to disk
5. Transaction Manager — ensures ACID properties
6. Buffer Manager — caches frequently accessed data in memory

We don’t need to know all these internals for most interviews, but knowing the DBMS isn’t just “a black box” shows deeper understanding.

Interview Tip

When asked “What is a DBMS?”, don’t just give the textbook definition. Mention the problems it solves (concurrency, integrity, recovery, security) and name a few types. That shows we understand the why, not just the what.

Not all databases are built the same. The type of database we pick depends entirely on the shape of our data and the access patterns we expect. Picking the wrong type is like using a screwdriver to hammer a nail — it technically works, but it’s painful.

Let’s walk through every major type.

The Seven Main Types

1. Relational Databases (RDBMS)

The most widely used type. Data is organized into tables (relations) with a fixed schema. Every row follows the same structure. We query using SQL.

The big strength? Relationships. We can link tables together with foreign keys and use JOINs to pull related data in a single query.

-- Classic relational structure
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),  -- foreign key relationship
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

Best for: Banking, e-commerce, any app with structured data and complex relationships.

2. Document Databases

Instead of tables, data lives as JSON-like documents. Each document can have a completely different structure — no rigid schema required.

Think of it like a filing cabinet. Each folder (document) can contain whatever we want. One folder might have 5 pages, another might have 20 with different fields.

-- MongoDB-style document (not SQL, but shown for clarity)
-- A user document might look like:
-- {
--   "_id": "abc123",
--   "name": "Manish",
--   "email": "manish@example.com",
--   "addresses": [
--     { "city": "Mumbai", "zip": "400001" },
--     { "city": "Delhi", "zip": "110001" }
--   ]
-- }
-- No need to create a separate "addresses" table!

Best for: Content management, user profiles, catalogs — anything with semi-structured or frequently changing schemas.

3. Key-Value Stores

The simplest model. Every piece of data is stored as a key-value pair. Give it a key, get back a value. That’s it. No complex queries, no joins, no relationships.

Because it’s so simple, it’s incredibly fast. Most key-value stores live entirely in memory.

Best for: Caching, session storage, feature flags, rate limiting, leaderboards.

4. Wide-Column Stores

Think of it like a relational table, but each row can have different columns. Data is grouped into column families, and we query by row key.

These are designed for massive scale — billions of rows, thousands of columns, distributed across many nodes.

Best for: IoT data, event logging, recommendation engines, anything with huge write throughput.

5. Graph Databases

Data is stored as nodes (entities) and edges (relationships between them). Each node and edge can have properties.

When our queries are all about “how is X connected to Y?”, graph databases destroy relational databases in performance. A query that needs 10 JOINs in SQL might be a simple traversal in a graph.

Best for: Social networks, fraud detection, recommendation engines, knowledge graphs.

6. Columnar Databases

In a normal row-based database, data for a single row is stored together. In a columnar database, data for a single column is stored together.

Why does this matter? When we run analytics queries like “what’s the average order total?”, we only need the total column. A columnar store reads just that column — not every field of every row.

Best for: Analytics, data warehousing, business intelligence, reporting.

7. Time-Series Databases

Optimized for data that comes in as timestamped data points — metrics, sensor readings, stock prices. They handle massive write throughput and have built-in functions for time-based aggregation (averages per hour, rolling windows, etc.).

Best for: Monitoring/observability, IoT sensor data, financial data, log analytics.

How to Pick the Right Type

There’s no “best” database. It depends on what we’re building:

The Real World: Polyglot Persistence

Most production systems don’t use just one database. They use multiple types together:

• PostgreSQL as the primary data store
• Redis for caching and sessions
• Elasticsearch for full-text search
• ClickHouse for analytics

This is called polyglot persistence — picking the right tool for each job instead of forcing everything into one database.

Interview Tip

If an interviewer asks “which database would you pick?”, never answer with just a name. Always explain the why — what access patterns, what scale, what consistency requirements. That’s what separates a good answer from a great one.

This is probably the most common database interview question. “When would you pick SQL vs NoSQL?” Let’s break it down properly.

In simple language, SQL databases store data in structured tables with a predefined schema. NoSQL databases store data in flexible formats — documents, key-value pairs, graphs, or wide columns.

The Core Differences

Schema: Rigid vs Flexible

With SQL, we define the schema upfront. Every row in a table must follow it. Want to add a new field? We need to run an ALTER TABLE migration.

-- SQL: Schema is defined upfront
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INT
);

-- Every user MUST have these columns. No exceptions.
-- Adding a "phone" field later requires a migration:
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

With NoSQL (say MongoDB), documents in the same collection can have completely different fields. One user document might have a phone field, another might not. No migration needed.

-- MongoDB: No predefined schema
-- Document 1:
-- { "name": "Manish", "email": "manish@example.com", "age": 26 }
--
-- Document 2 (same collection, different fields):
-- { "name": "Rahul", "email": "rahul@example.com", "phone": "+91..." }
--
-- Both live happily in the same collection

Scaling: Vertical vs Horizontal

Vertical scaling means getting a bigger, faster machine. More RAM, more CPU, faster disk. This is how most SQL databases scale. There’s a ceiling though — machines can only get so big.

Horizontal scaling means adding more machines to share the load. NoSQL databases are designed for this from the ground up. Data gets distributed (sharded) across multiple nodes.

Now, this isn’t a hard rule anymore. PostgreSQL can scale horizontally with tools like Citus. MongoDB added ACID transactions. The lines are blurring. But the default design philosophy still differs.

ACID vs BASE

SQL databases follow ACID — every transaction is Atomic, Consistent, Isolated, and Durable. When we transfer money between accounts, either both updates happen or neither does. No partial state.

NoSQL databases typically follow BASE — Basically Available, Soft state, Eventually consistent. The data might be slightly stale for a moment, but the system stays available and eventually catches up.

In simple language: ACID says “everything is perfectly correct, always.” BASE says “things will be correct eventually, but we prioritize staying online.”

We cover both in detail in their own notes.

When to Pick SQL

• Our data has clear, well-defined relationships (users → orders → products)
• We need strong consistency and ACID transactions (banking, payments)
• We know our schema and it won’t change dramatically
• We need complex queries with JOINs, aggregations, subqueries
• Our data fits on one (admittedly large) machine

When to Pick NoSQL

• Our schema evolves rapidly (startup MVP, experimental features)
• We need to scale to massive read/write throughput across many servers
• Our data is semi-structured or varies by record (user profiles, product catalogs)
• We need low-latency reads and can tolerate eventual consistency
• We’re building real-time features like chat, feeds, or gaming leaderboards

The Dirty Secret: Most Apps Don’t Need NoSQL

Here’s the thing most people won’t tell us in an interview — for 90% of applications, PostgreSQL is more than enough. It handles JSON, full-text search, and can scale pretty far vertically.

NoSQL makes sense at massive scale (think Twitter, Netflix, Uber) or when the data genuinely doesn’t fit a relational model. For a typical web app with a few million rows? PostgreSQL all day.

Hybrid Approaches

Modern databases are blurring the lines:

• PostgreSQL has jsonb columns for document-style storage inside a relational database
• MongoDB added multi-document ACID transactions (since v4.0)
• CockroachDB and YugabyteDB give us SQL + horizontal scaling (NewSQL)
• FaunaDB combines document flexibility with relational guarantees

The real skill isn’t knowing “SQL good, NoSQL bad” (or vice versa). It’s knowing when each approach makes sense and being able to justify the decision.

Interview Tip

Never say “NoSQL is better because it scales.” That’s a red flag. Instead, talk about the specific trade-offs for the specific use case. Interviewers love hearing “for this use case, I’d pick X because…” followed by concrete reasoning.

ACID is the set of four guarantees that make database transactions reliable. Every time we hear “transaction” in the database world, ACID is what makes it trustworthy.

Think of it like a contract. The database promises: “If you wrap your operations in a transaction, I guarantee these four things will hold true.”

The Classic Bank Transfer Example

Let’s say Manish wants to transfer Rs 1000 from Account A to Account B. This involves two operations:

1. Subtract 1000 from Account A
2. Add 1000 to Account B

What could go wrong? The system crashes after step 1 but before step 2. Now Rs 1000 has vanished into thin air. ACID prevents this.

A — Atomicity

All or nothing. Either the entire transaction succeeds, or the entire transaction is rolled back. No partial updates.

In our bank transfer: either BOTH the debit and credit happen, or NEITHER happens. If the system crashes mid-transaction, the database rolls back everything to the state before the transaction started.

Think of it like an “undo” button that triggers automatically on failure.

-- Atomicity in action
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
-- What if the system crashes RIGHT HERE?
-- With atomicity, the first UPDATE gets rolled back automatically.
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

COMMIT;
-- Only at COMMIT do both changes become permanent

C — Consistency

The database must move from one valid state to another valid state. All rules, constraints, and triggers must be satisfied before and after the transaction.

If we have a rule that says “balance cannot be negative,” and Manish tries to transfer Rs 5000 from an account with only Rs 1000 — the entire transaction is rejected. The database stays consistent.

-- Consistency: constraints are always enforced
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);

BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 'A';
-- If Account A only has 1000, this violates the CHECK constraint
-- The transaction FAILS, nothing changes
COMMIT;
-- ERROR: new row violates check constraint "positive_balance"

I — Isolation

Concurrent transactions don’t interfere with each other. Each transaction runs as if it’s the only one running on the database, even if thousands are running simultaneously.

Without isolation, two people transferring money at the same time could see each other’s half-finished work, leading to incorrect balances.

-- Without isolation (the problem):
-- Transaction 1: reads balance of A = 5000
-- Transaction 2: reads balance of A = 5000
-- Transaction 1: sets A = 5000 - 1000 = 4000
-- Transaction 2: sets A = 5000 - 2000 = 3000
-- Result: A = 3000, but it should be 2000! (one debit was "lost")

-- With isolation, Transaction 2 waits until Transaction 1 finishes
-- or the database detects the conflict and handles it properly

The level of isolation can be tuned (we’ll cover isolation levels in a separate note). More isolation = safer but slower.

D — Durability

Once a transaction is committed, it’s permanent. Even if the server crashes, loses power, or catches fire — the committed data survives.

The database achieves this by writing to a Write-Ahead Log (WAL) before confirming the commit. Even if the main data files are corrupted, the WAL can replay the changes.

In simple language: once the database says “COMMIT successful,” we can trust that the data is safely stored. No take-backsies.

All Four Together

A Full Transaction Example

-- Transfer Rs 1000 from Account A to Account B
BEGIN;  -- Start the transaction (Atomicity boundary)

-- Check balance first (Consistency)
DO $$
BEGIN
    IF (SELECT balance FROM accounts WHERE id = 'A') < 1000 THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
END $$;

-- Perform the transfer
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

COMMIT;  -- Make it permanent (Durability)
-- If anything failed above, ROLLBACK undoes everything (Atomicity)

Which Databases Support ACID?

• Full ACID: PostgreSQL, MySQL (InnoDB), SQLite, Oracle, SQL Server
• Document-level ACID: MongoDB (multi-document since v4.0)
• Typically not ACID: Cassandra, DynamoDB, Redis (they follow BASE instead)
• NewSQL (ACID + distributed): CockroachDB, TiDB, Google Spanner

Interview Tip

ACID is one of the most asked DBMS topics. Don’t just list the four words — explain each with the bank transfer example. Interviewers want to see that we understand why each property matters, not just what the acronym stands for.

If ACID is the strict, rule-following accountant, BASE is the laid-back startup founder who says “it’ll all work out eventually.” And honestly? For many systems, that’s perfectly fine.

BASE is the consistency model used by most NoSQL and distributed databases. It prioritizes availability and partition tolerance over strict consistency.

The acronym stands for:

• Basically Available
• Soft state
• Eventually consistent

Basically Available

The system guarantees a response to every request — even if the data might be slightly stale. It won’t just refuse to answer because one node is down.

In simple language: “We’ll always give you something, even if it’s not the absolute latest version.”

Compare this to ACID where a transaction might block or fail if it can’t guarantee perfect consistency.

Real-world example: When we scroll Instagram and the like count shows 1,042 instead of 1,043 (because the latest like hasn’t propagated yet), that’s basic availability in action. The app didn’t crash or show a loading spinner — it gave us a slightly stale number.

Soft State

The state of the system can change over time, even without new input. Background processes (replication, anti-entropy repairs) are constantly syncing data between nodes.

This is the opposite of ACID where the state only changes when we explicitly run a transaction.

Think of it like this: we write data to Node A, and for a brief moment, Node B has stale data. But behind the scenes, the system is syncing. The state is “soft” — it’s in flux until everything converges.

Eventually Consistent

Given enough time with no new updates, all replicas will converge to the same value. We just can’t guarantee it happens immediately.

This is the key trade-off. ACID says “after my transaction commits, everyone sees the new data instantly.” BASE says “everyone will see it… eventually.”

How long is “eventually”? In most modern systems, we’re talking milliseconds to a few seconds. It’s not like we wait days.

ACID vs BASE Comparison

When BASE Makes Sense

Not everything needs bank-level consistency. Here are some cases where eventual consistency is totally fine:

• Social media feeds — if a new post shows up 2 seconds late, nobody notices
• Like/view counters — exact real-time accuracy doesn’t matter
• Product catalog browsing — a price update can take a moment to propagate
• DNS — the entire internet runs on eventual consistency (TTL-based caching)
• Shopping cart — items might be slightly out of sync across devices for a moment

When BASE Does NOT Make Sense

• Financial transactions — we absolutely need ACID here
• Inventory/booking systems — double-selling a concert ticket is unacceptable
• User authentication — a password change must be immediately consistent
• Anything where “slightly wrong” causes real harm

How Eventual Consistency Works in Practice

-- Imagine a 3-node Cassandra cluster
-- We write: INSERT INTO posts (id, content) VALUES (1, 'Hello World');

-- Step 1: Write hits Node A (coordinator), saved locally
-- Step 2: Node A sends the write to Node B and Node C (async replication)
-- Step 3: Node A confirms success to the client

-- For a brief moment:
-- Node A: has the data ✓
-- Node B: syncing... (stale)
-- Node C: syncing... (stale)

-- After a few milliseconds, all three nodes have the data
-- That gap is "eventual consistency"

Tunable Consistency

Many NoSQL databases let us tune the consistency level per query. In Cassandra, for example:

• ONE — read/write to one node (fastest, least consistent)
• QUORUM — majority of nodes must agree (balanced)
• ALL — every node must respond (slowest, most consistent)

So BASE isn’t always “fully eventually consistent.” We can dial it up when we need stronger guarantees for specific operations.

Interview Tip

When talking about BASE, always contrast it with ACID. Show that we understand it’s not “worse” — it’s a deliberate trade-off for distributed systems. The key insight: we sacrifice strict consistency to gain availability and partition tolerance, and for many use cases that’s the right call.

The CAP theorem is one of the most famous (and most misunderstood) concepts in distributed systems. It was proposed by Eric Brewer in 2000 and formally proved in 2002.

In simple language: in a distributed system, when a network partition happens, we have to choose between consistency and availability. We can’t have both.

The Three Letters

C — Consistency: Every read receives the most recent write. All nodes see the same data at the same time. If we write “balance = 500” and immediately read, we get 500 — not some stale value.

A — Availability: Every request receives a response (even if it’s not the latest data). The system never refuses to answer.

P — Partition Tolerance: The system continues to work even when network messages between nodes are lost or delayed. In a distributed system, network partitions will happen — it’s not a matter of “if” but “when.”

The Triangle

Why “Pick 2 out of 3”?

Here’s the key insight: partition tolerance isn’t really optional in a distributed system. Networks fail. Cables get cut. Data centers lose connectivity. It happens.

So the real choice is: when a partition happens, do we sacrifice Consistency or Availability?

• CP (Consistency + Partition Tolerance): During a partition, the system refuses to respond rather than return stale data. We get correct data or no data.
• AP (Availability + Partition Tolerance): During a partition, the system always responds, even if the data might be stale.
• CA (Consistency + Availability): This only works on a single node (no partitions possible). The moment we go distributed, we lose this option.

Where Do Popular Databases Fall?

CP — Consistency + Partition Tolerance

These databases will reject requests or become read-only during a partition rather than serve stale data.

• MongoDB (with majority write concern)
• HBase
• Redis Cluster (when a partition is detected, minority partition stops serving)
• etcd / ZooKeeper (consensus-based)

AP — Availability + Partition Tolerance

These databases always respond, even if the data might be temporarily inconsistent.

• Cassandra (tunable, but AP by default)
• DynamoDB
• CouchDB
• Riak

CA — Consistency + Availability (Single Node Only)

Not truly distributed. Works only when there’s no possibility of a network partition.

• Single-node PostgreSQL
• Single-node MySQL
• SQLite

A Practical Example

Imagine we have a distributed database with two nodes, Node A and Node B. A network cable between them gets cut (partition!).

A user writes balance = 500 to Node A. Now another user reads from Node B.

CP choice: Node B says “Sorry, I can’t serve this read because I haven’t synced with Node A. I might give you wrong data.” The system is consistent but unavailable.

AP choice: Node B says “Here’s the last value I have: balance = 300 (stale).” The system is available but inconsistent.

Neither is “wrong.” It depends on what we’re building.

-- CP behavior (MongoDB with majority read concern):
-- If the primary is unreachable, reads FAIL
-- db.accounts.find({id: "A"}).readConcern("majority")
-- Result during partition: Error - primary unavailable

-- AP behavior (Cassandra with consistency level ONE):
-- Reads succeed from any available node, even with stale data
-- SELECT balance FROM accounts WHERE id = 'A';  -- consistency ONE
-- Result during partition: Returns stale value (but doesn't fail!)

Common Misconceptions

”We have to give up one of the three permanently”

Nope. The trade-off only kicks in during a network partition. When everything is running smoothly (no partition), we can have all three. CAP is about what happens when things go wrong.

”CA databases exist in distributed systems”

Not really. Any multi-node system can experience a network partition. CA only works for single-node setups.

”CAP means NoSQL is better”

CAP doesn’t say one is better. It says distributed systems have trade-offs. Plenty of CP systems use SQL (CockroachDB, Google Spanner).

PACELC: The Extended Version

CAP only tells us what happens during a partition. But what about normal operation? The PACELC theorem extends CAP:

• Partition: choose A (availability) or C (consistency)
• Else (no partition): choose L (latency) or C (consistency)

For example, DynamoDB is PA/EL (available during partition, low latency normally). MongoDB is PC/EC (consistent during partition, consistent normally but with higher latency).

Interview Tip

CAP comes up in almost every system design interview. Don’t just recite the definition — give a concrete example like the two-node partition scenario above. Bonus points for mentioning that the trade-off only applies during a partition, and that PACELC gives us a more complete picture.

SQL commands aren’t all the same kind. They fall into four categories based on what they do. Knowing these categories helps us understand what a SQL statement is actually doing at a higher level.

The Four Categories

DDL — Data Definition Language

DDL commands define or modify the structure of our database — tables, columns, indexes, schemas. They don’t touch the actual data. Think of DDL as building the shelves, not putting books on them.

DDL commands are auto-committed. Once we run them, there’s no rolling back (in most databases).

CREATE

-- Create a new table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    department_id INT,
    salary DECIMAL(10,2) DEFAULT 0,
    hired_at TIMESTAMP DEFAULT NOW()
);

-- Create an index
CREATE INDEX idx_employees_department ON employees(department_id);

-- Create a schema (namespace)
CREATE SCHEMA hr;

ALTER

-- Add a column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- Change a column's data type
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12,2);

-- Rename a column
ALTER TABLE employees RENAME COLUMN phone TO mobile;

-- Add a constraint
ALTER TABLE employees ADD CONSTRAINT fk_department
    FOREIGN KEY (department_id) REFERENCES departments(id);

-- Drop a column
ALTER TABLE employees DROP COLUMN mobile;

DROP

-- Drop a table entirely (structure + data, gone forever)
DROP TABLE employees;

-- Drop only if it exists (avoids error)
DROP TABLE IF EXISTS employees;

-- Drop a table and everything that depends on it
DROP TABLE departments CASCADE;

TRUNCATE

-- Remove ALL rows but keep the table structure
TRUNCATE TABLE employees;

-- Faster than DELETE because it doesn't log individual row deletions
-- Resets auto-increment counters
-- Cannot be rolled back in most databases (it's DDL, not DML)

The key difference: DROP removes the table itself. TRUNCATE empties it but keeps the structure. DELETE removes specific rows (and is DML, not DDL).

DML — Data Manipulation Language

DML commands work with the actual data — the rows inside our tables. These are the commands we use 95% of the time.

DML commands are transactional. We can wrap them in BEGIN/COMMIT and roll them back if something goes wrong.

SELECT

-- Basic query
SELECT name, email FROM employees WHERE department_id = 5;

-- With sorting and limiting
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

-- With joins (more on this in the Joins note)
SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id;

INSERT

-- Insert a single row
INSERT INTO employees (name, email, department_id, salary)
VALUES ('Manish', 'manish@example.com', 3, 75000);

-- Insert multiple rows
INSERT INTO employees (name, email, department_id, salary)
VALUES
    ('Alice', 'alice@example.com', 1, 80000),
    ('Bob', 'bob@example.com', 2, 70000),
    ('Charlie', 'charlie@example.com', 1, 90000);

-- Insert from a query
INSERT INTO archived_employees
SELECT * FROM employees WHERE hired_at < '2020-01-01';

UPDATE

-- Update specific rows
UPDATE employees SET salary = 85000 WHERE id = 1;

-- Update with a calculation
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 3;
-- 10% raise for everyone in department 3

-- Update with a JOIN (PostgreSQL syntax)
UPDATE employees e
SET department_id = d.id
FROM departments d
WHERE d.name = 'Engineering' AND e.name = 'Manish';

DELETE

-- Delete specific rows
DELETE FROM employees WHERE id = 5;

-- Delete with a condition
DELETE FROM employees WHERE hired_at < '2015-01-01';

-- Delete all rows (but table structure remains — unlike TRUNCATE, this is logged)
DELETE FROM employees;

DCL — Data Control Language

DCL manages who can do what in the database. It’s about permissions and access control.

-- Grant SELECT permission on a table
GRANT SELECT ON employees TO readonly_user;

-- Grant all permissions
GRANT ALL PRIVILEGES ON employees TO admin_user;

-- Grant permission on a schema
GRANT USAGE ON SCHEMA hr TO analyst_role;

-- Revoke a permission
REVOKE DELETE ON employees FROM intern_user;

-- Revoke all
REVOKE ALL PRIVILEGES ON employees FROM ex_employee;

In real projects, we usually manage permissions through roles, not individual users:

-- Create a role and assign permissions
CREATE ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

-- Assign the role to a user
GRANT analyst TO manish;

TCL — Transaction Control Language

TCL commands manage transactions — grouping multiple DML operations into an all-or-nothing unit.

-- Start a transaction
BEGIN;

-- Do some work
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- Save a checkpoint (we can rollback to here instead of the beginning)
SAVEPOINT after_transfer;

-- Do more work that might fail
UPDATE audit_log SET last_transfer = NOW() WHERE account_id = 1;

-- Oops, something went wrong? Rollback to the savepoint
ROLLBACK TO SAVEPOINT after_transfer;

-- Or commit everything
COMMIT;

DROP vs TRUNCATE vs DELETE

This comparison comes up a lot in interviews:

Interview Tip

Interviewers love asking “What’s the difference between DELETE and TRUNCATE?” or “Is TRUNCATE DDL or DML?” The answer is that TRUNCATE is DDL (it’s a structural operation that deallocates pages), DELETE is DML (it logs each row deletion and fires triggers). Knowing this distinction shows we understand what’s happening under the hood.

Joins are how we combine data from two or more tables based on a related column. This is one of the most fundamental operations in SQL, and easily the most asked topic in database interviews.

Let’s set up our example tables first:

-- Employees table
-- id | name    | dept_id
-- 1  | Alice   | 1
-- 2  | Bob     | 2
-- 3  | Charlie | NULL    (no department assigned)
-- 4  | Diana   | 1

-- Departments table
-- id | name
-- 1  | Engineering
-- 2  | Marketing
-- 3  | HR          (no employees in this dept)

Visual Overview

INNER JOIN

Returns only the rows where there’s a match in both tables. If an employee has no department, or a department has no employees — they’re excluded.

SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Result:
-- employee | department
-- Alice    | Engineering
-- Bob      | Marketing
-- Diana    | Engineering

-- Charlie is excluded (dept_id is NULL, no match)
-- HR is excluded (no employees have dept_id = 3)

This is the most common join. When people just say “JOIN” without a prefix, they usually mean INNER JOIN.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right table. If there’s no match, the right side gets NULLs.

Think of it like: “Give me all employees, and their department if they have one.”

SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Result:
-- employee | department
-- Alice    | Engineering
-- Bob      | Marketing
-- Charlie  | NULL          ← included! (no matching dept)
-- Diana    | Engineering

LEFT JOIN is incredibly useful for finding “missing” data:

-- Find employees with no department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;
-- Returns: Charlie

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN. Returns all rows from the right table, plus matching rows from the left. If there’s no match, the left side gets NULLs.

SELECT e.name AS employee, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- Result:
-- employee | department
-- Alice    | Engineering
-- Diana    | Engineering
-- Bob      | Marketing
-- NULL     | HR            ← included! (no employees in HR)

In practice, most developers just use LEFT JOIN and flip the table order instead of using RIGHT JOIN. It reads more naturally.

FULL OUTER JOIN

Returns all rows from both tables. Matching rows are combined, non-matching rows get NULLs on the missing side.

SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- Result:
-- employee | department
-- Alice    | Engineering
-- Bob      | Marketing
-- Charlie  | NULL          ← no department
-- Diana    | Engineering
-- NULL     | HR            ← no employees

Note: MySQL doesn’t support FULL OUTER JOIN directly. We’d simulate it with a UNION of LEFT and RIGHT JOIN.

CROSS JOIN

Returns the cartesian product — every row from the left table paired with every row from the right table. No ON condition needed.

If table A has 4 rows and table B has 3 rows, the result has 4 x 3 = 12 rows.

SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;

-- Every employee paired with every department (12 rows)
-- Alice    | Engineering
-- Alice    | Marketing
-- Alice    | HR
-- Bob      | Engineering
-- Bob      | Marketing
-- Bob      | HR
-- ... and so on

Cross joins are rarely used in practice, but they’re useful for generating combinations (like all dates crossed with all products for a report).

SELF JOIN

A table joined with itself. We use aliases to treat the same table as two different tables. Super useful for hierarchical data.

-- Find employees and their managers
-- (assuming employees table has a manager_id column)
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Result:
-- employee | manager
-- Alice    | Bob       (Alice reports to Bob)
-- Bob      | NULL      (Bob is the top boss)
-- Charlie  | Alice     (Charlie reports to Alice)

Multiple Joins

We often chain joins together to pull data from 3+ tables:

-- Get order details with customer and product info
SELECT
    c.name AS customer,
    p.name AS product,
    o.quantity,
    o.total_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.created_at DESC;

JOIN Performance Tips

1. Always join on indexed columns — joining on non-indexed columns causes full table scans.
2. Use INNER JOIN when possible — it’s typically faster than OUTER JOINs because the optimizer has more flexibility.
3. Filter early — put WHERE conditions to reduce rows before the join, not after.
4. Avoid joining too many tables — each join multiplies complexity. If we’re joining 7+ tables, we might want to rethink the query or use CTEs.

Interview Tip

The classic interview question is “explain the different types of joins.” Draw the Venn diagram on a whiteboard, use a concrete example like employees/departments, and show the actual result sets. Knowing what each join returns (and what it excludes) is more impressive than reciting definitions.

A subquery is simply a query inside another query. Instead of hardcoding a value, we compute it on the fly. And a CTE (Common Table Expression) is a way to write subqueries that are actually readable.

Types of Subqueries

Scalar Subquery — Returns a Single Value

This goes wherever a single value is expected — in a SELECT, WHERE, or HAVING clause.

-- Find employees who earn more than the average salary
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- The subquery returns one number (e.g., 75000)
-- The outer query then filters: WHERE salary > 75000

Row Subquery — Returns a Single Row

Returns one row with multiple columns. Useful for comparing against a set of values.

-- Find the employee with the highest salary in each department
SELECT name, salary, department_id
FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

Table Subquery — Returns Multiple Rows

Returns a full result set that we can use as a virtual table.

-- Use a subquery as a temporary table in FROM
SELECT dept_name, avg_salary
FROM (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 80000;

Correlated vs Non-Correlated

This distinction is crucial and comes up in interviews a lot.

Non-Correlated Subquery

The inner query runs once, independently of the outer query. It doesn’t reference any columns from the outer query.

-- Non-correlated: inner query runs ONCE
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- The database computes the average once, then uses that value for every row

Correlated Subquery

The inner query runs once for each row of the outer query. It references a column from the outer query. This makes it slower but more powerful.

-- Correlated: inner query runs ONCE PER ROW
-- Find employees who earn more than their department's average
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id  -- references outer query!
);

-- For each employee, the database computes their department's average
-- Then checks if that employee's salary is above it

In simple language: if the subquery can run on its own (paste it in a SQL editor and it works), it’s non-correlated. If it references the outer query and can’t run alone, it’s correlated.

EXISTS vs IN

Both check if something exists, but they work differently.

IN — Checks Against a List

-- Find employees in departments that are in New York
SELECT name FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'New York'
);

-- The subquery returns a list of IDs: (1, 3, 7)
-- Then it checks: WHERE department_id IN (1, 3, 7)

EXISTS — Checks for Existence

-- Same logic, different approach
SELECT e.name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = e.department_id AND d.location = 'New York'
);

-- For each employee, it asks: "Does a matching department exist?"
-- Returns TRUE/FALSE, doesn't actually return the rows

When to Use Which?

• Use IN when the subquery returns a small result set. It materializes the whole list first.
• Use EXISTS when the subquery might return a large result set. It stops as soon as it finds one match (short-circuits).
• EXISTS is usually faster for correlated subqueries with large tables.
• IN can have issues with NULL values (NULL IN (…) returns NULL, not FALSE).

-- Gotcha with IN and NULLs
SELECT name FROM employees
WHERE department_id NOT IN (SELECT id FROM departments);
-- If departments has a NULL id, this returns NOTHING!
-- Because NOT IN with NULLs evaluates to UNKNOWN

-- EXISTS doesn't have this problem
SELECT e.name FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE d.id = e.department_id
);
-- Works correctly even with NULLs

CTEs — Common Table Expressions

CTEs let us name a subquery and reference it by name. They use the WITH keyword and make complex queries way more readable.

Think of it like: “Let me define a temporary result, give it a name, and use it below.”

-- Without CTE (messy nested subqueries)
SELECT dept_name, avg_salary
FROM (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
) AS dept_stats
WHERE avg_salary > (SELECT AVG(salary) FROM employees);

-- With CTE (clean and readable)
WITH dept_stats AS (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
),
company_avg AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT ds.dept_name, ds.avg_salary
FROM dept_stats ds, company_avg ca
WHERE ds.avg_salary > ca.avg_salary;

We can chain multiple CTEs and each one can reference the previous ones.

Recursive CTEs

Recursive CTEs are powerful for hierarchical data — org charts, folder structures, category trees.

They have two parts:

1. Base case — the starting rows
2. Recursive case — how to find the next level

-- Find all subordinates of a manager (org chart traversal)
WITH RECURSIVE subordinates AS (
    -- Base case: start with the manager
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1  -- Start from employee #1

    UNION ALL

    -- Recursive case: find people who report to current level
    SELECT e.id, e.name, e.manager_id, s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT name, level FROM subordinates ORDER BY level;

-- Result:
-- name    | level
-- Alice   | 1       (the manager)
-- Bob     | 2       (reports to Alice)
-- Charlie | 2       (reports to Alice)
-- Diana   | 3       (reports to Bob)

Another classic example — generating a series of dates:

-- Generate all dates in January 2024
WITH RECURSIVE dates AS (
    SELECT DATE '2024-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day'
    FROM dates
    WHERE dt < '2024-01-31'
)
SELECT dt FROM dates;

CTE vs Subquery — When to Use What

In general: use CTEs for readability and when we need to reference the same derived table more than once. Use subqueries for simple, one-off filters.

Interview Tip

If asked to write a complex SQL query in an interview, use CTEs liberally. It shows clean thinking and makes the query much easier for the interviewer to follow. Also, knowing the difference between correlated and non-correlated subqueries is a common “gotcha” question — always mention the performance implication (correlated = once per row).

Aggregate functions take a bunch of rows and squash them down into a single value. Think of it like summarizing — instead of looking at 1,000 individual salaries, we want one number: the average.

The Five Core Aggregates

-- Sample data: employees table
-- name    | department | salary
-- Alice   | Eng        | 90000
-- Bob     | Eng        | 80000
-- Charlie | Marketing  | 70000
-- Diana   | Marketing  | 75000
-- Eve     | Eng        | 85000

SELECT
    COUNT(*) AS total_employees,     -- 5
    SUM(salary) AS total_payroll,    -- 400000
    AVG(salary) AS avg_salary,       -- 80000
    MIN(salary) AS lowest_salary,    -- 70000
    MAX(salary) AS highest_salary    -- 90000
FROM employees;

Quick Notes on COUNT

-- COUNT(*) counts ALL rows (including NULLs)
SELECT COUNT(*) FROM employees;  -- 5

-- COUNT(column) counts non-NULL values in that column
SELECT COUNT(manager_id) FROM employees;  -- might be 4 if one is NULL

-- COUNT(DISTINCT column) counts unique non-NULL values
SELECT COUNT(DISTINCT department) FROM employees;  -- 2 (Eng, Marketing)

GROUP BY — Splitting Into Buckets

Without GROUP BY, aggregates work on the entire table. With GROUP BY, we split rows into groups and aggregate each group separately.

Think of it like sorting physical papers into piles by department, then counting each pile.

-- Average salary PER department
SELECT
    department,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    MAX(salary) AS top_salary
FROM employees
GROUP BY department;

-- Result:
-- department | headcount | avg_salary | top_salary
-- Eng        | 3         | 85000      | 90000
-- Marketing  | 2         | 72500      | 75000

The Golden Rule of GROUP BY

Every column in SELECT must either be:

1. In the GROUP BY clause, OR
2. Inside an aggregate function

This is the rule that trips up beginners:

-- This FAILS (name is not grouped or aggregated)
SELECT name, department, AVG(salary)
FROM employees
GROUP BY department;
-- ERROR: column "name" must appear in GROUP BY or be in an aggregate

-- This WORKS
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Why? Because if we’re grouping by department, there are multiple names per group. The database doesn’t know which name to show.

HAVING vs WHERE

This is probably the most asked question about GROUP BY. Here’s the simple answer:

• WHERE filters rows before grouping
• HAVING filters groups after grouping

-- WHERE: filter individual rows BEFORE grouping
-- "Only look at employees hired after 2020"
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hired_at > '2020-01-01'    -- filters ROWS first
GROUP BY department;

-- HAVING: filter groups AFTER grouping
-- "Only show departments where the average salary is above 80K"
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;      -- filters GROUPS

-- Both together:
-- "Among employees hired after 2020, show departments with avg salary > 80K"
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hired_at > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 80000;

The key thing: WHERE cannot use aggregate functions because it runs before grouping. HAVING can.

-- This FAILS
SELECT department, COUNT(*) FROM employees
WHERE COUNT(*) > 2 GROUP BY department;
-- ERROR: aggregate functions not allowed in WHERE

-- This WORKS
SELECT department, COUNT(*) FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

SQL Execution Order

Understanding the order SQL actually executes helps a lot:

1. FROM — pick the table(s)
2. WHERE — filter individual rows
3. GROUP BY — form groups
4. HAVING — filter groups
5. SELECT — pick columns and compute aggregates
6. ORDER BY — sort the results
7. LIMIT — cap the output

This is why we can’t use a column alias from SELECT in a WHERE clause — SELECT hasn’t run yet!

-- This FAILS in most databases
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING avg_sal > 80000;  -- Can't use alias in HAVING (in strict SQL)

-- This WORKS
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;  -- Repeat the expression
-- (PostgreSQL and MySQL are more lenient here, but standard SQL requires this)

Practical Examples

-- Top 3 departments by headcount
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC
LIMIT 3;

-- Departments where everyone earns at least 60K
SELECT department, MIN(salary) AS min_salary
FROM employees
GROUP BY department
HAVING MIN(salary) >= 60000;

-- Monthly revenue breakdown
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS revenue,
    AVG(amount) AS avg_order_value
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Grouping by Multiple Columns

We can group by more than one column. Each unique combination of values becomes a group.

-- Average salary by department AND job level
SELECT department, job_level, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_level
ORDER BY department, job_level;

-- Result:
-- department | job_level | avg_salary
-- Eng        | Junior    | 70000
-- Eng        | Senior    | 95000
-- Marketing  | Junior    | 60000
-- Marketing  | Senior    | 85000

Interview Tip

The WHERE vs HAVING question is guaranteed in DBMS interviews. The one-line answer: “WHERE filters rows before grouping, HAVING filters groups after grouping.” But go further — explain that WHERE can’t use aggregates because it runs before GROUP BY in the execution order. That shows deep understanding.

Window functions are one of the most powerful features in SQL, and once we learn them, we wonder how we ever lived without them. They let us perform calculations across a set of rows without collapsing them into a single row like GROUP BY does.

In simple language: GROUP BY squashes rows together. Window functions compute values across rows but keep every row in the result.

The Syntax

Every window function uses the OVER() clause:

function_name() OVER (
    PARTITION BY column    -- divide rows into groups (optional)
    ORDER BY column        -- order within each group (optional)
)

PARTITION BY vs GROUP BY

This is the fundamental difference to understand:

-- GROUP BY: collapses rows (one row per department)
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
-- Result: 2 rows (one per department)

-- PARTITION BY: keeps all rows, adds the aggregate as a new column
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- Result: all 5 rows, each with their department's average salary
-- name    | department | salary | dept_avg
-- Alice   | Eng        | 90000  | 85000
-- Bob     | Eng        | 80000  | 85000
-- Eve     | Eng        | 85000  | 85000
-- Charlie | Marketing  | 70000  | 72500
-- Diana   | Marketing  | 75000  | 72500

See how every row is preserved? That’s the magic.

Ranking Functions

ROW_NUMBER()

Assigns a unique sequential number to each row within a partition. No ties — if two rows have the same value, they still get different numbers.

-- Number employees by salary within each department
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

-- Result:
-- name    | department | salary | rn
-- Alice   | Eng        | 90000  | 1
-- Eve     | Eng        | 85000  | 2
-- Bob     | Eng        | 80000  | 3
-- Diana   | Marketing  | 75000  | 1
-- Charlie | Marketing  | 70000  | 2

Super useful for “top N per group” queries:

-- Top 2 highest paid employees per department
WITH ranked AS (
    SELECT
        name, department, salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 2;

RANK()

Like ROW_NUMBER, but ties get the same rank. After a tie, it skips numbers.

-- If Alice and Eve both earn 90000:
-- RANK: 1, 1, 3 (skips 2)
SELECT
    name, salary,
    RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;
-- name    | salary | rnk
-- Alice   | 90000  | 1
-- Eve     | 90000  | 1   ← same rank (tie)
-- Bob     | 80000  | 3   ← skips 2!

DENSE_RANK()

Same as RANK, but doesn’t skip numbers after ties.

-- DENSE_RANK: 1, 1, 2 (no gaps)
SELECT
    name, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;
-- name    | salary | drnk
-- Alice   | 90000  | 1
-- Eve     | 90000  | 1   ← same rank (tie)
-- Bob     | 80000  | 2   ← no gap!

LAG and LEAD — Looking at Neighboring Rows

LAG looks at the previous row. LEAD looks at the next row. These are incredibly useful for comparisons over time.

-- Compare each month's revenue with the previous month
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

-- Result:
-- month   | revenue | prev_month | change
-- Jan     | 10000   | NULL       | NULL
-- Feb     | 12000   | 10000      | 2000
-- Mar     | 11000   | 12000      | -1000
-- Apr     | 15000   | 11000      | 4000

-- LAG with a default value (avoid NULLs for the first row)
LAG(revenue, 1, 0) OVER (ORDER BY month)
--            ^  ^
--            |  └── default value if no previous row
--            └── how many rows back to look

Running Totals with SUM() OVER()

This is a classic use case — compute a cumulative sum as we move through rows.

-- Running total of daily sales
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Result:
-- order_date | amount | running_total
-- Jan 1      | 100    | 100
-- Jan 2      | 250    | 350
-- Jan 3      | 175    | 525
-- Jan 4      | 300    | 825

We can also do running totals per group:

-- Running total per department
SELECT
    department,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY department
        ORDER BY order_date
    ) AS dept_running_total
FROM orders;

Moving Averages with Frame Clauses

We can define a “window frame” — a sliding window of rows:

-- 3-day moving average
SELECT
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- current + 2 previous
    ) AS moving_avg_3day
FROM orders;

Common Real-World Patterns

-- Percentage of total
SELECT
    department,
    salary,
    salary * 100.0 / SUM(salary) OVER () AS pct_of_total
FROM employees;

-- Find duplicates (keep one, mark the rest)
WITH numbered AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
DELETE FROM users WHERE id IN (
    SELECT id FROM numbered WHERE rn > 1
);

Interview Tip

Window functions are a favorite topic in SQL interviews because they separate people who know basic SQL from those who know it well. Practice the “top N per group” pattern (ROW_NUMBER + CTE + WHERE rn <= N) — it comes up constantly. Also, be ready to explain the difference between ROW_NUMBER, RANK, and DENSE_RANK with a tie scenario.

A view is a saved SQL query that we can use like a table. It doesn’t store any data — it just runs the query every time we access it. A materialized view actually stores the result on disk, like a cached snapshot.

Think of it like this: a view is a saved recipe (we cook fresh every time), and a materialized view is a meal-prepped container (already cooked, just reheat).

Regular Views

Creating a View

-- Create a view that shows employee details with department names
CREATE VIEW employee_details AS
SELECT
    e.id,
    e.name,
    e.email,
    e.salary,
    d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- Now we can query it like a regular table
SELECT * FROM employee_details WHERE department = 'Engineering';

-- Under the hood, the database runs the full JOIN query every time

Why Use Views?

1. Simplify complex queries — write the complex JOIN once, use the simple view name everywhere
2. Security — expose only certain columns to certain users (hide salary, SSN, etc.)
3. Abstraction — if the underlying table structure changes, we update the view definition and all dependent queries keep working
4. Consistency — everyone uses the same business logic (e.g., “active users” always means the same thing)

-- Security: create a view that hides sensitive data
CREATE VIEW public_employees AS
SELECT id, name, department_id
FROM employees;
-- No salary, no email, no SSN

GRANT SELECT ON public_employees TO analyst_role;
-- Analysts can query this view but not the underlying table

Updating Through Views

Simple views can sometimes be updated (INSERT, UPDATE, DELETE through the view), but it only works if the view maps directly to a single underlying table without aggregations or JOINs.

-- This view is updatable (simple, single table)
CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = true;

-- We can insert through it
INSERT INTO active_users (name, email, is_active)
VALUES ('Manish', 'manish@example.com', true);

-- WITH CHECK OPTION prevents inserting rows that don't match the view's filter
CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = true
WITH CHECK OPTION;

-- This would FAIL (violates the WHERE condition)
INSERT INTO active_users (name, email, is_active)
VALUES ('Ghost', 'ghost@example.com', false);

Dropping and Modifying Views

-- Replace an existing view (or create if it doesn't exist)
CREATE OR REPLACE VIEW employee_details AS
SELECT e.id, e.name, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- Drop a view
DROP VIEW IF EXISTS employee_details;

Materialized Views

A materialized view stores the query result physically on disk. When we query it, the database reads from the stored result — no recomputation needed.

This is huge for performance when we have expensive queries (complex joins, aggregations over millions of rows) that don’t need to be real-time.

Creating a Materialized View

-- Create a materialized view for a slow analytics query
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS total_orders,
    SUM(amount) AS revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- This query runs ONCE and stores the result
-- Subsequent reads are instant — just reading from stored data
SELECT * FROM monthly_revenue WHERE month >= '2024-01-01';

Refreshing Materialized Views

The data in a materialized view gets stale. We need to refresh it periodically.

-- Full refresh: recomputes the entire view
REFRESH MATERIALIZED VIEW monthly_revenue;

-- Concurrent refresh: allows reads during refresh (needs a UNIQUE index)
CREATE UNIQUE INDEX idx_monthly_revenue_month ON monthly_revenue(month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Refresh Strategies

There are several ways to keep materialized views up to date:

• Manual refresh — trigger it from our app or a cron job after a data load
• Scheduled refresh — use pg_cron or a similar scheduler to refresh every N minutes/hours
• On-demand refresh — refresh when a user requests the dashboard (with a staleness check)
• Trigger-based — use a database trigger to refresh after inserts (careful with performance)

-- Example: refresh every hour using pg_cron (PostgreSQL extension)
-- SELECT cron.schedule('refresh-monthly-revenue', '0 * * * *',
--     'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue');

View vs Materialized View

When to Use Each

Use a regular view when:

• We want to simplify a complex query for reuse
• We need real-time, always-current data
• We want to restrict column access for security
• The underlying query is fast enough

Use a materialized view when:

• The query is expensive (complex joins, heavy aggregations)
• We can tolerate slightly stale data
• It’s for dashboards, reports, or analytics
• The underlying data doesn’t change every second

Interview Tip

Know the difference between views and materialized views cold — it’s a common question. The key distinction: views are virtual (re-run every time), materialized views are physical (stored, need refreshing). Bonus: mention REFRESH MATERIALIZED VIEW CONCURRENTLY as a way to avoid blocking reads during refresh.

Stored procedures and triggers let us run logic inside the database rather than in our application code. They’ve been around for decades, and while modern apps don’t use them as heavily as before, they’re still important to understand.

Stored Procedures

A stored procedure is a block of SQL code that we save in the database and call by name. Think of it like a function — it takes parameters, does some work, and optionally returns results.

Why Use Them?

• Reduce network roundtrips — instead of sending 10 queries from our app, send one procedure call
• Enforce business logic at the database level — the logic runs no matter which app connects
• Security — grant users permission to execute a procedure without giving them direct table access
• Reusability — write once, call from anywhere

Basic Syntax (PostgreSQL)

-- Create a stored procedure to transfer funds
CREATE OR REPLACE PROCEDURE transfer_funds(
    sender_id INT,
    receiver_id INT,
    amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Debit the sender
    UPDATE accounts SET balance = balance - amount
    WHERE id = sender_id;

    -- Check if sender had enough balance
    IF NOT FOUND OR (SELECT balance FROM accounts WHERE id = sender_id) < 0 THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- Credit the receiver
    UPDATE accounts SET balance = balance + amount
    WHERE id = receiver_id;

    -- Log the transaction
    INSERT INTO transaction_log (from_id, to_id, amount, created_at)
    VALUES (sender_id, receiver_id, amount, NOW());
END;
$$;

-- Call the procedure
CALL transfer_funds(1, 2, 500.00);

Functions vs Procedures

In PostgreSQL, there’s a distinction:

-- FUNCTION: returns a value, can be used in SELECT
CREATE OR REPLACE FUNCTION get_employee_count(dept_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    emp_count INT;
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees WHERE department_id = dept_id;
    RETURN emp_count;
END;
$$;

-- Use in a query
SELECT department_id, get_employee_count(department_id) FROM departments;

-- PROCEDURE: doesn't return a value, called with CALL
-- Can manage transactions (COMMIT/ROLLBACK inside)
CALL transfer_funds(1, 2, 500.00);

Triggers

A trigger is a stored procedure that fires automatically when a specific event happens on a table — INSERT, UPDATE, or DELETE. We don’t call it manually; it runs on its own.

BEFORE vs AFTER Triggers

• BEFORE triggers run before the operation. We can modify the data or cancel the operation entirely.
• AFTER triggers run after the operation. Good for logging, auditing, or cascading updates.

-- Trigger function: automatically set updated_at on every UPDATE
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = NOW();  -- NEW refers to the row being inserted/updated
    RETURN NEW;
END;
$$;

-- Attach the trigger to the employees table
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON employees        -- fires BEFORE every UPDATE
    FOR EACH ROW                      -- runs once per row
    EXECUTE FUNCTION update_timestamp();

-- Now any UPDATE on employees automatically sets updated_at
UPDATE employees SET salary = 90000 WHERE id = 1;
-- updated_at is automatically set to NOW() — we didn't have to include it

INSERT, UPDATE, DELETE Triggers

-- Audit log: track who deleted what
CREATE OR REPLACE FUNCTION log_deletion()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, deleted_at)
    VALUES (
        TG_TABLE_NAME,           -- name of the table that fired the trigger
        OLD.id,                  -- OLD refers to the row being deleted/updated
        'DELETE',
        row_to_json(OLD),        -- save the entire deleted row as JSON
        NOW()
    );
    RETURN OLD;
END;
$$;

-- Fire AFTER delete on any important table
CREATE TRIGGER audit_employee_delete
    AFTER DELETE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION log_deletion();

Conditional Triggers

We can add a WHEN clause to only fire the trigger under certain conditions:

-- Only fire when salary actually changes (not on every update)
CREATE TRIGGER salary_change_alert
    AFTER UPDATE ON employees
    FOR EACH ROW
    WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
    EXECUTE FUNCTION notify_salary_change();

Common Trigger Use Cases

• Auto-timestamps — set created_at on INSERT, updated_at on UPDATE
• Audit logging — track all changes to sensitive tables
• Validation — reject invalid data before it’s written
• Cascading updates — update a denormalized counter when related data changes
• Notifications — send a NOTIFY event when something changes (PostgreSQL)

The Downsides

Stored procedures and triggers have fallen out of favor in modern application development. Here’s why:

1. Hidden logic — business rules live in two places (app + database). Debugging is harder.
2. Hard to test — unit testing a trigger is much harder than testing application code.
3. Version control — database code is harder to track in git compared to app code.
4. Portability — PL/pgSQL doesn’t work in MySQL. T-SQL doesn’t work in PostgreSQL. Stored procedure languages are vendor-specific.
5. Scaling — putting logic in the database means the database does more work. It’s harder to scale a database than stateless app servers.
6. ORM friction — ORMs (Prisma, SQLAlchemy, Sequelize) manage schema and queries from the application layer, making stored procedures redundant for most use cases.

Why Modern Apps Use ORMs Instead

ORMs like Prisma, SQLAlchemy, and Sequelize handle most of what stored procedures used to do — all from application code:

-- Instead of a stored procedure for creating an order,
-- the application code handles it:
--
-- async function createOrder(userId, items) {
--   const tx = await prisma.$transaction([
--     prisma.order.create({ data: { userId, total } }),
--     prisma.inventory.updateMany({ ... }),
--     prisma.auditLog.create({ data: { ... } }),
--   ]);
--   return tx;
-- }
--
-- Benefits: testable, in git, uses the same language as the rest of the app

When Stored Procedures/Triggers Still Make Sense

Despite the trend away from them, there are valid use cases:

• Multi-application databases — when multiple apps share one database, enforcing rules at the database level ensures consistency
• Performance-critical operations — eliminating network roundtrips matters for bulk operations
• Audit requirements — regulatory compliance may require database-level audit triggers that can’t be bypassed by application code
• Auto-timestamps — even ORM-heavy apps often use triggers for updated_at because it catches manual SQL updates too

Interview Tip

Interviewers often ask “What are triggers? Give an example.” The updated_at auto-timestamp trigger is the perfect example — it’s simple, practical, and universally understood. If they ask about pros and cons, mentioning the testability and portability issues shows we’ve thought about this in a real-world context, not just theoretically.

Before we write a single line of SQL, we need a blueprint. That blueprint is the Entity-Relationship (ER) Diagram. It’s a visual representation of our data model — what entities exist, what attributes they have, and how they relate to each other.

Think of it like an architect’s floor plan. We don’t start building walls before we know where the rooms, doors, and hallways go.

The Three Building Blocks

1. Entities

An entity is a “thing” we want to store data about. Users, products, orders, departments — these are all entities. Each entity becomes a table in our database.

2. Attributes

Attributes are the properties of an entity. A User entity might have: id, name, email, created_at. Each attribute becomes a column in the table.

• Primary Key — uniquely identifies each row (usually id)
• Required vs Optional — NOT NULL vs nullable
• Derived — computed from other attributes (like age from birth_date)

3. Relationships

How entities connect to each other. A User places Orders. An Order contains Products. These connections are the relationships.

A Simple ER Model

Cardinality — How Many on Each Side?

Cardinality describes how many instances of one entity relate to another. There are three main types:

One-to-One (1:1)

Each row in Table A relates to exactly one row in Table B, and vice versa.

Example: Each user has exactly one profile. Each profile belongs to exactly one user.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(id),  -- UNIQUE makes it 1:1
    bio TEXT,
    avatar_url VARCHAR(500)
);

One-to-one relationships are rare. They’re usually used to split a wide table into two (for performance or security reasons).

One-to-Many (1:N)

One row in Table A can relate to many rows in Table B, but each row in Table B relates to only one row in Table A.

Example: One department has many employees. Each employee belongs to one department.

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT REFERENCES departments(id)  -- FK on the "many" side
);

This is the most common relationship type. The foreign key always goes on the “many” side.

Many-to-Many (M:N)

Rows in Table A can relate to multiple rows in Table B, and vice versa.

Example: One order can contain many products. One product can appear in many orders.

We can’t directly represent this with a foreign key. We need a junction table (also called a bridge table or join table).

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    total DECIMAL(10,2)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

-- Junction table: connects orders and products
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10,2)  -- price at time of purchase (denormalized)
);

Reading an ER Diagram

When we see an ER diagram, here’s how to translate it:

1. Each box becomes a table
2. Each attribute becomes a column
3. Lines between boxes become foreign keys
4. The “1” side doesn’t need anything special
5. The “N” side gets the foreign key column
6. “M:N” relationships need a junction table

Common ER Diagram Notations

There are several notation styles. The two most common are:

• Chen notation — uses diamonds for relationships, ovals for attributes (more academic)
• Crow’s foot notation — uses fork symbols to show cardinality (more practical, industry standard)

In crow’s foot notation:

• A single line means “one”
• A fork (crow’s foot) means “many”
• A circle means “optional” (zero)
• A dash means “mandatory” (one)

Practical Tips for Drawing ER Diagrams

1. Start with entities — identify the nouns (User, Product, Order, Payment)
2. Add relationships — identify the verbs (places, contains, pays for)
3. Determine cardinality — ask “can one X have many Y?” for both directions
4. Add attributes — what do we need to know about each entity?
5. Identify keys — every entity needs a primary key
6. Normalize — check for redundancy and split tables if needed

Tools for Drawing ER Diagrams

• dbdiagram.io — write schema in code, get a visual diagram (free, great for quick work)
• Lucidchart — drag and drop, good for presentations
• draw.io (diagrams.net) — free, versatile, works offline
• pgAdmin — can generate ER diagrams from existing PostgreSQL databases

Interview Tip

If an interviewer asks us to design a schema, always start with an ER diagram (even a rough one on the whiteboard). It shows structured thinking. Identify entities first, then relationships, then attributes. Don’t jump straight into writing CREATE TABLE statements.

Normalization is the process of organizing a database to reduce redundancy and prevent update anomalies. We break one messy table into multiple clean tables, each representing a single concept.

Think of it like organizing a messy closet. Everything is shoved in one pile (the flat table), and we’re sorting it into separate drawers (normalized tables) so things don’t get lost or duplicated.

Why Normalize?

Without normalization, we get three kinds of anomalies:

• Insert anomaly — we can’t add data without unrelated data. (Can’t add a new department without assigning an employee to it.)
• Update anomaly — changing one fact requires updating multiple rows. (Department name changes? Update every employee row.)
• Delete anomaly — deleting data accidentally removes unrelated data. (Delete the last employee in a department? The department itself disappears.)

The Starting Point: A Flat Table

Let’s normalize this mess step by step:

-- The "everything in one table" approach
-- student_id | student_name | course_id | course_name | instructor | instructor_phone
-- 1          | Alice        | CS101     | Databases   | Dr. Smith  | 555-1234
-- 1          | Alice        | CS102     | Networks    | Dr. Jones  | 555-5678
-- 2          | Bob          | CS101     | Databases   | Dr. Smith  | 555-1234
-- 2          | Bob          | CS103     | OS          | Dr. Smith  | 555-1234
-- 3          | Charlie      | CS102     | Networks    | Dr. Jones  | 555-5678

Problems everywhere: Alice’s name is stored twice. Dr. Smith’s phone number is stored three times. Change one, forget the other — data becomes inconsistent.

The Normalization Progression

1NF — First Normal Form

Rule: Every cell must contain a single, atomic value. No lists, no repeating groups, no arrays stuffed into one column.

-- VIOLATES 1NF (courses is a comma-separated list)
-- student_id | student_name | courses
-- 1          | Alice        | CS101, CS102

-- SATISFIES 1NF (one value per cell, one row per enrollment)
-- student_id | student_name | course_id
-- 1          | Alice        | CS101
-- 1          | Alice        | CS102

Also: every table must have a primary key. In our example, the primary key is the combination (student_id, course_id).

2NF — Second Normal Form

Rule: Must be in 1NF AND no partial dependencies. Every non-key column must depend on the entire primary key, not just part of it.

This only matters when we have a composite primary key (two or more columns). If our primary key is a single column, we’re automatically in 2NF.

Look at our table with composite key (student_id, course_id):

-- student_id | student_name | course_id | course_name | instructor
-- PK: (student_id, course_id)

-- student_name depends on student_id ALONE (partial dependency!)
-- course_name depends on course_id ALONE (partial dependency!)
-- Both violate 2NF because they don't need the FULL composite key

To fix: move partially dependent columns to their own tables.

-- Students table (student_name depends only on student_id)
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100)
);

-- Courses table (course_name, instructor depend only on course_id)
CREATE TABLE courses (
    course_id VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(100)
);

-- Enrollments table (the relationship)
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id VARCHAR(10) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Now each non-key column depends on the full primary key of its table.

3NF — Third Normal Form

Rule: Must be in 2NF AND no transitive dependencies. Non-key columns must depend directly on the primary key, not through another non-key column.

In simple language: if A determines B, and B determines C, then C shouldn’t be in the same table as A. Move C to B’s table.

-- Our courses table still has a problem:
-- course_id → instructor → instructor_phone
-- instructor_phone depends on instructor, NOT on course_id directly
-- That's a transitive dependency!

-- course_id | course_name | instructor | instructor_phone
-- CS101     | Databases   | Dr. Smith  | 555-1234
-- CS103     | OS          | Dr. Smith  | 555-1234   ← phone duplicated!

To fix: move instructor details to their own table.

-- Instructors table
CREATE TABLE instructors (
    instructor_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20)
);

-- Courses table (references instructor by FK)
CREATE TABLE courses (
    course_id VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(100),
    instructor_id INT REFERENCES instructors(instructor_id)
);

Now instructor_phone depends directly on instructor_id in its own table. No transitive dependency.

BCNF — Boyce-Codd Normal Form

Rule: Must be in 3NF AND every determinant must be a candidate key. This is a stricter version of 3NF that handles some edge cases.

In simple language: for every functional dependency X -> Y, X must be a superkey (capable of being a primary key).

BCNF and 3NF are the same in most practical cases. The difference only shows up with overlapping composite candidate keys — which is pretty rare.

-- A rare BCNF violation example:
-- A student can only have one advisor per subject
-- An advisor teaches only one subject

-- student | subject | advisor
-- Alice   | DB      | Dr. Smith     (advisor → subject)
-- Bob     | DB      | Dr. Smith
-- Alice   | Net     | Dr. Jones

-- Candidate keys: (student, subject) and (student, advisor)
-- advisor → subject is a dependency where advisor is NOT a superkey
-- This violates BCNF!

-- Fix: split into two tables
-- student_advisor: student | advisor
-- advisor_subject:  advisor | subject

The Full Normalized Schema

After all that work, our original flat table is now four clean tables:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE instructors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20)
);

CREATE TABLE courses (
    id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    instructor_id INT REFERENCES instructors(id)
);

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id VARCHAR(10) REFERENCES courses(id),
    enrolled_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)
);

No redundancy. Change an instructor’s phone in one place, it’s updated everywhere. Delete a student, courses still exist. Add a new course without needing a student. All anomalies gone.

How Far Should We Normalize?

Most real-world databases aim for 3NF. Going beyond that (BCNF, 4NF, 5NF) is rarely needed and can make queries slower due to excessive JOINs.

The practical rule: normalize until the redundancy is gone, then stop. If performance suffers from too many JOINs, we can strategically denormalize (which we cover in the next note).

Interview Tip

Normalization is a classic DBMS interview topic. Walk through the example step by step: start with a messy flat table, explain the anomalies, then normalize through 1NF, 2NF, and 3NF. The key phrases to remember: “atomic values” (1NF), “no partial dependencies” (2NF), “no transitive dependencies” (3NF). If asked about BCNF, mention it’s a stricter 3NF where every determinant must be a candidate key.

We just spent an entire note learning how to normalize — remove redundancy, split tables, eliminate anomalies. And now we’re going to talk about intentionally adding redundancy back in. Welcome to denormalization.

In simple language: denormalization is the trade-off of faster reads at the cost of slower writes and more storage. We duplicate data so we don’t have to JOIN five tables for a simple query.

Why Denormalize?

Normalization is great for data integrity. But it creates a lot of tables, which means a lot of JOINs. JOINs are expensive. When our app needs to serve a dashboard that joins 8 tables with millions of rows, normalization can kill performance.

Denormalization helps when:

• Read performance matters more than write performance (most web apps)
• Query complexity is getting out of hand (too many JOINs)
• Analytics/reporting needs fast aggregations
• The data doesn’t change often (or we can tolerate slightly stale data)

The key insight: normalize first, then denormalize strategically where needed. Never start denormalized — we’ll end up with a mess.

Common Denormalization Patterns

1. Duplicated Fields

Store a frequently accessed value from a related table directly in the current table.

-- Normalized: need to JOIN to get the department name
SELECT o.id, o.total, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- Denormalized: customer name stored directly on the order
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);

-- Now we can query without a JOIN
SELECT id, total, customer_name FROM orders;

-- Trade-off: if the customer changes their name, we need to
-- update it in both the customers table AND every order

2. Precomputed/Cached Counters

Instead of running COUNT(*) every time, maintain a counter column.

-- Normalized: count posts every time (slow on large tables)
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.name;

-- Denormalized: maintain a counter
ALTER TABLE users ADD COLUMN post_count INT DEFAULT 0;

-- Increment on new post
UPDATE users SET post_count = post_count + 1 WHERE id = :user_id;

-- Decrement on deleted post
UPDATE users SET post_count = post_count - 1 WHERE id = :user_id;

-- Now the count is instant — no JOIN, no aggregation
SELECT name, post_count FROM users WHERE id = 1;

This is extremely common. Think of follower counts on social media — nobody runs COUNT(*) on a 100M row followers table for every profile view.

3. Summary Tables

Pre-aggregate data into a separate table for reporting.

-- Instead of computing daily revenue from millions of orders each time:
CREATE TABLE daily_revenue (
    date DATE PRIMARY KEY,
    total_orders INT,
    total_revenue DECIMAL(12,2),
    avg_order_value DECIMAL(10,2)
);

-- Populate it nightly (or in real-time with triggers)
INSERT INTO daily_revenue (date, total_orders, total_revenue, avg_order_value)
SELECT
    DATE(ordered_at),
    COUNT(*),
    SUM(total),
    AVG(total)
FROM orders
WHERE DATE(ordered_at) = CURRENT_DATE - 1
GROUP BY DATE(ordered_at);

4. Computed Columns

Store a value that could be derived, to avoid computing it every time.

-- Instead of computing full_name every time:
ALTER TABLE employees ADD COLUMN full_name VARCHAR(200);

-- Or instead of computing age from birth_date:
-- (This is a bad example actually — age changes daily,
-- so we'd just compute it. But for things like total_price
-- on an order line item, it makes sense.)

ALTER TABLE order_items ADD COLUMN total_price DECIMAL(10,2);
-- total_price = quantity * unit_price
-- Computed once at insert time, read millions of times

5. Embedding/Flattening Relationships

Instead of a junction table, store related data directly (common in NoSQL but applicable to SQL too).

-- Instead of: orders → order_items → products (3 tables, 2 JOINs)
-- Store order items as JSON:
ALTER TABLE orders ADD COLUMN items JSONB;

-- Insert:
-- UPDATE orders SET items = '[
--   {"product_id": 1, "name": "Widget", "qty": 2, "price": 9.99},
--   {"product_id": 3, "name": "Gadget", "qty": 1, "price": 24.99}
-- ]' WHERE id = 1;

-- Query without JOINs:
SELECT id, total, items FROM orders WHERE id = 1;

The Trade-offs

When Denormalization Makes Sense

• Read-heavy workloads — 99% reads, 1% writes (most web apps)
• Analytics and reporting — dashboards that aggregate millions of rows
• Caching layers — materialized views and summary tables act as “database-level caches”
• NoSQL databases — document stores are denormalized by design (embed related data)
• Search/listing pages — product listings, search results, feeds

When It Doesn’t Make Sense

• Write-heavy workloads — every write needs to update multiple places
• Frequently changing data — keeping denormalized copies in sync becomes a nightmare
• Small datasets — JOINs on small tables are fast. Don’t optimize prematurely.
• Data integrity is critical — banking, healthcare, anything where inconsistency is unacceptable

How to Keep Denormalized Data in Sync

1. Triggers — automatically update denormalized fields when source data changes
2. Application logic — update both the source and the copy in the same transaction
3. Background jobs — periodically refresh summary tables (materialized views)
4. Event-driven — publish change events, subscribers update denormalized copies

Interview Tip

Interviewers love asking “when would you denormalize?” The answer is always: “when read performance is more important than write simplicity, and we’ve already normalized properly.” Give a concrete example like cached counters (follower count, like count) or summary tables for dashboards. Show that we understand it’s a deliberate trade-off, not laziness.

Keys are the backbone of relational databases. They uniquely identify rows, link tables together, and enforce data integrity. Without keys, a relational database is just a bunch of spreadsheets with no connections.

Primary Keys

A primary key uniquely identifies each row in a table. No two rows can have the same primary key, and it can never be NULL.

Natural Keys vs Surrogate Keys

A natural key uses a real-world value that’s already unique — like an email address or a Social Security Number.

A surrogate key is an artificial value we generate — like an auto-incrementing integer or a UUID.

-- Natural key: using email (it's already unique)
CREATE TABLE users (
    email VARCHAR(255) PRIMARY KEY,
    name VARCHAR(100)
);

-- Surrogate key: auto-incrementing integer
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- 1, 2, 3, 4...
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);

-- Surrogate key: UUID
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);

Auto-increment vs UUID — When to Use Which?

Rule of thumb: Use auto-increment for internal IDs. Use UUIDs when IDs are exposed in URLs or when working with distributed databases.

Foreign Keys

A foreign key is a column that references the primary key of another table. It’s how we create relationships between tables and enforce referential integrity.

Referential integrity means: if a row in the orders table says user_id = 5, there MUST be a user with id = 5 in the users table. The database enforces this automatically.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    total DECIMAL(10,2),
    -- Foreign key constraint
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);

-- This WORKS (user 1 exists)
INSERT INTO users (id, name) VALUES (1, 'Manish');
INSERT INTO orders (user_id, total) VALUES (1, 99.99);

-- This FAILS (user 999 doesn't exist)
INSERT INTO orders (user_id, total) VALUES (999, 49.99);
-- ERROR: insert or update violates foreign key constraint "fk_user"

ON DELETE Behavior

What happens when we delete a parent row that has child rows referencing it? We have several options:

-- RESTRICT (default): prevent deletion if references exist
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
-- "Can't delete this user — they have orders!"

-- CASCADE: delete child rows too
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
-- Delete the user → all their orders are automatically deleted too

-- SET NULL: set the FK to NULL
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
-- Delete the user → orders remain but user_id becomes NULL

-- SET DEFAULT: set the FK to its default value
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET DEFAULT
-- Delete the user → user_id is set to whatever DEFAULT is defined

Similarly, ON UPDATE CASCADE is useful when the referenced key might change:

-- If user's id changes, update all orders that reference it
FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

Which ON DELETE to Use?

• CASCADE — when child data is meaningless without the parent (order items when an order is deleted)
• RESTRICT — when deletion should be blocked (can’t delete a department if employees are assigned)
• SET NULL — when the relationship is optional (the comment still exists, but the author is gone)

Composite Keys

A composite key is a primary key made of two or more columns together. Neither column is unique on its own, but the combination is.

-- A student can enroll in many courses
-- A course can have many students
-- The combination (student_id, course_id) is unique

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    enrolled_at TIMESTAMP DEFAULT NOW(),
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)  -- composite key
);

-- Student 1 can enroll in Course 101 and Course 102
-- But Student 1 can't enroll in Course 101 twice

Junction Tables for Many-to-Many

We can’t directly represent a many-to-many relationship with a foreign key. We need a junction table (also called a bridge table, join table, or linking table).

-- Students ←→ Courses (many-to-many)
-- A student takes many courses. A course has many students.

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200)
);

-- Junction table: the "glue" between them
CREATE TABLE student_courses (
    student_id INT REFERENCES students(id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)
);

-- Enroll student 1 in courses 1 and 2
INSERT INTO student_courses (student_id, course_id) VALUES (1, 1);
INSERT INTO student_courses (student_id, course_id) VALUES (1, 2);

-- Find all courses for a student
SELECT c.title
FROM courses c
JOIN student_courses sc ON c.id = sc.course_id
WHERE sc.student_id = 1;

Junction tables can also carry extra data about the relationship itself — like when the enrollment happened, what grade was received, etc.

Unique Constraints

Besides the primary key, we often need other columns to be unique:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,              -- single column unique
    username VARCHAR(50) UNIQUE,
    UNIQUE (first_name, last_name)          -- composite unique (rare)
);

The difference between a UNIQUE constraint and a PRIMARY KEY:

• A table can have many UNIQUE constraints but only one PRIMARY KEY
• UNIQUE columns can be NULL (and multiple NULLs are allowed in most databases)
• PRIMARY KEY = UNIQUE + NOT NULL

Check Constraints

While we’re talking about constraints, CHECK constraints are worth mentioning:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),           -- must be positive
    discount DECIMAL(3,2) CHECK (discount BETWEEN 0 AND 1),  -- 0-100%
    status VARCHAR(20) CHECK (status IN ('active', 'archived', 'draft'))
);

Interview Tip

When designing a schema in an interview, always start by identifying the relationships and their cardinalities. For 1:N, put the FK on the “many” side. For M:N, create a junction table. Know the ON DELETE options (CASCADE, RESTRICT, SET NULL) and when to use each. And be ready to explain natural vs surrogate keys — most interviewers have a preference and like to debate it.

Schema design isn’t just about normalization. There are well-known patterns that solve specific problems — analytics, flexibility, deletion, and polymorphism. Let’s walk through the most important ones.

Star Schema

The star schema is the bread and butter of data warehousing and analytics. It has two types of tables:

• Fact table — stores the events/transactions (sales, clicks, orders). Contains foreign keys to dimension tables and numeric measures (amount, quantity, duration).
• Dimension tables — stores the context (who, what, where, when). Contains descriptive attributes.

The fact table sits in the center, surrounded by dimension tables — like a star.

-- Fact table: one row per sale
CREATE TABLE fact_sales (
    id SERIAL PRIMARY KEY,
    date_id INT REFERENCES dim_date(id),
    customer_id INT REFERENCES dim_customer(id),
    product_id INT REFERENCES dim_product(id),
    store_id INT REFERENCES dim_store(id),
    quantity INT,
    amount DECIMAL(10,2),
    discount DECIMAL(5,2)
);

-- Dimension table: date details (precomputed for fast queries)
CREATE TABLE dim_date (
    id INT PRIMARY KEY,           -- e.g., 20240315 for March 15, 2024
    full_date DATE,
    year INT,
    quarter INT,
    month INT,
    month_name VARCHAR(20),
    day_of_week VARCHAR(10),
    is_weekend BOOLEAN
);

-- Queries are fast — just one JOIN per dimension
SELECT
    d.year, d.month_name,
    p.category,
    SUM(f.amount) AS total_revenue
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.id
JOIN dim_product p ON f.product_id = p.id
GROUP BY d.year, d.month_name, p.category;

When to use: Data warehouses, BI dashboards, analytics systems. Denormalized by design for query speed.

Snowflake Schema

A snowflake schema is like a star schema, but the dimension tables are normalized — they reference additional lookup tables.

Instead of dim_product having a category column, it has a category_id that references a separate dim_category table.

-- Star: product has category as a string
-- dim_product: id, name, category, brand

-- Snowflake: product references a category table
CREATE TABLE dim_category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

CREATE TABLE dim_product (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category_id INT REFERENCES dim_category(id),
    brand VARCHAR(100)
);

Star vs Snowflake: Star is simpler and faster (fewer JOINs). Snowflake saves storage and avoids redundancy. Most data warehouses prefer star for its simplicity.

EAV — Entity-Attribute-Value

The EAV pattern stores data as rows of (entity, attribute, value) triples instead of fixed columns. It’s the most flexible schema possible — we can add any attribute to any entity without changing the table structure.

-- Traditional approach: every product type needs different columns
-- Electronics: screen_size, battery, processor
-- Clothing: size, color, material
-- Books: author, isbn, pages
-- Maintaining one table per type (or one wide table) gets messy fast

-- EAV approach: three columns handle everything
CREATE TABLE product_attributes (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id),
    attribute_name VARCHAR(100),   -- "screen_size", "color", "author"
    attribute_value TEXT            -- everything stored as text
);

-- Insert attributes for a laptop
INSERT INTO product_attributes (product_id, attribute_name, attribute_value) VALUES
(1, 'screen_size', '15.6'),
(1, 'processor', 'M2'),
(1, 'ram', '16GB');

-- Insert attributes for a t-shirt
INSERT INTO product_attributes (product_id, attribute_name, attribute_value) VALUES
(2, 'size', 'L'),
(2, 'color', 'Blue'),
(2, 'material', 'Cotton');

The downsides of EAV are significant:

• No type safety (everything is a string)
• Can’t add constraints (can’t enforce “screen_size must be a number”)
• Queries become ugly (need to PIVOT to get a row-per-entity view)
• Performance suffers at scale

When to use: Truly dynamic attributes where the schema is unknowable upfront. Product catalogs with wildly different product types. User-defined custom fields. Consider JSONB columns in PostgreSQL as a better modern alternative.

Polymorphic Associations

When multiple tables need to relate to the same table. For example: both posts and comments can have “likes.” Instead of a separate likes table for each, we use one table with a type column.

-- Polymorphic likes table
CREATE TABLE likes (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    likeable_type VARCHAR(50) NOT NULL,  -- 'post' or 'comment'
    likeable_id INT NOT NULL,            -- id in the respective table
    created_at TIMESTAMP DEFAULT NOW()
);

-- Like a post
INSERT INTO likes (user_id, likeable_type, likeable_id) VALUES (1, 'post', 42);

-- Like a comment
INSERT INTO likes (user_id, likeable_type, likeable_id) VALUES (1, 'comment', 99);

-- Get all likes on a post
SELECT * FROM likes WHERE likeable_type = 'post' AND likeable_id = 42;

The problem: We can’t use a foreign key constraint because likeable_id could reference different tables. Referential integrity relies on application code.

Alternatives:

• Separate tables: post_likes and comment_likes (cleaner, FK constraints work)
• Shared parent: make both posts and comments inherit from a content table

Soft Deletes

Instead of actually deleting a row with DELETE, we mark it as deleted with a timestamp column. The data stays in the table but is hidden from normal queries.

-- Add a soft delete column
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP DEFAULT NULL;

-- "Delete" a user (soft)
UPDATE users SET deleted_at = NOW() WHERE id = 5;

-- All queries must filter out deleted rows
SELECT * FROM users WHERE deleted_at IS NULL;

-- Create a view for convenience
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

-- To "undelete" — just clear the timestamp
UPDATE users SET deleted_at = NULL WHERE id = 5;

Benefits:

• Data is recoverable (undo delete)
• Audit trail (when was it deleted?)
• Referential integrity preserved (foreign keys don’t break)
• Legal/compliance requirements (retain data for N years)

Downsides:

• Every query needs the WHERE deleted_at IS NULL filter (easy to forget)
• Table grows forever (need periodic cleanup)
• Unique constraints get tricky (two “deleted” users can have the same email)

-- Fix the unique constraint issue with a partial unique index
CREATE UNIQUE INDEX idx_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;
-- Only enforces uniqueness among non-deleted rows

When to Use Each Pattern

Interview Tip

Schema design patterns come up in system design interviews. When designing a data model, mention the pattern by name — “I’d use a star schema for the analytics layer” or “I’d implement soft deletes here for audit compliance.” It shows we’ve seen real production systems and know the trade-offs of each approach.

A database migration is a versioned, trackable change to our database schema. Instead of manually running ALTER TABLE on production (please don’t), we write migration files that describe exactly what changed and in what order.

Think of it like git for our database schema. Each migration is a commit — we can see the history, roll forward, and roll back.

Why We Need Migrations

Without migrations, schema changes become chaos:

• “Did you run the ALTER TABLE on production?”
• “Which version of the schema does this server have?”
• “Someone added a column but didn’t tell the team.”
• “The staging database has a different schema than production.”

Migrations solve all of this by making schema changes code. They live in git, get reviewed in PRs, and run in order.

Up and Down Migrations

Every migration has two parts:

• Up — applies the change (add column, create table, create index)
• Down — reverses the change (drop column, drop table, drop index)

-- Migration: 001_create_users_table.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Migration: 001_create_users_table.down.sql
DROP TABLE IF EXISTS users;

-- Migration: 002_add_phone_to_users.up.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Migration: 002_add_phone_to_users.down.sql
ALTER TABLE users DROP COLUMN phone;

The migration tool tracks which migrations have been applied (usually in a schema_migrations table) and only runs the ones that haven’t been applied yet.

How Migration Tools Work

The migration tool:

1. Checks the schema_migrations table to see what’s been run
2. Finds new migration files that haven’t been applied
3. Runs them in order
4. Records each successful migration

Popular Migration Tools

-- Example: Knex.js migration file
-- exports.up = function(knex) {
--   return knex.schema.createTable('users', (table) => {
--     table.increments('id');
--     table.string('name').notNullable();
--     table.string('email').unique().notNullable();
--     table.timestamps(true, true);
--   });
-- };
--
-- exports.down = function(knex) {
--   return knex.schema.dropTable('users');
-- };

Zero-Downtime Migrations

The hardest part of migrations isn’t writing them — it’s running them on a production database with live traffic and zero downtime.

The Expand-Contract Pattern

This is the gold standard for zero-downtime schema changes. It works in three phases:

Phase 1: Expand — add the new structure without removing the old one

-- We want to rename "name" to "full_name"
-- DON'T: ALTER TABLE users RENAME COLUMN name TO full_name;
-- (This breaks all queries using "name" instantly)

-- DO: Add the new column alongside the old one
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

-- Backfill existing data
UPDATE users SET full_name = name WHERE full_name IS NULL;

Phase 2: Migrate — update application code to use the new column. Deploy the app.

Phase 3: Contract — once all code uses the new column, remove the old one.

-- Only after the app is fully migrated:
ALTER TABLE users DROP COLUMN name;

Adding a Column

Adding a nullable column is usually safe. Adding a NOT NULL column or a column with a DEFAULT can lock the table in some databases.

-- Safe: add a nullable column (instant in PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;

-- Dangerous in older databases: adding with DEFAULT
-- This rewrites every row in the table!
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Safe alternative: add nullable first, then backfill
ALTER TABLE users ADD COLUMN status VARCHAR(20);
UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

Adding an Index

Creating an index on a large table can lock it for minutes or hours. Use CONCURRENTLY:

-- Blocks reads/writes during creation (bad!)
CREATE INDEX idx_users_email ON users(email);

-- Doesn't block other operations (good! But slower to build)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Common Pitfalls

1. Dropping a Column That’s Still Being Read

If the old code is still running when we drop a column, queries break. Always deploy the code change first, then drop the column in a later migration.

2. Renaming a Table or Column

A rename is effectively a drop + create. The old name stops working instantly. Use the expand-contract pattern instead.

3. Not Testing Migrations on a Copy of Production

A migration that works on a dev database with 100 rows might lock a production table with 50 million rows for 30 minutes. Always test with production-like data volumes.

4. Making Migrations Non-Idempotent

A migration should be safe to run twice. Use IF NOT EXISTS and IF EXISTS:

-- Idempotent: safe to run multiple times
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20);

DROP INDEX IF EXISTS idx_users_email;

5. Mixing Schema Changes and Data Changes

Keep schema migrations and data migrations separate. Schema changes are usually fast and reversible. Data backfills can be slow and should run as background jobs.

Migration Best Practices

1. One change per migration — don’t create 5 tables in one file
2. Always write a down migration — even if we think we’ll never need it
3. Never modify an already-applied migration — create a new one instead
4. Test on staging first — with production-like data volumes
5. Use transactions — wrap the migration in BEGIN/COMMIT so it’s atomic (where supported)
6. Review migrations in PRs — schema changes deserve as much scrutiny as code changes

Interview Tip

Migrations come up in system design and backend interviews. The key thing to demonstrate is awareness of the zero-downtime problem — that you can’t just ALTER TABLE on a live database with millions of users. Mention the expand-contract pattern and CREATE INDEX CONCURRENTLY. That shows real production experience.

An index in a database is like the index at the back of a textbook. Instead of flipping through every single page to find “B-Tree”, we look it up in the index, get the page number, and jump straight there.

Without an index, the database has to scan every row in the table to find what we’re looking for. That’s called a full table scan (or sequential scan). For a table with millions of rows, that’s painfully slow.

With an index, the database maintains a separate data structure (usually a B+ Tree) that maps column values to the physical location of the rows. So instead of checking every row, it narrows down to just the matching ones.

The Trade-off: Reads vs Writes

Here’s the thing — indexes aren’t free. Every time we INSERT, UPDATE, or DELETE a row, the database also has to update every index on that table.

Think of it like maintaining a book index. If we add a new chapter, we have to update the index too. More indexes = more maintenance work.

Clustered vs Non-Clustered Indexes

A clustered index determines the physical order of data on disk. Think of it like a phone book — the entries are physically sorted by last name. A table can have only one clustered index because the data can only be physically sorted one way.

In most databases, the primary key automatically creates a clustered index. In PostgreSQL, the table data isn’t automatically kept in order, but we can use CLUSTER to physically reorder it.

A non-clustered index is a separate structure that points back to the actual rows. Think of it like the index at the back of a book — it’s separate from the content, and just tells us where to look.

-- Primary key creates a clustered index automatically (in MySQL InnoDB)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- clustered index on id
    email VARCHAR(255),
    name VARCHAR(100)
);

-- Non-clustered index on email
CREATE INDEX idx_users_email ON users(email);

-- Now this query uses the index instead of scanning every row
SELECT * FROM users WHERE email = 'manish@example.com';