PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。分区的具体好处是：某些类型的查询性能可以得到极大提升。更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。批量删除可以用简单的删除某个分区来实现。可以将很少用的数据移动到便宜的、转速慢的存储介质上。
一、环境
系统环境：Ubuntu 15.04
PostgreSQL版本：postgresql-9.4.4
二、创建主表
2.1 创建主表
test=# CREATE TABLE tbl_student(
test(# id integer,
test(# name varchar(20),
test(# grade integer,
test(# class integer,
test(# age integer);
CREATE TABLE
test=#
2.2 创建分区表
test=# CREATE TABLE tbl_student_grade_1(
test(# CHECK (grade = 1) //一年级学生
test(# ) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_2(
CHECK (grade = 2) //二年级学生
) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_3(
CHECK (grade = 3) //三年级学生
) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_4(
CHECK (grade = 4) //四年级学生
) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_5(
CHECK (grade = 5) //五年级学生
) INHERITS (tbl_student);
CREATE TABLE
test=# CREATE TABLE tbl_student_grade_6(
CHECK (grade = 6) //六年级学生
) INHERITS (tbl_student);
CREATE TABLE
test=#
2.3 分区表上建索引
test=# CREATE INDEX tbl_student_grade_1_index on tbl_student_grade_1(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_2_index on tbl_student_grade_2(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_3_index on tbl_student_grade_3(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_4_index on tbl_student_grade_4(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_5_index on tbl_student_grade_5(grade);
CREATE INDEX
test=# CREATE INDEX tbl_student_grade_6_index on tbl_student_grade_6(grade);
CREATE INDEX
test=#
对于用户来说，插入数据的时候，并不需要了解分区别的存在，也就是说表应该是一个透明的整体，用户只需要插入数据即可，不需要区分。那么我们就需要给各个分区表建立触发器来进行辅助操作。
2.4 创建触发器函数
test=# CREATE OR REPLACE FUNCTION tbl_student_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.grade = 1) THEN INSERT INTO tbl_student_grade_1 VALUES (NEW.*);
ELSEIF (NEW.grade = 2) THEN INSERT INTO tbl_student_grade_2 VALUES (NEW.*);
ELSEIF (NEW.grade = 3) THEN INSERT INTO tbl_student_grade_3 VALUES (NEW.*);
ELSEIF (NEW.grade = 4) THEN INSERT INTO tbl_student_grade_4 VALUES (NEW.*);
ELSEIF (NEW.grade = 5) THEN INSERT INTO tbl_student_grade_5 VALUES (NEW.*);
ELSEIF (NEW.grade = 6) THEN INSERT INTO tbl_student_grade_6 VALUES (NEW.*);
ELSE RAISE EXCEPTION 'Date error,out of range.Fix it and try again';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
test=#
2.5 创建触发器
test=# CREATE TRIGGER insert_tbl_student_trigger
test-# BEFORE INSERT ON tbl_student
test-# FOR EACH ROW EXECUTE PROCEDURE tbl_student_insert_trigger();
CREATE TRIGGER
test=#
三、查看表
现在所有的内容准备就绪，我们来查看各个表
3.1 查看所有表
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------------+-------+--------------
public | tbl_student | table | highgo-yueyf
public | tbl_student_grade_1 | table | highgo-yueyf
public | tbl_student_grade_2 | table | highgo-yueyf
public | tbl_student_grade_3 | table | highgo-yueyf
public | tbl_student_grade_4 | table | highgo-yueyf
public | tbl_student_grade_5 | table | highgo-yueyf
public | tbl_student_grade_6 | table | highgo-yueyf
(7 rows)
test=#
3.2 查看主表
test=# \d tbl_student
Table "public.tbl_student"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
grade | integer |
class | integer |
age | integer |
Triggers:
insert_tbl_student_trigger BEFORE INSERT ON tbl_student FOR EACH ROW EXECUTE PROCEDURE tbl_student_insert_trigger()
Number of child tables: 6 (Use \d+ to list them.)
test=#
3.3 查看分区表
test=# \d tbl_student_grade_1
Table "public.tbl_student_grade_1"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
grade | integer |
class | integer |
age | integer |
Indexes:
"tbl_student_grade_1_index" btree (grade)
Check constraints:
"tbl_student_grade_1_grade_check" CHECK (grade = 1)
Inherits: tbl_student
test=#
四、测试
4.1 插入测试用数据
每个年级三个班，每个班有一名学生的数据，六个年级共有18名学生的数据。
test=# INSERT INTO tbl_student VALUES (1, 'David', 1, 1, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (2, 'Jim', 1, 2, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (3, 'Sandy', 1, 3, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (4, 'Candy', 2, 1, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (5, 'Lily', 2, 2, 8);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (6, 'Smith', 2, 3, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (7, 'Pool', 3, 1, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (8, 'Alen', 3, 2, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (9, 'May', 3, 3, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (10, 'Huan', 4, 1, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (11, 'Yue', 4, 2, 8);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (12, 'Kate', 4, 3, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (13, 'Gay', 5, 1, 8);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (14, 'Jane', 5, 2, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (15, 'Wolf', 5, 3, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (16, 'David', 6, 1, 6);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (17, 'David', 6, 2, 7);
INSERT 0 0
test=# INSERT INTO tbl_student VALUES (18, 'David', 6, 3, 7);
INSERT 0 0
test=#
4.2 再次查看主表数据
test=# SELECT * FROM tbl_student;
id | name | grade | class | age
----+-------+-------+-------+-----
1 | David | 1 | 1 | 7
2 | Jim | 1 | 2 | 6
3 | Sandy | 1 | 3 | 6
4 | Candy | 2 | 1 | 7
5 | Lily | 2 | 2 | 8
6 | Smith | 2 | 3 | 7
7 | Pool | 3 | 1 | 7
8 | Alen | 3 | 2 | 6
9 | May | 3 | 3 | 7
10 | Huan | 4 | 1 | 7
11 | Yue | 4 | 2 | 8
12 | Kate | 4 | 3 | 7
13 | Gay | 5 | 1 | 8
14 | Jane | 5 | 2 | 6
15 | Wolf | 5 | 3 | 7
16 | David | 6 | 1 | 6
17 | David | 6 | 2 | 7
18 | David | 6 | 3 | 7
(18 rows)
test=#
4.3 查看分区表的数据 6个年级分别查看
test=# SELECT * FROM tbl_student_grade_1;
id | name | grade | class | age
----+-------+-------+-------+-----
1 | David | 1 | 1 | 7
2 | Jim | 1 | 2 | 6
3 | Sandy | 1 | 3 | 6
(3 rows)
test=# SELECT * FROM tbl_student_grade_2;
id | name | grade | class | age
----+-------+-------+-------+-----
4 | Candy | 2 | 1 | 7
5 | Lily | 2 | 2 | 8
6 | Smith | 2 | 3 | 7
(3 rows)
test=# SELECT * FROM tbl_student_grade_3;
id | name | grade | class | age
----+------+-------+-------+-----
7 | Pool | 3 | 1 | 7
8 | Alen | 3 | 2 | 6
9 | May | 3 | 3 | 7
(3 rows)
test=# SELECT * FROM tbl_student_grade_4;
id | name | grade | class | age
----+------+-------+-------+-----
10 | Huan | 4 | 1 | 7
11 | Yue | 4 | 2 | 8
12 | Kate | 4 | 3 | 7
(3 rows)
test=# SELECT * FROM tbl_student_grade_5;
id | name | grade | class | age
----+------+-------+-------+-----
13 | Gay | 5 | 1 | 8
14 | Jane | 5 | 2 | 6
15 | Wolf | 5 | 3 | 7
(3 rows)
test=# SELECT * FROM tbl_student_grade_6;
id | name | grade | class | age
----+-------+-------+-------+-----
16 | David | 6 | 1 | 6
17 | David | 6 | 2 | 7
18 | David | 6 | 3 | 7
(3 rows)
test=#
至此，分区表建立完毕。数据自动分到了各个分区表中，方便管理。
Ubuntu下Postgresql卸载出错的解决办法：http://www.linuxdiyf.com/linux/14528.html
Postgresql 9.1添加用户Ubuntu12.04：http://www.linuxdiyf.com/linux/14379.html
fedora22用rpm包安装配置postgresql数据库：http://www.linuxdiyf.com/linux/13212.html
Fedora21升级到Fedora22后，PostgreSQL无法启动：http://www.linuxdiyf.com/linux/12657.html
CentOS 6.5下PostgreSQL故障切换实现：http://www.linuxdiyf.com/linux/12605.html