POSTGRESQL UPDATE 操作 没那么容易

最近在 NC 呆了一段时间，公司的PG 使用量是蛮大的，库的使用量不小，单表的数据量在100多G的也比较多，数据库也都是上T 的规模。问题主要在数据量大，PG数据库使用的方式有一些问题，当然这个和业务逻辑因素以及数据库上下游的数据需求都有关。

主要是客户UPDATE 的需求多，一张上亿行的表，由于业务原因要更新这张表本身的70%的数据，9000万行数据。所以这期的题目是 update 操作没有那么容易，指的是数据量的问题，以及更新时间的双重问题。 

1  数据量大   2 更新不能影响业务，业务的工作的时间基本上在早7：00AM --  晚 10：00 PM  

更新数据不能影响业务，同时更新还有如下几个注意的问题

1   更新的语句并不是一条，而是根据条件给了165条语句

2   每个更新语句的条件不一样，更新的数据量不知道多少

3   更新库的内存并没有太大，对比数据量来说，数据库的内存有点节省

4   数据库是云上数据库，并非本地数据库

5   数据做了逻辑数据复制槽，以及数据抽取的工作

好了条件给清楚了，下面是怎么做的问题， 当然你可以将语句定时去执行，并且根据时间到一定时间就不做了，然后看看做几天，最后回复工作完毕。

但实际上这样做了，那就的几个晚上就不用睡觉了。 

我们来看看如果遇到这样的问题有没有什么好的办法

1首先先对UPDATE 语句的执行计划进行分析，当时分析出四种方式进行UPDATE ，并对每种UPDATE的方式进行性能比对。

2 预定处理目标的时间消耗，产生一个全量的测试库，并且将UPDATE 语句转换为select 语句，在测试库将每个UPDATE 的行数进行详细的统计。

3  根据产生的得出的每个UPDATE 语句需要更新的数据行数，来调出典型的数据量，在测试库中做几个UPDATE 统计时间。

4  根据UPDATE 统计的时间，除以行数，最终得出每行更新的时间，然后通过计算，将每个UPDATE 的时间进行计算，得出每个UPDATE 需要的时间。

5   根据PG的原理，将较大的事务拆分，例如将每次更新几百万行的UPDATE SQL 进行时间维度的拆分，大拆小。

6    根据云数据库特性查看备份的时间，UPDATE 的时间避开备份的工作时间

7    查看在测试库中UPDATE的数据库性能分析，查看对于内存以及CPU 等指标的影响，以及dead tuple的情况

8    针对UPDATE 后对于表进行 VACUUM 和 ANALYZE 操作时间的测试和预估。

最终生成一次大表更新的整体计划。

实际上这样的操作也可以应用到其他数据库同类型的工作，但是这里也有一些问题。

1  即使拆分成多个时间维度，但由于业务希望快速完成的时间限制，对于wal log 生成的量并没有控制，导致下游大数据在短时间收获了大量的wal  日志的数据，造成了数据的积压。

2 优化的手段不充分，后期会考虑针对这样的大型密集型UPDATE 对于work_mem  的SESSION 手段的调节。

3  对于云数据库checkpoint 的配置参数的调整，优化一些我们可以做的性能。

4 在工作前进行分析，如果业务催的急，并且我们可以付出成本，则提高数据库的内存，对于后期maintanence 的维护操作都是有利的。

5  针对后期表的fillfactor 的调整，尤其对于频繁更新的表我们的在建表的时候考虑fillfactor 的调节。

6   根据情况分析出一些不使用的索引，在UPDATE 时将与之有关的索引清理了。

但如果是实体机，估计还可以调整一些参数

maintenacne_work_men

checkpoint_completion_target

wal_buffer

min_wal_size

max_wal_size

max_worker_processes

当然如果需要也可以执行 pg_repack 将表进行整体的彻底的处理。

最后是注意更新需要的磁盘空间，如果磁盘空间不够的情况下，那以上的工作都是无效的，所以提前预估你需要的UPDATE 后占用的空间。

总结，一次UPDATE 的工作需要在业务逻辑，物理参数调整，UPDATE语句的优化， 以及防止大事务，+ 最后的表善后等等进行全方位的统计和规划。