Day 51: Database Architecture – Master‑Slave Replication, Read‑Write Separation, Sharding & Consistency

Diagnosing scaling need

Ask four questions to decide the scaling technique: is read pressure high, is write pressure high, is data volume too large, or is availability insufficient? The answer selects replication, read‑write separation, vertical splitting, or horizontal sharding.

Replication basics (MySQL binlog example)

Application writes → primary commits transaction & records binlog
Primary writes binlog → replica pulls binlog
Replica stores as relay log
Replica replays relay log → updates its data

The replica lags behind the primary by a small amount.

What replication solves

Creates identical data copies.

Offloads read traffic to replicas.

Provides a candidate node for failover.

Enables backup, reporting, and analytics on replicas.

What replication does NOT solve

Does not increase primary write capacity.

Does not perform automatic failure detection or switchover.

Does not eliminate replication delay.

Does not replace offline backups.

Does not protect against replicated accidental deletions.

Read‑write separation

Application → Data Access Layer
   ├─ Replica 1 : normal queries
   ├─ Replica 2 : reporting, analytics
   └─ Primary   : inserts, updates, strong‑consistency reads
Primary ── replication logs ──► Replicas

Benefits

Multiple replicas share read load.

Primary reserves resources for writes.

Reporting and backup workloads no longer pressure the primary.

Read capacity scales horizontally by adding replicas.

Replication vs. read‑write separation

Replication answers “how are data copies created?”.

Read‑write separation answers “where do read and write requests go?”.

Data‑access layer importance

Application code should not embed connection logic; a unified data‑access layer or middleware decides routing, load‑balances replicas, handles failover, and can force critical reads back to the primary.

Replication confirmation modes

Mode            Primary confirms when               Benefit                         Cost
--------------------------------------------------------------------------------------------------------------------------------
Asynchronous    Immediately after commit            Low latency, high throughput    Replica lag; possible data loss on primary crash
Semi‑sync      After at least one replica receives  Reduces loss window              Higher write latency; replica may still be unreadable
Synchronous    After all configured replicas ack    Strong consistency, higher safety  Write latency increases; replica/network failures affect availability

Note: “replica has received the log” does not guarantee the data is queryable; visibility depends on DBMS configuration.

Read‑your‑write (RYW) strategies

Route the same user’s reads to the primary for a short window after a write.

Read order details directly from the primary after creation.

Wait until the replica has caught up to a specific log position before reading.

Critical data (balance, inventory, payment status) always read from primary or via strong‑consistency reads.

Non‑critical browsing and reporting can tolerate slightly stale replica data.

Fault‑tolerant switchover steps (one primary, two replicas)

Detect whether the primary truly failed.

Compare replica replication progress.

Promote the most up‑to‑date replica to new primary.

Prevent the old primary from accepting writes (avoid split‑brain).

Redirect application connections to the new primary.

Re‑integrate the former primary after repair.

Verify no data loss or duplication occurred.

Vertical splitting (business‑level partitioning)

Original monolithic schema:
  users, products, orders, inventory, payments
After vertical split:
  UserDB   → users, addresses, memberships
  ProductDB→ products, categories
  OrderDB  → orders, order_items
  StockDB  → stock, stock_logs
  PaymentDB→ payment_records, refunds

Benefits: isolates load, limits fault impact, allows independent scaling per domain.

Costs: cross‑database joins become difficult, distributed transactions are required, and data duplication may be needed.

Vertical table splitting (column‑level)

ProductBase(product_id, name, price, status)
ProductDetail(product_id, long_description, large_image, specs)

Suitable when hot and cold fields differ markedly; reduces row width and I/O.

Horizontal splitting (sharding)

order_00(order_id, user_id, …)
order_01(order_id, user_id, …)
order_02(order_id, user_id, …)

Key distinction:

Vertical split → different tables/columns.

Horizontal split → same‑structure rows on different nodes.

Sharding types

Horizontal table sharding : multiple physical tables within the same DB instance.

Horizontal database sharding : each shard lives on a separate DB instance, spreading storage, CPU, and I/O.

Sharding key selection criteria

Cover the most common query predicates.

Uniform value distribution and high cardinality.

