🧠 Simple Definition

A Primary Key is a column (or set of columns) that uniquely identifies every row in a table. It cannot be null and must be unique across all rows. A Foreign Key is a column in one table that references a Primary Key in another table, creating a relationship and enforcing referential integrity.

🧪 SQL Example

-- Primary key: uniquely identifies each user
CREATE TABLE users (
  id       INT          PRIMARY KEY,  -- ← Primary Key (auto-unique, not null)
  email    VARCHAR(255) UNIQUE NOT NULL,
  name     VARCHAR(100) NOT NULL
);

-- Foreign key: orders.user_id references users.id
CREATE TABLE orders (
  id       INT  PRIMARY KEY,
  user_id  INT  NOT NULL,
  amount   DECIMAL(10, 2),

  FOREIGN KEY (user_id) REFERENCES users(id)  -- ← Foreign Key
  ON DELETE CASCADE  -- if user deleted, delete their orders too
);

🧪 Prisma Schema Example

model User {
  id     Int     @id @default(autoincrement())  // ← Primary Key
  email  String  @unique
  orders Order[]
}

model Order {
  id     Int   @id @default(autoincrement())
  amount Float
  userId Int
  user   User  @relation(fields: [userId], references: [id])  // ← Foreign Key
}

📊 Key Properties

Property	Primary Key	Foreign Key
Uniqueness	✅ Must be unique	❌ Can repeat (many orders per user)
NULL allowed	❌ Never NULL	⚠️ Depends (can be nullable for optional relations)
Index	✅ Automatically indexed	✅ Should be indexed (for JOIN performance)
One per table	✅ One PK per table	❌ Can have multiple FKs
Purpose	Row identification	Relationship + referential integrity

🔑 Foreign Key Actions

Action	ON DELETE	ON UPDATE
`CASCADE`	Delete child rows when parent deleted	Update child FK when parent PK updated
`SET NULL`	Set FK to NULL when parent deleted	Set FK to NULL when parent PK updated
`RESTRICT`	Prevent parent deletion if children exist	Prevent parent update if children exist
`NO ACTION`	Default — like RESTRICT (checked at end of TX)	Default

🌍 Composite Primary Key

-- Junction table for many-to-many relationship
CREATE TABLE user_roles (
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY (user_id, role_id),  -- ← Composite PK
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

💡 Possible Follow-up Questions

What is a composite primary key and when would you use one?
What is referential integrity and how do foreign keys enforce it?
What is the difference between CASCADE DELETE and RESTRICT?
What is a surrogate key vs a natural key?
Why should you index foreign key columns?

⚡ One-line Interview Answer

A primary key uniquely identifies each row in a table (enforces uniqueness and non-null constraint, automatically indexed). A foreign key in one table references a primary key in another, creating a relationship and enforcing referential integrity — controlling what happens to child rows when the parent is deleted or updated (CASCADE, SET NULL, RESTRICT).

🧠 Simple Definition

WHERE filters rows before any grouping or aggregation happens. HAVING filters groups after GROUP BY aggregation. You cannot use aggregate functions (COUNT, SUM, AVG) in a WHERE clause — that's what HAVING is for.

⚡ Super Simple Line

WHERE = filter individual rows (before grouping).
HAVING = filter aggregate results (after grouping).
WHERE can't use SUM/COUNT. HAVING can.

🧪 Example: Both in Same Query

SELECT
  department,
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'           -- ← WHERE: filter ROWS first (active only)
GROUP BY department
HAVING COUNT(*) > 5               -- ← HAVING: filter GROUPS (only depts with 6+ people)
   AND AVG(salary) > 60000;       -- ← HAVING: aggregate condition on the group

Execution order:

FROM employees (get all rows)
WHERE status = 'active' (filter to active rows)
GROUP BY department (form groups)
HAVING COUNT(*) > 5 (filter groups)
SELECT (compute final output)

❌ Common Mistake: Using Aggregate in WHERE

-- ❌ WRONG — cannot use aggregate in WHERE
SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 5   -- ERROR: aggregate functions not allowed in WHERE
GROUP BY department;

-- ✅ CORRECT — use HAVING for aggregate conditions
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

📊 WHERE vs HAVING

Feature	WHERE	HAVING
When it filters	Before GROUP BY	After GROUP BY
Filters	Individual rows	Groups (aggregated results)
Aggregate functions	❌ Not allowed	✅ Allowed (COUNT, SUM, AVG)
Can reference aliases	❌ No (evaluated before SELECT)	❌ No (depends on DB)
Performance	✅ Faster (reduces rows early)	Slower (aggregates all first)
Works without GROUP BY	✅ Yes	Rarely

🌍 Real-World Examples

-- Find categories with more than 10 active products
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE status = 'active'          -- WHERE: filter to active products only
GROUP BY category_id
HAVING COUNT(*) > 10;            -- HAVING: only categories with 10+ active products

-- Find customers with total orders over $1000
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE created_at > '2024-01-01'  -- WHERE: recent orders only
GROUP BY customer_id
HAVING SUM(amount) > 1000;       -- HAVING: only high-value customers

💡 Possible Follow-up Questions

What is the SQL query execution order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY)?
Can you use a WHERE clause without a GROUP BY?
Why is WHERE more efficient than HAVING for row filtering?
Can you use column aliases in HAVING? (PostgreSQL: yes, MySQL: sometimes)
What is the difference between HAVING COUNT(*) and WHERE COUNT(*)?

🧠 Simple Definition

SQL JOINs combine rows from two or more tables based on a matching column (usually a foreign key relationship). The type of JOIN determines which rows are included when there's no match between tables.

⚡ Super Simple Line

INNER = only matching rows from both tables.
LEFT = all left rows + matched right rows (NULL if no right match).
RIGHT = all right rows + matched left rows (NULL if no left match).
FULL OUTER = all rows from both sides (NULLs where no match).
CROSS = every combination of every row (cartesian product).

🧪 Sample Data Setup

-- Customers table
| id | name    |
|----|---------|
| 1  | Alice   |
| 2  | Bob     |
| 3  | Charlie |

-- Orders table
| id | customer_id | amount |
|----|-------------|--------|
| 1  | 1           | 100    |
| 2  | 1           | 250    |
| 3  | 2           | 75     |
-- Note: Charlie (id=3) has no orders

🧪 JOIN Examples

-- 1. INNER JOIN: only customers who have orders
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- Result: Alice, Bob only (Charlie excluded)

-- 2. LEFT JOIN: ALL customers, even those without orders
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- Result: Alice, Bob, Charlie (Charlie has NULL amount)

-- 3. RIGHT JOIN: ALL orders, even if no matching customer
SELECT c.name, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- 4. FULL OUTER JOIN: everything from both sides
SELECT c.name, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

-- 5. SELF JOIN: join a table with itself
SELECT a.name AS employee, b.name AS manager
FROM employees a
JOIN employees b ON a.manager_id = b.id;

📊 Visual Comparison

JOIN Type	Left Rows	Right Rows	Unmatched
INNER JOIN	Matching only	Matching only	Discarded both sides
LEFT JOIN	All rows	Matching only	NULL on right side
RIGHT JOIN	Matching only	All rows	NULL on left side
FULL OUTER JOIN	All rows	All rows	NULL on both sides
CROSS JOIN	All rows	All rows	Cartesian product

🌍 Real-World Use Cases

INNER JOIN: Get all orders with their customer info (both must exist)
LEFT JOIN: Get all users, show their last login if it exists (users without logins still appear)
FULL OUTER JOIN: Find unmatched rows on both sides (data integrity checks)
SELF JOIN: Employee hierarchy tree (employee → manager)

💡 Possible Follow-up Questions

What is the difference between INNER JOIN and WHERE with multiple tables?
When would you use a CROSS JOIN?
What is a self-join and give a use case?
How do NULLs affect JOIN results?
How does JOIN performance depend on indexes?

⚡ One-line Interview Answer

INNER JOIN returns only rows matching in both tables; LEFT JOIN returns all left-table rows with NULLs for unmatched right-table columns; FULL OUTER JOIN returns all rows from both tables with NULLs where matches don't exist on either side.

🧠 Simple Definition

A subquery (also called an inner query or nested query) is a SQL query embedded inside another SQL query. The inner query executes first and its result is used by the outer query. Subqueries can appear in SELECT, WHERE, FROM, and HAVING clauses.

🧪 Types of Subqueries

1. Scalar Subquery — Returns a single value

-- Find employees earning above average salary
SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees  -- ← scalar: returns one number
);

2. Row Subquery — Returns a single row

SELECT * FROM products
WHERE (category_id, price) = (
  SELECT category_id, MIN(price) FROM products GROUP BY category_id LIMIT 1
);

3. Table Subquery (Derived Table) — Returns multiple rows/columns

-- Subquery in FROM clause (like a temporary table)
SELECT dept_avg.department, dept_avg.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avg  -- ← named derived table
WHERE dept_avg.avg_salary > 60000;

4. Correlated Subquery — References outer query

-- Find employees earning more than their department's average
SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department  -- ← references outer query's e.department
);
-- Note: Correlated subqueries run ONCE PER ROW — can be slow on large tables

5. EXISTS Subquery — Check existence

-- Find customers who have placed at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- EXISTS stops at first match — more efficient than IN for large sets

📊 Subquery vs JOIN vs CTE

Feature	Subquery	JOIN	CTE
Readability	Medium	High	✅ Highest
Reusability in same query	❌ Repeat each time	❌	✅ Reference multiple times
Performance	Depends on optimizer	Usually best	Similar to subquery
Recursive queries	❌	❌	✅ RECURSIVE CTEs

⚠️ Performance Note

Non-correlated subqueries: run once, result cached → generally OK
Correlated subqueries: run once per outer row → can be very slow on large tables
Modern query optimizers (PostgreSQL) often rewrite subqueries as JOINs automatically
Use EXPLAIN ANALYZE to check if subquery is causing a performance problem

💡 Possible Follow-up Questions

What is a correlated subquery and why can it be slow?
What is the difference between IN and EXISTS?
When would you use a subquery vs a CTE?
Can a subquery be used in an UPDATE or DELETE statement?
How does the database optimizer handle subqueries?

⚡ One-line Interview Answer

A subquery is a query nested inside another query where the inner query executes first and its result is used by the outer query. Types include scalar (single value in WHERE), derived table (in FROM clause), and correlated (references outer query's row — runs once per outer row, so can be slow on large datasets and often better replaced with a JOIN or CTE).

🧠 Simple Definition

A CTE (Common Table Expression) is a named temporary result set defined at the start of a query using the WITH keyword. It exists only for the duration of the query and can be referenced like a table. CTEs make complex queries more readable, reusable, and support recursive queries for hierarchical data.

⚡ Super Simple Line

CTE = a named temporary "virtual table" you define once at the top and reuse multiple times in the same query. Like a variable for a SQL result set.

🧪 Basic CTE Syntax

WITH cte_name AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.name, e.salary, c.avg_salary
FROM employees e
JOIN cte_name c ON e.department = c.department
WHERE e.salary > c.avg_salary;  -- employees earning above their dept average

🧪 Multiple CTEs (Chained)

WITH
  active_users AS (
    SELECT * FROM users WHERE active = true
  ),
  high_spenders AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 1000
  )
SELECT u.name, h.total
FROM active_users u
JOIN high_spenders h ON u.id = h.user_id;
-- Active users who spent more than $1000

🧪 Recursive CTE (Hierarchy / Tree)

-- Find all employees in a management chain
WITH RECURSIVE org_chart AS (
  -- Anchor: start with CEO (no manager)
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find each person's reports
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org_chart o ON e.manager_id = o.id
)
SELECT name, depth FROM org_chart ORDER BY depth;
-- Outputs every employee with their level in the hierarchy

📊 CTE vs Subquery

Feature	CTE	Subquery
Readability	✅ Named, defined once at top	❌ Nested, harder to read
Reusability in same query	✅ Can reference same CTE multiple times	❌ Must repeat subquery
Recursive queries	✅ Supported (RECURSIVE keyword)	❌ Not possible
Performance	Similar in modern PostgreSQL	Similar in modern PostgreSQL
Debugging	✅ Easier (run CTE independently)	❌ Harder to isolate

