Scaling Databases

Lets talk about how we can scale database !

So as you might be aware, there are two types of scaling.

1. Vertical Scaling - This is where you increase the memory and computer power for a single DB server. This enables us to store more data and serve more queries.

2. Horizontal Scaling - Vertical scaling has physical limits and it becomes a single point of failure. You can overcome these limitations by doing horizontal scaling, where you add more DB servers to the DB cluster. And the good thing is you can keep adding more servers to the cluster as you need them.

Database partitioning

Partitioning is like splitting. Imaging curring a cake. Either you can cut it vertically or you can layer by layer horizontally.

This is a vertical scaling technique where you split the table into partitions but keep them in the same database.

Each partition contains a subset of data. These data subsets are created based on various factors like date range, geographic location, or some specific column values.

Imagine a massive phone book. Instead of one gigantic book, you could divide it into sections based on the first letter of the last name (A-M, N-Z). Each of these sections is a partition, and they all exist as part of your overall phone book system.

Vertical partitioning improves query performance and manageability (indexing, backups, etc.)

Database sharding

Sharding is a form of horizontal partitioning.

In sharding, we split the table rows across multiple database servers. Each server holds a subset of the data ie. a shard.

Think of our phone book example again. Sharding would be like having entirely separate phone books, each covering a different alphabetical range (A-M on one server, N-Z on another). To find a number, you need to know which phone book (shard) to look in.

Key componets of sharding

1. Shared-nothing architecture

In a shared-nothing architecture, each shard is a separate database server. Data is distributed across these servers, and they don't share any resources.

2. Shard key

The shard key is the column or combination of columns used to determine which shard a row belongs to.

3. Routing mechanism

The routing mechanism is responsible for directing queries to the correct shard based on the shard key. This is either part of the application layer or a separate middleware.

Sharding strategies based on shard key

1. Key based (Hash) based sharing

Image credit

2. Geo based sharding

Image credit

3. Directory based sharding

Image credit

4. Range based sharding

Image credit

Comparison of sharding strategies

Sample Architecture with Sharding

How big companies use sharding ?