EDB Postgres 10 企業版裡面的 Partitioned Table 系統表
本文授權轉載自渡鴉之丘
在 PostgreSQL 10 引進了原生的 Partitioned Table 語法,EDB 的企業版在很久之前就有 Tartitioned Table,各自也都有相關的系統表。那麼,這些系統表有相互含括嗎?
這裡紀錄一下這個問題,然後順便偷懶,當作新的 Partitioned Table 語法功能筆記~
以下參考 Postgres 10 Partitioned Table 原生語法範例與 EDB 10 企業版範例,分別建立 Partitioned Table。然後查詢 Postgres 10 新增的原生系統表與企業版的 Oracle 相容 Data Dictionary。
首先是利用 EDB 10 企業版語法建立 Partitioned Table,然後分別查找原生 Partitioned Table 系統表 pg_partitioned_table 與企業版的 ALL_PART_TABLES
edb=# -- 驗明正身~ edb=# select version(); version ------------------------------------------------------------------------------------------------------------- EnterpriseDB 10.1.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit (1 row) edb=# edb=# CREATE TABLE sales ( dept_no number, part_no varchar2, country varchar2(20), date date, amount number ) PARTITION BY LIST(country) ( PARTITION europe VALUES('FRANCE', 'ITALY'), PARTITION asia VALUES('INDIA', 'PAKISTAN'), PARTITION americas VALUES('US', 'CANADA') ); CREATE TABLE edb=# edb=# \x Expanded display is on. edb=# select * from pg_partitioned_table; -[ RECORD 1 ]-+------ partrelid | 16384 partstrat | l partnatts | 1 partattrs | 3 partclass | 3126 partcollation | 100 partexprs | partnullorder | 0 edb=# select * from all_part_tables ; -[ RECORD 1 ]-------------+------------- owner | ENTERPRISEDB schema_name | PUBLIC table_name | SALES partitioning_type | LIST subpartitioning_type | NONE partition_count | 3 def_subpartition_count | 0 partitioning_key_count | 1 subpartitioning_key_count | status | VALID def_tablespace_name | def_pct_free | def_pct_used | def_ini_trans | def_max_trans | def_initial_extent | def_next_extent | def_min_extents | def_max_extents | def_pct_increase | def_freelists | def_freelist_groups | def_logging | YES def_compression | NONE def_buffer_pool | DEFAULT ref_ptn_constraint_name | interval | edb=# edb=# select * from all_tab_partitions ; -[ RECORD 1 ]------+------------------------------------ table_owner | ENTERPRISEDB schema_name | PUBLIC table_name | SALES composite | NO partition_name | EUROPE subpartition_count | 0 high_value | FOR VALUES IN ('FRANCE', 'ITALY') high_value_length | 33 partition_position | tablespace_name | pct_free | 0 pct_used | 0 ini_trans | 0 max_trans | 0 initial_extent | next_extent | min_extent | 0 max_extent | 0 pct_increase | 0 freelists | freelist_groups | logging | YES compression | NONE num_rows | 0 blocks | 0 empty_blocks | avg_space | chain_cnt | avg_row_len | sample_size | last_analyzed | buffer_pool | global_stats | YES user_stats | NO backing_table | sales_europe -[ RECORD 2 ]------+------------------------------------ table_owner | ENTERPRISEDB schema_name | PUBLIC table_name | SALES composite | NO partition_name | ASIA subpartition_count | 0 high_value | FOR VALUES IN ('INDIA', 'PAKISTAN') high_value_length | 35 partition_position | tablespace_name | pct_free | 0 pct_used | 0 ini_trans | 0 max_trans | 0 initial_extent | next_extent | min_extent | 0 max_extent | 0 pct_increase | 0 freelists | freelist_groups | logging | YES compression | NONE num_rows | 0 blocks | 0 empty_blocks | avg_space | chain_cnt | avg_row_len | sample_size | last_analyzed | buffer_pool | global_stats | YES user_stats | NO backing_table | sales_asia -[ RECORD 3 ]------+------------------------------------ table_owner | ENTERPRISEDB schema_name | PUBLIC table_name | SALES composite | NO partition_name | AMERICAS subpartition_count | 0 high_value | FOR VALUES IN ('US', 'CANADA') high_value_length | 30 partition_position | tablespace_name | pct_free | 0 pct_used | 0 ini_trans | 0 max_trans | 0 initial_extent | next_extent | min_extent | 0 max_extent | 0 pct_increase | 0 freelists | freelist_groups | logging | YES compression | NONE num_rows | 0 blocks | 0 empty_blocks | avg_space | chain_cnt | avg_row_len | sample_size | last_analyzed | buffer_pool | global_stats | YES user_stats | NO backing_table | sales_americas edb=# edb=# \d+ sales Table "public.sales" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------+-----------------------------+-----------+----------+---------+----------+--------------+------------- dept_no | numeric | | | | main | | part_no | character varying | | | | extended | | country | character varying(20) | | | | extended | | date | timestamp without time zone | | | | plain | | amount | numeric | | | | main | | Partition key: LIST (country) Partitions: sales_americas FOR VALUES IN ('US', 'CANADA'), sales_asia FOR VALUES IN ('INDIA', 'PAKISTAN'), sales_europe FOR VALUES IN ('FRANCE', 'ITALY') edb=# |
上面可見,透過 Oracle 相容語法建立的 Partitioned Table 也會在 pg_partitioned_table 裡面註冊相關資訊。
下面切換到 postgres database 裡面,用原生語法建立 Partitioned Table。一樣用兩種
edb=# \c postgres You are now connected to database "postgres" as user "enterprisedb". postgres=# postgres=# --PGSQL10 原生語法 postgres=# postgres=# CREATE TABLE measurement_year_month ( logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate)); CREATE TABLE measurement_ym_older PARTITION OF measurement_year_month FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11); CREATE TABLE measurement_ym_y2016m11 PARTITION OF measurement_year_month FOR VALUES FROM (2016, 11) TO (2016, 12); CREATE TABLE measurement_ym_y2016m12 PARTITION OF measurement_year_month FOR VALUES FROM (2016, 12) TO (2017, 01); CREATE TABLE measurement_ym_y2017m01 PARTITION OF measurement_year_month FOR VALUES FROM (2017, 01) TO (2017, 02); CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE postgres=# postgres=# select * from pg_partitioned_table; -[ RECORD 1 ]-+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- partrelid | 16408 partstrat | r partnatts | 2 partattrs | 0 0 partclass | 3123 3123 partcollation | 0 0 partexprs | ({FUNCEXPR :funcid 2021 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 100 :args ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 152 :constvalue 8 [ 32 0 0 0 121 101 97 114 ]} {VAR :varno 1 :varattno 1 :vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 162}) :location 144} {FUNCEXPR :funcid 2021 :funcresulttype 701 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 100 :args ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 180 :constvalue 9 [ 36 0 0 0 109 111 110 116 104 ]} {VAR :varno 1 :varattno 1 :vartype 1114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 191}) :location 172}) partnullorder | 0 postgres=# postgres=# select * from all_part_tables ; -[ RECORD 1 ]-------------+----------------------- owner | ENTERPRISEDB schema_name | PUBLIC table_name | MEASUREMENT_YEAR_MONTH partitioning_type | RANGE subpartitioning_type | NONE partition_count | 4 def_subpartition_count | 0 partitioning_key_count | 2 subpartitioning_key_count | status | VALID def_tablespace_name | def_pct_free | def_pct_used | def_ini_trans | def_max_trans | def_initial_extent | def_next_extent | def_min_extents | def_max_extents | def_pct_increase | def_freelists | def_freelist_groups | def_logging | YES def_compression | NONE def_buffer_pool | DEFAULT ref_ptn_constraint_name | interval | postgres=# postgres=# select * from all_tab_partitions ; -[ RECORD 1 ]------+------------------------------------------------------- table_owner | ENTERPRISEDB schema_name | PUBLIC table_name | MEASUREMENT_YEAR_MONTH composite | NO partition_name | MEASUREMENT_YM_OLDER subpartition_count | 0 high_value | FOR VALUES FROM (MINVALUE, MINVALUE) TO ('2016', '11') high_value_length | 54 partition_position | tablespace_name | pct_free | 0 pct_used | 0 ini_trans | 0 max_trans | 0 initial_extent | next_extent | min_extent | 0 max_extent | 0 pct_increase | 0 freelists | freelist_groups | logging | YES compression | NONE num_rows | 0 blocks | 0 empty_blocks | avg_space | chain_cnt | avg_row_len | sample_size | last_analyzed | buffer_pool | global_stats | YES user_stats | NO backing_table | measurement_ym_older -[ RECORD 2 ]------+------------------------------------------------------- table_owner | ENTERPRISEDB schema_name | PUBLIC table_name | MEASUREMENT_YEAR_MONTH composite | NO partition_name | MEASUREMENT_YM_Y2016M11 subpartition_count | 0 high_value | FOR VALUES FROM ('2016', '11') TO ('2016', '12') high_value_length | 48 partition_position | tablespace_name | pct_free | 0 pct_used | 0 ini_trans | 0 max_trans | 0 initial_extent | next_extent | min_extent | 0 max_extent | 0 pct_increase | 0 freelists | freelist_groups | logging | YES compression | NONE num_rows | 0 blocks | 0 empty_blocks | avg_space | chain_cnt | avg_row_len | sample_size | last_analyzed | buffer_pool | global_stats | YES user_stats | NO backing_table | measurement_ym_y2016m11 -[ RECORD 3 ]------+------------------------------------------------------- table_owner | ENTERPRISEDB schema_name | PUBLIC table_name | MEASUREMENT_YEAR_MONTH composite | NO partition_name | MEASUREMENT_YM_Y2016M12 subpartition_count | 0 high_value | FOR VALUES FROM ('2016', '12') TO ('2017', '1') high_value_length | 47 partition_position | tablespace_name | pct_free | 0 pct_used | 0 ini_trans | 0 max_trans | 0 initial_extent | next_extent | min_extent | 0 max_extent | 0 pct_increase | 0 freelists | freelist_groups | logging | YES compression | NONE num_rows | 0 blocks | 0 empty_blocks | avg_space | chain_cnt | avg_row_len | sample_size | last_analyzed | buffer_pool | global_stats | YES user_stats | NO backing_table | measurement_ym_y2016m12 -[ RECORD 4 ]------+------------------------------------------------------- table_owner | ENTERPRISEDB schema_name | PUBLIC table_name | MEASUREMENT_YEAR_MONTH composite | NO partition_name | MEASUREMENT_YM_Y2017M01 subpartition_count | 0 high_value | FOR VALUES FROM ('2017', '1') TO ('2017', '2') high_value_length | 46 partition_position | tablespace_name | pct_free | 0 pct_used | 0 ini_trans | 0 max_trans | 0 initial_extent | next_extent | min_extent | 0 max_extent | 0 pct_increase | 0 freelists | freelist_groups | logging | YES compression | NONE num_rows | 0 blocks | 0 empty_blocks | avg_space | chain_cnt | avg_row_len | sample_size | last_analyzed | buffer_pool | global_stats | YES user_stats | NO backing_table | measurement_ym_y2017m01 postgres=# postgres=# \d+ measurement_year_month Table "public.measurement_year_month" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+-----------------------------+-----------+----------+---------+---------+--------------+------------- logdate | timestamp without time zone | | not null | | plain | | peaktemp | integer | | | | plain | | unitsales | integer | | | | plain | | Partition key: RANGE (date_part('year'::text, logdate), date_part('month'::text, logdate)) Partitions: measurement_ym_older FOR VALUES FROM (MINVALUE, MINVALUE) TO ('2016', '11'), measurement_ym_y2016m11 FOR VALUES FROM ('2016', '11') TO ('2016', '12'), measurement_ym_y2016m12 FOR VALUES FROM ('2016', '12') TO ('2017', '1'), measurement_ym_y2017m01 FOR VALUES FROM ('2017', '1') TO ('2017', '2') postgres=# |
上面可以看到,原生語法建立的 Partitioned Table 也可以在企業版的 data dictionary 查看得到。這讓 Partitioned Table 狀況查看在企業版更為容易~
此外,兩個 Partitioned Table 語法的內部架構上應該還是有一點點差異,從 pg_partitioned_table 可以略窺一二。
既然這篇要順道紀錄 Partitioned Table 功能,就再紀錄一下以下幾點:
- Oracle 語法利用 Partitioned Name 管理,不會直接看 Table Partitions 名稱;建立 PGSQL 10 partitioned table 則會自動指定 Partitioned Name
- 在 EDB10 裡面,企業版語法提供 List/Range/Hash 三種,而原生的支援 List/Range 兩種(PGSQL 11 將引入 Hash Partitioned Table 支援)
- PGSQL 10 的 Partitioned Table Index 建立目前還是需要逐個對個別 Table Partitions 建立;此外,目前還沒支援類似 Oracle 的 Global Index 這種跨 Partition 的 Unique Constraint 物件
- 目前的 Partitioned Table 還不支援 Upsert 功能(INSERT … ON CONFLICT);也還沒支援更新 Partitioned Key 欄位的資料(涉及資料的跨表格搬遷)
- 查詢上,還是需要調整 constraint_exclusion 為 partition 才能讓查詢把 Partitioned Key 納入執行計畫考量。不過這已經預設好了,其實不用管它~
- 舊方法的 Table Partitioning 還是能用的!先前的幾個 Partitioned Table 管理套件都還是能在 PGSQL 10 使用(例如,pg_partman 或是 pgslice 等擴充套件)
- 在 pgAdmin4 v2.1 裡面,正式支援 Partitioned Table 的資訊。呈現上,只會呈現母表,點開之後,才會看到列舉 Partitions,點右鍵就可以建立 Index。
最後,根據 PostgresWeekly 2018 年一月號電子報發出的訊息,PGSQL 11 的 Partitioned Table 將會改善建立 Index 的功能,可以對母表下達 Index 建立指令,就自動幫所有 Table Partitions 建立完畢;以及更新 Partitioned Key 欄位資料(跨 Table Partition 資料異動)。目前 Partitoined Table 還正在優化中,後續會不斷有更多強化功能(例如,跟 Foreign data Wrapper 整合~),請各位拭目以待。
最後,看來 EDB 企業版與 PGSQL 原生功能的整合性還不錯,功能都不會延遲跟上~
參考資料
舊方法的 Partitioned Table:還是能用的
之前的筆記
艾力克斯札記: PostgreSQL Partition Table
Creating partitions automatically in PostgreSQL – StreamBright – Medium
Handling Very Large Tables in Postgres Using Partitioning | Heroku
EDB 10 企業版手冊
PostgreSQL 10 手冊