DatabaseAdvanced

SQL & Relational Database Internals

Relational databases provide ACID guarantees through Write-Ahead Logging (WAL) and B-Tree indexing structures.

Key Mental Models & Invariants

-B-Trees maintain logarithmic O(log N) lookup, insertion, and range queries.
-Write-Ahead Logging (WAL) ensures durability before data pages are flushed to disk.
-Transaction isolation levels (Read Committed -> Repeatable Read -> Serializable).
-Composite indexes obey the leftmost prefix rule.

Deep Dive Architecture

### Indexing & The Leftmost Prefix Rule If you create an index on `(tenant_id, created_at, status)`: - [OK] `WHERE tenant_id = 1` (Uses index) - [OK] `WHERE tenant_id = 1 AND created_at > '2026-01-01'` (Uses index) - [NO] `WHERE created_at > '2026-01-01'` (Cannot use index; misses tenant_id)

Code Examplesql

-- Finding second-highest salary with Window Function
WITH RankedSalaries AS (
  SELECT employee_id, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) as rank
  FROM employees
)
SELECT salary FROM RankedSalaries WHERE rank = 2;

DENSE_RANK handles ties properly without skipping rank numbers.