Databases — Beyond SELECT *
The gap between knowing SQL and understanding databases is enormous. SQL is a query language. Databases are complex storage systems with their own internal architectures, optimization algorithms, concurrency models, and failure modes. An engineer who only knows SQL can write queries that work. An engineer who understands databases can write queries that scale, design schemas that perform under load, and configure the database for the workload it's handling. This module goes beyond queries into the machinery underneath. You'll understand the relational model at a mathematical level — why normalization matters and when to violate it. You'll understand how B-tree indexes are structured and why a composite index works for some queries but not others. You'll understand what 'transactions are ACID' actually means — atomicity, consistency, isolation, durability — and what happens at each isolation level when two transactions run concurrently. You'll understand MVCC, the mechanism that lets PostgreSQL serve reads without blocking writes. The module also covers NoSQL in an honest way: not as a replacement for relational databases but as a set of tradeoffs that are appropriate for certain access patterns. Document stores, key-value stores, column-family databases, and graph databases all have genuine use cases. Understanding when to reach for each one, and when the relational model is still the right answer, is the kind of database wisdom that only comes from understanding all the options.
What You'll Learn
-
1
The Relational Model — Tables, keys, normalization, when to denormalize
-
2
SQL Deep Dive — Joins, subqueries, window functions, CTEs
-
3
Indexing — B-trees, hash indexes, composite indexes, why queries are slow
-
4
Query Planning — How the database decides what to do with your query
-
5
Transactions and ACID — Isolation levels, locking, MVCC
-
6
NoSQL — Document stores, key-value, column-family, graph databases
-
7
Data Modeling — Choosing the right database and schema for the problem
Capstone Project: Build a Query Optimizer
Build a tool that takes a slow SQL query, analyzes its EXPLAIN plan, suggests indexes, rewrites the query if necessary, and documents the reasoning behind each optimization — then applies the changes to a test database and measures the actual performance improvement. You'll work with multi-million row tables, complex joins, and queries with multiple optimization opportunities, gaining practical experience in the query optimization process that database administrators and backend engineers use daily.
Why This Matters for Your Career
Database performance is the bottleneck in most web applications, and database bugs are some of the most expensive: they affect every user, they often corrupt data, and they can be invisible until the system is under load. Engineers who understand indexing, query planning, and transaction isolation are the ones who catch these issues before they reach production. Transaction isolation in particular is a source of subtle, hard-to-reproduce bugs that surface under concurrency. Phantom reads, non-repeatable reads, and lost updates — these are database consistency anomalies that occur at the wrong isolation level. Understanding what MVCC is and how different isolation levels prevent different anomalies is what lets you configure your database correctly for your workload. The NoSQL landscape has matured significantly, and the right database choice can dramatically simplify application code. A graph database for a social network, a time-series database for metrics, a document store for flexible schema data — knowing these options and their genuine tradeoffs, rather than using a relational database for everything by default, is the mark of an engineer who thinks carefully about their architecture.