PgSQL数据库分区

PgSQL数据库分区 ：分区的意思就是把逻辑上的一个大表分割成物理上的几个小块

分区建立规则：
   1.建立主表：
   2.创建分区继承(inherits)：
   3.定义约束(或者定义索引)：
   4  定义tigger(触发器)：

举例说明：

1。create table

create table articles (
  id serial not null,
  topic varchar(255),
  content text,
  poster varchar(100),
  posttime timestamp
);

2。创建分区

create table articles_q1 (
  check ( posttime >= timestamp '2007-01-01 00:00:01' and postgime < timestamp '2007-04-01 00:00:01')
) inherits (articles);
create table articles_q2 (
  check ( posttime >= timestamp '2007-04-01 00:00:01' and posttime < timestamp '2007-07-01 00:00:01')
) inherits (articles);
create table articles_q3 (
  check ( posttime >= timestamp '2007-07-01 00:00:01' and posttime < timestamp '2007-10-01 00:00:01')
) inherits (articles);
create table articles_q4 (
  check ( posttime >= timestamp '2007-10-01 00:00:01' and posttime < timestamp '2008-01-01 00:00:01')
) inherits (articles);

3。定义主键索引

alter table articles_q1 add primary key (id);
alter table articles_q2 add primary key (id);
alter table articles_q3 add primary key (id);
alter table articles_q4 add primary key (id);

4。创建触发器函数

create function insert_articles() returns trigger as $$
declare
 cur_timestamp timestamp;
begin
 raise exception 'yes';

 exception
  when raise_exception then

  begin
   cur_timestamp := now();
   if cur_timestamp >= timestamp '2007-01-01 00:00:01' and cur_timestamp < timestamp '2007-04-01 00:00:01' then
    insert into articles_q1 (topic,content,poster,posttime) select NEW.topic,NEW.content,NEW.poster,now();
   elsif cur_timestamp >= timestamp '2007-04-01 00:00:01' and cur_timestamp < timestamp '2007-07-01 00:00:01' then
    insert into articles_q2 (topic,content,poster,posttime) select NEW.topic,NEW.content,NEW.poster,now();
   elsif cur_timestamp >= timestamp '2007-07-01 00:00:01' and cur_timestamp < timestamp '2007-10-01 00:00:01' then

   insert into articles_q3 (topic,content,poster,posttime) select NEW.topic,NEW.content,NEW.poster,now();
   else
    insert into articles_q4 (topic,content,poster,posttime) select NEW.topic,NEW.content,NEW.poster,now();
   end if;
  end;

 return null;
end;
$$ language plpgsql;

create trigger tg_insert_articles before insert on articles
  for each row execute procedure insert_articles();

5.测试

insert into articles (topic,content,poster,posttime) values ('topic','content','wo','2007-09-15 00:00:00');
返回:
INSERT 0 0
查看结果：

select * from articles;
显示有一条记录

select * from only articles;
没有任何记录

select * from articles_q3;
有一条记录，表示成功（注意：这里是表示添加数据时是在2007第三季度，如果不是第三季的话注意改变表名）

6.开启约束排除

set constraint_exclusion = on;
explain select count(id) from articles where posttime >= timestamp '2007-09-14 00:00:00';
这样它只会查找q3与q4两个表，关闭后它会查找q1~q4四个表。

本文示例参考：http://www.phpchina.com/2145/viewspace_15265.html