Range partitioning by date in PostgreSQL

Introduction

Range partitioning by date helps organize data into smaller parts based on date ranges. This makes searching and managing data faster and easier.

You have a large table with data collected over many years and want to speed up queries for specific time periods.

You want to archive old data separately but still keep it accessible.

You need to improve performance for reports that focus on recent months or years.

You want to manage data storage by splitting it into monthly or yearly parts.

You want to delete old data quickly by dropping partitions instead of running slow delete commands.

Syntax

PostgreSQL

CREATE TABLE parent_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    created_date DATE NOT NULL
) PARTITION BY RANGE (created_date);

CREATE TABLE partition_name PARTITION OF parent_table
    FOR VALUES FROM ('start_date') TO ('end_date');

The parent table holds the structure but no data directly.

Each partition stores rows for a specific date range.

Examples

This creates a parent table 'sales' partitioned by the 'sale_date' column.

PostgreSQL

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

This creates a partition for all sales in the year 2023.

PostgreSQL

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

This creates a partition for all sales in the year 2024.

PostgreSQL

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Sample Program

This example creates a table 'orders' partitioned by year. It inserts two orders, one in 2023 and one in 2024. The SELECT query shows all orders sorted by date.

PostgreSQL

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name TEXT,
    order_date DATE NOT NULL
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

INSERT INTO orders (customer_name, order_date) VALUES
('Alice', '2023-05-10'),
('Bob', '2024-03-15');

SELECT * FROM orders ORDER BY order_date;

Important Notes

Partitions must cover all possible date ranges without overlap.

Queries on the parent table automatically use the correct partitions.

Dropping a partition is faster than deleting many rows.

Summary

Range partitioning by date splits data into parts based on date ranges.

This improves query speed and data management.

Each partition holds data for a specific time period.