PostgreSQL 强大的多层表继承--及其在海量数据分类按月分区存储中的应用

概述最近发现大家越来越关注 PostgreSQL了。2008年以来，通过对PostgreSQL的实际使用，发现其对象-关系数据库机制对现实问题非常有帮助。在多重表继承下，对上亿条不同类别的数据条目进行按型号、按月份双层分区管理，既可在总表查阅所有条目的共有字段，也可在各类型字表查询附加字段，非常高效。下面把这种分区机制介绍如下！       实验平台：PostgreSQL 9.1       实验背景 最近发现大家越来越关注 Postgresql了。2008年以来，通过对Postgresql的实际使用，发现其对象-关系数据库机制对现实问题非常有帮助。在多重表继承下，对上亿条不同类别的数据条目进行按型号、按月份双层分区管理，既可在总表查阅所有条目的共有字段，也可在各类型字表查询附加字段，非常高效。下面把这种分区机制介绍如下！

实验平台：Postgresql 9.1

实验背景：

假设有N种数据收集设备，分别叫做 machine1,machine2...machineN,各类收集设备从传感器上采集的数据各不相同。但是他们都包括3个共有属性：1、采集时刻 2、一个电压值 3、机器的ID。 这些机器源源不断的从各个传感器收集信息，每类机器还有各自不同的附加数据。比如，machine1有当前最大单元数、当前已使用单元数两个属性。Machine2有前端传感器的ID和取值。数据量约100万条/天，要求数据库容纳至少5年的数据。

设计原则：

由于采集的频率高，每天会有上百万条数据存入，为了考虑缩小索引的规模，提高检索效率，采用按月分区存储。由于各类机器的字段各有区别，使得我们必须设计不同的表结构， 分别存储各类数据。由于要求能够统一检索基本信息、按需检索额外信息，我们采用Postgresql的表继承，首先按照机器类型分类，而后各类型机器内按照月份分类。

数据库结构：

全局ID 序列:

