Databases at Scale

Scaling Beyond One Machine

A single database node eventually hits a wall: CPU, RAM, IOPS, or connection limits. Scaling has two directions. Vertical scaling (a bigger box) is simple but bounded and expensive at the top. Horizontal scaling (more boxes) is unbounded but forces you to confront replication, partitioning, and distributed consistency. This page is about the horizontal world.

SQL vs NoSQL, ACID vs BASE

SQL / relational systems offer a fixed schema, joins, and ACID transactions. NoSQL trades some of these for horizontal scale and flexible schemas, often under BASE semantics.

BASE = Basically Available, Soft state, Eventually consistent. The system stays available and converges over time rather than guaranteeing an immediately consistent view. It is a design stance, not a weaker ACID.

NoSQL families

Normalization vs Denormalization

Normalization removes redundancy so each fact lives in one place — great for write integrity, painful for read-heavy joins at scale. Denormalization duplicates data to serve reads without joins — fast reads, but writes must update every copy and can drift. At scale you often denormalize deliberately and keep copies in sync with events/CDC.

Indexing

B-trees update in place (read-optimized, good for mixed workloads). LSM-trees buffer writes in memory and flush sorted runs to disk, so writes are cheap and sequential but reads may check several files (mitigated with Bloom filters), and background compaction costs I/O.

Replication

Leader-Follower (single-leader)          Leaderless (quorum)
        writes                                  W nodes ack write
          |                                     R nodes serve read
       [Leader] --replicate--> [Follower]       W + R > N  => overlap
          |                 \-> [Follower]       (N replicas total)
       reads/writes           reads only

Synchronous replication waits for a follower to confirm (no data loss on leader failure, higher latency). Asynchronous replication returns immediately (fast, but a leader crash can lose the last un-replicated writes and readers see replication lag). Quorum systems use the rule W + R > N so read and write sets overlap, guaranteeing a read sees the latest write.

Partitioning / Sharding

Partitioning splits one logical dataset across nodes so each holds a subset. The partition key choice is the single most important scaling decision — a bad key creates hot shards.

Rebalancing moves partitions when you add/remove nodes. Avoid hash % N (adding a node reshuffles everything). Consistent hashing or a fixed number of partitions (far more than nodes, reassigned as a unit) keeps rebalancing cheap.

Consistent hashing ring [0, 2^32):
  key -> next node clockwise; add/remove moves only ~1/N of keys.
  Virtual nodes give each physical node many ring points => even load.
  (Cassandra, DynamoDB, Riak, Vitess use variants of this.)

CAP in Databases

Under a network Partition you must choose Consistency (reject requests to avoid divergence) or Availability (serve possibly stale data). It is a choice made during partitions; the healthy path can be both fast and consistent. PACELC extends it: else, absent partitions, you still trade Latency vs Consistency.

Isolation Levels & Anomalies

Dirty read: reading another transaction's uncommitted write. Non-repeatable read: the same row returns different values within one transaction. Phantom: a range query returns different rows because another transaction inserted/deleted. *MySQL InnoDB's Repeatable Read blocks phantoms via next-key locks; the SQL standard permits them.

Distributed Transactions: 2PC vs Saga

Operational Building Blocks

Connection pooling (PgBouncer, HikariCP) reuses a bounded set of DB connections; databases handle far fewer connections than app instances can open. Read replicas offload read traffic (watch lag). Change Data Capture (CDC) streams the commit log (Debezium reading the WAL/binlog) to keep caches, search indexes, and downstream services in sync without dual-write bugs.

CDC pipeline:
  Postgres WAL / MySQL binlog --> Debezium --> Kafka --> [search index]
                                                      \--> [cache]
                                                      \--> [data warehouse]

NewSQL & OLTP vs OLAP

NewSQL aims for the holy grail: horizontal scale and SQL + ACID. Google Spanner uses TrueTime (GPS/atomic clocks) for external consistency; CockroachDB and YugabyteDB layer distributed SQL over a Raft-replicated KV store; Vitess shards MySQL transparently (powers YouTube, Slack).

OLTP (transactional) systems handle many small, low-latency reads/writes (row-oriented, e.g. Postgres). OLAP (analytical) systems scan huge volumes for aggregates (column-oriented, e.g. Snowflake, ClickHouse, BigQuery). Don't run heavy analytics on your OLTP primary — ETL/CDC the data into a warehouse.