🌍 When to Use CTE Over Subquery

When you'd need the same subquery twice or more in a query
For recursive data (org charts, category trees, comment threads)
When you want to break complex logic into named, readable steps
When readability and maintainability matter

💡 Possible Follow-up Questions

What is a recursive CTE and give a real-world use case?
Does PostgreSQL optimize CTEs the same way as subqueries?
What is the difference between a CTE and a temporary table?
Can a CTE be used in an INSERT, UPDATE, or DELETE statement?
What is a "materialized" CTE in PostgreSQL?

⚡ One-line Interview Answer

A CTE is a named temporary result set defined with the WITH keyword that exists only for the duration of the query. I prefer CTEs over subqueries when the same result is needed multiple times, when writing recursive hierarchical queries, or when the logic is complex enough that naming each step dramatically improves readability.

🧠 Simple Definition

Window functions perform calculations across a set of rows related to the current row, but unlike GROUP BY, they do NOT collapse rows into a single output row. Each row retains its individual identity while also having access to aggregated information from its "window" of related rows.

⚡ Super Simple Line

Window functions = run aggregate calculations OVER a window of rows without collapsing the result set.
GROUP BY collapses rows. Window functions preserve all rows.

🔑 Syntax

FUNCTION() OVER (
  PARTITION BY column   -- divide rows into groups
  ORDER BY column       -- order within each partition
  ROWS BETWEEN ...      -- optional frame clause
)

🧪 RANK() Example

SELECT
  name,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_rank
FROM employees;

-- Output:
-- | name    | department | salary | salary_rank |
-- |---------|------------|--------|-------------|
-- | Alice   | Engineering| 95000  | 1           |
-- | Bob     | Engineering| 90000  | 2           |
-- | Charlie | Engineering| 90000  | 2           |  ← same rank (tie)
-- | Dave    | Engineering| 80000  | 4           |  ← skips 3 (gap after tie)
-- | Eve     | Sales      | 70000  | 1           |  ← restarts for new partition

📊 Common Window Functions

Function	What It Does	Tie Behavior
`ROW_NUMBER()`	Unique sequential number (1,2,3...)	Arbitrary order for ties
`RANK()`	Rank with gaps after ties (1,2,2,4)	Same rank, next skipped
`DENSE_RANK()`	Rank without gaps (1,2,2,3)	Same rank, no gaps
`LAG(col, n)`	Value from n rows before current	N/A
`LEAD(col, n)`	Value from n rows after current	N/A
`SUM() OVER()`	Running total	N/A
`AVG() OVER()`	Moving average	N/A
`FIRST_VALUE()`	First value in window	N/A

🧪 LAG/LEAD Example (Month-over-Month Growth)

SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM monthly_sales;
-- Calculates revenue growth vs previous month for each row

🧪 Running Total Example

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- Each row shows the cumulative total up to that date

🌍 Real-World Use Cases

Leaderboards (rank users by score per category)
Running totals (cumulative revenue per day)
Period-over-period comparisons (LAG for month-over-month growth)
Top N per group (ROW_NUMBER for top 3 products per category)

💡 Possible Follow-up Questions

What is the PARTITION BY clause and how does it work?
What's the difference between RANK and DENSE_RANK?
How would you find the top 3 earners per department?
What is the ROWS BETWEEN frame clause?
How do window functions differ from GROUP BY + subquery?

⚡ One-line Interview Answer

Window functions perform aggregate calculations across a related set of rows (the "window") without collapsing them into a single row, using OVER (PARTITION BY ... ORDER BY ...) syntax. RANK() assigns ranks within each partition, giving ties the same rank with gaps, while DENSE_RANK() gives no gaps and ROW_NUMBER() gives unique sequential numbers regardless of ties.

🧠 Simple Definition

DELETE, TRUNCATE, and DROP all remove data, but at different levels of scope and with different implications for transactions, triggers, and recovery.

⚡ Super Simple Line

DELETE = remove specific rows (with WHERE), logged, rollbackable.
TRUNCATE = remove ALL rows instantly, minimal logging, usually not rollbackable.
DROP = delete the entire table (structure + data) permanently.

🧪 Examples

-- DELETE: remove specific rows with optional WHERE
DELETE FROM orders WHERE status = 'cancelled';  -- removes matching rows only
DELETE FROM orders;                              -- removes ALL rows (very slow for large tables)

-- TRUNCATE: removes ALL rows instantly
TRUNCATE TABLE orders;          -- fast, no WHERE support
TRUNCATE TABLE orders RESTART IDENTITY;  -- also resets auto-increment counter

-- DROP: removes the entire table
DROP TABLE orders;              -- table is gone — cannot SELECT from it anymore
DROP TABLE IF EXISTS orders;    -- won't error if table doesn't exist

📊 Comparison Table

Feature	DELETE	TRUNCATE	DROP
Removes	Specific rows	All rows	Entire table + structure
WHERE clause	✅ Yes	❌ No	❌ No
Speed	Slow (row by row)	✅ Very fast	✅ Fast
Transaction safe	✅ Fully	✅ In PostgreSQL	❌ Usually not
Triggers fire	✅ Yes (per row)	❌ No	❌ No
Resets auto-increment	❌ No	✅ With RESTART IDENTITY	✅ Table gone
Table structure kept	✅ Yes	✅ Yes	❌ No
WAL logging	Fully logged	Minimal logging	Minimal

🌍 When to Use Each

DELETE: Remove specific records (expired sessions, cancelled orders)
TRUNCATE: Clear a staging/temp table before reloading data (ETL pipelines)
DROP: Remove a table during schema migration (deprecated tables)

🚨 PostgreSQL Note on TRUNCATE

Unlike MySQL, PostgreSQL TRUNCATE IS transactional — you CAN rollback it inside a transaction. This makes it safer for use in migrations.

-- In PostgreSQL (unlike MySQL): TRUNCATE can be rolled back!
BEGIN;
TRUNCATE TABLE staging_data;
-- If something goes wrong:
ROLLBACK;  -- ✅ data restored in PostgreSQL

💡 Possible Follow-up Questions

Why is TRUNCATE faster than DELETE?
Does TRUNCATE fire triggers?
How do you recover from an accidental DROP TABLE?
What is the difference between TRUNCATE and DELETE in MySQL vs PostgreSQL?
What DDL vs DML categories do these belong to?

⚡ One-line Interview Answer

DELETE removes specific rows using a WHERE condition, is fully logged and rollbackable, and fires row-level triggers. TRUNCATE removes all rows instantly with minimal logging (no WHERE clause, no trigger firing), and resets the identity counter. DROP removes the entire table structure and data permanently — they differ in scope, speed, rollback support, and trigger behavior.

🧠 Simple Definition

A stored procedure is a named, precompiled block of SQL statements stored directly in the database that can be called by name. It can accept parameters, contain conditional logic, loops, and multiple SQL statements, and return results. Stored procedures run server-side, reducing network roundtrips.

🧪 Creating and Calling a Stored Procedure (PostgreSQL)

-- Create a stored procedure
CREATE OR REPLACE PROCEDURE transfer_funds(
  sender_id   INT,
  receiver_id INT,
  amount      DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
  -- Check sufficient balance
  IF (SELECT balance FROM accounts WHERE id = sender_id) < amount THEN
    RAISE EXCEPTION 'Insufficient funds';
  END IF;

  -- Perform transfer atomically
  UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
  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());

  COMMIT;
END;
$$;

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

📊 Stored Procedure vs Function vs Application Code

Feature	Stored Procedure	DB Function	App Code
Returns value	Via OUT params	✅ Returns value	✅ Returns value
Transaction control	✅ Can COMMIT/ROLLBACK	❌ Cannot	✅ Full control
Network roundtrips	✅ Minimal (runs on DB)	✅ Minimal	❌ Multiple roundtrips
Maintainability	❌ Hard to version/test	❌ Same issue	✅ Version controlled code
Performance	✅ Precompiled plan	✅ Precompiled	Depends
Called with	CALL	SELECT	Application

✅ When to Use Stored Procedures

Complex multi-step database operations that should be atomic
Operations called frequently from many different clients/languages
Security: expose only procedure interface, not underlying table structure
Legacy systems or DBA-managed database logic

❌ When to Avoid Stored Procedures

Modern applications with ORM-managed migrations (version control is hard)
Logic that needs testing with unit tests (hard to test SP in isolation)
When business logic should stay in the application layer
Microservices (each service should own its own data layer logic)

🌍 Real-World Use Cases

Banking: Fund transfers (multi-step atomicity)
Reporting: Complex aggregations run by many report viewers
Data ETL: Transform and load pipelines inside the DB
Audit logging: Automatically record changes on sensitive tables

💡 Possible Follow-up Questions

What is the difference between a stored procedure and a function?
What is a trigger and how does it differ from a stored procedure?
What are the downsides of storing business logic in stored procedures?
How do you debug a stored procedure?
How does Prisma handle stored procedures?

⚡ One-line Interview Answer

A stored procedure is a named, precompiled block of SQL logic stored in the database that accepts parameters and can contain conditional logic, transactions, and multiple SQL operations. It runs server-side reducing network roundtrips, and is useful for complex atomic operations shared across multiple clients — though I prefer to keep business logic in the application layer for testability and maintainability, using stored procedures mainly for DBA-managed reporting or security-isolated database operations.

🧠 Simple Definition

Normalization is the process of organizing database tables to eliminate data redundancy and insertion/update/deletion anomalies by splitting data into related tables with proper relationships. Denormalization is the intentional reversal — adding redundancy back to speed up read queries by reducing costly JOINs.

⚡ Super Simple Line

Normalization = split tables → reduce redundancy → ensure consistency (optimizes writes).
Denormalization = merge/duplicate → reduce JOINs → faster reads (optimizes reads).

📚 Normal Forms (1NF, 2NF, 3NF)

First Normal Form (1NF)

All column values must be atomic (no arrays or comma-separated lists). Each row must be uniquely identifiable.

-- ❌ Violates 1NF (phone_numbers is not atomic)
| id | name  | phone_numbers          |
|----|-------|------------------------|
| 1  | Alice | "555-0001, 555-0002"   |

-- ✅ 1NF compliant
| id | name  | phone_number |
|----|-------|--------------|
| 1  | Alice | 555-0001     |
| 1  | Alice | 555-0002     |

Second Normal Form (2NF)

Must be in 1NF. All non-key columns must be fully dependent on the ENTIRE primary key (no partial dependencies on composite keys).

Third Normal Form (3NF)

Must be in 2NF. No non-key column can depend on another non-key column (no transitive dependencies).

-- ❌ Violates 3NF (zip determines city — transitive dependency)
| id | name  | zip   | city       |
|----|-------|-------|------------|
| 1  | Alice | 10001 | New York   |

-- ✅ 3NF — separate zip-to-city relationship
| id | name  | zip   |     | zip   | city     |
|----|-------|-------|     |-------|----------|
| 1  | Alice | 10001 |     | 10001 | New York |

🔥 When to Denormalize Intentionally

1. Read-Heavy Reporting Dashboards

Joining 8+ tables per analytics query is expensive. Store pre-computed aggregates.

-- Denormalized: store total_orders directly on customer (avoid counting every query)
| customer_id | name  | total_orders | lifetime_value |
|-------------|-------|--------------|----------------|
| 1           | Alice | 47           | 12500          |

2. Caching Computed Values

-- Instead of: SELECT COUNT(*) FROM products WHERE category_id = X
-- Store it: UPDATE categories SET product_count = product_count + 1
-- Update on write, read directly → no join

📊 Normalization vs Denormalization

Aspect	Normalized	Denormalized
Write performance	✅ Fast (update once)	❌ Slower (update multiple places)
Read performance	❌ Slower (many JOINs)	✅ Fast (data co-located)
Data consistency	✅ Easy (single source of truth)	❌ Harder (must keep copies in sync)
Storage	✅ Minimal	❌ More redundant data
Best for	OLTP (transactional apps)	OLAP (analytics, dashboards)

💡 Possible Follow-up Questions

What is Boyce-Codd Normal Form (BCNF)?
When should you denormalize in practice?
What is an OLTP vs OLAP database design?
What are insertion anomalies, update anomalies, and deletion anomalies?
How does MongoDB's document model relate to denormalization?

