数据清理（TTL）-阿里云帮助中心

当业务数据（如图数据中的点和边）持续累积时，过期的历史数据不仅会增加存储成本，还可能降低热点数据的查询性能。通过为数据表设置TTL（Time-To-Live），您可以建立一套自动化的数据清理机制，以实现数据生命周期管理、控制成本并保障系统性能。本文介绍的TTL功能并非数据库原生属性，而是基于pg_cron插件实现的一种自定义定时删除任务。它会周期性地调用polar_ttl_delete_expired_records函数，分批次地物理删除指定时间点之前的数据。

工作原理

数据TTL功能依赖pg_cron插件提供的定时调度能力。其核心工作流程如下：

创建策略：您通过调用polar_set_ttl函数，定义一个TTL策略。该函数本质上是在pg_cron中注册了一个新的定时任务。
定时触发：pg_cron的调度器根据您设置的schedule（Cron 表达式），在指定时间自动触发该任务。
执行删除：任务触发后，会调用polar_age插件提供的polar_ttl_delete_expired_records函数。
分批清理：该函数根据您设置的过期时间（expire_time），查询并循环删除表中的过期数据，直到所有符合条件的旧数据被清理完毕。

此方案依赖于数据表中一个明确的时间戳字段（例如polar_age插件自动创建的timeline字段）来判断数据是否过期。

配置并管理数据TTL策略

以下步骤将引导您完成从环境准备到 TTL 策略配置与管理的完整流程。

安装定时任务插件

在postgres库中安装pg_cron插件：

\c postgres
CREATE EXTENSION pg_cron;

步骤一：创建TTL管理函数

为了简化TTL策略的创建、查看和删除操作，您需要在数据库中创建一组辅助函数。在postgres数据库中，依次执行以下三段SQL，以创建polar_set_ttl、polar_show_ttl和polar_drop_ttl函数。

polar_set_ttl（创建或更新TTL策略）

-- 创建或替换 polar_set_ttl 函数
CREATE OR REPLACE FUNCTION public.polar_set_ttl(
    dbname TEXT,                -- 业务数据库名称
    schemaname TEXT,            -- Schema 名称
    tablename TEXT,             -- 表名称
    ttl_col TEXT,               -- 用于判断过期的时间列名
    schedule TEXT,              -- Cron 格式的调度策略
    expire_time INTERVAL        -- 数据保留时长，早于此间隔的数据将被删除
) RETURNS TEXT AS $$
DECLARE
    task_name TEXT;
    sql_query TEXT;
BEGIN
    -- 生成一个唯一的任务名称，以便于识别和管理
    task_name := 'ttl_task_' || dbname || '_' || schemaname || '_' || tablename;

    -- 构造用于创建 cron 任务的 SQL 命令
    -- format 函数用于安全地将参数插入到字符串中，防止 SQL 注入
    sql_query := format(
        'SELECT cron.schedule_in_database(%L, %L, %L, %L);',
        task_name,
        schedule,
        -- 要执行的核心命令是调用删除函数，并传入所有必要参数
        format('CALL polar_ttl_delete_expired_records(%L, %L, %L, %L, 1000000)', schemaname, tablename, ttl_col, expire_time),
        dbname
    );

    -- 执行构造好的 SQL 命令
    EXECUTE sql_query;

    -- 返回任务名称，方便用户确认
    RETURN task_name;
END;
$$ LANGUAGE plpgsql;

polar_show_ttl（查看已配置的TTL策略）

-- 创建或替换 polar_show_ttl 函数
CREATE OR REPLACE FUNCTION public.polar_show_ttl(db_name TEXT)
RETURNS TABLE (
    jobid BIGINT,
    schedule TEXT,
    database TEXT,
    username TEXT,
    active BOOLEAN,
    jobname TEXT,
    expiretime TEXT
) AS $$
BEGIN
    -- 从 cron.job 表中查询所有与 TTL 相关的任务
    -- 注意：此查询依赖于 'ttl_task_' 的命名约定，如果任务名被手动修改，可能无法查到
    RETURN QUERY
    SELECT
        j.jobid,
        j.schedule,
        j.database,
        j.username,
        j.active,
        j.jobname,
        -- 通过正则表达式从 command 字段中解析出 expire_time 参数
        -- 这种方式较为脆弱，强依赖于 command 字符串的固定格式
        split_part(
            regexp_replace(j.command, '^CALL polar_ttl_delete_expired_records\((.*)\)$', '\1'),
            ',',
            4
        ) AS expiretime
    FROM
        cron.job AS j
    WHERE
        j.database = db_name
        AND j.jobname LIKE 'ttl_task_%';
