Home/Learn/System Design/Data Partitioning Strategies

Data Partitioning Strategies

Intermediate

Data Management

Data partitioning divides a dataset into smaller subsets for parallel processing and storage. Horizontal partitioning splits rows; vertical partitioning splits columns. The choice of partition key and strategy determines query performance and scalability.

Overview

Data partitioning distributes data across storage units to improve performance, manageability, and availability. Horizontal partitioning (sharding) places different rows in different partitions based on a key. Vertical partitioning places different columns in different tables or services — separating frequently accessed columns from rarely accessed ones. Functional partitioning groups data by business domain (users in one DB, orders in another). The partition key is the most critical decision: it must distribute data evenly, minimise cross-partition queries, and align with the most common access patterns. Hot partitions (one partition receiving disproportionate traffic) are the most common problem. Strategies to avoid hotspots include composite keys, salting, and write-sharding with read-fan-out.

Horizontal vs Vertical Partitioning

Horizontal partitioning splits rows — each partition has all columns but a subset of rows. Vertical partitioning splits columns — each partition has all rows but a subset of columns. They can be combined.

Conceptual + SQL — partitioning types

// Horizontal partitioning (sharding)
// Partition by user_id range or hash
//
// Partition 1: user_id 1–1M       (all columns)
// Partition 2: user_id 1M+1–2M    (all columns)
// Partition 3: user_id 2M+1–3M    (all columns)

// Vertical partitioning
// Split wide table into narrow, focused tables
//
// users_core:    id, name, email, created_at    (hot, queried often)
// users_profile: id, bio, avatar_url, settings  (cold, queried rarely)
// users_audit:   id, last_login, login_count    (analytics)

// PostgreSQL declarative partitioning
CREATE TABLE orders (
    id         BIGSERIAL,
    user_id    BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total      DECIMAL(10,2)
) 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');

Choosing a Partition Key

A good partition key distributes data evenly and aligns with query patterns. Bad keys create hot partitions. Composite keys can help — e.g., (tenant_id, created_date) distributes within a tenant.

Conceptual — partition key selection

// Good partition keys:
// ✅ user_id — even distribution, queries are per-user
// ✅ tenant_id — multi-tenant SaaS, all tenant data co-located
// ✅ (sensor_id, date) — time-series, avoids single-sensor hotspot

// Bad partition keys:
// ❌ country — 50% of users might be in one country → hot partition
// ❌ status — only a few values (ACTIVE/INACTIVE) → huge partitions
// ❌ created_at alone — all writes go to "current" partition

// Hotspot mitigation: salting
// Problem: celebrity user_id gets 1000x more writes
// Solution: append random suffix to partition key
//   key = user_id + "#" + random(0, 9)
//   Writes spread across 10 partitions
//   Reads fan out to 10 partitions and merge

// DynamoDB: partition key design
// Table: user_activity
// PK: user_id#YYYY-MM-DD  SK: timestamp
// Distributes writes across days, queries scoped to one day

Functional Partitioning

In microservices, each service owns its own database — this is functional partitioning by domain. It provides strong isolation but requires careful handling of cross-service queries.

Conceptual — functional partitioning

// Functional partitioning in microservices
//
// User Service     → users_db (PostgreSQL)
// Order Service    → orders_db (PostgreSQL)
// Product Service  → products_db (MongoDB)
// Analytics Service→ analytics_db (ClickHouse)
//
// Each service owns its data — no shared database
//
// Cross-service queries:
//   Option 1: API composition (Order → User service for name)
//   Option 2: Materialised view via events
//     User updated → event → Order service caches user_name
//   Option 3: CQRS read model (separate query database)
//
// Benefit: services scale independently
// Cost: no cross-service joins, eventual consistency between services

Key Points to Remember

1Horizontal partitioning splits rows; vertical partitioning splits columns; functional partitioning splits by domain.
2The partition key must distribute data evenly and match query access patterns.
3Hot partitions are the #1 problem — use composite keys, salting, or write-sharding to avoid them.
4Time-based partitioning is great for logs and time-series — old partitions can be archived or dropped.
5Microservices naturally use functional partitioning — each service owns its database.

Interview Questions

What is the difference between horizontal and vertical partitioning?

EasyTCS

How do you choose a good partition key?

MediumAmazon

What is a hot partition and how do you mitigate it?

MediumGoogle

How does DynamoDB handle data partitioning?

MediumFlipkart

Design a partitioning strategy for a multi-tenant analytics platform ingesting 1M events/sec.

HardUber

Ask Aria about Data Partitioning Strategies

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

ACID vs BASE

REST vs gRPC