Declarative partitioning
Declarative partitioning
is a partitioning method for dividing a table into partitions
Declarative partitioning
has been available since PostgreSQL 10.
Overview
Traditionally, PostgreSQL provided partitioning functionality via inheritance partitioning, which leveraged PostgreSQL's table inheritance capabilty. However this method has several limitations; in particular setup and maintenance is a much more manual process.
Declarative partitioning
, as the name implies, provides a way of explicitly declaring a table with partitions, and currently provides the following partitioning methods:
Range Partitioning
List Partitioning
Hash Partitioning
(PostgreSQL 11 and later)
A partitioned table created with declarative partitioning
consists of a top-level partitioned table and one or more partitions, which can be normal tables or foreign tables.
Configuration
Following configuration parameters influence declarative partitioning
usage:
System functions
Following system functions are related to declarative partitioning
usage:
Change history
- PostgreSQL 16
- bulk inserts and updates made more efficient (commit 3592e0ff)
- PostgreSQL 15
- foreign key triggers now created for partitioned tables as well as their partitions (commit f4566345)
CLUSTER
can now be executed on partitioned tables (commit cfdd03f4)
- PostgreSQL 14
- partitions can be detached concurrently using
ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY
(commit 71f4c8c6) - support for partitioned tables and indexes added to
REINDEX
(commit a6642b3a) - postgres_fdw: partitions specified in the
IMPORT FOREIGN SCHEMA ... LIMIT
clause can now be imported (commit a3740c48)
- partitions can be detached concurrently using
- PostgreSQL 13
ROW
expressions can now be used to define partitions (commit bb4114a4)- expanded range of cases where partition pruning can be applied (commit 4e85642d)
- expanded range of cases where partitionwise joins can be applied (commits c8434d64 and 981643dc)
BEFORE
row-level triggers permitted on partitioned tables (commit 487e9861)- partitioned tables can be logically replicated via publications (commits 17b9e7f9 and 83fd4532)
- partitioned tables can now be replication targets in logical replication (commit f1ac27bf)
- parameter
publish_via_partition_root
added toCREATE PUBLICATION
(commit 83fd4532 pgbench
options--partitions
and--partition-method
added (commit b1c1aa53)
- PostgreSQL 12
ALTER TABLE ... ATTACH PARTITION
is now performed with reduced locking requirements (commit 898e5e32)- insertion into partitioned tables with
COPY
speeded up through use of multi-inserts (commit 0d5f05cd) - psql
\dP
command to list partitioned tables and indexes added (commit 1c5d9270) - following system functions added:
pg_partition_ancestors()
(commit b96f6b19)pg_partition_root()
(commit 3677a0b2)pg_partition_tree()
(commit d5eec4ee)
- PostgreSQL 11
- support for default partitions added (commit 6f6b99d1)
- partition pruning is able to handle any stable expression (commit 73b7f48f)
- support for partition pruning at execution time added (commit 499be013)
- hash partitioning support added (commit 1aba8e65)
UPDATE
can now move tuples between partitions (initial commit 2f178441)- unique indexes allowed on partitioned tables (commit eb7ed3f3)
INSERT ... ON CONFLICT ...
allowed on partitioned tables (commit 555ee77a)- foreign keys allowed on partitioned tables (commit 3de241db)
FOR EACH ROW
triggers allowed on partitioned tables (commit 86f57594)INSERT
,UPDATE
, andCOPY
on partitioned tables can route rows to foreign partitions (commit 3d956d95)- partition-wise joins enabled (commit f49842d1)
- partition-wise grouping/aggregation enabled (commit e2f1eb0e)
- PostgreSQL 10
- added (initial commit f0e44751)
References
- PostgreSQL documentation: Declarative Partitioning