是否应拆分大规模级联DELETE事务？PostgreSQL超时异常求解

问题背景 #

需要从bills表删除约1600万条记录，同时通过bill_id外键级联删除另外四张表的关联记录，数据库执行耗时超1小时。使用的JDBC代码如下：

public int deleteExpiredBills(final Integer someDate, final Set<Integer> itemIdsCanNotBeDeleted) {
    return namedParameterJdbcTemplate.update(
            "DELETE FROM bills WHERE expired_date::date < CURRENT_DATE - :someDate " +
                    "AND item_id NOT IN (:itemIdsCanNotBeDeleted)",
            new MapSqlParameterSource(Map.of(
                    "someDate", someDate,
                    "itemIdsCanNotBeDeleted", itemIdsCanNotBeDeleted)
            )
    );
}

调用该查询时Spring Boot应用抛出异常，但PostgreSQL仍会在一段时间后完成删除操作：

com.zaxxer.hikari.pool.ProxyConnection - HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@1de77ce0 marked as broken because of SQLSTATE(08006), ErrorCode(0)
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

问题解答 #

1. 异常是否由事务耗时过长导致？ #

是。这个I/O异常本质是长时间事务占用数据库连接，导致Hikari连接池判定连接失效，或是数据库端因事务执行过久主动断开了与应用的连接。PostgreSQL后台继续执行删除，是因为数据库已接收并启动了删除操作，即便应用端连接断开，数据库仍会完成剩余执行，但应用无法获取最终的执行结果与状态。

2. 此类场景的最优解决方案是什么？ #

最优方案需围绕降低单事务负载、减少锁竞争、提升执行效率展开：

• 优先采用分批删除：这是处理大数量删除的核心手段，避免单事务处理百万级数据引发的日志膨胀、锁表、连接超时问题。
• 优化查询索引：给expired_date和item_id创建联合索引，避免全表扫描拖慢删除速度，例如：CREATE INDEX idx_bills_expired_item ON bills(expired_date, item_id);
• 替换级联删除为手动批量删除关联表：级联删除会隐式触发关联表操作，且难以控制批量规模。可先查询出待删除的bill_id批次，依次删除关联表数据，最后删除bills表，实现更可控的操作流程。
• 选择业务低峰期执行：避免删除操作占用过多资源，影响正常业务的数据库性能。
• 优化过滤条件：若itemIdsCanNotBeDeleted集合过大，NOT IN会降低查询效率，可改用NOT EXISTS或临时表关联过滤。

3. 是否应使用LIMIT n拆分事务并循环执行直至无记录可删？ #

完全应该，这是处理大数量删除的标准实践，具体实现要点：

• 选择合适的LIMIT值：建议每次删除1000-10000条（根据数据库性能调整，太小会增加交互次数，太大仍可能导致单次执行超时）。
• 循环执行直到删除行数为0：确保所有符合条件的记录都被清理。
• 每次循环使用独立事务：避免长时间占用事务，减少锁持有时间。
• 可选添加短暂休眠：避免连续执行给数据库带来过大压力。

示例代码思路：

public void deleteExpiredBillsInBatches(final Integer someDate, final Set<Integer> itemIdsCanNotBeDeleted) {
    final int batchSize = 5000;
    int deletedRows;
    do {
        deletedRows = namedParameterJdbcTemplate.update(
                "DELETE FROM bills WHERE expired_date::date < CURRENT_DATE - :someDate " +
                        "AND item_id NOT IN (:itemIdsCanNotBeDeleted) LIMIT :batchSize",
                new MapSqlParameterSource(Map.of(
                        "someDate", someDate,
                        "itemIdsCanNotBeDeleted", itemIdsCanNotBeDeleted,
                        "batchSize", batchSize)
                )
        );
        // 可选：每次批量删除后短暂休眠，降低数据库压力
        // Thread.sleep(100);
    } while (deletedRows > 0);
}

内容的提问来源于stack exchange，提问作者SorryForAsking