What is Data Partitioning Strategies?

Explore data partitioning strategies including horizontal and vertical partitioning, key-based and range-based sharding, and hot spot mitigation.

Data Partitioning Strategies - System Design Tutorial | TechLead

Data Partitioning Strategies

As data grows beyond what a single database server can handle efficiently, partitioning becomes essential. Data partitioning (or sharding) distributes data across multiple machines, enabling horizontal scaling for both storage and query throughput. Choosing the right partitioning strategy has profound implications for query performance, data distribution, and operational complexity.

Horizontal vs. Vertical Partitioning

These are two fundamentally different approaches to splitting data.

Aspect	Horizontal Partitioning (Sharding)	Vertical Partitioning
What is split	Rows are distributed across partitions	Columns are split into separate tables/services
Schema	Each partition has the same schema	Each partition has a subset of columns
Use case	Large tables with billions of rows	Tables with many columns, some rarely accessed
Example	Users A-M on shard 1, N-Z on shard 2	User profile in one table, user preferences in another
Scalability	Scales to many shards	Limited by number of column groups

Vertical partitioning is often the first step: separate frequently accessed columns from large or rarely used ones. Horizontal partitioning is used when the row count exceeds a single machine's capacity.

Partitioning Criteria

For horizontal partitioning, the key decision is how to assign rows to partitions. There are three primary strategies.

Key-Based (Hash) Partitioning

Apply a hash function to a partition key (e.g., user ID) and use the result to determine which partition stores the data.

// Key-based partitioning
function getPartition(key: string, numPartitions: number): number {
  const hash = hashFunction(key); // e.g., MD5, MurmurHash, CRC32
  return hash % numPartitions;
}

// Example: 4 partitions
// userId "user_123" -> hash -> 2847593 % 4 = 1 -> Partition 1
// userId "user_456" -> hash -> 9182734 % 4 = 2 -> Partition 2

// Problem: Adding a partition reshuffles almost all keys!
// Solution: Consistent hashing (described below)

class ConsistentHash {
  private ring: Map<number, string> = new Map(); // position -> node
  private sortedPositions: number[] = [];
  private virtualNodes: number;

  constructor(nodes: string[], virtualNodes: number = 150) {
    this.virtualNodes = virtualNodes;
    for (const node of nodes) {
      this.addNode(node);
    }
  }

  addNode(node: string): void {
    for (let i = 0; i < this.virtualNodes; i++) {
      const position = this.hash(`${node}:${i}`);
      this.ring.set(position, node);
      this.sortedPositions.push(position);
    }
    this.sortedPositions.sort((a, b) => a - b);
  }

  removeNode(node: string): void {
    for (let i = 0; i < this.virtualNodes; i++) {
      const position = this.hash(`${node}:${i}`);
      this.ring.delete(position);
      this.sortedPositions = this.sortedPositions.filter((p) => p !== position);
    }
  }

  getNode(key: string): string {
    const hash = this.hash(key);
    // Find the first position on the ring >= hash
    const idx = this.sortedPositions.findIndex((p) => p >= hash);
    const position = this.sortedPositions[idx >= 0 ? idx : 0];
    return this.ring.get(position)!;
  }

  private hash(key: string): number {
    // Simplified hash - use MurmurHash in production
    let h = 0;
    for (let i = 0; i < key.length; i++) {
      h = (h * 31 + key.charCodeAt(i)) | 0;
    }
    return Math.abs(h);
  }
}

Range-Based Partitioning

Assign continuous ranges of the partition key to each partition. This is ideal for time-series data or when range queries are common.

// Range-based partitioning
interface RangePartition {
  partitionId: string;
  startKey: string; // inclusive
  endKey: string;   // exclusive
  server: string;
}

const partitions: RangePartition[] = [
  { partitionId: "p1", startKey: "A", endKey: "H", server: "server1" },
  { partitionId: "p2", startKey: "H", endKey: "O", server: "server2" },
  { partitionId: "p3", startKey: "O", endKey: "Z", server: "server3" },
];

// For time-series: partition by date range
const timePartitions: RangePartition[] = [
  { partitionId: "2025-Q1", startKey: "2025-01-01", endKey: "2025-04-01", server: "server1" },
  { partitionId: "2025-Q2", startKey: "2025-04-01", endKey: "2025-07-01", server: "server2" },
  { partitionId: "2025-Q3", startKey: "2025-07-01", endKey: "2025-10-01", server: "server3" },
];

List-Based (Directory) Partitioning

Use a lookup table that maps specific key values to partitions. This is useful when data has natural groupings like country or region.

const regionPartitionMap: Record<string, string> = {
  "US": "partition-americas",
  "CA": "partition-americas",
  "BR": "partition-americas",
  "UK": "partition-europe",
  "DE": "partition-europe",
  "FR": "partition-europe",
  "JP": "partition-asia",
  "KR": "partition-asia",
  "IN": "partition-asia",
};

function getPartitionForUser(countryCode: string): string {
  return regionPartitionMap[countryCode] || "partition-default";
}

