Eric Campbell 6 min read Engineering

Data Partitioning Strategies for Big Data

Data partitioning is the practice of dividing large datasets into smaller, more manageable pieces called partitions. Each partition contains a subset of the data and can be stored, queried, and...

Key Insights

  • Partitioning strategy should be driven by your query patterns, not your data structure—choose partition keys based on how you read data, not how you write it.
  • Hot partitions kill distributed systems faster than almost any other problem; design for even distribution from day one, because retrofitting partition schemes is painful and expensive.
  • The best partitioning strategy is often a composite approach—combining time-based range partitioning with hash partitioning handles both analytical queries and high-throughput writes effectively.

Introduction to Data Partitioning

Data partitioning is the practice of dividing large datasets into smaller, more manageable pieces called partitions. Each partition contains a subset of the data and can be stored, queried, and processed independently. At scale, partitioning isn’t optional—it’s the difference between queries that complete in seconds and queries that time out.

The benefits are straightforward: query performance improves because you scan less data, parallel processing becomes possible because workers can operate on different partitions simultaneously, and maintenance operations like backups and schema changes become manageable when you can operate on partitions independently.

But partitioning adds complexity. You need to choose partition keys, handle cross-partition queries, manage partition metadata, and deal with rebalancing as data grows. For datasets under a few hundred gigabytes on modern hardware, partitioning often creates more problems than it solves. Start simple, measure, and partition when you have evidence you need it.

Horizontal vs. Vertical Partitioning

Horizontal partitioning (sharding) splits data by rows. Each partition contains complete records, but only a subset of them. A user table partitioned by user ID would store users 1-1000 in partition A and users 1001-2000 in partition B.

Vertical partitioning splits data by columns. Each partition contains all records, but only a subset of columns. This approach works well when you have wide tables with columns that are accessed together in predictable patterns.

-- Original wide user table
CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255),
    password_hash VARCHAR(255),
    full_name VARCHAR(255),
    bio TEXT,
    avatar_url VARCHAR(500),
    preferences JSONB,
    created_at TIMESTAMP,
    last_login TIMESTAMP,
    login_count INT
);

-- Vertical partitioning: separate by access pattern
CREATE TABLE user_auth (
    user_id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    last_login TIMESTAMP,
    login_count INT
);

CREATE TABLE user_profile (
    user_id BIGINT PRIMARY KEY,
    full_name VARCHAR(255),
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMP
);

CREATE TABLE user_settings (
    user_id BIGINT PRIMARY KEY,
    preferences JSONB
);

Authentication checks hit user_auth without loading profile data. Profile pages query user_profile without touching sensitive auth fields. This reduces I/O, improves cache efficiency, and allows different tables to have different replication or security policies.

Horizontal partitioning scales write throughput by distributing writes across nodes. Vertical partitioning scales read performance by reducing the data loaded per query. Most large systems use both.

Common Partitioning Strategies

Range partitioning assigns rows to partitions based on value ranges. Time-series data almost always uses range partitioning by timestamp—queries naturally filter by time windows, and old partitions can be archived or dropped efficiently.

Hash partitioning applies a hash function to the partition key and assigns rows to partitions based on the hash value. This distributes data evenly but makes range queries expensive since they must scan all partitions.

List partitioning assigns rows to partitions based on explicit value lists. Geographic data might use list partitioning by region code, placing all US data in one partition and EU data in another.

Composite partitioning combines strategies. The most common pattern is range-hash: first partition by time range, then hash within each time partition.

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, year, month, hash, abs

spark = SparkSession.builder.appName("PartitioningExample").getOrCreate()

# Load raw event data
events = spark.read.parquet("s3://data-lake/raw/events/")

# Composite partitioning: range by date, hash by user for even distribution
partitioned_events = events.withColumn(
    "year", year(col("event_timestamp"))
).withColumn(
    "month", month(col("event_timestamp"))
).withColumn(
    "user_bucket", abs(hash(col("user_id"))) % 64  # 64 hash buckets
)

# Write with composite partition scheme
partitioned_events.write \
    .partitionBy("year", "month", "user_bucket") \
    .parquet("s3://data-lake/partitioned/events/")

# Query with partition pruning - only scans relevant partitions
spark.read.parquet("s3://data-lake/partitioned/events/") \
    .filter((col("year") == 2024) & (col("month") == 6)) \
    .filter(col("user_bucket") == abs(hash(lit("user-123"))) % 64)

The year/month partitioning enables efficient time-range queries. The user bucket ensures no single partition becomes a hot spot from high-activity users.

Partitioning in Distributed Systems

Distributed databases make partitioning explicit in their data models. In DynamoDB, every table has a partition key that determines which node stores each item. Choose it wrong, and you’ll hit throughput limits on hot partitions while other partitions sit idle.

import boto3

dynamodb = boto3.resource('dynamodb')

# Anti-pattern: Using date as partition key for time-series data
# All writes for "today" hit the same partition
bad_table = dynamodb.create_table(
    TableName='events_bad',
    KeySchema=[
        {'AttributeName': 'date', 'KeyType': 'HASH'},      # Partition key
        {'AttributeName': 'event_id', 'KeyType': 'RANGE'}  # Sort key
    ],
    AttributeDefinitions=[
        {'AttributeName': 'date', 'AttributeType': 'S'},
        {'AttributeName': 'event_id', 'AttributeType': 'S'}
    ],
    BillingMode='PAY_PER_REQUEST'
)

