PostgreSql数据库分表

2020-06-17 00:00:00 数据 时间 分区表 组件 添加

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. 获取后一次分表时间的方法

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

相关文章