Partitioning basics
Partitioning splits one huge table into smaller physical pieces that PostgreSQL treats as one table.
In this page:
Syntax
CREATE TABLE parent_table (
id bigint,
created date
) PARTITION BY RANGE (created);
CREATE TABLE partition_name PARTITION OF parent_table
FOR VALUES FROM ('start') TO ('end');Partitioning basics
With declarative partitioning you create a parent table using PARTITION BY RANGE, LIST or HASH and then child partitions that hold slices, such as one per month.
Queries that filter on the partition key touch only the relevant partitions (partition pruning), and old data can be removed by dropping a partition instantly.
Note:
Dropping a partition is much faster than deleting millions of rows.
Example: Partitioning basics
shop=# CREATE TABLE measurements (
shop(# id bigint GENERATED ALWAYS AS IDENTITY,
shop(# taken_at date NOT NULL,
shop(# value numeric
shop(# ) PARTITION BY RANGE (taken_at);
shop=# CREATE TABLE measurements_2024_01 PARTITION OF measurements FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
shop=# CREATE TABLE measurements_2024_02 PARTITION OF measurements FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
shop=# DROP TABLE measurements_2024_01; -- removes a whole month instantly⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Partitioning small tables
- Choosing a partition key that queries do not filter on
- Creating too many tiny partitions
Chapter Summary
- Declarative partitioning by RANGE, LIST or HASH
- Partition pruning skips irrelevant partitions
- Drop old partitions instantly
- Best for very large tables
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: