PostgreSQL中如何并行化SELECT与DELETE以提升批量删除性能？

1. 使用FOR UPDATE SKIP LOCKED实现并行无冲突取数删除 #

这是解决同一product并行处理的最优方案，PostgreSQL 9.5及以上版本支持。它允许后续查询直接跳过已被其他事务锁定的行，多个客户端可同时获取不同的批次，无需等待锁释放。

修改后的查询语句：

DELETE FROM codes c
USING (
    SELECT id 
    FROM codes 
    WHERE product = 'abc'
    -- 可选：若需按顺序处理，添加ORDER BY id
    ORDER BY id
    LIMIT 100
    FOR UPDATE SKIP LOCKED
) AS selected
WHERE c.id = selected.id
RETURNING c.id;

优势：

• 完全并行处理同一product的请求，不会出现响应时间随客户端数量线性增长的问题
• 自动避免重复选取同一ID，无需额外协调逻辑
• 锁持有时间短，仅锁定当前批次的行，减少冲突概率

2. 按ID范围分片处理 #

如果ID是连续递增的，可以将同一product的数据按ID区间划分批次，让不同客户端处理不同区间，从根源上避免锁冲突。

示例查询：

DELETE FROM codes
WHERE product = 'abc' 
  AND id BETWEEN :start_id AND :end_id
RETURNING id;

实现要点：

• 需要维护一个记录已处理区间的表，或由调度器分配区间给客户端，确保区间不重叠
• 适合后台批量清理任务，而非随机并发请求场景
• 需提前确保(product, id)复合索引存在，快速定位区间数据

3. 结合分区表优化大表性能 #

若codes表数据量极大（千万级以上），且绝大多数操作按product维度进行，可将表按product分区。分区表的锁粒度更细，且查询/删除操作仅针对目标分区，大幅减少扫描和锁的范围。

步骤：

1. 创建按product分区的表（列表分区更适合明确的product值，哈希分区适合product值较多的场景）
2. 为每个分区创建(id)索引或(product, id)复合索引
3. 结合FOR UPDATE SKIP LOCKED进行分批删除，性能会进一步提升

通用优化：添加必要索引 #

无论采用哪种方案，都必须为product字段创建复合索引，确保查询能快速定位目标数据，缩短锁持有时间：

CREATE INDEX idx_codes_product_id ON codes (product, id);

注意事项：

• 使用SKIP LOCKED时，若没有符合条件的行，查询会返回空结果，客户端需处理该场景
• 若业务要求严格的处理顺序，务必在子查询中添加ORDER BY id，确保批次按顺序获取

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