Database Concepts

6 min read

The Theory Behind Every Storage Decision

Choosing a database is only half the battle. Understanding how databases work internally — how they guarantee correctness, handle concurrent access, distribute data across machines, and optimize queries — is what separates someone who can name databases from someone who can design with them. These concepts are database-agnostic: ACID properties apply to PostgreSQL and CockroachDB alike; CAP theorem governs every distributed system whether it's Cassandra or Spanner; indexing matters whether you're using MySQL or MongoDB.

This is the theory layer that makes Topic 03 (Data Storage) actionable. If Data Storage tells you which database to pick, Database Concepts tells you why that choice has the trade-offs it does — and what knobs you can turn once you've picked.

When This Comes Up

System design interviews: "How would you handle concurrent writes to the same row?" → isolation levels. "What happens when a node goes down?" → CAP theorem. "How would you scale this database?" → sharding and replication. These questions test whether you understand the mechanics, not just the product names.
Production debugging: Deadlocks, stale reads, hot partitions, slow queries — every one of these maps to a concept below. Engineers who understand the theory can diagnose and fix production issues; those who don't just restart things.
Architecture reviews: "Why did you choose eventual consistency here?" is a question you'll be asked in design doc reviews. The answer must reference CAP, the consistency model, and the business justification.

How the Sub-Topics Connect

The sub-topics progress from correctness guarantees (ACID, transactions, isolation) → distributed system trade-offs (CAP, consistency models) → performance mechanics (indexing) → data organization (normalization) → scale (sharding, replication):

1. ACID Properties

The four guarantees that make relational databases trustworthy: Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). ACID is the reason you can trust a bank transfer — if the debit succeeds but the credit fails, the entire transaction rolls back. Understanding ACID is the foundation for understanding why some databases offer it and others trade it away for performance.

2. Transactions & Isolation Levels

Transactions group multiple operations into an atomic unit. But how isolated are concurrent transactions from each other? The answer is the isolation level, and it's a spectrum: from Read Uncommitted (fastest, least safe — you can see other transactions' uncommitted changes) to Serializable (slowest, safest — behaves as if transactions ran one at a time). Most production databases default to Read Committed or Repeatable Read — understanding what anomalies each level allows is critical for correctness.

3. CAP Theorem

In a distributed system, you can have at most two of three: Consistency (every read returns the latest write), Availability (every request gets a response), and Partition Tolerance (the system works despite network failures). Since network partitions are inevitable in distributed systems, the real choice is between CP (consistent but may reject requests during a partition) and AP (available but may return stale data). CAP explains why Cassandra and DynamoDB are AP while Spanner and CockroachDB are CP.

4. Consistency Models

CAP gives you a binary (CP or AP), but reality is a spectrum. Consistency models describe how fresh a read is relative to the latest write. Strong consistency (linearizability) means every read sees the latest write — expensive but simple to reason about. Eventual consistency means reads might be stale, but will catch up — cheap but requires the application to handle staleness. In between are causal consistency, read-your-writes, and monotonic reads. The right model depends on the use case: strong for payments, eventual for social media likes.

5. Indexing

Without an index, every query is a full table scan — O(n). With the right index, it's O(log n) or O(1). Indexing is the single most impactful performance optimization in any database. B-tree indexes (the default) work for range queries and equality lookups. Hash indexes work for exact matches. Composite indexes cover multi-column queries. Full-text indexes power search. Understanding when to add an index, what to index, and the write overhead of each index is essential for every system design.

6. Normalization & Denormalization

Normalization eliminates data redundancy — each fact stored exactly once (3NF). Denormalization reintroduces redundancy for read performance — storing precomputed or duplicated data to avoid expensive joins. The tension is: normalized schemas are correct and compact but slow to read (joins); denormalized schemas are fast to read but harder to keep consistent (updates must propagate). In practice, most systems start normalized and selectively denormalize hot read paths.

7. Sharding & Partitioning

When a single database machine can't hold your data or handle your traffic, you split the data across multiple machines — that's sharding (horizontal partitioning). The shard key determines which machine holds which data. Choose a good key (high cardinality, even distribution) and queries route to one shard. Choose a bad key and you get hot shards, cross-shard queries, and uneven growth. Sharding is the most impactful and most painful scaling decision you'll make.

8. Replication

Replication copies data to multiple machines for durability, availability, and read scaling. Single-leader replication (one primary, many replicas) is the simplest and most common. Multi-leader replication allows writes at multiple nodes but creates conflict resolution challenges. Leaderless replication (Dynamo-style) writes to multiple nodes and uses quorum reads for consistency. The replication strategy determines your system's behavior during failures — and failures will happen.

Sub-Topics

#	Sub-Topic	What You'll Learn
1	ACID Properties	The four guarantees that make relational databases trustworthy
2	Transactions & Isolation Levels	How concurrent transactions interact — and what anomalies to watch for
3	CAP Theorem	The fundamental trade-off in every distributed database
4	Consistency Models	The spectrum from strong to eventual consistency
5	Indexing	The most impactful query performance optimization
6	Normalization & Denormalization	Balancing data integrity against read performance
7	Sharding & Partitioning	Splitting data across machines for horizontal scale
8	Replication	Copying data for durability, availability, and read throughput