PostgreSQL之分區表(partitioning)_PostgreSQL

來源:互聯網
上載者:User

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

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.