Sharding: How to Split a Database to Scale Horizontally

⏱️ Lectura: 12 min

A users table that grows faster than a single server’s capacity stops being an indexing problem: it becomes an architecture problem. When neither the biggest disk nor the most generous RAM is enough, the only way out is to split the data across multiple machines. This is called sharding.

📑 En este artículo

1. TL;DR
2. What Sharding Is and Why It Matters
3. How It Works: Partitioning Strategies
   1. Range Sharding
   2. Hash Sharding
   3. Directory-Based Sharding
4. Practical Examples
5. How to Implement It Step by Step
6. Real-World Use Cases
7. Common Mistakes and Best Practices
8. Comparison with Alternatives
9. Going Deeper (Advanced)
10. Frequently Asked Questions
    1. Are Sharding and Partitioning the Same Thing?
    2. When Should You Use Sharding Instead of Scaling Vertically?
    3. How Do Queries That Span Multiple Shards Work?
    4. What Happens When You Need to Add More Shards (Resharding)?
    5. Hash Sharding or Range Sharding: Which One Should You Choose?
    6. Does Sharding Replace Indexes or Replication?
11. References

This guide explains how database sharding works, what strategies exist to distribute data (by hash, by range, by directory), how to implement it today in PostgreSQL, and the mistakes that turn a well-intentioned sharding effort into a months-long migration.

TL;DR

• You’ll understand the difference between sharding, partitioning, and replication, and when to use each one.
• You’ll be able to calculate which shard a record belongs to using a hash function in a few lines of code.
• You’ll know how to create hash partitions in PostgreSQL with CREATE TABLE … PARTITION BY HASH.
• You’ll identify the three gotchas that break a poorly designed sharding setup: hot keys, cross-shard joins, and resharding.
• You’ll compare range sharding against hash sharding with a clear decision table.
• You’ll learn how Vitess solves MySQL sharding without rewriting the entire application.
• You’ll be able to verify with EXPLAIN whether PostgreSQL is using partition pruning in your queries.

What Sharding Is and Why It Matters

Database sharding consists of dividing a table (or a set of related tables) into horizontal fragments, called shards, and distributing those fragments across independent servers. Each shard holds a subset of the rows, not the columns, and can answer most queries without depending on the other shards.

It’s easy to confuse sharding with partitioning. Partitioning divides a table into fragments that still live on the same server and under the same database engine; sharding goes a step further and distributes those fragments across separate physical machines, each with its own disk, its own memory, and its own database process. PostgreSQL has supported native declarative partitioning since version 10, and hash partitioning specifically since version 11.

It matters because scaling vertically (buying a bigger server) has a ceiling: there’s a physical limit on the CPU, RAM, and disk a single server can offer, and that limit becomes extremely expensive well before it’s reached. Sharding changes the problem: instead of one bigger server, you use several medium-sized servers, each responsible for a portion of the data.

📌 Note: sharding isn’t free. It adds operational complexity (more servers to monitor, more connections to manage) and query complexity (joins and transactions that cross shards stop being trivial). It’s a tool for a specific problem: when a single server is no longer enough, not a default architecture choice.

How It Works: Partitioning Strategies

Range Sharding

Each shard receives a contiguous range of values from the partitioning key (the shard key). For example, orders with id 1 to 1,000,000 go to shard 0, orders with id 1,000,001 to 2,000,000 go to shard 1, and so on. The advantage is that range queries (“all orders from this week”) touch only a few shards. The downside is the hot shard: if the key grows sequentially (an auto-incrementing id, a date), all new writes end up on the last shard while the others sit nearly idle.

Hash Sharding

A hash function is applied to the shard key, and the result, modulo the number of shards, determines which shard each row goes to. The distribution is nearly always uniform, so there are no hot shards from sequential keys. The downside shows up when the number of shards changes: with a simple modulo, adding one more shard relocates most of the existing rows. That’s why large systems usually rely on consistent hashing or virtual shards (more logical shards than physical ones) to minimize how much data moves when scaling.

Directory-Based Sharding

A lookup service (a small table, almost always replicated and cached) explicitly stores which shard each key belongs to. It’s the most flexible option: it lets you move a specific key without touching a mathematical formula, useful for isolating a particularly heavy customer into its own shard. The downside is that this lookup service becomes an additional critical piece that also needs to stay available.

