Goal: “The best index is the one you don’t have to think about — until a query takes 30 seconds.” — This week we open the hood on how databases actually find data and how they keep it consistent when multiple things happen at once. Indexes and transactions are the backbone of every production database.
Topics
| Day | Track | Focus | Topics |
|---|---|---|---|
| Mon | A — DSA Practice | DP Patterns (1D) | Solve 2-3 problems. Focus on classic 1D DP — longest increasing subsequence, coin change, house robber variants. |
| Tue | B — DBMS | Indexes & Query Plans | How Indexes Work · B-Tree and B+ Tree · Types of Indexes · EXPLAIN and Query Plans |
| Wed | A — DSA Practice | DP Patterns (2D & Knapsack) | Solve 2-3 problems. Try 2D grid DP, 0/1 knapsack, and unbounded knapsack variants. |
| Thu | B — DBMS | Optimization & Transactions | Query Optimization · Full-Text Search · Transactions Deep Dive · Isolation Levels |
| Fri | B — DBMS | Concurrency Control | Locking Mechanisms · Deadlocks · MVCC · Optimistic vs Pessimistic Locking |
Key Concepts
- B+ Trees are what most databases actually use for indexing — all data lives in leaf nodes, and leaf nodes are linked together for efficient range scans. B-Trees store data in internal nodes too, which makes them less efficient for sequential reads.
EXPLAINis our best friend for debugging slow queries. Learn to read query plans — look for sequential scans on large tables (bad), index scans (good), and the estimated vs actual row counts.- Isolation levels trade consistency for performance. From weakest to strongest: Read Uncommitted → Read Committed → Repeatable Read → Serializable. Most production databases default to Read Committed (PostgreSQL) or Repeatable Read (MySQL InnoDB).
- MVCC (Multi-Version Concurrency Control) is how PostgreSQL handles concurrent reads and writes without locking. Each transaction sees a snapshot of the data — readers never block writers and writers never block readers.
- Deadlocks happen when two transactions are each waiting for the other to release a lock. Databases detect these automatically and kill one transaction — our job is to design queries that minimize the chance.
Practice
- DSA: Solve 5-6 DP problems this week — mix of 1D, 2D grid, and knapsack variants
- DBMS: Run
EXPLAIN ANALYZEon 3 different queries (one with a JOIN, one with a subquery, one with GROUP BY) and analyze the output — identify where indexes would help - DBMS: Explain the difference between Read Committed and Repeatable Read with concrete examples — what anomalies does each prevent?
- DBMS: Describe a scenario where optimistic locking is better than pessimistic locking, and vice versa
~12 topics + DSA practice · ~2 hrs/day · Deep dive into how databases really work