SQL 99 🛢️ Database Sharding and Read Replicas - KandZ

⏱️ Estimated read: 15 min | |

Sharding and read replicas are the two primary strategies for scaling a database beyond a single server, and they solve completely different problems. Read replicas create copies of the entire database that serve read-only queries, reducing the load on the primary instance for read-heavy workloads . Sharding partitions the data itself across multiple independent database instances, distributing both the data volume and the write load across many servers . Confusing the two is the most common architectural mistake in database scaling: a team adds read replicas to solve a write bottleneck, discovers that writes still go to the primary, and then has to retrofit sharding under pressure.

The distinction is precise. A read replica is a full copy. Every write that happens on the primary must be replicated to every replica, which means replication adds write amplification rather than reducing it . Sharding is a split. Each shard holds a subset of the data, and a write for a row in shard A never touches shard B. The primary constraint on read replicas is replication lag—the delay between a write committing on the primary and appearing on a replica . The primary constraint on sharding is cross-shard queries—a query that needs data from multiple shards must fan out to all of them and merge the results, which is expensive and complex .

Key point: Read replicas scale reads by copying the entire database to additional nodes, but they do not reduce write load because every write must be replicated to every copy; sharding scales writes and data volume by partitioning rows across independent nodes, but it makes cross-shard queries expensive and requires a shard key that distributes data evenly.

Why read replicas and sharding solve different bottlenecks

The write amplification problem. Every write on the primary is sent to every read replica. If the primary handles 1,000 writes per second and there are three replicas, the total write traffic across the system is 4,000 operations per second—one on the primary, three replicated . Adding a fourth replica makes it 5,000. Replicas do not reduce the work the primary does; they multiply the work the system does to keep copies in sync. This is why read replicas are the correct tool for read-heavy workloads and the wrong tool for write-heavy ones.

The data volume problem. A read replica holds a full copy of the database. If the database is 10 TB, every replica is 10 TB. Sharding divides that 10 TB across multiple nodes: four shards of 2.5 TB each, or ten shards of 1 TB each. The total storage across the cluster is the same, but each individual node holds only a fraction . This is the property that allows a sharded database to exceed the storage capacity of a single server, which a replicated database cannot.

The replication lag problem. Read replicas are updated asynchronously. Under heavy write load, the replica can fall seconds or minutes behind the primary . An application that reads its own writes—a user updates their profile, then immediately views it—may see stale data if the read is routed to a replica. The mitigation is to route the user’s reads to the primary for a short window after a write, or to use synchronous replication for critical operations, which adds latency to every write . Sharding does not have this problem for single-shard operations: the write and the subsequent read go to the same shard, and the shard is consistent.

The cross-shard query problem. Sharding introduces a new cost: a query that needs data from multiple shards must be sent to all of them and the results merged. A query for “all orders in the last hour” on a database sharded by customer ID must fan out to every shard because orders from the same hour are spread across all of them . This is why the shard key must be chosen carefully: the key should align with the most common query pattern so that most queries touch a single shard. Read replicas do not have this problem because every replica holds the full dataset; a query on a replica is the same query as on the primary, just executed against a copy.

a. Read replicas: copying for read scale and availability

A read replica is created from a snapshot of the primary, then kept in sync through the database engine’s native replication mechanism. In PostgreSQL, this is streaming replication: the primary sends WAL (write-ahead log) records to the replica, which applies them to stay current . In MySQL, it is binary log replication. In Amazon RDS, the process is managed by the platform: RDS takes a snapshot, creates the replica from it, and configures asynchronous replication between the primary and the replica .

The replica accepts read-only connections. Applications route their read queries to the replica pool and their write queries to the primary. The routing can be done in the application code, in a connection pooler, or in a proxy.

-- PostgreSQL: create a read replica using pg_basebackup
-- On the replica host:
pg_basebackup -h primary-host -D /var/lib/postgresql/data -U replicator -P -R
-- The -R flag writes recovery.conf (or postgresql.auto.conf) with the primary_conninfo
-- Start PostgreSQL on the replica; it will stream WAL from the primary

The -R flag is the detail that makes the replica a replica: it writes the connection string to the primary into the recovery configuration, so the replica knows where to stream from. Without it, the base backup is just a copy, not a live replica.

