Databases in System Design

The Paper That Changed How the World Stores Data

The year is 2007. Amazon's engineers publish a research paper titled "Dynamo: Amazon's Highly Available Key-Value Store." In it, they describe a radical design decision: in order to ensure that a shopping cart never fails to accept an item, they deliberately sacrificed strict data consistency.

A traditional relational database would refuse a write if it could not guarantee correctness across all replicas. Amazon chose the opposite: always accept the write, reconcile conflicts later. The shopping cart might briefly show a duplicate item, but it would never show a confusing error to a customer trying to buy something.

This paper shocked the database world. It said, plainly, that the decades-old assumption — that every database must be ACID-compliant and strongly consistent — was wrong for certain problems. Within five years, dozens of new database systems had been built on Dynamo's ideas. The NoSQL revolution had begun. Understanding the trade-off Amazon chose is the foundation of modern database design.

Relational Databases (SQL)

Relational databases, invented by Edgar Codd at IBM in 1970, organise data into tables with rows and columns. Relationships between tables are expressed through foreign keys. Queries are written in SQL (Structured Query Language).

The defining guarantee of relational databases is ACID:

• Atomicity: A transaction either completes entirely or not at all. No partial writes.
• Consistency: Every transaction leaves the database in a valid state.
• Isolation: Concurrent transactions behave as if they ran sequentially.
• Durability: Once committed, a transaction survives crashes.

These guarantees make relational databases the right choice for financial systems, e-commerce orders, and any data where correctness is non-negotiable.

When to use SQL:

• Structured data with well-defined schemas
• Complex queries with multiple joins
• ACID transactions (payments, inventory, bookings)
• Reporting and analytics with ad-hoc queries

Major systems: PostgreSQL, MySQL, Oracle, Microsoft SQL Server.

Scaling SQL:

SQL databases scale vertically (bigger servers) by default, but several techniques extend their capacity:

• Read replicas: Route all reads to replica databases, reserving the primary for writes. Instagram used this pattern heavily.
• Connection pooling (e.g., PgBouncer): Reuse database connections rather than creating thousands of new ones.
• Partitioning: Split a single large table into smaller physical pieces on the same server (range partitioning by date is common for logs and events).

NoSQL: Four Distinct Categories

"NoSQL" is not one thing — it is a family of four different data models, each optimised for different access patterns.

Document Databases

Store data as JSON-like documents. Each document can have a different structure — no fixed schema.

Examples: MongoDB, Google Firestore, CouchDB.

Best for: Product catalogues (each product has different attributes), user profiles, content management systems, applications where the schema evolves frequently.

Example document:

{
  "user_id": "u_8472",
  "name": "Priya Sharma",
  "preferences": { "theme": "dark", "language": "en" },
  "addresses": [
    { "type": "home", "city": "Bangalore" }
  ]
}

Key-Value Databases

The simplest model: a dictionary. Look up any value instantly by its key.

Examples: Redis, Amazon DynamoDB, Riak.

Best for: Session storage, shopping carts, caching, feature flags, leaderboards. Any access pattern that is always "get item by ID."

Wide-Column Databases

Store data in tables, but each row can have a different set of columns. Designed for massive scale — billions of rows distributed across hundreds of servers.

Examples: Apache Cassandra (originally built at Facebook), Google Bigtable, HBase.

Best for: Time-series data, IoT sensor readings, messaging (Instagram's message history used Cassandra), write-heavy workloads distributed globally.

Graph Databases

Model data as nodes (entities) and edges (relationships). Finding deeply connected data is fast because relationships are first-class citizens, not JOINs.

Examples: Neo4j, Amazon Neptune.

Best for: Social networks ("friends of friends"), fraud detection (unusual relationship patterns), recommendation engines, knowledge graphs.

SQL vs NoSQL: A Decision Framework

Sharding: Horizontal Partitioning

When a single database server cannot hold all your data, you shard — split the data across multiple servers, each responsible for a portion.

Three sharding strategies:

1. Hash sharding: Apply a hash function to the partition key (e.g., hash(user_id) % 4). Distributes data evenly. Problem: adding a new shard requires resharding everything — consistent hashing solves this.

2. Range sharding: Shard by value ranges. Shard 1 holds orders from 2020, Shard 2 holds orders from 2021. Simple, but creates hot spots if one range receives disproportionate traffic.

3. Directory sharding: A lookup table maps each key to its shard. Flexible but the lookup table itself becomes a bottleneck and single point of failure.

Facebook's MySQL at scale: Facebook has sharded MySQL to handle over 10 billion rows per table. Each shard is a MySQL primary with replicas. Facebook also built TAO — a distributed cache layer sitting above MySQL — to serve billions of social graph queries per second without hitting the database.

Replication

Replication means keeping copies of the same data on multiple servers. It serves two purposes: high availability (if one server dies, another takes over) and read scaling (distribute reads across replicas).

Most production systems use asynchronous primary-replica replication — trading the small risk of data loss for significantly better write performance.

Database Comparison Table

The CAP Theorem

In 2000, Eric Brewer proposed the CAP theorem: a distributed database can guarantee at most two of three properties:

• Consistency: Every read receives the most recent write.
• Availability: Every request receives a response (may be stale).
• Partition Tolerance: The system continues operating even if network partitions split the cluster.

Since network partitions are unavoidable in real distributed systems, the practical choice is between CP (consistent but may become unavailable during partitions) and AP (always available but may return stale data).

Amazon chose AP for DynamoDB. Traditional SQL databases choose CP. Understanding this trade-off — which Amazon's 2007 Dynamo paper made concrete — is what separates junior engineers from senior architects.

Key Takeaways

Amazon's 2007 decision to sacrifice consistency for availability was not a mistake — it was a deliberate engineering trade-off matched to a specific business requirement. Shopping carts that silently show a duplicate item are a minor inconvenience. Shopping carts that refuse to accept items lose sales.

The lesson is not "use NoSQL." The lesson is: match your database choice to your data model and consistency requirements. Financial transactions demand ACID. Social feeds tolerate eventual consistency. A product catalogue needs flexible schemas. Every major system you use today — Instagram, Netflix, Uber, Spotify — uses multiple database types simultaneously, each chosen for the specific trade-offs that match its workload.