Partitioning: Vertical and Horizontal Sharding

Why this matters: replication gives you copies; partitioning gives you capacity. It is also the one database decision that is genuinely expensive to reverse, because the partition key determines the physical layout of every row.

Key takeaway

Data partitioning (or sharding) distributes data across multiple nodes, with subsets managed on each. The goal is balanced partitions that efficiently handle increasing query rates and data volume.

Why partition

As data volumes and concurrent traffic grow, single-node databases reach scalability limits and latency and throughput degrade. Traditional databases offer robust features — range queries, secondary indexes, ACID transactions — but maintaining those in a distributed environment is challenging.

At some point a single node becomes the bottleneck. The alternatives all cost something:

Option	Cost
Migrate to NoSQL	Expensive — many codebases are tightly coupled to relational databases
Adopt third-party scaling tools	Adds operational and architectural complexity
Partition the existing database	Optimizes for the specific problem while preserving the relational model

The hotspot problem

Partitioning must be balanced. If one partition receives significantly more data or queries it becomes a hotspot — a single congested node absorbing the majority of traffic, degrading the whole system.

There are two ways to shard.

Vertical sharding

Vertical sharding moves specific tables or columns to different database instances or physical servers. It is often used to separate columns containing large text or binary data (blobs) from the main table to improve retrieval speed.

If an Employee table contains a large photo blob, split it into a lighter Employee table holding metadata and an EmployeePicture table holding the blob. Both retain the primary key EmployeeID so the data can be reconstructed efficiently.

Vertical sharding is often manual and static; horizontal sharding is better suited to automation and dynamic scaling.

Horizontal sharding

Horizontal sharding divides a table row-wise into multiple smaller tables. Each partition is a shard. Two primary strategies:

Key-range-based sharding

Each partition is assigned a continuous range of keys. An Invoice table partitioned on Customer_Id puts specific ID ranges in different partitions.

Shard 1: Customer_Id 1-2      Shard 2: Customer_Id 3-4

When multiple tables are linked by foreign keys, shard them on the same partition key so related data — a Customer and their Orders — stays on the same shard, keeping retrieval local.

Common design techniques for multi-table sharding:

Technique	What it does
Mapping table	A table on each shard mapping partition keys to shards, so applications can route queries efficiently
Replicated partition key	The partition key is included in all related tables, aiding routing and isolation at the cost of storage
Unique primary keys	Keys must be unique across all shards to prevent collisions during migration or analytical merges
Consistency point	A column such as `Creation_date` serves as a merge point for a global view, assuming clocks are synchronized

	Advantages	Disadvantages
Key-range sharding	Range queries are easy — you know exactly which node holds a key range; data stays sorted within partitions	Range queries only work on the partitioning key; uneven key distribution causes hotspots

Hash-based sharding

A hash function determines the partition: compute the hash of the key and take the modulus of the partition count.

partition = hash(key) mod n          where n = number of nodes

With n = 4, keys whose hash mod 4 equals 2 go to node 2, and so on. This randomizes placement to ensure uniform distribution.

	Advantages	Disadvantages
Hash-based sharding	Keys are uniformly distributed across nodes, minimizing hotspots	Range queries are inefficient — keys are scattered randomly across all partitions

The two strategies are exact opposites: key-range preserves locality and risks skew; hashing destroys locality and guarantees spread.

Consistent hashing

Consistent hashing maps both servers and data keys onto positions on an abstract circle, or ring. This allows nodes to scale with minimal data movement, preserving performance.

	Advantages	Disadvantages
Consistent hashing	Facilitates easy horizontal scaling; improves throughput and latency	Random node assignment can lead to non-uniform data distribution

Its disadvantage is real and has a standard fix — virtual nodes, where each physical server owns many small arcs of the ring rather than one large one, which smooths the distribution. The Key-Value Store chapter covers that mechanism in depth.