Partitioning in
PostgreSQL
RANGE, LIST, HASH, AND COMPOSITE PARTITIONING
Introduction to Partitioning
u Partitioning splits a table into smaller, more
manageable pieces called partitions. Benefits
include:
u - Improved query performance
u - Faster maintenance operations
u - Efficient data management
u PostgreSQL supports Range, List, Hash, and
Composite partitioning.
Range Partitioning
u Range partitioning divides data based on ranges of
values:
u - Ideal for data with a natural ordering (e.g., dates,
IDs)
u Example:
u CREATE TABLE orders (
u order_id INT, order_date DATE, ...
u ) PARTITION BY RANGE (order_date);
List Partitioning
u List partitioning divides data based on a predefined
list of values:
u - Useful for categorical data (e.g., regions,
departments)
u Example:
u CREATE TABLE employees (
u emp_id INT, department TEXT, ...
u ) PARTITION BY LIST (department);
Hash Partitioning
u Hash partitioning distributes data based on a hash
function:
u - Ensures even distribution across partitions
u - Useful for non-ordered data (e.g., unique IDs)
u Example:
u CREATE TABLE transactions (
u trans_id INT, amount NUMERIC, ...
u ) PARTITION BY HASH (trans_id);
Composite Partitioning
u Composite partitioning combines multiple
partitioning strategies:
u - For complex data distribution needs
u Example:
u CREATE TABLE sales (
u sale_id INT, sale_date DATE, region TEXT, ...
u ) PARTITION BY RANGE (sale_date)
u SUBPARTITION BY LIST (region);
