PostgreSQL分區表（partitioning）應用實例詳解

前言

項目中有需求要垂直分表，即按照時間區間將數據拆分到n個表中，PostgreSQL提供了分區表的功能。分區表實際上是把邏輯上的一個大表分割成物理上的幾小塊，提供了很多好處，比如：

1、查詢性能大幅提升
2、刪除歷史數據更快
3、可將不常用的歷史數據使用表空間技術轉移到低成本的存儲介質上
那麼什麼時候該使用分區表呢？官方給出的指導意見是：當表的大小超過了數據庫服務器的物理內存大小則應當使用分區表，接下來結合一個例子具體記錄一下創建分區表的詳細過程。

創建分區表

首先看一下需求，現在有一張日志表，現在需要按表中的操作時間字段（operation_time）分區，如下圖：

這個需求就是一個典型的按時間創建分區表，首先看一下步驟：

1.創建父表
2.創建n個子表，每個子表都是繼承於父表
3.定義一個規則（Rule）或觸發器（Trigger），把對主表的數據插入重定向到合適的分區表

如上所示，整體的大步驟就分為以上三個，當然還可以有一些小的優化措施，比如對於每個分區，在關鍵字字段上創建一個索引等等。首先來看第一步——創建父表。

在創建分區表之前應當先創建一張“父表”，所有分區表都從它繼承，這個表中沒有數據，也不要在這個表上定義任何檢查約束及索引，現在我們就先創建這樣一張表，但之前先建一個序列：

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";

接下來創建“父表”，因為是日志表，所以表名命名為“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 '系統日志表';
COMMENT ON COLUMN "public"."t_sys_log_main"."account_affiliation_code" IS '帳號所屬機構代碼';
COMMENT ON COLUMN "public"."t_sys_log_main"."account_affiliation" IS '帳號所屬機構';
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");

運行以上DDL語句創建父表，創建成功後接下來就可以挨個創建分區表了，由於每個分區表都是從父表繼承的，所以分區表不會增加任何字段，下面我們按需求創建4張分區子表，分別用於存放9月、10月、11月和12月的日志數據：

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);

如上所示，運行完成後即可創建4張分區子表，在上面的語句中我們添加了一個約束表示只允許插入本月的數據，接下來在這4張分區表的每個分區鍵上建立索引：

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);

到此為止我們的分區表就創建完畢了，接下來需要考慮數據插入的問題，如何才能讓不同日期的數據自動的插入與其對應的分區子表中呢？有兩種解決方案，分別是：規則（Rule）和觸發器（Trigger），相比觸發器，Rule的開銷更大，所以我在這裡就不做過多介紹了，下面直接介紹Trigger的方式。

Trigger通常會結合自定義函數（Function）來實現分區插入，Function負責根據條件選擇插入，而Trigger則負責Function的自動調用。首先定義Function，功能很簡單，即根據日期區間insert數據即可：

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;

最後再創建觸發器用於執行剛才的Function：

CREATE TRIGGER sys_log_insert_trigger BEFORE INSERT ON t_sys_log_main
FOR EACH ROW
EXECUTE PROCEDURE sys_log_insert_trigger();

到這裡就全部創建完成了，最後測試一下看看結果。為了確認我們的觸發器的確觸發了，我們打開存儲過程的統計開關，在postgresql.conf中，找到track_functions，改成all：

接下來就可以運行幾條測試insert語句來看看是否能把指定的時間記錄分別插入到與其對應的分區子表中，插入之前先看下sys_log_insert_trigger()的統計信息：

可以看到目前沒有統計記錄，接下來插入幾條測試數據：

INSERT INTO t_sys_log_main VALUES 
(1,'200022', '西安高新第一中學初中校區', '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', '西安高新第一中學初中校區', '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', '西安高新第一中學初中校區', '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', '西安高新第一中學初中校區', '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', '西安高新第一中學初中校區', '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', '西安高新第一中學初中校區', '2016-12-25 18:49:26.004', '01', 'xx', 'zhsz_t', 'test！', '127.0.0.1');

緊接著再看一下sys_log_insert_trigger()的統計信息：

如上圖，可以看出調用了6次函數，因為我們插入了6條數據，至此分區表由創建到測試的整個過程就已經成功完成了。

總結

簡單記錄一下PostgreSQL創建分區表的完整步驟以及注意事項，希望對遇到同樣問題的朋友有所幫助，The End。