PostgreSQL大表删除99.9%数据的性能影响及最优方案咨询

问题1：批量删除99.9%行后是否影响后续运行性能 #

如果直接用DELETE命令执行批量删除且不做后续维护，会显著影响后续运行性能。PostgreSQL的DELETE操作不会物理移除已删除的行（称为死元组），仅会标记其为不可见。你的场景下删除后99.9%的存储页都是无效死元组，后续全表扫描、范围查询都需要遍历大量无效页面，IO开销会暴涨。
如果删除后执行了VACUUM FULL，或采用下文的清理方案，就不会遗留性能问题。

问题2：一次性清理场景的最优方案选择 #

你当前满足可禁写、无效行占比极高、无额外索引的前提，最优方案是「备份有效行+TRUNCATE原表+回写有效行」，比建表重命名、直接DELETE+VACUUM的方案更适配，原因如下：

• 执行速度最快：330万行中有效行仅3300行左右，导出有效数据的开销可以忽略，TRUNCATE是DDL操作，直接释放整张表的磁盘空间，几乎瞬间完成，回写有效行的开销也极低。
• 无死元组残留：操作完成后表的物理存储完全干净，不需要后续做VACUUM维护，后续读写性能无任何影响。
• 适配成本低：不需要调整原表的关联依赖，外键、视图、用户权限、触发器等绑定在原表上的配置完全不需要修改，比创建新表再重命名的方案出问题的概率更低。
  具体操作步骤如下：

1. 锁表禁写后执行：CREATE TABLE 临时备份表 AS SELECT * FROM 原表 WHERE 你的保留条件;
2. 执行清空：TRUNCATE TABLE 原表;
3. 回写有效数据：INSERT INTO 原表 SELECT * FROM 临时备份表;
4. 确认数据无误后删除临时备份表即可。

问题3：VACUUM机制、表完整性与空间复用相关疑问 #

批量删除对表完整性和数据结构的影响 #

只要DELETE语句正常执行没有报错或强制中断，完全不会影响表完整性与数据结构，主键、约束、字段定义、未删除的有效数据都不会出现损坏，PostgreSQL的MVCC机制保证了删除操作的原子性，要么全部生效要么全部回滚。

删除后的空间复用规则 #

• 仅执行DELETE不做其他操作：死元组占用的空间会被标记为可复用，后续该表的新写入会优先占用这些空闲空间，但不会返还给操作系统，操作系统层面看到的数据库磁盘占用不会变小。
• 执行普通VACUUM：会把分散的死元组空间整理成连续的空闲块，提升空间复用效率，但依然不会把空闲空间返还给操作系统。
• 执行VACUUM FULL：会把所有有效行整理到连续的物理页面，剩余空闲空间会全部返还给操作系统，但该操作会全程锁表，且需要和表当前占用空间等量的临时磁盘空间，对你的场景来说完全没必要，用TRUNCATE方案效率更高。

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