Database Sharding

Overview

• Definition: Horizontal partitioning of database across multiple distributed servers
• Purpose: Split large datasets into smaller, manageable pieces (shards)
• Distribution: Each shard contains subset of total data
• Independence: Shards operate on separate database instances

Core Problem

• Performance Degradation: Large tables cause slow queries
• Scalability Limits: Single database cannot handle massive datasets
• Index Limitations: Even indexes become ineffective with enormous row counts
• Resource Constraints: Memory, CPU, and storage limitations on single server

Sharding Strategies

Horizontal Sharding (Row-based)

• Method: Split rows across multiple databases
• Example: Users 1-1000 → Shard 1, Users 1001-2000 → Shard 2
• Most Common: Standard sharding approach

Vertical Sharding (Column-based)

• Method: Split columns across multiple databases
• Example: User profile data → Shard 1, User activity data → Shard 2
• Use Case: Different access patterns for different data types

Directory-based Sharding

• Method: Lookup service maps data location
• Benefit: Flexible data distribution
• Overhead: Additional lookup layer required

Key-based (Hash) Sharding

• Method: Hash function determines shard location
• Formula: shard = hash(key) % num_shards
• Benefit: Even data distribution
• Challenge: Difficult to rebalance

Range-based Sharding

• Method: Data ranges assigned to specific shards
• Example: A-M names → Shard 1, N-Z names → Shard 2
• Risk: Uneven distribution (hotspots)

Advantages

Performance Benefits

• Parallel Processing: Queries run on multiple servers simultaneously
• Smaller Indexes: Faster index operations on smaller datasets
• Reduced Lock Contention: Fewer concurrent operations per shard
• Faster Queries: Working with subset of data

Scalability Benefits

• Horizontal Scaling: Add more servers as data grows
• Data Distribution: No single point of storage bottleneck
• Memory Scaling: More total memory across shards
• Processing Power: Distributed CPU resources

Operational Benefits

• Fault Isolation: Failure affects only specific shard
• Security Segmentation: Access control per shard
• Maintenance Windows: Update shards independently
• Geographic Distribution: Place shards closer to users

Disadvantages

Complexity Challenges

• Application Logic: Client must be shard-aware
• Routing Logic: Determine correct shard for operations
• Data Location: Track where specific data resides
• Configuration Management: Multiple database configurations

Transaction Limitations

• Cross-shard Transactions: Distributed transactions are complex
• ACID Properties: Difficult to maintain across shards
• Rollback Complexity: Coordinating rollbacks across multiple shards
• Consistency Challenges: Eventual consistency vs. strong consistency

Operational Difficulties

• Schema Changes: Must coordinate across all shards
• Joins: Cross-shard joins are expensive or impossible
• Aggregations: Difficult to aggregate data across shards
• Backup/Recovery: Complex backup and restore procedures

Rebalancing Issues

• Data Growth: Uneven shard growth over time
• Hotspots: Some shards receive more traffic
• Resharding: Moving data between shards is expensive
• Downtime: Rebalancing may require maintenance windows

Implementation Considerations

Shard Key Selection

• Critical Decision: Determines data distribution quality
• Requirements:
  • High cardinality (many unique values)
  • Even distribution
  • Query-friendly (appears in WHERE clauses)
• Examples: User ID, tenant ID (hashed)
• Bad Keys: A monotonic timestamp sends every new write to one hot shard; geographic region is low-cardinality and skewed. Use time only as a secondary/range key (e.g. archival)

Query Patterns

• Shard-local Queries: Queries hitting single shard (optimal)
• Cross-shard Queries: Queries spanning multiple shards (expensive)
• Design Goal: Maximize shard-local operations

Monitoring Requirements

• Shard Health: Monitor each shard individually
• Load Balancing: Track query distribution
• Performance Metrics: Response times per shard
• Capacity Planning: Storage and performance trends

Common Patterns

Database-per-Tenant

• Use Case: Multi-tenant SaaS applications
• Isolation: Complete data separation per customer
• Benefits: Security, compliance, customization
• Challenges: Management overhead

Geographic Sharding

• Use Case: Global applications
• Method: Shard by user location or data jurisdiction
• Benefits: Reduced latency, data compliance
• Considerations: Cross-region consistency

Time-based Sharding

• Use Case: Time-series data
• Method: Shard by date ranges
• Benefits: Archive old shards, query recent data
• Pattern: Hot/warm/cold data tiers

Best Practices

Design Principles

• Plan Early: Consider sharding during initial design
• Shard Key: Choose carefully based on access patterns
• Start Simple: Begin with fewer shards, scale up
• Monitor Closely: Track performance and distribution

Avoid Common Pitfalls

• Don’t Over-shard: Too many small shards create overhead
• Avoid Hotspots: Design for even load distribution
• Plan for Growth: Consider future scaling needs
• Test Thoroughly: Validate cross-shard scenarios

Alternatives to Consider

Read Replicas

• Use Case: Read-heavy workloads
• Benefit: Simpler than sharding
• Limitation: Doesn’t help with write scalability

Caching

• Use Case: Frequently accessed data
• Benefit: Reduces database load
• Limitation: Temporary solution for growing data

Database Clustering

• Use Case: High availability needs
• Benefit: Built-in clustering features
• Limitation: Still limited by single logical database

When to Use Sharding

• Data Size: Tables with hundreds of millions of rows
• Write Scalability: High write volume that single DB can’t handle
• Geographic Requirements: Data locality needs
• Tenant Isolation: Strong isolation requirements

When to Avoid Sharding

• Premature Optimization: Don’t shard until you need to
• Small Datasets: Overhead outweighs benefits
• Complex Queries: Heavy use of joins and aggregations
• Limited Resources: Insufficient expertise or infrastructure