Databases at scale: replication, sharding and consistency

Scaling reads: replication

A primary database handles writes and streams changes to read replicas. Reads go to replicas, writes to the primary.

• Replication lag means a user might not immediately see their own write on a replica. Fix: read your own writes from the primary for a short time, or route that user's reads to the primary.
• If the primary fails, promote a replica (failover).

Scaling writes: partitioning and sharding

• Vertical scaling: a bigger machine. Simple; has a ceiling.
• Partitioning: split one big table into parts (by date, by region) within one database.

• Sharding: split data across several databases by a shard key (user id, company id).

  • Hash-based: even spread, but range queries hit every shard.
  • Range-based: good for ranges, risk of hot shards.
  • Consistent hashing reduces data movement when adding shards.
  • Cross-shard joins and transactions become hard, so pick a key that keeps related data together.

CAP theorem (in plain words)

When a network partition happens (nodes can't talk), a distributed system must choose between:

• Consistency: every read gets the latest write (or an error).
• Availability: every request gets a response (possibly stale).

Banking balances choose consistency; social feeds and like counts choose availability. In normal operation, the trade-off is latency vs consistency (PACELC).

SQL vs NoSQL in design rounds

• Relational: structured data, relations, transactions, flexible queries. Default choice.
• Key-value / wide-column (DynamoDB, Cassandra): massive write throughput and simple lookups by key; you design tables around queries.
• Document (MongoDB): flexible shapes, whole-document reads.

Say what you'd store where and why, for example: "orders in Postgres for transactions; the product catalogue cached in Redis; search in Elasticsearch; analytics events in Kafka → a warehouse".

IDs at scale

Auto-increment ids don't work across shards. Options: UUIDs (random, large, poor index locality), time-ordered ids (UUIDv7, Snowflake-style: timestamp + machine + sequence).

Check yourself

• What is replication lag and how can it confuse users?
• How would you pick a shard key for a multi-tenant SaaS?
• Give one system that should prefer consistency and one that should prefer availability.