PostgreSQL之分區表（partitioning）

標籤：style   http   io   ar   os   sp   for   strong   資料   

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的語句。就可以查看了。

執行插入：   

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

輸出如下：

PostgreSQL之分區表（partitioning）