PostgreSQL之分區(qū)表(partitioning)
PostgreSQL有一項(xiàng)非常有用的功能，分區(qū)表，或者partitioning。當(dāng)某個(gè)TABLE的記錄非常的多，千萬(wàn)甚至更多的時(shí)候，我們其實(shí)需要將他分割成子表。一個(gè)龐大的TABLE，就像水果倉(cāng)庫(kù)雜亂無(wú)章地堆放著無(wú)數(shù)的蘋(píng)果桃子和桔子，查找不方便，性能降低，比較合理的做法是將倉(cāng)庫(kù)分成三個(gè)子區(qū)域，分表放蘋(píng)果桃子和桔子。一張大表就變成了三個(gè)小表的集合。
通過(guò)合理的設(shè)計(jì)，可以將選擇一定的規(guī)則，將大表切分多個(gè)不重不漏的子表，這就是傳說(shuō)中的partitioning。比如，我們可以按時(shí)間切分，每天一張子表，比如我們可以按照某其他字段分割，總之了就是化整為零，提高查詢(xún)的效能。
怎么實(shí)現(xiàn)這個(gè)分區(qū)表的功能呢？
1 建立大表。
2 創(chuàng)建分區(qū)繼承
3 定義Rule或者Trigger？
下面根據(jù)一個(gè)簡(jiǎn)單的例子，描述這個(gè)過(guò)程。我們將學(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)建了兩個(gè)分區(qū)表，student_qualified和student_nqualified，繼承了大表student的一切字段，同時(shí)設(shè)定了約束,即CHECK條件。
3 定義Rule或者Trigger。
雖然我們定義了CHECK條件，但是往student插入數(shù)據(jù)時(shí)，PostgreSQL并不能根據(jù)score是否低于60插入的正確的子表，原因是，你并沒(méi)有定義這種規(guī)則，來(lái)告訴數(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.*);
這兩個(gè)Rule告訴了PostgreSQL，當(dāng)往總表插數(shù)據(jù)的時(shí)候，如果是score< 60,則插入student_nqualified,如果score>=60,則插入student_qualified.注意了，這個(gè)分割一定要不重不漏，如果我們不小心將>=60條件的“=”丟掉，等于60分的記錄將會(huì)錄入大表student，不在任何一個(gè)分區(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ù)卻存在了對(duì)應(yīng)的分區(qū)子表。符合我們的期望。同時(shí)還不影響查詢(xún)。
Rule是一個(gè)分流的辦法，還有TRIGGER也能做到讓正確的數(shù)據(jù)流向正確的分區(qū)子表。
首先我們定義個(gè)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之前，就會(huì)觸發(fā)trigger：
CREATE TRIGGER insert_student BEFORE INSERT ON student FOR EACH row EXECUTE PROCEDURE student_insert_trigger() ;
我們首先通過(guò)刪除TABLE student，測(cè)試下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的語(yǔ)句。就可以查看了。
為了確認(rèn)我們的觸發(fā)器的確觸發(fā)了，我們打開(kāi)存儲(chǔ)過(guò)程的統(tǒng)計(jì)開(kāi)關(guān)：
在postgresql.conf中，找到track_functions,改成all
track_functions = all
插入之前先看下function student_insert_trigger的統(tǒng)計(jì)信息：
執(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)計(jì)信息
我們看到trigger觸發(fā)了6次。
執(zhí)行下查詢(xún)：
SELECT p.relname,c.tableoid,c.* FROM student c, pg_class p WHERE c.tableoid = p.oid
輸出如下：
參考文獻(xiàn)
- PostgreSQL 分區(qū)表的實(shí)現(xiàn)示例
- PostgreSQL分區(qū)表的實(shí)現(xiàn)示例
- PostgreSQL12.5中分區(qū)表的一些操作實(shí)例
- PostgreSQL怎么創(chuàng)建分區(qū)表詳解
- PostgreSQL?10分區(qū)表及性能測(cè)試報(bào)告小結(jié)
- PostgreSQL分區(qū)表（partitioning）應(yīng)用實(shí)例詳解
- PostgreSQL教程(三)：表的繼承和分區(qū)表詳解
- PostgreSQL 分區(qū)表的 ALTER TABLE 語(yǔ)句執(zhí)行機(jī)制解析
相關(guān)文章
這篇文章主要介紹了PostgreSQL教程(九)：事物隔離介紹,本文主要針對(duì)讀已提交和可串行化事物隔離級(jí)別進(jìn)行說(shuō)明和比較,需要的朋友可以參考下2015-05-05
PostgreSQL中json數(shù)據(jù)類(lèi)型詳解
json數(shù)據(jù)也可以被存儲(chǔ)為text，但是 與text數(shù)據(jù)類(lèi)型相比，JSON 數(shù)據(jù)類(lèi)型的優(yōu)勢(shì)在于能強(qiáng)制要求每個(gè)被存儲(chǔ)的值符合 JSON 規(guī)則，這篇文章主要介紹了PostgreSQL中json數(shù)據(jù)類(lèi)型,需要的朋友可以參考下2023-04-04
postgresql 中的COALESCE()函數(shù)使用小技巧
這篇文章主要介紹了postgresql 中的COALESCE()函數(shù)使用小技巧，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL是一款簡(jiǎn)介而又性能強(qiáng)大的數(shù)據(jù)庫(kù)應(yīng)用程序,其在日期時(shí)間數(shù)據(jù)方面所支持的功能也都非常給力,下面就來(lái)看一下PostgreSQL中常用的日期時(shí)間腳本使用教程.2016-05-05
這篇文章主要介紹了PostgreSQL 自動(dòng)Vacuum配置方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
postgresql修改完端口后直接psql連接數(shù)據(jù)庫(kù)報(bào)錯(cuò)的解決
這篇文章主要介紹了postgresql修改完端口后直接psql連接數(shù)據(jù)庫(kù)報(bào)錯(cuò)的解決，具有很好的參考價(jià)值，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
Ruoyi從mysql切換到postgresql的幾個(gè)踩坑實(shí)戰(zhàn)
最近由于工作的原因,需要將Ruoyi從mysql切換到postgresql,所以這篇文章主要給大家介紹了關(guān)于Ruoyi從mysql切換到postgresql的幾個(gè)踩坑實(shí)戰(zhàn),需要的朋友可以參考下2023-02-02
Ubuntu中卸載Postgresql出錯(cuò)的解決方法
這篇文章主要給大家介紹了關(guān)于在Ubuntu中卸載Postgresql出錯(cuò)的解決方法，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2017-09-09
這篇文章主要介紹了pgpool復(fù)制和負(fù)載均衡操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
文章介紹了在數(shù)據(jù)庫(kù)中處理日期和時(shí)間類(lèi)型時(shí)的一些注意事項(xiàng),包括如何將字符串轉(zhuǎn)換為日期或時(shí)間類(lèi)型,以及在比較時(shí)自動(dòng)轉(zhuǎn)換的情況,作者建議在使用數(shù)據(jù)庫(kù)時(shí),根據(jù)具體情況進(jìn)行適當(dāng)?shù)念?lèi)型轉(zhuǎn)換,以避免潛在的錯(cuò)誤2025-01-01