postgresql 数据库分库 pg数据库分库分表

1、背景

在项目组件的开发中，统计模块使用的表数据量较大，影响查询性能，需要进行分表处理。本文将介绍PostgreSql数据库表分区的策略以及其在巡查考评组件开发中的应用。

2、术语解释

• 主表：该表是创建子表的模板，它是一个正常的普通表，但是正常情况下它并不存储任何数据。
• 子表/分表：这些表继承并属于一个主表，子表中存储所有的数据。

3、问题分析

3.1 PostgreSql如何分表

数据库表分区把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。那么如何使用PostgreSql来实现这个功能呢？

1. 创建主表

2. 创建多个分表。每个分区表必须继承自主表，并且正常情况下都不要为这些分区表添加任何新的列

可以看到，这里我们为tb_patrol_detail_stat创建了一个2018年12月的子表。

3. 为分区表添加限制。这些限制决定了该表所能允许保存的数据集范围。这里必须保证各个分区表之间的限制不能有重叠

为子表tb_patrol_detail_stat_201812添加约束，限制该字表中只能存储statistic_time在2018年12月份的数据。这里需要注意的是添加约束时，约束的字段类型应与主表的分表字段的类型一致，否则会导致全表扫描。

4. 定义一个trigger或者rule把对主表的数据插入操作重定向到对应的分区表

添加了一个名为insert_tb_patrol_detail_stat_201812的规则，当向主表添加statistic_time在2018年12月份的数据时重定向到子表tb_patrol_detail_stat_201812中。

3.2 在应用组件中分表

上一节介绍了PostgreSql是如何实现分表的，本节将介绍如何在巡查考评组件中应用分表。

在巡查考评组件中，对数据量较大的表进行按月分表。分表策略如下：

1. 在系统启动时进行一次分表
2. 每月1号凌晨1点进行一次分表
3. 每次分表的逻辑如下图所示

注意：“最后一次分表时间”表示最晚的子表月份。

正常情况下，每次分表后最后分表时间都会晚于当前时间5个月，也就是会有5个月的富余子表。如果出现当前时间晚于上次分表时间，说明系统有一段时间没有进行分表了，需要把缺失的子表补上去。

4. 获取最后一次分表时间的方法

根据主表名称获取到子表插入规则，获取最晚的规则名的后缀即为最后一次分表时