Range Partitioning

Most data in practice has a natural timeline. Orders are placed on specific dates. Logs are written at specific timestamps. Sensor readings arrive in sequence. Financial transactions happen in chronological order.

When you query this data, you almost always filter by time. "Show me last week's orders." "Give me today's logs." "What were the sensor readings between 2 PM and 4 PM?"

Range partitioning exploits this natural ordering. Each partition covers a contiguous range of values, typically dates. January's data in one partition, February's in another. When you query for March data, the database opens only the March partition. The other 11 months are never touched.

This is the most intuitive and most widely used partitioning strategy. If your data has timestamps and your queries filter by time, range partitioning is almost certainly the right choice.

In PostgreSQL:

CREATE TABLE events (
 id UUID DEFAULT gen_random_uuid(),
 event_type VARCHAR(50),
 payload JSONB,
 created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
 FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
 FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE events_2025_03 PARTITION OF events
 FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

The range boundaries are inclusive on the lower end and exclusive on the upper end. A row with created_at = '2025-02-01 00:00:00' goes into events_2025_02, not events_2025_01.

Each partition is a fully independent table:

• Its own heap files on disk
• Its own indexes (you create them per-partition or let PostgreSQL auto-create them)
• Its own VACUUM and ANALYZE schedules
• Its own statistics for the query planner

Partition pruning in action:

EXPLAIN SELECT * FROM events WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';
-- Output shows: Scan on events_2025_03 (other partitions excluded)