Nimendra's Notes 🪴

Database Scaling, Sharding and Replication

5 min read

systemdesign database/databaseengineering

Arpit System Design Course
🌟 Database Scaling - Supplementary
Database Denormalization

Introduction

Availability is crucial in database coz fault of the database can be breaks any system.
Most of the databases has their own scaling techniques.

Various database system gives various ways to scaling, sharding and replication.

  • MySQL offers master-slave and group replication.
  • PostgreSQL provides streaming replication and logical replication.
  • Cassandra replicates data automatically across nodes using a tunable consistency model.
  • CockroachDB and Spanner use consensus protocols like Raft or Paxos to ensure consistent replication across distributed nodes.
  • Databases like MongoDB, Cassandra, and Elasticsearch natively support automatic sharding.

Scaling

Vertical Scaling (Scale-Up)

  • Add more CPU, RAM, Disk to the database.
  • Require downtime during reboot.
  • Gives you ability to handle “scale”, more load.
  • Vertical scaling has a physical hardware limitation.

e.g: Upgrading a PostgreSQL instance on Amazon RDS from db.t3.medium to db.r6g.4xlarge.

Horizontal Scaling (Scale-Out)

Adding more machines (nodes) to system to handle increased load and distribute the workload across multiple servers.

Read Replicas

Suppose our system handles 90% reads and only 10% writes. In such cases, we can offload read operations to read replicas so that the primary (or “master”) database is dedicated to handling writes.

This improves performance and reduces load on the primary node.

To make this work effectively, **API servers must be aware of which database to use**:

  • Use the primary for all write operations (insert, update, delete).
  • Use read replicas for read-heavy operations (queries, reports, analytics).

Write Replicas

In certain systems, especially globally distributed databases, we may have multiple nodes that accept write operations—known as write replicas or multi-primary setups.

This setup allows users in different regions to write to their closest node, reducing latency. However, it also introduces complexity, such as:

  • Conflict resolution between concurrent writes
  • Consensus protocols (e.g., Paxos or Raft) to maintain consistency

Only databases specifically designed for this—like Cassandra, CockroachDB, or Google Spanner—support reliable multi-primary replication.

Replication

Changes on one database(Master) needs to be sent to replica to maintain consistency.

  1. Synchronous replication
  2. Asynchronous replication

Partitioning

Vertical partitioning vs horizontal partitioning

There are main two categories of partitioning,

1. Vertical Partitioning

some columns are moved to new tables. Each table contains the same no. of of rows but fewer columns.

2. Horizontal Partitioning

Divides a table into multiple smaller tables. Each table is a separate data store, and it contains the same no. of columns but fewer rows.

It’s widely used and often called sharding

horizontal partitioning is a foundational idea behind sharding, and when you implement it across servers or nodes, it becomes a sharding strategy
Horizontal Partitioning + Distributed Storage = Sharding

Routing Algorithm

Decides which partition(shard) stores the data.

Range based Partitioning: This algorithm uses ordered columns, such as integers, longs, timestamps, to separate the rows. For example, the diagram below uses the User ID column for range partition: User IDs 1 and 2 are in shard 1, User IDs 3 and 4 are in shard 2.

Hash-based sharding: This algorithm applies a hash function to one column or several columns to decide which row goes to which table. For example, the diagram below uses User ID mod 2 as a hash function. User IDs 1 and 3 are in shard 1, User IDs 2 and 4 are in shard 2.

Sharding

One node cannot handle the data/load we split it into multiple exclusive subsets writes on a particular now/document will go to one particular shard.

Vertical scaling has limit. So we use Horizontal scaling.

Sharding and Partitioning are independent.

Sharding : Method of distributing data across multiple instance(machine)
Partitioning: Splitting a subset of data within the same instance

Each database server is thus a shard and we say that the data is partitioned.

Important

In a sharded database system, the full dataset is split into pieces (partitions), and each piece is assigned to a separate database server (shard). This enables the system to scale horizontally and handle large volumes of data and traffic efficiently.

If data is not partitioned, but duplicated across nodes, then those nodes are replicas, not shards.

Benefits

  1. Shorten response time. By sharding one table into multiple tables, queries go over fewer rows, and results are returned much more quickly.
  2. Facilitate horizontal scaling. Sharding facilitates the possibility of adding more machines to spread out the load.

Drawbacks

  1. The order by operation is more complicated. Usually, we need to fetch data from different shards and sort the data in the application’s code.
  2. Uneven distribution. Some shards may contain more data than others (this is also called the hotspot).
  3. Cross Shared queries are expensive.

Cross Shared Queries

In a sharded database, data is split across multiple shards (i.e., database nodes), each holding a subset of the total data — typically based on a shard key (e.g., user ID, region, etc.).

Cross-shard joins are especially expensive.

SELECT * FROM Orders o
JOIN Users u ON o.user_id = u.id;

If Orders and Users are sharded differently, the system has to:

  • Fetch data from multiple shards
  • Reconstruct the join logic manually
  • Move a lot of data between nodes

Cross shared queries create network overhead, Increased latency and reduced parallelism

Backlinks

Graph View