Three ways to scale your Database (Replication vs Sharding vs Partitioning)

Taking your database to the next level regarding scale is often harder than scaling web servers. In web servers, you can start new instances of your app and do horizontal scaling or you can increase server resources (memory/cpu) and do vertical scaling. But, when it comes to the database side your queries will get slower and you might run out of space.

In this article, I will introduce three ways to scale your database:

1. Replication
2. Sharding
3. Partitioning

Replication

Replicating the database is to create copies of same data for more than one server.
With multiple copies of data on different database servers, replication provides a level of fault tolerance against the loss of a single database server.

The main advantages of replication are:

• Load Balancing: Queries will be distributed across multiple servers (maybe in different data centers in different regions)
• High availability: If one server is down there are other servers to handle queries

There are different types of replication:

• Single-leader architecture: One server accepts writes from clients, and replicas get data from there. This is the easiest method.
• Multi-leader architecture: Multiple servers that accept writes and each replica set get data from their leader. It's used in different data centers so writes from different regions become faster like read queries.
• No-leader architecture: All servers accepts writes and reads. All replicas get data from each other. This architecture is supported by databases like Cassandra to achieve maximum scalability.

Sharding

Sharding is a method for distributing data across multiple machines. Running queries on a single server with a lot of data will consume a lot of CPU but running queries on a subset of this data will be much faster and will consume less CPU.
Sharding can be used with replication in which each shard will be replicated.
Sharding is complex and require some experience because you will need to analyze queries and data structure to determine the sharding key and algorithm.

Sharding key: a specific column value that indicates which shard this row is stored in.
Sharding algorithm: an algorithm to distribute your data to one or more shards.

The main advantages of sharding are:

• Faster Queries: less data -> less CPU/memory usage -> faster queries.
• Storage Capacity: Servers will not run out of space because data is distributed across multiple servers.
• High Availability: If one shard is down other data won't be lost.

Sharding may not be a good option if most of your queries are JOINs.

Partitioning

Partitioning is dividing large tables into multiple tables. By dividing a large table into smaller, individual tables, queries that access only a fraction of the data can run faster and use less CPU because there is less data to scan.
Unlike Sharding and Replication, Partitioning is vertical scaling because each data partition is in the same server.
Partitioning should be done when:

• You have large tables, like in a users table, you can partition based on country.
• You have historical data that are rarely queried and example would be having tables like jobs_2020 and jobs_2021 for old jobs but for new ones there is the table jobs.

The main advantage of partitioning is:

• Faster Queries: smaller tables and indexes -> less data to scan -> less CPU/memory usage -> faster queries.

If you liked the article you can join my newsletter and follow me.