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）仅支持范围分区和列表分区，尚未支持散列分区。

特别说明：

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

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

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;

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

select *,tableoid from  partition_main

id | 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)，之后再让该表重新继承主表。

	(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 = partition	# on,off,or partition

set constraint_exclusion = off;                                                 

explain select count(*) from partition_main where insert_date >= DATE '2014-01-01';
                              QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
explain ';
                                           QUERY PLAN 

set constraint_exclusion = ;                                                  

explain select * 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 行记录)

CREATE RULE insert_tbl_partition_201401 AS
ON INSERT TO partition_main WHERE
    ( insert_date 2014-01-012014-02-01 )
DO INSTEAD
    INTO partition_201401 VALUES (NEW.*);

RULE insert_tbl_partition_201402 AS
2014-03-01INTO partition_201402 ....省略

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