flowchart TD
    A["Application"] --> B["Hash function on the shard key"]
    B --> C{"hash mod N"}
    C -->|"result 0"| D[("Shard 0")]
    C -->|"result 1"| E[("Shard 1")]
    C -->|"result 2"| F[("Shard 2")]

Practical Examples

The simplest case: calculating which shard a record belongs to based on its key. This is the bare minimum any hash-sharding routing layer needs.

const crypto = require("crypto");

function shardId(clienteId, totalShards) {
  const hash = crypto.createHash("md5").update(String(clienteId)).digest();
  return hash.readUInt32BE(0) % totalShards;
}

console.log(shardId(42, 4));   // returns a number between 0 and 3
console.log(shardId(1001, 4)); // different customers, different shards

This function takes a clienteId, computes its MD5 hash, and uses the first 4 bytes as an integer to apply the modulo. The result is deterministic: the same clienteId always lands on the same shard as long as totalShards doesn’t change.

The next realistic step is to use that calculation to pick an actual database connection, not just a number:

const pools = [pool0, pool1, pool2, pool3]; // one pg.Pool per shard

async function getPedidosDeCliente(clienteId) {
  const shard = shardId(clienteId, pools.length);
  const db = pools[shard];
  const { rows } = await db.query(
    "SELECT * FROM pedidos WHERE cliente_id = $1",
    [clienteId]
  );
  return rows;
}

This routing layer is, in essence, what tools like Vitess do internally: they receive a query, identify the shard key, and route it to the right server without the application ever needing to know how many shards exist.

How to Implement It Step by Step

You don’t need to set up a full distributed infrastructure to start experimenting with sharding: PostgreSQL ships with declarative hash partitioning ready to use.

First, create the parent table specifying the partitioning strategy and the column that acts as the shard key:

CREATE TABLE pedidos (
    id bigint NOT NULL,
    cliente_id bigint NOT NULL,
    creado_en timestamptz NOT NULL,
    monto numeric(10,2) NOT NULL
) PARTITION BY HASH (cliente_id);

Then create the partitions, each responsible for one remainder of the modulo:

CREATE TABLE pedidos_p0 PARTITION OF pedidos
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE pedidos_p1 PARTITION OF pedidos
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE pedidos_p2 PARTITION OF pedidos
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE pedidos_p3 PARTITION OF pedidos
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

To confirm the table was actually partitioned, \d+ pedidos in psql lists the four child partitions. And to confirm that a specific query only touches the partition it should (partition pruning), this is enough:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pedidos WHERE cliente_id = 42;

The execution plan should show a single Seq Scan or Index Scan on pedidos_p0 (or the corresponding partition), not a scan across all four tables. If all four partitions show up in the plan, the query isn’t using the shard key in the WHERE clause, and PostgreSQL can’t prune the rest.

💡 Tip: this partitioning lives on a single Postgres server. For real sharding across separate machines, you also need a routing layer (application code, Vitess, Citus) that knows which server to connect to, not just which table.

Real-World Use Cases

The most common pattern in multi-tenant SaaS is to shard by tenant_id: each enterprise customer lands on its own shard, which also simplifies data isolation between customers. Social networks tend to shard by user_id, because almost every frequent query (profile, own posts, settings) filters by that same field.

Time-series systems (metrics, logs, sensor events) usually shard by date, accepting the hot-shard risk on the most recent range in exchange for being able to drop entire shards once old data expires, instead of running row-by-row DELETEs.

flowchart LR
    subgraph Date range
    S0["Shard 0: January-March"]
    S1["Shard 1: April-June"]
    S2["Shard 2: July-September (active)"]
    end
    Q["Query: July events"] --> S2

Common Mistakes and Best Practices

The most frequent mistake is choosing a shard key that isn’t the one actually used to filter in production. If 90% of queries filter by cliente_id but the data was sharded by fecha, every typical query ends up being a scatter-gather that hits every shard, which cancels out most of the benefit.

The second mistake is underestimating resharding. Adding new shards almost always means moving data between servers while the system keeps receiving traffic. Without a strategy based on virtual shards or consistent hashing, that data movement can take weeks and requires a dual-write window (writing to both the old and the new shard at once) to avoid losing updates during the migration.

The third is cross-shard transactions: updating two rows that live on different shards within a single ACID transaction isn’t natively possible. It requires a two-phase commit protocol (2PC) or redesigning the operation as a saga with compensations, and both add latency and failure surface.