Keep related data in the same shard to avoid cross‑shard joins.

Stable over time.

Support future scaling and migration.

Example: use user_id for queries that fetch all orders of a user, or order_id if direct order lookup dominates.

Range vs. hash sharding

Range sharding simplifies range queries and archiving but can cause hotspot on the latest range.

Hash sharding yields even distribution but makes range queries and node‑addition more complex.

Hotspot handling

Split the hot key.

Bucketize inventory.

Queue requests and apply rate‑limiting.

Expand the hot shard.

Redesign the sharding rule.

New issues after sharding

Cross‑shard queries

Aggregations (e.g., total sales) require querying multiple shards, sorting/aggregating per shard, then merging results.

Cross‑shard joins

Align data with the same sharding key.

Perform joins in the application layer.

Replicate small reference data.

Use a data‑warehouse or search index for the join.

Cross‑shard transactions

Two‑phase commit (2PC) for strong consistency.

Try‑Confirm‑Cancel (TCC) pattern.

Saga with compensating actions.

Reliable messaging with eventual consistency.

Global unique IDs

Prevent duplicate IDs across shards using UUIDs, segment‑based IDs, timestamp‑node‑sequence schemes, or a centralized ID service.

Resharding / scaling

When increasing shard count (e.g., from 4 to 8), redesign routing rules, migrate existing data, perform double‑read/write during migration, gradually shift traffic, verify data integrity, then retire old shards.

Replication vs. sharding comparison

Aspect          Replication                              Sharding
--------------------------------------------------------------------------------------------------------------------------------
Data relationship   Multiple nodes store identical or near‑identical copies   Different nodes store disjoint subsets of data
Main goal          Scale reads, increase redundancy, enable failover    Scale capacity and write throughput
Typical routing    Writes → primary; reads → a replica                Locate target node via sharding key
Core challenges    Replication lag, failover, data loss, split‑brain    Sharding key choice, cross‑shard queries/transactions, rebalancing

Both can be combined: each shard can be a primary with two replicas, giving capacity expansion and read redundancy.

Distributed database fundamentals

A Distributed Database System (DDBS) physically spreads data across multiple nodes while presenting a single logical database to users.

Four core characteristics

Physical dispersion across nodes or sites.

Logical centralization – users see one database.

Site autonomy – each node can manage its own data.

Controlled redundancy – replicas are deliberately configured.

Three transparency types

Location transparency: users need not know where data resides.

Sharding transparency: users need not know how a logical table is split.

Replication transparency: users need not know how many copies exist or which copy is read.

Consistency across layers

Single‑node transaction

ACID transactions guarantee consistency within one database instance.

Primary‑replica consistency

After a primary write, replicas may lag; choose async, semi‑sync, or sync replication, decide whether critical reads go to primary, and define acceptable replication delay (RPO).

Cross‑shard consistency

When a business operation spans shards, select 2PC, TCC, Saga, or reliable messaging based on required consistency vs. availability.

Network partition (CAP) considerations

Strong consistency for balance, inventory, payment – prefer to fail or wait.

Eventual consistency for browsing, likes, reporting – tolerate stale data for higher availability.

Full e‑commerce case study (step‑by‑step)

Classify bottlenecks: read pressure (90 % queries), write latency, table size (8 billion rows), heavy aggregation, manual failover.

Expand reads: enable master‑replica, add multiple replicas, route normal queries to replicas via a unified data‑access layer, monitor and handle replication lag.

Handle read‑your‑write: read order details from primary after creation, keep session reads on primary for a short period, use strong‑consistency reads for balance/inventory/payment.

Vertical then horizontal: split by business (users, products, orders, stock, payments); when order table remains huge, shard by user_id (high cardinality, uniform distribution).

Address sharding side‑effects: use global unique order IDs, a routing layer to locate shards, aggregate cross‑shard queries in a service, offload platform‑wide stats to a data‑warehouse, apply appropriate distributed‑transaction patterns, plan re‑sharding and data‑validation procedures.

Achieve high availability: configure replicas for each shard, deploy failure detection, leader election, automatic routing switch, split‑brain protection, independent backups, define RTO/RPO and conduct regular failover drills.