基于PostgreSQL数组的30天滑动窗口方案可行性与效率咨询

需求背景 #

要给4000万用户做30天滑动窗口计算，统计每个用户在窗口内属于A、B、C类的次数，核心诉求是不想每次统计都回溯扫描30天的全量数据，于是考虑用PostgreSQL数组维护用户的每日类别数据，现在不确定这个方案的效率表现。

数组方案核心思路 #

为每个用户维护一个固定长度为30的数组calc_30d_arr，存储每日所属类别：

• 每日新增数据时，将当日类别插入数组头部，同时截断数组保持长度为30，自动丢弃最早的一天数据
• 统计时直接基于数组计算各类别次数，无需扫描历史分区

修正后的数组更新代码 #

原代码存在长度溢出问题：若原数组为30个元素，calc_30d_arr[:30]再前置新元素会变成31个，需取前29位保证总长度为30：

select  
   a.user_id,
   array_prepend(a.class, b.calc_30d_arr[:29]) as count_array
from table_{last_partition} a
inner join array_table b on (a.user_id = b.user_id)

修正后的统计代码 #

原统计代码中cnt_C误匹配为'B'，此处修正：

select 
      cardinality(array_positions(calc_30d_arr, 'A')) as cnt_A,
      cardinality(array_positions(calc_30d_arr, 'B')) as cnt_B,
      cardinality(array_positions(calc_30d_arr, 'C')) as cnt_C
from array_table

方案效率分析 #

核心优势 #

• 避免全量扫描：每日更新仅需关联最新数据分区与用户数组表，无需遍历30天历史数据，IO开销大幅降低
• 统计响应极快：直接调用PostgreSQL内置函数对数组计算，无需聚合多日数据，统计查询耗时极短

潜在问题与优化方向 #

1. 高并发写入锁竞争：4000万用户每日更新会给array_table带来不小写入压力，建议：
   • 按user_id对array_table做哈希分区，分散写入负载
   • 使用INSERT ... ON CONFLICT (user_id) DO UPDATE原子操作替代先查询再更新，减少锁等待
2. 数组存储空间：假设类别为单字符，4000万用户的总存储约12GB（4000万×30×1字节），属于可接受范围；若将类别改为枚举类型，还能进一步压缩空间
3. 统计函数优化：原统计代码多次调用array_positions会重复遍历数组，改成一次拆分数组统计效率更高：

   select 
      user_id,
      sum(case when elem = 'A' then 1 else 0 end) as cnt_A,
      sum(case when elem = 'B' then 1 else 0 end) as cnt_B,
      sum(case when elem = 'C' then 1 else 0 end) as cnt_C
   from array_table, unnest(calc_30d_arr) as elem
   group by user_id
   

与传统方案对比 #

• 传统方案每次统计需扫描4000万×30=12亿条记录，IO与计算成本极高，无法支持高频统计
• 数组方案每日更新仅处理当日新增用户数据（若每日活跃用户占比低，开销更小），统计仅需扫描4000万个数组，效率提升显著

内容的提问来源于stack exchange，提问作者Maria Luiza Wuillaume