postgresql 表分区

在上一篇文章 postgresql 继承特性来理解表分区就很容易了。

postgresql继承特性使得postgresql建立表分区很容易，因为继承特性已经解决了update、select、delete在父表操作时映射到子表的问题，在做表分区时，我们只要解决insert问题就可以了。

先建立各个表：

--建立主表
create table users(
id serial,
name character varying(32),
age integer,
constraint users_primary_key primary key(id)
);

–建立各个分表，继承加上check约束就可以了
–users_0 id<=5 放入这个表


create table users_0 ( check(5>=age),
constraint users_0_primary_key primary key(id)
) inherits(users);
--users_1 10>=age>5 放入这个表
create table users_1 (
check(10>=age and age >5),
constraint users_1_primary_key primary key(id)
) inherits(users);
--users_2 大于10 放在这个表中
create table users_2 (
check(age>10),
constraint users_2_primary_key primary key(id)
) inherits(users);

现在我们建立了一个主表，三个子表，回想一下postgresql继承的特性，在子表上的数据可以被主表 select、update、delete，而现在insert我们需要建立规则，让在主表insert的数据转入各个分表中，当然转入规则也就是我们建立分表同时附带的check检查。

--当insert age <= 5 时，把数据insert into users_0表，注意users_0中check条件也是age<=5

create rule users_insert_0 as on insert to users where(age <= 5) do instead insert into users_0 values(NEW.id,NEW.name,NEW.age);

--同样建立users_1,users_2表的插入规则
-- users_1
create rule users_insert_1 as on insert to users where (age > 5 and age <=10) do instead insert into users_1 values(NEW.id,NEW.name,NEW.age);
-- users_2
create rule users_insert_2 as on insert to users where (age > 10)do instead insert into users_2 values(NEW.id,NEW.name,NEW.age);

现在我们建立了users表的插入规则，我们程序中透明使用分区，对于程序只有users表，而不会存在users_0,users_1,users_2这些分表。我们插入些数据验证我们的配置是否正确。

insert into users(name,age) values(‘daigong’,1);

insert into users(name,age) values(‘reidai’,6);

insert into users(name,age) values(‘kpt00′,12);

然后我们查找users表

select * from users;

现在有三条数据，分别再不同的users表中

接下来查看下查询等操作是否像我预想的一样对对应的表进行扫描：

EXPLAIN ANALYZE select count(*) from users where age<=5;

分析结果：

Aggregate  (cost=76.27..76.28 rows=1 width=0) (actual time=0.086..0.087 rows=1 loops=1)

->  Append  (cost=0.00..74.00 rows=908 width=0) (actual time=0.025..0.041 rows=1 loops=1)

->  Seq Scan on users  (cost=0.00..18.50 rows=227 width=0) (actual time=0.002..0.002 rows=0 loops=1)               Filter: (age <= 5)

->  Seq Scan on users_0 users  (cost=0.00..18.50 rows=227 width=0) (actual time=0.019..0.021 rows=1 loops=1)               Filter: (age <= 5)

->  Seq Scan on users_1 users  (cost=0.00..18.50 rows=227 width=0) (actual time=0.005..0.005 rows=0 loops=1)               Filter: (age <= 5)

->  Seq Scan on users_2 users  (cost=0.00..18.50 rows=227 width=0) (actual time=0.004..0.004 rows=0 loops=1)               Filter: (age <= 5)

Total runtime: 0.180 ms(11 rows)

发现查询的时候，依然扫描了全部子表，这里需要修改一个属性 constraint_exclusion 避免扫描 PostgreSQL 分区表所有分区 ，在文章中所说：

constraint_exclusion 的含义是：当PG生产执行计划时是否考虑表上的约束，这个参数有三个选项：”off,on ,partition” ，

默认参数为 off, 意思不使用表上的 constraint 来生成计划，如果设置成on ,则对所有表生效，生成 PLAN 时会考虑表上的 constraint,

建议设置成 partition,只对分区表生效，从而避免扫描分区表所有分区。

我使用的版本是 8.3 好像只支持 true 、 false，默认为false，设置成true后查询：

 set constraint_exclusion=true

注意1：不要使用自增id作表分区条件，由于第一次我使用自增序列zi导致插入数据库的id不是连续的，而是间隔的（3,10,17….）怀疑为数据库多次计算了序列。

注意2：中途遇到了很多失败，使用 drop table users cascade 语句来级联删除。

EXPLAIN ANALYZE select count(*) from users where age<=5;

Aggregate  (cost=38.14..38.15 rows=1 width=0) (actual time=0.024..0.025 rows=1 loops=1)

->  Append  (cost=0.00..37.00 rows=454 width=0) (actual time=0.012..0.016 rows=1 loops=1)

->  Seq Scan on users  (cost=0.00..18.50 rows=227 width=0) (actual time=0.002..0.002 rows=0 loops=1)               Filter: (age <= 5)

->  Seq Scan on users_0 users  (cost=0.00..18.50 rows=227 width=0) (actual time=0.006..0.007 rows=1 loops=1)               Filter: (age <= 5)

Total runtime: 0.075 ms(7 rows)

这个时候数据库在查询的时候只扫描 users_0表，考虑到了check条件。

问题1：“目前，在主表上的 UPDATE 和 DELETE 命令并不执行约束排除”官方原话,如果进行update操作 update users set age=6 where id = 1；就会出现 new row for relation “users_0″ violates check constraint “users_0_age_check” 错误，这是因为原来age=1再users_0表中，而修改之后该条数据违反了users_0中的check，可能需要update触发器处理，最好找到一个字段，比如说发布时间之类的字段用来做分区条件，不要用经常修改的字段来做。

问题2：刚刚发现rails3.07对postgresql支持有问题，因为rails insert时使用了 insert …returing id，而增加了role …where 的表无法返回 returing，看老外有解决方法，不过没有尝试，具体解决链接

原文地址：http://daigong.sinaapp.com/?p=15