Strategy	Best For	Watch Out For
Hash-based	Even distribution, point queries	Range queries require scatter-gather
Range-based	Range queries, time-series data	Hot spots if data is not uniformly distributed
List-based	Natural groupings, data locality	Uneven partition sizes, requires manual management

Partition Rebalancing

As data grows or load changes, partitions need to be rebalanced. This involves moving data between nodes without downtime.

Fixed number of partitions: Create many more partitions than nodes (e.g., 1000 partitions, 10 nodes). Assign ~100 partitions per node. When adding a node, move some partitions to it. Used by Riak, Elasticsearch, and Couchbase.
Dynamic partitioning: Split partitions when they grow too large, merge when too small. Used by HBase and MongoDB.
Proportional partitioning: Each node maintains a fixed number of partitions. When a new node joins, it splits some existing partitions and takes half. Used by Cassandra.

Rebalancing Safety Rule

Never let rebalancing happen automatically without operator confirmation in production. Automatic rebalancing during a network partition or node failure can cascade into a larger outage. Most mature systems require manual operator approval for rebalancing operations.

Hot Spots and Mitigation

Hot spots occur when one partition receives disproportionately more traffic than others. Common causes include celebrity users, viral content, and time-based keys where all writes go to the current time partition.

// Hot spot mitigation: Add random suffix to hot keys
function mitigateHotKey(key: string, isHot: boolean): string {
  if (!isHot) return key;
  // Spread hot key across N sub-partitions
  const suffix = Math.floor(Math.random() * 100);
  return `${key}_${suffix}`;
}

// Reading requires scatter-gather across all suffixed keys
async function readHotKey(baseKey: string): Promise<number> {
  const promises = Array.from({ length: 100 }, (_, i) =>
    db.get(`${baseKey}_${i}`)
  );
  const values = await Promise.all(promises);
  return values.reduce((sum, v) => sum + (v || 0), 0);
}

// Alternative: Use a two-level key
// Level 1: Coarse partition (by user_id)
// Level 2: Fine partition (by timestamp or random)
// This keeps related data on the same shard while spreading load

Partitioning in Practice

PostgreSQL (Declarative Partitioning)

// PostgreSQL supports RANGE, LIST, and HASH partitioning natively
// SQL:
// CREATE TABLE orders (
//   id BIGSERIAL,
//   created_at TIMESTAMP NOT NULL,
//   customer_id INT,
//   total DECIMAL
// ) PARTITION BY RANGE (created_at);
//
// CREATE TABLE orders_2025_q1 PARTITION OF orders
//   FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
// CREATE TABLE orders_2025_q2 PARTITION OF orders
//   FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

// PostgreSQL automatically routes queries to the correct partition
// SELECT * FROM orders WHERE created_at = '2025-03-15';
// Only scans orders_2025_q1

MongoDB (Sharding)

// MongoDB sharding with hashed shard key
// db.adminCommand({
//   shardCollection: "mydb.users",
//   key: { userId: "hashed" }
// });

// Range-based shard key for time-series
// db.adminCommand({
//   shardCollection: "mydb.events",
//   key: { timestamp: 1 }
// });

// Compound shard key for better distribution
// db.adminCommand({
//   shardCollection: "mydb.messages",
//   key: { channelId: 1, timestamp: 1 }
// });

Cassandra (Partition Key + Clustering Key)

// Cassandra uses a partition key (hashed) + clustering key (sorted within partition)
// CREATE TABLE messages (
//   channel_id UUID,       -- partition key: determines which node
//   message_id TIMEUUID,   -- clustering key: sorted within partition
//   user_id UUID,
//   content TEXT,
//   PRIMARY KEY (channel_id, message_id)
// ) WITH CLUSTERING ORDER BY (message_id DESC);

// This design:
// - Distributes channels across nodes (hash of channel_id)
// - Stores messages within a channel sorted by time
// - Supports efficient range queries within a channel

Best Practices

Choose the partition key carefully. It should distribute data evenly and align with your most common query patterns
Avoid cross-partition queries. Design your schema so that common queries only hit a single partition
Plan for growth. Use consistent hashing or a large fixed number of partitions to avoid painful rebalancing
Monitor partition sizes. Alert when any partition is 3x larger than the average
Consider composite keys. Combining a well-distributed key with a range key gives you both even distribution and efficient range queries
Start without sharding. A single well-tuned PostgreSQL instance can handle millions of rows. Only shard when you have a clear need

Data Partitioning Strategies

Data Partitioning Strategies

Horizontal vs. Vertical Partitioning

Partitioning Criteria

Key-Based (Hash) Partitioning

Range-Based Partitioning

List-Based (Directory) Partitioning

Partition Rebalancing

Rebalancing Safety Rule

Hot Spots and Mitigation

Partitioning in Practice

PostgreSQL (Declarative Partitioning)

MongoDB (Sharding)

Cassandra (Partition Key + Clustering Key)

Best Practices

Continue Learning

Software Architecture

Cloud & Kubernetes

Performance Engineering

Data Engineering

Engineering Leadership