二、Partitioning_table
2.1 关于分区表
借助表的继承特性PostgreSQL实现了分区表功能，虽然相比Oracle、MySQL的分区表来说其实现过程比较麻烦，但是这种方式同样能达到分区的效果，而且对大表的查询优化效果很明显。
PostgreSQL的分区表概念与其它数据库的分区表概念相同，都是将逻辑上的一个大表分割成物理上的多个子块。分区带来的不仅仅是访问性能上的提升，而且还可以使管理与维护变得更加方便、简单。
表分区的优点：
-
可以显著提升表的查询性能，尤其是当频繁的查询访问发生在一个单一的分区上时，性能的提升效果将会非常显著;
-
分区减小了每个块上的索引大小，使之前由于整个数据集上的索引过大而无法完全放入内存中导致读写操作产生大量的磁盘访问的问题得到改善;
-
当查询或更新大量发生在单一分区上时，可以通过发挥分区上顺序扫描的优点来提升性能，进而代替使用索引随机访问读取整个表;
-
批量加载或删除可通过添加或删除分区来完成。使用ALTER TABLE NO INHERIT和DROP TABLE远远要比批量操作快得多，此类命令避免了由于批量DELETE而引发的VACUUM额外开销;
-
可以将很少用到的数据移到便宜的、速度慢的存储介质上去。
通常建议当表的大小超过了服务器物理内存的时候创建分区表，但是究竟哪个表在分区策略中受益最终取决于应用程序。
目前，PostgreSQL没有单独的分区表功能，而是通过表继承的方式来实现该功能，每个分区被创建为一个唯一父表的子表，父表中通常不存放数据，它的存在只是为了代表整个数据集。
PostgreSQL目前支持的分区类型：Range Partitioning（范围分区）、List Partitioning（列表分区）
范围分区：表被一个或多个键字字段分区成“范围”，范围中不重叠的数值分布到不同的分区里。
列表分区：明确地列出每个分区里应该出现哪些键字值。
组合分区：将现有分区类型组合使用（先范围再列表）。
2.2 分区表实例
2.2.1 范围分区实例
创建主表：
1part=# create table people(id int not null,name varchar(20) not null,logdate date not null); 2CREATE TABLE
创建分区表：
1part=# create table people_y2013m12( check( logdate >= DATE '2013-12-01' AND logdate < DATE '2014-01-01' ) ) inherits(people); 2CREATE TABLE 3part=# create table people_y2014m01( check( logdate >= DATE '2014-01-01' AND logdate < DATE '2014-02-01' ) ) inherits(people); 4CREATE TABLE 5part=# create table people_y2014m02( check( logdate >= DATE '2014-02-01' AND logdate < DATE '2014-03-01' ) ) inherits(people); 6CREATE TABLE
为分区表创建索引：
1part=# create index people_y2013m12_logdate on people_y2013m12(logdate); 2CREATE INDEX 3part=# create index people_y2014m01_logdate on people_y2014m01(logdate); 4CREATE INDEX 5part=# create index people_y2014m02_logdate on people_y2014m02(logdate); 6CREATE INDEX
建立触发器函数：
1part=# create or replace function people_insert_trigger() 2part-# returns trigger as $$ 3part$# begin 4part$# if (new.logdate >= DATE '2013-12-01' and 5part$# new.logdate < DATE '2014-01-01') then 6part$# insert into people_y2013m12 values (new.*); 7part$# elsif (new.logdate >= DATE '2014-01-01' and 8part$# new.logdate < DATE '2014-02-01') then 9part$# insert into people_y2014m01 values (new.*); 10part$# elsif (new.logdate >= DATE '2014-02-01' and 11part$# new.logdate < DATE '2014-03-01') then 12part$# insert into people_y2014m02 values (new.*); 13part$# else 14part$# RAISE EXCEPTION 'Date out of range. Fix the people_insert_trigger() function!'; 15part$# end if; 16part$# return null; 17part$# end; 18part$# $$ 19part-# language plpgsql; 20CREATE FUNCTION
创建触发器：
1part=# CREATE TRIGGER insert_people_trigger 2part-# BEFORE INSERT ON people 3part-# FOR EACH ROW EXECUTE PROCEDURE people_insert_trigger(); 4CREATE TRIGGER
查看主表信息：
1part=# \d+ people 2 Table "public.people" 3 Column | Type | Modifiers | Storage | Stats target | Description 4---------+-----------------------+-----------+----------+--------------+------------- 5 id | integer | not null | plain | | 6 name | character varying(20) | not null | extended | | 7 logdate | date | not null | plain | | 8Triggers: 9 insert_people_trigger BEFORE INSERT ON people FOR EACH ROW EXECUTE PROCEDURE people_insert_trigger() 10Child tables: people_y2013m12, 11 people_y2014m01, 12 people_y2014m02 13Has OIDs: no
测试：
1part=# insert into people values (1,'lian1','2013-12-10'); 2INSERT 0 0 3part=# insert into people values (2,'lian2','2014-01-10'); 4INSERT 0 0 5part=# insert into people values (3,'lian3','2014-02-10'); 6INSERT 0 0 7part=# insert into people values (4,'lian4','2014-03-10'); 8ERROR: Date out of range. Fix the people_insert_trigger() function! 9STATEMENT: insert into people values (4,'lian4','2014-03-10'); 10ERROR: Date out of range. Fix the people_insert_trigger() function!
查看数据及数据在分区上的具体分布：
1part=# select * from people; 2 id | name | logdate 3----+-------+------------ 4 1 | lian1 | 2013-12-10 5 2 | lian2 | 2014-01-10 6 3 | lian3 | 2014-02-10 7(3 rows) 8part=# SELECT p.relname,c.* FROM people c, pg_class p WHERE c.tableoid = p.oid; 9 relname | id | name | logdate 10-----------------+----+-------+------------ 11 people_y2013m12 | 1 | lian1 | 2013-12-10 12 people_y2014m01 | 2 | lian2 | 2014-01-10 13 people_y2014m02 | 3 | lian3 | 2014-02-10 14(3 rows)
2.2.2 列表分区实例
创建主表：
1list=# create table customer(id int,name varchar(20),city varchar(30)); 2CREATE TABLE
创建分区表：
1list=# create table customer_sd( check(city in ('jinan','qingdao','weifang','zibo')) ) inherits(customer); 2CREATE TABLE 3list=# create table customer_sx( check(city in ('xian','xianyang','weinan','baoji')) ) inherits(customer); 4CREATE TABLE
查看主表结构信息：
1list=# \d+ customer 2 Table "public.customer" 3 Column | Type | Modifiers | Storage | Stats target | Description 4--------+-----------------------+-----------+----------+--------------+------------- 5 id | integer | | plain | | 6 name | character varying(20) | | extended | | 7 city | character varying(30) | | extended | | 8Child tables: customer_sd, 9 customer_sx 10Has OIDs: no
为分区表创建索引：
1list=# create index customer_sd_city on customer_sd(city); 2CREATE INDEX 3list=# create index customer_sx_city on customer_sx(city); 4CREATE INDEX
创建触发器函数：
1list=# create or replace function customer_insert_trigger() 2list-# returns trigger as $$ 3list$# begin 4list$# if (new.city in ('jinan','qingdao','weifang','zibo')) then 5list$# insert into customer_sd values (new.*); 6list$# elsif (new.city in ('xian','xianyang','weinan','baoji')) then 7list$# insert into customer_sx values (new.*); 8list$# else 9list$# RAISE EXCEPTION 'City out of list. Fix the customer_insert_trigger() function!'; 10list$# end if; 11list$# return null; 12list$# end; 13list$# $$ 14list-# language plpgsql; 15CREATE FUNCTION
创建触发器：
1list=# CREATE TRIGGER insert_customer_trigger 2list-# BEFORE INSERT ON customer 3list-# FOR EACH ROW EXECUTE PROCEDURE customer_insert_trigger(); 4CREATE TRIGGER
查看主表结构：
1list=# \d+ customer 2 Table "public.customer" 3 Column | Type | Modifiers | Storage | Stats target | Description 4--------+-----------------------+-----------+----------+--------------+------------- 5 id | integer | | plain | | 6 name | character varying(20) | | extended | | 7 city | character varying(30) | | extended | | 8Triggers: 9 insert_customer_trigger BEFORE INSERT ON customer FOR EACH ROW EXECUTE PROCEDURE customer_insert_trigger() 10Child tables: customer_sd, 11 customer_sx 12Has OIDs: no
插入测试数据：
1list=# insert into customer values (1,'lian1','xian'); 2INSERT 0 0 3list=# insert into customer values (2,'lian2','qingdao'); 4INSERT 0 0 5list=# insert into customer values (3,'lian3','jinan'); 6INSERT 0 0 7list=# insert into customer values (4,'lian4','weinan'); 8INSERT 0 0 9list=# insert into customer values (5,'lian5','xianyang'); 10INSERT 0 0 11list=# insert into customer values (6,'lian6','beijing'); 12ERROR: City out of list. Fix the customer_insert_trigger() function!
查看数据在分区上的分布：
1list=# SELECT p.relname,c.* FROM customer c, pg_class p WHERE c.tableoid = p.oid; 2 relname | id | name | city 3-------------+----+-------+---------- 4 customer_sd | 2 | lian2 | qingdao 5 customer_sd | 3 | lian3 | jinan 6 customer_sx | 1 | lian1 | xian 7 customer_sx | 4 | lian4 | weinan 8 customer_sx | 5 | lian5 | xianyang 9(5 rows)
2.3 分区管理
2.3.1 删除分区
直接删除：
1part=# drop table people_y2013m12; 2DROP TABLE 3part=# select * from people; 4 id | name | logdate 5----+-------+------------ 6 2 | lian2 | 2014-01-10 7 3 | lian3 | 2014-02-10 8(2 rows) 9 10part=# \d+ people 11 Table "public.people" 12 Column | Type | Modifiers | Storage | Stats target | Description 13---------+-----------------------+-----------+----------+--------------+------------- 14 id | integer | not null | plain | | 15 name | character varying(20) | not null | extended | | 16 logdate | date | not null | plain | | 17Triggers: 18 insert_people_trigger BEFORE INSERT ON people FOR EACH ROW EXECUTE PROCEDURE people_insert_trigger() 19Child tables: people_y2014m01, 20 people_y2014m02 21Has OIDs: no
或不删除分区而是仅取消继承关系：
1part=# alter table people_y2014m02 no inherit people; 2ALTER TABLE 3{将成为一个普通表存在，数据依然存在} 4part=# select * from people_y2014m02; 5 id | name | logdate 6----+-------+------------ 7 3 | lian3 | 2014-02-10 8(1 row) 9part=# \d+ people 10 Table "public.people" 11 Column | Type | Modifiers | Storage | Stats target | Description 12---------+-----------------------+-----------+----------+--------------+------------- 13 id | integer | not null | plain | | 14 name | character varying(20) | not null | extended | | 15 logdate | date | not null | plain | | 16Triggers: 17 insert_people_trigger BEFORE INSERT ON people FOR EACH ROW EXECUTE PROCEDURE people_insert_trigger() 18Child tables: people_y2014m01 19Has OIDs: no
取消继承关系的分区同样可以恢复继承关系（或清空数据后再重新继承）：
1part=# alter table people_y2014m02 inherit people; 2ALTER TABLE 3part=# \d+ people 4 Table "public.people" 5 Column | Type | Modifiers | Storage | Stats target | Description 6---------+-----------------------+-----------+----------+--------------+------------- 7 id | integer | not null | plain | | 8 name | character varying(20) | not null | extended | | 9 logdate | date | not null | plain | | 10Triggers: 11 insert_people_trigger BEFORE INSERT ON people FOR EACH ROW EXECUTE PROCEDURE people_insert_trigger() 12Child tables: people_y2014m01, 13 people_y2014m02 14Has OIDs: no 15part=# select * from people; 16 id | name | logdate 17----+-------+------------ 18 2 | lian2 | 2014-01-10 19 3 | lian3 | 2014-02-10 20(2 rows)
2.3.2 添加分区
为people重新添加分区people_y2013m12：
1part=# create table people_y2013m12( check( logdate >= DATE '2013-12-01' AND logdate < DATE '2014-01-01' ) ) inherits(people); 2CREATE TABLE 3part=# create index people_y2013m12_logdate on people_y2013m12(logdate); 4CREATE INDEX 5part=# \d+ people 6 Table "public.people" 7 Column | Type | Modifiers | Storage | Stats target | Description 8---------+-----------------------+-----------+----------+--------------+------------- 9 id | integer | not null | plain | | 10 name | character varying(20) | not null | extended | | 11 logdate | date | not null | plain | | 12Triggers: 13 insert_people_trigger BEFORE INSERT ON people FOR EACH ROW EXECUTE PROCEDURE people_insert_trigger() 14Child tables: people_y2013m12, 15 people_y2014m01, 16 people_y2014m02 17Has OIDs: no 18{people_y2013m12已加入进继承关系中}
注意：若添加一个在触发器函数中未定义过范围的新分区则需要同时修改触发器函数。
建议：一般创建触发器函数时一个好习惯是将条件写得更未来一些（如多写一年或几年的时间），这样可以避免以后在需要创建新分区的时候需要重新创建触发器函数。
另一种添加新分区的方式
{创建一个与主表类似的表 → 为新表增加约束 → 建立继承关系}
语法如下：
1create table t_name (like parent_name including defaults including constraints); 2alter table t_name add constraint constr_name check ( logdate >= DATE '……' AND logdate < DATE '……' ); 3alter table t_name inherit parent_name;
2.3.3 分区表查询优化
通过调整数据库系统参数constraint_exclusion，控制是否检查表约束达到优化查询的效果。
constraint_exclusion = on/off/partition
on:检查所有表中的约束来优化查询
off:不检查表中的约束
partition:只检查继承子表和UNION ALL子句中涉及的约束（默认）
partition（默认）：
1part=# show constraint_exclusion ; 2 constraint_exclusion 3---------------------- 4 partition 5(1 row) 6part=# explain select * from people where logdate >= '2014-01-01'; 7 QUERY PLAN 8---------------------------------------------------------------------------------------------- 9 Append (cost=0.00..39.76 rows=567 width=66) 10 -> Seq Scan on people (cost=0.00..0.00 rows=1 width=66) 11 Filter: (logdate >= '2014-01-01'::date) 12 -> Bitmap Heap Scan on people_y2014m01 (cost=6.34..19.88 rows=283 width=66) 13 Recheck Cond: (logdate >= '2014-01-01'::date) 14 -> Bitmap Index Scan on people_y2014m01_logdate (cost=0.00..6.27 rows=283 width=0) 15 Index Cond: (logdate >= '2014-01-01'::date) 16 -> Bitmap Heap Scan on people_y2014m02 (cost=6.34..19.88 rows=283 width=66) 17 Recheck Cond: (logdate >= '2014-01-01'::date) 18 -> Bitmap Index Scan on people_y2014m02_logdate (cost=0.00..6.27 rows=283 width=0) 19 Index Cond: (logdate >= '2014-01-01'::date) 20(11 rows)
{只扫描符合条件的分区}
on：
1part=# set constraint_exclusion = on; 2SET 3part=# explain select * from people where logdate >= '2014-01-01'; 4 QUERY PLAN 5---------------------------------------------------------------------------------------------- 6 Append (cost=0.00..39.76 rows=567 width=66) 7 -> Seq Scan on people (cost=0.00..0.00 rows=1 width=66) 8 Filter: (logdate >= '2014-01-01'::date) 9 -> Bitmap Heap Scan on people_y2014m01 (cost=6.34..19.88 rows=283 width=66) 10 Recheck Cond: (logdate >= '2014-01-01'::date) 11 -> Bitmap Index Scan on people_y2014m01_logdate (cost=0.00..6.27 rows=283 width=0) 12 Index Cond: (logdate >= '2014-01-01'::date) 13 -> Bitmap Heap Scan on people_y2014m02 (cost=6.34..19.88 rows=283 width=66) 14 Recheck Cond: (logdate >= '2014-01-01'::date) 15 -> Bitmap Index Scan on people_y2014m02_logdate (cost=0.00..6.27 rows=283 width=0) 16 Index Cond: (logdate >= '2014-01-01'::date) 17(11 rows)
{只扫描符合条件的分区}
off：
1part=# set constraint_exclusion = off; 2SET 3part=# explain select * from people where logdate >= '2014-01-01'; 4 QUERY PLAN 5---------------------------------------------------------------------------------------------- 6 Append (cost=0.00..59.64 rows=850 width=66) 7 -> Seq Scan on people (cost=0.00..0.00 rows=1 width=66) 8 Filter: (logdate >= '2014-01-01'::date) 9 -> Bitmap Heap Scan on people_y2014m01 (cost=6.34..19.88 rows=283 width=66) 10 Recheck Cond: (logdate >= '2014-01-01'::date) 11 -> Bitmap Index Scan on people_y2014m01_logdate (cost=0.00..6.27 rows=283 width=0) 12 Index Cond: (logdate >= '2014-01-01'::date) 13 -> Bitmap Heap Scan on people_y2014m02 (cost=6.34..19.88 rows=283 width=66) 14 Recheck Cond: (logdate >= '2014-01-01'::date) 15 -> Bitmap Index Scan on people_y2014m02_logdate (cost=0.00..6.27 rows=283 width=0) 16 Index Cond: (logdate >= '2014-01-01'::date) 17 -> Bitmap Heap Scan on people_y2013m12 (cost=6.34..19.88 rows=283 width=66) 18 Recheck Cond: (logdate >= '2014-01-01'::date) 19 -> Bitmap Index Scan on people_y2013m12_logdate (cost=0.00..6.27 rows=283 width=0) 20 Index Cond: (logdate >= '2014-01-01'::date) 21(15 rows)
{对所有分区进行扫描}
2.4 通过rule重定向实现分区规则
实现分区规则不一定要用触发器，也可通过rule的方式来实现。
创建主表：
1rule=# create table people(id int not null,name varchar(20) not null,logdate date not null); 2CREATE TABLE
创建分区表：
1rule=# create table people_y2013m12( check( logdate >= DATE '2013-12-01' AND logdate < DATE '2014-01-01' ) ) inherits(people); 2CREATE TABLE 3rule=# create table people_y2014m01( check( logdate >= DATE '2014-01-01' AND logdate < DATE '2014-02-01' ) ) inherits(people); 4CREATE TABLE 5rule=# create table people_y2014m02( check( logdate >= DATE '2014-02-01' AND logdate < DATE '2014-03-01' ) ) inherits(people); 6CREATE TABLE
在主表上创建规则：
1rule=# create rule people_insert_y2013m12 as 2rule-# on insert to people where 3rule-# (logdate >= DATE '2013-12-01' AND logdate < DATE '2014-01-01') 4rule-# do instead 5rule-# insert into people_y2013m12 values (new.*); 6CREATE RULE 7rule=# create rule people_insert_y2014m01 as 8rule-# on insert to people where 9rule-# (logdate >= DATE '2014-01-01' AND logdate < DATE '2014-02-01') 10rule-# do instead 11rule-# insert into people_y2014m01 values (new.*); 12CREATE RULE 13rule=# create rule people_insert_y2014m02 as 14rule-# on insert to people where 15rule-# (logdate >= DATE '2014-02-01' AND logdate < DATE '2014-03-01') 16rule-# do instead 17rule-# insert into people_y2014m02 values (new.*); 18CREATE RULE
查看主表信息：
1rule=# \d+ people 2 Table "public.people" 3 Column | Type | Modifiers | Storage | Stats target | Description 4---------+-----------------------+-----------+----------+--------------+------------- 5 id | integer | not null | plain | | 6 name | character varying(20) | not null | extended | | 7 logdate | date | not null | plain | | 8Rules: 9 people_insert_y2013m12 AS 10 ON INSERT TO people 11 WHERE new.logdate >= '2013-12-01'::date AND new.logdate < '2014-01-01'::date DO INSTEAD INSERT INTO people_y2013m12 (id, name, logdate) 12 VALUES (new.id, new.name, new.logdate) 13 people_insert_y2014m01 AS 14 ON INSERT TO people 15 WHERE new.logdate >= '2014-01-01'::date AND new.logdate < '2014-02-01'::date DO INSTEAD INSERT INTO people_y2014m01 (id, name, logdate) 16 VALUES (new.id, new.name, new.logdate) 17 people_insert_y2014m02 AS 18 ON INSERT TO people 19 WHERE new.logdate >= '2014-02-01'::date AND new.logdate < '2014-03-01'::date DO INSTEAD INSERT INTO people_y2014m02 (id, name, logdate) 20 VALUES (new.id, new.name, new.logdate) 21Child tables: people_y2013m12, 22 people_y2014m01, 23 people_y2014m02 24Has OIDs: no
插入测试数据：
1rule=# insert into people values (1,'lian1','2013-12-05'); 2INSERT 0 0 3rule=# insert into people values (2,'lian2','2014-01-05'); 4INSERT 0 0 5rule=# insert into people values (3,'lian3','2014-02-05'); 6INSERT 0 0 7rule=# insert into people values (4,'lian4','2014-03-05'); 8INSERT 0 1 9rule=# SELECT p.relname,c.* FROM people c, pg_class p WHERE c.tableoid = p.oid; 10 relname | id | name | logdate 11-----------------+----+-------+------------ 12 people | 4 | lian4 | 2014-03-05 13 people_y2013m12 | 1 | lian1 | 2013-12-05 14 people_y2014m01 | 2 | lian2 | 2014-01-05 15 people_y2014m02 | 3 | lian3 | 2014-02-05 16(4 rows) 17{不符合条件的数据被插入到了主表中}
rule方式的优缺点：
-
批量插入（非copy）时，rule方式比trigger方式效率高。因为rule方式的额外开销是基于表而非基于行;
-
copy会忽略rule。若要使用copy插入数据并且分区是通过rule方式实现，则需要直接copy到对应的分区表中。而trigger方式则不存在这样的问题;
-
rule方式中不在定义范围内的插入不会报错，而是直接将数据插入到主表中。
2.5 注意事项
分区表
-
由于在创建分区时系统不会自动检查分区条件的冲突性，因此在创建时需要格外注意创建代码的安全;
-
在对表进行update的时候，不能对分区键值进行跨区更新，因为每个分区表上都有check约束，如下：
a=# SELECT p.relname,c.* FROM t c, pg_class p WHERE c.tableoid = p.oid; relname | id | logdate ------------+----+------------ t_y2014m01 | 1 | 2014-01-10 t_y2014m02 | 2 | 2014-02-10 (2 rows) a=# update t set logdate='2014-02-05' where logdate='2014-01-10'; ERROR: new row for relation "t_y2014m01" violates check constraint "t_y2014m01_logdate_check" DETAIL: Failing row contains (1, 2014-02-05). STATEMENT: update t set logdate='2014-02-05' where logdate='2014-01-10'; ERROR: new row for relation "t_y2014m01" violates check constraint "t_y2014m01_logdate_check" DETAIL: Failing row contains (1, 2014-02-05). a=# update t set logdate='2014-01-05' where logdate='2014-01-10'; UPDATE 1 a=# SELECT p.relname,c.* FROM t c, pg_class p WHERE c.tableoid = p.oid; relname | id | logdate ------------+----+------------ t_y2014m01 | 1 | 2014-01-05 t_y2014m02 | 2 | 2014-02-10 (2 rows) a=# update t set logdate='2014-01-05' where logdate='2014-01-10'; UPDATE 1 a=# SELECT p.relname,c.* FROM t c, pg_class p WHERE c.tableoid = p.oid; relname | id | logdate ------------+----+------------ t_y2014m01 | 1 | 2014-01-05 t_y2014m02 | 2 | 2014-02-10 (2 rows) a=# update t_y2014m01 set logdate='2014-02-05' where logdate='2014-01-05'; ERROR: new row for relation "t_y2014m01" violates check constraint "t_y2014m01_logdate_check" DETAIL: Failing row contains (1, 2014-02-05). STATEMENT: update t_y2014m01 set logdate='2014-02-05' where logdate='2014-01-05'; ERROR: new row for relation "t_y2014m01" violates check constraint "t_y2014m01_logdate_check" DETAIL: Failing row contains (1, 2014-02-05).
-
在对表进行VACUUM或ANALYZE操作时，需要在每一个分区表上操作，并非直接对主表操作即可。
约束排除
-
只当查询的where子句中包含约束时有效;
-
CHECK中应避免跨数据类型的比较，因为规划器无法做出正确的判断;
-
由于规划器会对主表的所有分区表上的约束都会进行一次检查，所以过多的分区会需要大量的约束排除检查时间，因此要避免在分区多达成百上千的情况下使用。
PostgreSQL从继承到分区（一）
http://my.oschina.net/lianshunke/blog/205296
PostgreSQL从继承到分区（二）
http://my.oschina.net/lianshunke/blog/205296
PostgreSQL从继承到分区（三）