2012年8月9日 星期四

PostgreSQL Partition Table

你曾有維護資料庫時,需要處理複雜的sql,以及繁瑣的程序,花費不少DBA的生命~~
呵呵~~Partition Table可以減輕"部份"維護上的程序,提昇"部份"DBA的價值.
當然Partition已經不是什麼新鮮的技術,在Oracle,Sql Server,Sybase等資料庫己是古老的技術,而PostgreSQL 8.1才開始有(還是老技術),
我呢~呵呵~想了解一下在PostgreSQL也可以Partition.  ^_^
看看對DBA 在 maintain 時會有什麼幫助.

首先是Non-Partition Table(傳統表單),單純的維護單一資料表,因此資料越多越不好處理.



在來是有 Partition 的 Table，明顯的在寫同一個Table時可以分散I/O,所以寫檔時並不會提昇效率,相反的在讀取時,透過適當的Index後,是可以減少查詢範圍以提昇效率.


PostgreSQL的做法其實是使用Table間的繼承,以及搭配rule或trigger,來實現partition的功能.
我覺得 PostgreSQL 只有Partition的功能,而創建Partition Table時很花功夫,維護Table資料時很容易.
呵呵~~"有一好就沒有二好",所以又是要丟銅板(取捨)的問題...
以下是我實作的兩種Partition Table方法,請各位看官不吝指教..

首先要有個 table,表單名稱為 lab01,依據日期時間來區分partition.

CREATE TABLE lab01 (
    id int not null,
    logdate date not null,
    val int
);

1.模式一: 先苦後甘型
   優點：資料整檔時,只需要 drop partition table, 處理速度快
   缺點：每年都需要新建下一年度的 partition table, 需要人工程序處理

