Relational databases handle distributed storage through techniques like sharding and replication, which help scale data across multiple servers while maintaining ACID properties. Sharding splits data horizontally (by rows) across different nodes based on a key, such as user IDs or geographic regions. For example, a customer database might store users with IDs 1-1000 on Server A and 1001-2000 on Server B. Replication creates copies of data on multiple nodes for redundancy and read scalability—like a primary node handling writes and replicas serving read queries. Tools like PostgreSQL’s logical replication or MySQL’s group replication automate this process, ensuring data consistency between nodes.
Distributed relational databases face challenges in maintaining consistency and handling transactions spanning multiple nodes. To address this, systems use protocols like two-phase commit (2PC) to coordinate transactions across shards. For instance, if a transaction updates data on two shards, a coordinator node ensures both either commit or rollback. However, this introduces latency and complexity. Some databases, like Google Spanner, use synchronized clocks and the TrueTime API to enable globally consistent reads and writes without full locking. Others, like CockroachDB, employ distributed SQL layers that route queries intelligently and manage data placement automatically, reducing developer overhead.
Modern distributed relational systems often combine sharding and replication with additional optimizations. For example, Amazon Aurora separates storage and compute, using a distributed storage layer that replicates data across availability zones. This allows compute nodes to scale independently while maintaining durability. Partitioning strategies, such as range-based or hash-based sharding, are chosen based on query patterns. Hash sharding evenly distributes data but complicates range queries, while range sharding groups related data (e.g., time-series logs by date) for efficient scans. Developers must also handle edge cases, like resharding when a node becomes overloaded, which requires tools for online schema changes or data rebalancing without downtime.