⚡ One-line Interview Answer

Normalization organizes tables into normal forms (1NF, 2NF, 3NF) to eliminate data redundancy and ensure update consistency at the cost of more JOINs, while denormalization intentionally duplicates data to accelerate read-heavy queries in analytics systems — the key tradeoff is write consistency vs read performance.

🧠 Simple Definition

SQL (Relational) databases like PostgreSQL store data in structured tables with fixed schemas, enforce ACID compliance, and use powerful JOINs for relationships. NoSQL databases like MongoDB store flexible semi-structured data (documents, key-values, graphs) optimized for horizontal scaling and dynamic schemas.

⚡ Super Simple Line

SQL = structured, consistent, relational, ACID (use for financial/transactional data).
NoSQL = flexible, scalable, document-based (use for social, real-time, or schema-changing data).

📊 Comparison Table

Feature	SQL (PostgreSQL)	NoSQL (MongoDB)
Data Model	Tables with rows and columns	JSON-like BSON documents
Schema	Strict, predefined, enforced at DB level	Dynamic, flexible, schema-on-read
Relationships	Rich JOINs + foreign key constraints	Embedding or manual references
ACID	✅ Full transactions out-of-the-box	✅ Single-document atomic; multi-doc needs sessions
Scaling	Vertical (more RAM/CPU) + read replicas	✅ Horizontal (sharding across many nodes)
Query Language	SQL (standardized, powerful)	MongoDB Query Language (JSON-based)
Indexing	B-Tree, partial, expression indexes	B-Tree, text, geospatial, TTL indexes
Best For	Banking, ERP, analytics	Catalogs, social feeds, real-time apps

✅ Choose PostgreSQL When:

Data integrity is critical: Banking, payments, healthcare (ACID guarantees)
Complex relational queries: Multi-table JOINs, window functions, complex aggregations
Schema is stable and well-defined: You know the data shape in advance
Strong consistency required: Financial ledgers, inventory management
Regulatory compliance: Auditing, transactions that cannot be lost

✅ Choose MongoDB When:

Schema evolves frequently: SaaS apps where each customer configures their own fields
Document-centric data: Product catalogs with varying attributes per category
Massive write scale: IoT sensor data, event logging, social media posts
Horizontal sharding needed: Data volume exceeds what one SQL server can handle
Geospatial or real-time data: Location tracking, user feeds

🌍 Real-World Hybrid Example

E-commerce Platform:
├── PostgreSQL: orders, payments, inventory (ACID critical)
├── MongoDB: product catalog (varying attributes per category)
└── Redis: cart sessions, flash sale inventory counters

💡 Possible Follow-up Questions

What is the CAP theorem and how does it apply to SQL vs NoSQL?
When would you use a graph database vs SQL vs MongoDB?
What is BASE consistency and how does it differ from ACID?
Can PostgreSQL also store JSON documents? (Yes — JSONB type)
How does MongoDB handle transactions across multiple collections?

⚡ One-line Interview Answer

I choose PostgreSQL when data integrity, complex relational queries, and strong ACID guarantees are critical — like financial transactions or reporting. I choose MongoDB when the schema evolves frequently, data is document-centric with varying structures, or horizontal sharding is needed for massive write scale — and in practice I often use both together, with PostgreSQL handling transactional data and MongoDB handling catalog or event data.

🧠 Simple Definition

An ORM (Object-Relational Mapper) like Prisma or TypeORM abstracts SQL queries into JavaScript/TypeScript method calls and provides type safety, migrations, and automatic query generation. Raw SQL gives you direct control over every query with no abstraction layer.

⚡ Super Simple Line

ORM = type-safe, faster to write, less control. Raw SQL = full power, more verbose, requires manual parameterization.

📊 ORM vs Raw SQL Comparison

Aspect	ORM (Prisma)	Raw SQL
Type Safety	✅ Full TypeScript types auto-generated from schema	❌ Manual — no types unless using a query builder
SQL Injection Prevention	✅ Automatic parameterization	⚠️ Manual — must parameterize yourself
Development Speed	✅ Faster CRUD — less boilerplate	❌ More verbose for simple queries
Migrations	✅ Schema-as-code, auto-generated migrations	❌ Manual migration SQL files
Complex Queries	❌ Can be awkward for complex JOINs, CTEs, window functions	✅ Full SQL power
Query Efficiency	❌ Can generate inefficient SQL (N+1, over-fetching)	✅ Precise control over query
DB-Specific Features	❌ Hard to access (LATERAL joins, materialized views)	✅ Direct access
Debugging	❌ Must inspect generated SQL	✅ Write exactly what runs

🧪 Prisma (ORM) Example

// ✅ Type-safe, auto-parameterized, readable
const user = await prisma.user.findUnique({
  where: { id: userId },
  include: {
    orders: {
      where: { status: "completed" },
      orderBy: { createdAt: "desc" },
      take: 5
    }
  }
});
// user.orders is fully typed as Order[]

🧪 Raw SQL Example (Same Query)

// More control, but verbose and requires manual handling
const result = await pool.query(`
  SELECT u.*, json_agg(o.*) AS orders
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  WHERE u.id = $1
    AND o.status = 'completed'
  GROUP BY u.id
  ORDER BY o.created_at DESC
  LIMIT 5
`, [userId]);

🚨 ORM Pitfalls to Watch For

N+1 Problem with ORM

// ❌ N+1: fetches users, then 1 query per user for orders
const users = await prisma.user.findMany();
for (const user of users) {
  const orders = await prisma.order.findMany({ where: { userId: user.id } });
  // 1 + N queries! ❌
}

// ✅ Fix: use include for eager loading (1 query with JOIN)
const users = await prisma.user.findMany({
  include: { orders: true }  // single optimized query
});

🌍 When to Use Each

✅ ORM for: CRUD operations, rapid development, type safety requirements, standard queries
✅ Raw SQL for: complex analytics, window functions, CTEs, reporting, performance-critical paths, DB-specific optimizations
✅ Best of both: Use ORM for 80% of queries + raw SQL escape hatches via prisma.$queryRaw for complex ones

💡 Possible Follow-up Questions

How do you use raw SQL inside Prisma (prisma.$queryRaw)?
What is the N+1 problem and how does an ORM cause it?
How does Prisma generate TypeScript types from the schema?
What is a query builder (like Knex.js) and how does it differ from an ORM?
How do ORMs handle database migrations?

⚡ One-line Interview Answer

ORMs like Prisma offer type safety, automatic SQL injection prevention, schema-as-code migrations, and faster CRUD development at the cost of potential query inefficiency, N+1 problems, and limited access to advanced SQL features. Raw SQL offers full control and optimization at the cost of verbosity and manual parameterization — I typically use an ORM for standard operations and drop to raw SQL for complex analytics or performance-critical queries.

🧠 Simple Definition

The N+1 query problem occurs when code fetches a list of N records with one query, then executes one additional query for each record to load related data — resulting in N+1 total queries instead of 1 or 2 optimized queries. This causes unnecessary database load and high latency.

⚡ Super Simple Line

N+1 = 1 query to get 100 orders + 100 separate queries to get the customer for each order = 101 queries instead of 1 JOIN query.

🔥 Classic N+1 Example

// ❌ N+1 Problem
const orders = await prisma.order.findMany();  // 1 query: gets 100 orders

for (const order of orders) {
  // ❌ 1 extra query per order = 100 more queries!
  const customer = await prisma.customer.findUnique({
    where: { id: order.customerId }
  });
  console.log(`${customer.name}: ${order.total}`);
}
// Total: 1 + 100 = 101 queries 💀

✅ Fix 1: Eager Loading (ORM)

// ✅ Single optimized query with JOIN
const orders = await prisma.order.findMany({
  include: { customer: true }  // JOIN in one query
});
// Total: 1 query ✅

✅ Fix 2: SQL JOIN (Raw)

-- ✅ One query with JOIN
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id;
-- Total: 1 query ✅

✅ Fix 3: Batching / DataLoader Pattern

// ✅ Collect all IDs, fetch in one batch query
const orders = await prisma.order.findMany();
const customerIds = [...new Set(orders.map(o => o.customerId))];

// One batch query instead of N
const customers = await prisma.customer.findMany({
  where: { id: { in: customerIds } }
});

// Map customers to orders in memory
const customerMap = new Map(customers.map(c => [c.id, c]));
const ordersWithCustomers = orders.map(o => ({
  ...o,
  customer: customerMap.get(o.customerId)
}));
// Total: 2 queries ✅

🔍 How to Detect N+1

Enable Prisma query logging: log: ["query"] in PrismaClient
Look for the same query repeated N times in logs
Use database monitoring tools (e.g., PostgreSQL's pg_stat_statements)
Check response times — N+1 causes latency to scale linearly with data size

const prisma = new PrismaClient({ log: ["query"] });
// Logs every SQL query — N+1 will be obvious (same query repeated)

📊 Impact

Scenario	Without Fix	With Fix
100 orders, load customers	101 queries	1-2 queries
1000 orders, load customers	1001 queries	1-2 queries
Response time	~2000ms (network RTT per query)	~20ms

💡 Possible Follow-up Questions

How do you detect N+1 in a running application?
What is DataLoader and how does it batch requests?
How does Prisma's include/select help avoid N+1?
What is the difference between eager loading and lazy loading?
How does N+1 manifest differently in REST vs GraphQL APIs?

🧠 Simple Definition

Soft delete marks a record as deleted (e.g., sets deletedAt timestamp or isDeleted = true) without physically removing it from the database. Hard delete permanently removes the row. The choice depends on whether you need auditability, data recovery, or must preserve referential integrity.

⚡ Super Simple Line

Soft delete = mark as deleted, keep data → recoverable, auditable.
Hard delete = remove permanently → simple, storage-efficient.

🧪 Soft Delete Implementation

-- Schema with soft delete columns
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;
ALTER TABLE users ADD COLUMN deleted_by INTEGER;

-- Soft delete a user
UPDATE users
SET deleted_at = NOW(), deleted_by = admin_id
WHERE id = 42;

-- Query active users (must always filter!)
SELECT * FROM users WHERE deleted_at IS NULL;

-- Query deleted users (for admin / audit)
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- Restore a user
UPDATE users SET deleted_at = NULL WHERE id = 42;

🧪 Soft Delete in Prisma

// Schema
model User {
  id        Int       @id @default(autoincrement())
  name      String
  deletedAt DateTime?  // null = active, set = deleted
}

// Soft delete
await prisma.user.update({
  where: { id: userId },
  data: { deletedAt: new Date() }
});

// Always filter in queries (use middleware to automate)
const activeUsers = await prisma.user.findMany({
  where: { deletedAt: null }
});

📊 When to Use Soft vs Hard Delete

Use Case	Soft Delete	Hard Delete
Need data recovery	✅ User deleted account by mistake	❌ Gone forever
Audit trail required	✅ Who deleted what and when	❌ No record
Legal data retention	✅ Must keep for 7 years (GDPR exception)	❌ Not compliant
Referential integrity	✅ Orders reference deleted user → still valid	❌ Foreign key violation or cascade delete
Truly unnecessary data	❌ Storage waste	✅ Clean removal
GDPR right to erasure	❌ Data still exists	✅ Actually deleted

⚠️ Soft Delete Pitfalls

Must filter everywhere: Every query needs WHERE deleted_at IS NULL — easy to forget
Unique constraints break: If user with email "a@b.com" is soft-deleted, you can't create a new user with the same email unless you handle it
Storage grows: Data never actually removed
Fix: Use Prisma middleware or a global query extension to auto-filter soft-deleted records

// Prisma middleware to automatically filter soft-deleted records
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'findMany' || params.action === 'findFirst') {
      params.args.where = { ...params.args.where, deletedAt: null };
    }
  }
  return next(params);
});

💡 Possible Follow-up Questions

How do you handle unique constraints with soft deletes?
How does GDPR "right to erasure" interact with soft deletes?
How do you automate soft-delete filtering in Prisma?
What is the difference between soft delete and archiving?
How do you handle foreign key relationships with soft-deleted records?

⚡ One-line Interview Answer

