Distributed Database Systems

TL;DR

Distributed databases spread data across multiple interconnected computers, improving scalability and availability compared to a single central database. They introduce challenges like ensuring data consistency and managing network failures, which are handled by various architectures and protocols. Understanding their trade-offs helps you choose the right system for your application's needs.

1. The Mental Model

Think of a distributed database not as one giant vault, but as several smaller, synchronized vaults scattered across different locations. Each vault holds a piece of the treasure, and they all work together to give you a complete picture when you ask for it.

2. The Core Material

A distributed database system (DDBS) is a collection of logically interrelated databases distributed over a computer network. These systems make the distribution transparent to you, meaning you can interact with it as if it were a single, centralized database.

The main reasons for using a DDBS are:

• Scalability: You can add more machines (nodes) to handle increased data volume or user traffic without overhauling the entire system.
• Availability: If one part of the system fails, others can often continue operating, providing fault tolerance.
• Performance: Data can be stored closer to where it's most used, reducing latency for local operations.
• Autonomy: Different departments or locations can manage their own data while still contributing to a larger system.

Data Distribution Strategies

Photo by RDNE Stock project on Pexels

How data is spread across nodes is crucial:

• Fragmentation:
  • Horizontal Fragmentation: You split a table's rows across different nodes. For example, customers from North America on one server, and customers from Europe on another.
  • Vertical Fragmentation: You split a table's columns across different nodes. For instance, customer personal details on one server, and their order history on another.
• Replication: You store multiple copies of the same data on different nodes. This boosts availability and can improve read performance, but it complicates keeping all copies consistent.

Distributed Transaction Management

Photo by Leeloo The First on Pexels

When an operation spans multiple nodes, it becomes a distributed transaction. Ensuring these transactions are correct (e.g., all parts succeed or all parts fail) is harder than in a single database.

• Two-Phase Commit (2PC): This is a common protocol to achieve atomicity across multiple nodes.
  1. Prepare Phase: A coordinator node asks all participating nodes if they can commit the transaction. Each node writes its changes to a local log and responds "yes" or "no."
  2. Commit Phase: If all nodes respond "yes," the coordinator tells them to commit. If any respond "no," the coordinator tells everyone to abort.

graph TD
    Client["Client (Initiates Transaction)"] --> Coordinator["Coordinator Node"];
    Coordinator -- "1. Prepare to Commit?" --> ParticipantA["Participant A"];
    Coordinator -- "1. Prepare to Commit?" --> ParticipantB["Participant B"];
    ParticipantA -- "2. Ready/Abort" --> Coordinator;
    ParticipantB -- "2. Ready/Abort" --> Coordinator;
    Coordinator -- "3. If All Ready: Global Commit" --> ParticipantA;
    Coordinator -- "3. If Any Abort: Global Abort" --> ParticipantB;
    ParticipantA -- "4. Acknowledgment" --> Coordinator;
    ParticipantB -- "4. Acknowledgment" --> Coordinator;

Consistency Models

Photo by Markus Winkler on Pexels

When data is replicated, ensuring all copies are identical becomes complex, especially in the face of network partitions. The CAP Theorem states that a distributed system can only guarantee two out of three properties simultaneously:

• Consistency (C): All clients see the same data at the same time.
• Availability (A): Every request receives a response, without guarantee that it is the most recent write.
• Partition Tolerance (P): The system continues to operate despite network failures (partitions) that prevent some nodes from communicating with others.

Most modern distributed databases prioritize Partition Tolerance and then choose between strong Consistency (like traditional RDBMS) or high Availability (like many NoSQL databases).

Types of Distributed Databases

Photo by panumas nikhomkhai on Pexels

• Homogeneous DDBS: All participating databases use the same database management system (DBMS) software. Simpler to manage.
• Heterogeneous DDBS: Different nodes use different DBMS software (e.g., Oracle, MySQL, MongoDB). More complex but allows integration of existing systems.

3. Worked Example

Let's say you have an e-commerce platform and your Orders table is getting too large. You decide to distribute it horizontally based on Region.

Original Table (Conceptual):

Orders
+------------+------------+----------+----------+
| OrderID    | CustomerID | Region   | Amount   |
+------------+------------+----------+----------+
| 1001       | 1          | EU       | 150.00   |
| 1002       | 2          | NA       | 230.50   |
| 1003       | 1          | EU       | 75.00    |
| 1004       | 3          | NA       | 120.00   |
| ...        | ...        | ...      | ...      |
+------------+------------+----------+----------+

You implement horizontal fragmentation into two separate database instances (servers):

Node 1 (EU Server):

-- This server stores orders where Region = 'EU'
CREATE TABLE Orders_EU (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Region VARCHAR(2),
    Amount DECIMAL(10, 2)
);

INSERT INTO Orders_EU (OrderID, CustomerID, Region, Amount) VALUES
(1001, 1, 'EU', 150.00),
(1003, 1, 'EU', 75.00);

Node 2 (NA Server):

-- This server stores orders where Region = 'NA'
CREATE TABLE Orders_NA (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Region VARCHAR(2),
    Amount DECIMAL(10, 2)
);

INSERT INTO Orders_NA (OrderID, CustomerID, Region, Amount) VALUES
(1002, 2, 'NA', 230.50),
(1004, 3, 'NA', 120.00);

Now, when a client application queries for an order, a query router (middleware) determines which server to send the request to based on the Region predicate or OrderID range. For example, a query like SELECT * FROM Orders WHERE OrderID = 1002; would be routed to Node 2 if the router knows that OrderID ranges are partitioned such that 1002 falls into NA. If you query for all orders, the router might send sub-queries to both nodes and combine the results.

This setup distributes the load and storage, improving performance for region-specific queries and allowing independent scaling of each regional server.

4. Key Takeaways

• Distributed databases spread data across multiple machines for better scalability and availability.
• Data can be distributed using fragmentation (splitting rows or columns) or replication (making copies).
• Distributed transactions need protocols like Two-Phase Commit (2PC) to ensure atomicity across nodes.
• The CAP theorem highlights the trade-offs between Consistency, Availability, and Partition Tolerance in distributed systems.
• Homogeneous systems use the same DBMS across all nodes, while heterogeneous systems integrate different DBMSs.
• You typically interact with a distributed database as if it were a single system due to distribution transparency.
• Performance can improve by storing data closer to its users.

Common mistakes to avoid:
- Don't assume a distributed system automatically solves all performance problems; complex queries can still be slow.
- Neglecting consistency models can lead to applications seeing outdated or incorrect data.
- Over-replicating data without considering storage costs and write overhead can be inefficient.
- Forgetting about network latency and failures, which are much more prominent in distributed setups.

5. Now Try It

Imagine you're designing a distributed database for a global social media platform. You have a Posts table and a Users table. Describe how you would apply horizontal fragmentation to both tables to handle user traffic from different continents (e.g., North America, Europe, Asia) and justify your choices for each table. Your answer should explain what data goes to which region and why that's beneficial.