List partitioning by category in PostgreSQL

Introduction

List partitioning helps organize data into smaller parts based on categories. This makes searching and managing data faster and easier.

You have a large table of products and want to separate them by product type.

You want to store customer data divided by country or region.

You manage orders and want to split them by order status like 'pending', 'shipped', or 'cancelled'.

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

You want to keep data organized and easier to maintain.

Syntax

PostgreSQL

CREATE TABLE parent_table (
  id SERIAL PRIMARY KEY,
  category TEXT NOT NULL,
  data TEXT
) PARTITION BY LIST (category);

CREATE TABLE partition_name PARTITION OF parent_table
  FOR VALUES IN ('category_value1', 'category_value2');

The parent table holds the structure but no data directly.

Each partition stores rows for specific category values.

Examples

This example creates a products table partitioned by category with two partitions: Electronics and Clothing.

PostgreSQL

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  category TEXT NOT NULL,
  name TEXT
) PARTITION BY LIST (category);

CREATE TABLE products_electronics PARTITION OF products
  FOR VALUES IN ('Electronics');

CREATE TABLE products_clothing PARTITION OF products
  FOR VALUES IN ('Clothing');

Orders table is partitioned by order status with partitions for Pending and Completed orders.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  status TEXT NOT NULL,
  amount NUMERIC
) PARTITION BY LIST (status);

CREATE TABLE orders_pending PARTITION OF orders
  FOR VALUES IN ('Pending');

CREATE TABLE orders_completed PARTITION OF orders
  FOR VALUES IN ('Completed');

Sample Program

This program creates a customer_data table partitioned by region with USA and Europe partitions. It inserts three customers and selects all rows ordered by ID.

PostgreSQL

CREATE TABLE customer_data (
  customer_id SERIAL PRIMARY KEY,
  region TEXT NOT NULL,
  name TEXT
) PARTITION BY LIST (region);

CREATE TABLE customer_data_usa PARTITION OF customer_data
  FOR VALUES IN ('USA');

CREATE TABLE customer_data_europe PARTITION OF customer_data
  FOR VALUES IN ('Europe');

INSERT INTO customer_data (region, name) VALUES
  ('USA', 'Alice'),
  ('Europe', 'Bob'),
  ('USA', 'Charlie');

SELECT * FROM customer_data ORDER BY customer_id;

Important Notes

List partitioning improves query speed by limiting search to relevant partitions.

Each partition is a separate table storing only rows for its category values.

Be careful to include all possible category values in partitions to avoid errors.

Summary

List partitioning splits a table into parts based on category values.

It helps organize data and speeds up queries by focusing on relevant partitions.

Each partition holds rows for specific category values defined in the parent table.