PostgreSql系列(一)分表实践，大数据量下分类型分月存储

数据库的表继承，面向对象的思想也可以应用在数据库中

一、PostgreSQL的表继承

1.1 典型场景

关系型数据库，对于同一类的异构数据存储上会有差异，拿告警数据来说，几十上百种的告警数据，大体分为布控告警类、解析事件告警类、设备类告警、资源类告警、第三方类告警等。

从告警的数据字段来说，告警类数据除包含告警时间，告警设备，告警名称，告警人等公共字段。各个告警类又包含自己特有的字段如布控信息、事件信息等。如果都采用宽表来存储，列的填充非常稀疏，对数据存储和数据检索来说都有不便。

1.2 表继承说明

PostgreSQL采用表继承来解决该问题，使用表继承，可以在主表中存储公共字段，子表中存储专用字段。能够较好的解决上述问题，对于java开发来说理解起来很容易，面向对象的思想。

二、实例描述

有N种告警数据，分别对应type1，type2…type3,各类告警数据各不相同，但是有一些公共属性，告警名称、设备ID、告警类型。每种类型有一些附加数据。

大致要求：

• 每天30W,至少容纳3年的数据
• 每种类型的留存期等支持配置，分类型表最佳
• 读写比100:1,需对关键字段建立索引

三、实践描述

3.1 实现思路

• 总表>>>类型表>>>某月某类型表
• 通用查询>>>>总表
• 差异化查询>>>>类型表
• 实际存储>>>>某月某类型表

3.2 实现步骤

1. 创建告警数据全局唯一ID，所用表id使用这个全局唯一ID
2. 创建总表
3. 创建类型1子表
4. 创建类型2子表
5. 创建类型1子表触发器
6. 创建类型2子表触发器
7. 为类型1子表设置触发器
8. 为类类2子表设置触发器

3.3 具体实现

3.3.1 创建告警数据全局唯一ID，所用表id使用这个全局唯一ID

CREATE SEQUENCE serial_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE serial_id_seq
  OWNER TO pgsql;

3.3.2 告警基本表

承载了告警的公共属性

 CREATE TABLE base_table
(
  id bigint NOT NULL,
  device_id character varying(32),
  alarm_time timestamp with time zone,
  alarm_type character varying(32),
  CONSTRAINT pk_base_table_id PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

ALTER TABLE base_table
  OWNER TO pgsql;
 
CREATE INDEX idx_alarm_time
  ON base_table
  USING btree
  (alarm_time);

3.3.3 type=1的类型1子表

 CREATE TABLE base_table_type1
(
  other_1 integer,
  other_2 varchar,
  CONSTRAINT pk_base_table_type1 PRIMARY KEY (id )
)
INHERITS (base_table)

WITH (
  OIDS=FALSE
);

ALTER TABLE base_table_type1
  OWNER TO pgsql;
 
 
CREATE INDEX idx_base_table_type1_alarm_time
  ON base_table_type1
  USING btree
  (alarm_time );

3.3.4 type=2的类型2子表

 CREATE TABLE base_table_type2
(
  other_1 integer,
  other_2 varchar,
  CONSTRAINT pk_base_table_type2 PRIMARY KEY (id )
)
INHERITS (base_table)

WITH (
  OIDS=FALSE
);

ALTER TABLE base_table_type2
  OWNER TO pgsql;
 
 
CREATE INDEX idx_base_table_type2_alarm_time
  ON base_table_type2
  USING btree
  (alarm_time );

3.3.5 创建类型1子表触发器

-- Function: on_insert_base_table_type1()
 
-- DROP FUNCTION on_insert_base_table_type1();
 
CREATE OR REPLACE FUNCTION on_insert_base_table_type1()
  RETURNS trigger AS
$BODY$
DECLARE 
--Variable Hold subtable name
str_sub_tablename varchar;
--Variable Hold year\month info with timestamle
str_sub_alarm_time varchar;
str_sql_cmd