Partition types (range, list, hash) in PostgreSQL

Introduction

Partitioning helps split a big table into smaller parts. This makes data easier to manage and faster to search.

You have a large sales table and want to separate data by year.

You want to group customer data by country for quick access.

You want to spread data evenly across parts to balance load.

You want to delete old data easily by removing a partition.

You want to improve query speed by searching only relevant parts.

Syntax

PostgreSQL

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

-- Then create partitions:
CREATE TABLE partition_name PARTITION OF table_name
  FOR VALUES partition_values;

partition_type can be RANGE, LIST, or HASH.

Each partition holds rows matching its defined values.

Examples

This creates a sales table partitioned by date ranges. One partition holds sales in 2023.

PostgreSQL

CREATE TABLE sales (
  id serial PRIMARY KEY,
  sale_date date,
  amount numeric
) PARTITION BY RANGE (sale_date);

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

This partitions customers by country. One partition holds customers from the USA.

PostgreSQL

CREATE TABLE customers (
  id serial PRIMARY KEY,
  name text,
  country text
) PARTITION BY LIST (country);

CREATE TABLE customers_usa PARTITION OF customers
  FOR VALUES IN ('USA');

This partitions logs by hashing user_id into 4 parts. This partition holds remainder 0.

PostgreSQL

CREATE TABLE logs (
  id serial PRIMARY KEY,
  event_time timestamp,
  user_id int
) PARTITION BY HASH (user_id);

CREATE TABLE logs_part1 PARTITION OF logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Sample Program

This example creates an orders table partitioned by year. It inserts orders in 2023 and 2024, then selects all orders sorted by date.

PostgreSQL

CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  order_date date,
  region text
) 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, region) VALUES
  ('2023-05-10', 'North'),
  ('2024-03-15', 'South');

SELECT * FROM orders ORDER BY order_date;

Important Notes

Range partitions split data by continuous ranges, like dates.

List partitions split data by specific values, like countries.

Hash partitions spread data evenly using a hash function, good for load balancing.

Partitioning improves query speed by scanning only relevant partitions.

Common mistake: forgetting to create partitions after defining the main table.

Summary

Partitioning divides big tables into smaller, manageable parts.

Use RANGE for continuous data ranges, LIST for specific values, HASH for even distribution.

Partitions help with faster queries and easier data management.