Definition of the word
a piece of broken ceramic, metal, glass, or rock, typically having sharp edges.”shards of glass flew in all directions”
Why
In today’s data-driven world, success often means rapid growth – and with that growth comes increasingly massive datasets. Traditional database setups eventually hit performance walls: queries slow down, hardware costs skyrocket, and your once-nimble application becomes sluggish under the weight of its own data.
Analogy
The Single Database Way (One Platform):
Imagine you have just one train platform at a busy station. Every train — whether it’s going north, south, east, or west — has to use that same platform. Passengers have to wait while a train arrives, unloads, loads, and departs before the next train can come in. It gets crowded fast, delays pile up, and the station staff struggles to manage the constant flow.
The Sharding Way (Multiple Platforms):
Now imagine you build separate platforms for different train lines — one for northbound trains, one for southbound, another for eastbound, and so on. Passengers go directly to the platform for their route, trains arrive and depart simultaneously on different tracks, and everything runs much more smoothly. The workload is divided, delays are reduced, and the station is far more efficient.
How
Database sharding offers a strategic solution by horizontally partitioning your data across multiple separate database instances. Rather than scaling up a single monolithic database (expensive and eventually impossible), sharding lets you scale out by distributing the load across multiple machines.
1. Application-Level Sharding (Manual)
- How it works: Your app logic decides which database to write/read from based on a sharding key (e.g., user ID).
- Pros:
- Simple and flexible.
- Works with vanilla PostgreSQL.
- Cons:
- App becomes tightly coupled to shard logic.
- Cross-shard queries are hard to manage.
Use case: SaaS apps with clean tenant separation.
2. Foreign Data Wrappers (FDW)
- Use postgres_fdw to link multiple Postgres servers and query them as if they’re one.
- Each shard is a separate PostgreSQL instance, and a coordinator node aggregates results.
- Pros:
- Works with standard Postgres.
- Allows federated queries.
- Cons:
- Limited optimizer support.
- Performance penalty for cross-shard joins.
3. Citus (PostgreSQL Extension)
- Distributed PostgreSQL: Citus transforms Postgres into a horizontally scalable database.
- Supports real-time distributed SQL queries.
- Handles sharding, replication, and distributed transactions.
- Pros:
- Automatic sharding and parallelism.
- Supports complex queries and joins.
- Cons:
- Requires Citus installation.
- Some Postgres features may be limited.
Use case: Real-time analytics, multi-tenant apps.
4. Schema-Based Sharding
- Create multiple schemas in one Postgres instance, each acting like a shard.
- Good for multi-tenant apps where each tenant gets a schema.
- Pros:
- Simple to manage.
- No extra tooling.
- Cons:
- Doesn’t scale across machines.
- Can hit performance limits at scale.
5. Hash-Based or Range-Based Sharding (Custom)
- Partition data based on a hash or range of a sharding key (like
user_id) and route queries accordingly. - Can be implemented with:
- Partitioned tables (Postgres 10+).
- Application logic or proxy routing.
- Pros:
- Better distribution control.
- Cons:
- Requires custom coordination and routing logic.
6. Proxy-Based Sharding
- Use a proxy layer (like Pgpool-II, Odyssey, or custom reverse proxies) to manage routing and connections to shards.
- Can perform load balancing and failover too.
- Cons:
- Adds latency and complexity.
- Doesn’t inherently solve cross-shard consistency.
Simple Example implementation with EF
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public DateTime CreatedAt { get; set; }
}
public class ShardDbContext : DbContext
{
public ShardDbContext(DbContextOptions options) : base(options)
{
}
public DbSet<Customer> Customers { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Customer>()
.HasKey(c => c.Id);
}
}
public class ShardingManager
{
private readonly Dictionary<int, string> _shardMap = new Dictionary<int, string>
{
{ 1, "Server=shard1.example.com;Database=Tenant1Db;User Id=user;Password=password;" },
{ 2, "Server=shard2.example.com;Database=Tenant2Db;User Id=user;Password=password;" },
{ 3, "Server=shard3.example.com;Database=Tenant3Db;User Id=user;Password=password;" }
};
public string GetConnectionString(int tenantId)
{
if (_shardMap.TryGetValue(tenantId, out string connectionString))
{
return connectionString;
}
throw new KeyNotFoundException($"No shard configured for tenant {tenantId}");
}
}
public class ShardDbContextFactory
{
private readonly ShardingManager _shardingManager;
public ShardDbContextFactory(ShardingManager shardingManager)
{
_shardingManager = shardingManager;
}
public ShardDbContext CreateDbContext(int tenantId)
{
var connectionString = _shardingManager.GetConnectionString(tenantId);
var optionsBuilder = new DbContextOptionsBuilder<ShardDbContext>();
optionsBuilder.UseNpgsql(connectionString);
return new ShardDbContext(optionsBuilder.Options);
}
}
public class CustomerRepository
{
private readonly ShardDbContextFactory _contextFactory;
public CustomerRepository(ShardDbContextFactory contextFactory)
{
_contextFactory = contextFactory;
}
public async Task<Customer> GetCustomerAsync(int tenantId, int customerId)
{
using var dbContext = _contextFactory.CreateDbContext(tenantId);
return await dbContext.Customers.FindAsync(customerId);
}
public async Task<List<Customer>> GetAllCustomersAsync(int tenantId)
{
using var dbContext = _contextFactory.CreateDbContext(tenantId);
return await dbContext.Customers.ToListAsync();
}
public async Task AddCustomerAsync(int tenantId, Customer customer)
{
using var dbContext = _contextFactory.CreateDbContext(tenantId);
dbContext.Customers.Add(customer);
await dbContext.SaveChangesAsync();
}
public async Task<List<Customer>> GetCustomersFromAllShards()
{
var allCustomers = new List<Customer>();
foreach (var tenantId in new[] { 1, 2, 3 }) // Hardcoded for simplicity
{
using var dbContext = _contextFactory.CreateDbContext(tenantId);
var customers = await dbContext.Customers.ToListAsync();
allCustomers.AddRange(customers);
}
return allCustomers;
}
}
public class Program
{
public static void Main(string[] args)
{
var services = new ServiceCollection();
services.AddSingleton<ShardingManager>();
services.AddSingleton<ShardDbContextFactory>();
services.AddScoped<CustomerRepository>();
var serviceProvider = services.BuildServiceProvider();
var customerRepo = serviceProvider.GetRequiredService<CustomerRepository>();
ExampleUsageAsync(customerRepo).Wait();
}
private static async Task ExampleUsageAsync(CustomerRepository repository)
{
int currentTenantId = 1;
await repository.AddCustomerAsync(currentTenantId, new Customer
{
Name = "John Doe",
Email = "john@example.com",
CreatedAt = DateTime.UtcNow
});
var customers = await repository.GetAllCustomersAsync(currentTenantId);
foreach (var customer in customers)
{
Console.WriteLine($"Customer: {customer.Name}, Email: {customer.Email}");
}
var allCustomers = await repository.GetCustomersFromAllShards();
Console.WriteLine($"Total customers across all shards: {allCustomers.Count}");
}
}
TechSpark 