This article covers how to create a multi-column partitioned table and how pruning occurs in such cases.
1. Creating partitions
2. Tuple Routing
3. Partition pruning
Multi-column partitioning allows us to specify more than one column as a partition key. Currently multi-column partitioning is possible only for range and hash type. Range partitioning was introduced in PostgreSQL10 and hash partitioning was added in PostgreSQL 11.
Creating Partitions
To create a multi-column partition, when defining the partition key in the CREATE TABLE command, state the columns as a comma-separated list. You can specify a maximum of 32 columns.
CREATE TABLE tbl_range (id int, col1 int, col2 int, col3 int)
PARTITION BY RANGE (col1, col2, col3);
CREATE TABLE tbl_hash (id int, col1 int, col2 int, col3 int)
PARTITION BY HASH (col1, col2, col3);
Range
When we mention the partition bounds for a partition of a multicolumn range partitioned table, we need to specify the bound for each of the columns of the partition key in the CREATE TABLE ... PARTITION OF or the ALTER TABLE ... ATTACH PARTITION command.
CREATE TABLE p1 PARTITION OF tbl_range
FOR VALUES FROM (1, 110, 50) TO (20, 200, 200);
Or
ALTER TABLE tbl_range ATTACH PARTITION r1
FOR VALUES FROM (1, 110, 50) TO (20, 200, 200);
The tuple routing section explains how these bounds work for the partition.
Please note that if the unbounded value -- MINVALUE or MAXVALUE -- is used for one of the columns, then all the subsequent columns should also use the same unbounded value.
CREATE TABLE r2 PARTITION OF tbl_range
FOR VALUES FROM (900, MINVALUE, MINVALUE) TO (1020, 200, 200);
ALTER TABLE tbl_range ATTACH PARTITION r3
FOR VALUES FROM (1, 110, 50) TO (MAXVALUE, MAXVALUE, MAXVALUE);
Hash
When we mention the partition bounds for a partition of a multicolumn hash partitioned table, we need to specify only one bound irrespective of the number of columns used.
CREATE TABLE p1 PARTITION OF tbl_hash
FOR VALUES WITH (MODULUS 100, REMAINDER 20);
Or
ALTER TABLE tbl_hash ATTACH PARTITION h1
FOR VALUES FROM (WITH (MODULUS 100, REMAINDER 20);
Tuple Routing
The partitioned parent table will not store any rows but routes all the inserted rows to one of the partitions based on the value of the partition key. This section explains how the tuple routing takes place for the range and hash multi-column partition key.
Range
In the range partitioned table, the lower bound is included in the table but the upper bound is excluded. In a single partitioned table with bound of 0 to 100, rows with partition key value 0 will be permitted in the partition but rows with value 100 will not.
For a multi-column range partition, the row comparison operator is used for tuple routing which means the columns are compared left-to-right, stopping at first unequal value pair. If the partition key value is equal to the upper bound of that column then the next column will be considered.
Consider a partition with bound (0,0) to (100, 50). This would accept a row with the partition key value (0, 100) because the value of the first column satisfies the partition bound of the first column which is 0 to 100 and in this case the second column is not considered.
The partition key value (100, 49) would also be accepted because the first column value is equal to the upper bound specified and so the second column is considered here and it satisfies the restriction 0 to 50.
On the same grounds, rows with value (100, 50) or (101, 10) will not be accepted in the said partition.
Note that if any of the partition key column values is NULL then it can only be routed to the default partition if it exists else it throws an error.
Hash
In the hash partitioned case, the hash of each column value that is part of the partition key is individually calculated and then combined to get a single 64-bit hash value. The modulus operation is performed on this hash value and the remainder is used to determine the partition for the inserted row.
There is no special handling for NULL values, the hash value is generated and combined as explained above to find the partition for the row to be inserted.
Partition Pruning
One of the main reasons to use partitioning is the improved performance achieved by partition pruning. Pruning in a multi-column partitioned table has few restrictions which are explained below.
For simplicity, all examples in this section only showcase the plan time pruning using constants. This pruning capability can be seen in other plans as well where pruning is feasible like runtime pruning, partition-wise aggregation, etc.
Query using all the partition key columns
When the query uses all the partition key columns in its WHERE clause or JOIN clause, partition pruning is possible.
Consider the following multi-column range partitioned table.
Partitioned table "public.tbl_range"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
col1 | integer | | | | plain | |
col2 | integer | | | | plain | |
col3 | integer | | | | plain | |
Partition key: RANGE (col1, col2, col3)
Partitions: r1 FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (1000, 2000, 3000),
r2 FOR VALUES FROM (1000, 2000, 3000) TO (5000, 6000, 7000),
r3 FOR VALUES FROM (5000, 6000, 7000) TO (10000, 11000, 12000),
r4 FOR VALUES FROM (10000, 11000, 12000) TO (15000, 16000, 17000),
r5 FOR VALUES FROM (15000, 16000, 17000) TO (MAXVALUE, MAXVALUE, MAXVALUE)
Following two queries show partition pruning when using all the columns in the partition key.
postgres=# EXPLAIN SELECT * FROM tbl_range WHERE col1 = 5000 AND col2 = 12000 AND col3 = 14000;
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on r3 tbl_range (cost=0.00..230.00 rows=1 width=16)
Filter: ((col1 = 5000) AND (col2 = 12000) AND (col3 = 14000))
(2 rows)
postgres=# EXPLAIN SELECT * FROM tbl_range WHERE col1 < 5000 AND col2 = 12000 AND col3 = 14000;
QUERY PLAN
-----------------------------------------------------------------------
Append (cost=0.00..229.99 rows=2 width=16)
-> Seq Scan on r1 tbl_range_1 (cost=0.00..45.98 rows=1 width=16)
Filter: ((col1 < 5000) AND (col2 = 12000) AND (col3 = 14000))
-> Seq Scan on r2 tbl_range_2 (cost=0.00..184.00 rows=1 width=16)
Filter: ((col1 < 5000) AND (col2 = 12000) AND (col3 = 14000))
(5 rows)
Similarly, for a hash partitioned table with multiple columns in partition key, partition pruning is possible when all columns of partition key are used in a query.
Consider the following multi-column hash partitioned table.
Partitioned table "public.tbl_hash"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | | | plain | |
col1 | integer | | | | plain | |
col2 | integer | | | | plain | |
col3 | integer | | | | plain | |
Partition key: HASH (col1, col2, col3)
Partitions: h1 FOR VALUES WITH (modulus 5, remainder 0),
h2 FOR VALUES WITH (modulus 5, remainder 1),
h3 FOR VALUES WITH (modulus 5, remainder 2),
h4 FOR VALUES WITH (modulus 5, remainder 3),
h5 FOR VALUES WITH (modulus 5, remainder 4)
postgres=# EXPLAIN SELECT * FROM tbl_hash WHERE col1 = 5000 AND col2 = 12000 AND col3 = 14000;
QUERY PLAN
------------------------------------------------------------------------------
Gather (cost=1000.00..7285.05 rows=1 width=16)
Workers Planned: 1
-> Parallel Seq Scan on h4 tbl_hash (cost=0.00..6284.95 rows=1 width=16)
Filter: ((col1 = 5000) AND (col2 = 12000) AND (col3 = 14000))
(4 rows)
Unlike the range partitioned case, only equality operators support partition pruning as the < or > operators will scan all the partitions due to the manner of tuple distribution in a hash-partitioned table.
Queries using a set of partition key columns
Since the multi-column hash partition uses a combined hash value, partition pruning is not applicable when the queries use a subset of the partition key columns.
For the range multi-column partition, however, if the query used the first few columns of the partition key, then partition pruning is still feasible. The tbl_range table described above is used here as well.
The query below only uses the first two out of the three partition key columns.
postgres=# EXPLAIN SELECT * FROM tbl_range WHERE col1 = 5000 AND col2 = 12000;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on r3 tbl_range (cost=0.00..205.00 rows=1 width=16)
Filter: ((col1 = 5000) AND (col2 = 12000))
(2 rows)
The query below uses only the first partition key column.
postgres=# EXPLAIN SELECT * FROM tbl_range WHERE col1 < 2000;
QUERY PLAN
--------------------------------------------------------------------------
Append (cost=0.00..199.97 rows=3997 width=16)
-> Seq Scan on r1 tbl_range_1 (cost=0.00..35.99 rows=1999 width=16)
Filter: (col1 < 2000)
-> Seq Scan on r2 tbl_range_2 (cost=0.00..144.00 rows=1998 width=16)
Filter: (col1 < 2000)
(5 rows)
Conclusion
To determine the candidate for the multi-column partition key, we should check for columns that are frequently used together in queries. For range partitioning, the sequence of columns can be from the most frequently grouped columns to the least frequently used one to enjoy the benefits of partition pruning in most cases. The sequence of columns does not matter in hash partitioning as it does not support pruning for a subset of partition key columns.
To learn more about Partitioning in PostgreSQL, watch my recent Webinar “The truth about PostgreSQL Partitioning”