0PricingLogin▼
SQL Academy · Lesson

Range, List and Hash Partitioning

Pick the right partitioning strategy: range for time-series, list for tenants, hash for even distribution.

Range Partitioning

Each partition covers a continuous range of values. Most common for time-series:

CREATE TABLE events (
  id BIGSERIAL,
  ts TIMESTAMPTZ NOT NULL,
  data JSONB
) PARTITION BY RANGE (ts);

CREATE TABLE events_2024_q1 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE events_2024_q2 PARTITION OF events
  FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

Range Bounds Are Half-Open

FROM is inclusive, TO is exclusive. Boundaries align cleanly — no overlap, no gaps.

All lessons in this course

  1. Why Partition: Pruning, Maintenance
  2. Range, List and Hash Partitioning
  3. Detaching and Attaching Partitions
  4. Querying Across Partitions Efficiently
← Back to SQL Academy