Discuss different approaches to database sharding. What are the benefits and drawbacks of sharding?

Database sharding is a technique used to distribute data across multiple database instances to improve scalability, performance, and fault tolerance. It is commonly used in large-scale applications where a single database becomes a bottleneck due to high read/write operations.

Approaches to Database Sharding

1. Horizontal Sharding (Range-Based Sharding)

This method distributes rows based on a fixed range of values. For example, users with user_id between 1-100000 are stored in shard_1, while 100001-200000 are stored in shard_2.

SQL Implementation Using Partitioning (PostgreSQL)

CREATE TABLE users (
    user_id INT NOT NULL,
    name VARCHAR(100),
    email VARCHAR(100)
) PARTITION BY RANGE (user_id);

CREATE TABLE users_part1 PARTITION OF users 
    FOR VALUES FROM (1) TO (100000);

CREATE TABLE users_part2 PARTITION OF users 
    FOR VALUES FROM (100001) TO (200000);

Query Example on Partitioned Table

SELECT * FROM users WHERE user_id = 5000; -- Automatically queries users_part1

Pros:

• Efficient for time-series data or sequential datasets.
• Easy to implement using partitioning in relational databases.

Cons:

• Uneven data growth: Some shards may get overloaded while others remain underutilized.
• Rebalancing issues: If one shard exceeds its capacity, redistributing data can be complex.

2. Hash-Based Sharding

This method assigns data to different shards using a hash function on a key (e.g., MOD(user_id, num_shards)). It ensures even data distribution across shards.

SQL Implementation Using MOD() for Sharding

SELECT * FROM users WHERE MOD(user_id, 4) = 0; -- Data for Shard 0
SELECT * FROM users WHERE MOD(user_id, 4) = 1; -- Data for Shard 1
SELECT * FROM users WHERE MOD(user_id, 4) = 2; -- Data for Shard 2
SELECT * FROM users WHERE MOD(user_id, 4) = 3; -- Data for Shard 3

Pros:

• Ensures even load distribution across shards.
• Avoids range-based hotspots.

Cons:

• Difficult to add shards dynamically (changing the number of shards breaks the hash function).
• Cross-shard queries are complex, requiring aggregation from multiple shards.

3. Directory-Based Sharding

A lookup table maintains mappings between keys and shards, allowing dynamic data placement.

Example Lookup Table for Sharding

CREATE TABLE shard_lookup (
    user_id INT PRIMARY KEY,
    shard_id VARCHAR(50)
);

INSERT INTO shard_lookup VALUES (12345, 'db_shard_2');
SELECT shard_id FROM shard_lookup WHERE user_id = 12345;

Pros:

• Flexible sharding strategy, allowing easy addition or removal of shards.
• Custom data placement is possible based on workload requirements.

Cons:

• The lookup table becomes a single point of failure if not replicated properly.
• Additional query overhead for fetching the shard location before retrieving actual data.

4. Geo-Based Sharding

Data is stored geographically, meaning users from different regions are placed in separate databases for reduced latency.

Example: Sharding Users Based on Region

US_users → us_db
EU_users → eu_db
ASIA_users → asia_db

Pros:

• Minimizes latency by keeping data closer to users.
• Ensures compliance with regional data regulations (e.g., GDPR).

Cons:

• Difficult to rebalance shards if traffic patterns change.
• Cross-region queries become slow, requiring additional network overhead.

Benefits of Sharding

Drawbacks of Sharding

Nuances to Consider

• Choosing the Right Sharding Strategy

  • The optimal sharding method depends on data access patterns (e.g., range-based for sequential data, hash-based for uniform distribution).

• Maintaining Data Consistency

  • Transactions spanning multiple shards require distributed transactions, which are complex and slow.

• Monitoring & Failover

  • Each shard must be monitored separately to detect failures and prevent data inconsistencies.

Tecyfy Takeaway
Sharding is essential for scaling large applications, but it introduces complexity in data consistency, cross-shard queries, and maintenance. The right sharding approach depends on workload characteristics—hash-based for even distribution, range-based for sequential data, and geo-based for regional deployments. Proper monitoring, indexing, and query optimization are crucial for successful sharding implementation.