一、简介

　　在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，Postgresql也不例外。

　　Postgressql分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。

　　分区的具体好处是：

某些类型的查询性能可以得到极大提升。
更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
批量删除可以用简单的删除某个分区来实现。
可以将很少用的数据移动到便宜的、转速慢的存储介质上。

　　在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。

　　小表分区不实际，表在多大情况下才考虑分区呢？Postgressql官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。

　　PG目前（9.2.2）仅支持范围分区和列表分区，尚未支持散列分区。

二、环境

系统环境：CentOS release 6.3 (Final)

Postgresql版本：Postgresql 9.2.2 on x86_64-unknown-linux-gnu,compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4),64-bit

三、实现分区

3.1 创建主表

david=# create table tbl_partition (
david(# id integer,david(# name varchar(20),david(# gender boolean,david(# join_date date,david(# dept char(4));
CREATE TABLE
david=#

3.2 创建分区表

table tbl_partition_201211 ( check ( join_date >= DATE '2012-11-01' AND join_date < DATE 2012-12-01' ) ) INHERITS (tbl_partition); table tbl_partition_201212 ( 2013-01-01' ) ) INHERITS (tbl_partition); table tbl_partition_201301 ( 2013-02-01table tbl_partition_201302 ( 2013-03-01' ) ) INHERITS (tbl_partition); table tbl_partition_201303 ( 2013-04-01' ) ) INHERITS (tbl_partition); table tbl_partition_201304 ( 2013-05-01table tbl_partition_201305 ( 2013-06-01=#

3.3 分区键上建索引

index tbl_partition_201211_joindate on tbl_partition_201211 (join_date); INDEX davidindex tbl_partition_201212_joindate on tbl_partition_201212 (join_date); index tbl_partition_201301_joindate on tbl_partition_201301 (join_date); index tbl_partition_201302_joindate on tbl_partition_201302 (join_date); index tbl_partition_201303_joindate on tbl_partition_201303 (join_date); index tbl_partition_201304_joindate on tbl_partition_201304 (join_date); index tbl_partition_201305_joindate on tbl_partition_201305 (join_date); 对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。

3.4 创建触发器函数

CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.join_date AND NEW.join_date ' ) THEN INSERT INTO tbl_partition_201211 VALUES (NEW.*); ELSIF ( NEW.join_date AND NEW.join_date INTO tbl_partition_201212 INTO tbl_partition_201301 INTO tbl_partition_201302 INTO tbl_partition_201303 INTO tbl_partition_201304 *); ELSIF ( NEW.join_date AND NEW.join_date THEN INTO tbl_partition_201305 *); ELSE RAISE EXCEPTION Date out of range. Fix the tbl_partition_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; FUNCTION david 说明：如果不想丢失数据，上面的ELSE 条件可以改成INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date，这样，错误的join_date 数据就可以插入到这张表中而不是报错了。

3.5 创建触发器

davidTRIGGER insert_tbl_partition_trigger
david-#     BEFORE ON tbl_partition
david-#     FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger();
TRIGGER
david=#

四、查看表

4.1 查看所有表

=# \d tbl_partition_201304 public.tbl_partition_201304" | Indexes: "tbl_partition_201304_joindate" btree (join_date) Check constraints: "tbl_partition_201304_join_date_check" CHECK (join_date >= '::date < '::date) Inherits: tbl_partition david五、测试

5.1 插入数据

insert into tbl_partition values (1,David',0); line-height:1.5!important">12013-01-10TS'); INSERT 0 0 david2,0); line-height:1.5!important">Sandy02013-02-103,0); line-height:1.5!important">Eagle4,0); line-height:1.5!important">Miles2012-12-15SD5,0); line-height:1.5!important">Simon2012-12-106,0); line-height:1.5!important">Rock2012-11-107,0); line-height:1.5!important">Peter2013-01-118,0); line-height:1.5!important">Sally2013-03-10BCSC9,0); line-height:1.5!important">Carrie2013-04-0210,0); line-height:1.5!important">Lee2013-01-05BMC11,0); line-height:1.5!important">NicolePROJ12,0); line-height:1.5!important">Renee 5.2 查看主表数据

6.1 移除数据/分区

实现分区表之后，我们就可以很容易地移除不再使用的旧数据了，最简单的方法就是：

drop table tbl_partition_201304;

这样可以快速移除大量数据，而不是逐条删除数据。

另一个推荐做法是将分区从分区表中移除，但是保留访问权限。

alter table tbl_partition_201304 no inherit tbl_partition; ALTER =#

和直接DROP 相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据(TRUNCATE TABLE)，之后再让该表重新继承主表。

table tbl_partition_201304 inherit tbl_partition; 6.2 增加分区

我们可以像之前那样增加一个分区

table tbl_partition_201306 ( 2013-07-01' ) ) INHERITS (tbl_partition);

index tbl_partition_201306_joindate on tbl_partition_201306 (join_date);

同时，需要修改触发器函数，将插入条件改成相应的值。

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

另外，还可以如下增加新的分区：

table tbl_partition_201307 david-# (LIKE tbl_partition INCLUDING DEFAULTS INCLUDING CONSTRAINTS); =# davidtable tbl_partition_201307 add constraint tbl_partition_201307_join_date_check david-# 2013-08-01' ); index tbl_partition_201307_joindate on tbl_partition_201307 (join_date);

david=# copy tbl_partition_201307 from '/tmp/tbl_partition_201307.sql'; //从文件中拷贝数据，这些数据可以是事前准备的 davidtable tbl_partition_201307 inherit tbl_partition;

七、约束排除

约束排除（Constraint exclusion）是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。

确保postgresql.conf 里的配置参数constraint_exclusion 是打开的。没有这个参数，查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。

如果没有约束排除，查询会扫描tbl_partition 表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后再试图证明该分区不需要被扫描，因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。

可以使用EXPLAIN 命令显示一个规划在constraint_exclusion 关闭和打开情况下的不同：

7.1 约束排除关闭

set constraint_exclusion = off; SET david=# explain select count(*) from tbl_partition where join_date '; QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=172.80..172.81 rows1 width0) -> Append (cost0.00..167.62 rows2071 width0) -> Seq Scan on tbl_partition (cost0.00 rows0) Filter: (join_date '::date) on tbl_partition_201211 tbl_partition (cost18.62 rows230 widthon tbl_partition_201212 tbl_partition (coston tbl_partition_201301 tbl_partition (coston tbl_partition_201302 tbl_partition (coston tbl_partition_201303 tbl_partition (coston tbl_partition_201305 tbl_partition (coston tbl_partition_201304 tbl_partition (coston tbl_partition_201306 tbl_partition (coston tbl_partition_201307 tbl_partition (cost'::date) (22 rows) david 从上面的查询计划中可以看出，Postgresql 扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划：

7.2 约束排除开启

on; 76.80..76.81 rows74.50 rows921 width 可以看到，Postgresql 只扫描四月份以后的分区表。

八、可选的分区方式

还可以通过建立规则的方式进行分区。

RULE insert_tbl_partition_201211 AS
ON TO tbl_partition WHERE
    ( join_date ' )
DO INSTEAD
    *);

RULE insert_tbl_partition_201212 *);

...
    
RULE insert_tbl_partition_201306 INTO tbl_partition_201306 *);
    
RULE insert_tbl_partition_201307 INTO tbl_partition_201307 RULE insert_tbl_partition_error_join_date OR join_date INTO tbl_partition_error_join_date *);