如何使用Postgres聚合连续相邻社交活动生成Feed信息流？

核心规则：仅聚合Feed流中位置连续、同一条帖子跨多群组的投递记录，被其他活动隔开的同帖记录不合并，天然适配千级场景下帖子乱序投递的情况

实现逻辑 #

采用经典的孤岛与间隙（Gaps and Islands）窗口函数解法，不需要递归、不需要额外中间表，单次查询即可完成聚合，千级活跃场景下单次查询耗时稳定在10ms级。

前置基础表结构（对齐常规社交业务设计） #

• users：用户表，核心字段user_id
• groups：群组表，核心字段group_id
• posts：帖子表，核心字段post_id、author_id、content、publish_time
• group_members：群组成员关系表，字段user_id、group_id、join_time
• group_post_deliveries：帖子群组投递事件表，字段delivery_id、post_id、group_id、deliver_time（Feed流原始事件源，支持乱序写入）

可直接落地的SQL实现 #

查询逻辑分三步：先拉取用户可见的投递事件并按Feed流规则排序，再给连续同帖的记录块打统一标记，最后按块聚合输出结果。

-- 示例：查询user_id为传入参数的用户第一页Feed，返回20条聚合结果
WITH user_visible_events AS (
    SELECT
        d.deliver_time,
        d.delivery_id,
        d.post_id,
        d.group_id,
        p.content,
        p.author_id,
        -- 对比上一条记录的post_id，不一致则标记为新分组起点
        CASE WHEN LAG(d.post_id) OVER (
            ORDER BY d.deliver_time DESC, d.delivery_id DESC
        ) = d.post_id THEN 0 ELSE 1 END AS is_new_block
    FROM group_post_deliveries d
    -- 关联过滤当前用户加入的群组，只返回可见内容
    INNER JOIN group_members gm 
        ON d.group_id = gm.group_id AND gm.user_id = $1
    INNER JOIN posts p 
        ON d.post_id = p.post_id
    -- 仅拉取近30天的事件，避免全表扫描，200条原始事件足够覆盖20条聚合结果
    WHERE d.deliver_time > NOW() - INTERVAL '30 days'
    ORDER BY d.deliver_time DESC, d.delivery_id DESC
    LIMIT 200
),
marked_blocks AS (
    SELECT
        *,
        -- 累加分组标记，给每个连续同帖块生成唯一ID
        SUM(is_new_block) OVER (
            ORDER BY deliver_time DESC, delivery_id DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS block_id
    FROM user_visible_events
)
-- 按块聚合输出最终Feed条目
SELECT
    MIN(deliver_time) AS display_time,
    post_id,
    author_id,
    content,
    COUNT(DISTINCT group_id) AS published_group_count, -- 用于展示「已发布至X个群组」
    ARRAY_AGG(group_id) AS related_group_ids
FROM marked_blocks
GROUP BY block_id, post_id, author_id, content
LIMIT 20;

性能优化配置 #

• 必须加对应索引避免排序和回表开销：
  • group_members 建联合索引 (user_id, group_id)
  • group_post_deliveries 建联合索引 (group_id, deliver_time DESC, delivery_id DESC, post_id)
• 千级DAU场景不需要额外做预聚合，上述SQL性能够用；如果后续QPS涨到万级，可以加一层Redis缓存用户最近200条Feed，过期时间设为1小时即可。

注意：不要把原始事件拉到应用层内存做聚合，数据库窗口函数处理有序分组的效率比应用层遍历高10~100倍，尤其是Feed流分页场景下优势更明显。

• pg：Postgres生态成熟的Node.js客户端，支持连接池、参数化查询，无额外依赖，性能最高，直接运行上述SQL即可。
• kysely：类型安全的轻量SQL构造器，对窗口函数支持完善，编译生成的SQL和手写性能一致，适合TS项目替代裸SQL，避免语法错误。不推荐用TypeORM、Prisma等重型ORM实现这类逻辑，这类工具对窗口函数支持差，生成的SQL常带冗余字段和关联，性能损耗大。
• bullmq：如果需要做Feed预聚合缓存，可以用这个轻量消息队列，新帖子投递到群组时触发异步聚合任务，提前把用户Feed结果写入Redis，进一步降低接口延迟。
• p-queue：控制异步任务并发数，避免预聚合任务打满数据库连接。

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