Phase 2: Databases
Explore the concepts of Databases.
SQL vs NoSQL
SQL (relational) databases store data in tables with a predefined schema and query it with structured query language, while NoSQL (non-relational) databases trade a rigid schema for flexible data models and easier horizontal scale. Choosing between them is one of the earliest and most common decisions in a system design interview. 🧠 Mental model: SQL is a spreadsheet with strict columns - every row follows the same rules. NoSQL is a filing cabinet - each folder can hold different shaped documents, and you grab them by the label on the tab.
NoSQL Database Types
NoSQL databases fall into four broad categories - key-value, document, wide-column, and graph - each optimized for a different data shape and access pattern. Matching a category to a workload is far more useful in an interview than memorizing product names. 🧠 Mental model: Key-value = a dictionary (look up by word). Document = a folder of papers (each paper is different). Wide-column = a spreadsheet where each row can have different columns. Graph = a social network map (nodes and connections).
Database Indexing
An index is an auxiliary data structure that lets a database locate rows without scanning an entire table, trading extra storage and slower writes for dramatically faster reads. It is the first lever most engineers reach for when queries are slow. 🧠 Mental model: An index is like the index at the back of a textbook. Without it, you'd flip through every page to find "B-tree." With it, you look up the page number directly. But every time the book is updated, the index must be updated too.
Normalization vs Denormalization
Normalization organizes data into multiple related tables to remove redundancy, while denormalization deliberately duplicates data across tables or documents to make reads faster. System designs constantly trade one against the other depending on whether reads or writes dominate. 🧠 Mental model: Normalization is a well-organized filing system - each fact stored once, in one drawer. Denormalization is photocopying a form and putting a copy in every drawer that needs it - faster to grab, but if the form changes, you must update every copy.
ACID vs BASE
ACID (Atomicity, Consistency, Isolation, Durability) names the strong guarantees of traditional transactional databases, while BASE (Basically Available, Soft state, Eventual consistency) names the relaxed model many distributed stores adopt to stay available at scale. They mark opposite ends of a spectrum between correctness and availability. 🧠 Mental model: ACID is a bank teller - every transaction is precise, verified, and recorded before you leave. BASE is a coffee shop loyalty card - your points might take a moment to update across all stores, but you won't lose them.
Transactions & Isolation Levels
In database systems, a transaction is a sequence of operations performed as a single logical unit of work. Isolation levels define the degree to which a transaction must be isolated from the data modifications made by other concurrent transactions. 🧠 Mental model: Imagine multiple people editing the same shared document. An isolation level dictates whether someone can see your drafted sentences before you hit "save" (commit), or if they must wait until you are completely done.
Database Replication
Replication keeps copies of the same data on multiple database nodes so the system can survive machine failures and serve more read traffic. The two dominant topologies are leader-follower replication and multi-leader replication. 🧠 Mental model: Replication is like having multiple copies of the same book in different libraries. If one library burns down, the book isn't lost. But if someone updates the book in one library, the other copies take time to get the new edition.
Sharding & Partitioning
When a database becomes too large to fit on a single machine, or when the write throughput exceeds what one node can handle, the data must be split into smaller, independent pieces called shards or partitions. Each piece is hosted on a separate node, allowing the database to scale horizontally almost indefinitely. 🧠 Mental model: Sharding is like splitting a massive library into several smaller buildings by author's last name. Building A holds authors A-F, Building B holds G-M, etc. You can handle much more traffic, but if someone wants books by both Asimov and Tolkien, they must visit two buildings.
Storage Engine Internals
A storage engine is the component inside a database that actually writes data to and reads it from disk. The two dominant designs are B-tree-based engines, which update data in place and favor reads, and LSM-tree-based engines, which append writes and merge them in the background to favor high write throughput. 🧠 Mental model: B-tree is like a well-organized filing cabinet - every document has a fixed slot, fast to find, but rearranging on every insert is work. LSM-tree is like a pile of sticky notes - write fast by just adding to the pile, then sort and merge them later.
Object & Blob Storage
Object storage (or blob storage) is an architecture that manages data as objects, as opposed to file systems which manage data as a directory hierarchy, or block storage which manages data as sectors and tracks. It is designed to store massive amounts of unstructured data like images, videos, backups, and logs efficiently and cheaply. 🧠 Mental model: Think of a valet parking lot vs a self-parking garage. In a file system (garage), you navigate levels and aisles to find your car. In object storage (valet), you hand the valet a ticket (the unique ID) and they fetch your car (the blob). You don't care where it was parked.
Connection Pooling
Connection pooling is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Opening a new database connection is computationally expensive and slow; pooling mitigates this overhead. 🧠 Mental model: Imagine calling a busy call center. If every time you had a quick question you had to dial, go through the phone tree, and wait on hold, it would take forever. Connection pooling is like having a dedicated line already open to an agent - you just speak your question, get the answer, and hand the phone to the next person.