The monitoring metric is replication lag. In PostgreSQL, pg_stat_replication on the primary shows the replay_lag for each connected replica—the delay between the primary’s current WAL position and what the replica has applied . In MySQL, SHOW REPLICA STATUS shows Seconds_Behind_Source . A lag that grows under load indicates the replica cannot keep up, which may mean the replica needs more CPU or the primary is generating WAL faster than the network can transfer it.

Read replicas also serve availability. If the primary fails, a replica can be promoted to become the new primary. This is the disaster recovery use case: the replica is not just for read scale, it is a warm standby that can take over . The promotion is manual in most systems unless a failover manager is configured, and the promoted replica becomes the new source for the other replicas.

b. Sharding: partitioning for write scale and data volume

Sharding divides the rows of a table across multiple independent database instances. Each shard holds a subset of the rows, determined by a shard key. The application, or a routing layer, computes the shard from the key and sends the query to the correct instance.

The shard key is the most consequential decision. It must distribute data evenly across shards, and it must align with the query pattern so that most queries touch a single shard . A multi-tenant SaaS application shards by tenant_id: each tenant’s data lives on one shard, and queries for a tenant’s data go to one shard. A time-series application might shard by user_id if the most common query is “recent data for this user,” because that query touches one shard. Sharding by order_date would spread a single user’s data across all shards, making the most common query a cross-shard fan-out.

The two common distribution methods are range and hash. Range sharding assigns contiguous key ranges to shards: user_id 1–1,000,000 on shard 1, 1,000,001–2,000,000 on shard 2. It supports range queries efficiently but can create hotspots if the key is monotonically increasing (all new users land on the last shard). Hash sharding applies a hash function to the key and assigns the result to a shard: hash(user_id) % shard_count. It distributes data evenly but makes range queries expensive because the hash scatters ordered values across all shards .

-- Citus (PostgreSQL extension): distribute a table by tenant_id
CREATE TABLE orders (
    id BIGSERIAL,
    tenant_id BIGINT NOT NULL,
    amount NUMERIC
);
SELECT create_distributed_table('orders', 'tenant_id');
-- Citus creates shards and routes queries by tenant_id automatically

The create_distributed_table call is the point of no return: Citus creates the shards and configures the routing. From that point, a query with WHERE tenant_id = 42 goes to one shard. A query without tenant_id fans out to every shard, which is the cross-shard cost .

The operational cost of sharding is significant. Schema changes must be applied to every shard. Rebalancing—moving data when a new shard is added—requires a tool that can migrate rows without downtime. Cross-shard transactions require two-phase commit or application-level compensation. The general guidance is to exhaust vertical scaling and read replicas before sharding, and to consider sharding when a single server’s limits are reached—roughly 1 TB of data or 10,000 queries per second .

c. Combining them: replicas within shards

Sharding and replication are complementary, not alternative. In a sharded system, each shard can itself have read replicas. The shard’s primary handles writes for its subset of the data, and the shard’s replicas serve reads for that subset. This is the architecture of most large-scale systems: sharding for write scale and data volume, replication within each shard for read scale and availability.

Shard 1: Primary + Replica
Shard 2: Primary + Replica
Shard 3: Primary + Replica

A query for tenant 42 goes to Shard 2’s replica (if it is a read) or Shard 2’s primary (if it is a write). The replica is a full copy of Shard 2, not of the entire database. The replication lag applies only to Shard 2’s data, and the read-your-writes problem is confined to that shard.

Oracle’s sharded database architecture uses this pattern explicitly: each shard is a separate database instance, and each shard can have a standby replica managed by Data Guard for high availability and read-only access . The shard catalog maintains the routing table that maps key ranges to shards, and the application connects through a shard director that routes queries to the correct shard .

Complete Example Session

The following session demonstrates the two strategies in sequence: a read replica for read scale, then sharding for write scale, then the combination.

// ============================================
// PART 1: READ REPLICA — READ SCALE
// ============================================
-- Primary handles all writes
-- Replica handles read-only queries
-- Every write on primary is replicated to replica

Primary:  INSERT INTO users (id, name) VALUES (1, 'Alice');  -- write
Replica:  SELECT * FROM users WHERE id = 1;                  -- read (may be stale)

-- Monitor lag:
-- PostgreSQL: SELECT replay_lag FROM pg_stat_replication;
-- MySQL:      SHOW REPLICA STATUS; -- Seconds_Behind_Source

// ============================================
// PART 2: THE WRITE BOTTLENECK
// ============================================
-- Primary is saturated by writes
-- Adding another replica does NOT help
-- Every write is still sent to every replica
-- Write amplification increases with each replica

