pgPedia

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Top / D

• Browse categories
• Random entry
• Blog
• PostgreSQL releases
  • PostgreSQL 16 (dev)
  • PostgreSQL 15 (latest)
  • PostgreSQL 14
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10 (EOL)

Version charts

SQL commands
System catalog tables
System catalog views
System statistics views
File system functions

Contrib modules
Object Identifier Types
Progress reporting views
Storage parameters (relopts)
Predefined roles

Useful overviews

xlog renaming in Pg 10
Online tools for PostgreSQL
Abbreviations and acronyms

Useful links

PostgreSQL documentation
PostgreSQL feature matrix
PostgreSQL versioning policy
PostgreSQL latest versions

@postgrespedia

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:

• enable_partition_pruning
• enable_partitionwise_join
• enable_partitionwise_aggregate

System functions

Following system functions are related to declarative partitioning usage:

• pg_get_partkeydef()
• pg_partition_ancestors()
• pg_partition_root()
• pg_partition_tree()

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)
• 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 to CREATE 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, and COPY 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

Categories