PostgreSQL的分区表（分区）应用实例详解

前言

在项目中，有一个需要拆表垂直，即数据分割成n表根据时间间隔，和PostgreSQL提供了分区表的功能。事实上，分区表，将一个逻辑大表分成几小块物理，提供许多好处，如：

1。查询性能大大提高。

2。删除历史数据更快。

三.将表空间技术用于低成本存储介质可以用于不常用的历史数据。

我们应该什么时候使用分区表官方的指令是：当表的大小超过数据库服务器的物理内存大小时，应该使用分区表。接下来，结合一个例子，记录创建分区表的详细过程。

创建分区表

首先，看看需求。现在有一个日志表，现在你需要按operation_time分区表中，如下：
这个需求是由时间创建的典型分区表。首先，看看台阶：

1。创建父表

2。创建一个n子表，其中每一个是从父表继承

三.定义规则（规则）或触发器（触发器）将主表上的数据插入重定向到适当的分区表。
如上所示，整个大步骤分为三个部分，当然也有一些小的优化措施，例如，对于每个分区，在关键字段上创建索引等。

在创建分区表之前，我们应该首先创建一个父表。所有分区表都是从它继承的。表中没有数据，也没有定义表上的任何检查约束和索引。现在我们先创建这样一个表，但是我们需要先建立一个序列。
创建序列公共。t_sys_log_main_id_seq
增量1
自动1
99999999次
开始1
缓存1；

更改表公共。t_sys_log_main_id_seq业主Postgres ；

接下来，父表的创建，因为它是一个日志表，所以表的名字命名为t_sys_log_main：

创建表公共。t_sys_log_main （
身份（'t_sys_log_main_id_seq INT4默认nextval '：：regclass）不为空，
account_affiliation_codevarchar（100）整理违约不空，
account_affiliationvarchar（50）整理违约不空，
operation_time 时间戳（6）不为空，
operation_keyvarchar（2）整理违约不空，
operation_valuevarchar（30）整理违约不空，
operation_loginidvarchar（100）整理违约不空，
operation_messagevarchar（300）整理违约不空，
operation_ipvarchar（30）整理违约不空
）
与（OID = false）

；
对表公共的注释。t_sys_log_main 是系统日志表；
评论列公共。t_sys_log_main 。account_affiliation_code 是组织机构代码帐户；
评论列公共。t_sys_log_main 。account_affiliation 的帐户组织；
评论列公共。t_sys_log_main 。operation_time 运行时间；
评论列公共。t_sys_log_main 。operation_key 的操作类型（重点）；
评论列公共。t_sys_log_main 。operation_value 的操作类型（值）；
评论列公共。t_sys_log_main 。operation_loginid 是操作帐户；
评论列公共。t_sys_log_main 。operation_message 操作信息；
评论列公共。t_sys_log_main 。operation_ip 是登录地址；

更改表公共。t_sys_log_main添加主键（ID）；

运行DDL语句创建一个表，然后你可以创建一个成功的创建分区表，因为每一个分区表是从父表继承，所以分区表将不会增加任何领域，我们根据创建日志数据存储在九月，十月的4个分区表的需要十一月和十二月：

创建表t_sys_log_y2016m09
（检查（operation_time日期'2016-09-01'and operation_time <日期> = '2016-10-01 '））
继承（t_sys_log_main）；

创建表t_sys_log_y2016m10
（检查（operation_time日期'2016-10-01'and operation_time <日期> = '2016-11-01 '））
继承（t_sys_log_main）；

创建表t_sys_log_y2016m11
（检查（operation_time日期'2016-11-01'and operation_time <日期> = '2016-12-01 '））
继承（t_sys_log_main）；

创建表t_sys_log_y2016m12
（检查（operation_time日期'2016-12-01'and operation_time <日期> = '2017-01-01 '））
继承（t_sys_log_main）；

