DesignFlix 8: Partitioning and Sharding Depend on the Queries

A table gets large, queries get slower, and somebody suggests sharding. Before splitting data across servers, ask which query is struggling and what part of the workload needs more capacity.

Partitioning and sharding both divide data, but that doesn't make them the same operational decision. A monthly report and a customer's order history may benefit from different divisions.

Let's compare those two queries, starting inside one database before moving across database servers.

Partition a table around a useful rule

In this example, partitioning means dividing a table within one database deployment. We can group rows by ranges, lists or a hash rule, depending on the database's features.

Suppose an orders table holds several years of data, while most reports examine one month. A range partition by creation date lets the database work with the relevant date ranges instead of treating every row as one undivided collection.

A small PostgreSQL example creates a July partition:

CREATE TABLE orders (
  order_id BIGINT,
  customer_id BIGINT,
  created_at DATE
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_07 PARTITION OF orders
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

The upper bound is exclusive. A July query can use the partition key:

SELECT order_id FROM orders
WHERE created_at >= DATE '2026-07-01'
  AND created_at < DATE '2026-08-01';

PostgreSQL's partitioning guide explains partition pruning: excluding partitions that cannot contain matching rows. Check the actual query plan rather than assuming every query automatically touches one partition.

The wrong query can still visit every partition

Now the page asks for every order belonging to customer 42, without a date range. Monthly partitions don't tell us which month contains that customer's orders.

The database may need to consider many partitions. Appropriate indexes can help within them, but the date split doesn't magically become a customer lookup.

Partitioning also helps with maintenance, such as removing an old date range under a retention policy. It doesn't automatically increase the CPU or storage capacity of a single server. Match the benefit to the problem you're solving.

Shard by the route most requests take

Sharding commonly means horizontal partitions hosted across database servers. Each shard owns a subset of rows, and requests need a way to find the correct owner.

If most requests ask for one customer's orders, a customer-based shard key may keep that work local. A deliberately simple teaching rule could be:

def shard_for(customer_id):
    return customer_id % 4

assert shard_for(42) == 2
assert shard_for(43) == 3

This routes an ID to one of four numbered shards. It illustrates routing, not a complete sharding strategy. Production designs may use stable virtual partitions and a mapping layer so capacity changes don't require such a simple rule to be rewritten everywhere.

Notice that replication is a separate choice. A shard can have replicas of its own. Splitting rows across four owners doesn't automatically give every row four copies.

Choose your key wisely.

A global report crosses the new boundary

The customer page now has a clear route. The July sales report may need data from every shard, because each contains orders from some customers during July.

We can query several shards and combine results, or maintain a separate reporting view. Either way, the cross-shard work still exists. Transactions and joins that span owners may also become more complicated under the chosen database's capabilities.

Which query becomes local after this split, and which one now has to cross it?

A useful key makes common operations easier without making the important remaining operations unmanageable. Write down both before adopting it.

Distribution needs to stay balanced

Regional sharding might sound natural for an Indian retail app: one owner for Mumbai-area customers, another for Delhi-area customers. It can also leave one shard much busier than another if traffic or customer counts differ sharply.

A hash rule can spread many customers, but one unusually large customer may still create a hotspot. Date-based routing can put most new writes in the latest range. Measure distribution of work, not only distribution of row counts.

Adding capacity requires a migration plan: move data, update ownership and route reads and writes correctly while the move happens. With the simple modulo example, changing four to five reroutes many existing IDs. The old data doesn't move because a function changed.

Choose the split from the workload

For our monthly report, table partitioning may improve pruning and maintenance inside the current deployment. For customer-local traffic that exceeds one server's capacity, sharding may provide a useful way to distribute the workload.

Product terminology varies, so describe the actual arrangement: where rows live, who owns them and how requests are routed. Then test the customer page, global report, hottest key and migration path. A split is successful when those operations fit the new layout, not simply when each table contains fewer rows.