求助：PostgreSQL中快速随机删除大量行的高效方法

针对你3000万行大表随机删除的性能问题，以下是几个比原方法高效得多的实现方式：

方案一：小事务分批删除 #

一次性删除大量行会导致事务日志暴涨、索引频繁更新，既拖慢速度又容易超时。改成每次删一小批，循环执行：

-- 每次删除1万行，重复执行直到达到目标行数
WITH sampled_rows AS (
    SELECT ctid FROM interest_rate_set TABLESAMPLE BERNOULLI(1) LIMIT 10000
)
DELETE FROM interest_rate_set WHERE ctid IN (SELECT ctid FROM sampled_rows);

• 可调整BERNOULLI(1)的采样比例和LIMIT 10000的单次删除行数，控制事务量级，避免超时
• 可以用bash或Python写简单脚本循环执行，直到返回的影响行数为0
• 优势：无需中断业务，执行过程可随时暂停，不会产生超大事务日志

方案二：创建新表保留目标行（最快方案） #

PostgreSQL的删除操作本质是标记行删除，还要维护索引，效率极低。直接把需要保留的行导出到新表再替换原表，是大表数据清理的最优解：

-- 1. 创建新表，保留50%的随机行（与原表结构完全一致）
CREATE TABLE interest_rate_set_new AS
SELECT * FROM interest_rate_set TABLESAMPLE BERNOULLI(50);

-- 2. 同步原表的索引、约束、触发器、权限
-- 示例：如果原表有主键索引
CREATE UNIQUE INDEX idx_interest_rate_set_id ON interest_rate_set_new(id);
-- 可通过\d interest_rate_set查看原表结构，同步所有索引、外键、触发器等

-- 3. 原子切换表，几乎无业务中断
BEGIN;
ALTER TABLE interest_rate_set RENAME TO interest_rate_set_old;
ALTER TABLE interest_rate_set_new RENAME TO interest_rate_set;
COMMIT;

-- 4. 确认数据无误后，删除旧表
DROP TABLE interest_rate_set_old;

• 优势：速度比直接删除快10倍以上，彻底避免原表的索引维护开销
• 注意：如果表有实时写入，需在切换前暂停写入，或用SET TRANSACTION ISOLATION LEVEL REPEATABLE READ保证数据一致性
• 时序库后续优化建议：改用按时间分区表，以后删除旧数据直接DROP分区，效率远高于逐行删除

额外优化提示 #

• 操作前务必备份数据，避免误操作
• 执行方案一时，可临时调高work_mem（如SET work_mem = '64MB';），提升CTID数组的处理效率
• 操作完成后执行VACUUM ANALYZE interest_rate_set;，整理表统计信息

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