一、简介
在数据库日渐庞大的今天,为了方便对数据库数据的管理,比如按时间,按地区去统计一些数据时,基数过于庞大,多有不便。很多商业数据库都提供分区的概念,按不同的维度去存放数据,便于后期的管理,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 创建分区表
3.3 分区键上建索引
3.4 创建触发器函数
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 查看所有表
5.1 插入数据
6.1 移除数据/分区
实现分区表之后,我们就可以很容易地移除不再使用的旧数据了,最简单的方法就是:
另一个推荐做法是将分区从分区表中移除,但是保留访问权限。
和直接DROP 相比,该方式仅仅是使子表脱离了原有的主表,而存储在子表中的数据仍然可以得到访问,因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说,就可以在此时对该表进行必要的维护操作,如数据清理、归档等,在完成诸多例行性的操作之后,就可以考虑是直接删除该表(DROP TABLE),还是先清空该表的数据(TRUNCATE TABLE),之后再让该表重新继承主表。
我们可以像之前那样增加一个分区
说明:创建触发器函数时,最好把插入条件写更未来一点,比如多写十年,这样以后增加新分区时就不需要重新创建触发器函数了,也可以避免一些不必要的错误。
另外,还可以如下增加新的分区:
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 约束排除关闭
7.2 约束排除开启
八、可选的分区方式
还可以通过建立规则的方式进行分区。
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 *);
九、注意事项
VACUUM或 ANALYZE tbl_partition只会对主表起作用,要想分析表,需要分别分析每个分区表。
十、参考资料
- Postgresql官方说明: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