Why partitioning is needed in PostgreSQL

Introduction

Partitioning helps manage very large tables by splitting them into smaller, easier parts. This makes data faster to find and keeps the database organized.

When a table grows very large and queries become slow.

When you want to archive old data separately but keep it accessible.

When you need to improve performance for specific queries on parts of data.

When managing data by time periods like months or years.

When you want to delete or load data in chunks without affecting the whole table.

Syntax

PostgreSQL

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

Partition methods include RANGE, LIST, and HASH.

Each partition is a separate table holding a subset of data.

Examples

This creates a sales table partitioned by date ranges.

PostgreSQL

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

This creates a logs table partitioned by different log types.

PostgreSQL

CREATE TABLE logs (
  id SERIAL,
  log_type TEXT,
  message TEXT
) PARTITION BY LIST (log_type);

Sample Program

This example creates an orders table partitioned by year. It inserts data into two partitions and selects all orders.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id INT NOT NULL,
  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.00),
  ('2024-03-20', 2, 150.00);

SELECT * FROM orders;

Important Notes

Partitioning improves query speed by scanning only relevant partitions.

It helps with easier data maintenance like archiving or deleting old data.

Not all queries benefit equally; design partitions based on common query patterns.

Summary

Partitioning splits big tables into smaller parts for better speed and management.

Use partitioning when dealing with large data or time-based data.

Partitions act like separate tables but work together as one logical table.