PostgreSQL分区表（Table Partitioning）应用

一、简介

　　在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，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 分区键上建索引

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

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 *);