--- title: "10 — Relational Databases: Scaling Reads and Writes Beyond One Box" uid: system-design-relational-databases tags: ["sql", "sharding", "denormalization", "replication", "databases", "roadmap:system-design", "system-design"] excerpt: "Five distinct levers when one relational database can't keep up — replication, federation, sharding, denormalization, SQL tuning — each trading a different cost for headroom." date: 2026-08-13T03:27:33+0000 source: https://www.aveshina.my.id/en/blog/system-design-relational-databases --- "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. ```figure ``` 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](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](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](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](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](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/](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](https://www.youtube.com/watch?v=kKjm4ehYiMs) ```quiz Q: Your read-heavy app is saturating a single database. The first lever to pull is usually… - sharding by user ID - read replicas (master-slave replication) correct: 1 explain: Read load is usually the first wall, and replication spreads reads across slaves cheaply without restructuring. Sharding is reserved for write scale and is hard to reverse. Q: Federation differs from sharding because it splits by… - function (users vs forums vs products), not by key within one function - hashing a key into ranges correct: 0 explain: Federation splits databases by bounded context/function. Sharding splits one function's data by key. They are orthogonal. Q: Denormalization primarily trades… - read performance for write amplification - write performance for read amplification correct: 0 explain: Denormalization writes redundant copies to avoid joins, improving read performance at the cost of more write work and the risk of copy inconsistency. Q: Before reaching for sharding, the cheapest lever is usually… - SQL tuning (indexes, query rewrites, profiling) - buying a bigger server (vertical scaling) correct: 0 explain: SQL tuning costs no new infrastructure and is the most-skipped lever. Add indexes, fix N+1, profile the slow query log first. Q: In master-slave replication, if the master fails before a slave is promoted, the system can… - continue serving both reads and writes normally - operate read-only until a slave is promoted or a new master is provisioned correct: 1 explain: With the master down and no promotion yet, writes cannot be accepted. The system degrades to read-only until promotion completes. ```