PostgreSQL 11 preview - 分区表 增强 汇总

背景

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

E.1.3.1.1. 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)

  允许UPDATE分区字段值，自动将新记录写入新的分区表中。

• 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)

  在分区表主表上创建的索引，将来添加分区时，自动创建对应定义的索引。

  如果分区是ATTACH上来的，并且已经存在索引时，可以把这个索引挂载到在分区表主表上创建的全局索引上（只要定义一致）。挂载方法类似继承关系的挂载。

  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)

  允许postgres_fdw的外部表，作为分区，同时允许insert,update,copy数据路由到对应外部表分区。

  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)

  支持postgres_fdw外部表作为分区，并支持将聚合下推到对应的外部数据源执行。

digoal’s 大量PostgreSQL文章入口