Postgres分表
这个需求就是一个典型的按时间创建分区表，首先看一下步骤：
- 创建父表
先创建一张“父表”，所有分区表都从它继承，这个表中没有数据，也不要在这个表上定义任何检查约束及索引，现在我们就先创建这样一张表，但之前先建一个序列：
CREATE SEQUENCE "public"."control_alarm_info_uid_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 99999999
START 1
CACHE 1;
ALTER TABLE "public"."control_alarm_info_uid_seq " OWNER TO "postgres";
接下来创建“父表”，
-- Table: control_alarm_info
-- DROP TABLE control_alarm_info;
CREATE TABLE control_alarm_info
(
uid bigserial NOT NULL,
status bigint,
create_time timestamp without time zone,
update_time timestamp without time zone,
creator character varying(32),
store_id integer,
store_name character varying(255),
target_id integer,
person_name character varying(255),
telphone character varying(255),
alarm_time character varying(255),
face_picurl character varying(255),
camera_info_id character varying(255),
camera_name character varying(255),
event_log_id character varying(255),
similarity real,
person_code character varying(64),
alarm_type character varying(255),
bkg_picurl character varying(255),
app_key character varying(64)
)
WITH (
OIDS=FALSE
);
ALTER TABLE control_alarm_info4
OWNER TO postgres;
- 按时间触发，创建n个子表
- 创建n个子表，每个子表都是继承于父表
由于每个分区表都是从父表继承的，所以分区表不会增加任何字段，下面我们按需求创建4张分区子表，分别用于存放9月、10月、11月和12月的日志数据：
create table control_alarm_info_201809
(CHECK (alarm_time >= '2018-09-01' AND alarm_time < '2018-10-01'))
INHERITS (control_alarm_info);
create table control_alarm_info_201810
(CHECK (alarm_time >= '2018-10-01' AND alarm_time < '2018-11-01'))
INHERITS (control_alarm_info);
create table control_alarm_info_201811
(CHECK (alarm_time >= '2018-11-01' AND alarm_time < '2018-12-01'))
INHERITS (control_alarm_info);
接下来在这4张分区表的每个分区键上建立索引：
在上面的语句中我们添加了一个约束表示只允许插入本月的数据，接下来在这4张分区表的每个分区键上建立索引：
create index control_alarm_info_201809_alarm_time ON control_alarm_info_201809 (alarm_time);
create index control_alarm_info_201810_alarm_time ON control_alarm_info_201810 (alarm_time);
create index control_alarm_info_201811_alarm_time ON control_alarm_info_201811(alarm_time);
查询时查询条件中包含这些索引时才会提高查询效率，如果能定位到一张子表内，效率更高。
- 定义一个规则（Rule）或触发器（Trigger），把对主表的数据插入重定向到合适的分区表
如何才能让不同日期的数据自动的插入与其对应的分区子表中呢？有两种解决方案，分别是：规则（Rule）和触发器（Trigger），相比触发器，Rule的开销更大，所以我在这里就不做过多介绍了，下面直接介绍Trigger的方式。
Trigger通常会结合自定义函数（Function）来实现分区插入，Function负责根据条件选择插入，而Trigger则负责Function的自动调用。首先定义Function，功能很简单，即根据日期区间insert数据即可：
Drop trigger control_alarm_info_insert_trigger;
CREATE
OR REPLACE FUNCTION control_alarm_info_insert_trigger () RETURNS TRIGGER AS $$
BEGIN
IF (
NEW .alarm_time >= '2018-09-01'
AND NEW .alarm_time < '2018-10-01'
) THEN
INSERT INTO control_alarm_info_201809
VALUES
(NEW .*) ;
ELSEIF (
NEW .alarm_time >= '2018-10-01'
AND NEW .alarm_time < '2018-11-01'
) THEN
INSERT INTO control_alarm_info_201810
VALUES
(NEW .*) ;
ELSEIF (
NEW .alarm_time >= '2018-11-01'
AND NEW .alarm_time < '2018-12-01'
) THEN
INSERT INTO control_alarm_info_201811
VALUES
(NEW .*) ;
ELSE
RAISE EXCEPTION 'Date out of range!' ;
END
IF ; RETURN NULL ;
END ; $$ LANGUAGE plpgsql;
最后再创建触发器用于执行刚才的Function：
CREATE TRIGGER control_alarm_info_insert_trigger BEFORE INSERT ON control_alarm_info
FOR EACH ROW
EXECUTE PROCEDURE control_alarm_info_insert_trigger();
未分表时200万数据中查询最后一条告警记录,2.505s
分表后200万数据中查询最后一条告警记录,0.221s，提升了10倍
插入性能
未分表时200万数据中插入10万条数据，需要104s