END;
$$ LANGUAGE plpgsql;

`polar_drop_ttl`（删除指定的TTL策略）

-- 创建或替换 polar_drop_ttl 函数
CREATE OR REPLACE FUNCTION public.polar_drop_ttl(dbname TEXT, schemaname TEXT, tablename TEXT)
RETURNS TABLE (
    unscheduled BOOLEAN
) AS $$
DECLARE
    task_name TEXT;
    sql_query TEXT;
BEGIN
    -- 根据数据库、Schema 和表名构造唯一的任务名称
    task_name := 'ttl_task_' || dbname || '_' || schemaname || '_' || tablename;
    
    -- 构造用于取消 cron 任务的 SQL 命令
    sql_query := format(
        'SELECT cron.unschedule(%L);',
        task_name
    );

    -- 执行 SQL 并返回结果
    RETURN QUERY EXECUTE sql_query;
END;
$$ LANGUAGE plpgsql;

步骤二：设置、查看与删除TTL策略

完成函数创建后，您可以通过调用这些函数来管理TTL策略。

设置TTL策略：调用polar_set_ttl函数，为指定的数据表创建一个自动删除任务。

-- 示例：为 testdb 数据库中 graph.rel1 表设置 TTL 策略
-- 策略内容：基于 timeline 列，保留最近 7 天的数据
-- 调度计划：在每小时的第 30 分钟启动删除任务
SELECT polar_set_ttl('testdb', 'my_graph', 'Person', 'timeline', '30 * * * *', '7 days');

关键参数说明：

schedule：任务的调度策略，使用标准的Cron表达式。

需求场景	Cron表达式	说明
每5分钟执行一次	`/5 * * *`	适用于需要高频清理的场景。
每小时的第30分钟执行	`30 * * * *`
每天凌晨2:15执行	`15 2 * * *`	推荐用于业务低峰期，以减小影响。
每周日凌晨4点执行	`0 4 * * 0`	适合周度维护任务。

expire_time：数据保留的时间窗口，是一个INTERVAL类型的值。例如'7 days'表示删除创建时间在7天之前的所有数据；'1 month'表示保留近一个月的数据。

查看TTL策略：调用polar_show_ttl函数，检查指定数据库中已配置的TTL任务及其过期时间。

-- 查看 testdb 数据库中所有已设置的 TTL 策略
SELECT * FROM polar_show_ttl('testdb');

返回结果：

 jobid |  schedule  | database | username | active |             jobname             | expiretime 
-------+------------+----------+----------+--------+---------------------------------+------------
     1 | 30 * * * * | testdb   |    xxx   | t      | ttl_task_testdb_my_graph_Person |  '7 days'

删除TTL策略：调用polar_drop_ttl函数，停止并移除某个表的自动删除任务。

-- 示例：删除 testdb 数据库中 graph.rel1 表的 TTL 策略
SELECT polar_drop_ttl('testdb', 'my_graph', 'Person');

应用于生产环境

在生产环境中使用TTL功能时，不当的配置可能会影响数据库的性能和稳定性。请遵循以下最佳实践。

最佳实践

为时间列创建索引
这是最重要的性能优化措施。务必为TTL判断所依据的时间列（如timeline）创建一个索引。缺少索引将导致删除操作进行全表扫描，可能引发长时间的锁等待，甚至拖垮整个数据库。
错峰执行任务
如果您需要为多张表设置TTL策略，请将它们的schedule启动时间错开（例如，一个在0 2 * * *，另一个在15 2 * * *）。这可以避免在同一时刻触发多个高I/O的删除任务，防止系统资源竞争。
监控与处理表膨胀
大量的DELETE操作会使表和索引产生碎片，导致表膨胀，即物理文件大小远大于实际数据大小。建议定期运行VACUUM命令来回收空间。在必要时，可考虑在维护窗口执行VACUUM FULL（注意：此操作会锁表）。

风险防范

充分测试：在生产环境应用任何TTL策略之前，务必在预发或测试环境中进行充分验证，确保其行为符合预期。
数据备份：TTL执行的是物理删除，数据一旦删除将无法恢复。在为关键业务表设置TTL之前，请确保您已有完善的数据备份和恢复策略。