Advanced SQL Fundamentals (N+1, Index, Transaction, Isolation Level)

1. N+1 query

• Occurs when fetching N records in an initial query, then executing an additional N separate queries inside a loop to fetch related data for each record, resulting in a total of N+1 queries:

const customers = await db.customer.findMany(); // 1 query
for (const customer of customers) {
  const orders = await db.order.findMany({ where: { customerId: customer.id } }); // N queries
}

• Performance issue: each query incurs 1 network round-trip to the DB. With production-scale data (thousands/millions of records), the total latency grows linearly, easily exceeding timeouts (for example, 10,000 customers x 5ms/query = 50 seconds, unacceptable for a single API request).
• Solution: use include (Prisma) or relations (TypeORM) so the ORM automatically combines these into 1-2 queries:

const customers = await db.customer.findMany({
  include: { orders: true }
});

• Real-world risk: this issue remains hidden during local testing (small datasets, N+1 still feels fast) and only surfaces in production (large datasets) as the API slows down over time. It is difficult to trace without knowing the concept to investigate in the right direction, making it a frequently asked topic in backend interviews.

2. Index

• Without an index: the DB must perform a full table scan, scanning every row sequentially to find matching WHERE conditions, with a time complexity of O(n).
• With an index: the DB uses a tree structure (typically a B-Tree), pre-sorted by the indexed column, reducing search complexity to O(log n) (analogous to the principle that binary search only works on sorted data).
• Trade-off: faster reads, but slower writes (INSERT/UPDATE/DELETE), because every write operation must update the tree structures of all indexes on the table, not just write to the main table.
• Practical rules:
  1. Only index columns that are frequently used for filtering or sorting (WHERE, ORDER BY, JOIN ON).
  2. Consider the table's write frequency: read-heavy/write-light tables (e.g. catalogs) can be indexed generously; write-heavy/simple-read tables (e.g. logs) should keep index counts minimal.

3. Transactions and ACID

• Problem to solve: executing 2 separate UPDATE queries without a transaction (for example, a funds transfer: deduct account A, credit account B), where a server crash between steps leaves the data in an inconsistent state (A loses money, B never receives it).
• Transactions group a set of operations into a single unit:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

• If an error occurs midway, ROLLBACK aborts all changes within the transaction, reverting the data back to its exact state before the transaction began.
• ACID:
  • Atomicity: the entire group of operations acts as 1 unit; all succeed or all rollback, with no "half-finished" states.
  • Consistency: transactions transition the DB from one valid state to another, preserving all constraints (for example, stock count never drops below zero).
  • Isolation: concurrently running transactions cannot see each other's intermediate, uncommitted states; they only see results after COMMIT.
  • Durability: once committed, data is guaranteed to persist permanently, even if the server crashes immediately afterward.

4. Isolation level

• Stricter isolation requires transactions to wait on one another more frequently (more locking), reducing concurrency and throughput. This is the core trade-off, which is why SQL allows choosing an appropriate isolation level rather than enforcing the highest level unconditionally.
• 3 concurrency anomalies to know:
  • Dirty read: transaction B reads uncommitted data from transaction A (which might subsequently be rolled back), leading to corrupted logic if B relies on that data.
  • Non-repeatable read: within the same transaction, reading the same row twice yields 2 different values because another transaction committed changes between the two reads.
  • Phantom read: similar, but occurs at the row-set level: running the same SELECT ... WHERE twice returns new rows the second time because another transaction inserted and committed them.
• The 4 isolation levels (lowest to highest) and the anomalies each level prevents:

• READ COMMITTED is the most common default (PostgreSQL, Oracle), providing a balanced trade-off between safety and performance for most applications.