⚠️ Watch out: a join between two tables that live on different shards isn’t resolved by the database engine for you. You need to bring the data to the application layer and join it there, or denormalize ahead of time to avoid the join.

Comparison with Alternatives

Strategy	What It Solves	Advantage	Limitation
Sharding	Data and write volume that exceeds a single server	Scales horizontally with almost no ceiling	Complex cross-shard joins and transactions
Read replicas	Read volume	Simple to set up, no schema changes	Doesn’t help if the bottleneck is writes
Partitioning (single-server)	Large tables on a single server	Improves maintenance and indexing without new infrastructure	Doesn’t solve the server’s CPU or RAM limits
Vertical scaling	Occasional resource shortage	Zero code changes	Physical ceiling and non-linearly growing cost

Going Deeper (Advanced)

When a query needs data from several shards at once (for example, an aggregate report), the usual pattern is scatter-gather: the routing layer sends the same query to every shard in parallel and combines the results in memory before returning them to the client.

sequenceDiagram
    participant App as Application
    participant R as Router
    participant S0 as Shard 0
    participant S1 as Shard 1
    App->>R: aggregate query
    R->>S0: subquery
    R->>S1: subquery
    S0-->>R: partial result
    S1-->>R: partial result
    R-->>App: combined result

Vitess, originally created at YouTube to scale MySQL, implements exactly this routing layer (called VTGate) so the application keeps speaking the standard MySQL protocol without ever knowing how many shards exist underneath. The project also handles live resharding by moving data shard by shard while keeping both copies in sync until the final cutover.

To minimize how much data moves when adding or removing shards, mature systems don’t shard directly across N physical servers: they create many more logical shards (say, 4,096) than there are physical servers, and assign several logical shards to each server. Adding a new server only requires reassigning some logical shards, not recomputing the hash for every individual row.

💭 Key takeaway: the same “adding a node shouldn’t remap everything” problem that consistent hashing solves in caching systems is, at its core, the same problem that resharding with virtual shards solves in a database.

📖 Summary on Telegram: View summary

Your next step: set up a table with the four hash partitions from this article on a local PostgreSQL instance and confirm with EXPLAIN (ANALYZE, BUFFERS) that a query by cliente_id only touches one partition.

Frequently Asked Questions

Are Sharding and Partitioning the Same Thing?

No. Partitioning divides a table into fragments within the same server and database engine. Sharding distributes those fragments across separate physical servers. You can have partitioning without sharding, but sharding almost always uses partitioning as its foundation within each node.

When Should You Use Sharding Instead of Scaling Vertically?

When the data or write volume already exceeds what a single server can reasonably offer, or when the cost of continuing to scale vertically is higher than the operational cost of managing several smaller servers.

How Do Queries That Span Multiple Shards Work?

With a scatter-gather pattern: the routing layer sends the query to every relevant shard in parallel and combines the results. It’s slower and more complex than a single-shard query, so it’s worth designing the shard key to minimize how many queries need it.

What Happens When You Need to Add More Shards (Resharding)?

With a simple modulo over the number of shards, adding a new one forces most of the existing data to move. That’s why production systems use virtual shards or consistent hashing, which only relocate a fraction of the data when scaling.

Hash Sharding or Range Sharding: Which One Should You Choose?

Hash if writes are sequential (auto-incrementing ids, timestamps) and you want to avoid a hot shard. Range if typical queries ask for contiguous ranges (all events from a given week) and you can tolerate or manage the hot shard on the most recent range.

Does Sharding Replace Indexes or Replication?

No. They’re complementary: each shard still needs its own indexes to respond quickly, and usually has its own read replicas for fault tolerance. Sharding solves total volume; indexes and replication solve speed and availability within each shard.

References

• PostgreSQL: Table Partitioning: official documentation on declarative partitioning, including hash mode.
• MongoDB: Sharding: official guide on shard keys, chunks, and automatic balancing.
• Vitess: official site for the MySQL sharding project originally created at YouTube.
• Wikipedia: Shard (database architecture): general overview of the concept and its history.

📱 Enjoying this content? Follow @programacion on Telegram for daily tech content in Spanish: quick summaries, fresh content every day.

Imagen destacada: Foto de Kirill Sh en Unsplash