本文主要是介绍PostgreSQL分区表(partitioning)应用实例,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
前言
项目中有需求要垂直分表,即按照时间区间将数据拆分到n个表中,PostgreSQL提供了分区表的功能。分区表实际上是把逻辑上的一个大表分割成物理上的几小块,提供了很多好处,比如:
查询性能大幅提升删除历史数据更快可将不常用的历史数据使用表空间技术转移到低成本的存储介质上
那么什么时候该使用分区表呢?官方给出的指导意见是:当表的大小超过了数据库服务器的物理内存大小则应当使用分区表,接下来结合一个例子具体记录一下创建分区表的详细过程。
创建分区表
首先看一下需求,现在有一张日志表,现在需要按表中的操作时间字段(operation_time)分区,如下图:
这个需求就是一个典型的按时间创建分区表,首先看一下步骤:<喎�"/kf/ware/vc/" target="_blank" class="keylink">vcD4NCrS0vai4uLHttLS9qG649tfTse2jrMO/uPbX07HttrzKx7zMs9DT2ri4se22qNLl0ru49rnm1PKjqFJ1bGWjqbvytKW3osb3o6hUcmlnZ2Vyo6mjrLDRttTW97HttcTK/b7dsuXI69bYtqjP8rW9us/KyrXEt9bH+LHtDQo8cD7I58nPy/nKvqOs1fvM5bXEtPOyvdbovs231s6q0tTJz8j9uPajrLWxyLu7ub/J0tTT0NK70KnQobXE08W7r7TryqmjrLHIyOe21NPaw7+49rfWx/ijrNTaudi8/NfW19a2zsnPtLS9qNK7uPbL99L9tci1yKGjytfPyMC0v7S12tK7sr0mbWRhc2g7Jm1kYXNoO7S0vai4uLHtoaM8L3A+DQo8cD7U2rS0vai31sf4se3Wrsew06a1sc/ItLS9qNK71cUmbGRxdW87uLix7SZyZHF1bzujrMv509C31sf4se22vLTTy/y8zLPQo6zV4rj2se3W0MO709DK/b7do6zSsrK70qrU2tXiuPax7cnPtqjS5cjOus687LLp1LzK+Lywy/fS/aOsz9bU2s7Sw8e+zc/ItLS9qNXi0fnSu9XFse2jrLWr1q7HsM/IvajSu7j20PLB0KO6PC9wPg0KPHByZSBjbGFzcz0="brush:sql;"> CREATE SEQUENCE "public"."t_sys_log_main_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 99999999 START 1 CACHE 1; ALTER TABLE "public"."t_sys_log_main_id_seq" OWNER TO "postgres";
接下来创建“父表”,因为是日志表,所以表名命名为“t_sys_log_main”:
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
|
运行以上DDL语句创建父表,创建成功后接下来就可以挨个创建分区表了,由于每个分区表都是从父表继承的,所以分区表不会增加任何字段,下面我们按需求创建4张分区子表,分别用于存放9月、10月、11月和12月的日志数据:
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
如上所示,运行完成后即可创建4张分区子表,在上面的语句中我们添加了一个约束表示只允许插入本月的数据,接下来在这4张分区表的每个分区键上建立索引:
?
1 2 3 4 |
|
到此为止我们的分区表就创建完毕了,接下来需要考虑数据插入的问题,如何才能让不同日期的数据自动的插入与其对应的分区子表中呢?有两种解决方案,分别是:规则(Rule)和触发器(Trigger),相比触发器,Rule的开销更大,所以我在这里就不做过多介绍了,下面直接介绍Trigger的方式。
Trigger通常会结合自定义函数(Function)来实现分区插入,Function负责根据条件选择插入,而Trigger则负责Function的自动调用。首先定义Function,功能很简单,即根据日期区间insert数据即可:
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
|
最后再创建触发器用于执行刚才的Function:
?
1 2 3 |
|
到这里就全部创建完成了,最后测试一下看看结果。为了确认我们的触发器的确触发了,我们打开存储过程的统计开关,在postgresql.conf中,找到track_functions,改成all:
接下来就可以运行几条测试insert语句来看看是否能把指定的时间记录分别插入到与其对应的分区子表中,插入之前先看下sys_log_insert_trigger()的统计信息:
可以看到目前没有统计记录,接下来插入几条测试数据:
?
1 2 3 4 5 6 7 8 9 10 11 12 |
|
紧接着再看一下sys_log_insert_trigger()的统计信息:
如上图,可以看出调用了6次函数,因为我们插入了6条数据,至此分区表由创建到测试的整个过程就已经成功完成了。
总结
简单记录一下PostgreSQL创建分区表的完整步骤以及注意事项,希望对遇到同样问题的朋友有所帮助,The End。
这篇关于PostgreSQL分区表(partitioning)应用实例的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!