PostgreSQL-Partition Table

1.Partition Table簡單介紹:
    是針對比較大型的TABLE做分區的功能,
優點:能使TABLE的查詢(Select)效能增加!!效能的增加通常是以倍數計算,
         如果能善用Partition Key能讓查詢SQL變很快(真的快到很有感覺)!!

缺點:會影響到異動(insert,update,delete)TABLE的效能!!此效能下降不會太大!!
         之前測試各家的Partition 功能差不多效能會下降10%左右!!

至於TABLE要多大才要做Partition呢??
  通常我的判斷是只要TABLE大於本機實體記憶體的話就應該做Partition,
或者是一些很常用到查詢的TABLE(筆數很少跟常異動的就比較不適合囉!!)~~
所以資料庫在設計的一開始就應該把Partition Table這個功能考慮進去!!
避免之後要將Non-Partition Table改成Partition Table浪費的時間及停機的次數!!

2.如何建立PostgreSQL  Partition Table!!!
 PostgreSQL  是利用資料庫的CHECK 及RULE 來實現Partition 功能,相較其他幾家資料庫來看是比較麻煩一點的!
以下為建立PostgreSQL  Partition Table的一些流程(以官方文件來說明)!!!
  
   2-0:設定postgresql.conf 設定檔
    把postgresql.conf 設定檔中的constraint exclusion參數打開，
 這樣針對主資料表的查詢，會針對子資料表進行最佳化。
    (SET constraint_exclusion = on;)

   2-1:Create Master Table
CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
);

  2-2:增加非重疊的表約束//以logdate 做為Select條件
CREATE TABLE measurement_yy04mm02 (
    CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);

CREATE TABLE measurement_yy04mm03 (
    CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);

  2-3:設置一個非常簡單的規則來插入數據//以logdate 做為Insert條件
CREATE RULE measurement_insert_yy04mm02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
    INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
CREATE RULE measurement_insert_yy04mm03 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
DO INSTEAD
    INSERT INTO measurement_yy04mm03 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

  2-4:執行SQL,觀察SQL PLAN(以下舉幾個簡單的SQL來觀察)
insert into measurement values(1, DATE'2004-02-02',2,3) \\ insert measurement_insert_yy04mm02

insert into measurement values(1, DATE'2004-03-12',2,3) \\ insert measurement_insert_yy04mm03

insert into measurement values(1, DATE'2004-04-02',2,3)  \\ insert measurement

select * from measurement   \\ scan measurement  && measurement_insert_yy04mm03 && measurement_insert_yy04mm02
select * from measurement 
where logdate >= DATE '2004-02-01' AND logdate < DATE '2004-02-12' \\ scan measurement  && measurement_insert_yy04mm02

select * from measurement 
where logdate >= DATE '2004-03-05' AND logdate < DATE '2004-03-20' \\ scan measurement  && measurement_insert_yy04mm03select * from measurement 
where logdate >= DATE '2004-04-01' AND logdate < DATE '2004-05-20' \\ scan measurement

delete  from measurement   \\ scan measurement  && measurement_insert_yy04mm03 && measurement_insert_yy04mm02
delete  from measurement 
where logdate >= DATE '2004-02-01' AND logdate < DATE '2004-02-12' \\ scan measurement  && measurement_insert_yy04mm02

delete  from measurement 
where logdate >= DATE '2004-03-05' AND logdate < DATE '2004-03-20' \\ scan measurement  && measurement_insert_yy04mm03
delete  from measurement
where logdate >= DATE '2004-04-01' AND logdate < DATE '2004-05-20' \\ scan measurement 
update measurement set city_id=2   \\ scan measurement  && measurement_insert_yy04mm03 && measurement_insert_yy04mm02
update measurement set city_id=2 
where logdate >= DATE '2004-02-01' AND logdate < DATE '2004-02-12' \\ scan measurement  && measurement_insert_yy04mm02

update measurement set city_id=2 
where logdate >= DATE '2004-03-05' AND logdate < DATE '2004-03-20' \\ scan measurement  && measurement_insert_yy04mm03
update measurement set city_id=2  where logdate >= DATE '2004-04-01' AND logdate < DATE '2004-05-20' \\ scan measurement 
  2-5:結論    要實現PostgreSQL  Partition功能,跟其他資料庫比起來操作上有點麻煩(不好用),也不是非常SMART!!
但是基本上有達到Partition的精神!!