PostgreSQL/RDBMS中列级TTL实现及NO_AWARD自动移除方案探讨

除了定时任务全表扫描更新，以下几种方案更高效且贴合PostgreSQL特性：

1. 用生成列（Generated Column）实时计算状态 #

不需要手动维护award_category列，直接通过入职日期动态生成状态值，自动同步入职满一年的变化：

-- 先删除原有的物理列（如果存在）
ALTER TABLE employees DROP COLUMN IF EXISTS award_category;

-- 添加存储型生成列，实时计算奖励类别
ALTER TABLE employees ADD COLUMN award_category TEXT GENERATED ALWAYS AS (
    CASE
        WHEN CURRENT_DATE - hire_date >= 365 THEN NULL
        ELSE 'NO_AWARD'
    END
) STORED;

优点：无需任何更新操作，值自动随时间变化；查询性能接近物理列。
注意：生成列是存储型（STORED），会占用存储空间，但PostgreSQL会自动维护其值，无需人工干预。

2. 用视图（View）动态展示状态 #

如果不需要将award_category作为物理列存储，直接创建视图动态计算状态，完全省去维护成本：

CREATE OR REPLACE VIEW employee_award_status AS
SELECT
    *,
    CASE
        WHEN CURRENT_DATE - hire_date >= 365 THEN NULL
        ELSE 'NO_AWARD'
    END AS award_category
FROM employees;

优点：零存储开销，状态永远准确；适合不需要对award_category建索引的场景。
缺点：每次查询都要计算，数据量极大时可能影响查询性能。

3. 基于pg_cron创建精准定时单条更新 #

借助PostgreSQL的pg_cron扩展，在员工入职时创建一次性定时任务，精准在满一年当天更新该员工的award_category：

-- 先安装pg_cron扩展（需超级用户权限）
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 创建触发器函数，入职时生成定时更新任务
CREATE OR REPLACE FUNCTION create_award_update_task()
RETURNS TRIGGER AS $$
BEGIN
    -- 为新入职员工创建满一年时的更新任务
    PERFORM cron.schedule(
        'update-award-' || NEW.employee_id,
        TO_CHAR(NEW.hire_date + INTERVAL '1 year', 'YYYY-MM-DD HH24:MI:SS'),
        format('UPDATE employees SET award_category = NULL WHERE employee_id = %L;', NEW.employee_id)
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到员工表的INSERT事件
CREATE TRIGGER trigger_after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW EXECUTE FUNCTION create_award_update_task();

-- 可选：如果入职日期可能修改，添加UPDATE触发器来更新定时任务
CREATE OR REPLACE FUNCTION update_award_update_task()
RETURNS TRIGGER AS $$
BEGIN
    -- 删除旧的定时任务
    PERFORM cron.unschedule('update-award-' || OLD.employee_id);
    -- 创建新的定时任务
    PERFORM cron.schedule(
        'update-award-' || NEW.employee_id,
        TO_CHAR(NEW.hire_date + INTERVAL '1 year', 'YYYY-MM-DD HH24:MI:SS'),
        format('UPDATE employees SET award_category = NULL WHERE employee_id = %L;', NEW.employee_id)
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_after_employee_hire_update
AFTER UPDATE OF hire_date ON employees
FOR EACH ROW EXECUTE FUNCTION update_award_update_task();

优点：精准触发更新，避免全表扫描的性能消耗；适合需要保留物理列的场景。
注意：需要安装pg_cron扩展，且要确保定时任务的清理（比如员工离职时删除对应任务）。

4. 用触发器结合即时检查（适合高频查询场景） #

如果应用查询award_category的频率很高，可以在查询前自动触发更新，确保返回最新状态：

CREATE OR REPLACE FUNCTION check_and_update_award_status()
RETURNS TRIGGER AS $$
BEGIN
    IF CURRENT_DATE - NEW.hire_date >= 365 AND NEW.award_category = 'NO_AWARD' THEN
        NEW.award_category := NULL;
        -- 同步更新物理表
        UPDATE employees SET award_category = NULL WHERE employee_id = NEW.employee_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_before_employee_select
BEFORE SELECT ON employees
FOR EACH ROW EXECUTE FUNCTION check_and_update_award_status();

优点：确保每次查询返回的都是最新状态；无需定时任务。
缺点：每次查询都会执行检查逻辑，高并发场景下可能增加数据库负载。

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