Range Partitioning in PostgreSQL: What It Is and How It Works

In PostgreSQL, range partitioning is a method to split a large table into smaller pieces called partitions based on ranges of values in a column. Each partition holds rows where the column's values fall within a specific range, making data management and queries faster and more efficient.

How It Works

Range partitioning in PostgreSQL divides a big table into smaller parts, each storing rows with values in a certain range. Imagine a library where books are sorted by publication year: all books from 2000 to 2009 go on one shelf, 2010 to 2019 on another, and so on. This way, when you look for books from a specific decade, you only check the relevant shelf instead of the entire library.

In PostgreSQL, you define a range partition on a column like a date or number. The database automatically routes new rows to the correct partition based on the value in that column. This helps speed up queries because PostgreSQL can skip partitions that don't match the query range, reducing the amount of data it scans.

Example

This example shows how to create a table partitioned by range on a sales_date column, splitting data by year ranges.

sql

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sales_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sales_date);

CREATE TABLE sales_2020 PARTITION OF sales
  FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

CREATE TABLE sales_2021 PARTITION OF sales
  FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

INSERT INTO sales (sales_date, amount) VALUES
  ('2020-05-15', 100),
  ('2021-07-20', 200);

SELECT * FROM sales WHERE sales_date >= '2021-01-01';

Output

 id | sales_date | amount 
----+------------+--------
  2 | 2021-07-20 |    200
(1 row)

When to Use

Use range partitioning when your data naturally divides into continuous ranges, such as dates, numbers, or timestamps. It is especially helpful for very large tables where queries often filter by these ranges.

For example, a sales database can use range partitioning by year or month to speed up reports for specific periods. It also helps with maintenance tasks like archiving old data or dropping partitions without affecting the whole table.

Key Points

• Range partitioning splits data by continuous value ranges in a column.
• It improves query speed by scanning only relevant partitions.
• Partitions are defined with FOR VALUES FROM ... TO ... clauses.
• Ideal for time-series data or numeric ranges.
• Makes data management and maintenance easier.

Key Takeaways

Range partitioning divides a table into parts based on value ranges in a column.

It speeds up queries by limiting data scanned to relevant partitions.

Best used for data with natural ranges like dates or numbers.

Partitions can be managed independently for easier maintenance.

PostgreSQL routes rows automatically to the correct partition.