Python+SQLAlchemy删除PostgreSQL数据速度远慢于查询的问题

我使用Python和SQLAlchemy访问PostgreSQL数据库时遇到了严重的性能问题：查询速度极快，但删除耗时是查询的25万倍。

现有一张包含9200万行数据的RP表，需要删除其中约7万行数据。定位待删除数据的代码运行迅速——仅需约0.2秒即可完成500次循环（每次统计符合d_id条件的行数），代码如下：

import sqlalchemy as sa
...
with Session(engine, autobegin=True) as session:
...
    r_count = 0
    for image in images:
        sub_r = sa.select(RP).filter_by(d_id=image.id)
        count = session.execute(sa.select(sa.func.count()).select_from(sub_r)).scalar_one()
        r_count += count
    # 此处输出耗时和统计信息

但添加删除逻辑后，原单次循环耗时<0.01秒，现在每次循环需250秒，总删除耗时预计超36小时。我尝试了三种删除策略：

• session.query(RP).filter_by(d_id=image.id).delete()
• del_rp = sa.delete(RP).where(RP.d_id ==image.id); session.execute(del_rp)
• 查询所有符合条件的对象后逐行session.delete(r)

同时尝试过中途执行session.commit()，均无明显改善。

通过pgAdmin dashboard观察到，每次删除操作似乎返回数十亿条元组；查看服务器日志发现，最初执行的SQL为DELETE FROM rp WHERE rp.d_id = 5254591 RETURNING rp.id，修改为不返回结果的DELETE FROM rp WHERE rp.d_id = 5254591后，问题依旧。

目前无法及时中断运行中的代码，只能等待循环结束，急需解决删除速度过慢的方案。

解决方案 #

1. 批量删除而非循环单删 #

核心问题是循环执行单条删除语句，PostgreSQL每次删除都要处理事务日志、锁表、索引更新等操作，500次循环相当于触发500次独立的删除事务，开销极大。改为一次性批量删除所有目标行：

# 先收集所有要删除的d_id
target_d_ids = [image.id for image in images]

with Session(engine) as session:
    # 批量删除所有符合条件的行
    del_stmt = sa.delete(RP).where(RP.d_id.in_(target_d_ids))
    result = session.execute(del_stmt)
    print(f"删除了 {result.rowcount} 行")
    session.commit()

这种方式将多次删除合并为一次，大幅减少事务和索引更新的开销。

2. 临时关闭非必要索引（业务允许时） #

如果RP表除d_id索引外还有其他索引，删除时每个索引都要更新，这是耗时的主要原因之一。若业务可以接受索引临时缺失，可先删索引、完成删除后重建：

-- 先删除非必要索引（示例）
DROP INDEX idx_rp_some_column;
-- 执行批量删除
DELETE FROM rp WHERE d_id IN (...);
-- 重建索引
CREATE INDEX idx_rp_some_column ON rp(some_column);

3. 临时调优PostgreSQL配置参数 #

针对大批量删除，可临时调整以下参数降低开销：

• maintenance_work_mem: 调大至64MB或更高，让数据库在处理索引更新、垃圾回收时分配更多内存
• work_mem: 提升该值减少排序/哈希操作的磁盘IO
• autovacuum: 临时关闭自动清理（删除完成后再开启），避免抢占资源

修改参数可通过ALTER SYSTEM命令，执行后用SELECT pg_reload_conf();生效。

4. 分批次删除（避免事务过大） #

若批量删除行数过多导致事务日志膨胀，可分批次执行删除，比如每次删1000行：

target_d_ids = [image.id for image in images]
batch_size = 1000

with Session(engine) as session:
    for i in range(0, len(target_d_ids), batch_size):
        batch_ids = target_d_ids[i:i+batch_size]
        del_stmt = sa.delete(RP).where(RP.d_id.in_(batch_ids))
        result = session.execute(del_stmt)
        session.commit()
        print(f"已删除 {result.rowcount} 行")

5. 分区表直接TRUNCATE（适用场景） #

如果RP表是按d_id或关联字段分区的，可直接TRUNCATE对应分区，速度远快于DELETE：

TRUNCATE TABLE rp_partition_xxx;

前提是分区键与删除条件完全匹配。

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