Databases at Scale — Replication, Partitioning and Sharding

Choose SQL or NoSQL, scale reads with replicas, partition and shard data, and handle hot keys and rebalancing.

Est. 45 min Updated Suggest a fix
// Part 01 — Why scale changes the design

A Database Is a Product Decision, Not a Box

An early application might fit on one database server. As it grows, the same database may face more reads, more writes, larger tables, tighter latency goals, and a bigger failure impact. Scaling is not simply "add more machines": every extra copy or partition introduces coordination, operational work, and new failure modes.

Start from a workload. A Swiggy-style order service may need strongly consistent writes for a payment state transition, fast reads for restaurant menus, and high-volume append-only events for delivery tracking. Those workloads can justify different storage strategies; a fashionable database name cannot.

QuestionRelational database often fitsNoSQL family may fit
Data shapeRelationships, constraints, joins matterAccess patterns are known and data is naturally key-oriented
TransactionsMulti-row invariants need ACID transactionsA bounded aggregate can be updated atomically by key
QueriesAd hoc joins and flexible reportingPredictable key lookups or scans dominate
EvolutionSchema and constraints are valuable contractsDifferent records vary or throughput needs a specific model
💡 Note
SQL versus NoSQL is not a scale ladder. Relational systems scale vertically and horizontally, while NoSQL systems still require careful data modelling, indexes, backups, and operations.
// Part 02 — Choose from the workload

Model Queries Before Picking a Database

Write down the important reads and writes before choosing a storage engine. Record the fields used to locate data, sort order, expected result size, consistency requirement, and whether a query spans multiple entities. A key-value store can be excellent for a lookup and awkward for an unplanned report; a relational database can be a strong default while the product is still discovering its access patterns.

  1. List entities and invariants: an order has one current payment state, and a payment must not be captured twice.
  2. List access patterns: fetch an order by ID, list a customer’s recent orders, and find pending payments for reconciliation.
  3. Estimate read/write rates, row growth, payload size, and peak-to-average variation using stated assumptions.
  4. Choose the simplest engine and schema that supports the operations and correctness guarantees.
  5. Measure real query plans and saturation before introducing replicas or shards.

A useful design note also names the source of truth. If both a SQL database and a search index contain an order, decide which one accepts writes and how the other catches up. Two independent writable copies usually create a conflict problem, not redundancy.

🎯 Pro Tip
In an interview, explain the access pattern and invariant first. Then name a storage model and say what would make you reconsider it. This is more convincing than claiming one database is universally fastest.
// Part 03 — Replication

Replicas Increase Read Capacity, Not Write Capacity

Replication maintains copies of data on multiple database nodes. With a primary-replica arrangement, writes go to the primary and are copied to replicas. Directing eligible reads to replicas can spread read load and provide copies for failover, but a replica can lag behind the primary. A user who writes an address and immediately reads it from a lagging replica may see the old address.

text
                 writes                    replication stream
Client  -------------------->  Primary  -------------------->  Replica A
  |                                |                                |
  | reads requiring freshness      | commit                         | stale-able reads
  +------------------------------> +                                <+
                                                                   Replica B

Primary failure: choose/promote a replica, then redirect traffic.
The promotion decision must prevent two primaries from accepting writes.

A common session guarantee is read-your-writes: after a customer changes an address, route that customer’s next read to the primary or to a replica known to have applied the write. Other screens may accept slightly stale catalog data. This is a product-level choice, not just a connection-pool setting.

Replication choiceBenefitCost / concern
Synchronous acknowledgementStronger acknowledgement durability across copiesWrite latency and availability depend on replicas
Asynchronous replicationLow-latency primary writes; replica can be far awayLag and possible loss of acknowledged data during failure
Read replicasMore capacity for eligible readsStale reads, replica overload, and failover complexity
// Part 04 — Partitioning

Partition Large Tables Before You Need a New Cluster

Partitioning splits one logical table into smaller physical pieces while keeping it within one database system. Range partitioning can group orders by month; hash partitioning can distribute users across buckets. Partition pruning lets the database skip irrelevant pieces when a query filters on the partition key.

For example, an order history query for one customer and date range can benefit from partitions aligned with those filters. A query that omits the partition key may still touch every partition. Partitioning does not automatically make every query faster, and partition boundaries need maintenance as data grows.

partition_plan.py
python
from datetime import date


def order_partition_for(created_on: date) -> str:
    """Return the monthly partition name used by the storage layer."""
    return f"orders_{created_on.year}_{created_on.month:02d}"


for day in [date(2026, 1, 15), date(2026, 11, 9)]:
    print(day.isoformat(), order_partition_for(day))

The example only names a partition; it does not replace database DDL, indexes, retention policy, or query planning. Production partitioning is a storage-engine feature, so use the documentation for your selected database and verify that representative queries prune partitions as expected.

