PostgreSQL表清理：保留90天内数据及旧数据最新记录

假设我们有一个PostgreSQL表，表结构如下：

CREATE TABLE your_table_name (
  "id" BIGINT NOT NULL PRIMARY KEY,
  "changedAt" TIMESTAMP NOT NULL,
  "familyId" VARCHAR(12) NOT NULL,
  -- 其他字段省略以简化
);

该表会接收大量更新记录，需要实现以下数据保留规则：

• 保留90天以内的所有数据；
• 对于任何familyId，无论其近90天是否有更新，都需要保留该familyId的最新一条旧记录（即早于90天的最新记录）。

示例数据（以逗号分隔字段）：

1, 2024-04-12 13:20:23, "Test 1", ...
2, 2024-03-12 13:20:23, "Test 1", ...
3, 2024-01-01 13:20:23, "Test 1", ...
4, 2022-01-12 13:20:23, "Test 1", ...

在这个例子中，记录3和4都早于90天，但我们需要保留记录3（Test 1的最新旧记录），仅删除记录4。

需要解决两个问题：

1. 如何编写符合规则的DELETE语句？
2. 哪些索引可以提升该清理操作的执行速度？

一、DELETE语句实现 #

方法1：使用CTE标记需保留的记录 #

通过公共表表达式先筛选出所有需要保留的记录，再删除不在此列表中的数据，逻辑直观易懂：

WITH keep_records AS (
  -- 保留90天以内的所有记录
  SELECT id
  FROM your_table_name
  WHERE changedAt >= NOW() - INTERVAL '90 days'
  
  UNION
  
  -- 每个familyId早于90天的最新一条记录
  SELECT id
  FROM (
    SELECT 
      id,
      ROW_NUMBER() OVER (PARTITION BY familyId ORDER BY changedAt DESC) AS rn
    FROM your_table_name
    WHERE changedAt < NOW() - INTERVAL '90 days'
  ) sub
  WHERE rn = 1
)
DELETE FROM your_table_name
WHERE id NOT IN (SELECT id FROM keep_records);

方法2：使用NOT EXISTS直接过滤待删除记录 #

如果表中id是唯一主键，可直接筛选出需要删除的记录，避免UNION的开销，数据量大时效率更高：

DELETE FROM your_table_name t
WHERE 
  -- 仅处理早于90天的记录
  t.changedAt < NOW() - INTERVAL '90 days'
  -- 并且不是该familyId早于90天的最新记录
  AND EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE 
      t2.familyId = t.familyId
      AND t2.changedAt < NOW() - INTERVAL '90 days'
      AND t2.changedAt > t.changedAt
  );

注意事项：

• 替换your_table_name为实际表名；
• 若表数据量极大，建议分批执行DELETE（比如每次删除1000条），避免长时间锁表影响业务。

二、提升速度的索引方案 #

为了加速清理操作，建议创建以下索引：

• 复合索引(familyId, changedAt DESC)：
  该索引能快速定位每个familyId的最新旧记录，无论是CTE子查询还是NOT EXISTS关联，都能大幅降低排序和过滤的开销。

• 单字段索引(changedAt)：
  用于快速筛选90天以内的记录，减少需要扫描的数据总量。

创建索引的SQL语句：

-- 创建复合索引
CREATE INDEX idx_familyid_changedat_desc ON your_table_name ("familyId", "changedAt" DESC);

-- 创建单字段索引
CREATE INDEX idx_changedat ON your_table_name ("changedAt");

权衡说明：
索引会增加写入（INSERT/UPDATE）的开销，因为每次写入都需要维护索引。由于该表接收大量更新，若写入性能压力极大，可仅保留复合索引(familyId, changedAt DESC)——它也能覆盖changedAt的过滤需求，只是效率略低于单字段索引。

内容的提问来源于stack exchange，提问作者Krisztián Kocsis