I use soft delete (setting deletedAt timestamp) when data recovery, audit trails, legal retention, or referential integrity are required, and hard delete when data genuinely should disappear and storage or GDPR compliance matters. The main tradeoff is that soft delete requires filtering deleted records in every query, which I automate using ORM middleware to prevent accidentally exposing deleted data.

🧠 Simple Definition

In MongoDB, embedding means storing related data inside a single document. Referencing means storing just an ID and looking up the related document separately. This is the core data modeling decision in MongoDB, and it fundamentally affects performance, query complexity, and scalability.

⚡ Super Simple Line

Embed = put related data inside the document (like a nested JSON object).
Reference = store only the ID, and join later (like a foreign key in SQL).

🧪 Embedding Example

// User with embedded addresses
{
  _id: ObjectId("abc"),
  name: "Alice",
  addresses: [
    { type: "home", city: "New York" },
    { type: "work", city: "Boston" }
  ]
}
// ✅ One query retrieves everything — no join needed

🧪 Referencing Example

// User document
{ _id: ObjectId("u1"), name: "Alice" }

// Order documents referencing the user
{ _id: ObjectId("o1"), userId: ObjectId("u1"), total: 100 }
{ _id: ObjectId("o2"), userId: ObjectId("u1"), total: 250 }

// To get user + orders, need two queries or $lookup

📊 When to Embed vs Reference

Situation	Strategy	Why
Data always fetched together	✅ Embed	Single read, no join
One-to-few (e.g., addresses per user)	✅ Embed	Bounded size, always related
Child has no independent existence	✅ Embed	Logically inseparable
Array could grow unbounded (>100)	✅ Reference	MongoDB 16MB document limit
Data shared across multiple documents	✅ Reference	Avoids duplication inconsistency
Many-to-many relationship	✅ Reference	Cannot embed in both directions
Child accessed independently often	✅ Reference	Separate queries are more efficient

🚨 The 16MB Document Limit

MongoDB has a hard limit of 16MB per document. If you embed an array that grows unboundedly (e.g., all comments on a popular post), you will hit this limit. Always reference when arrays could grow large.

🌍 Real-World Examples

✅ Embed: Blog Post + Comments (if limited)

{
  _id: ObjectId("p1"),
  title: "MongoDB Tips",
  comments: [   // ✅ OK if comments stay small
    { author: "Bob", text: "Great post!" }
  ]
}

✅ Reference: Blog Post + Comments (if unlimited)

// Post
{ _id: ObjectId("p1"), title: "MongoDB Tips" }

// Comments referencing post
{ _id: ObjectId("c1"), postId: ObjectId("p1"), text: "Great!" }
{ _id: ObjectId("c2"), postId: ObjectId("p1"), text: "Helpful!" }

🧠 Rule of Thumb

One-to-few → Embed
One-to-many → Reference (with array of IDs on parent)
One-to-squillions → Reference (store parent ID on child side)

💡 Possible Follow-up Questions

What happens if an embedded array grows too large?
How does $lookup work in MongoDB for references?
How do you handle many-to-many relationships in MongoDB?
What is the 16MB document size limit and how do you work around it?
How does this compare to how SQL handles relationships?

⚡ One-line Interview Answer

Embed when data is always accessed together and the child array is bounded; reference when data is accessed independently, shared across documents, or the array could grow unbounded beyond MongoDB's 16MB document size limit.

🧠 Simple Definition

The MongoDB Aggregation Pipeline is a data processing framework where documents pass through a series of stages, each transforming the data until the final result is produced. Think of it like an assembly line: data goes in, gets filtered, grouped, reshaped, sorted, and comes out transformed.

⚡ Super Simple Line

Aggregation pipeline = a chain of stages that transform documents one step at a time, similar to SQL's SELECT + WHERE + GROUP BY + ORDER BY combined.

🔑 Core Pipeline Stages

Stage	SQL Equivalent	What It Does
`$match`	WHERE	Filter documents by condition
`$group`	GROUP BY	Aggregate values (sum, avg, count)
`$project`	SELECT	Include, exclude, or rename fields
`$sort`	ORDER BY	Sort documents
`$limit`	LIMIT	Limit number of results
`$skip`	OFFSET	Skip N documents (pagination)
`$lookup`	LEFT JOIN	Join data from another collection
`$unwind`	N/A	Flatten array fields into separate documents
`$addFields`	computed columns	Add computed fields to documents
`$facet`	N/A	Run multiple pipelines in parallel

🧪 Real Example: Total Sales Per User

db.orders.aggregate([
  // Stage 1: filter only completed orders
  { $match: { status: "completed" } },

  // Stage 2: group by userId and sum their totals
  { $group: {
    _id: "$userId",
    totalSpent: { $sum: "$amount" },
    orderCount: { $sum: 1 }
  }},

  // Stage 3: sort by highest spenders
  { $sort: { totalSpent: -1 } },

  // Stage 4: top 10 only
  { $limit: 10 }
]);

🧪 $lookup Example (Join)

db.orders.aggregate([
  { $lookup: {
    from: "users",           // collection to join
    localField: "userId",    // field in orders
    foreignField: "_id",     // field in users
    as: "userDetails"        // output array field
  }}
]);
// Result: each order has a "userDetails" array with user data embedded

🧪 $unwind Example

// If a document has: { tags: ["mongodb", "nosql", "database"] }
{ $unwind: "$tags" }
// Creates 3 separate documents, one per tag
// Useful for counting tag frequency

⚙️ Performance Tips

Always put $match first to filter early and reduce documents processed
Put $project early to reduce document size before heavy operations
Use indexes on fields used in $match and $sort
Use allowDiskUse: true for large aggregations that exceed 100MB RAM limit

💡 Possible Follow-up Questions

How does $facet allow multiple aggregation pipelines?
How do you optimize a slow aggregation pipeline?
How does $lookup compare to SQL JOIN in terms of performance?
What is $unwind and when would you use it?
How do you paginate results using aggregation?

⚡ One-line Interview Answer

MongoDB's aggregation pipeline processes documents through sequential stages like $match (filter), $group (aggregate), $project (reshape), $lookup (join), and $sort, where each stage transforms the output of the previous one, similar to SQL's query clauses chained together.

🧠 Simple Definition

A database index is a data structure (typically a B-Tree) that the database maintains separately from the main data, mapping field values to document locations to enable fast lookups without scanning every document. Without an index, MongoDB does a collection scan (reads every document). With an index, it jumps directly to matching documents.

⚡ Super Simple Line

Index = a sorted shortcut to find documents fast, like a book's index pointing you to the right page.
Without index → scan all documents (slow).
With index → jump straight to matches (fast).

📚 Types of Indexes in MongoDB

Index Type	Best For	Example
Single Field	Simple field lookups	`db.users.createIndex({ email: 1 })`
Compound	Multi-field queries + sorts	`db.users.createIndex({ lastName: 1, age: -1 })`
Multikey	Array field indexing	`db.posts.createIndex({ tags: 1 })`
Text	Full-text keyword search	`db.articles.createIndex({ content: "text" })`
Geospatial 2dsphere	Location-based queries	`db.places.createIndex({ location: "2dsphere" })`
Hashed	Sharding shard key	`db.users.createIndex({ _id: "hashed" })`
TTL	Auto-expiring documents	`db.sessions.createIndex({ createdAt: 1 }, { expireAfterSeconds: 3600 })`

🧪 Compound Index Code

// Compound index - field order matters!
db.users.createIndex({ lastName: 1, age: -1 });

// This index CAN serve:
db.users.find({ lastName: "Smith" });                     // ✅ prefix match
db.users.find({ lastName: "Smith", age: { $gt: 25 } });  // ✅ both fields
db.users.find({ lastName: "Smith" }).sort({ age: -1 });  // ✅ sort covered

// This index CANNOT serve:
db.users.find({ age: 25 });  // ❌ age alone (not a prefix)

🧪 Text Index Code

// Create text index on multiple fields
db.articles.createIndex({ title: "text", content: "text" });

// Search using $text operator
db.articles.find({
  $text: { $search: "database index performance" }
});

// ⚠️ Only ONE text index allowed per collection

🏆 The ESR Rule (Interview Gold)

When building compound indexes, order fields in this sequence for maximum performance:

Equality first — fields with exact match conditions (e.g., status: "active")
Sort next — fields in the sort clause (e.g., createdAt: -1)
Range last — fields with range conditions (e.g., age: { $gt: 25 })

// Query: find active users older than 25, sorted by name
db.users.find({ status: "active", age: { $gt: 25 } }).sort({ name: 1 });

// ✅ ESR-correct index:
db.users.createIndex({ status: 1, name: 1, age: 1 });
// E=status, S=name, R=age

⚠️ When to AVOID Indexes

Collections with very few documents (full scan is fine)
Fields with very low cardinality (e.g., a boolean field — only 2 values)
Write-heavy collections (every write must update all indexes)
Too many indexes slow down writes significantly

💡 Possible Follow-up Questions

What is a covered query and how do indexes enable it?
How do you check if an index is being used with explain()?
What is index selectivity and why does it matter?
What is the ESR rule for compound indexes?
How does a TTL index work and when would you use it?
What is the difference between a multikey index and a regular index?

⚡ One-line Interview Answer

MongoDB indexes are B-Tree data structures that enable fast document lookups by mapping field values to locations, avoiding full collection scans. Compound indexes support multi-field queries and sorts following the ESR rule, while text indexes tokenize string fields for full-text keyword search, and TTL indexes automatically expire documents after a set duration.

🧠 Simple Definition

EXPLAIN ANALYZE is a PostgreSQL command that actually executes a query and then shows you a detailed query execution plan including actual runtime statistics (rows processed, time spent, memory used). It's the primary tool for diagnosing slow queries.

⚡ Super Simple Line

