PosrgreSQL 删除线上大表历史记录实践

Published: 2021-07-27

Tags: PosrgreSQL

本文总阅读量次

PostgreSQL 单表如果达到几亿条量级，且更新频繁，就容易发生问题，出于性能及稳定性考虑，需要对一些较大的表的历史记录进行删除。

考虑业务需求，最终数据清理规则如下：

  • 半年前的历史数据全部移除
  • 近一个月不活跃的用户，数据全部删除

历史表虽然很大，好在之前做过 Hash 分表，数据被分散在 100 个子表中

资源消耗

线上环境移除数据，首先要考虑的就是资源的消耗，直接执行 DELETE 轻则造成 CPU 消耗飙升，请求响应变慢，重则可能导致数据库性能下降，连锁反应下以至数据库崩溃。

所以删除方案重要的是减少资源消耗，删除速度没有那么重要

最终方案

使用两个定时/后台任务移除历史数据

  1. 每天凌晨 2:00 在 CPU 闲置时按子表循环删除六个月前的记录。
  2. 按用户 ID 到指定子表中删除记录，每次处理一个用户。

凌晨定时删除六个月前一天的数据

从数据库的 CPU 波动来看，资源消耗符合预期

通过日志也可以看到定时任务每天都在努力的工作

[2021-07-26 18:37:06] history_93 delete 139141 record, use 22.41s
[2021-07-26 18:37:29] history_94 delete 145750 record, use 23.24s
[2021-07-26 18:37:54] history_95 delete 141329 record, use 24.82s
[2021-07-26 18:38:17] history_96 delete 139971 record, use 23.35s
[2021-07-26 18:38:41] history_97 delete 141501 record, use 23.72s
[2021-07-26 18:39:04] history_98 delete 137353 record, use 23.24s
[2021-07-26 18:39:28] history_99 delete 135638 record, use 23.94s

这里没有什么特别的说明，Crontab 任务如下：

0 18 * * * /usr/bin/python3.7 -u /opt/task/delete_n_months_ago.py >> /opt/task/del.log

按用户删除数据的考量

最初的计划是每次删除 10 个不活跃用户的历史记录，但是经过测试发现，批量 DELETE 操作，PostgreSQL 没有自动判断用户所在的子表（所用数据库版本 Pg11），而是关联了 100 张子表并进行索引查找删除。

了解到在查询时，PostgreSQL 规划器能够正确判断用户所在子表。考虑到分表是对 user_id 进行哈希分表，一个用户的历史只会存在一个子表中，那么关联 100 张子表是无意义的操作。

似乎可以在删除的时候手动指定子表，但是查询发现 PostgreSQL 没有公开函数支持根据 Hash Key 判断记录存储在哪一张子表，一种折中的办法是，通过 EXPLAIN 的输出可以看到用户记录所在的子表。

于是删除不活跃用户的历史数据可以这样改造：

  1. 按用户删除，判断用户有没有历史，没有则跳过。
  2. 通过 Explain 命令解析出用户记录所在子表。
  3. 直接在子表上删除指定用户的历史。

最开始没仔细考量，按用户直接批量删除的时候性能很差

优化后按用户删除，速度起飞 🛫️

速度提升的关键是借助 EXPLAIN 手动指定子表，从根本上避免了 100 张子表扫描

-- 查询用户是否有历史数据
select user_id from history where user_id = 'user_id' limit 1;

-- 判断用户所在子表
explain select user_id from history where user_id = 'user_id' limit 1

-- 删除用户金币数据
delete from history_17 where user_id = 'user_id'

解析 EXPLAIN 命令获取子表

def get_partition_tablename(cursor, user_id):

    sql = f"explain select user_id from history where user_id = '{user_id}' limit 1"
    cursor.execute(sql)
    text = cursor.fetchall()

    match = re.findall( r'.*(coin_history_[0-9]+)\s.*', str(text))
    tablename = match[0]
    return tablename

待清理用户暂存

待删除用户信息保存到了一个数据库表中，处理后修改用户 status 字段（0-待处理，1-已清理）

负责删除的消费者脚本可以从数据库中批量获取用户进行处理，消费者添加待处理用户到表中也很简单

-- 批量获取待清理用户
SELECT user_id FROM history_del WHERE status = 0 LIMIT 10

-- 设置已清除标识位
UPDATE history_del SET status = 1 WHERE user_id = 'UUTEST001'

-- 消费者添加待清理用户
INSERT INTO history_del (user_id)
SELECT user_id FROM users WHERE updated < CURRENT_DATE - INTERVAL '1 months' and updated >= CURRENT_DATE - INTERVAL '6 months'
ON CONFLICT (user_id) DO NOTHING;

把 添加待清理用户 也添加到定时任务，可以使移除历史数据的任务能够全自动运行

补充：单表批量删除

如果需要清理的大表没有分区表，需要批量删除减小资源消耗，那么可以利用 LIMIT 进行删除。

EXPLAIN ANALYZE WITH rows AS (
    SELECT
      id
    FROM
      big_table
    ORDER BY id
    LIMIT 10
  )
  DELETE FROM big_table
  USING rows WHERE big_table.id = rows.id
  ;

UPDATE 与 DELETE 可以借助 CTE 批量更新删除

参考：https://blog.crunchydata.com/blog/simulating-update-or-delete-with-limit-in-postgres-ctes-to-the-rescue