Primary:  1,000 writes/sec
Replica1: receives 1,000 writes/sec
Replica2: receives 1,000 writes/sec
Total:    3,000 write operations/sec for 1,000 logical writes

// ============================================
// PART 3: SHARDING — WRITE SCALE
// ============================================
-- Split rows by tenant_id
-- Shard 1: tenants 1-1000
-- Shard 2: tenants 1001-2000
-- Shard 3: tenants 2001-3000

-- A write for tenant 42 goes to Shard 1 only
-- A write for tenant 1500 goes to Shard 2 only
-- Each shard handles 1/3 of the write load

-- Query with shard key: single shard
SELECT * FROM orders WHERE tenant_id = 42;  -- Shard 1

-- Query without shard key: fan-out to all shards
SELECT * FROM orders WHERE amount > 100;    -- Shard 1 + Shard 2 + Shard 3

// ============================================
// PART 4: COMBINED — SHARD + REPLICA PER SHARD
// ============================================
-- Each shard has a primary and a replica
-- Shard 1 Primary: writes for tenants 1-1000
-- Shard 1 Replica: reads for tenants 1-1000
-- Shard 2 Primary: writes for tenants 1001-2000
-- Shard 2 Replica: reads for tenants 1001-2000

-- Read for tenant 42: Shard 1 replica
-- Write for tenant 42: Shard 1 primary
-- Read for tenant 1500: Shard 2 replica
-- Write for tenant 1500: Shard 2 primary

The four parts demonstrate the progression: a read replica reduces read load but not write load; the write bottleneck emerges; sharding distributes writes across independent nodes; and the combination gives each shard its own read replica for read scale and availability within the shard.

Quick Reference

Read Replica vs Sharding

Aspect	Read Replica	Sharding
What it copies	Full database	Subset of rows
Scales	Reads	Writes + data volume
Write load	Amplified (every replica)	Distributed (each shard)
Cross-node query	Same query on any replica	Fan-out to all shards
Consistency	Asynchronous lag	Single-shard consistent
Primary constraint	Replication lag	Shard key choice
Operational cost	Low	High

When to Use Each

Bottleneck	Solution
Read-heavy, writes manageable	Read replicas
Write-heavy, single server saturated	Sharding
Data too large for one server	Sharding
High availability	Read replica (promotable)
Read scale within a shard	Replica per shard

Shard Key Selection

Criterion	Good	Bad
Distribution	Even across shards	Skewed (hotspot)
Query alignment	Most queries touch one shard	Most queries fan out
Cardinality	High (many distinct values)	Low (few values)
Growth	Stable over time	Monotonic (new data on one shard)

Monitoring Metrics

Metric	Tool
Replication lag (PostgreSQL)	`pg_stat_replication.replay_lag`
Replication lag (MySQL)	`SHOW REPLICA STATUS`
Shard balance	Row count / size per shard
Cross-shard query rate	Query logs / APM

Best Practices

✅ Do This:

-- Use read replicas for read-heavy workloads
-- Route writes to primary, reads to replica

-- Shard by a key that aligns with the query pattern
-- Multi-tenant: shard by tenant_id

-- Monitor replication lag
SELECT replay_lag FROM pg_stat_replication;

-- Replicate each shard for read scale and availability

❌ Don’t Do This:

-- Add replicas to solve a write bottleneck
-- Every write is replicated to every replica

-- Shard before exhausting vertical scaling and replicas
-- Sharding adds significant operational complexity

-- Shard by a key that causes cross-shard queries
-- A query without the shard key fans out to all shards

-- Ignore replication lag for read-your-writes
-- Route the user's reads to the primary after a write

Common Pitfalls

Pitfall	Why It Happens	Fix
Write bottleneck persists after adding replicas	Replicas do not reduce write load	Shard the data
Stale reads after write	Replication lag	Route reads to primary briefly
Cross-shard queries slow	Shard key misaligned with queries	Choose a key that matches access pattern
Hot shard	Monotonic key or skewed data	Hash sharding or rebalance
Schema change fails on one shard	DDL not propagated	Use a sharding tool or apply to all

Real-World Examples

1. PostgreSQL read replica

pg_basebackup -h primary -D /data -U replicator -P -R

2. MySQL read replica status

SHOW REPLICA STATUS\G
-- Look for Seconds_Behind_Source

3. Citus sharding

SELECT create_distributed_table('orders', 'tenant_id');

4. Query with shard key

