Skip to content
All glossary terms

Table partitioning

What is table partitioning in PostgreSQL?

Partitioning splits one logical table into smaller physical tables by range, list or hash, so queries and maintenance touch only the partitions they need.

With declarative partitioning, you create a partitioned table with PARTITION BY and then add partitions that each hold a range of keys, a list of values or a hash bucket. Applications read and write the parent table, and PostgreSQL routes each row to the right partition.

Partition pruning lets the planner skip partitions that cannot match a query's WHERE clause. Partitioning also makes removing old data cheap: dropping or detaching a partition is much faster than removing its rows one by one. The benefits usually show up only for tables that would otherwise be very large.

Example

The example creates monthly range partitions and asks EXPLAIN about rows from November. Only measurements_2026_11 appears in the output, because pruning skipped the October partition.

CREATE TABLE measurements (
  taken_on date NOT NULL,
  value numeric
) PARTITION BY RANGE (taken_on);

CREATE TABLE measurements_2026_10 PARTITION OF measurements
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE measurements_2026_11 PARTITION OF measurements
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

INSERT INTO measurements VALUES ('2026-10-15', 1.5), ('2026-11-02', 2.5);

EXPLAIN SELECT * FROM measurements WHERE taken_on >= '2026-11-01';

Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.

Sources

Official PostgreSQL documentation (in English).

Related terms