📌 Real World Example
A shipment-events table might use monthly partitions for retention, while the hot order table stays unpartitioned until measurements show a need. Apply partitioning to a concrete access or lifecycle problem rather than as a default ritual.
// Part 05 — Sharding

Sharding Distributes Data Across Independent Nodes

Sharding is horizontal partitioning across database nodes. A shard key maps each record to a shard. Picking the key is consequential: it should distribute traffic and storage while keeping common queries local. Customer ID is a natural key for a customer’s orders, but one unusually active customer can still create a hot shard. A key that spreads writes evenly may make a customer-history query span many shards.

shard_routing.py
python
from hashlib import sha256

SHARD_COUNT = 8


def shard_for(customer_id: str) -> int:
    """Stable routing for a fixed shard count; not a full resharding strategy."""
    digest = sha256(customer_id.encode("utf-8")).digest()
    return int.from_bytes(digest[:8], "big") % SHARD_COUNT


for customer in ["customer-101", "customer-202", "customer-303"]:
    print(customer, shard_for(customer))

Modulo routing is easy to understand but changing the shard count remaps many keys. Consistent-hash rings or a routing directory can reduce movement or enable explicit placement, but both add complexity. Plan how to move data, dual-read or dual-write safely during migration, validate counts, and roll back before increasing shard count.

⚠️ Important
Cross-shard joins and transactions are expensive or unavailable depending on the database. Keep invariants that must commit together inside one shard where possible; otherwise explicitly design the coordination and recovery workflow.
// Part 06 — Operations

Hot Keys, Lag and Rebalancing Are the Real Work

Average traffic hides hotspots. A celebrity restaurant, a popular sale, or one shared counter can send a large fraction of requests to a single key. Replicas do not necessarily fix a write hotspot, and adding shards does not help if the routing key still maps all hot traffic to one shard. Consider caching suitable reads, splitting a counter into buckets, or changing the data model while preserving the correctness rule.

Watch query latency, connection saturation, replication lag, disk and index growth, per-shard traffic, error rates, and backup restore time. Failover is not complete until clients reconnect safely and the promoted node is the only writer. Test recovery from backups; a successful backup job alone does not prove that the data can be restored.

  1. Set an explicit freshness budget for every read path and route reads accordingly.
  2. Keep shard ownership and schema changes observable, and alert on skew between shards.
  3. Use bounded connection pools and query timeouts so database saturation does not spread through the application.
  4. Practice failover and restore procedures with realistic data volumes.
// Part 07 — Common mistakes

Scale Without Breaking the Data Model

Adding replicas and assuming writes will scale too

Why: Replicas mainly distribute reads; writes still pass through the primary or coordination path.

Fix: Measure read and write pressure separately, then choose a write-scaling strategy only if needed.

Choosing a shard key by intuition alone

Why: The key may create skew or scatter the most frequent query across shards.

Fix: Model traffic distribution and query locality with representative data before migration.

Sending every read to a replica

Why: Replication lag can violate user expectations such as seeing an update immediately.

Fix: Classify reads by freshness requirement and provide read-your-writes routing where needed.

Treating partitioning as a substitute for indexes or query design

Why: Queries can still scan every partition or perform expensive joins.

Fix: Check execution plans, partition pruning, indexes, and realistic latency under load.

// Part 08 — Practice

Plan Storage for a Growing Orders Platform

Practice

Write a scale plan before choosing a shard count

  1. List the key reads and writes for checkout, order history, restaurant menus, and delivery tracking; mark their freshness and transaction needs.
  2. Choose SQL or a NoSQL model for each workload and justify it through access patterns, invariants, and query flexibility.
  3. Sketch a primary with replicas and identify which reads may go to replicas and what read-your-writes behavior is required.
  4. Choose a partition key and shard key for order history; describe one query that becomes easier and one that becomes harder.
  5. Describe how you would find a hot key, move a shard, validate copied records, and recover if the migration fails.

Hint: State assumptions explicitly. Do not invent a traffic target; estimate it from a defined user and order volume, then explain which measurement would validate the estimate.

🎯 Key Takeaways

  • ✓Choose storage from data invariants, access patterns, and workload rather than from a SQL-versus-NoSQL slogan.
  • ✓Replicas can improve read capacity and resilience, but may be stale and do not automatically scale writes.
  • ✓Partitioning organizes data within a database; sharding distributes it across nodes and makes routing a design responsibility.
  • ✓A shard key trades even distribution against query locality; hot keys can defeat otherwise good distribution.
  • ✓Plan failover, backups, restores, rebalancing, and migrations as core parts of the design.
  • ✓Measure query plans, lag, saturation, and per-shard skew before adding complexity.

Next

What comes next

Storage can persist work, but synchronous request chains still couple services. Next, use queues and workers to decouple slow or bursty tasks safely.

Lesson 07 · Message Queues and Asynchronous Processing →

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...