Choosing and Scaling Databases

SQL vs NoSQL, replication, sharding and consistency.

The database is usually the hardest part of a system to scale, because it holds state that must stay correct. Choosing the right kind, and knowing how to spread it across machines, is core to system design.

SQL versus NoSQL

• Relational (SQL): PostgreSQL, MySQL. Structured tables, joins, strong guarantees (ACID transactions). The default for most applications, and far more scalable than many people assume.
• Document (MongoDB): flexible JSON documents. Good when records vary in shape or are read as a whole.
• Key-value (Redis, DynamoDB): extremely fast lookups by key; limited querying.
• Wide-column (Cassandra): massive write throughput across many nodes.
• Graph (Neo4j): relationship-heavy queries such as social networks.
• Search (Elasticsearch, OpenSearch): full-text search and analytics.

A sound default: start with PostgreSQL, and add specialized stores only for needs it cannot meet.

Replication

Replication keeps copies of data on several machines. Typically one primary accepts writes and streams changes to replicas, which serve reads. It gives you higher read capacity and failover if the primary dies.

• Replicas can lag slightly behind (replication lag), so a user might not immediately see their own write. Route reads that need freshness to the primary.
• Synchronous replication is safer but slower; asynchronous is faster but can lose the last writes on failure.

Sharding

When one primary cannot handle the data size or write volume, shard (partition) the data: each shard holds a subset of rows on its own servers. The shard key decides where a row lives.

def shard_for(user_id: int, shards: int = 8) -> int:
    return user_id % shards          # simple hash sharding

print(shard_for(1234))            # user 1234 lives on shard 2

• Choose a key with even distribution; a bad key creates a hot shard.
• Cross-shard joins and transactions become hard or impossible, so shard only when you must.
• Adding shards moves data. Consistent hashing minimizes how much moves.

Consistency and the CAP theorem

In a distributed system, a network partition (nodes cannot talk) will eventually happen. CAP says that during one you must choose between Consistency (every read sees the latest write) and Availability (every request gets a response). Banking usually picks consistency; a social feed can accept eventual consistency, where replicas converge after a short time.

Indexing and denormalization

Indexes make queries fast (see the SQL indexes lesson). At large scale you may denormalize: store precomputed or duplicated data (like a counter or a joined view) to avoid expensive queries, accepting extra write work and the need to keep copies in sync.

Use a wide-column or partitioned store (Cassandra/DynamoDB) or sharded
PostgreSQL. Shard key = conversation_id, so all messages of a
conversation sit together and reads hit one shard. Sort messages by
timestamp within the partition. Watch for very active conversations
(hot partitions) and cap partition size by time-bucketing.