PostgreSQL+EF Core批量删插150万行数据性能逐次下降问题

核心问题分析 #

你遇到的性能衰减根源是表膨胀：

• 使用ExecuteDeleteAsync()时，PostgreSQL会逐行标记数据为"死元组"而非直接物理删除，这些死元组会占用磁盘空间。
• 普通VACUUM（包括autovacuum执行的）仅标记死元组为可复用，但不会将空间归还操作系统，导致表的物理体积持续增大。后续BulkInsert需要写入更多新磁盘块，而非复用旧块，因此速度越来越慢。
• VACUUM FULL会重写整个表回收空间，但会锁表，不符合无停机需求；TRUNCATE虽然快，但同样会锁表导致业务中断。

最优解决方案：原子交换表（Table Swap） #

通过创建备用表批量写入，再原子交换原表与备用表，完全避免锁表和表膨胀问题，步骤如下：

方案1：预创建备用表（推荐，复用表结构） #

提前创建与原表结构完全一致的备用表（含索引、约束）：

CREATE TABLE product_ranks_staging (LIKE product_ranks INCLUDING ALL);

然后在EF Core中执行以下逻辑：

// 1. 清空备用表（无业务访问，用TRUNCATE快速清空）
await _ctx.Database.ExecuteSqlRawAsync("TRUNCATE TABLE product_ranks_staging;");

// 2. 批量插入计算后的数据到备用表
await _ctx.BulkInsertAsync(allRanks, config => 
    config.DestinationTableName = "product_ranks_staging");

// 3. 原子交换两张表，原表全程可用
using (var transaction = _ctx.Database.BeginTransaction())
{
    await _ctx.Database.ExecuteSqlRawAsync(@"
        ALTER TABLE product_ranks RENAME TO product_ranks_old;
        ALTER TABLE product_ranks_staging RENAME TO product_ranks;
    ");
    transaction.Commit();
}

// 4. 异步清理旧表（不影响主业务）
await _ctx.Database.ExecuteSqlRawAsync("DROP TABLE product_ranks_old;");

方案2：动态创建临时表 #

如果无法预创建表，可动态生成临时表完成交换：

// 1. 复制原表结构创建临时表（含索引、约束）
await _ctx.Database.ExecuteSqlRawAsync(@"
    CREATE TEMPORARY TABLE product_ranks_temp (LIKE product_ranks INCLUDING ALL);
");

// 2. 批量插入数据到临时表
await _ctx.BulkInsertAsync(allRanks, config => 
    config.DestinationTableName = "product_ranks_temp");

// 3. 原子交换表
using (var transaction = _ctx.Database.BeginTransaction())
{
    await _ctx.Database.ExecuteSqlRawAsync(@"
        ALTER TABLE product_ranks RENAME TO product_ranks_old;
        ALTER TABLE product_ranks_temp RENAME TO product_ranks;
    ");
    transaction.Commit();
}

// 4. 清理旧表
await _ctx.Database.ExecuteSqlRawAsync("DROP TABLE product_ranks_old;");

方案优势 #

• 无锁无停机：表交换是原子操作，耗时微秒级，原表在整个过程中可正常读写。
• 性能稳定：每次插入都面向全新（或清空）的表，无死元组和表膨胀问题，速度始终与首次执行一致。
• 无需VACUUM：从根源避免了死元组产生，无需依赖autovacuum或手动清理操作。

补充优化（若必须保留删插逻辑） #

如果因限制无法使用交换表，可针对该表调整autovacuum参数，强制其更频繁地回收空间：

ALTER TABLE product_ranks SET (
    autovacuum_vacuum_threshold = 5000,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.01
);

该配置会让autovacuum在死元组数量达到5000+表大小1%时立即执行，但仍无法解决空间归还问题，性能表现仍不如交换表方案。

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