PostgreSQL之分區表(partitioning)

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 p
WHERE 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 CASCADE

CREATE 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 p
WHERE c.tableoid = p.oid

輸出如下：

參考文獻

1 PostgreSQL document