# Better: Composite partition key spreads load across partitions
# Shard suffix distributes writes while preserving query ability
good_table = dynamodb.create_table(
    TableName='events_good',
    KeySchema=[
        {'AttributeName': 'pk', 'KeyType': 'HASH'},        # "EVENT#2024-06-15#3"
        {'AttributeName': 'sk', 'KeyType': 'RANGE'}        # Sort key for ordering
    ],
    AttributeDefinitions=[
        {'AttributeName': 'pk', 'AttributeType': 'S'},
        {'AttributeName': 'sk', 'AttributeType': 'S'}
    ],
    BillingMode='PAY_PER_REQUEST'
)

# Write with sharding (application generates shard 0-9)
def write_event(event):
    shard = hash(event['event_id']) % 10
    pk = f"EVENT#{event['date']}#{shard}"
    table.put_item(Item={
        'pk': pk,
        'sk': event['timestamp'],
        **event
    })

# Query requires scatter-gather across shards
def query_events_by_date(date):
    results = []
    for shard in range(10):
        pk = f"EVENT#{date}#{shard}"
        response = table.query(KeyConditionExpression=Key('pk').eq(pk))
        results.extend(response['Items'])
    return results

The trade-off is clear: better write distribution requires more complex read patterns. This is acceptable when write throughput is the bottleneck.

Consistent hashing helps distributed systems rebalance partitions when nodes are added or removed. Instead of remapping all data, only partitions adjacent to the changed node move. Kafka, Cassandra, and DynamoDB all use variations of this technique.

Query Optimization with Partitions

Partition pruning is the primary performance benefit of partitioning. When your query predicate matches the partition key, the query planner eliminates irrelevant partitions before scanning any data.

# Create a partitioned table in Spark SQL
spark.sql("""
    CREATE TABLE events_partitioned (
        event_id STRING,
        user_id STRING,
        event_type STRING,
        payload STRING,
        event_date DATE
    )
    USING PARQUET
    PARTITIONED BY (event_date)
""")

# Query with partition pruning - EXPLAIN shows partition filtering
spark.sql("""
    EXPLAIN EXTENDED
    SELECT * FROM events_partitioned
    WHERE event_date = '2024-06-15'
    AND event_type = 'purchase'
""").show(truncate=False)

# Output shows: PartitionFilters: [isnotnull(event_date), (event_date = 2024-06-15)]
# Only the 2024-06-15 partition is scanned

# Anti-pattern: Function on partition column defeats pruning
spark.sql("""
    EXPLAIN EXTENDED
    SELECT * FROM events_partitioned
    WHERE YEAR(event_date) = 2024 AND MONTH(event_date) = 6
""").show(truncate=False)

# Output shows: PartitionFilters: []
# ALL partitions are scanned because the function prevents pruning

Common anti-patterns that bypass partition benefits: applying functions to partition columns, using OR conditions across partition values, and joining on non-partition columns when partition-aware joins are possible.

Rebalancing and Maintenance

Data growth and access pattern changes eventually require repartitioning. Plan for this from the start—retrofitting partition schemes on live systems is one of the most painful operations in data engineering.

#!/bin/bash
# Kafka partition reassignment for load balancing

# Generate reassignment plan
cat > reassignment.json << 'EOF'
{
  "version": 1,
  "partitions": [
    {"topic": "events", "partition": 0, "replicas": [1, 2, 3]},
    {"topic": "events", "partition": 1, "replicas": [2, 3, 1]},
    {"topic": "events", "partition": 2, "replicas": [3, 1, 2]},
    {"topic": "events", "partition": 3, "replicas": [1, 3, 2]},
    {"topic": "events", "partition": 4, "replicas": [2, 1, 3]},
    {"topic": "events", "partition": 5, "replicas": [3, 2, 1]}
  ]
}
EOF

# Execute reassignment with throttling to limit impact
kafka-reassign-partitions.sh \
  --bootstrap-server localhost:9092 \
  --reassignment-json-file reassignment.json \
  --throttle 50000000 \
  --execute

# Monitor progress
kafka-reassign-partitions.sh \
  --bootstrap-server localhost:9092 \
  --reassignment-json-file reassignment.json \
  --verify

Monitor partition skew continuously. A partition receiving 10x the traffic of others will become your bottleneck regardless of total cluster capacity. Track partition sizes, request rates, and latency percentiles per partition.

Choosing the Right Strategy

Start with your access patterns, not your data model. Ask these questions:

  1. What are your most common query predicates?
  2. Do you need range scans or point lookups?
  3. What’s your write-to-read ratio?
  4. How does data volume grow over time?

For time-series analytics, use range partitioning by time with sufficient granularity. For high-throughput transactional systems, use hash partitioning on a high-cardinality key. For multi-tenant applications, consider list partitioning by tenant with hash sub-partitioning.

Best practices checklist:

  • Choose partition keys based on query patterns, not write patterns
  • Ensure partition keys have high cardinality to avoid hot spots
  • Test partition pruning with EXPLAIN before production deployment
  • Monitor partition skew metrics from day one
  • Design partition schemes that allow online repartitioning
  • Document your partitioning strategy—future you will thank present you

Partitioning done right is invisible to application developers and dramatically improves system performance. Partitioning done wrong creates operational nightmares that compound over time. Invest the time upfront to get it right.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.