[sql] view plain copy CREATESEQUENCEserial_ID_seq INCREMENT1 MINVALUE1 MAXVALUE9223372036854775807 START1 CACHE1; ALTERtableserial_ID_seq OWNERTOpostgres;
该序列用于保持全局ID的唯一性。Postgresql各个继承表中的主键约束仅仅限于本表，在不想通过检查条件确保唯一的情况下，可以通过触发器手工从序列获取新的值，以及限制用户修改ID来保证唯一。基本表（爷爷表），承载了所有机器的共同属性 [sql] view plain copy CREATEtablebase_table ( IDbigintNOTNulL, dvaluedoubleprecision, sample_timetimestampwithtimezone, machine_codecharactervarying(32), CONSTRAINTpk_base_table_IDPRIMARYKEY(ID) ) WITH( OIDS=FALSE ); ALTERtablebase_table OWNERTOpostgres; CREATEINDEXIDx_sample_time ONbase_table USINGbtree (sample_time);
下面为机器类型1创建按类型分区子表（爸爸表） [sql] view plain copy CREATEtablebase_table_machine1 ( max_resinteger, curr_resinteger, CONSTRAINTpk_base_table_machine1PRIMARYKEY(ID) ) inheritS(base_table) WITH( OIDS=FALSE ); ALTERtablebase_table_machine1 OWNERTOpostgres; CREATEINDEXIDx_base_table_machine1_sample_time ONbase_table_machine1 USINGbtree (sample_time);
同样，为机器2创建按类型分区子表 [sql] view plain copy CREATEtablebase_table_machine2 ( manu_IDcharactervarying(16), manu_valuecharactervarying(16), CONSTRAINTpk_base_table_machine2PRIMARYKEY(ID) ) inheritS(base_table) WITH( OIDS=FALSE ); ALTERtablebase_table_machine2 OWNERTOpostgres; CREATEINDEXIDx_base_table_machine2_sample_time ONbase_table_machine2 USINGbtree (sample_time);
其他机器不再赘述。创建完后，我们开始写创建按月分区表的触发器(儿子表)。按月分区会判断每次插入的数据的时刻，按照月份放到分区表中。如果分区表不存在，则自动创建。这里给出机器1、机器2 的触发器 [sql] view plain copy --Function:on_insert_base_table_machine1() --DROPFUNCTIONon_insert_base_table_machine1(); CREATEORREPLACEFUNCTIONon_insert_base_table_machine1() RETURNStriggerAS $BODY$ DECLARE --VariableHoldsubtablename str_sub_tablenamevarchar; --VariableHoldyear\monthinfowithtimestamle str_sub_sample_timevarchar; str_sql_cmdvarchar; str_sub_checkvalvarchar; BEGIN --ThetrigglefuncwillbeexectuedonlywhenBEFOREINSERT IFTG_OP<>'INSERT'ORTG_table_name<>'base_table_machine1'ORTG_WHEN<>'BEFORE'THEN RETURNNulL; ENDIF; --Generatetablename str_sub_sample_time=date_part('year',NEW.sample_time)::varchar||'_'|| CASEWHENdate_part('month',NEW.sample_time)<10THEN'0'ELSE''END ||date_part('month',NEW.sample_time)::varchar; str_sub_tablename='machine1_'||str_sub_sample_time; --Checkiftablenotcreated select*frompg_tableswhereschemaname='public'andtablename=str_sub_tablename intostr_sql_cmd; IFNOTFOUNDTHEN --CreatetableCmd str_sql_cmd=' CREATEtable'||str_sub_tablename||' ( CONSTRAINTpk_'||str_sub_tablename||'PRIMARYKEY(ID), CONSTRAINTchk_'||str_sub_tablename||' CHECK(date_part(''year''::text,sample_time)='|| date_part('year',NEW.sample_time)::varchar|| '::doubleprecisionAND date_part(''month''::text,sample_time)='|| date_part('month',NEW.sample_time)::varchar||' ) ) inheritS(base_table_machine1) WITH(OIDS=FALSE); ALTERtable'||str_sub_tablename||'OWNERTOpostgres; CREATEINDEXIDx_'||str_sub_tablename||'_sample_time ON'||str_sub_tablename||' USINGbtree(sample_time); '; EXECUTEstr_sql_cmd; ENDIF; --insertData str_sql_cmd='INSERTINTO'||str_sub_tablename||' (ID,dvalue,sample_time,machine_code,max_res,curr_res)VALUES( nextval(''serial_ID_seq''),,,,,); '; EXECUTEstr_sql_cmdUSING NEW.dvalue, NEW.sample_time, NEW.machine_code, NEW.max_res, NEW.curr_res; --returnnullbecausemaintabledoesnotreallycontaindata returnNulL; END; $BODY$ LANGUAGEplpgsqlVolATILE COST100; ALTERFUNCTIONon_insert_base_table_machine1() OWNERTOpostgres;
[sql] view plain copy --Function:on_insert_base_table_machine2() --DROPFUNCTIONon_insert_base_table_machine2(); CREATEORREPLACEFUNCTIONon_insert_base_table_machine2() RETURNStriggerAS $BODY$ DECLARE --VariableHoldsubtablename str_sub_tablenamevarchar; --VariableHoldyear\monthinfowithtimestamle str_sub_sample_timevarchar; str_sql_cmdvarchar; str_sub_checkvalvarchar; BEGIN --ThetrigglefuncwillbeexectuedonlywhenBEFOREINSERT IFTG_OP<>'INSERT'ORTG_table_name<>'base_table_machine2'ORTG_WHEN<>'BEFORE'THEN RETURNNulL; ENDIF; --Generatetablename str_sub_sample_time=date_part('year',NEW.sample_time)::varchar; str_sub_tablename='machine2_'||str_sub_sample_time; --Checkiftablenotcreated select*frompg_tableswhereschemaname='public'andtablename=str_sub_tablename intostr_sql_cmd; IFNOTFOUNDTHEN --CreatetableCmd str_sql_cmd=' CREATEtable'||str_sub_tablename||' ( CONSTRAINTpk_'||str_sub_tablename||'PRIMARYKEY(ID),NEW.sample_time)::varchar||' ) ) inheritS(base_table_machine2) WITH(OIDS=FALSE); ALTERtable'||str_sub_tablename||'OWNERTOpostgres; CREATEINDEXIDx_'||str_sub_tablename||'_sample_time ON'||str_sub_tablename||' USINGbtree(sample_time); '; EXECUTEstr_sql_cmd; ENDIF; --insertData str_sql_cmd='INSERTINTO'||str_sub_tablename||' (ID,manu_ID,manu_value)VALUES( nextval(''serial_ID_seq''), NEW.manu_ID, NEW.manu_value; --returnnullbecausemaintabledoesnotreallycontaindata returnNulL; END; $BODY$ LANGUAGEplpgsqlVolATILE COST100; ALTERFUNCTIONon_insert_base_table_machine2() OWNERTOpostgres;
最后，为各个爸爸表设置触发器 [sql] view plain copy CREATETRIGGERtriggle_on_insert_machine1 BEFOREINSERT ONbase_table_machine1 FOREACHROW EXECUTEPROCEDUREon_insert_base_table_machine1();
[sql] view plain copy CREATETRIGGERtriggle_machine2 BEFOREINSERT ONbase_table_machine2 FOREACHROW EXECUTEPROCEDUREon_insert_base_table_machine2();
到此为止，我们可以分别向各个爸爸表（按类型分区表）插入数据，而后通过爷爷表（总表）检索基本信息，通过爸爸表检索详细信息。对总表的 *** 作会遍历反馈到所有子表，试图利用子表的索引进行查询。由于按月存储，插入工作只限于本月，所以检索历史数据效率很高。

当然了，这只是简单的实验，实际字段要比上述字段复杂很多。Postgresql的对象-关系数据库对解决上述问题非常有帮助，也全面的运用到我公司的各个环节，达到工业化标准的系统非常稳定，尽管设置了备份，但4年来从未真正用到。我们目前使用 16核心机架服务器，8GB内存，Ubuntu 12.04 LTS，优化配置(Postgresql.conf) 采用设置共享段shared_buffers 512MB,work_mem 32MB，维护maintenance_work_mem 512MB，checkpoint_segments = 16，获得了稳定而持久的生产力提升。

测试：

插入4条数据

[sql] view plain copy insertintobase_table_machine1(dvalue,curr_res)values(22.17273,'2012-06-0111:22:11','SC3010-192.168.1.12',1,2); insertintobase_table_machine1(dvalue,curr_res)values(12.8273,'2012-07-1210:23:01','SC3010-192.168.1.14',2); insertintobase_table_machine2(dvalue,manu_value)values(4412.1928,'2011-01-2102:08:34','PK937-192.168.1.113','TP1','E54DF'); insertintobase_table_machine2(dvalue,'2011-12-3104:21:31','PK937-192.168.1.112','TP2','CB67D');

看看 select 语句的结果

select * from base_table;

select * from base_table_machine2;

select * from base_table_machine1;

explain select * from base_table where sample_time >='2012-06-21 00:00:00' and sample_time <='2012-07-21 00:00:00';

自动使用索引关联到每个子表。

总结

以上是内存溢出为你收集整理的PostgreSQL 强大的多层表继承--及其在海量数据分类按月分区存储中的应用全部内容，希望文章能够帮你解决PostgreSQL 强大的多层表继承--及其在海量数据分类按月分区存储中的应用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: https://outofmemory.cn/sjk/1176454.html