postgresql 怎么自动创建分区

在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，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 数据就可以插入到这张表中而不是报错了。

附上出处链接：http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html

分区方法如下：

1.目前支持range，hash分区。

2.支持自动分区管理（通过函数接口创建分区，自动将主表数据迁移到分区表），或手工分区管理（通过函数实现，将现有的表绑定到分区表，或者从分区表分割） 。

3.支持的分区分区类型包括int，float，date，以及其他常用类型，包括自定义的域。

4.通过CUSTOM SCAN实现了有效的分区表JOIN，子查询过滤分区。

5.使用RuntimeAppend和RuntimeMergeAppend自定义计划节点实现了动态分区选择。

6. PartitionFilter HOOK，实现就地插入，代替传统的插入触发器或插入规则。

7.支持自动添加分区。目前仅支持范围分区表。

8.支持从/到直接读取或写入分区表的复制，提高效率。

9.支持分区分区的更新，需要添加替换，如果不需要更新分区分区，则不建议添加此转换器，会产生一定的性能影响。

10.允许用户自定义定义函数，在创建分区时会自动触发。

11.非插入式创建分区表，以及后台自动将主表数据迁移到分区表，非插入式。

12.支持FDW，通过配置参数pg_pathman.insert_into_fdw =（禁用| postgres | any_fdw）支持postgres_fdw或任意fdw(外部分区)

13.支持GUC参数配置，注意通过使用了HOOK，如果其他插件也使用了相同的HOOK，需要将pg_pathman放在后面注册，如pg_stat_statements。

插入数据的时候，会根据address_id算出它的hashcode值，然后落入到对应的分区表上。查询数据的时候，只要带入这个address_id作为查询条件，就会算出对应的hashcode的值，然后就可以直接去指定的分区表中查询检索数据，避免大表扫描，提升查询效率。

举个例子，有2000万的数据，做了hash分区，分出来20张表，在插入数据的时候把2000万的数据均匀分布到这20张表中，平均每张表的数据只有100万，那么查询某个数据的时候，就可以只查询其中的一个分区表，因为数据只有100万，远远比2000万小，所以速度更快

---