Database Replication & Sharding

What is Database Replication?

Replication is the process of copying and maintaining the same data across multiple database servers. It improves data availability, fault tolerance, and read performance.

Types of Replication

• Master-Slave (Primary-Replica): One server handles writes, others handle reads.
• Master-Master (Multi-Primary): Multiple servers handle both reads and writes.

Advantages of Replication

• Improves read performance
• Provides high availability and disaster recovery
• Supports load balancing for read operations

Disadvantages of Replication

• Data inconsistency due to replication lag
• Higher storage requirements
• Complex conflict resolution in multi-primary setups

What is Database Sharding?

Sharding is a database scaling technique where data is split across multiple servers (shards). Each shard holds a subset of the total data.

How Sharding Works

• Data is divided using a shard key
• Each shard handles a portion of the data
• Queries are routed to the correct shard

Advantages of Sharding

• Improves scalability and performance
• Reduces query time by working on smaller datasets
• Supports geographic distribution of data

Disadvantages of Sharding

• Complex setup and maintenance
• Cross-shard queries are slow and complex
• Uneven data growth may require rebalancing

Comparison Table

Feature	Replication	Sharding
Purpose	High availability and redundancy	Scalability and performance
Data Distribution	Full copy on each replica	Subset of data per shard
Read/Write Handling	Reads distributed, writes centralized	Reads and writes distributed by shard
Complexity	Lower setup complexity	Higher setup and query complexity

When to Use Replication vs Sharding

Use Replication When:

• You need high availability and fault tolerance
• You want to improve read performance by distributing read queries
• You need real-time backups or disaster recovery
• Your application is read-heavy and write operations are centralized
• You want to reduce downtime during maintenance

Example Scenarios:

• News websites with millions of readers but few writers
• E-commerce platforms where product browsing is frequent
• Analytics dashboards pulling data from replicas

Use Sharding When:

• Your database has grown too large for a single server
• You need to scale horizontally across multiple machines
• Your application has high write throughput and large datasets
• You want to isolate workloads to reduce contention
• You need to reduce query latency by working on smaller datasets

Example Scenarios:

• Social media platforms with billions of user records
• Online gaming systems with massive player data
• Financial systems handling millions of transactions

Best Practices for Replication and Sharding

Replication Best Practices

• Backup Strategy: Regularly back up publication, distribution, and subscription databases.
• Monitor Performance: Use tools like Replication Monitor to track latency, throughput, and resource usage.
• Script Topology: Script your replication setup for disaster recovery and automation.
• Alerting: Set up alerts for replication failures or delays.
• Validate Data: Periodically check for data consistency across replicas.
• Tune Agents: Adjust replication agent parameters for optimal performance.
• Retention Policies: Set appropriate retention periods for publications and distributions.
• Schema Changes: Plan schema changes carefully to avoid breaking replication.

Sharding Best Practices

• Choose the Right Shard Key: Select keys based on query patterns and data distribution.
• Estimate Growth: Plan for future data volume and shard expansion.
• Avoid Cross-Shard Joins: Design schema to minimize queries across multiple shards.
• Monitor Hotspots: Track uneven shard usage and rebalance when needed.
• Automate Resharding: Use tools or scripts to redistribute data as needed.
• Ensure Replication: Replicate shards for durability and fault tolerance.
• Shard-Aware Caching: Cache data at the shard level to reduce latency.
• Use Consistent Hashing: For dynamic workloads, consistent hashing helps with balanced distribution.