postgresql笔记019—详解分区

分区

分区概述

分区的意思是把逻辑上的一个大表分割成物理上的几块。分
使用分区的好处：

1. 使用分区时，可以对某些类型的查询性能得到极大的提升。特别是表中访问率比较高的行，位于一个单独分区或少数一个分区上的情况下。分区可以减少索引体积，从而可以将高使用率部分的索引存放在在内存中。如果索引不能全部存放在内存中，那么在索引上的读和写都会产生更多的磁盘访问。

2.当查询或更新一个分区的大部分记录时，连续扫描那个分区而不是使用索引离散的访问整个表，可以获得巨大的性能提升。

3.如果需要大量加载或删除的记录位于单独的分区上，那么可以通过直接读取或删除那个分区以获得巨大的性能提升。因为alter table on inherit 和drop table 比操作大量的数据要快的多。这些命令同时还可以避免由于大量delete 导致的 vacuum超载。
4.很少用的数据可以移动到便宜一些的慢速存储介质上。

不过这种好处通常只有在表可能会变的非常大的情况下才有价值。到底多大的表会从分区中受益取决于具体的应用，不过有个基本的默认规则就是表的大小超过了数据库服务器的物理内存大小。

postgresql可以实现下面形式的分区：
范围分区：
表被一个或者多个关键字段分区成“范围”，这些范围在不同的分区里没有重叠。
比如，我们可以通过实践范围分区，或者根据特定业务对象标识范围分区。

列表分区
通过明确地列出每个分区里应该出现哪些关键值实现。

实现分区

设置一个分区的表，需要实现以下步骤：
1.创建 主表 ，所有分区都从它继承。
这个表中没有数据，不要在这个表上定义任何检查约束，除非你希望约束同样也适用于所有分区。
同样，在其上定义任何的索引，或者唯一约束也没有任何意义。

2.创建几个 子表 ，每个都从主表上继承。通常，这些表不会增加任何字段。
我们将把子表称为分区，尽管它们就是普通的postgresql表。

3.给分区表增加约束，定义每个分区允许的键值。
经典案例：

check (x=1)
check ( country in ('china','japan','american'))
check ( old >=0 and old <120)

却表这些约束能够保证在不同的分区里不会有重叠的键值。

4.对于每个分区，在关键字字段上创建一个索引，以及其他你想创建的索引。
关键字字段索引并非严格必需的，但是在大多数情况下，它是有帮助的。如果你希望关键字值是唯一的，那么我们应该给每个分区创建一个唯一或者主键约束。

5.定义一个规则，或者触发器，来重定向数据插入主表到适当的分区。

6.确保postgresql.conf里的配置参数constraint_exclusinon是打开的。没有这个参数，查询不会按照需求进行优化。

案例：构造一个冰淇淋公司数据库。该公司每天都测量最高温度，以及每个地区的冰淇淋销售。创建如下一个表。

drop table measurement cascade;
create table test.measurement(
	city_id int not null,
	logdate date not null,
	peaktemp int,
	unitsales int
);

create table test.measure01 (
	check(logdate <'2010-01-01')
) inherits (test.measurement);


create table test.measure02 (
	check (logdate >= '2010-01-01' and logdate <'2020-01-01')
) inherits (test.measurement);

create table test.measure03 (
	check(logdate >='2020-01-01')
) inherits ( test.measurement);

如上面所示，创建一个主表和三个分区表。
当我们往三个分区表中插入数据的时候，也会同时向主表中插入数据。
但是往主表measurement 插入数据的时候，分区表中并不会同时插入数据。
想实现往主表中插入数据时，会自动向分区表中插入数据需要我们写一个触发器。

先写一个函数 measurement_insert_triggera

create or replace function measurement_insert_triggera()
returns trigger as $$
begin
if (new.logdate < date '2010-01-01') then 
	insert into measure01 values(new.*);
elsif(new.logdate >= date '2010-01-01' and new.logdate <  date '2020-01-01') then 
	insert into measure02 values(new.*);
elsif(new.logdate >= date '2020-01-01'  ) then
	insert into measure03 values(new.*);
else 
	raise exception 'date out of range, fix the measurement_insert_tiggera() function;';
end if ;
return null;
end;
$$
language plpgsql;

然后创建一个触发器：

drop trigger  insert_measure_trigger on measurement;
create trigger insert_measure_triggera
	before insert on measurement	
	for each row execute procedure measurement_insert_triggera();

删除触发器：

drop trigger  insert_measure_triggera on measurement;

向主表中插入数据：

insert into measurement values('004','2000-05-06',2333,233);

根据触发器，向主表中插入一条数据后，也会向分区表measure01表中插入一条数据。

select * from test.measure01;

结果如下：