PostgreSQL 11 preview - 分区表 增强 汇总

1 minute read


PostgreSQL 11 分区表有非常多的增强特性。

E. Partitioning

  • Allow faster partition elimination during query processing (Amit Langote, David Rowley, Dilip Kumar)

    This speeds access to partitioned tables with many partitions.


    《PostgreSQL 11 preview 分区过滤控制参数 - enable_partition_pruning》

  • Allow partition elimination during query execution (David Rowley, Beena Emerson)

    允许在execute阶段消除不需要的分区访问,以前只能在PLAN时消除不需要的分区访问。(使得JOIN, prepared statement也可以在exexute阶段消除不需要访问的分区)

    Previously partition elimination could only happen at planning time, meaning many joins and prepared queries could not use partition elimination.

  • Allow the creation of partitions based on hashing a key (Amul Sul)


    《PostgreSQL 11 preview - 分区表用法及增强 - 增加HASH分区支持 (hash, range, list)》

  • Allow updated rows to automatically move to new partitions based on the new row contents (Amit Khandekar)


  • Allow partitioned tables to have a default partition (Jeevan Ladhe, Beena Emerson, Ashutosh Bapat, Rahila Syed, Robert Haas)


    The default partition can store rows that don’t match any of the other defined partitions, and is searched accordingly.

  • Allow UNIQUE indexes on partitioned tables if the partition key guarantees uniqueness (Álvaro Herrera, Amit Langote)


  • Allow indexes on a partitioned table to be automatically created in any child partitions (Álvaro Herrera)



    ALTER INDEX name ATTACH PARTITION index_name; (name是分区表上的本地索引名, index_name是分区表主表上的索引名.)

    The new command ALTER INDEX ATTACH PARTITION allows indexes to be attached to partitions.

    This does not behave as a global index since the contents are private to each index. WARN WHEN USING AN EXISTING INDEX?

  • Allow foreign keys on partitioned tables (Álvaro Herrera)

    分区表支持foreign key.

  • Allow INSERT, UPDATE, and COPY on partitioned tables to properly route rows to foreign partitions (Etsuro Fujita, Amit Langote)


    This is supported by postgres_fdw foreign tables.

  • Allow FOR EACH ROW triggers on partitioned tables (Álvaro Herrera)


    Creation of a trigger on partitioned tables automatically creates triggers on all partition tables, and on newly-created ones.

    支持deferred unique约束,在事务结束时检查UNIQUE约束。

    This also allows deferred unique constraints on partitioned tables.

  • Allow equality joins between partitioned tables with identically partitioned child tables to join the child tables directly (Ashutosh Bapat)

    《PostgreSQL 11 preview - 分区表智能并行JOIN (已类似MPP架构,性能暴增)》

    This features is disabled by default but can be enabled by changing enable_partitionwise_join.

  • Perform aggregation on each partition, and then merge the results (Jeevan Chalke, Ashutosh Bapat, Robert Haas)

    《PostgreSQL 11 preview - 分区表智能并行聚合、分组计算(已类似MPP架构,性能暴增)》

    This features is disabled by default but can be enabled by changing enable_partitionwise_aggregate.

  • Allow postgres_fdw to push down aggregates to foreign tables that are partitions (Jeevan Chalke)


Flag Counter

digoal’s 大量PostgreSQL文章入口