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, List and Hash Partitioning is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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.

List Partitioning

Each partition holds specific values:

CREATE TABLE customers (
  id BIGSERIAL,
  country TEXT NOT NULL,
  name TEXT
) PARTITION BY LIST (country);

CREATE TABLE customers_us PARTITION OF customers FOR VALUES IN ('US');
CREATE TABLE customers_eu PARTITION OF customers FOR VALUES IN ('DE','FR','IT','ES','NL');
CREATE TABLE customers_other PARTITION OF customers DEFAULT;

DEFAULT Partition

Catches rows that match no other partition. Without one, INSERTs of non-matching rows fail.

Hash Partitioning

Rows distributed by hashing the partition key. Good when you want even distribution but have no natural range/list:

CREATE TABLE sessions (
  id UUID,
  user_id BIGINT,
  data JSONB
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Picking a Strategy

Range — time-series, monotonically growing IDs
List — tenants, countries, hot tiers
Hash — when no natural skew, just want parallelism

Sub-Partitioning

Partitions can themselves be partitioned (multi-level):

CREATE TABLE events_2024 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
  PARTITION BY HASH (user_id);
-- 2024 is a yearly range partition, internally hashed by user_id.

Indexes on Partitioned Tables

Indexes on the parent automatically propagate to all partitions. UNIQUE constraints must include the partition key:

CREATE INDEX events_user_idx ON events (user_id);
-- Creates one index per partition automatically.

ALTER TABLE events ADD CONSTRAINT events_uniq UNIQUE (id, ts);
-- ts must be in the constraint because it's the partition key.

Primary Keys and Partitions

The PRIMARY KEY of a partitioned table must include the partition column. This is a fundamental constraint of declarative partitioning.

Adding and Dropping Partitions

Cheap operations:

CREATE TABLE events_2024_q3 PARTITION OF events
  FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

DROP TABLE events_2023_q1;
-- Instant; equivalent to deleting all old data.

Automatic Partition Creation

Postgres doesn't auto-create future partitions. Common solutions:

pg_partman extension — managed monthly partitions
Scheduled job that CREATEs next partition

Foreign Keys

FKs to a partitioned table need PG 12+. FKs FROM a partitioned table to a normal table work normally.

Recap

Three partitioning strategies cover most cases.

Range for time/sequential
List for discrete sets
Hash for even distribution
PK must include partition column

Quick Check

You want to partition a sessions table by user_id with even distribution and no natural ordering. Which strategy?

Frequently asked questions

Is the “Range, List and Hash Partitioning” lesson free?

Yes — the full text of “Range, List and Hash Partitioning” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Range, List and Hash Partitioning”?

Pick the right partitioning strategy: range for time-series, list for tenants, hash for even distribution. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Range, List and Hash Partitioning” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy