前言
項(xiàng)目中有需求要垂直分表，即按照時間區(qū)間將數(shù)據(jù)拆分到n個表中，PostgreSQL提供了分區(qū)表的功能。分區(qū)表實(shí)際上是把邏輯上的一個大表分割成物理上的幾小塊，提供了很多好處，比如：
1、查詢性能大幅提升
2、刪除歷史數(shù)據(jù)更快
3、可將不常用的歷史數(shù)據(jù)使用表空間技術(shù)轉(zhuǎn)移到低成本的存儲介質(zhì)上
那么什么時候該使用分區(qū)表呢？官方給出的指導(dǎo)意見是：當(dāng)表的大小超過了數(shù)據(jù)庫服務(wù)器的物理內(nèi)存大小則應(yīng)當(dāng)使用分區(qū)表，接下來結(jié)合一個例子具體記錄一下創(chuàng)建分區(qū)表的詳細(xì)過程。
創(chuàng)建分區(qū)表
首先看一下需求，現(xiàn)在有一張日志表，現(xiàn)在需要按表中的操作時間字段（operation_time）分區(qū)，如下圖：
這個需求就是一個典型的按時間創(chuàng)建分區(qū)表，首先看一下步驟：
1.創(chuàng)建父表
2.創(chuàng)建n個子表，每個子表都是繼承于父表
3.定義一個規(guī)則（Rule）或觸發(fā)器（Trigger），把對主表的數(shù)據(jù)插入重定向到合適的分區(qū)表
如上所示，整體的大步驟就分為以上三個，當(dāng)然還可以有一些小的優(yōu)化措施，比如對于每個分區(qū)，在關(guān)鍵字字段上創(chuàng)建一個索引等等。首先來看第一步——創(chuàng)建父表。
在創(chuàng)建分區(qū)表之前應(yīng)當(dāng)先創(chuàng)建一張“父表”，所有分區(qū)表都從它繼承，這個表中沒有數(shù)據(jù)，也不要在這個表上定義任何檢查約束及索引，現(xiàn)在我們就先創(chuàng)建這樣一張表，但之前先建一個序列：
CREATE SEQUENCE "public"."t_sys_log_main_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 99999999
START 1
CACHE 1;
ALTER TABLE "public"."t_sys_log_main_id_seq" OWNER TO "postgres";
接下來創(chuàng)建“父表”，因?yàn)槭侨罩颈?，所以表名命名為“t_sys_log_main”：
CREATE TABLE "public"."t_sys_log_main" (
"id" int4 DEFAULT nextval('t_sys_log_main_id_seq'::regclass) NOT NULL,
"account_affiliation_code" varchar(100) COLLATE "default" NOT NULL,
"account_affiliation" varchar(50) COLLATE "default" NOT NULL,
"operation_time" timestamp(6) NOT NULL,
"operation_key" varchar(2) COLLATE "default" NOT NULL,
"operation_value" varchar(30) COLLATE "default" NOT NULL,
"operation_loginid" varchar(100) COLLATE "default" NOT NULL,
"operation_message" varchar(300) COLLATE "default" NOT NULL,
"operation_ip" varchar(30) COLLATE "default" NOT NULL
)
WITH (OIDS=FALSE)
;
COMMENT ON TABLE "public"."t_sys_log_main" IS '系統(tǒng)日志表';
COMMENT ON COLUMN "public"."t_sys_log_main"."account_affiliation_code" IS '帳號所屬機(jī)構(gòu)代碼';
COMMENT ON COLUMN "public"."t_sys_log_main"."account_affiliation" IS '帳號所屬機(jī)構(gòu)';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_time" IS '操作時間';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_key" IS '操作類型(key)';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_value" IS '操作類型(value)';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_loginid" IS '操作帳號';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_message" IS '操作信息';
COMMENT ON COLUMN "public"."t_sys_log_main"."operation_ip" IS '登錄地址';
ALTER TABLE "public"."t_sys_log_main" ADD PRIMARY KEY ("id");
運(yùn)行以上DDL語句創(chuàng)建父表，創(chuàng)建成功后接下來就可以挨個創(chuàng)建分區(qū)表了，由于每個分區(qū)表都是從父表繼承的，所以分區(qū)表不會增加任何字段，下面我們按需求創(chuàng)建4張分區(qū)子表，分別用于存放9月、10月、11月和12月的日志數(shù)據(jù)：
create table t_sys_log_y2016m09
(CHECK (operation_time >= DATE '2016-09-01' AND operation_time DATE '2016-10-01'))
INHERITS (t_sys_log_main);
create table t_sys_log_y2016m10
(CHECK (operation_time >= DATE '2016-10-01' AND operation_time DATE '2016-11-01'))
INHERITS (t_sys_log_main);
create table t_sys_log_y2016m11
(CHECK (operation_time >= DATE '2016-11-01' AND operation_time DATE '2016-12-01'))
INHERITS (t_sys_log_main);
create table t_sys_log_y2016m12
(CHECK (operation_time >= DATE '2016-12-01' AND operation_time DATE '2017-01-01'))
INHERITS (t_sys_log_main);
如上所示，運(yùn)行完成后即可創(chuàng)建4張分區(qū)子表，在上面的語句中我們添加了一個約束表示只允許插入本月的數(shù)據(jù)，接下來在這4張分區(qū)表的每個分區(qū)鍵上建立索引：
create index t_sys_log_y2016m09_operation_time ON t_sys_log_y2016m09(operation_time);
create index t_sys_log_y2016m10_operation_time ON t_sys_log_y2016m10(operation_time);
create index t_sys_log_y2016m11_operation_time ON t_sys_log_y2016m11(operation_time);
create index t_sys_log_y2016m12_operation_time ON t_sys_log_y2016m12(operation_time);
到此為止我們的分區(qū)表就創(chuàng)建完畢了，接下來需要考慮數(shù)據(jù)插入的問題，如何才能讓不同日期的數(shù)據(jù)自動的插入與其對應(yīng)的分區(qū)子表中呢？有兩種解決方案，分別是：規(guī)則（Rule）和觸發(fā)器（Trigger），相比觸發(fā)器，Rule的開銷更大，所以我在這里就不做過多介紹了，下面直接介紹Trigger的方式。
Trigger通常會結(jié)合自定義函數(shù)（Function）來實(shí)現(xiàn)分區(qū)插入，F(xiàn)unction負(fù)責(zé)根據(jù)條件選擇插入，而Trigger則負(fù)責(zé)Function的自動調(diào)用。首先定義Function，功能很簡單，即根據(jù)日期區(qū)間insert數(shù)據(jù)即可：
CREATE
OR REPLACE FUNCTION sys_log_insert_trigger () RETURNS TRIGGER AS $$
BEGIN
IF (
NEW .operation_time >= DATE '2016-09-01'
AND NEW .operation_time DATE '2016-10-01'
) THEN
INSERT INTO t_sys_log_y2016m09
VALUES
(NEW .*) ;
ELSEIF (
NEW .operation_time >= DATE '2016-10-01'
AND NEW .operation_time DATE '2016-11-01'
) THEN
INSERT INTO t_sys_log_y2016m10
VALUES
(NEW .*) ;
ELSEIF (
NEW .operation_time >= DATE '2016-11-01'
AND NEW .operation_time DATE '2016-12-01'
) THEN
INSERT INTO t_sys_log_y2016m11
VALUES
(NEW .*) ;
ELSEIF (
NEW .operation_time >= DATE '2016-12-01'
AND NEW .operation_time DATE '2017-01-01'
) THEN
INSERT INTO t_sys_log_y2016m12
VALUES
(NEW .*) ;
ELSE
RAISE EXCEPTION 'Date out of range!' ;
END
IF ; RETURN NULL ;
END ; $$ LANGUAGE plpgsql;
最后再創(chuàng)建觸發(fā)器用于執(zhí)行剛才的Function：
CREATE TRIGGER sys_log_insert_trigger BEFORE INSERT ON t_sys_log_main
FOR EACH ROW
EXECUTE PROCEDURE sys_log_insert_trigger();
到這里就全部創(chuàng)建完成了，最后測試一下看看結(jié)果。為了確認(rèn)我們的觸發(fā)器的確觸發(fā)了，我們打開存儲過程的統(tǒng)計開關(guān)，在postgresql.conf中，找到track_functions，改成all：
接下來就可以運(yùn)行幾條測試insert語句來看看是否能把指定的時間記錄分別插入到與其對應(yīng)的分區(qū)子表中，插入之前先看下sys_log_insert_trigger()的統(tǒng)計信息：
可以看到目前沒有統(tǒng)計記錄，接下來插入幾條測試數(shù)據(jù)：
INSERT INTO t_sys_log_main VALUES
(1,'200022', '西安高新第一中學(xué)初中校區(qū)', '2016-9-8 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES
(1,'200023', '西安高新第一中學(xué)初中校區(qū)', '2016-9-12 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES
(1,'200024', '西安高新第一中學(xué)初中校區(qū)', '2016-10-8 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES
(1,'200025', '西安高新第一中學(xué)初中校區(qū)', '2016-11-8 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES
(1,'200026', '西安高新第一中學(xué)初中校區(qū)', '2016-12-8 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
INSERT INTO t_sys_log_main VALUES
(1,'200027', '西安高新第一中學(xué)初中校區(qū)', '2016-12-25 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');
緊接著再看一下sys_log_insert_trigger()的統(tǒng)計信息：
如上圖，可以看出調(diào)用了6次函數(shù)，因?yàn)槲覀儾迦肓?條數(shù)據(jù)，至此分區(qū)表由創(chuàng)建到測試的整個過程就已經(jīng)成功完成了。
總結(jié)
簡單記錄一下PostgreSQL創(chuàng)建分區(qū)表的完整步驟以及注意事項(xiàng)，希望對遇到同樣問題的朋友有所幫助，The End。
您可能感興趣的文章:- PostgreSQL LIST、RANGE 表分區(qū)的實(shí)現(xiàn)方案
- PostgreSQL 創(chuàng)建表分區(qū)
- 淺析postgresql 數(shù)據(jù)庫 TimescaleDB 修改分區(qū)時間范圍
- 利用python為PostgreSQL的表自動添加分區(qū)
- 如何為PostgreSQL的表自動添加分區(qū)
- 淺談PostgreSQL 11 新特性之默認(rèn)分區(qū)
- PostgreSQL之分區(qū)表(partitioning)
- PostgreSQL教程(三)：表的繼承和分區(qū)表詳解
- 淺談PostgreSQL表分區(qū)的三種方式
