6.1Distributed databases and replication

As data volume and access load grow beyond what a single server can handle, distributing a database across multiple sites or nodes becomes an unavoidable design decision. But distribution is not free—there is a constant trade-off among consistency, availability, and response performance, and which to prioritize and which to sacrifice. A database designer or data-platform engineer must be able to select the right partitioning scheme, replication method, and consistency model according to business requirements—whether consistency is paramount (as in financial transactions) or availability and responsiveness take priority (as in a social-media feed). This section builds the pattern for that design judgment on top of how distributed databases actually work.

6.1.1Partitioning data: horizontal and vertical

• Horizontal partitioning (row partitioning) splits a single table by rows across multiple sites or nodes—for example, splitting a customer table by region or customer-ID range and placing each portion at a different site or node. Each fragment keeps the same table structure (columns), only the rows differ, which suits load distribution and scale-out.
• Vertical partitioning (column partitioning) splits a single table by columns—for example, separating frequently accessed columns (name, status, etc.) from rarely accessed ones (detailed history, large text fields, etc.) into different tables or storage tiers, so that only the frequently used columns sit on fast storage. After splitting, each fragment is still linked back via the original row's primary key.
• The key criterion for choosing a partitioning scheme is "skew in the access pattern." If access concentrates on a specific region or customer segment, horizontal partitioning can speed up just that range; if access concentrates on specific columns, vertical partitioning can optimize just those columns. Mixed partitioning, combining both, is also used in practice.

6.1.2Distribution transparency (the six transparencies)

• The ideal for a distributed database is that users and applications can treat it as if it were a single database, unaware of where or how data is distributed—this is called transparency. The six representative kinds: location transparency (no need to know which site holds the data), fragmentation transparency (no need to know how it is partitioned), replication transparency (no need to know how many replicas exist), failure transparency (processing continues despite a partial site failure, without the application needing to be aware), concurrency transparency (concurrency control happens behind the scenes), and performance transparency (performance is optimized to remain acceptable despite distribution).
• Transparency is an ideal that is difficult to fully achieve. In particular, failure transparency and the CAP theorem are in tension: during a network partition, it is impossible to guarantee both "always respond even if some sites are down (availability)" and "always return the latest consistent data (consistency)" simultaneously—a limit covered next.

6.1.3Replication: synchronous and asynchronous

• Replication duplicates the same data across multiple nodes. Its main purposes are improving availability (processing continues on other replicas if one node fails) and improving read performance (reads can be distributed across replicas). Most designs use a master/slave (primary/replica) configuration, concentrating writes on the master and propagating changes to the slaves.
• Synchronous replication completes a commit only after the write has been propagated to (all or a specified quorum of) replicas. This guarantees no inconsistency between replicas (strong consistency), but write response time degrades because the write must wait for replica propagation, and a slow or failed replica directly impacts overall write performance.
• Asynchronous replication treats the write to the master as committed immediately, propagating to replicas afterward (in the background). Write response performance is high, but there is a risk of losing not-yet-propagated updates if the master fails (data loss), and reading from a replica may return data older than the master (temporary inconsistency).

6.1.4Sharding and the CAP theorem / BASE

• Sharding is a form of horizontal partitioning that distributes data across multiple independent nodes (shards) based on a shard key, with each shard independently handling reads and writes, achieving horizontal scale-out that includes writes. Whereas simple replication is strong for scaling reads, sharding is strong for scaling the whole workload including writes. Care is needed because a poorly chosen shard key can create hotspots (skewed access to a specific shard).
• The CAP theorem states that a distributed system cannot simultaneously and fully satisfy all three of Consistency, Availability, and Partition tolerance. Since network partitions are a realistic possibility, partition tolerance is treated as effectively mandatory, so the real design choice becomes consistency (CP: prioritize consistency during a partition, rejecting or delaying some requests) versus availability (AP: keep responding during a partition, accepting the possibility of returning stale or inconsistent data).
• BASE is a design philosophy for availability-focused distributed systems, contrasted with ACID. It stands for Basically Available, Soft state (state may change over time), and Eventually consistent (eventual consistency). Eventual consistency is a relaxed consistency model in which the system may be temporarily inconsistent immediately after an update, but all nodes will eventually converge to the same value if no further updates occur—adopted by AP-leaning NoSQL systems and the like.

Suppose a data-platform engineer at an e-commerce site faces two distinct requirements. First, payment and inventory finalization—it is unacceptable for the business to let a customer double-purchase the same inventory item or have a payment finalized twice, a strong consistency requirement. Here, the engineer chooses synchronous replication, committing a write to the master only after it has propagated to replicas, and from a CAP-theorem standpoint adopts a CP-leaning configuration that prioritizes consistency (rejecting some writes during a network partition rather than allowing inconsistency). Reduced response performance is accepted, since correctness of payment is the top priority. Second, product view counts and "like" counts—here, always returning a response matters more to the business than perfect freshness or consistency. The engineer chooses asynchronous replication and, from a CAP standpoint, an AP-leaning configuration that prioritizes availability, accepting eventual consistency (BASE). Writes commit immediately for high throughput, but reads from certain replicas may return a counter value that is a few seconds stale—judged acceptable given the business requirement. Furthermore, if order volume growth pushes a single master's write processing near its limit, simple replication (which only scales reads) will not relieve the write bottleneck, so the engineer considers migrating to sharding keyed on customer ID or order date, taking care in shard-key selection to avoid hotspots concentrated on a large customer or a specific date. Discerning, requirement by requirement, "is consistency the lifeline here, or is availability/responsiveness the lifeline," and choosing the replication method, CAP-theorem stance, and partitioning/sharding scheme accordingly, is the essence of this practice.

6.1.5Section summary

• Horizontal partitioning = by row, vertical partitioning = by column. Choose the scheme based on skew in the access pattern
• Synchronous replication = strong consistency, lower performance; asynchronous replication = higher performance, risk of data loss and temporary inconsistency
• Under the CAP theorem, partition tolerance is a given; choose consistency-first (CP) or availability-first (AP, BASE/eventual consistency) based on business requirements. Use sharding to scale writes