We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design Masterclass · Database Fundamentals
Free preview: the first 2 parts of this 10-part lesson.
Already bought the course? Sign in to read the whole lesson.
this lesson is part of
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design courseread the rest of this lesson in
System Design Masterclass
Unlock this lesson and the 751 other paid lessons
$25 once, by PayPal
See the System Design coursein Database Fundamentals
This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.
One-time payment by card or PayPal
Get Lifetime AccessOne-time payment: UPI, card or netbanking
Get Lifetime Access18 Foundation lessons are free, no payment needed. Try them now
It is easy and good
“It is really nice and good Please join the course”
You run a global SaaS platform. Your events table has 10 billion rows, growing by 50 million per day. You've already partitioned by month (range partitioning). But each monthly partition still has 1.5 billion rows, far too large for efficient queries and maintenance.
You could switch to daily partitions, but then you'd have 365 partitions per year. After 3 years, that's over 1,000 partitions, and the query planner starts struggling with that many.
What if you could partition by month first, then sub-partition each month by something else, like a hash of the user ID? Now each monthly partition is split into 16 sub-partitions of ~94 million rows each. That's manageable. Your monthly range queries still prune to the right month, and within that month, the data is evenly distributed.
This is composite partitioning (also called multi-level or sub-partitioning), applying two partitioning strategies in sequence. The first level creates partitions. The second level sub-partitions each partition.
In PostgreSQL, composite partitioning is implemented by partitioning a partition:
-- Level 1: Partition by RANGE (month)
CREATE TABLE events (
id UUID DEFAULT gen_random_uuid(),
user_id UUID NOT NULL,
event_type VARCHAR(50),
payload JSONB,
created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
-- Create monthly partition, itself partitioned by HASH
CREATE TABLE events_2025_03 PARTITION OF events
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01')
PARTITION BY HASH (user_id);
-- Sub-partitions for March 2025
CREATE TABLE events_2025_03_p0 PARTITION OF events_2025_03
FOR VALUES WITH (MODULUS 16, REMAINDER 0);
CREATE TABLE events_2025_03_p1 PARTITION OF events_2025_03
FOR VALUES WITH (MODULUS 16, REMAINDER 1);
-- ... Up to p15
The result is a tree structure:
events (root)
├── events_2025_01 (range: January)
│ ├── events_2025_01_p0 (hash: 0)
│ ├── events_2025_01_p1 (hash: 1)
│ └── ... (16 sub-partitions)
├── events_2025_02 (range: February)
│ ├── events_2025_02_p0
│ └── ...
└── events_2025_03 (range: March)
├── events_2025_03_p0
└── ...
Query pruning works at both levels:
-- Prunes to March (range) + one sub-partition (hash on user_id)
SELECT * FROM events WHERE created_at >= '2025-03-01' AND user_id = 'abc123';
-- Prunes to March (range), scans all 16 hash sub-partitions
SELECT * FROM events WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';
-- No range pruning, but prunes to 1 hash sub-partition per month
SELECT * FROM events WHERE user_id = 'abc123';