Creating partitioned tables in PostgreSQL

Introduction

Partitioned tables help organize large data by splitting it into smaller parts. This makes searching and managing data faster and easier.

When you have a very large table and want to improve query speed.

When you want to store data by time periods, like monthly sales data.

When you want to separate data by categories, like regions or departments.

When you want to manage data in smaller chunks for easier backups or archiving.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
) PARTITION BY partition_method (column_name);

partition_method can be RANGE, LIST, or HASH depending on how you want to split the data.

You must create partitions (child tables) after creating the main partitioned table.

Examples

This creates a table partitioned by date ranges.

PostgreSQL

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

This creates a table partitioned by department names.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT
) PARTITION BY LIST (department);

This creates a table partitioned by hashing the id column.

PostgreSQL

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  event_time TIMESTAMP,
  event_type TEXT
) PARTITION BY HASH (id);

Sample Program

This example creates a partitioned table for orders by year. It adds two partitions for 2023 and 2024. Then it inserts orders into each partition and selects all orders sorted by date.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id INT,
  amount NUMERIC
) 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 (order_date, customer_id, amount) VALUES
  ('2023-06-15', 1, 100.50),
  ('2024-03-20', 2, 200.75);

SELECT * FROM orders ORDER BY order_date;

Important Notes

Each partition is a separate table that holds a subset of data.

You must define partitions that cover all possible values to avoid errors on insert.

Partitioning improves performance but adds some complexity in setup.

Summary

Partitioned tables split large tables into smaller parts for better performance.

You choose how to split data: by range, list, or hash.

After creating the main table, create partitions for each data segment.