EXPLAIN = shows the plan (doesn't run the query).
EXPLAIN ANALYZE = runs the query AND shows plan + actual performance stats.
Use it to find why a query is slow and where to add indexes.

🧪 Basic Usage

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 10;

📋 Sample Output Explained

Limit (cost=0.42..20.56 rows=10 width=100) (actual time=0.052..0.185 rows=10 loops=1)
  -> Index Scan using orders_customer_id_idx on orders
      (cost=0.42..1854.20 rows=900 width=100)
      (actual time=0.045..0.172 rows=10 loops=1)
      Index Cond: (customer_id = 42)
Planning Time: 0.5 ms
Execution Time: 0.2 ms

🔍 What to Look For

Warning Sign	What It Means	Fix
Seq Scan on large table	Full table scan — no index used	Add index on filter/join column
Rows estimate ≠ actual rows	Stale query planner statistics	Run ANALYZE or VACUUM ANALYZE
Nested Loop on large sets	O(n²) join — bad for big tables	Consider Hash Join or index
High actual time	Slow node — bottleneck found here	Optimize that specific operation
Sort without index	In-memory sort of many rows	Add index on ORDER BY columns
High rows × loops	Nested loop multiplying work	Rewrite join or add index

🧪 Identifying a Missing Index

-- Bad output (no index):
Seq Scan on orders (cost=0.00..35000.00 rows=1000000 width=50)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999990  ← 999,990 rows scanned to find 10!

-- Fix: add an index
CREATE INDEX orders_customer_id_idx ON orders(customer_id);

-- Good output (with index):
Index Scan using orders_customer_id_idx on orders
  Index Cond: (customer_id = 42)  ← only 10 rows touched ✅

⚙️ EXPLAIN Options

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;
-- ANALYZE: run the query + show actual times
-- BUFFERS: show cache hit/miss info (shared_blks_hit vs shared_blks_read)
-- FORMAT JSON: machine-readable output for tools like explain.depesz.com

💡 Possible Follow-up Questions

What is the difference between EXPLAIN and EXPLAIN ANALYZE?
What does a high row estimate mismatch mean and how do you fix it?
What is VACUUM ANALYZE and why is it important?
How do you read the cost numbers in an EXPLAIN output?
What is a Bitmap Index Scan vs an Index Scan?
What tools help visualize EXPLAIN ANALYZE output?

⚡ One-line Interview Answer

EXPLAIN ANALYZE executes a query and returns the full execution plan with actual runtime stats. I look for Seq Scans on large tables indicating missing indexes, high rows-removed-by-filter counts, mismatches between estimated and actual rows suggesting stale statistics requiring VACUUM ANALYZE, and slow sort operations that could benefit from an index on the ORDER BY column.

🧠 Simple Definition

A database transaction is a sequence of operations that are executed as a single logical unit of work. Transactions must satisfy ACID properties to guarantee data integrity. PostgreSQL implements transactions natively and uses MVCC for isolation and WAL (Write-Ahead Log) for durability.

⚡ Super Simple Line

Transaction = group of operations that all succeed together or all fail together. ACID = the 4 guarantees every reliable database transaction must have.

🔑 ACID Properties Explained

A — Atomicity

All operations in a transaction succeed, or ALL are rolled back. There is no partial execution.

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- debit
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- credit
COMMIT;  -- both succeed together
-- If second UPDATE fails → ROLLBACK automatically reverts first UPDATE

C — Consistency

The database always moves from one valid state to another. Constraints (NOT NULL, foreign keys, CHECK) are enforced. Invalid state is never written.

I — Isolation

Concurrent transactions don't see each other's intermediate state. PostgreSQL uses MVCC — each transaction reads a consistent snapshot.

D — Durability

Committed transactions survive crashes. PostgreSQL uses WAL (Write-Ahead Log) — changes are written to the log before the actual data files, so a crash can always be recovered.

⚙️ PostgreSQL Under the Hood

MVCC (Multi-Version Concurrency Control)

When a row is updated, PostgreSQL writes a NEW version of the row (doesn't overwrite)
Old version remains visible to active transactions that started before the update
Readers never block writers, writers never block readers
Dead row versions are cleaned up by VACUUM

WAL (Write-Ahead Log)

Before changing actual data files, PostgreSQL writes the change to the WAL log first
On crash: PostgreSQL replays WAL to recover committed but unwritten changes
WAL also enables streaming replication (replica servers replay the same WAL)

📊 Isolation Levels in PostgreSQL

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed (default)	❌ Prevented	Possible	Possible
Repeatable Read	❌ Prevented	❌ Prevented	❌ Prevented
Serializable	❌ Prevented	❌ Prevented	❌ Prevented

💡 Possible Follow-up Questions

What is the difference between COMMIT and ROLLBACK?
What is a savepoint in a transaction?
What are the different isolation levels and their tradeoffs?
What is a dirty read, phantom read, and non-repeatable read?
How does VACUUM work in PostgreSQL and why is it needed?
How does WAL enable replication?

⚡ One-line Interview Answer

A database transaction is a logical unit of work that must satisfy ACID properties: Atomicity (all-or-nothing), Consistency (valid state transitions), Isolation (concurrent transactions don't interfere), and Durability (committed data survives crashes). PostgreSQL implements isolation using MVCC where each transaction reads a consistent snapshot without blocking others, and durability using Write-Ahead Logging which ensures changes can be recovered after a crash.

🧠 Simple Definition

MongoDB supports multi-document ACID transactions since version 4.0 (replica sets) and 4.2 (sharded clusters). By default, individual document writes in MongoDB are atomic. For operations spanning multiple documents or collections, you use session-based transactions to guarantee all-or-nothing execution.

⚡ ACID Properties Explained Simply

Property	Meaning	MongoDB Implementation
Atomicity	All operations succeed or all roll back	commitTransaction() / abortTransaction()
Consistency	Data always moves between valid states	Schema validation + constraints
Isolation	Concurrent transactions don't interfere	MVCC snapshot isolation in WiredTiger
Durability	Committed data survives crashes	Write-ahead journal + replica acknowledgment

🧪 Transaction Code Example (Node.js)

const { MongoClient } = require("mongodb");
const client = new MongoClient("mongodb://localhost:27017");

async function transferFunds(fromId, toId, amount) {
  const session = client.startSession();

  try {
    session.startTransaction({
      readConcern: { level: "snapshot" },
      writeConcern: { w: "majority" }
    });

    const accounts = client.db("bank").collection("accounts");

    // Debit sender
    await accounts.updateOne(
      { _id: fromId, balance: { $gte: amount } },  // ensure sufficient funds
      { $inc: { balance: -amount } },
      { session }
    );

    // Credit receiver
    await accounts.updateOne(
      { _id: toId },
      { $inc: { balance: amount } },
      { session }
    );

    await session.commitTransaction();
    console.log("✅ Transfer successful");

  } catch (error) {
    await session.abortTransaction();
    console.error("❌ Transaction rolled back:", error.message);
    throw error;
  } finally {
    session.endSession();
  }
}

⚠️ When Should You Use Transactions?

✅ Transferring funds between accounts (multi-document atomicity required)
✅ Creating an order + updating inventory + recording payment (3 collections)
✅ Any operation where partial failure would leave data inconsistent
❌ Simple CRUD on a single document (MongoDB is already atomic at document level)
❌ As a substitute for good schema design (embed related data to avoid transactions)

🚨 Transaction Performance Considerations

Transactions in MongoDB have higher overhead than single-document operations. Best practice: 80-90% of operations should be single-document. Use transactions only when truly needed for cross-document consistency.

Transactions hold locks — keep them short
Default timeout: 60 seconds (configurable)
Transactions require a replica set (even in development)

💡 Possible Follow-up Questions

How do MongoDB transactions compare to PostgreSQL transactions?
What happens if a transaction times out?
Can transactions span multiple replica set members?
What are read concerns and write concerns in MongoDB?
Why is single-document atomicity often sufficient in MongoDB?
How does the session object relate to transactions?

⚡ One-line Interview Answer

MongoDB guarantees ACID at the single-document level by default, and supports multi-document ACID transactions via session-based transactions that span collections and replica set members, using MVCC for isolation and write-ahead journaling for durability — though transactions should be used sparingly since good schema design with document embedding often eliminates the need for them.

🧠 Simple Definition

WiredTiger is MongoDB's default storage engine since version 3.2. It replaced the old MMAPv1 engine. MVCC (Multi-Version Concurrency Control) is WiredTiger's concurrency mechanism that allows multiple readers and writers to access data simultaneously without blocking each other by maintaining multiple versions of each document.

⚡ Super Simple Line

WiredTiger = MongoDB's storage engine (handles disk I/O, compression, locking).
MVCC = readers see a consistent snapshot of data without blocking writers, and writers don't block readers.

🧠 How MVCC Works (Step by Step)

Transaction A starts a read → gets a read timestamp
Transaction B starts writing → creates a new version of the document
Transaction A still reads the old version (its snapshot)
After Transaction B commits → Transaction A's next read would see the new version
Old versions are cleaned up when no longer needed (called checkpoint)

📊 WiredTiger vs Old MMAPv1

Feature	WiredTiger	MMAPv1 (old)
Locking level	Document-level (fine-grained)	Collection-level (coarse)
Concurrency	High (MVCC — readers don't block writers)	Low (writers block all readers)
Compression	Snappy (default), zlib, zstd	None
Memory	Uses its own cache (configurable)	Used OS mmap
Write performance	Better (journal batching)	Slower

⚙️ Key WiredTiger Features

Document-level concurrency: Multiple documents in the same collection can be modified simultaneously
Compression: Data is compressed by default using Snappy, reducing disk usage ~50-70%
Cache: WiredTiger maintains its own in-memory cache (default: 50% of RAM minus 1GB)
Checkpointing: Periodically writes a consistent snapshot to disk for crash recovery
Journaling: Write-ahead log ensures durability even before checkpoint

🧪 Checking Your Storage Engine

db.serverStatus().storageEngine
// Returns: { name: 'wiredTiger', supportsCommittedReads: true, ... }

💡 Possible Follow-up Questions

How does MVCC prevent dirty reads?
What is a checkpoint in WiredTiger?
How does WiredTiger compression affect performance?
What is the WiredTiger cache and how do you tune it?
How does MVCC differ from pessimistic locking?
What is journaling and how does it relate to durability?

⚡ One-line Interview Answer

WiredTiger is MongoDB's default storage engine that provides document-level locking, data compression, and MVCC (Multi-Version Concurrency Control), which allows readers to see a consistent snapshot of data at their read timestamp without blocking writers, enabling high concurrency compared to the old collection-level locking in MMAPv1.

🧠 Simple Definition

A unique constraint is a database-level rule that prevents two rows from having the same value(s) in a specific column or combination of columns. It's enforced by the database engine as an atomic operation, making it the only reliable way to prevent duplicates under concurrent load.

⚡ Super Simple Line

Unique constraint = the database guarantees no duplicates, even if 1,000 requests try to create the same record simultaneously.

🔥 Why Application-Level Checks Are Not Enough

// ❌ DANGEROUS — Race condition!
async function createUser(email: string) {
  // Two simultaneous requests can BOTH pass this check
  const existing = await prisma.user.findUnique({ where: { email } });
  if (existing) throw new Error("Email taken");

  // Both proceed to insert → DUPLICATE USER ❌
  return prisma.user.create({ data: { email } });
}
// Time: Request A checks → no user found
// Time: Request B checks → no user found (A hasn't inserted yet)
// Time: Request A inserts → success
// Time: Request B inserts → success but creates duplicate! ❌

✅ The Fix: Database-Level Unique Constraint

-- PostgreSQL
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- Composite unique constraint (e.g., one OAuth account per provider per user)
ALTER TABLE oauth_accounts
ADD CONSTRAINT unique_user_provider UNIQUE (user_id, provider_id);

// Prisma schema
model User {
  id    Int    @id @default(autoincrement())
  email String @unique  // ← database-enforced unique constraint
}

model OAuthAccount {
  id         Int    @id
  userId     Int
  providerId String
  @@unique([userId, providerId])  // ← composite unique
}

🧪 Handling Unique Constraint Violations

import { Prisma } from "@prisma/client";

async function createUser(email: string) {
  try {
    return await prisma.user.create({ data: { email } });
  } catch (error) {
    if (error instanceof Prisma.PrismaClientKnownRequestError) {
      if (error.code === "P2002") {  // Unique constraint violation
        throw new Error("409: Email already registered");
      }
    }
    throw error;
  }
}

🌍 Upsert — The Atomic Alternative

// Instead of check-then-insert, use upsert (atomic)
const user = await prisma.user.upsert({
  where: { email },
  update: {},          // nothing to update if exists
  create: { email }    // create if not exists
});
// ✅ Database guarantees atomicity — no race condition

📊 Common Unique Constraint Use Cases

Table	Unique Column(s)	Reason
users	email	One account per email
users	username	Unique usernames
oauth_accounts	(user_id, provider)	One Google/GitHub account per user
product_variants	(product_id, sku)	No duplicate SKUs per product
friendships	(user_id, friend_id)	No duplicate friend connections

💡 Possible Follow-up Questions

What is the difference between a unique constraint and a unique index?
How do you handle unique constraint violations gracefully in an API?
What is an upsert and how does it prevent race conditions?
How do unique constraints interact with soft deletes?
What error code does Prisma return for unique constraint violations?

⚡ One-line Interview Answer

Unique constraints enforce uniqueness at the database level as an atomic operation, which is critical because application-level "check then insert" patterns are vulnerable to race conditions under concurrent load — two simultaneous requests can both pass the check and both insert duplicates. The correct approach is to rely on database constraints and catch the P2002 (unique constraint violated) error to return a clean 409 Conflict response.

🧠 Simple Definition

Pessimistic locking assumes conflicts are likely and locks a resource immediately when reading it, preventing others from modifying it. Optimistic locking assumes conflicts are rare and doesn't lock anything, but detects conflicts at update time using a version field.

⚡ Super Simple Line

Pessimistic = "I expect conflict, so I lock it now."
Optimistic = "I expect no conflict, so I just check at update time."

🧪 Pessimistic Locking (SQL: SELECT FOR UPDATE)

BEGIN;
  -- Lock the row — no one else can update this until we commit
  SELECT * FROM inventory
  WHERE product_id = 42
  FOR UPDATE;  -- ← pessimistic lock

  -- Safely update knowing no one changed it
  UPDATE inventory
  SET quantity = quantity - 1
  WHERE product_id = 42;

COMMIT;
-- Lock is released on commit

✅ Good for:

High-conflict scenarios (many users competing for same resource)
Critical operations like inventory management, seat booking
When conflict resolution after the fact is expensive

❌ Downside:

Reduces concurrency (other transactions wait)
Risk of deadlocks if multiple rows locked in different order

🧪 Optimistic Locking (Version Field)

-- Table has a 'version' column
-- Step 1: Read the row and note the version
SELECT id, balance, version FROM accounts WHERE id = 1;
-- Returns: { id: 1, balance: 500, version: 3 }

-- Step 2: Try to update — include version in WHERE clause
UPDATE accounts
SET balance = 450, version = version + 1
WHERE id = 1 AND version = 3;  -- ← check no one changed it

-- If 0 rows affected → someone else updated it first → retry!

✅ Good for:

Low-conflict scenarios (most writes don't collide)
Better concurrency and throughput
Distributed systems and microservices (no shared lock state)

❌ Downside:

Requires retry logic in application code
High conflict = many retries = poor performance

📊 Comparison Table

Feature	Pessimistic Locking	Optimistic Locking
Locks data?	✅ Yes (immediate)	❌ No lock acquired
Conflict assumption	Conflicts are frequent	Conflicts are rare
Concurrency	Lower (others wait)	Higher (no blocking)
Deadlock risk	✅ Yes	❌ No
Conflict handling	Prevented upfront	Detected at update time, retry
Best use case	Bank transfers, inventory	User profile updates, blog posts

💡 Possible Follow-up Questions

How do you implement optimistic locking in Prisma or TypeORM?
What happens if two optimistic updates conflict — how do you retry?
How does pessimistic locking lead to deadlocks?
What is SELECT FOR UPDATE SKIP LOCKED and when is it useful?
How does optimistic locking relate to MVCC?

⚡ One-line Interview Answer

Pessimistic locking prevents conflicts by locking the row immediately on read (SELECT FOR UPDATE), suitable for high-conflict scenarios like banking. Optimistic locking avoids locks entirely by using a version field and checking at update time whether the data changed, suitable for low-conflict scenarios with better concurrency but requiring retry logic on conflict.

🧠 Simple Definition

A deadlock is a state where two or more database transactions are each waiting for a lock held by the other, forming a circular dependency that prevents any of them from proceeding. Databases automatically detect and resolve deadlocks by aborting one of the transactions and returning an error.

⚡ Super Simple Line

Deadlock = Transaction A holds lock on Row X and wants Row Y. Transaction B holds lock on Row Y and wants Row X. Both wait forever → deadlock → database kills one.

🔥 Deadlock Visualization

Transaction A:
  1. LOCK Row X ✅ (acquired)
  2. Try to LOCK Row Y... ⏳ (waiting for B)

Transaction B:
  1. LOCK Row Y ✅ (acquired)
  2. Try to LOCK Row X... ⏳ (waiting for A)

→ Circular wait → Deadlock detected → DB aborts one transaction

🧪 SQL Deadlock Example

-- Transaction A (session 1)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- (pause here)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- tries to lock row 2 → BLOCKS

-- Transaction B (session 2)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- locks row 2
-- (pause here)
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- tries to lock row 1 → BLOCKS

-- Result: PostgreSQL detects circular dependency, aborts one transaction:
-- ERROR: deadlock detected
-- DETAIL: Process X waits for Lock on tuple (...)

🛡️ How to Prevent Deadlocks

1. Consistent Lock Ordering (Most Important)

// ❌ Inconsistent order — deadlock risk
async function transfer(fromId: number, toId: number, amount: number) {
  await lockAccount(fromId);  // TX A locks id=1, TX B locks id=2
  await lockAccount(toId);    // both now blocked ← deadlock
}

// ✅ Always lock in ascending ID order — prevents circular dependency
async function transfer(fromId: number, toId: number, amount: number) {
  const [first, second] = fromId < toId ? [fromId, toId] : [toId, fromId];
  await lockAccount(first);   // Both TXs lock the smaller ID first
  await lockAccount(second);  // No circular dependency ✅
}

2. Keep Transactions Short

// ❌ Bad — long transaction holds locks
async function processPayment() {
  await db.beginTransaction();
  await lockAccount(userId);
  await callExternalPaymentAPI();  // Network call while holding lock! ❌
  await db.commit();
}

// ✅ Good — do external work OUTSIDE the transaction
async function processPayment() {
  const paymentResult = await callExternalPaymentAPI();  // before lock
  await db.beginTransaction();
  await recordPayment(paymentResult);  // quick, no external calls
  await db.commit();
}

3. Lock Timeouts + Retry Logic

-- Set a lock timeout (PostgreSQL)
SET lock_timeout = '5s';  -- fail if can't acquire lock in 5 seconds

// Application-level retry on deadlock
async function withRetry(fn: () => Promise, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (isDeadlockError(error) && attempt < maxRetries - 1) {
        await sleep(Math.random() * 100 * (attempt + 1));  // exponential backoff
        continue;
      }
      throw error;
    }
  }
}

4. Index Foreign Keys (PostgreSQL)

PostgreSQL locks parent rows when updating foreign keys on child rows. Adding indexes on foreign key columns reduces lock scope and deadlock probability.

📊 Prevention Techniques

Technique	How It Helps
Consistent lock ordering	Eliminates circular dependency possibility
Short transactions	Reduces time locks are held
Lock timeout + retry	Automatically recovers from deadlocks
Index foreign keys	Reduces lock scope on parent rows
Avoid user interaction in TX	Don't wait for user input while holding locks

💡 Possible Follow-up Questions

How does a database detect a deadlock cycle?
What is the difference between a deadlock and a livelock?
How does MongoDB handle deadlocks with document-level locking?
What is SELECT FOR UPDATE SKIP LOCKED and how does it help queues avoid deadlocks?
How do you identify deadlocks in PostgreSQL logs?

⚡ One-line Interview Answer

A deadlock occurs when concurrent transactions form a circular lock dependency, each waiting for the other's lock. Databases auto-detect and abort one transaction to resolve it. Prevention involves always acquiring locks in a consistent order (e.g., sorted by ID), keeping transactions short to minimize lock hold time, setting lock timeouts with retry logic, and indexing foreign key columns to reduce lock scope.

🧠 Simple Definition

Redis (Remote Dictionary Server) is an open-source, in-memory data store that functions as a database, cache, and message broker. It stores data entirely in RAM for sub-millisecond latency and supports multiple native data structures, each optimized for specific use cases.

⚡ Super Simple Line

Redis = blazing-fast in-memory key-value store with multiple data structures, used for caching, session storage, queues, leaderboards, pub/sub messaging, and rate limiting.

📚 Redis Data Structures

1. String — Most basic type

SET user:1:name "Alice"
GET user:1:name         # → "Alice"
INCR page_views         # Atomic counter (no race condition)
EXPIRE session:abc 3600 # Auto-expire in 1 hour

📌 Use for: caching API responses, session tokens, counters, feature flags

2. Hash — Object storage

HSET user:1 name "Alice" age 30 email "alice@example.com"
HGET user:1 name    # → "Alice"
HGETALL user:1      # → all fields

📌 Use for: storing structured objects (user profiles, product details)

3. List — Ordered collection / Queue

LPUSH job_queue "job:1"   # Push to left
RPUSH job_queue "job:2"   # Push to right
RPOP job_queue             # Pop from right (FIFO queue)
LRANGE job_queue 0 -1     # Get all items

📌 Use for: task queues, activity feeds, recent items history

4. Set — Unique unordered collection

SADD active_users "user:1" "user:2" "user:3"
SMEMBERS active_users        # All unique members
SISMEMBER active_users "user:1"  # Check membership O(1)
SINTER premium_users active_users  # Intersection

📌 Use for: unique visitor tracking, tags, mutual friends, permissions sets

5. Sorted Set — Scored ranking

ZADD leaderboard 5000 "alice" 8000 "bob" 3000 "charlie"
ZRANGE leaderboard 0 -1 WITHSCORES  # Ascending order
ZREVRANGE leaderboard 0 2           # Top 3 (descending)
ZINCRBY leaderboard 500 "alice"     # Increment score

📌 Use for: leaderboards, rate limiting, priority queues, time-series events

6. Stream — Event log

XADD events * action "purchase" userId "1" amount "100"
XREAD COUNT 10 STREAMS events 0   # Read 10 events

📌 Use for: event sourcing, audit logs, message streaming

📊 Quick Reference Table

Structure	Commands	Best Use Case
String	GET, SET, INCR, EXPIRE	Cache, counters, sessions
Hash	HSET, HGET, HGETALL	Object fields (user profile)
List	LPUSH, RPOP, LRANGE	Queues, feeds
Set	SADD, SMEMBERS, SINTER	Unique collections, tags
Sorted Set	ZADD, ZRANGE, ZINCRBY	Leaderboards, rate limits
Stream	XADD, XREAD	Event streaming, audit log

💡 Possible Follow-up Questions

How does Redis handle persistence if it's in-memory? (RDB vs AOF)
What is Redis pub/sub and how does it work?
How do you implement rate limiting with Redis?
What is Redis Cluster and how does sharding work?
What is the difference between Redis EXPIRE and TTL?
How would you implement a distributed lock with Redis?

⚡ One-line Interview Answer

Redis is an in-memory data store that provides sub-millisecond latency using multiple data structures: Strings for caching and counters, Hashes for object storage, Lists for queues, Sets for unique collections, Sorted Sets for leaderboards and rate limiting, and Streams for event logging — making it suitable for caching, session management, real-time analytics, and message brokering.

🧠 Simple Definition

Redis stores data in RAM for speed, but provides two persistence mechanisms to prevent data loss on restart: RDB (Redis Database) creates periodic point-in-time binary snapshots of the entire dataset, while AOF (Append Only File) logs every write command sequentially to a file as it happens.

⚡ Super Simple Line

RDB = take a photo of data every N minutes (fast restart, some data loss risk).
AOF = record every write in a log (slower restart, maximum durability).
Production = use both together.

🔑 RDB — Snapshots

# redis.conf configuration
save 900 1    # Save if at least 1 key changed in 900 seconds
save 300 10   # Save if at least 10 keys changed in 300 seconds
save 60 10000 # Save if at least 10,000 keys changed in 60 seconds

# Manual snapshot
redis-cli BGSAVE  # Fork background process to create snapshot

# Snapshot file location
dbfilename dump.rdb
dir /var/lib/redis

How RDB works:

Redis forks a child process
Child writes entire memory dataset to a temp .rdb file
On success, temp file replaces the old dump.rdb
Main process continues serving requests (no blocking)

🔑 AOF — Append Only File

# redis.conf configuration
appendonly yes
appendfilename "appendonly.aof"

# Sync frequency options:
appendfsync always    # Sync every write — safest, slowest
appendfsync everysec  # Sync every 1 second — good balance (default)
appendfsync no        # Let OS decide — fastest, least durable

AOF Rewrite (Compaction):

# AOF file can grow large — rewrite compacts it
# (e.g., 1000 INCR operations → single SET with final value)
redis-cli BGREWRITEAOF  # background rewrite/compact the AOF file

📊 RDB vs AOF Comparison

Feature	RDB (Snapshots)	AOF (Append Only)
Durability	Lower — can lose changes since last snapshot	✅ Higher — max 1 second loss with everysec
Recovery Speed	✅ Very fast (direct binary load)	Slower (replay all commands)
File Size	✅ Compact binary file	Larger (all commands + needs rewrite)
Write Performance	✅ No overhead during snapshots	Slight overhead (fsync per second)
Point-in-time Recovery	✅ Can keep multiple snapshots	❌ Only last state
Human-readable	❌ Binary format	✅ Text log (easier to inspect)

🏭 Production Best Practice

# Enable BOTH for maximum durability + fast recovery
appendonly yes          # AOF for durability
appendfsync everysec    # 1 second max data loss
save 3600 1             # RDB backup every hour

# Recovery on startup:
# Redis uses AOF if both exist (more complete)
# Falls back to RDB if no AOF found

Why both?

AOF ensures maximum durability (1 second data loss max)
RDB provides fast restart (load binary snapshot, then replay only recent AOF)
RDB snapshots serve as backups and point-in-time recovery

💡 Possible Follow-up Questions

What happens to AOF data if Redis crashes mid-write?
What is AOF rewrite and why is it needed?
What is the BGSAVE command and how does it work?
How does Redis persistence affect performance?
What is Redis persistence vs Redis Sentinel vs Redis Cluster?

⚡ One-line Interview Answer

Redis persists data through two mechanisms: RDB creates periodic binary snapshots of the full dataset (fast restart, some data loss risk), and AOF logs every write command sequentially (maximum durability with up to 1 second loss using everysec sync). In production, both are enabled together — AOF ensures minimal data loss while RDB enables faster restart and serves as a point-in-time backup.

🧠 Simple Definition

Cache invalidation is the process of removing or updating cached data when the underlying source data changes. It's one of the hardest problems in computer science because you must balance keeping cache fresh (accuracy) vs keeping it fast (performance).

⚡ Super Simple Line

Cache invalidation = deciding WHEN and HOW to remove stale cached data and replace it with fresh data from the source.

📚 Cache Invalidation Strategies

1. Cache-Aside (Lazy Loading) — Most Common

async function getUser(userId) {
  // 1. Check cache first
  const cached = await redis.get(`user:${userId}`);
  if (cached) return JSON.parse(cached);  // ✅ cache hit

  // 2. Cache miss — fetch from DB
  const user = await db.users.findById(userId);

  // 3. Store in cache for next time
  await redis.setex(`user:${userId}`, 3600, JSON.stringify(user));

  return user;
}
// On update: explicitly delete the cache key
async function updateUser(userId, data) {
  await db.users.update(userId, data);
  await redis.del(`user:${userId}`);  // ← invalidate
}

✅ Pros: Simple, only caches what's actually needed
❌ Cons: First request after expiry is slow (cache miss)

2. Write-Through — Always Current

async function updateUser(userId, data) {
  // Write to DB AND cache simultaneously
  await Promise.all([
    db.users.update(userId, data),
    redis.setex(`user:${userId}`, 3600, JSON.stringify(data))
  ]);
}
// Cache is always fresh — no stale reads

✅ Pros: Cache always fresh, no cold start
❌ Cons: Slower writes (two writes per update), cache may hold unused data

3. Write-Behind (Write-Back) — Async

// Write to cache immediately, sync DB asynchronously
async function updateUser(userId, data) {
  await redis.setex(`user:${userId}`, 3600, JSON.stringify(data));
  queue.add('sync-db', { userId, data });  // background job
}

✅ Pros: Fastest write performance
❌ Cons: Risk of data loss if cache fails before DB sync

4. TTL (Time-to-Live) — Simplest

SET product:123 "{...}" EX 300  # Auto-expire in 5 minutes

✅ Pros: Zero application logic needed
❌ Cons: Data can be stale up to TTL duration; no instant invalidation

5. Event-Driven Invalidation — Most Precise

// On any write event, publish invalidation message
eventBus.on('user.updated', async ({ userId }) => {
  await redis.del(`user:${userId}`);
  await redis.del(`user:${userId}:profile`);
  await redis.del(`user:${userId}:permissions`);
});

✅ Pros: Precise, immediate invalidation
❌ Cons: Complex event system, risk of missed events

📊 Strategy Comparison

Strategy	Freshness	Write Speed	Complexity
Cache-Aside	Good (on miss)	✅ Fast	Low
Write-Through	✅ Best	Slower	Medium
Write-Behind	Good	✅✅ Fastest	High + risk
TTL	Acceptable	✅ Fast	Lowest
Event-Driven	✅ Best	✅ Fast	Highest

💡 Possible Follow-up Questions

What is a cache stampede and how do you prevent it?
How do you handle cache invalidation in a distributed system?
What is the difference between cache-aside and read-through caching?
How do you decide what TTL to use?
What happens if the cache goes down — how do you protect the database?

⚡ One-line Interview Answer

Cache invalidation determines when stale cached data should be removed or refreshed. The main strategies are cache-aside (fetch-on-miss, delete-on-write), write-through (update cache and DB together), write-behind (update cache first and sync DB asynchronously), TTL expiry (auto-expire after a time limit), and event-driven invalidation (explicitly delete specific cache keys when source data changes).

🧠 Simple Definition

A cache stampede (also called a dogpile effect or thundering herd) occurs when a popular cached value expires simultaneously for many concurrent requests. All of them detect the cache miss at the same moment and flood the database with the same query, potentially overwhelming it.

🔥 Why It Happens

Time: 12:00:00 — Cache key "homepage_data" set with TTL=60s
Time: 12:01:00 — TTL expires

At 12:01:00, 500 concurrent requests all:
  1. Check cache → MISS
  2. ALL query the database → 500 simultaneous DB queries ❌
  3. ALL write the result to cache → 500 redundant writes

🛡️ Prevention Strategies

1. Mutex / Lock (First request fetches, others wait)

async function getDataWithLock(key) {
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  // Try to acquire a lock
  const lockAcquired = await redis.set(
    `lock:${key}`, "1",
    "NX", "EX", 5  // NX = only set if not exists, expire in 5s
  );

  if (lockAcquired) {
    // This request got the lock — fetch and populate cache
    const data = await fetchFromDB(key);
    await redis.setex(key, 60, JSON.stringify(data));
    await redis.del(`lock:${key}`);
    return data;
  } else {
    // Another request has the lock — wait and retry
    await sleep(100);
    return getDataWithLock(key);  // retry
  }
}

2. Probabilistic Early Expiration (XFetch Algorithm)

// Re-cache BEFORE expiry based on probability
// Probability increases as TTL gets closer to 0
function shouldEarlyRecompute(ttlRemaining, beta = 1.0) {
  const now = Date.now() / 1000;
  return now - beta * Math.log(Math.random()) > (now + ttlRemaining);
}

async function getData(key) {
  const [cached, ttl] = await Promise.all([redis.get(key), redis.ttl(key)]);

  if (cached && !shouldEarlyRecompute(ttl)) {
    return JSON.parse(cached);
  }

  // Either miss or probabilistic early refresh
  const data = await fetchFromDB(key);
  await redis.setex(key, 60, JSON.stringify(data));
  return data;
}

3. Background Refresh (Stale-While-Revalidate)

async function getData(key) {
  const cached = await redis.get(key);

  // Return stale data immediately
  if (cached) {
    const { data, expiresAt } = JSON.parse(cached);

    // If close to expiry, refresh in background
    if (Date.now() > expiresAt - 5000) {
      fetchFromDB(key).then(fresh => {
        redis.setex(key, 60, JSON.stringify({
          data: fresh,
          expiresAt: Date.now() + 60000
        }));
      });
    }

    return data;  // ← user gets stale data instantly, no wait
  }

  // True cache miss — fetch and block
  const data = await fetchFromDB(key);
  await redis.setex(key, 60, JSON.stringify({ data, expiresAt: Date.now() + 60000 }));
  return data;
}

4. Jitter (Randomized TTL)

// Instead of all keys expiring at exactly the same time
const TTL = 60 + Math.floor(Math.random() * 30);  // 60-90 seconds
await redis.setex(key, TTL, data);
// Different keys expire at different times → no simultaneous stampede

📊 Strategy Comparison

Strategy	Complexity	User Experience	DB Protection
Mutex Lock	Medium	Wait (other users pause)	✅ Strong
Probabilistic Early Expire	Medium	Smooth (no wait)	✅ Good
Background Refresh	High	✅ Best (instant stale)	✅ Strong
TTL Jitter	Low	Normal	Moderate

💡 Possible Follow-up Questions

What is the thundering herd problem?
How does a distributed lock work in Redis?
What is stale-while-revalidate and when is it safe to use?
How would you prevent stampede for a real-time leaderboard?
What is the XFetch algorithm?

⚡ One-line Interview Answer

A cache stampede happens when many concurrent requests simultaneously miss the cache after a key expires and overwhelm the database with duplicate queries. Prevention strategies include mutex locking so only one request fetches and others wait, probabilistic early expiration to refresh before expiry, background refresh to serve stale data instantly while refreshing asynchronously, and randomized TTL jitter to prevent synchronized expiration.

🧠 Simple Definition

Queues build up when a component in a system receives work faster than it can process it. Identifying queue buildup points (bottlenecks) is essential for diagnosing performance problems and designing scalable systems.

⚡ Super Simple Line

Queue builds up wherever incoming rate > processing rate. Find the bottleneck, fix the bottleneck, monitor continuously.

📍 Where Queues Can Build Up

1. Network Layer

High latency or limited bandwidth causes packets to queue up in buffers
TCP connection pool exhaustion causes connection queue to grow
Fix: CDN, connection pooling (PgBouncer), compression, TCP tuning

2. Database

Slow queries hold locks → other queries queue waiting for the lock
Too many concurrent connections exceed DB connection limit
Unindexed queries cause sequential scans that are slow
Fix: Add indexes, use connection pooler, optimize queries, read replicas

-- See active queries and wait times in PostgreSQL
SELECT pid, query, state, wait_event, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

3. Application Server

CPU-bound processing uses all available threads → new requests queue in web server
Memory pressure causes GC pauses → spikes in response time
Node.js: blocking the event loop with synchronous CPU work
Fix: Horizontal scaling, worker threads, load balancing, async processing

4. Message Queue (Kafka, Redis Queue, BullMQ)

Consumer is slower than producer → queue depth grows indefinitely
Fix: Add more consumers (horizontal scaling), optimize processing, prioritize critical jobs

5. External APIs / Third-Party Services

Slow upstream APIs cause application to wait → thread/connection pool exhausted
Rate limiting from third-party → requests queue internally
Fix: Timeouts, circuit breakers, async/background processing, caching API responses

6. Application Code

N+1 queries, missing indexes, synchronous file I/O
CPU-intensive computations blocking the main thread
Fix: Code profiling, algorithmic improvements, offload to worker threads

🔍 How to Find Queue Buildup

Layer	Monitoring Tool	Signal
Database	pg_stat_activity, slow query logs	Long-running queries, many waiting
Application	APM (Datadog, New Relic), profiler	High CPU, memory, slow response p99
Message Queue	BullMQ dashboard, Kafka Consumer Lag	Growing queue depth, consumer lag
Network	netstat, load balancer metrics	Connection queue length, timeouts

🌍 Real-World Example: Diagnosing a Slow API

Symptom: API response times spiking to 5+ seconds

Investigation:
1. Check application logs → requests completing, no errors
2. Check DB slow query logs → found: 200ms query running 50x/second (N+1!)
3. Add index + fix N+1 → response drops to 50ms
4. Load test → now DB connection pool exhausts at high traffic
5. Add PgBouncer connection pooler → stable at 5,000 req/min

💡 Possible Follow-up Questions

What is a circuit breaker and how does it prevent queue buildup from cascading?
How do you monitor database connection pool usage?
What is consumer lag in Kafka and why does it matter?
How does the event loop queue in Node.js work?
What is backpressure and how do you handle it?

⚡ One-line Interview Answer

Queues build up wherever processing rate falls below arrival rate — common hotspots include the database (slow queries, lock contention, exhausted connection pool), the application server (CPU saturation, event loop blocking), message queues (slow consumers), external APIs (rate limiting or slow upstream), and the network (bandwidth or connection limits). I diagnose bottlenecks using observability tools (APM, slow query logs, queue depth metrics) and fix them with indexes, caching, horizontal scaling, connection pooling, or async background processing.

🧠 Simple Definition

Prisma Migrate is Prisma's schema-driven migration system. You define your database schema in schema.prisma, and Prisma generates the corresponding SQL migration files. In production, migrations are applied in strict order and tracked in the _prisma_migrations table.

🔄 Migration Workflow

Development Flow

# 1. Edit schema.prisma (add a new field)
# model User {
#   id    Int    @id
#   name  String
#   email String @unique  ← NEW FIELD
# }

# 2. Create migration (generates SQL file + applies to dev DB)
npx prisma migrate dev --name add_email_to_user

# Result: creates migration file:
# prisma/migrations/20240615_add_email_to_user/migration.sql
# Content: ALTER TABLE "User" ADD COLUMN "email" TEXT NOT NULL;

# 3. Prisma Client regenerated with new types automatically
npx prisma generate

Production Flow

# Apply pending migrations without prompting (safe for CI/CD)
npx prisma migrate deploy
# Reads _prisma_migrations table → applies only unapplied migrations in order

🚨 What Happens When a Migration Fails in Production?

_prisma_migrations table status:
| id  | migration_name              | finished_at | applied_steps | logs          |
|-----|-----------------------------|-------------|---------------|---------------|
| 1   | 20240614_initial_schema     | 2024-06-14  | 1             | null          |
| 2   | 20240615_add_email_to_user  | null        | 0             | ERROR: ...    |  ← FAILED

Recovery Steps

Identify the failure: prisma migrate status shows which migration failed
Fix the root cause: Either fix the migration SQL or fix the data issue
Option A — Resolve manually: Apply the fix SQL manually, then mark as resolved:
```
prisma migrate resolve --applied "20240615_add_email_to_user"
```
Option B — Create a new fix migration: If the original migration is too broken to salvage, create a new migration that corrects the state

🛡️ Production Migration Best Practices

✅ Always backup before migrating production
✅ Use backward-compatible changes: Add nullable columns, never drop/rename in one step
✅ Run migrations BEFORE deploying new code (expand-contract pattern)
✅ Test migrations on a staging environment first
✅ Keep migrations small and focused — one change per migration
❌ Never edit migration files after they've been applied to production

🧪 Expand-Contract Pattern (Zero Downtime)

# Step 1: EXPAND — add new column (nullable, backward compatible)
ALTER TABLE users ADD COLUMN username TEXT;  # nullable, old code works fine

# Step 2: Deploy new code that writes to BOTH old and new columns

# Step 3: Migrate existing data in batches
UPDATE users SET username = email WHERE username IS NULL;

# Step 4: CONTRACT — make required, drop old if needed
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

💡 Possible Follow-up Questions

What is the _prisma_migrations table and how does it work?
How do you run zero-downtime migrations?
What is the difference between prisma migrate dev and prisma migrate deploy?
How do you handle a migration that locks a production table?
What is prisma db push and when would you use it instead of migrate?

⚡ One-line Interview Answer

Prisma Migrate generates SQL migration files from schema.prisma changes using prisma migrate dev in development, and applies them in strict order in production via prisma migrate deploy, tracking each in the _prisma_migrations table. When a production migration fails, I identify the failed entry, fix the underlying issue, and use prisma migrate resolve or create a corrective migration — always backing up first and using backward-compatible expand-contract patterns for zero downtime.

🧠 Simple Definition

Zero-downtime database migrations update the database schema without taking the application offline or causing errors in currently-running application instances. This requires all schema changes to be backward-compatible — meaning both the old and new versions of your application code can query the same database simultaneously during a rolling deployment.

⚡ Super Simple Line

Zero-downtime migration = change DB schema without breaking the old app version that's still running while the new version deploys.
Key rule: Never rename or drop columns in a single step.

🔄 The Expand-Contract Pattern (3 Phases)

Phase 1: EXPAND — Add, Don't Remove

-- Add new column as nullable (old app doesn't know about it yet — that's OK)
ALTER TABLE users ADD COLUMN username TEXT;
-- ✅ Old app still works: it ignores the new column
-- ✅ New app can start writing to username

Phase 2: MIGRATE — Dual-Write + Backfill

// New app code writes to BOTH old and new columns
async function updateUser(id: number, data: UserData) {
  await prisma.user.update({
    where: { id },
    data: {
      email: data.email,      // old column — still written for old app instances
      username: data.username  // new column — written by new app instances
    }
  });
}

// Background job to backfill existing rows
async function backfillUsernames() {
  let cursor = 0;
  while (true) {
    const batch = await db.query(
      "SELECT id, email FROM users WHERE username IS NULL LIMIT 1000"
    );
    if (batch.length === 0) break;

    await db.query(
      "UPDATE users SET username = email WHERE id = ANY($1)",
      [batch.map(r => r.id)]
    );
    await sleep(100);  // throttle to avoid DB overload
  }
}

Phase 3: CONTRACT — Make Required, Drop Old

-- Only after ALL app instances run new code + backfill is complete
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- Later (separate deployment), drop the old column
ALTER TABLE users DROP COLUMN email;  -- only when no code references it anymore

🚨 Dangerous Migrations (Avoid These Patterns)

Operation	Risk	Safe Alternative
Rename column	Old app can't find old column name → crash	Add new column + migrate + drop old
Drop column	Old app still reads it → error	Remove from code first, then drop column
Add NOT NULL without default	Old app inserts without new column → constraint error	Add nullable first, backfill, then add NOT NULL
Change column type	Old data may not convert → runtime error	Add new column with new type, migrate, drop old
Add index without CONCURRENTLY	Table locked during index build → downtime	CREATE INDEX CONCURRENTLY

🔧 PostgreSQL: Create Index Without Locking

-- ❌ Locks the table during build (downtime)
CREATE INDEX idx_users_email ON users(email);

-- ✅ No table lock — safe in production
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Slightly slower to build but doesn't block reads or writes

🛠️ Deployment Pipeline

1. Backup production database
2. Run migrations (backward-compatible only — expand phase)
3. Deploy new app version (rolling update — old + new running together)
4. Verify application health
5. Run backfill job (background, throttled)
6. Run contract migration (remove old columns) in a LATER deployment

💡 Possible Follow-up Questions

What is the expand-contract pattern in detail?
How do you create an index on a production table without downtime?
How do you handle a migration that must change a column type?
How does blue-green deployment interact with database migrations?
What is CREATE INDEX CONCURRENTLY and when must you use it?

⚡ One-line Interview Answer

Zero-downtime migrations require backward-compatible schema changes using the expand-contract pattern: in the expand phase, add new columns as nullable while keeping old ones; deploy new code that writes to both; backfill existing rows; and only in a later contract phase make columns required and drop old ones. Critical techniques include CREATE INDEX CONCURRENTLY to avoid table locks, never renaming or dropping columns in a single deployment, and always running migrations before deploying new code in a CI/CD pipeline.

🧠 Simple Definition

Database scaling increases capacity and performance under growing load. Read replication copies data to multiple read-only replicas to distribute read queries. Sharding horizontally partitions data across multiple database servers (shards) so each shard holds only a subset of the data, scaling both reads and writes.

⚡ Super Simple Line

Replication = copy the entire DB to scale reads.
Sharding = split the data across servers to scale reads + writes + storage.
Vertical scaling = bigger machine. Horizontal = more machines.

📈 Vertical Scaling (Scale Up)

Give the existing server more CPU, RAM, faster SSD
✅ Simple — no code changes
❌ Has a hard limit (you can't buy infinite RAM)
❌ Single point of failure

📈 Read Replication (Scale Reads)

            ┌─── Primary (Read/Write) ───┐
            │                             │
        Read Replica 1              Read Replica 2
        (Read Only)                 (Read Only)

Writes → Primary → async replication → Replicas
Reads → Any Replica (load balanced)

// Route reads to replica, writes to primary
const primaryDb = new PrismaClient({ datasourceUrl: PRIMARY_URL });
const replicaDb = new PrismaClient({ datasourceUrl: REPLICA_URL });

// Write → primary
await primaryDb.user.create({ data: { email } });

// Read → replica (can serve stale by replication lag)
const users = await replicaDb.user.findMany();

✅ Pros: Low complexity, built into PostgreSQL/MySQL/MongoDB, good for read-heavy apps
❌ Cons: All writes still go to primary (write bottleneck remains), replication lag (eventual consistency)

📈 Sharding (Horizontal Partitioning)

Shard Key: user_id % 3

user_id 1,4,7,10  → Shard 0
user_id 2,5,8,11  → Shard 1
user_id 3,6,9,12  → Shard 2

Each shard is an independent DB with ~33% of the data
Both reads and writes distributed across shards

Common Sharding Strategies

Strategy	How	Pros	Cons
Range-based	user_id 1-1000 → Shard 1, 1001-2000 → Shard 2	Easy range queries	Hot spots if not balanced
Hash-based	hash(user_id) % N → Shard N	Even distribution	No range queries
Directory-based	Lookup table maps key → shard	Flexible	Lookup overhead
Geographic	EU users → EU shard	Compliance (GDPR)	Imbalanced if users concentrated

📊 Replication vs Sharding

Feature	Read Replication	Sharding
Scales What?	Read throughput	Reads + Writes + Storage
Data Distribution	Full copy on every replica	Subset per shard
Write bottleneck	❌ Still single primary	✅ Distributed across shards
Complexity	Low (native DB feature)	High (custom routing, cross-shard queries)
Cross-shard JOINs	N/A	❌ Very complex / expensive
Implementation	Built-in (pglogical, streaming)	Application logic or Vitess/Citus

⚠️ Sharding Pitfalls

Cross-shard JOINs are extremely expensive (often impossible)
Resharding when you outgrow your shard count is painful
Hot shards (popular shard key values) can become bottlenecks
Choose shard key very carefully — it's very hard to change later

💡 Possible Follow-up Questions

What is replication lag and how does it cause consistency issues?
How do you choose a sharding key?
What is Citus and how does it extend PostgreSQL with sharding?
What is MongoDB Atlas sharding and how does it work?
What is a hot shard and how do you avoid it?

⚡ One-line Interview Answer

Read replication distributes read queries across identical copies of the full dataset (good for read-heavy workloads, low complexity), while sharding horizontally partitions data across multiple servers based on a shard key (scales both reads and writes and storage, but introduces cross-shard query complexity and requires careful shard key selection). In practice, I'd use read replicas first, only moving to sharding when write throughput or storage size becomes the actual bottleneck.

Databases

What is Primary Key vs Foreign Key?

🧠 Simple Definition

🧪 SQL Example

🧪 Prisma Schema Example

📊 Key Properties

🔑 Foreign Key Actions

🌍 Composite Primary Key

💡 Possible Follow-up Questions

⚡ One-line Interview Answer

Difference between WHERE and HAVING?

🧠 Simple Definition

⚡ Super Simple Line

🧪 Example: Both in Same Query

Execution order:

❌ Common Mistake: Using Aggregate in WHERE

📊 WHERE vs HAVING

🌍 Real-World Examples

💡 Possible Follow-up Questions

What is the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN? Explain types of SQL JOINs.

🧠 Simple Definition

⚡ Super Simple Line

🧪 Sample Data Setup

🧪 JOIN Examples

📊 Visual Comparison

🌍 Real-World Use Cases

💡 Possible Follow-up Questions

⚡ One-line Interview Answer

What is a Subquery? How does it work?

🧠 Simple Definition

🧪 Types of Subqueries

1. Scalar Subquery — Returns a single value

2. Row Subquery — Returns a single row

3. Table Subquery (Derived Table) — Returns multiple rows/columns

4. Correlated Subquery — References outer query

5. EXISTS Subquery — Check existence

📊 Subquery vs JOIN vs CTE

⚠️ Performance Note

💡 Possible Follow-up Questions

⚡ One-line Interview Answer

What is a CTE? When over a subquery?

🧠 Simple Definition

⚡ Super Simple Line

🧪 Basic CTE Syntax

🧪 Multiple CTEs (Chained)

🧪 Recursive CTE (Hierarchy / Tree)

📊 CTE vs Subquery

🌍 When to Use CTE Over Subquery

💡 Possible Follow-up Questions

⚡ One-line Interview Answer

What are window functions? Example with RANK().

🧠 Simple Definition

⚡ Super Simple Line

🔑 Syntax

🧪 RANK() Example

📊 Common Window Functions

🧪 LAG/LEAD Example (Month-over-Month Growth)

🧪 Running Total Example

🌍 Real-World Use Cases

💡 Possible Follow-up Questions

⚡ One-line Interview Answer

Difference between DELETE, TRUNCATE, DROP?

🧠 Simple Definition

⚡ Super Simple Line

🧪 Examples

📊 Comparison Table

🌍 When to Use Each

🚨 PostgreSQL Note on TRUNCATE

💡 Possible Follow-up Questions

⚡ One-line Interview Answer

What is Stored Procedure?

🧠 Simple Definition

🧪 Creating and Calling a Stored Procedure (PostgreSQL)

📊 Stored Procedure vs Function vs Application Code

✅ When to Use Stored Procedures

❌ When to Avoid Stored Procedures

🌍 Real-World Use Cases

💡 Possible Follow-up Questions

⚡ One-line Interview Answer

What is database normalization? When is denormalization intentional, and why is it important in database design?