Sub-partitioning in PostgreSQL

Introduction

Sub-partitioning helps organize large tables into smaller, more manageable pieces by dividing data twice, making queries faster and maintenance easier.

When you have a big table with data that can be grouped by two different categories, like region and year.

When you want to speed up searches by narrowing down data to smaller parts.

When you need to archive or delete old data efficiently without affecting recent data.

When managing data that grows quickly and needs to be split for better performance.

When you want to improve backup and restore times by working on smaller data chunks.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_definitions
) PARTITION BY partition_method1 (column1);

CREATE TABLE partition_name PARTITION OF table_name
  FOR VALUES partition_values
  PARTITION BY partition_method2 (column2);

CREATE TABLE subpartition_name PARTITION OF partition_name
  FOR VALUES subpartition_values;

partition_method1 and partition_method2 can be RANGE, LIST, or HASH.

You first define the parent table partitioned by the first key, then define its partitions which are themselves partitioned by the second key.

Examples

This creates a sales table partitioned by region, then subpartitioned by sale date ranges.

PostgreSQL

CREATE TABLE sales (
  id SERIAL,
  region TEXT,
  sale_date DATE,
  amount NUMERIC
) PARTITION BY LIST (region);

Defines the 'East' region partition with subpartitioning by sale_date.

PostgreSQL

CREATE TABLE sales_east PARTITION OF sales
  FOR VALUES IN ('East')
  PARTITION BY RANGE (sale_date);

Defines a subpartition for sales in the East region for the year 2023.

PostgreSQL

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

Sample Program

This example creates an orders table partitioned by region and subpartitioned by order date. It inserts two orders in the North region for 2023 and then selects them.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT,
  order_region TEXT,
  order_date DATE,
  total NUMERIC
) PARTITION BY LIST (order_region);

CREATE TABLE orders_north PARTITION OF orders
  FOR VALUES IN ('North')
  PARTITION BY RANGE (order_date);

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

INSERT INTO orders (customer_id, order_region, order_date, total) VALUES
  (1, 'North', '2023-05-10', 100.00),
  (2, 'North', '2023-11-20', 250.50);

SELECT * FROM orders WHERE order_region = 'North' AND order_date >= '2023-01-01' AND order_date < '2024-01-01';

Important Notes

Sub-partitioning can improve query speed but adds complexity to table design.

Make sure your queries include the partition keys to benefit from partition pruning.

PostgreSQL supports multi-level partitioning (sub-partitioning) in version 10 or higher.

Summary

Sub-partitioning splits data twice for better organization and performance.

Use LIST, RANGE, or HASH methods for both partitions and subpartitions.

Define the parent table first, then create its partitions, then subpartitions of those.