PostgreSQL11preview-分割槽表增強彙總

標籤

PostgreSQL , 分割槽表 , 增強 , 11

---

背景

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外部表作為分割槽，並支援將聚合下推到對應的外部資料來源執行。