大规模优化PostgreSQL用户连续日记记录天数计算方案咨询

问题背景 #

需要计算用户连续创建日记条目的天数，现有PostgreSQL查询可分组获取最新连续记录组，但需优化以支持大规模用户场景，同时评估新增streak字段的可行性，探索其他优化方法。

现有表结构 #

CREATE TABLE "diaries" (
"id" SERIAL NOT NULL,
"text" VARCHAR(4000),
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"user_profile_id" INTEGER NOT NULL,

CONSTRAINT "diaries_pkey" PRIMARY KEY ("id"));

现有查询语句 #

WITH daily_entries AS (
    SELECT
        user_profile_id,
        created_at::date AS entry_date
    FROM
        diaries
    WHERE
        user_profile_id = 1
        AND created_at::date <= CURRENT_DATE
    GROUP BY
        user_profile_id, created_at::date
),
streaks AS (
    SELECT
        user_profile_id,
        entry_date,
        entry_date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY user_profile_id ORDER BY entry_date) AS streak_group
    FROM
        daily_entries
),
streaks_summary AS (
    SELECT
        user_profile_id,
        MIN(entry_date) AS streak_start,
        MAX(entry_date) AS streak_end,
        COUNT(*) AS streak_length
    FROM
        streaks
    GROUP BY
        user_profile_id, streak_group
)
SELECT
    streak_length
FROM
    streaks_summary
WHERE
    streak_end = CURRENT_DATE
    AND (user_profile_id = 1);

优化方案 #

1. 即时查询优化 #

索引优化

给diaries表创建复合索引，直接覆盖查询过滤和排序需求，避免全表扫描：

CREATE INDEX idx_diaries_user_created ON diaries(user_profile_id, created_at);

该索引能快速定位指定用户的所有日记条目，同时按created_at排序，大幅提升daily_entries阶段的分组效率。

简化查询逻辑

无需生成所有历史连续记录组，直接倒序查找当前连续日期段，减少计算量：

WITH daily_entries AS (
    SELECT DISTINCT created_at::date AS entry_date
    FROM diaries
    WHERE user_profile_id = 1
      AND created_at::date <= CURRENT_DATE
    ORDER BY entry_date DESC
),
consecutive_check AS (
    SELECT 
        entry_date,
        CURRENT_DATE - entry_date AS day_diff,
        ROW_NUMBER() OVER (ORDER BY entry_date DESC) AS rn
    FROM daily_entries
)
SELECT COUNT(*) AS streak_length
FROM consecutive_check
WHERE day_diff = rn - 1;

逻辑说明：倒序取用户所有日记日期，对比日期与今天的差值和行号，连续日期的差值会等于行号-1，直到出现断档停止计算，无需处理全部历史数据。

2. 预计算字段（用户级连续天数存储） #

直接在日记表加streak字段并非最优，建议单独维护用户级连续状态表，可行性及维护方式如下：

方案设计

创建user_streaks表存储用户当前连续状态：

CREATE TABLE user_streaks (
    user_profile_id INTEGER PRIMARY KEY REFERENCES user_profiles(id),
    current_streak INTEGER NOT NULL DEFAULT 0,
    streak_start DATE,
    streak_end DATE,
    updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

维护逻辑

• 新增日记时：
  1. 检查用户streak_end是否为CURRENT_DATE - 1，若是则current_streak +=1，更新streak_end为CURRENT_DATE；
  2. 若streak_end不是前一天，且当天无日记记录，则重置current_streak=1，streak_start和streak_end设为CURRENT_DATE；
  3. 可通过触发器或业务代码实现，确保原子性。
• 删除/修改日记时：
  若操作影响到当前连续段的日期（比如删除了连续段中的某一天），需触发该用户的连续天数重新计算，可通过异步任务处理避免实时阻塞。

优势

查询时直接读取user_streaks表的current_streak，性能最优，适合高并发场景。

3. 其他优化策略 #

• 缓存策略：若用户每日最多写一篇日记，可将连续天数缓存至Redis，过期时间设为当日23:59:59，大部分查询直接读缓存，无需访问数据库。
• 批量计算：允许非实时更新的场景下，每日凌晨跑批处理，计算所有用户的当前连续天数并更新user_streaks表，彻底避免实时计算压力。

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