如何在CockroachDB中定期删除未验证且TTL过期的数据？

CockroachDB原生行级TTL确实只支持单一时间字段触发清理，但你可以通过以下两种方案实现带verified=false条件的自动过期删除：

方案1：内置调度（SCHEDULE）+ 批量删除逻辑 #

这是最灵活可控的方案，直接用CockroachDB的内置调度功能定时执行批量删除，避免依赖外部定时任务工具。

1.1 基础批量删除SQL #

如果数据量不大，直接每天执行单条删除语句（加LIMIT避免锁表）：

DELETE FROM newsletters
WHERE verified = false AND ttl_stamp < NOW()::TIMESTAMPZ
LIMIT 1000;

1.2 创建每日调度 #

把上面的SQL做成每日执行的调度：

CREATE SCHEDULE delete_unverified_expired_rows
EVERY '1 day'
START '2024-01-01 00:00:00+00' -- 替换成你想开始执行的时间
AS DELETE FROM newsletters
WHERE verified = false AND ttl_stamp < NOW()::TIMESTAMPZ
LIMIT 1000;

1.3 大数量场景：循环批量删除存储过程 #

如果待删除数据量很大，单条LIMIT语句可能无法一次清理完成，可以写个存储过程循环执行，直到没有符合条件的行：

CREATE OR REPLACE PROCEDURE clean_unverified_expired_data()
LANGUAGE plpgsql
AS $$
DECLARE
  deleted_count INT;
BEGIN
  LOOP
    -- 每次删除1000行，可根据性能调整
    DELETE FROM newsletters
    WHERE verified = false AND ttl_stamp < NOW()::TIMESTAMPZ
    LIMIT 1000;
    
    -- 获取本次删除的行数
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    
    -- 没有行被删除时退出循环
    IF deleted_count = 0 THEN
      EXIT;
    END IF;
    
    -- 每批提交，避免长事务占用资源
    COMMIT;
  END LOOP;
END;
$$;

然后创建调度调用这个存储过程：

CREATE SCHEDULE clean_unverified_expired_schedule
EVERY '1 day'
START '2024-01-01 00:00:00+00'
AS CALL clean_unverified_expired_data();

可以用SHOW SCHEDULES;查看调度状态，DROP SCHEDULE <schedule_id>;删除不需要的调度。

方案2：行级TTL+计算字段（间接实现） #

如果你更倾向于用原生TTL机制，可以通过生成计算字段来间接实现多条件触发：

2.1 添加条件TTL计算字段 #

创建一个存储字段，只有当verified=false时，它的值等于ttl_stamp；否则设为极远的未来（确保不会被TTL清理）：

ALTER TABLE newsletters
ADD COLUMN conditional_ttl TIMESTAMPZ GENERATED ALWAYS AS (
  CASE WHEN verified = false THEN ttl_stamp ELSE '9999-12-31 23:59:59+00' END
) STORED;

2.2 给计算字段设置行级TTL #

把原生TTL绑定到这个计算字段上：

ALTER TABLE newsletters
ADD CONSTRAINT conditional_ttl_expiration
FOR ROW EXPIRE AT SYSTEM TIME conditional_ttl;

这样，只有verified=false且ttl_stamp过期的行才会被TTL自动清理；verified=true的行因为conditional_ttl是极远未来，永远不会被删除。

方案2优缺点 #

• 优点：利用原生TTL机制，无需手动维护调度
• 缺点：需要额外存储计算字段，且TTL的清理频率由CockroachDB默认控制（默认每1小时，可通过ttl_job_interval集群设置调整，但灵活性不如自定义调度）

注意事项 #

• 批量删除时务必加LIMIT，避免一次性删除大量数据导致的锁冲突和性能波动
• 存储过程中的COMMIT很重要，防止长事务占用过多资源
• 测试时可以手动执行删除语句或调用存储过程，验证条件是否生效

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