Postgres主从分区表应用

1.实现分区表

（1）创建主表

create table tbl_partition (
id integer,
name varchar(20),
gender boolean,
join_date date,
dept char(4));

（2）创建从表

create table tbl_partition_201211 (
check ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )       
) INHERITS (tbl_partition);

create table tbl_partition_201212 (
check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )      
) INHERITS (tbl_partition);

create table tbl_partition_201301 (
check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' )      
) INHERITS (tbl_partition);

create table tbl_partition_201302 (
check ( join_date >= DATE '2013-02-01' AND join_date < DATE '2013-03-01' )  
) INHERITS (tbl_partition);

create table tbl_partition_201303 (
check ( join_date >= DATE '2013-03-01' AND join_date < DATE '2013-04-01' ) 
) INHERITS (tbl_partition);

create table tbl_partition_201304 (
check ( join_date >= DATE '2013-04-01' AND join_date < DATE '2013-05-01' )  
) INHERITS (tbl_partition);

create table tbl_partition_201305 (
check ( join_date >= DATE '2013-05-01' AND join_date < DATE '2013-06-01' )  
) INHERITS (tbl_partition);

（3）创建从表索引（可以省略）

create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date); 

create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);    

create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date); 

create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date); 

create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date); 

create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date); 

（4）创建触发器

对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。

触发函数

CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
    IF ( NEW.join_date >= DATE '2012-11-01' AND    
         NEW.join_date < DATE '2012-12-01' ) THEN  
        INSERT INTO tbl_partition_201211 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2012-12-01' AND  
            NEW.join_date < DATE '2013-01-01' ) THEN  
        INSERT INTO tbl_partition_201212 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-01-01' AND  
            NEW.join_date < DATE '2013-02-01' ) THEN  
        INSERT INTO tbl_partition_201301 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-02-01' AND  
            NEW.join_date < DATE '2013-03-01' ) THEN  
        INSERT INTO tbl_partition_201302 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-03-01' AND  
            NEW.join_date < DATE '2013-04-01' ) THEN  
        INSERT INTO tbl_partition_201303 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-04-01' AND  
            NEW.join_date < DATE '2013-05-01' ) THEN  
        INSERT INTO tbl_partition_201304 VALUES (NEW.*);
    ELSIF ( NEW.join_date >= DATE '2013-05-01' AND
            NEW.join_date < DATE '2013-06-01' ) THEN
        INSERT INTO tbl_partition_201305 VALUES (NEW.*); 
    ELSE  
        RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';  
    END IF;  
    RETURN NULL;  
END;  
$$  
LANGUAGE plpgsql;

触发器

CREATE TRIGGER insert_tbl_partition_trigger
BEFORE INSERT ON tbl_partition
FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger();

2.测试分区表

（1）添加数据

insert into tbl_partition values (1, 'David', '1', '2013-01-10', 'TS');

insert into tbl_partition values (2, 'Sandy', '0', '2013-02-10', 'TS');

insert into tbl_partition values (3, 'Eagle', '1', '2012-11-01', 'TS');

insert into tbl_partition values (4, 'Miles', '1', '2012-12-15', 'SD');

insert into tbl_partition values (5, 'Simon', '1', '2012-12-10', 'SD');

insert into tbl_partition values (6, 'Rock', '1', '2012-11-10', 'SD');

insert into tbl_partition values (7, 'Peter', '1', '2013-01-11', 'SD');

insert into tbl_partition values (8, 'Sally', '0', '2013-03-10', 'BCSC');

insert into tbl_partition values (9, 'Carrie', '0', '2013-04-02', 'BCSC');

insert into tbl_partition values (10, 'Lee', '1', '2013-01-05', 'BMC');

insert into tbl_partition values (11, 'Nicole', '0', '2012-11-10', 'PROJ');

insert into tbl_partition values (12, 'Renee', '0', '2013-01-10', 'TS');

执行脚本后，数据都自动存在子表中。

（2）测试查询

set constraint_exclusion = on;//确保约束开启
EXPLAIN select * from tbl_partition p where p.join_date < DATE '2013-01-01';

结果

约束排除（Constraint exclusion）是一种查询优化技巧。set constraint_exclusion = on;off是关闭。

查询只在join_date < 2013-01-01的子表中查询，而不会查询所有的子表，对于大数据表速度会快上很多。

3.管理分区

（1）移除分区

简单做法：

drop table tbl_partition_201304;

推荐做法：把子表还原成一个普通数据表

alter table tbl_partition_201304 no inherit tbl_partition;

让表重新继承主表

alter table tbl_partition_201304 inherit tbl_partition;

(2)增加分区

create table tbl_partition_201306 (
check ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' )  
) INHERITS (tbl_partition);

create index tbl_partition_201306_joindate on tbl_partition_201306 (join_date);

说明：创建触发器函数时，最好把插入条件写更未来一点，比如多写十年，这样以后增加新分区时就不需要重新创建触发器函数了，也可以避免一些不必要的错误。

还可以先创建一个普通表，在让表继承主表

4.通过建立规则的方式进行分区

CREATE RULE insert_tbl_partition_201211 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201211 VALUES (NEW.*);

CREATE RULE insert_tbl_partition_201212 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201212 VALUES (NEW.*);

...
    
CREATE RULE insert_tbl_partition_201306 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201306 VALUES (NEW.*);
    
CREATE RULE insert_tbl_partition_201307 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201307 VALUES (NEW.*);
    
CREATE RULE insert_tbl_partition_error_join_date AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-08-01' OR join_date < DATE '2012-11-01' )
DO INSTEAD
    INSERT INTO tbl_partition_error_join_date VALUES (NEW.*);

5.jpa+hibernate 向主从表中插入数据的bug

使用jpa+hibernate 插入数据时，出现：Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1 的错误。

因为hibernate当执行一条插入语句时，会要求数据库返回一个成功插入记录的个数“1”。然而此时，我的操作是向子表插入，而postgre在父表上返回的成功的插入记录数为“0”，这个值与Hibernate设想的值“1”不相同，故抛出异常.

修正方法：在实体类上加上@SQLInsert(sql="INSERT INTO some_table (date_created, some_date, version, id) VALUES (?,?,?,?)",check=ResultCheckStyle.NONE)标签，让hibernate不检测返回值。

参考：http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html