# How to Solve Distributed Databases

## What Is The Distributed Databases Problem?

The Distributed Databases Problem provides us with a scenario in which we have an SQL database and asks us to create a solution that allows us to add more machines once the first has reached capacity. In simple terms we must find a way to distribute this database. The caveat to this is that we do not have access to any automated tools for distributing. This problem demands consideration for the bottlenecks that may occur while manually distributing a SQL database.

## An Example of the Distributed Databases Problem

How would you organize a SQL database like MySQL such that you can add more machines once your current ones reach maximum capacity? With the limitation that you do not have access to any automated tools for distributing.

## How to Solve the Distributed Databases Problem

To handle the increasing capacity in our SQL-based database system without relying on automated distributing tools, we can take a manual sharding approach. This means we'll manually divide the data across multiple machines or servers, allowing us to scale the system and add more machines when needed.

### SQL Databases

Let’s start by talking about SQL databases:

- SQL guarantees atomicity and isolation of transactions.
- SQL seamlessly supports multi-table queries (JOINs).
- SQL optimizes both indexes and queries on the DB level.

Before we go further, let’s clarify the functional requirements for this problem. Making an RDBMS (a relational database management system, the generalization of the loose term “SQL database”) horizontally scalable is, in general, a next-to-impossible task. For the purposes of scoping this conversation, let’s agree what we should focus on, and what can be left outside the spotlight.

Specifically, there are several optimization directions:

1. Scale to support more data volume (capacity),
2. Scale to support more query throughput (TPS, transactions per second),
3. Scale to support faster queries (tail latency), and
4. Scale to enable broad cross-shard complex queries with JOINs and other cross-shard data transfer.

Let’s assume that most high-throughput / low-latency queries only need to access the data that lives on one shard. The queries that require the data from multiple shards are allowed to be slow.

### Sharding the database

Next, the question is: how do we go about sharding our database? First, we have to identify the sharding key. We’ll choose a column or attribute in our table that can act as a sharding key. This key can help to evenly distribute the data across multiple machines if we can assume even load. Common examples include things such as user IDs.

Once we have identified the sharding key, we can employ consistent hashing as our sharding technique. Consistent hashing ensures that the distribution of data across shards is balanced and minimizes the amount of data that needs to be remapped when adding or removing machines from the system.

When it comes to partitioning the data, we can split it based on our sharding key among the different machines in the network. Each machine will be responsible for storing a specific range or subset of data based on the sharding key value.

Keep in mind that we’ll have to adjust our SQL queries to include the sharding key in the WHERE clause to ensure that the queries are routed to the appropriate machine holding the relevant data.

Now that we’ve determined the sharding strategy, we'll set up multiple instances of the SQL database, each running on a separate machine. Each instance will be responsible for storing and serving a specific shard of the data. We'll configure our application to interact with the appropriate database instance based on the shard key/criteria we used for sharding.

#### Load Balancer

To ensure the requests hit the correct shards, we introduce a load balancer into the system architecture. The load balancer acts as a traffic controller, receiving incoming requests and routing them to the correct database shards.

### Indexing

Even in a system with a distributed SQL database, optimizing query performance is still vital. We'll create appropriate indexes in our database schema to improve search and retrieval efficiency. An index is essentially a separate data structure that may contain a sorted copy of specific data fields.

### Benefits

Indexing the sharding key primarily improves the performance of SELECT statements, specifically those that involve filtering or searching based on the sharding key. However, indexing the sharding key may not have a direct impact on the speed of UPDATE or MERGE statements.

While this manual sharding approach requires more effort and management compared to automated distributing tools, it gives us flexibility and control over our database infrastructure. We can scale the system by adding more machines while still benefiting from the advantages of a SQL-based database.