一、简介
在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，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
- ITEYE：http://diegoball.iteye.com/blog/713826
- kenyon(君羊)：http://my.oschina.net/Kenyon/blog/59455
