丁久

Posted on May 16 • Originally published at dingjiu1989-hue.github.io

Database Table Partitioning: Range, List, Hash

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Database Table Partitioning: Range, List, Hash

Partitioning splits a large logical table into smaller physical pieces called partitions. Each partition holds a subset of rows based on a partition key. PostgreSQL has supported declarative partitioning since version 10, with major improvements in subsequent releases.

Why Partition?

Tables exceeding hundreds of gigabytes benefit from partitioning for several reasons:

Query performance : The query planner can skip irrelevant partitions (partition pruning), dramatically reducing the amount of data scanned.
Maintenance speed : Operations like VACUUM, REINDEX, and CLUSTER can target individual partitions instead of the entire table.
Bulk deletes : Dropping an entire partition is far faster than DELETE FROM large_table WHERE ... because it bypasses the need to vacuum dead tuples.
Archival : Older partitions can be detached and moved to cheaper storage without affecting access to recent data.

Partitioning Methods

PostgreSQL supports three built-in partitioning methods: range, list, and hash.

Range Partitioning

Range partitioning divides data by contiguous ranges of the partition key. It is ideal for time-series data, log tables, and date-range datasets.

CREATE TABLE orders (

id BIGSERIAL,

order_date DATE NOT NULL,

customer_id INTEGER,

total NUMERIC(10,2)

) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2025_q1 PARTITION OF orders

FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders

FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

CREATE TABLE orders_2025_q3 PARTITION OF orders

FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

The planner prunes partitions when the query includes a filter on order_date:

EXPLAIN SELECT * FROM orders WHERE order_date = '2025-05-15';

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Only scans orders_2025_q2

List Partitioning

List partitioning assigns rows to partitions based on a discrete set of values. This works well for categorical data such as region, status, or department.

CREATE TABLE events (

id BIGSERIAL,

event_type TEXT NOT NULL,

payload JSONB,

created_at TIMESTAMPTZ DEFAULT NOW()

) PARTITION BY LIST (event_type);

CREATE TABLE events_pageview PARTITION OF events

FOR VALUES IN ('pageview');

CREATE TABLE events_click PARTITION OF events

FOR VALUES IN ('click', 'dblclick');

CREATE TABLE events_custom PARTITION OF events

FOR VALUES IN ('custom');

Queries filtering on event_type prune to the matching partition:

EXPLAIN SELECT * FROM events WHERE event_type = 'click';

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Only scans events_click

Hash Partitioning

Hash partitioning distributes rows evenly across a fixed number of partitions using a hash function on the partition key. It is useful when there is no natural partitioning column and you want uniform data distribution for parallel I/O.

CREATE TABLE sessions (

session_id UUID NOT NULL,

user_id INTEGER,

payload JSONB,

started_at TIMESTAMPTZ

) PARTITION BY HASH (session_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);

Hash partitioning ensures roughly equal row counts across partitions. It does not support partition pruning on range queries, but it does prune when the partition key is filtered by equality.

Partition Pruning

Partition pruning is the query planner's ability to skip partitions that cannot contain matching rows. This optimization applies at execution time and, since PostgreSQL 11, also at plan time.

EXPLAIN (ANALYZE, BUFFERS)

SELE

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Database Table Partitioning: Range, List, Hash

Database Table Partitioning: Range, List, Hash