ITPub博客

首页 > 数据库 > 数据库开发技术 > PostgreSQL分区表应用

PostgreSQL分区表应用

原创 数据库开发技术 作者:LowerMerion 时间:2016-02-18 11:28:12 0 删除 编辑
和各大商用库一样,PostgreSQL也提供分区表的概念,但使用方法有一些不同。
1. 创建
在PG里表分区是通过表继承来实现的,一般都是建立一个主表,里面是空,然后每个分区都去继承它。无论何时,都应保证主表里面是空的。如:
(1)创建主表
create table tbl_partition (
id integer,
name varchar(20),
gender boolean,
join_date date,
dept char(4));
(2)创建分区表
camus=# create table tbl_partition_201211 (
check ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )       
) INHERITS (tbl_partition);
CREATE TABLE
camus=# create table tbl_partition_201212 (
check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )      
) INHERITS (tbl_partition);
CREATE TABLE
camus=# create table tbl_partition_201301 (
check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' )      
) INHERITS (tbl_partition);
CREATE TABLE
(3)分区键上建索引
camus=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);
CREATE INDEX
camus=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date); 
CREATE INDEX
camus=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);    
CREATE INDEX
对于开发人员来说,希望数据库是透明的,只管 insert into tbl_partition。对于数据插向哪个分区,则希望由DB决定。
这点,ORACLE实现了,但是PG不行,需要前期人工处理下。
(4)创建触发器函数
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 FUNCTION
(5)创建触发器
camus=# CREATE TRIGGER insert_tbl_partition_trigger
camus-#     BEFORE INSERT ON tbl_partition
camus-#     FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger();
CREATE TRIGGER


2. 查询
(1)查看
查看所有表:
camus=# \dt
查看主表:
camus=# \d tbl_partition
查看分区表:
camus=# \d tbl_partition_201304
(2)查询数据
可查询各分区表,也可从主表中查到所有数据


3. 管理分区
(1)移除数据/分区
直接drop分区:
drop table tbl_partition_201304;

将分区从分区表中移除,但是保留访问权限:
alter table tbl_partition_201304 no inherit tbl_partition;
和直接DROP 相比,该方式仅仅是使子表脱离了原有的主表,而存储在子表中的数据仍然可以得到访问,因为此时该表已经被还原成一个普通的数据表了。
此时可以对该表进行必要的维护操作,如数据清理、归档等,在完成诸多例行性的操作之后,就可以考虑是直接删除该表(DROP TABLE),
还是先清空该表的数据(TRUNCATE TABLE),之后再让该表重新继承主表。
truncate table tbl_partition_201304;
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. 约束排除
约束排除(Constraint exclusion)是一种查询优化技巧,它改进了用上面方法定义的表分区的性能。
如果没有约束排除(即Constraint exclusion为off),查询会扫描tbl_partition 表中的每一个分区。打开了约束排除之后,
规划器将检查每个分区的约束然后再试图证明该分区不需要被扫描,因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个,它就把该分区从查询规划里排除出去。
如需使用这个功能,需确保postgresql.conf 里的配置参数constraint_exclusion 是打开的(取消注释)。没有这个参数,查询不会按照需要进行优化。


5. VACUUM 或 ANALYZE tbl_partition 只会对主表起作用,要想分析表,需要分别分析每个分区表。

以上类容参考:http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30158432/viewspace-1990459/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-03-10

  • 博文量
    6
  • 访问量
    8662