Choose and scale a database

Choosing a data store is about access patterns and guarantees, not fashion. Relational databases (PostgreSQL, MySQL) offer ACID transactions, joins, and a strict schema - a strong default. NoSQL stores trade some of that for a specific shape: key-value (Redis, DynamoDB) for lookups by key, document (MongoDB) for flexible nested records, wide-column (Cassandra) for huge write-heavy datasets partitioned by key, and graph databases for relationship traversal.

An index (usually a B-tree) lets the database find rows without scanning the whole table, and supports range queries such as “posts from the last week.” The cost: extra storage, and every write must also update each index.

In leader-follower replication, one leader accepts writes and streams them to followers, which serve reads. This scales reads and adds redundancy. With asynchronous replication, followers can lag behind, so a read from a follower may return stale data.

A common fix for lag is read-your-writes consistency: for a short window after a user writes, route that user’s reads to the leader. Failover promotes a follower when the leader dies; with asynchronous replication, writes the old leader acknowledged but had not yet replicated can be lost. Multi-leader and leaderless replication accept writes in several places, at the cost of resolving conflicts.