面对千万级记录的级联 delete 操作，单次长事务易引发连接超时、锁表、内存溢出及服务阻塞；最优实践是结合分批删除（limit + 循环）、合理配置数据库超时参数，并辅以异步非阻塞执行，兼顾数据一致性与系统稳定性。
面对千万级记录的级联 delete 操作，单次长事务易引发连接超时、锁表、内存溢出及服务阻塞；最优实践是结合分批删除（limit + 循环）、合理配置数据库超时参数，并辅以异步非阻塞执行，兼顾数据一致性与系统稳定性。
在 PostgreSQL 中执行涉及 bills 表及其四个关联表（通过 bill_id 外键级联）的大规模删除（如 1600 万条记录），若采用单条 DELETE ... WHERE ... 语句，将面临多重风险：
- 连接超时中断：HikariCP 报错 SQLSTATE(08006), ErrorCode(0) 及 An I/O error occurred while sending to the backend 明确表明客户端连接因等待过久被主动关闭（默认 connection-timeout=30s），但 PostgreSQL 后端仍在后台执行——这会导致应用层误判失败，而实际数据已部分/全部删除，破坏操作可观测性与幂等性；
- 事务膨胀与 WAL 压力：单事务写入海量 WAL 日志，可能触发 checkpoint 频繁、wal_buffers 耗尽，甚至导致 transaction ID wraparound 风险；
- 锁粒度失控：DELETE 在行级加 FOR UPDATE 锁，长事务使锁持有时间剧增，阻塞其他 DML，影响业务可用性；
- 内存与回滚段压力：PostgreSQL 需维护 MVCC 快照及回滚信息，大事务显著增加 work_mem 与 temp_buffers 消耗。
✅ 推荐方案：分批 + 异步 + 参数协同优化
1. 分批删除（Batched DELETE）——核心安全策略
避免单事务，改用带 LIMIT 的循环删除，每次仅处理固定数量（建议 5,000–50,000 条，依单行体积与硬件调整）：
PostgreSQL 18.4 ubuntu
下载
PostgreSQL 18.4 官方 Ubuntu 安装包现已发布，这是目前最新的稳定版本。推荐通过官方 APT 仓库安装：先执行 sudo apt update 更新索引，再运行 sudo apt install postgresql-18 即可完成部署。新版本引入了异步 I/O 子系统，在顺序扫描与 VACUUM 场景下性能提升显著，同时支持 UUID v7 原生生成函数与虚拟生成列。
-- 示例：每次删除最多 10,000 条过期账单（含级联）
WITH batch AS (
SELECT id FROM bills
WHERE expired_date::date <blockquote><p>✅ 优势：每批次为独立短事务，WAL 写入可控、锁持有时间短、可监控进度、支持断点续删；<br>
⚠️ 注意：需确保 ORDER BY id（或主键）避免重复扫描；若 bills.id 无索引，务必先创建 CREATE INDEX CONCURRENTLY idx_bills_expired_item ON bills(expired_date, item_id);</p></blockquote><h3>2. 异步非阻塞执行——解耦业务线程</h3><p>使用 @Async 将删除任务移交独立线程池，防止 Web 请求线程长时间挂起：</p><pre class="brush:php;toolbar:false;">@Configuration
@EnableAsync
public class AsyncConfig {
@Bean(name = "deletionTaskExecutor")
public Executor taskExecutor() {
ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
executor.setCorePoolSize(2);
executor.setMaxPoolSize(4);
executor.setQueueCapacity(100);
executor.setThreadNamePrefix("deletion-");
executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
executor.initialize();
return executor;
}
}
@Service
public class BillCleanupService {
@Async("deletionTaskExecutor")
public CompletableFuture<integer> deleteExpiredBillsAsync(
Integer someDate, Set<integer> itemIdsCanNotBeDeleted) {
int totalDeleted = 0;
int batchSize = 10_000;
boolean hasMore;
do {
String sql = """
WITH batch AS (
SELECT id FROM bills
WHERE expired_date::date > result = namedParameterJdbcTemplate.queryForList(sql,
new MapSqlParameterSource()
.addValue("someDate", someDate)
.addValue("itemIdsCanNotBeDeleted", itemIdsCanNotBeDeleted)
.addValue("batchSize", batchSize)
);
hasMore = result.size() == batchSize;
totalDeleted += result.size();
// 可选：每批次后休眠 100ms 减轻系统负载
if (hasMore) {
try { Thread.sleep(100); } catch (InterruptedException e) { Thread.currentThread().interrupt(); }
}
} while (hasMore);
log.info("Async deletion completed: {} records removed", totalDeleted);
return CompletableFuture.completedFuture(totalDeleted);
}
}</integer></integer>? 关键点：
- 禁用默认 SimpleAsyncTaskExecutor（会无限创建线程），必须显式配置有界线程池；
- 使用 CompletableFuture 支持回调与状态追踪；
- RETURNING id 精确统计每批删除数，避免 update() 返回值在级联场景下失真。
3. 数据库层协同调优
-
延长客户端超时：在 application.yml 中增大 Hikari 连接超时（仅用于异步任务初始化连接）：
spring: datasource: hikari: connection-timeout: 60000 # 60秒 validation-timeout: 3000 -
调整 PostgreSQL 事务参数（postgresql.conf）：
# 允许长事务（谨慎！仅限维护窗口） idle_in_transaction_session_timeout = 0 # 禁用空闲事务超时 # 加速删除（牺牲部分持久性，生产环境需评估） synchronous_commit = local # 或 'off'（仅测试环境）
总结：三原则保障大规模删除稳健性
| 原则 | 实施方式 | 目标 |
|---|---|---|
| 分而治之 | LIMIT + CTE + 循环，每批 ≤5w 行 | 控制事务大小、降低锁争用、支持进度监控 |
| 异步解耦 | @Async + 自定义线程池 + CompletableFuture | 避免阻塞业务线程，提升服务响应性 |
| 参数协同 | 调整 connection-timeout、synchronous_commit、索引优化 | 平衡性能、一致性与运维可观测性 |
切记：永远在预发环境全量压测，监控 pg_stat_progress_delete 视图、pg_locks 及 pg_stat_activity，确保方案在真实负载下可持续运行。
