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 varchar;
str_sub_checkval varchar;
BEGIN
	--The triggle func will be exectued only when BEFORE INSERT
	IF TG_OP <> 'INSERT' OR TG_TABLE_NAME <>'base_table_type1' OR TG_WHEN <> 'BEFORE' THEN
		RETURN NULL;
	END IF;
	--Generate Table Name
	str_sub_alarm_time = date_part('year',NEW.alarm_time)::varchar || '_' || 
		CASE WHEN date_part('month',NEW.alarm_time) <10 THEN '0' ELSE '' END
		||date_part('month',NEW.alarm_time)::varchar;
	str_sub_tablename = 'type1_' || str_sub_alarm_time;
	--Check if table not created
	select * from pg_tables where schemaname = 'ams' and tablename=str_sub_tablename 
		into str_sql_cmd;
	IF NOT FOUND THEN
		--Create table Cmd
		str_sql_cmd = '
			CREATE TABLE '||str_sub_tablename||' 
			(
				CONSTRAINT pk_'|| str_sub_tablename||' PRIMARY KEY (id ),
				CONSTRAINT chk_'|| str_sub_tablename||'
				CHECK(date_part(''year''::text, alarm_time) = '||
				 date_part('year',NEW.alarm_time)::varchar||
				 '::double precision AND 
				 date_part(''month''::text, alarm_time) = '||
				 date_part('month',NEW.alarm_time)::varchar||'
				 )
			)
			INHERITS (base_table_type1)
			WITH ( OIDS=FALSE );
			ALTER TABLE '||str_sub_tablename||' OWNER TO pgsql;
			CREATE INDEX idx_'|| str_sub_tablename||'_alarm_time
				ON '|| str_sub_tablename||'
				USING btree (alarm_time );
			';
		EXECUTE str_sql_cmd;
	END IF;
	--insert Data
	str_sql_cmd = 'INSERT INTO '||str_sub_tablename||' 
	 ( id,device_id,alarm_time,alarm_type,other_1,other_2) VALUES (
	 nextval(''serial_id_seq''),$1,$2,$3,$4,$5);
	';
	EXECUTE str_sql_cmd USING
		NEW.device_id,
		NEW.alarm_time,
		NEW.alarm_type,
		NEW.other_1,
		NEW.other_2;
	--return null because main table does not really contain data
	return NULL;
END;
 
	$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION on_insert_base_table_type1()
  OWNER TO pgsql;

3.3.6 创建类型2子表触发器

-- Function: on_insert_base_table_type2()
 
-- DROP FUNCTION on_insert_base_table_type2();
 
CREATE OR REPLACE FUNCTION on_insert_base_table_type2()
  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 varchar;
str_sub_checkval varchar;
BEGIN
	--The triggle func will be exectued only when BEFORE INSERT
	IF TG_OP <> 'INSERT' OR TG_TABLE_NAME <>'base_table_type2' OR TG_WHEN <> 'BEFORE' THEN
		RETURN NULL;
	END IF;
	--Generate Table Name
	str_sub_alarm_time = date_part('year',NEW.alarm_time)::varchar || '_' || 
		CASE WHEN date_part('month',NEW.alarm_time) <10 THEN '0' ELSE '' END
		||date_part('month',NEW.alarm_time)::varchar;
	str_sub_tablename = 'type2_' || str_sub_alarm_time;
	--Check if table not created
	select * from pg_tables where schemaname = 'ams' and tablename=str_sub_tablename 
		into str_sql_cmd;
	IF NOT FOUND THEN
		--Create table Cmd
		str_sql_cmd = '
			CREATE TABLE '||str_sub_tablename||' 
			(
				CONSTRAINT pk_'|| str_sub_tablename||' PRIMARY KEY (id ),
				CONSTRAINT chk_'|| str_sub_tablename||'
				CHECK(date_part(''year''::text, alarm_time) = '||
				 date_part('year',NEW.alarm_time)::varchar||
				 '::double precision AND 
				 date_part(''month''::text, alarm_time) = '||
				 date_part('month',NEW.alarm_time)::varchar||'
				 )
			)
			INHERITS (base_table_type2)
			WITH ( OIDS=FALSE );
			ALTER TABLE '||str_sub_tablename||' OWNER TO pgsql;
			CREATE INDEX idx_'|| str_sub_tablename||'_alarm_time
				ON '|| str_sub_tablename||'
				USING btree (alarm_time );
			';
		EXECUTE str_sql_cmd;
	END IF;
	--insert Data
	str_sql_cmd = 'INSERT INTO '||str_sub_tablename||' 
	 ( id,device_id,alarm_time,alarm_type,other_1,other_2) VALUES (
	 nextval(''serial_id_seq''),$1,$2,$3,$4,$5);
	';
	EXECUTE str_sql_cmd USING
		NEW.device_id,
		NEW.alarm_time,
		NEW.alarm_type,
		NEW.other_1,
		NEW.other_2;
	--return null because main table does not really contain data
	return NULL;
