EDB Postgres 10 企業版裡面的 Partitioned Table 系統表

EDB-Loader 工具的簡單範例
2018-02-06
Greenplum 如何確認暫存表
2018-02-06

本文授權轉載自渡鴉之丘

在 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。

EDB Postgres 10 企業版裡面的 Partitioned Table 系統表1

最後,根據 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 手冊