Partitioning types in PostgreSQL

PostgreSQL supports three main types of declarative partitioning: Range, List, and Hash partitioning.

1. Range Partitioning

Range partitioning divides a table into partitions based on a range of values from a partitioning column (or set of columns).

• Use Case: Ideal for partitioning data based on time (dates/timestamps) or numerical sequences, where new data naturally falls into increasing ranges.
• Example: Partitioning sales data by month or year. A partition for January 2024 would hold all rows where the sale date falls within that month's range.
• Syntax: Uses PARTITION BY RANGE (column) and partitions are created with FOR VALUES FROM (lower_bound) TO (upper_bound).

2. List Partitioning

List partitioning divides a table based on explicit, discrete values from a partitioning column.

• Use Case: Suitable for categorical data where the values are distinct and non-overlapping.
• Example: Partitioning a customer table by region or country code. A partition for 'US' would hold all rows where the region is 'US'.
• Syntax: Uses PARTITION BY LIST (column) and partitions are created with FOR VALUES IN (value1, value2, ...).

3. Hash Partitioning

Hash partitioning distributes rows across a specified number of partitions by applying a hash function to the partitioning column's value.

• Use Case: Best when there is no natural range or list to partition by, or when you need to ensure a relatively even distribution of data across partitions to balance load.
• Example: Partitioning by a primary key like a transaction ID, ensuring data is evenly spread regardless of the ID's sequence.
• Syntax: Uses PARTITION BY HASH (column) and partitions are created with FOR VALUES WITH (MODULUS number, REMAINDER number).

Composite Partitioning

You can also create Composite Partitioning (or sub-partitioning) by defining a partition itself as a partitioned table, combining two or more of the methods for a multi-level scheme (e.g., Range by date, then List by region within each date partition).