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

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

      实验平台：PostgreSQL 9.1

      实验背景：

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

     设计原则：

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

     数据库结构：

    全局ID 序列:

 

1.     CREATE SEQUENCE serial_id_seq  
2.   INCREMENT 1  
3.   MINVALUE 1  
4.   MAXVALUE 9223372036854775807  
5.   START 1  
6.   CACHE 1;  
7. ALTER TABLE serial_id_seq  
8.   OWNER TO postgres;  

  该序列用于保持全局ID的唯一性。PostgreSQL各个继承表中的主键约束仅仅限于本表，在不想通过
检查条件确保唯一的情况下，可以通过触发器手工从序列获取新的值，以及限制用户修改ID来保
证唯一。基本表（爷爷表），承载了所有机器的共同属性

 

1. CREATE TABLE base_table  
2. (  
3.   id bigint NOT NULL,  
4.   dvalue double precision,  
5.   sample_time timestamp with time zone,  
6.   machine_code character varying(32),  
7.   CONSTRAINT pk_base_table_id PRIMARY KEY (id )  
8. )  
9. WITH (  
10.   OIDS=FALSE  
11. );  
12. ALTER TABLE base_table  
13.   OWNER TO postgres;  
14.   
15. CREATE INDEX idx_sample_time  
16.   ON base_table  
17.   USING btree  
18.   (sample_time );  

下面为机器类型1创建按类型分区子表（爸爸表）

 

1. CREATE TABLE base_table_machine1  
2. (  
3.   max_res integer,  
4.   curr_res integer,  
5.   CONSTRAINT pk_base_table_machine1 PRIMARY KEY (id )  
6. )  
7. INHERITS (base_table)  
8. WITH (  
9.   OIDS=FALSE  
10. );  
11. ALTER TABLE base_table_machine1  
12.   OWNER TO postgres;  
13.   
14.   
15. CREATE INDEX idx_base_table_machine1_sample_time  
16.   ON base_table_machine1  
17.   USING btree  
18.   (sample_time );  

同样，为机器2创建按类型分区子表

 

1. CREATE TABLE base_table_machine2  
2. (  
3.   manu_id character varying(16),  
4.   manu_value character varying(16),  
5.   CONSTRAINT pk_base_table_machine2 PRIMARY KEY (id )  
6. )  
7. INHERITS (base_table)  
8. WITH (  
9.   OIDS=FALSE  
10. );  
11. ALTER TABLE base_table_machine2  
12.   OWNER TO postgres;  
13.   
14. CREATE INDEX idx_base_table_machine2_sample_time  
15.   ON base_table_machine2  
16.   USING btree  
17.   (sample_time );  

其他机器不再赘述。创建完后，我们开始写创建按月分区表的触发器(儿子表)。按月分区会判断
每次插入的数据的时刻，按照月份放到分区表中。如果分区表不存在，则自动创建。这里给出机器1、
机器2 的触发器

 

1. -- Function: on_insert_base_table_machine1()  
2.   
3. -- DROP FUNCTION on_insert_base_table_machine1();  
4.   
5. CREATE OR REPLACE FUNCTION on_insert_base_table_machine1()  
6.   RETURNS trigger AS  
7. $BODY$  
8. DECLARE   
9. --Variable Hold subtable name  
10. str_sub_tablename varchar;  
11. --Variable Hold year\month info with timestamle  
12. str_sub_sample_time varchar;  
13. str_sql_cmd varchar;  
14. str_sub_checkval varchar;  
15. BEGIN  
16.     --The triggle func will be exectued only when BEFORE INSERT  
17.     IF TG_OP <> 'INSERT' OR TG_TABLE_NAME <>'base_table_machine1' OR TG_WHEN <>
     'BEFORE' THEN  
18. 上一个：MongoDB使用mapreduce完成数据迭代
    下一个：Postgres SQL用法摘记

    更多图片编程知识:

    • 
      关于sql数据类型详细介绍
    • 
      ADO.NET与SQL Server
    • 
      ASP.NET2.0+SQL Server2005构建多层应用
    • 
      ubuntu安装postgresql，以及修改账户密码
    • 
      sql优化-提防错误关联
    • 
      使用PLSQL导入导出数据库

    更多SQLServer疑问解答：

    配置MSSQL复制指定快照文件夹提示：不是有效的路径或文件名

    详细解读varchar和Nvarchar区别

    SQL SERVER 2005 同步复制技术

    进程未能大容量复制到表 解决方法

    MSSql实例教程:MSSql数据库同步

    SQLServer2000同步复制技术实现（分发和订阅）

    sqlserver

    SQLSERVER

    测试 sqlserver 最大用户数连接

    写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的

    sqlserver2008的安装问题。

    为什么安装的SQL server 2008我的没有MSSQLSERVER协议啊？

    怎样把exel表里的数据复制到sqlserver表里

    sqlserver数据库主键和外键问题

    sqlserver 中的左表连接查询和右表连接查询有啥不同？有什么用？