This article discusses table partitions, the benefits of using them to increase performance, and the types of partitions that can be used in PostgreSQL.
1. Benefits of partitioning
2. When to use partitioning
3. How to use partitioning
a. List partition
b. Range partition
c. Hash partition
d. Multilevel partition
4. Limitations
With huge data being stored in databases, performance and scaling are two main factors that are affected. As table size increases with data load, more data scanning, swapping pages to memory, and other table operation costs also increase. Partitioning may be a good solution, as It can help divide a large table into smaller tables and thus reduce table scans and memory swap problems, which ultimately increases performance.
Partitioning helps to scale PostgreSQL by splitting large logical tables into smaller physical tables that can be stored on different storage media based on uses. Users can take better advantage of scaling by using declarative partitioning along with foreign tables using postgres_fdw.
Benefits of partitioning
- PostgreSQL declarative partitioning is highly flexible and provides good control to users. Users can create any level of partitioning based on need and can modify, use constraints, triggers, and indexes on each partition separately as well as on all partitions together.
- Query performance can be increased significantly compared to selecting from a single large table.
- Partition-wise-join and partition-wise-aggregate features increase complex query computation performance as well.
- Bulk loads and data deletion can be much faster, as based on user requirements these operations can be performed on individual partitions.
- Each partition can contain data based on its frequency of use and so can be stored on media that may be cheaper or slower for low-use data.
When to use partitioning
Most benefits of partitioning can be enjoyed when a single table is not able to provide them. So we can say that if a lot of data is going to be written on a single table at some point, users need partitioning. Apart from data, there may be other factors users should consider, like update frequency of the data, use of data over a time period, how small a range data can be divided, etc. With good planning and taking all factors into consideration, table partitioning can give a great performance boost and scale your PostgreSQL to larger datasets.
How to use partitioning
As of PostgreSQL12 release List, Range, Hash and combinations of these partition methods at different levels are supported. Let’s explore what these are and how users can create different types of partitions with examples. For this article we will use the same table, which can be created by different partition methods.
LIST PARTITION
A list partition is created with predefined values to hold in a partitioned table. A default partition (optional) holds all those values that are not part of any specified partition.
postgres=# CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY LIST(status);
CREATE TABLE
postgres=# CREATE TABLE cust_active PARTITION OF customers FOR VALUES IN ('ACTIVE');
CREATE TABLE
postgres=# CREATE TABLE cust_archived PARTITION OF customers FOR VALUES IN ('EXPIRED');
CREATE TABLE
postgres=# CREATE TABLE cust_others PARTITION OF customers DEFAULT;
CREATE TABLE
postgres=# \d+ customers
Partitioned table "public.customers"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition key: LIST (status)
Partitions: cust_active FOR VALUES IN ('ACTIVE'),
cust_archived FOR VALUES IN ('EXPIRED'),
cust_others DEFAULT
postgres=# \d+ cust_active
Table "public.cust_active"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition of: customers FOR VALUES IN ('ACTIVE')
Partition constraint: ((status IS NOT NULL) AND (status = 'ACTIVE'::text))
Access method: heap
postgres=# INSERT INTO customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
INSERT 0 4
postgres=# SELECT tableoid::regclass,* FROM customers;
tableoid | id | status | arr
---------------+----+-------------+-----
cust_active | 1 | ACTIVE | 100
cust_archived | 3 | EXPIRED | 38
cust_others | 2 | RECURRING | 20
cust_others | 4 | REACTIVATED | 144
(4 rows)
RANGE PARTITION
A range partition is created to hold values within a range provided on the partition key. Both minimum and maximum values of the range need to be specified, where minimum value is inclusive and maximum value is exclusive.
postgres=# CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);
CREATE TABLE
postgres=# CREATE TABLE cust_arr_small PARTITION OF customers FOR VALUES FROM (MINVALUE) TO (25);
CREATE TABLE
postgres=# CREATE TABLE cust_arr_medium PARTITION OF customers FOR VALUES FROM (25) TO (75);
CREATE TABLE
postgres=# CREATE TABLE cust_arr_large PARTITION OF customers FOR VALUES FROM (75) TO (MAXVALUE);
CREATE TABLE
postgres=#
postgres=# \d+ customers
Partitioned table "public.customers"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition key: RANGE (arr)
Partitions: cust_arr_large FOR VALUES FROM ('75') TO (MAXVALUE),
cust_arr_medium FOR VALUES FROM ('25') TO ('75'),
cust_arr_small FOR VALUES FROM (MINVALUE) TO ('25')
postgres=# \d+ cust_arr_small
Table "public.cust_arr_small"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition of: customers FOR VALUES FROM (MINVALUE) TO ('25')
Partition constraint: ((arr IS NOT NULL) AND (arr < '25'::numeric))
Access method: heap
postgres=#
postgres=# INSERT INTO customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
INSERT 0 4
postgres=# SELECT tableoid::regclass,* FROM customers;
tableoid | id | status | arr
-----------------+----+-------------+-----
cust_arr_small | 2 | RECURRING | 20
cust_arr_medium | 3 | EXPIRED | 38
cust_arr_large | 1 | ACTIVE | 100
cust_arr_large | 4 | REACTIVATED | 144
(4 rows)
HASH PARTITION
A hash partition is created by using modulus and remainder for each partition, where rows are inserted by generating a hash value using these modulus and remainders.
postgres=# CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY HASH(id);
CREATE TABLE
postgres=# CREATE TABLE cust_part1 PARTITION OF customers FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE
postgres=# CREATE TABLE cust_part2 PARTITION OF customers FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE
postgres=# CREATE TABLE cust_part3 PARTITION OF customers FOR VALUES WITH (modulus 3, remainder 2);
CREATE TABLE
postgres=#
postgres=# \d+ customers
Partitioned table "public.customers"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition key: HASH (id)
Partitions: cust_part1 FOR VALUES WITH (modulus 3, remainder 0),
cust_part2 FOR VALUES WITH (modulus 3, remainder 1),
cust_part3 FOR VALUES WITH (modulus 3, remainder 2)
postgres=# \d+ cust_part1
Table "public.cust_part1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition of: customers FOR VALUES WITH (modulus 3, remainder 0)
Partition constraint: satisfies_hash_partition('16475'::oid, 3, 0, id)
Access method: heap
postgres=#
postgres=# INSERT INTO customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
INSERT 0 4
postgres=# SELECT tableoid::regclass,* FROM customers;
tableoid | id | status | arr
------------+----+-------------+-----
cust_part1 | 2 | RECURRING | 20
cust_part1 | 4 | REACTIVATED | 144
cust_part2 | 3 | EXPIRED | 38
cust_part3 | 1 | ACTIVE | 100
(4 rows)
MULTILEVEL PARTITION
PostgreSQL multilevel partitions can be created up to N levels. Partition methods LIST-LIST, LIST-RANGE, LIST-HASH, RANGE-RANGE, RANGE-LIST, RANGE-HASH, HASH-HASH, HASH-LIST, and HASH-RANGE can be created in PostgreSQL declarative partitioning.
postgres=# CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY LIST(status);
CREATE TABLE
postgres=# CREATE TABLE cust_active PARTITION OF customers FOR VALUES IN ('ACTIVE','RECURRING','REACTIVATED') PARTITION BY RANGE(arr);
CREATE TABLE
postgres=# CREATE TABLE cust_arr_small PARTITION OF cust_active FOR VALUES FROM (MINVALUE) TO (101) PARTITION BY HASH(id);
CREATE TABLE
postgres=# CREATE TABLE cust_part11 PARTITION OF cust_arr_small FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE
postgres=# CREATE TABLE cust_part12 PARTITION OF cust_arr_small FOR VALUES WITH (modulus 2, remainder 1);
CREATE TABLE
postgres=# CREATE TABLE cust_other PARTITION OF customers DEFAULT PARTITION BY RANGE(arr);
CREATE TABLE
postgres=# CREATE TABLE cust_arr_large PARTITION OF cust_other FOR VALUES FROM (101) TO (MAXVALUE) PARTITION BY HASH(id);
CREATE TABLE
postgres=# CREATE TABLE cust_part21 PARTITION OF cust_arr_large FOR VALUES WITH (modulus 2, remainder 0);
CREATE TABLE
postgres=# CREATE TABLE cust_part22 PARTITION OF cust_arr_large FOR VALUES WITH (modulus 2, remainder 1);
CREATE TABLE
postgres=#
postgres=# \d+ customers
Partitioned table "public.customers"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition key: LIST (status)
Partitions: cust_active FOR VALUES IN ('ACTIVE', 'RECURRING', 'REACTIVATED'), PARTITIONED,
cust_other DEFAULT, PARTITIONED
postgres=# \d+ cust_active
Partitioned table "public.cust_active"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition of: customers FOR VALUES IN ('ACTIVE', 'RECURRING', 'REACTIVATED')
Partition constraint: ((status IS NOT NULL) AND (status = ANY (ARRAY['ACTIVE'::text, 'RECURRING'::text, 'REACTIVATED'::text])))
Partition key: RANGE (arr)
Partitions: cust_arr_small FOR VALUES FROM (MINVALUE) TO ('101'), PARTITIONED
postgres=# \d+ cust_arr_small
Partitioned table "public.cust_arr_small"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition of: cust_active FOR VALUES FROM (MINVALUE) TO ('101')
Partition constraint: ((status IS NOT NULL) AND (status = ANY (ARRAY['ACTIVE'::text, 'RECURRING'::text, 'REACTIVATED'::text])) AND (arr IS NOT NULL) AND (arr < '101'::numeric))
Partition key: HASH (id)
Partitions: cust_part11 FOR VALUES WITH (modulus 2, remainder 0),
cust_part12 FOR VALUES WITH (modulus 2, remainder 1)
postgres=# \d+ cust_part11
Table "public.cust_part11"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | text | | | | extended | |
arr | numeric | | | | main | |
Partition of: cust_arr_small FOR VALUES WITH (modulus 2, remainder 0)
Partition constraint: ((status IS NOT NULL) AND (status = ANY (ARRAY['ACTIVE'::text, 'RECURRING'::text, 'REACTIVATED'::text])) AND (arr IS NOT NULL) AND (arr < '101'::numeric) AND satisfies_hash_partition('16621'::oid, 2, 0, id))
Access method: heap
postgres=#
postgres=# INSERT INTO customers VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'REACTIVATED',38), (4,'EXPIRED',144);
INSERT 0 4
postgres=# SELECT tableoid::regclass,* FROM customers;
tableoid | id | status | arr
-------------+----+-------------+-----
cust_part11 | 1 | ACTIVE | 100
cust_part11 | 2 | RECURRING | 20
cust_part12 | 3 | REACTIVATED | 38
cust_part22 | 4 | EXPIRED | 144
(4 rows)
Limitations
Partitioning was introduced in PostgreSQL 10 and continues to be improved and made more stable. Still, there are certain limitations that users may need to consider:
1. Unique constraints on partitioned tables must include all the partition key columns. One work-around is to create unique constraints on each partition instead of a partitioned table.
2. Partition does not support BEFORE ROW triggers on partitioned tables. If necessary, they must be defined on individual partitions, not the partitioned table.
3. Range partition does not allow NULL values.
4. PostgreSQL does not create a system-defined subpartition when not given it explicitly, so if a subpartition is present at least one partition should be present to hold values.
5. In the case of HASH-LIST, HASH-RANGE, and HASH-HASH composite partitions, users need to make sure all partitions are present at the subpartition level as HASH can direct values at any partition based on hash value.
For more information, please refer to the PostgreSQL documentation:
https://www.postgresql.org/docs/current/ddl-partitioning.html
https://www.postgresql.org/docs/current/sql-createtable.html
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...