PostgreSQL中仅追加式在线状态事件表的时效性索引方案

针对你的场景——只插入不删除的聊天室在线事件表，仅需查询最近1小时的分组统计——完全可以实现仅维护有效数据的索引，避免索引膨胀。下面是两种实用方案：

方案一：分区表+按需创建索引（推荐） #

这是最适合长期数十亿数据量的方案，既能自动隔离过期数据，又能让索引只服务于最近1小时的数据。

1. 改造为按小时范围分区的主表 #

把原表改成以created_at为分区键的范围分区表，每小时的数据会自动存入对应分区：

CREATE TABLE presence_event (
    id bigint,
    user_id bigint REFERENCES "user"(id),
    channel_id bigint REFERENCES "channel"(id),
    created_at timestamptz,
    event char CHECK (event IN ('join', 'leave'))
) PARTITION BY RANGE (created_at);

2. 动态创建/销毁分区 #

• 提前用脚本自动创建未来几小时的分区（比如当前小时和下一小时），示例：

-- 创建当前小时的分区（需替换时间为动态生成值）
CREATE TABLE presence_event_2024052010 PARTITION OF presence_event
FOR VALUES FROM ('2024-05-20 10:00:00+00') TO ('2024-05-20 11:00:00+00');

• 用pg_cron定时删除超过1小时的分区（需先安装pg_cron扩展），每小时执行一次清理：

SELECT cron.schedule('clean-old-presence-partitions', '0 * * * *', $$
    DO $$
    DECLARE
        partition_name text;
    BEGIN
        FOR partition_name IN (
            SELECT tablename
            FROM pg_tables
            WHERE tablename LIKE 'presence_event_%'
            AND to_timestamp(substring(tablename from 'presence_event_(.*)'), 'YYYYMMDDHH') < now() - interval '1 hour'
        ) LOOP
            EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(partition_name);
        END LOOP;
    END $$;
$$);

3. 仅在活跃分区创建索引 #

只给当前正在写入、且在查询范围内的分区创建复合索引，完全匹配你的分组统计需求：

CREATE INDEX idx_presence_active ON presence_event_2024052010 (user_id, channel_id, event) INCLUDE (created_at);

过期分区被删除后，对应的索引也会自动清理，不会占用额外空间。

方案二：部分索引（适合临时过渡） #

如果不想立刻改造分区表，可以创建部分索引，仅包含最近1小时的数据：

CREATE INDEX idx_presence_recent ON presence_event (user_id, channel_id, event)
WHERE created_at >= now() - interval '1 hour';

但要注意：PostgreSQL不会自动更新部分索引的过滤条件，随着时间推移，超出1小时的旧数据会留在索引里。你需要定期重建索引来清理无效数据，比如每天执行一次：

REINDEX INDEX idx_presence_recent;

这种方案的维护成本更高，重建索引会消耗数据库资源，适合数据量增长较慢的场景。

查询示例（适配两种方案） #

用下面的查询可以高效利用上面创建的索引：

SELECT 
    user_id, 
    channel_id,
    COUNT(*) FILTER (WHERE event = 'join') AS join_count,
    COUNT(*) FILTER (WHERE event = 'leave') AS leave_count
FROM presence_event
WHERE created_at >= now() - interval '1 hour'
GROUP BY user_id, channel_id;

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