PostgreSQL之分區表(partitioning)_PostgreSQL

PostgreSQL有一項非常有用的功能，分區表，或者partitioning。當某個TABLE的記錄非常的多，千萬甚至更多的時候，我們其實需要將他分割成子表。一個龐大的TABLE，就像水果倉庫雜亂無章地堆放著無數的蘋果桃子和桔子，尋找不方便，效能降低，比較合理的做法是將倉庫分成三個子領域，分表放蘋果桃子和桔子。一張大表就變成了三個小表的集合。

通過合理的設計，可以將選擇一定的規則，將大表切分多個不重不漏的子表，這就是傳說中的partitioning。比如，我們可以按時間切分，每天一張子表，比如我們可以按照某其他欄位分割，總之了就是化整為零，提高查詢的效能。

怎麼實現這個分區表的功能呢？
   1 建立大表。
   2 建立分區繼承
   3 定義Rule或者Trigger？

下面根據一個簡單的例子，描述這個過程。我們將學生按照低於60分和不低於60分切分成兩張子表。

   1 建立大表   

CREATE TABLE student (student_id bigserial, name varchar(32), score smallint)

   2 建立分區繼承。

CREATE TABLE student_qualified (CHECK (score >= 60 )) INHERITS (student) ;CREATE TABLE student_nqualified (CHECK (score < 60)) INHERITS (student) ;

建立了兩個分區表，student_qualified和student_nqualified，繼承了大表student的一切欄位，同時設定了約束,即CHECK條件。

3 定義Rule或者Trigger。

   雖然我們定義了CHECK條件，但是往student插入資料時，PostgreSQL並不能根據score是否低於60插入的正確的子表，原因是，你並沒有定義這種規則，來告訴資料這麼做。我們需要定義Rule或者Trigger，將資料插入到正確的分區表。
   先看下Rule的定義：

CREATE OR REPLACE RULE insert_student_qualified AS ON INSERT TO student     WHERE score >= 60    DO INSTEAD    INSERT INTO student_qualified VALUES(NEW.*);CREATE OR REPLACE RULE insert_student_nqualified AS ON INSERT TO student     WHERE score < 60    DO INSTEAD    INSERT INTO student_nqualified VALUES(NEW.*);

這兩個Rule告訴了PostgreSQL，當往總表插資料的時候，如果是score< 60,則插入student_nqualified,如果score>=60,則插入student_qualified.注意了，這個分割一定要不重不漏，如果我們不小心將>=60條件的“=”丟掉，等於60分的記錄將會錄入大表student，不在任何一個分區表中。
我們插入一些記錄：

INSERT INTO student (name,score) VALUES('Jim',77);INSERT INTO student (name,score) VALUES('Frank',56);INSERT INTO student (name,score) VALUES('Bean',88);INSERT INTO student (name,score) VALUES('John',47);INSERT INTO student (name,score) VALUES('Albert','87');INSERT INTO student (name,score) VALUES('Joey','60');

我們看下資料分布情況，是否分布到了正確的的分區表：

SELECT p.relname,c.tableoid,c.* FROM student c, pg_class pWHERE c.tableoid = p.oid

輸出如下：

我們看到，雖然我們插入的是大表，但是資料卻存在了對應的分區子表。符合我們的期望。同時還不影響查詢。

Rule是一個分流的辦法，還有TRIGGER也能做到讓正確的資料流向正確的分區子表。
首先我們定義個function。

CREATE OR REPLACE FUNCTION student_insert_trigger()RETURNS TRIGGER AS $$BEGIN   IF(NEW.score >= 60) THEN     INSERT INTO student_qualified VALUES (NEW.*);   ELSE      INSERT INTO student_nqualified VALUES (NEW.*);   END IF;   RETURN NULL;END;$$LANGUAGE plpgsql ;

然後定義TRIGGER，當插入到student之前，就會觸發trigger：

CREATE TRIGGER insert_student   BEFORE INSERT ON student  FOR EACH row  EXECUTE PROCEDURE student_insert_trigger() ;

我們首先通過刪除TABLE student，測試下trigger方式。

DROP TABLE STUDENT CASCADECREATE TABLE student (student_id bigserial, name varchar(32), score smallint) ;CREATE TABLE student_qualified (CHECK (score >= 60 )) INHERITS (student) ;CREATE TABLE student_nqualified (CHECK (score < 60)) INHERITS (student) ;

然後執行定義FUNCTION和定義TRIGGER的語句。就可以查看了。
為了確認我們的觸發器的確觸發了，我們開啟預存程序的統計開關：
在postgresql.conf中，找到track_functions,改成all

track_functions = all

插入之前先看下function student_insert_trigger的統計資訊：

執行插入：

INSERT INTO student (name,score) VALUES('Jim',77);INSERT INTO student (name,score) VALUES('Frank',56);INSERT INTO student (name,score) VALUES('Bean',88);INSERT INTO student (name,score) VALUES('John',47);INSERT INTO student (name,score) VALUES('Albert','87');INSERT INTO student (name,score) VALUES('Joey','60');

插入後，看下function student_insert_trigger的統計資訊

我們看到trigger觸發了6次。
執行下查詢：

SELECT p.relname,c.tableoid,c.* FROM student c, pg_class pWHERE c.tableoid = p.oid

輸出如下：

參考文獻

1 PostgreSQL document