Partitioning — Animate | AI Wisdom

← Back to topic
🎞 ANIMATE · Mode 3 of 5 · +100 XP— Database Partitioning
📋Range
⚙️Partitions
🔄Pruning
✅Hash
🎯 Next: Test →
Step 1 / 4
Speed
Range Partitioning
PARTITION BY RANGE (created_at) splits table into monthly chunks💡 Analogy
Like organizing a filing cabinet by year — you only open the 2024 drawer to find 2024 files.
Range partitioning splits a table into child tables based on a column range. Queries for a time range only scan relevant partitions.
Console Output
> logs table: PARTITION BY RANGE (created_at)
> Parent table: virtual — no data stored here
> Monthly partitions store the actual rows
●partitioning.sql
Range PartitioningL1–7
1-- Range partitioning: logs table partitioned by month
2CREATE TABLE logs (
3    id         BIGSERIAL,
4    created_at TIMESTAMPTZ NOT NULL,
5    level      VARCHAR(10)  NOT NULL,
6    message    TEXT         NOT NULL
7) PARTITION BY RANGE (created_at);
8 
9-- Create monthly partitions
10CREATE TABLE logs_2024_01
11    PARTITION OF logs
12    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
13 
14CREATE TABLE logs_2024_02
15    PARTITION OF logs
16    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
17 
18-- Indexes on partitions (automatically covers all partitions)
19CREATE INDEX idx_logs_level_created ON logs (level, created_at DESC);
20 
21-- Query automatically hits only relevant partition(s)
22EXPLAIN SELECT * FROM logs
23WHERE created_at >= '2024-01-15'
24  AND created_at <  '2024-02-01'
25  AND level = 'ERROR';
26-- → Partition pruning: only scans logs_2024_01
27 
28-- Hash partitioning: spread users across N shards
29CREATE TABLE users (
30    id    UUID NOT NULL,
31    email VARCHAR(255) NOT NULL
32) PARTITION BY HASH (id);
33 
34CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
35CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
36CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
37CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);
37 lines
Timeline — 4 Steps
Range Partitioning
PARTITION BY RANGE (created_at) splits table into monthly chunks💡 Analogy
Like organizing a filing cabinet by year — you only open the 2024 drawer to find 2024 files.
Range partitioning splits a table into child tables based on a column range. Queries for a time range only scan relevant partitions.
Console Output
> logs table: PARTITION BY RANGE (created_at)
> Parent table: virtual — no data stored here
> Monthly partitions store the actual rows

Was this page helpful?

Discussion

PARTITION BY RANGE (created_at) splits table into monthly chunks

💡 Analogy

Like organizing a filing cabinet by year — you only open the 2024 drawer to find 2024 files.

Range partitioning splits a table into child tables based on a column range. Queries for a time range only scan relevant partitions.

Console Output

> logs table: PARTITION BY RANGE (created_at)
> Parent table: virtual — no data stored here
> Monthly partitions store the actual rows

PARTITION BY RANGE (created_at) splits table into monthly chunks

💡 Analogy

Like organizing a filing cabinet by year — you only open the 2024 drawer to find 2024 files.

Range partitioning splits a table into child tables based on a column range. Queries for a time range only scan relevant partitions.

Console Output

> logs table: PARTITION BY RANGE (created_at)
> Parent table: virtual — no data stored here
> Monthly partitions store the actual rows