一、简介
在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。
PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。
分区的具体好处是：
- 某些类型的查询性能可以得到极大提升。
- 更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
- 批量删除可以用简单的删除某个分区来实现。
- 可以将很少用的数据移动到便宜的、转速慢的存储介质上。
在PG里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。无论何时，都应保证主表里面是空的。
小表分区不实际，表在多大情况下才考虑分区呢？PostgresSQL官方给出的建议是：当表本身大小超过了机器物理内存的实际大小时（the size of the table should exceed the physical memory of the database server），可以考虑分区。
PG目前（9.2.2）仅支持范围分区和列表分区，尚未支持散列分区。
特别说明：
1.继承， 权限和约束不会继承。
2.继承，对父表的操作(select丶update丶delete),默认会引用子表 行，若不想引用子表 行可用关键字 ONLY，只对父表操作。
二、环境
系统环境：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 创建主表
CREATE TABLE partition_main
(
id integer,
insert_date date
)
3.2 创建分区表
--2014年01月分区表
CREATE TABLE partition_201401
(
CONSTRAINT partition_201402_insert_date_check CHECK (insert_date >= '2014-01-01'::date AND insert_date < '2014-02-01'::date)
)
INHERITS (partition_main);
--2014年02月分区表
CREATE TABLE partition_201402
(
CONSTRAINT partition_201402_insert_date_check CHECK (insert_date >= '2014-02-01'::date AND insert_date < '2014-03-01'::date)
)
INHERITS (partition_main);
--2014年03月分区表
CREATE TABLE partition_201403
(
CONSTRAINT partition_201403_insert_date_check CHECK (insert_date >= '2014-03-01'::date AND insert_date < '2014-04-01'::date)
)
INHERITS (partition_main);
--2014年04月分区表
CREATE TABLE partition_201404
(
CONSTRAINT partition_201404_insert_date_check CHECK (insert_date >= '2014-04-01'::date AND insert_date < '2014-05-01'::date)
)
INHERITS (partition_main);3.3 分区键上建索引
--postgresql 默认索引类型btree
CREATE INDEX partition_201401_insert_date_index ON partition_201401 USING btree (insert_date);
CREATE INDEX partition_201402_insert_date_index ON partition_201402 USING btree (insert_date);
CREATE INDEX partition_201403_insert_date_index ON partition_201403 USING btree (insert_date);
CREATE INDEX partition_201404_insert_date_index ON partition_201404 USING btree (insert_date);3.4 创建触发器函数
CREATE OR REPLACE FUNCTION tbl_partition_insert_function()
RETURNS trigger AS
$BODY$
BEGIN
IF ( NEW.insert_date >= DATE '2014-01-01' AND NEW.insert_date < DATE '2014-02-01' ) THEN
INSERT INTO partition_201401 VALUES (NEW.*);
ELSIF ( NEW.insert_date >= DATE '2014-02-01' AND NEW.insert_date < DATE '2014-03-01' ) THEN
INSERT INTO partition_201402 VALUES (NEW.*);
ELSIF ( NEW.insert_date >= DATE '2014-03-01' AND NEW.insert_date < DATE '2014-04-01' ) THEN
INSERT INTO partition_201403 VALUES (NEW.*);
ELSIF ( NEW.insert_date >= DATE '2014-04-01' AND NEW.insert_date < DATE '2014-05-01' ) THEN
INSERT INTO partition_201404 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;说明： 如果不想丢失数据，上面的ELSE 条件可以改成 INSERT INTO partition_error_insert_date VALUES (NEW.*); 同时需要创建一张结构和partition_main 一样的表partition_error_insert_date，这样，错误的insert_date 数据就可以插入到这张表中而不是报错了。
3.5 创建触发器
CREATE TRIGGER tbl_partition_insert_trigger
BEFORE INSERT
ON partition_main
FOR EACH ROW
EXECUTE PROCEDURE tbl_partition_insert_function();四、测试
4.1 插入数据
insert into partition_main values(1,DATE '2014-01-01');
insert into partition_main values(2,DATE '2014-02-01');
insert into partition_main values(3,DATE '2014-03-01');
insert into partition_main values(4,DATE '2014-04-01');4.2 查看主表数据
select *,tableoid from partition_mainid | insert_date | tableoid
---+-------------+----------
1 | 2014-01-02 | 66658
2 | 2014-02-01 | 66661
3 | 2014-03-01 | 66664
4 | 2014-04-01 | 66667可从tableoid 看出每一行记录来自不同的表
5、管理分区
5.1 移除数据/分区
实现分区表之后，我们就可以很容易地移除不再使用的旧数据了，最简单的方法就是：
drop table tbl_partition_201304;
这样可以快速移除大量数据，而不是逐条删除数据。
另一个推荐做法是将分区从分区表中移除，但是保留访问权限。
alter table tbl_partition_201304 no inherit tbl_partition;
和直接DROP 相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROP TABLE)，还是先清空该表的数据(TRUNCATE TABLE)，之后再让该表重新继承主表。
alter table tbl_partition_201304 inherit tbl_partition;
5.2 增加分区
我们可以像之前那样增加一个分区
create table partition_201405 ( check ( insert_date >= DATE '2014-05-01' AND insert_date < DATE '2014-06-01' ) ) INHERITS (partition_main);
create index partition_201405_insert_date_index on partition_201405 (insert_date );
同时，需要修改触发器函数，将插入条件改成相应的值。
说明：创建触发器函数时，最好把插入条件写更未来一点，比如多写十年，这样以后增加新分区时就不需要重新创建触发器函数了，也可以避免一些不必要的错误。
另外，还可以如下增加新的分区：
(LIKE partition_main INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
alter table partition_201406 add constraint partition_201406_insert_date_check
check ( insert_date >= DATE '2014-06-01' AND insert_date < DATE '2014-07-01' );
create index partition_201406_insert_date_index on partition_201406 (insert_date);
copy partition_201406 from '/tmp/partition_201406.sql'; //从文件中拷贝数据，这些数据可以是事前准备的 alter table partition_201406 inherit partition_main;
六、约束排除
约束排除（Constraint exclusion）是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。
确保postgresql.conf 里的配置参数constraint_exclusion 是打开的。没有这个参数，查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。
constraint_exclusion = partition # on, off, or partition如果没有约束排除，查询会扫描tbl_partition 表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后再试图证明该分区不需要被扫描，因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。
可以使用EXPLAIN 命令显示一个规划在constraint_exclusion 关闭和打开情况下的不同：
6.1 约束排除关闭
set constraint_exclusion = off;
explain select count(*) from partition_main where insert_date >= DATE '2014-01-01'; QUERY PLAN -------------------------------------------------------------------------------------------------explain select count(*) from partition_main where insert_date >= DATE '2014-01-01'; QUERY PLAN
从上面的查询计划中可以看出，PostgreSQL 扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划：
6.2 约束排除开启
set constraint_exclusion = on; explain select * from partition_main where insert_date >= DATE '2014-02-01'; QUERY PLAN
----------------------------------------------------------------------
Append (cost=0.00..2.02 rows=3 width=8)
-> Seq Scan on partition_main (cost=0.00..0.00 rows=1 width=8)
Filter: (insert_date >= '2014-03-01'::date)
-> Seq Scan on partition_201403 (cost=0.00..1.01 rows=1 width=8)
Filter: (insert_date >= '2014-03-01'::date)
-> Seq Scan on partition_201404 (cost=0.00..1.01 rows=1 width=8)
Filter: (insert_date >= '2014-03-01'::date)
(7 行记录)
可以看到，PostgreSQL 只扫描四月份以后的分区表。
八、可选的分区方式
还可以通过建立规则的方式进行分区。
CREATE RULE insert_tbl_partition_201401 AS ON INSERT TO partition_main WHERE ( insert_date >= DATE '2014-01-01' AND insert_date < DATE '2014-02-01' ) DO INSTEAD INSERT INTO partition_201401 VALUES (NEW.*); CREATE RULE insert_tbl_partition_201402 AS ON INSERT TO partition_main WHERE ( insert_date >= DATE '2014-02-01' AND insert_date < DATE '2014-03-01' ) DO INSTEAD INSERT INTO partition_201402 VALUES (NEW.*); ....省略
八、注意事项
VACUUM 或 ANALYZE tbl_partition 只会对主表起作用，要想分析表，需要分别分析每个分区表。
九、分区表之 自动建表，
上述方法，建表 等工作 由人工完成。
下面的触发器，将会自动完成数据的插入，建表等工作。
CREATE OR REPLACE FUNCTION tbl_partition_insert_function()
RETURNS trigger AS
$BODY$
DECLARE tbl_name CHARACTER VARYING;
tbl_name_main CHARACTER VARYING='partition';
tbl_name_salve CHARACTER VARYING;
tbl_year INTEGER;
tbl_month INTEGER;
create_tbl_sql CHARACTER VARYING;
insert_tbl_sql CHARACTER VARYING;
index_sql CHARACTER VARYING;
BEGIN
SELECT date_part('year',NEW.insert_date::date) INTO tbl_year;
SELECT date_part('month',NEW.insert_date::date) INTO tbl_month;
IF(tbl_month<10)THEN
tbl_name_salve=tbl_year||'0'||tbl_month;
ELSE
tbl_name_salve=tbl_year||tbl_month;
END IF;
tbl_name=tbl_name_main||'_'||tbl_name_salve;
--判断表 时间分段表是否存在
IF ( (SELECT count(relname) FROM pg_class WHERE relname=tbl_name)>0) THEN
insert_tbl_sql='INSERT INTO '||tbl_name ||' VALUES('||NEW.id||','''||NEW.insert_date||''')';
EXECUTE insert_tbl_sql;
ELSE
create_tbl_sql='CREATE TABLE '||tbl_name
||'('
|| 'CONSTRAINT '||tbl_name||'insert_date_check'||' CHECK (insert_date >='''||tbl_year||'-'||tbl_month||'-01''' ||' AND insert_date <''' ||tbl_year||'-'||tbl_month+1||'-01'')'
||')INHERITS (partition_main)';
EXECUTE create_tbl_sql;
index_sql='CREATE INDEX '||tbl_name||'insert_date_index ON '||tbl_name||'(insert_date)';
EXECUTE index_sql;
insert_tbl_sql='INSERT INTO '||tbl_name ||' VALUES('||NEW.id||','''||NEW.insert_date||''')';
EXECUTE insert_tbl_sql;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE比较：第一种人工建表的方法，触发器函数内无动态执行指令，故 只用prepare一次。
第二种自动建表的方法，触发器函数内包含数条动态执行指令，动态命令每次都需要prepare,带来性能上的折损。
5764
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
