PostgreSQL之分區(qū)表(partitioning)
PostgreSQL有一項非常有用的功能，分區(qū)表，或者partitioning。當(dāng)某個TABLE的記錄非常的多，千萬甚至更多的時候，我們其實需要將他分割成子表。一個龐大的TABLE，就像水果倉庫雜亂無章地堆放著無數(shù)的蘋果桃子和桔子，查找不方便，性能降低，比較合理的做法是將倉庫分成三個子區(qū)域，分表放蘋果桃子和桔子。一張大表就變成了三個小表的集合。
通過合理的設(shè)計，可以將選擇一定的規(guī)則，將大表切分多個不重不漏的子表，這就是傳說中的partitioning。比如，我們可以按時間切分，每天一張子表，比如我們可以按照某其他字段分割，總之了就是化整為零，提高查詢的效能。
怎么實現(xiàn)這個分區(qū)表的功能呢？
1 建立大表。
2 創(chuàng)建分區(qū)繼承
3 定義Rule或者Trigger？
下面根據(jù)一個簡單的例子，描述這個過程。我們將學(xué)生按照低于60分和不低于60分切分成兩張子表。
1 建立大表
CREATE TABLE student (student_id bigserial, name varchar(32), score smallint)
2 創(chuàng)建分區(qū)繼承。
CREATE TABLE student_qualified (CHECK (score >= 60 )) INHERITS (student) ; CREATE TABLE student_nqualified (CHECK (score < 60)) INHERITS (student) ;
創(chuàng)建了兩個分區(qū)表，student_qualified和student_nqualified，繼承了大表student的一切字段，同時設(shè)定了約束,即CHECK條件。
3 定義Rule或者Trigger。
雖然我們定義了CHECK條件，但是往student插入數(shù)據(jù)時，PostgreSQL并不能根據(jù)score是否低于60插入的正確的子表，原因是，你并沒有定義這種規(guī)則，來告訴數(shù)據(jù)這么做。我們需要定義Rule或者Trigger，將數(shù)據(jù)插入到正確的分區(qū)表。
先看下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，當(dāng)往總表插數(shù)據(jù)的時候，如果是score< 60,則插入student_nqualified,如果score>=60,則插入student_qualified.注意了，這個分割一定要不重不漏，如果我們不小心將>=60條件的“=”丟掉，等于60分的記錄將會錄入大表student，不在任何一個分區(qū)表中。
我們插入一些記錄：
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');
我們看下數(shù)據(jù)分布情況，是否分布到了正確的的分區(qū)表：
SELECT p.relname,c.tableoid,c.* FROM student c, pg_class p WHERE c.tableoid = p.oid
輸出如下：
我們看到，雖然我們插入的是大表，但是數(shù)據(jù)卻存在了對應(yīng)的分區(qū)子表。符合我們的期望。同時還不影響查詢。
Rule是一個分流的辦法，還有TRIGGER也能做到讓正確的數(shù)據(jù)流向正確的分區(qū)子表。
首先我們定義個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，當(dāng)插入到student之前，就會觸發(fā)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) ;
然后執(zhí)行定義FUNCTION和定義TRIGGER的語句。就可以查看了。
為了確認我們的觸發(fā)器的確觸發(fā)了，我們打開存儲過程的統(tǒng)計開關(guān)：
在postgresql.conf中，找到track_functions,改成all
track_functions = all
插入之前先看下function student_insert_trigger的統(tǒng)計信息：
執(zhí)行插入：
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的統(tǒng)計信息
我們看到trigger觸發(fā)了6次。
執(zhí)行下查詢：
SELECT p.relname,c.tableoid,c.* FROM student c, pg_class p WHERE c.tableoid = p.oid
輸出如下：
參考文獻
相關(guān)文章
PostgreSQL數(shù)據(jù)庫如何定期清理歸檔(pg_wal)日志
在?般的?產(chǎn)環(huán)境中,數(shù)據(jù)庫都需要開啟歸檔模式,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL數(shù)據(jù)庫如何定期清理歸檔(pg_wal)日志的相關(guān)資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-05-05
postgresql數(shù)據(jù)庫配置文件postgresql.conf，pg_hba.conf,pg_ident.conf
這篇文章主要為大家介紹了postgresql數(shù)據(jù)庫中三個重要的配置文件postgresql.conf，pg_hba.conf,pg_ident.conf使用示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-02-02
postgresql高級應(yīng)用之合并單元格的思路詳解
這篇文章主要介紹了postgresql高級應(yīng)用之合并單元格,本文給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-05-05
postgresql 循環(huán)函數(shù)的簡單實現(xiàn)操作
這篇文章主要介紹了postgresql 循環(huán)函數(shù)的簡單實現(xiàn)操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 實現(xiàn)修改jsonb字段中的某一個值
這篇文章主要介紹了postgresql 實現(xiàn)修改jsonb字段中的某一個值操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
所謂慢SQL 是指在數(shù)據(jù)庫中執(zhí)行時間超過指定閾值的語句,慢查詢太多,對于業(yè)務(wù)而言,是有很大風(fēng)險的,可能隨時都會因為某種原因而被觸發(fā),本篇文章將介紹 PostgreSQL 慢 SQL 如何定位排查,需要的朋友可以參考下2024-07-07
這篇文章主要介紹了postgresql無則插入,有則更新問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-04-04
這篇文章主要介紹了PostgreSQL教程(十五)：系統(tǒng)表詳解,本文講解了pg_class、pg_attribute、pg_attrdef、pg_authid、pg_auth_members、pg_constraint、pg_tablespace、pg_namespace、pg_database等表的作用和字段介紹,需要的朋友可以參考下2015-05-05
Windows版?PostgreSQL?利用?pg_upgrade?進行大版升級操作方法
最近?PostgreSQL?15?版本正式發(fā)布了，新版本的各種特性和好處本文就不展開介紹了，主要介紹一下?Windows?環(huán)境下?PostgreSQL?大版本升級的方法，我們現(xiàn)在的幾個數(shù)據(jù)庫都是運行在?Windows服務(wù)器的?PostgreSQL?14,需要的朋友可以參考下2022-10-10
這篇文章主要給大家介紹了關(guān)于在Ubuntu中卸載Postgresql出錯的解決方法，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧。2017-09-09