Partitioning best practices in PostgreSQL

Introduction

Partitioning helps organize large tables into smaller pieces, making data easier and faster to manage.

When you have a very large table that slows down queries.

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

When you want to improve query speed by scanning only relevant parts of data.

When you want to manage 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 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 data.

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 PRIMARY KEY,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

This creates a logs table partitioned by specific log levels.

PostgreSQL

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  log_level TEXT NOT NULL,
  message TEXT
) PARTITION BY LIST (log_level);

This creates a users table partitioned by hashing the id.

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  region TEXT NOT NULL,
  name TEXT
) PARTITION BY HASH (id);

Sample Program

This example creates an orders table partitioned by year. It inserts data into two partitions and queries orders from 2024.

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-05-10', 1, 100.00),
  ('2024-03-15', 2, 150.00);

SELECT * FROM orders WHERE order_date >= '2024-01-01';

Important Notes

Always choose a partition key that matches your common query filters to speed up searches.

Keep partitions balanced in size to avoid some partitions becoming too large or too small.

Use partitioning to simplify data management tasks like archiving or deleting old data.

Summary

Partitioning breaks big tables into smaller, manageable parts.

Choose partition keys based on how you query your data.

Use RANGE, LIST, or HASH methods depending on your data and needs.