10 — Relational Databases: Scaling Reads and Writes Beyond One Box

"Just add more DB" was my scaling strategy, and it treated five different moves as one. Writing them down separated the levers: replication (copy data), federation (split by function), sharding (split by key), denormalization (trade writes for reads), and SQL tuning (make the existing box faster). [1][2] They are not interchangeable, and reaching for the wrong one is how the headroom gets spent without the benefit.

The framing that landed is that each lever attacks a different limit. Replication attacks read load. Federation and sharding attack write load by splitting the data. Denormalization attacks read latency at the cost of write complexity. SQL tuning attacks inefficient queries on the box you already have. The diagnostic habit is to identify _which_ limit I'm hitting before pulling any lever.

Replication: copy the data for reads and availability

Replication copies data from one database to another to increase availability and scalability [1]. Two structures:

• Master-slave. The master serves reads and writes, replicating writes to one or more slaves that serve only reads. Slaves can replicate to further slaves in a tree. If the master goes offline, the system runs read-only until a slave is promoted or a new master is provisioned [1].
• Master-master. Both masters serve reads and writes and coordinate on writes. If either goes down, the other continues serving both [1].

Replication is the first lever I reach for when read load is the bottleneck — most applications read far more than they write, so spreading reads across slaves multiplies effective read capacity cheaply. The cost is replication lag (a slave can be briefly behind the master) and the operational complexity of failover and promotion.

Federation: split by function

Federation (or functional partitioning) splits databases by _function_ instead of by data within one function [2]. Instead of one monolithic database, you have three: forums, users, products. Each sees less read/write traffic, so each has less replication lag, fits more data in memory (more cache hits from improved locality), and — with no single central master serializing writes — you can write in parallel across the functions, increasing throughput [2].

The cost is that cross-function joins now cross database boundaries, which is harder or impossible. Federation is a natural fit when the system already has clear bounded contexts (the same boundary reasoning as microservices).

Sharding: split by key

Sharding distributes data across different databases such that each manages only a _subset_ of the data [3]. Taking a users database: as the user count grows, you add shards. Each shard holds a slice (say, users whose ID hashes into a particular range). The result is less read/write traffic per shard, less replication, more cache hits, smaller indexes (faster queries), and no single central master serializing writes — so writes parallelize across shards [3]. If one shard goes down, the others keep working, though you pair this with replication to avoid data loss.

The cost is operational complexity — resharding when a shard fills up, cross-shard joins, and transactions that span shards. Sharding is the lever of last resort for write scale, because once you shard, reversing it is painful. Reach for it when federation and replication are no longer enough.

Denormalization: trade writes for reads

Denormalization improves read performance at the expense of some write performance [4]. Redundant copies of data are written into multiple tables to avoid expensive joins. Some RDBMS (PostgreSQL, Oracle) support materialized views that store the redundant data and keep it consistent automatically [4]. Once data is distributed via federation and sharding, managing joins across data centers gets even harder — denormalization can circumvent the need for those complex joins.

The cost is write amplification (one logical update touches several copies) and the risk of inconsistency between copies if a write partially fails. I reach for denormalization when read latency from joins is the bottleneck and the write-amplification cost is acceptable — typically for read-heavy derived views.

SQL tuning: make the existing box faster

Before sharding, there is usually headroom to recover on the box I already have. SQL tuning is the broad practice of diagnosing and repairing statements that fail to meet a performance standard, and it starts with measurement [5]:

• Benchmark — simulate high-load situations with tools like ab to find the ceiling.
• Profile — enable the slow query log to find which statements are actually slow.

Benchmarking and profiling point at concrete optimizations: adding indexes on columns used in WHERE/JOIN clauses, rewriting queries to avoid full-table scans, fixing N+1 patterns (covered in the antipatterns notes), and adjusting schema where the access pattern has drifted from the original design. Tuning is the cheapest lever because it costs no new infrastructure, and it is the one most often skipped in favor of the more dramatic moves.

How I use this

The order matters and is the whole practical takeaway. I tune SQL and add indexes first (cheapest). I add read replicas second, because read load is usually the first wall and replication solves it without restructuring. I federation by bounded context third, when a single schema holds unrelated domains that could live independently. I shard last, only when write volume on a single function outgrows one master, because sharding is the hardest move to reverse. Denormalization I apply opportunistically wherever a join is the measured bottleneck. Naming the lever before pulling it has saved me from sharding a database that just needed an index.

References

[1] D. Martin, "Replication," system-design-primer (open source), 2024. [Online]. Available: https://github.com/donnemartin/system-design-primer#replication

[2] D. Martin, "Federation," system-design-primer (open source), 2024. [Online]. Available: https://github.com/donnemartin/system-design-primer#federation

[3] High Scalability, "The coming of the shard," 2009. [Online]. Available: http://highscalability.com/blog/2009/8/6/an-unorthodox-approach-to-database-design-the-coming-of-the.html

[4] "Denormalization," Wikipedia. [Online]. Available: https://en.wikipedia.org/wiki/Denormalization

[5] Oracle, "Introduction to SQL tuning," Oracle Database Documentation, 23. [Online]. Available: https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/introduction-to-sql-tuning.html

[6] "Query optimization for mere humans in PostgreSQL," Towards Data Science, 2022. [Online]. Available: https://towardsdatascience.com/query-optimization-for-mere-humans-in-postgresql-875ab864390a/

[7] "Scaling up to your first 10 million users," YouTube, 2019. [Video]. Available: https://www.youtube.com/watch?v=kKjm4ehYiMs