如上所示，在运行之后，我们可以创建4个分区子表。在上面的语句中，我们添加了一个约束表达式，只在这个月插入数据。接下来，在4个分区表的每个分区键上构建索引。
创建t_sys_log_y2016m09指数t_sys_log_y2016m09_operation_time（operation_time）；
创建t_sys_log_y2016m10指数t_sys_log_y2016m10_operation_time（operation_time）；
创建t_sys_log_y2016m11指数t_sys_log_y2016m11_operation_time（operation_time）；
创建t_sys_log_y2016m12指数t_sys_log_y2016m12_operation_time（operation_time）；

到目前为止，我们已经创建了分区表。接下来，我们需要考虑数据插入的问题。我们如何自动将数据从不同的日期插入到相应的分区子表中呢有两种解决方案，即：规则（规则）和触发器（触发器）。与触发器相比，规则的代价更大。所以我没有做很多介绍。直接引入触发器的方法。

触发器通常将分区函数与函数（自定义函数）结合起来。函数负责根据条件插入，而Trigger负责函数的自动调用。首先定义函数，函数非常简单，即根据日期间隔插入数据即可：
创建
或替换功能（sys_log_insert_trigger）返回触发美元
开始

（如果
new.operation_time > =日期'2016-09-01
和new.operation_time <日期'2016-10-01
然后）
插入t_sys_log_y2016m09
价值观
（新的）；
（
new.operation_time > =日期'2016-10-01
和new.operation_time <日期'2016-11-01
然后）
插入t_sys_log_y2016m10
价值观
（新的）；
（
new.operation_time > =日期'2016-11-01
和new.operation_time <日期'2016-12-01
然后）
插入t_sys_log_y2016m11
价值观
（新的）；
（
new.operation_time > =日期'2016-12-01
和new.operation_time <日期'2017-01-01
然后）
插入t_sys_log_y2016m12
价值观
（新的）；
其他的
提高exception'date超出范围！；
结束
如果返回null；
结束语plpgsql美元；

最后，创建触发器来执行函数：
创建触发器在t_sys_log_main之前sys_log_insert_trigger
每一行
执行程序sys_log_insert_trigger（）；

一切都在这里完成。最后，测试的结果。为了证实我们的触发是触发，我们打开统计开关的存储过程，发现track_functions在postgresql.conf，并改变它的所有。

接下来，您可以运行几个测试INSERT语句，看看是否可以插入指定的时间记录到它们相应的分区子表中。在你插入，看看sys_log_insert_trigger统计（）。

您可以看到现在没有统计记录，然后插入了几个测试数据。
插入t_sys_log_main值
（1，'200022 '，'西安高新中学区，'2016-9-8 18:49:26.004，01，'xx '，'zhsz_t，测试！，'127.0.0.1）；
插入t_sys_log_main值
（1，'200023 '，'西安高新中学区，'2016-9-12 18:49:26.004，01，'xx '，'zhsz_t，测试！，'127.0.0.1）；
插入t_sys_log_main值
（1，'200024 '，'西安高新中学区，'2016-10-8 18:49:26.004，01，'xx '，'zhsz_t，测试！，'127.0.0.1）；
插入t_sys_log_main值
（1，'200025 '，'西安高新中学区，'2016-11-8 18:49:26.004，01，'xx '，'zhsz_t，测试！，'127.0.0.1）；
插入t_sys_log_main值
（1，'200026 '，'西安高新中学区，'2016-12-8 18:49:26.004，01，'xx '，'zhsz_t，测试！，'127.0.0.1）；
插入t_sys_log_main值
（1，'200027 '，'西安高新中学区，'2016-12-25 18:49:26.004，01，'xx '，'zhsz_t，测试！，'127.0.0.1）；

再看看sys_log_insert_trigger统计（）。

如上图所示，我们已经看到6个函数被调用了，因为我们插入了6个数据，到目前为止，分区表已经成功地从创建到测试完成了。

总结

简单的记录，创建一个分区表的完整步骤及注意PostgreSQL，希望有助于朋友们遇到同样的问题，结束。