SELECT * FROM orders WHERE tenant_id = 42;

5. Query without shard key (fan-out)

SELECT * FROM orders WHERE amount > 100;

6. Shard key choice

Multi-tenant SaaS → tenant_id
User-centric app → user_id
Time-series with per-user queries → user_id

7. Replica promotion

pg_ctl promote -D /data

8. Shard rebalancing

SELECT rebalance_table_shards();

9. Monitor shard size

SELECT shardid, shard_size FROM citus_shards;

10. Read-your-writes routing

After a write, route the user's reads to the primary for N seconds

Visual

Read Replica Architecture

┌──────────────────────────────────────────────┐
│  APPLICATION                                 │
│  Writes → Primary                            │
│  Reads  → Replica                            │
│         │                                    │
│         ▼                                    │
│  ┌─────────────┐    async    ┌─────────────┐ │
│  │  PRIMARY    │───────────▶│  REPLICA    │ │
│  │  (all data) │             │  (all data) │ │
│  └─────────────┘             └─────────────┘ │
│                                              │
│  Every write goes to every replica           │
│  Replica is a full copy                      │
└──────────────────────────────────────────────┘

Sharded Architecture

┌──────────────────────────────────────────────┐
│  APPLICATION                                 │
│  tenant 42 → Shard 1                         │
│  tenant 1500 → Shard 2                       │
│         │                                    │
│         ▼                                    │
│  ┌─────────┐  ┌─────────┐  ┌─────────┐      │
│  │ SHARD 1 │  │ SHARD 2 │  │ SHARD 3 │      │
│  │ 1-1000  │  │1001-2000│  │2001-3000│      │
│  └─────────┘  └─────────┘  └─────────┘      │
│                                              │
│  Each shard holds a subset                   │
│  Writes distributed, not amplified           │
└──────────────────────────────────────────────┘

Combined: Shard + Replica

┌──────────────────────────────────────────────┐
│  SHARD 1                                     │
│  ┌─────────┐    async    ┌─────────┐         │
│  │ Primary │────────────▶│ Replica │         │
│  └─────────┘             └─────────┘         │
│         │                                    │
│  SHARD 2                                     │
│  ┌─────────┐    async    ┌─────────┐         │
│  │ Primary │────────────▶│ Replica │         │
│  └─────────┘             └─────────┘         │
│                                              │
│  Each shard has its own replica              │
└──────────────────────────────────────────────┘

Decision Flow

┌──────────────────────────────────────────────┐
│  Is the bottleneck reads?                    │
│  ├─ YES → Add read replicas                  │
│  └─ NO → Is the bottleneck writes or data?   │
│          ├─ YES → Exhaust vertical scaling   │
│          │        then shard                 │
│          └─ NO → Profile the workload        │
└──────────────────────────────────────────────┘

Summary

Item	Value
Read replica	Full copy, read-only, async replication
Sharding	Subset of rows, independent instances
Read replica scales	Reads
Sharding scales	Writes + data volume
Write amplification	Replicas multiply write work
Replication lag	Stale reads on replicas
Shard key	Determines query routing
Cross-shard query	Fan-out, expensive
Combined	Replica per shard

Key takeaways:

Read replicas and sharding solve different bottlenecks. Replicas scale reads by copying the full database to additional nodes. Sharding scales writes and data volume by partitioning rows across independent nodes .
Read replicas do not reduce write load. Every write on the primary must be replicated to every replica, which amplifies the total write work rather than distributing it .
Replication lag causes stale reads. Under heavy write load, replicas fall behind. Applications that read their own writes must route those reads to the primary or use synchronous replication .
Sharding requires a shard key that aligns with the query pattern. A query without the shard key fans out to every shard, which is expensive and slow .
Sharding adds significant operational complexity. Schema changes, rebalancing, and cross-shard transactions all require additional tooling and planning. Exhaust vertical scaling and read replicas before sharding .
The two strategies are complementary. In a sharded system, each shard can have its own read replica for read scale and availability within that shard .

Remember: Read replicas and sharding are not competing strategies. They are sequential answers to sequential bottlenecks. When the primary is saturated with reads, add replicas. When the primary is saturated with writes, or when the data no longer fits on one server, shard. The replica copies everything; the shard splits everything. The replica has lag; the shard has cross-shard queries. The replica is operationally simple; the shard is operationally complex. Choose based on the bottleneck, not on the architecture diagram, and combine them when the system needs both read scale and write scale.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!