PostgreSQL 11 preview - 分區表 增強 彙總

标簽

PostgreSQL , 分區表 , 增強 , 11

https://github.com/digoal/blog/blob/master/201805/20180519_01.md#%E8%83%8C%E6%99%AF 背景

PostgreSQL 11 分區表有非常多的增強特性。

https://github.com/digoal/blog/blob/master/201805/20180519_01.md#e1311-partitioning 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架構，性能暴增)》 enable_partitionwise_aggregate
• Allow 

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

  支援postgres_fdw外部表作為分區，并支援将聚合下推到對應的外部資料源執行。