Partitioning commands and example in PostgreSQL

PostgreSQL uses Declarative Partitioning, which means you define a main (parent) table, and then create smaller tables (partitions) that inherit its structure. The database handles routing the data automatically.

Here are the commands to create a partitioned table based on a date range (a common technique):

---

Step 1: Create the Parent Partitioned Table

First, you create the main table, specifying the partitioning method and the key (the column) it will use. We'll use the RANGE method on the order_date.

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount NUMERIC(10, 2)
) PARTITION BY RANGE (order_date);

Component	Explanation
CREATE TABLE orders (...)	Defines the overall table structure.
PARTITION BY RANGE (order_date)	This is the crucial part. It tells PostgreSQL this is a partitioned table and will be split based on the range of the order_date column.

---

Step 2: Create the Partitions (Child Tables)

Next, you create the individual physical tables that will hold the data. Each child table must specify the exact range of values it will accept for the partition key.

Partition for January 2025

This partition will hold all orders from the start of January up to, but not including, February 1st.

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

Partition for February 2025

CREATE TABLE orders_2025_february
    PARTITION OF orders FOR VALUES
    FROM ('2025-02-01') TO ('2025-03-01');

Partition for Future Data

You should always create a partition for future data or for any data that falls outside your current defined ranges.

CREATE TABLE orders_future
    PARTITION OF orders FOR VALUES
    FROM ('2025-03-01') TO (MAXVALUE);

---

Step 3: Insert and Query Data

Once the structure is set up, you treat the parent table (orders) like any other table. PostgreSQL automatically routes the data to the correct child table.

Inserting Data

You insert data directly into the parent table. The database figures out where it belongs.

-- This row goes into orders_2025_january
INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES (101, 5001, '2025-01-15', 49.99);

-- This row goes into orders_2025_february
INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES (102, 5002, '2025-02-28', 120.00);

-- This row goes into orders_future
INSERT INTO orders (order_id, customer_id, order_date, amount)
VALUES (103, 5003, '2025-03-05', 75.50);

Querying Data (Where Partition Pruning Works)

You query the parent table, and as long as you filter by the partition key (order_date), the query will only hit the necessary partitions.

-- This query only scans orders_2025_january, orders_2025_february, and orders_future.
SELECT SUM(amount)
FROM orders
WHERE order_date >= '2025-01-01' AND order_date <= '2025-03-31';

-- This query only scans orders_2025_january (due to Partition Pruning)
SELECT *
FROM orders
WHERE order_date = '2025-01-15';