随笔-102 文章-0 评论-83
# [PostgreSQL分区表（Table Partitioning）应用](http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html)
**一、简介**
在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，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 创建分区表
[]( "复制代码")
~~~
david=# create table tbl_partition_201211 (
check ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201212 (
check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201301 (
check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201302 (
check ( join_date >= DATE '2013-02-01' AND join_date < DATE '2013-03-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201303 (
check ( join_date >= DATE '2013-03-01' AND join_date < DATE '2013-04-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201304 (
check ( join_date >= DATE '2013-04-01' AND join_date < DATE '2013-05-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201305 (
check ( join_date >= DATE '2013-05-01' AND join_date < DATE '2013-06-01' )
) INHERITS (tbl_partition);
CREATE TABLE
david=#
~~~
[]( "复制代码")
3.3 分区键上建索引
[]( "复制代码")
~~~
david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);
CREATE INDEX
david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date);
CREATE INDEX
david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);
CREATE INDEX
david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date);
CREATE INDEX
david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date);
CREATE INDEX
david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date);
CREATE INDEX
david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date);
CREATE INDEX
david=#
~~~
[]( "复制代码")
对于开发人员来说，希望数据库是透明的，只管 insert into tbl_partition。对于数据插向哪个分区，则希望由DB决定。这点，ORACLE实现了，但是PG不行，需要前期人工处理下。
3.4 创建触发器函数
[]( "复制代码")
~~~
david=# 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
david=#
~~~
[]( "复制代码")
**说明：**如果不想丢失数据，上面的ELSE 条件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date，这样，错误的join_date 数据就可以插入到这张表中而不是报错了。
3.5 创建触发器
~~~
david=# CREATE TRIGGER insert_tbl_partition_trigger
david-# BEFORE INSERT ON tbl_partition
david-# FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger();
CREATE TRIGGER
david=#
~~~
**四、查看表**
4.1 查看所有表
[]( "复制代码")
~~~
david=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------------------+-------+----------
public | tbl_partition | table | postgres
public | tbl_partition_201211 | table | postgres
public | tbl_partition_201212 | table | postgres
public | tbl_partition_201301 | table | postgres
public | tbl_partition_201302 | table | postgres
public | tbl_partition_201303 | table | postgres
public | tbl_partition_201304 | table | postgres
public | tbl_partition_201305 | table | postgres
(8 rows)
david=#
~~~
[]( "复制代码")
4.2 查看主表
[]( "复制代码")
~~~
david=# \d tbl_partition
Table "public.tbl_partition"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer |
name | character varying(20) |
gender | boolean |
join_date | date |
dept | character(4) |
Triggers:
insert_tbl_partition_trigger BEFORE INSERT ON tbl_partition FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger()
Number of child tables: 7 (Use \d+ to list them.)
david=#
~~~
[]( "复制代码")
4.3 查看分区表
[]( "复制代码")
~~~
david=# \d tbl_partition_201304
Table "public.tbl_partition_201304"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer |
name | character varying(20) |
gender | boolean |
join_date | date |
dept | character(4) |
Indexes:
"tbl_partition_201304_joindate" btree (join_date)
Check constraints:
"tbl_partition_201304_join_date_check" CHECK (join_date >= '2013-04-01'::date AND join_date < '2013-05-01'::date)
Inherits: tbl_partition
david=#
~~~
[]( "复制代码")
**五、测试**
5.1 插入数据
[]( "复制代码")
~~~
david=# insert into tbl_partition values (1, 'David', '1', '2013-01-10', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (2, 'Sandy', '0', '2013-02-10', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (3, 'Eagle', '1', '2012-11-01', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (4, 'Miles', '1', '2012-12-15', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (5, 'Simon', '1', '2012-12-10', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (6, 'Rock', '1', '2012-11-10', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (7, 'Peter', '1', '2013-01-11', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (8, 'Sally', '0', '2013-03-10', 'BCSC');
INSERT 0 0
david=# insert into tbl_partition values (9, 'Carrie', '0', '2013-04-02', 'BCSC');
INSERT 0 0
david=# insert into tbl_partition values (10, 'Lee', '1', '2013-01-05', 'BMC');
INSERT 0 0
david=# insert into tbl_partition values (11, 'Nicole', '0', '2012-11-10', 'PROJ');
INSERT 0 0
david=# insert into tbl_partition values (12, 'Renee', '0', '2013-01-10', 'TS');
INSERT 0 0
david=#
~~~
[]( "复制代码")
5.2 查看主表数据
[]( "复制代码")
~~~
david=# select * from tbl_partition;
id | name | gender | join_date | dept
----+--------+--------+------------+------
3 | Eagle | t | 2012-11-01 | TS
6 | Rock | t | 2012-11-10 | SD
11 | Nicole | f | 2012-11-10 | PROJ
4 | Miles | t | 2012-12-15 | SD
5 | Simon | t | 2012-12-10 | SD
1 | David | t | 2013-01-10 | TS
7 | Peter | t | 2013-01-11 | SD
10 | Lee | t | 2013-01-05 | BMC
12 | Renee | f | 2013-01-10 | TS
2 | Sandy | f | 2013-02-10 | TS
8 | Sally | f | 2013-03-10 | BCSC
9 | Carrie | f | 2013-04-02 | BCSC
(12 rows)
david=#
~~~
[]( "复制代码")
5.3 查看分区表数据
[]( "复制代码")
~~~
david=# select * from tbl_partition_201301 ;
id | name | gender | join_date | dept
----+-------+--------+------------+------
1 | David | t | 2013-01-10 | TS
7 | Peter | t | 2013-01-11 | SD
10 | Lee | t | 2013-01-05 | BMC
12 | Renee | f | 2013-01-10 | TS
(4 rows)
david=#
~~~
[]( "复制代码")
**六、管理分区**
6.1 移除数据/分区
实现分区表之后，我们就可以很容易地移除不再使用的旧数据了，最简单的方法就是：
~~~
david=# drop table tbl_partition_201304;
~~~
这样可以快速移除大量数据，而不是逐条删除数据。
另一个推荐做法是将分区从分区表中移除，但是保留访问权限。
~~~
david=# alter table tbl_partition_201304 no inherit tbl_partition;
ALTER TABLE
david=#
~~~
和直接DROP 相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据(TRUNCATE TABLE)，之后再让该表重新继承主表。
~~~
david=# alter table tbl_partition_201304 inherit tbl_partition;
ALTER TABLE
david=#
~~~
6.2 增加分区
我们可以像之前那样增加一个分区
~~~
david=# create table tbl_partition_201306 (
check ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' )
) INHERITS (tbl_partition);
~~~
~~~
david=# create index tbl_partition_201306_joindate on tbl_partition_201306 (join_date);
~~~
同时，需要修改触发器函数，将插入条件改成相应的值。
**说明：**创建触发器函数时，最好把插入条件写更未来一点，比如多写十年，这样以后增加新分区时就不需要重新创建触发器函数了，也可以避免一些不必要的错误。
另外，还可以如下增加新的分区：
[]( "复制代码")
~~~
david=# create table tbl_partition_201307
david-# (LIKE tbl_partition INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE
david=#
david=# alter table tbl_partition_201307 add constraint tbl_partition_201307_join_date_check
david-# check ( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' );
ALTER TABLE
david=#
david=# create index tbl_partition_201307_joindate on tbl_partition_201307 (join_date);david=# copy tbl_partition_201307 from '/tmp/tbl_partition_201307.sql'; //从文件中拷贝数据，这些数据可以是事前准备的
david=# alter table tbl_partition_201307 inherit tbl_partition;
~~~
[]( "复制代码")
**七、约束排除**
约束排除（Constraint exclusion）是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。
确保postgresql.conf 里的配置参数constraint_exclusion 是打开的。没有这个参数，查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。
如果没有约束排除，查询会扫描tbl_partition 表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后再试图证明该分区不需要被扫描，因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。
可以使用EXPLAIN 命令显示一个规划在constraint_exclusion 关闭和打开情况下的不同：
7.1 约束排除关闭
[]( "复制代码")
~~~
david=# set constraint_exclusion = off;
SET
david=# explain select count(*) from tbl_partition where join_date >= DATE '2013-04-01';
QUERY PLAN
-------------------------------------------------------------------------------------------------
Aggregate (cost=172.80..172.81 rows=1 width=0)
-> Append (cost=0.00..167.62 rows=2071 width=0)
-> Seq Scan on tbl_partition (cost=0.00..0.00 rows=1 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201211 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201212 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201301 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201302 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201303 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201305 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201304 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201306 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201307 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
(22 rows)
david=#
~~~
[]( "复制代码")
从上面的查询计划中可以看出，PostgreSQL 扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划：
7.2 约束排除开启
[]( "复制代码")
~~~
david=# set constraint_exclusion = on;
SET
david=# explain select count(*) from tbl_partition where join_date >= DATE '2013-04-01';
QUERY PLAN
-------------------------------------------------------------------------------------------------
Aggregate (cost=76.80..76.81 rows=1 width=0)
-> Append (cost=0.00..74.50 rows=921 width=0)
-> Seq Scan on tbl_partition (cost=0.00..0.00 rows=1 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201305 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201304 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201306 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
-> Seq Scan on tbl_partition_201307 tbl_partition (cost=0.00..18.62 rows=230 width=0)
Filter: (join_date >= '2013-04-01'::date)
(12 rows)
david=#
~~~
[]( "复制代码")
可以看到，PostgreSQL 只扫描四月份以后的分区表。
**八、可选的分区方式**
还可以通过建立规则的方式进行分区。
[]( "复制代码")
~~~
CREATE RULE insert_tbl_partition_201211 AS
ON INSERT TO tbl_partition WHERE
( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )
DO INSTEAD
INSERT INTO tbl_partition_201211 VALUES (NEW.*);
CREATE RULE insert_tbl_partition_201212 AS
ON INSERT TO tbl_partition WHERE
( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )
DO INSTEAD
INSERT INTO tbl_partition_201212 VALUES (NEW.*);
...
CREATE RULE insert_tbl_partition_201306 AS
ON INSERT TO tbl_partition WHERE
( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' )
DO INSTEAD
INSERT INTO tbl_partition_201306 VALUES (NEW.*);
CREATE RULE insert_tbl_partition_201307 AS
ON INSERT TO tbl_partition WHERE
( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' )
DO INSTEAD
INSERT INTO tbl_partition_201307 VALUES (NEW.*);
CREATE RULE insert_tbl_partition_error_join_date AS
ON INSERT TO tbl_partition WHERE
( join_date >= DATE '2013-08-01' OR join_date < DATE '2012-11-01' )
DO INSTEAD
INSERT INTO tbl_partition_error_join_date VALUES (NEW.*);
~~~
[]( "复制代码")
**九、注意事项**
VACUUM 或 ANALYZE tbl_partition 只会对主表起作用，要想分析表，需要分别分析每个分区表。
**十、参考资料**
- PostgreSQL官方说明：[http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html](http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html)
- ITEYE：http://diegoball.iteye.com/blog/713826
- kenyon(君羊)：http://my.oschina.net/Kenyon/blog/59455
分类: [Postgresql](http://www.cnblogs.com/mchina/category/381458.html)
标签: [postgresql](http://www.cnblogs.com/mchina/tag/postgresql/), [分区表](http://www.cnblogs.com/mchina/tag/分区表/), [Table Partitioning](http://www.cnblogs.com/mchina/tag/Table Partitioning/)
绿色通道： [好文要顶]()[关注我]()[收藏该文]()[与我联系](http://space.cnblogs.com/msg/send/David_Tang)[]( "分享至新浪微博")
[](http://home.cnblogs.com/u/mchina/)
[David_Tang](http://home.cnblogs.com/u/mchina/)
[关注 - 1](http://home.cnblogs.com/u/mchina/followees)
[粉丝 - 116](http://home.cnblogs.com/u/mchina/followers)
[+加关注]()
4
0
(请您对文章做出评价)
[« ](http://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html) 上一篇：[PostgreSQL的generate_series函数应用](http://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html "发布于2013-04-03 14:50")
[» ](http://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html) 下一篇：[PostgreSQL 序列（SEQUENCE）](http://www.cnblogs.com/mchina/archive/2013/04/10/3012493.html "发布于2013-04-10 15:52")
posted @ 2013-04-09 11:14[David_Tang](http://www.cnblogs.com/mchina/) 阅读(653) 评论(2) [编辑](http://www.cnblogs.com/mchina/admin/EditPosts.aspx?postid=2973427)[收藏](#)
Copyright ©2013 David_Tang
- 数据表
- 模式Schema
- 表的继承和分区
- 常用数据类型
- 函数和操作符-一
- 函数和操作符-二
- 函数和操作符-三
- 索引
- 事物隔离
- 性能提升技巧
- 服务器配置
- 角色和权限
- 数据库管理
- 数据库维护
- 系统表
- 系统视图
- SQL语言函数
- PL-pgSQL过程语言
- PostgreSQL 序列（SEQUENCE）
- PostgreSQL的时间-日期函数使用
- PostgreSQL 查看数据库，索引，表，表空间大小
- 用以查询某表的详细 包含表字段的注释信息
- PostgreSQL 系统表查看系统信息
- postgre存储过程简单实用方法
- PostgreSQL实用日常维护SQL
- PostgreSQL的时间函数使用整理
- 命令
- pg_ctl控制服务器
- initdb 初始化数据库簇
- createdb创建数据库
- dropdb 删除数据库
- createuser创建用户
- dropuser 删除用户
- psql交互式工具
- psql命令手册
- pg_dump 数据库转储
- pg_restore恢复数据库
- vacuumdb 清理优化数据库
- reindexdb 数据库重创索引
- createlang 安装过程语言
- droplang 删除过程语言
- pg_upgrade 升级数据库簇
- 调试存储过程
- 客户端命令-一
- 客户端命令-二
- 使用技巧
- PostgreSQL删除重复数据
- postgresql 小技巧
- PostgreSQL的10进制与16进制互转
- PostgreSQL的汉字转拼音
- Postgres重复数据的更新一例
- PostgreSQL使用with一例
- PostgreSQL在函数内返回returning
- PostgreSQL中的group_concat使用
- PostgreSQL数据库切割和组合字段函数
- postgresql重复数据的删除
- PostgreSQL的递归查询(with recursive)
- PostgreSQL函数如何返回数据集
- PostgreSQL分区表（Table Partitioning）应用 - David_Tang - 博客园
- PostgreSQL: function 返回结果集多列和单列的例子
- 利用pgAgent创建定时任务
- 浅谈 PostgreSQL 类型转换类似Oracle
- postgresql在windows(包括win7)下的安装配置
- PostgreSQL简介、安装、用户管理、启动关闭、创建删除数据库 (2010-11-08 12-52-51)转载▼标签： 杂谈分类： PostgreSQL
- PostgreSQL的generate_series函数应用
- PostgreSQL 8.3.1 全文检索(Full Text Search)
- postgresql record 使用
- 备份恢复
- PostgreSQL基于时间点恢复(PITR)
- Postgresql基于时间点恢复PITR案例(二)
- Postgres逻辑备份脚本
- Postgres invalid command \N数据恢复处理