pg分区表的实践

1、背景：

由于目前项目中使用到了分区表，仔细看了一下和项目的契合度也很高，因此就此来深入了解一下分区表的应用。

以前项目刚开始的时候，用的只是按某个业务id分表，后来随着业务量的增大，出现了一次比较大的事故，就是pg数据库发生了事务回卷的问题。

由于这个表实际上是一个数据表，每天都会有大量的数据插入进来，同时我们需要对历史数据
进行备份和删除处理，在备份完成后，就需要使用delete对原有数据进行删除，但是由于pg数据库的特性，在做了删除操作后，数据库中的磁盘空间并不会立马被释放，pg有AUTOVACUUM会在不影响正常数据库任务的时候做自动清理工作，从而清理磁盘空间。

可能是数据库过于繁忙，新增删除的数据量较大，导致AUTOVACUUM的清理速度一直跟不上事务的
增长速度，所以这个事务id所处的元组不能被正常回收，就会导致发生这种事务回卷的状况。从而使数据库进入到了一个自我保护的状态，导致后续服务的数据库写入请求都失败了。

从这以后，我们就开始进行了对数据库表的改造工作。而pg分区表似乎可以完美的契合我们项目的使用情况，

首先，我们项目基本是个大数据项目，在数据写入之后，几乎不会有修改的的操作，当我们对历史数据进行分析的时候，我们总是通过where条件中带业务时间字段的方式查询数据进行分析的，产出的结果也是5分钟、小时、 和天数据，少部分会有跨天查询的需求， 因此我们就可以就使用这个业务时间的字段进行按天做分区表，大部分的情况直接查询子表就可以了，少部分需要跨天查询的就用主表来查。
主要就是不会有其他字段单独作为条件来查询这张表，因此分区表的效率是很高的。

2、优势所在：

而分区表的优势之一，就是方便历史数据的删除，直接DROP掉历史子表就行了。现在冷热库的数据
备份，直接将子表备份到冷库后，再DROP子表也是更加方便，DROP表与delete from table的方式完全不同，因为子表是分区存储的，DROP表的方式会直接释放掉这个子表所占用的空间，不需要pg自动AUTOVACUUM来回收空间，从而避免pg数据库发生事务回卷的情况。

再来说说分区表的其他优点：

1. 对一张大表做分表，在合适条件下可以直接对子表做操作，性能更好。
2. 当需要跨多张子表一起查询时，也可以直接访问主表就行，从业务层表看就像没有分表一样。
3. 分区表是每一个子表都是分区存储的，因此可以放到不同的物理空间上，在需要删除历史数据时，可以直接使用Drop命令的方式删除子表，比直接从一张大表里面delete要方便和性能也要高得多。

3、适用场景：

从目前来看我们一张数据表虽然已经通过了某业务id进行了分表，如 ：
分区主表：表名_业务id
分区子表：表名_业务id_20220416
分区子表：表名_业务id_20220417 等等
但是实际上最近在做冷热库备份的时候看到，从分区主表的角度来看，有些类型的表已经达到了几十亿的数据量，分区子表也有几百万每天的量进来，如果没有对它进行按天做分区表的话，查询性能可能已经慢到无法支撑数据的正常运转了。

一般情况下，我们每一天晚上都会跑任务，将这一天的数据进行数据的分析、展示等写入到结果表中，因此直接查询子表就行了，而这也是效率最高的，它会比通过时间查询主表的方式性能会稍高。
只有偶尔的需要拿好几天的数据出来一起做分析处理，才会通过主表来查询。

特别要注意的是分区键的选取，一定要是最常用的，如果需要使用到主表进行查询的话，在查询条件中一定要带上进行这个分区键的字段，就像现在我们业务系统里就是一个带时间的业务字段，因为查询主表时需要通过这个分区键去决定访问哪些物理分区。

如果查询条件中不带这个分区键的话，系统便没有办法判断需要查询哪些分区，就只能访问所有的物理分区来查询数据，这样的效率无疑是最低的，业务中不应该有这样的需求，假如一个系统确实存在很多这样的需求，那么要么就是分区键没有选对，要么就是本身业务就根本不适合使用分区表。

4、创建分区表

下面做一个最简单的创建分区表的示例：

创建主表及其索引
CREATE TABLE "public"."table_name" (
  "id" int4 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "create_time" timestamp(0) NOT NULL
);
CREATE INDEX "time_idx" ON "public"."table_name" USING btree (
  "create_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);
创建分区子表及其索引
CREATE TABLE table_name_20220416(check(create_time >= '2022-04-16' and create_time < '2022-04-17')) inherits(table_name);
CREATE INDEX "20220416_time_idx" ON "public"."table_name_20220416" USING btree (
  "create_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);

1. 一般创建分区表，首先是创建主表
2. 再创建一个分区子表继承于这个主表
3. 如果有索引或者约束，子表也需要单独去建索引和约束