END;
 
	$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION on_insert_base_table_type2()
  OWNER TO pgsql;

3.3.7 为类型1子表设置触发器

CREATE TRIGGER triggle_on_insert_type1
  BEFORE INSERT
  ON base_table_type1
  FOR EACH ROW
  EXECUTE PROCEDURE on_insert_base_table_type1();

3.3.8 为类型2子表设置触发器

CREATE TRIGGER triggle_on_insert_type2
  BEFORE INSERT
  ON base_table_type2
  FOR EACH ROW
  EXECUTE PROCEDURE on_insert_base_table_type2();

3.4 结果测试

• 插入数据

INSERT INTO base_table_type2 (id,device_id,alarm_time,alarm_type,other_1,other_2) VALUES 
(6,'50010800051321000048','2020-06-01 11:22:11.000','002',1,'sbsbsbsbsb')
,(7,'50010800051321000028','2020-07-01 11:22:11.000','002',1,'sbsbsbsbsb')
,(8,'50010800051321000018','2020-08-01 11:22:11.000','002',1,'sbsbsbsbsb')
,(9,'50010800051321000058','2020-09-01 11:22:11.000','002',1,'sbsbsbsbsb')
,(10,'50010800051321000078','2020-10-01 11:22:11.000','002',1,'sbsbsbsbsb')
,(11,'50010800051321000098','2019-06-01 11:22:11.000','002',1,'sbsbsbsbsb')
;

INSERT INTO base_table_type1 (id,device_id,alarm_time,alarm_type,other_1,other_2) VALUES 
(1,'50010800051321000048','2020-06-01 11:22:11.000','001',1,'sbsbsbsbsb')
,(2,'50010800051321000049','2020-07-01 11:22:11.000','001',1,'sbsbsbsbsb')
,(3,'50010800051321000018','2020-08-01 11:22:11.000','001',1,'sbsbsbsbsb')
,(4,'50010800051321000028','2020-09-01 11:22:11.000','001',1,'sbsbsbsbsb')
,(5,'50010800051321000038','2019-06-01 11:22:11.000','001',1,'sbsbsbsbsb')
;

• 表结构示例
  

• select * from base_table
  

• select * from base_table_type1;
  

• select * from base_table_type2;
  

• 查看各表实际大小

SELECT
schemaname as schema,
tablename as table_name,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS size_p,
pg_total_relation_size(schemaname || '.' || tablename) AS siz,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS 表总大小,
pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename) AS 索引大小,
(100*(pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename)))/CASE WHEN pg_total_relation_size(schemaname || '.' || tablename) = 0 THEN 1 ELSE pg_total_relation_size(schemaname || '.' || tablename) END || '%' AS index_pct
FROM pg_tables
ORDER BY siz DESC 

可以看出，base_table,base_table_type1,base_table_type2这三种表实际没有存储任何数据。

• explain select * from base_table where device_id = ‘50010800051321000028’;

主表关联自动索引到了每个子表，base_table_type1/2类似

四、总结

pgsql的表继承有一定的局限性，具体如下。

1. 主表中执行查询，只能搜索到公共属性
2. 需要请求独特的特有参数时，需要到子表再次查询
3. 类别比较多的时候，表会较多
4. 触发器函数会比较复杂

但是在某些业务应用中，如上述告警类等场景中是十分贴合的。