CREATE TABLE lab01_201201 (
  CHECK (logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201201 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01')
DO INSTEAD
   INSERT INTO lab01_201201 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );
CREATE TABLE lab01_201202 (
  CHECK (logdate >= DATE '2012-02-01' AND logdate < DATE '2012-03-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201202 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-02-01' AND logdate < DATE '2012-03-01')
DO INSTEAD
   INSERT INTO lab01_201202 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201203 (
  CHECK (logdate >= DATE '2012-03-01' AND logdate < DATE '2012-04-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201203 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-03-01' AND logdate < DATE '2012-04-01')
DO INSTEAD
   INSERT INTO lab01_201203 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201204 (
  CHECK (logdate >= DATE '2012-04-01' AND logdate < DATE '2012-05-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201204 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-04-01' AND logdate < DATE '2012-05-01')
DO INSTEAD
   INSERT INTO lab01_201204 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201205 (
  CHECK (logdate >= DATE '2012-05-01' AND logdate < DATE '2012-06-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201205 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-05-01' AND logdate < DATE '2012-06-01')
DO INSTEAD
   INSERT INTO lab01_201205 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201206 (
  CHECK (logdate >= DATE '2012-06-01' AND logdate < DATE '2012-07-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201206 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-06-01' AND logdate < DATE '2012-07-01')
DO INSTEAD
   INSERT INTO lab01_201206 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201207 (
  CHECK (logdate >= DATE '2012-07-01' AND logdate < DATE '2012-08-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201207 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-07-01' AND logdate < DATE '2012-08-01')
DO INSTEAD
   INSERT INTO lab01_201207 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201208 (
  CHECK (logdate >= DATE '2012-08-01' AND logdate < DATE '2012-09-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201208 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-08-01' AND logdate < DATE '2012-09-01')
DO INSTEAD
   INSERT INTO lab01_201208 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201209 (
  CHECK (logdate >= DATE '2012-09-01' AND logdate < DATE '2012-10-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201209 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-09-01' AND logdate < DATE '2012-10-01')
DO INSTEAD
   INSERT INTO lab01_201209 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201210 (
  CHECK (logdate >= DATE '2012-10-01' AND logdate < DATE '2012-11-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201210 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-10-01' AND logdate < DATE '2012-11-01')
DO INSTEAD
   INSERT INTO lab01_201210 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201211 (
  CHECK (logdate >= DATE '2012-11-01' AND logdate < DATE '2012-12-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201211 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-11-01' AND logdate < DATE '2012-12-01')
DO INSTEAD
   INSERT INTO lab01_201211 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

 CREATE TABLE lab01_201212 (
  CHECK (logdate >= DATE '2012-12-01' AND logdate < DATE '2013-01-01')
) INHERITS (lab01);

 CREATE RULE lab01_insert_201212 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-12-01' AND logdate < DATE '2013-01-01')
DO INSTEAD
   INSERT INTO lab01_201212 VALUES ( NEW.id,
                                     NEW.logdate,
                                     NEW.val );

2.模式二： 先甘後苦型
   優點：partition table 依據月份建立, 無需再維護 partition table
   缺點：資料整檔時, 需要執行刪除的範圍, 查詢效率比沒有 partition table 時快
CREATE TABLE lab01_01 (

  CHECK (extract(month from logdate) = 1)
) INHERITS (lab01);

 CREATE RULE lab01_insert_01 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 1)
DO INSTEAD
   INSERT INTO lab01_01 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );
                                     
CREATE TABLE lab01_02 (
  CHECK (extract(month from logdate) = 2)
) INHERITS (lab01);

 CREATE RULE lab01_insert_02 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 2)
DO INSTEAD
   INSERT INTO lab01_02 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_03 (
  CHECK (extract(month from logdate) = 3)
) INHERITS (lab01);

 CREATE RULE lab01_insert_03 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 3)
DO INSTEAD
   INSERT INTO lab01_03 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_04 (
  CHECK (extract(month from logdate) = 4)
) INHERITS (lab01);

 CREATE RULE lab01_insert_04 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 4)
DO INSTEAD
   INSERT INTO lab01_04 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_05 (
  CHECK (extract(month from logdate) = 5)
) INHERITS (lab01);

 CREATE RULE lab01_insert_05 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 5)
DO INSTEAD
   INSERT INTO lab01_05 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_06 (
  CHECK (extract(month from logdate) = 6)
) INHERITS (lab01);

 CREATE RULE lab01_insert_06 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 6)
DO INSTEAD
   INSERT INTO lab01_06 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_07 (
  CHECK (extract(month from logdate) = 7)
) INHERITS (lab01);

 CREATE RULE lab01_insert_07 AS
ON INSERT TO lab01 WHERE
  (logdate >= DATE '2012-07-01' AND logdate < DATE '2012-08-01')
DO INSTEAD
   INSERT INTO lab01_07 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_08 (
  CHECK (extract(month from logdate) = 8)
) INHERITS (lab01);

 CREATE RULE lab01_insert_08 AS
ON INSERT TO lab01 WHERE
  CHECK (extract(month from logdate) = 8)
DO INSTEAD
   INSERT INTO lab01_08 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_09 (
  CHECK (extract(month from logdate) = 9)
) INHERITS (lab01);

 CREATE RULE lab01_insert_09 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 9)
DO INSTEAD
   INSERT INTO lab01_09 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_10 (
  CHECK (extract(month from logdate) = 10)
) INHERITS (lab01);

 CREATE RULE lab01_insert_10 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 10)
DO INSTEAD
   INSERT INTO lab01_10 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_11 (
  CHECK (extract(month from logdate) = 11)
) INHERITS (lab01);

 CREATE RULE lab01_insert_11 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 11)
DO INSTEAD
   INSERT INTO lab01_11 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

 CREATE TABLE lab01_12 (
  CHECK (extract(month from logdate) = 12)
) INHERITS (lab01);

 CREATE RULE lab01_insert_12 AS
ON INSERT TO lab01 WHERE
  (extract(month from logdate) = 12)
DO INSTEAD
   INSERT INTO lab01_12 VALUES ( NEW.id,
                                 NEW.logdate,
                                 NEW.val );

3. Partition 測試：新增測試資料
INSERT INTO lab01 VALUES( 1,DATE'01-MAY-81 00:00:00',99);
INSERT INTO lab01 VALUES( 2,DATE'23-MAY-87 00:00:00',89);
INSERT INTO lab01 VALUES( 3,DATE'23-JAN-82 00:00:00',79);
INSERT INTO lab01 VALUES( 4,DATE'20-FEB-81 00:00:00',69);
INSERT INTO lab01 VALUES( 5,DATE'22-FEB-81 00:00:00',59);
INSERT INTO lab01 VALUES( 6,DATE'02-APR-81 00:00:00',49);
INSERT INTO lab01 VALUES( 7,DATE'19-APR-87 00:00:00',39);
INSERT INTO lab01 VALUES( 8,DATE'09-JUN-81 00:00:00',29);
INSERT INTO lab01 VALUES( 9,DATE'28-SEP-81 00:00:00',19);
INSERT INTO lab01 VALUES(10,DATE'08-SEP-81 00:00:00',09);
INSERT INTO lab01 VALUES(11,DATE'17-NOV-81 00:00:00',01);
INSERT INTO lab01 VALUES(12,DATE'17-MAY-80 00:00:00',11);
INSERT INTO lab01 VALUES(13,DATE'03-MAY-12 00:00:00',21);
INSERT INTO lab01 VALUES(14,DATE'03-JAN-11 00:00:00',31);
INSERT INTO lab01 VALUES(15,DATE'03-FEB-12 00:00:00',41);
INSERT INTO lab01 VALUES(16,DATE'03-FEB-11 00:00:00',51);
INSERT INTO lab01 VALUES(17,DATE'03-APR-10 00:00:00',61);
INSERT INTO lab01 VALUES(18,DATE'03-APR-10 00:00:00',71);
INSERT INTO lab01 VALUES(19,DATE'03-JUN-12 00:00:00',81);
INSERT INTO lab01 VALUES(20,DATE'03-SEP-12 00:00:00',91);

驗證模式一
select * from lab01_201202;
   id | logdate | val
----+--------------------+-----
  15 | 03-FEB-12 00:00:00 | 41

select * from lab01_201205;
   id | logdate | val
----+--------------------+-----
  13 | 03-MAY-12 00:00:00 | 21


select * from lab01_201206;
   id | logdate | val
----+--------------------+-----
  19 | 03-JUN-12 00:00:00 | 81

select * from lab01_201209;
   id | logdate | val
----+--------------------+-----
  20 | 03-SEP-12 00:00:00 | 91

select * from lab01;
   id | logdate | val
----+--------------------+-----
    1 | 01-MAY-81 00:00:00 | 99
    2 | 23-MAY-87 00:00:00 | 89
    3 | 23-JAN-82 00:00:00 | 79
    4 | 20-FEB-81 00:00:00 | 69
    5 | 22-FEB-81 00:00:00 | 59
    6 | 02-APR-81 00:00:00 | 49
    7 | 19-APR-87 00:00:00 | 39
    8 | 09-JUN-81 00:00:00 | 29
    9 | 28-SEP-81 00:00:00 | 19
  10 | 08-SEP-81 00:00:00 | 9
  11 | 17-NOV-81 00:00:00 | 1
  12 | 17-MAY-80 00:00:00 | 11
  13 | 03-MAY-12 00:00:00 | 21
  14 | 03-JAN-11 00:00:00 | 31
  15 | 03-FEB-12 00:00:00 | 41
  16 | 03-FEB-11 00:00:00 | 51
  17 | 03-APR-10 00:00:00 | 61
  18 | 03-APR-10 00:00:00 | 71
  19 | 03-JUN-12 00:00:00 | 81
  20 | 03-SEP-12 00:00:00 | 91

缺點出現了，當沒有規劃到的 check constraint (年月)時，資料將會寫入 lab01 的空間.

驗證模式二
select * from lab01_01;
   id | logdate | val
----+--------------------+-----
    3 | 23-JAN-82 00:00:00 | 79
  14 | 03-JAN-11 00:00:00 | 31

select * from lab01_02;
   id | logdate | val
----+--------------------+-----
    4 | 20-FEB-81 00:00:00 | 69
    5 | 22-FEB-81 00:00:00 | 59
  15 | 03-FEB-12 00:00:00 | 41
  16 | 03-FEB-11 00:00:00 | 51

select * from lab01_04;
   id | logdate | val
----+--------------------+-----
    6 | 02-APR-81 00:00:00 | 49
    7 | 19-APR-87 00:00:00 | 39
  17 | 03-APR-10 00:00:00 | 61
  18 | 03-APR-10 00:00:00 | 71

select * from lab01_05;
   id | logdate | val
----+--------------------+-----
    1 | 01-MAY-81 00:00:00 | 99
    2 | 23-MAY-87 00:00:00 | 89
  12 | 17-MAY-80 00:00:00 | 11
  13 | 03-MAY-12 00:00:00 | 21

select * from lab01_06;
   id | logdate | val
----+--------------------+-----
    8 | 09-JUN-81 00:00:00 | 29
  19 | 03-JUN-12 00:00:00 | 81

select * from lab01_09;
   id | logdate | val
----+--------------------+-----
    9 | 28-SEP-81 00:00:00 | 19
  10 | 08-SEP-81 00:00:00 | 9
  20 | 03-SEP-12 00:00:00 | 91

select * from lab01_11;
   id | logdate | val
----+--------------------+-----
  11 | 17-NOV-81 00:00:00 | 1

如果模式一及模式二都建置時,結果有24筆,也就是說partition都有使用到,也造成資料重覆寫入.
select * from lab01 order by id;
   id | logdate | val
----+--------------------+-----
    1 | 01-MAY-81 00:00:00 | 99
    2 | 23-MAY-87 00:00:00 | 89
    3 | 23-JAN-82 00:00:00 | 79
    4 | 20-FEB-81 00:00:00 | 69
    5 | 22-FEB-81 00:00:00 | 59
    6 | 02-APR-81 00:00:00 | 49
    7 | 19-APR-87 00:00:00 | 39
    8 | 09-JUN-81 00:00:00 | 29
    9 | 28-SEP-81 00:00:00 | 19
  10 | 08-SEP-81 00:00:00 | 9
  11 | 17-NOV-81 00:00:00 | 1
  12 | 17-MAY-80 00:00:00 | 11
  13 | 03-MAY-12 00:00:00 | 21
  13 | 03-MAY-12 00:00:00 | 21
  14 | 03-JAN-11 00:00:00 | 31
  15 | 03-FEB-12 00:00:00 | 41
  15 | 03-FEB-12 00:00:00 | 41
  16 | 03-FEB-11 00:00:00 | 51
  17 | 03-APR-10 00:00:00 | 61
  18 | 03-APR-10 00:00:00 | 71
  19 | 03-JUN-12 00:00:00 | 81
  19 | 03-JUN-12 00:00:00 | 81
  20 | 03-SEP-12 00:00:00 | 91
  20 | 03-SEP-12 00:00:00 | 91

結語：Partition 非萬靈丹藥, 藥效 還取決於使用方式,所以請各位看官斟酌使用...
於
標籤：

1 則留言:

訂閱： 張貼留言 (Atom)

HDB 擴充套件 PXF (Pivotal Extension Framework) 應用

在Hadoop生態圈裡, 雖然擁有眾多生態系統工具, 但大多用戶仍然需要的是SQL的操作介面, Pivotal HDB 提供一個在Hadoop上運行的標準SQL引擎介面, 箇中好處開發者最能感受, 但本篇重點不在這, 哈哈~ 主要分享在HDB上可以輕易整合外部資料的應用. ...

  • EnterpriseDB Oracle Compatibility 分享 (3) - update multiple columns
    Update Multiple Columns 通常我們在執行多個欄位更新時, 我們的 SQL 大多會以下列語法來執行, UPDATE tablename SET col1 = 'val1' , col2 = 'val2' WHERE co...
  • 檔案外部表應用
    資料載入的另一方式, 透過查詢檔案外部表, 可以直接應用外部檔案. 使用情境為載入資料檔及資料轉換,資料轉換部份為轉換日期格式,以下是實作的方式: 1. 準備一個外部檔 test.dat ,內容如下: 2. 建立延伸套件 file_fdw     3. ...
  • PostgreSQL Partition Table
    你曾有維護資料庫時,需要處理複雜的sql,以及繁瑣的程序,花費不少DBA的生命~~ 呵呵~~Partition Table可以減輕"部份"維護上的程序,提昇"部份"DBA的價值. 當然Partition已經不是什麼新鮮的技術,在Orac...