postgresql表分区配置

概述

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

表分区存在以下优点：

1. 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度；
2. 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3. 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4. 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

在PostgreSQL中借助继承特性，可以很方便的实现表分区，因为继承特性已经解决了update、select、delete在父表操作时映射到子表的问题，在做表分区时，我们只要解决insert问题就可以了。下面就让我们来实际体验一下。

建立主表

本手册中我们将通过一个用户表的示例来进行演示。我们先来建立用户主表。

create table users( id serial, age integer );

建立分表

接下来我们来建立各个分表，在我们的实例中，我们一共需要建立单个分表，分别是：users_0，users_1，users_2。其中约束关系如下所示：

1. users_0存储age <= 5的数据；
2. users_1存储 10 >= age > 5的数据；
3. users_2存储age > 10的数据。

具体DDL如下所示：

create table users_0( check(5>=age) ) inherits(users);

create table users_1 ( check(10>=age and age>5) ) inherits(users);

create table users_2 ( check(age>10) ) inherits(users);

建立规则

现在我们建立了一个主表，三个子表。根据PostgreSQL继承的特性，在子表上的数据可以被主表 select、update、delete，而现在insert我们需要建立规则，让在主表insert的数据转入各个分表中。当然，转入规则也就是我们建立分表同时附带的check检查。具体DDL如下所示：

create rule users_insert_0 as on insert to users where(age <= 5) do instead insert into users_0 values(NEW.id, NEW.age);

create rule users_insert_1 as on insert to users where(age > 5 and age <= 10) do instead insert into users_0 values(NEW.id, NEW.age);

create rule users_insert_2 as on insert to users where(age > 10) do instead insert into users_0 values(NEW.id, NEW.age);

写入数据—测试

测试数据

现在我们已经建立了users表的插入规则，我们程序中透明使用分区，对于程序只有users表，而不会存在users_0，users_1，users_2这些分表。我们插入些数据验证我们的配置是否正确。具体DDL如下所示：

insert into(age) users vales(3);
insert into(age) users vales(6);
insert into(age) users vales(16);

现在3条测试数据已经全部写入users表，并且根据约束条件转入了各个分表，如下图所示：

检测约束条件

这时，让我们来测试一下如下所示的select语句：

explain analyze select * from users where age <= 5

这条语句并不会直接去执行select查询，实际上，这是一条针对这条select语句的解释说明语句，我们在进行性能优化时经常会用到“explain analyze”语句。执行后出现的结果如下所示：

从上图中我们可以看到，数据在查询时只查询了users_0表，并未针对所有分表进行查询，这是因为数据库考虑到了check条件。

将到这里顺便提一句，之所以数据库会考虑check条件，还要和postgresql.conf中的一个名为constraint_exclusion的配置项有关，这个参数的含义是：当数据库执行计划时是否考虑表上的约束。这个参数有三个选项：off，on，partition。默认值是partition。

其他

1.不要使用自增id作表分区条件。这会导致一些关于自增ID的增长问题。

2.选取作为check条件的字段应该是不会经常更新的字段，因为一旦对其进行更新，有可能导致一些问题，我们试着执行下面的语句：

update users set age = 7 where id = 3

id为3的数据的age原来是3，现在要将其更改成7，则会造成如下错误：

这是因为原来age=3的数据是在users_0表中，而修改之后该条数据违反了users_0中的check，从导致了这个错误。

转载于:https://my.oschina.net/manzjl/blog/147217