Composite Partitioning
What you'll learn: How to layer multiple partitioning strategies together to optimize both data distribution and query performance.
The Core Idea
Composite partitioning (also called multi-level or hybrid partitioning) means applying two or more partitioning strategies in sequence. Instead of choosing just hash-based, range-based, or list-based partitioning, you combine them to get the benefits of each.
The most common pattern is range-then-hash: first partition by a range (like date or geographic region), then further subdivide each range partition using hash partitioning.
Why Combine Strategies?
Each partitioning method has tradeoffs:
- Range partitioning enables efficient range queries but can create hot spots
- Hash partitioning distributes data evenly but makes range queries expensive
- List partitioning groups related data but may have uneven sizes
By compositing them, you balance these concerns.
Real-World Example
Imagine an e-commerce order database:
- First level (Range): Partition by
order_dateinto monthly buckets - Second level (Hash): Within each month, hash by
customer_id
This lets you:
- Query "all orders in March" efficiently (range scan one partition)
- Avoid hot spots from popular customers (hash spreads load within each month)
- Retain temporal locality for archival and analytics
Think of it like organizing a library: first by subject (range/list), then alphabetically by author within each subject (hash).
When to Use Composite Partitioning
- Your workload has both range queries and high-volume point lookups
- Single-strategy partitioning creates data skew or query inefficiency
- You need temporal partitioning (for data lifecycle) plus even distribution
Key Takeaway: Composite partitioning layers multiple strategies to achieve balanced distribution and efficient queries simultaneously, though it adds complexity to routing logic and partition management.