PostgreSQL大数量级门店运营数据表预计算汇总表设计咨询

一、低成本优先方案：优化现有daily_data查询性能 #

你当前的性能问题大多是索引和表结构优化不足导致，先做以下调整即可支撑2000万行级别的秒级查询，不需要额外建新表：

• 加覆盖联合索引：idx_pos_date_type(id_pos, date, data_type, value)，查询时直接走索引无需回表读原数据，性能可提升10倍以上
• 按date字段做月级分区，查询时仅扫描对应时间范围的分区，避免全表扫描

二、预聚合表合理设计方案 #

你预想的存储任意时间区间的方案完全不可行：1000家店+3年数据的时间区间组合可达上亿种，既存不下也无法维护。正确的设计是存储固定时间粒度的预聚合数据，再基于固定粒度做任意范围的二次计算：

表结构 #

加唯一索引：uk_pos_granularity_type(id_pos, time_granularity, time_key, data_type)，保障查询性能。

任意时间范围查询逻辑 #

• 查2021-11-02到2021-11-05这类跨天短周期：直接取日粒度对应time_key范围的数据，按门店、数据类型分组求和后计算指标即可，仅需扫4天的聚合数据，比扫原表快数十倍
• 查2021年1月到10月这类长周期：直接取月粒度对应time_key范围的数据求和，仅需扫10条聚合记录，性能可达毫秒级
• 常用的固定周期指标可直接预存到表中，比如月粒度转化率、年度单位工时销售额等，查询时直接取值无需计算

数据更新规则 #

• 日粒度数据：每天凌晨定时计算前一天的数据插入即可
• 周/月/年粒度数据：对应周期结束后第二天计算插入，有实时需求也可配置触发器同步更新

三、高频任意区间查询特殊优化方案 #

如果你的业务90%查询都是随机任意区间且要求毫秒级返回，可以额外加一张常用区间聚合表，仅存储业务高频使用的区间，比如最近7天、最近30天、自然季度、自然年等，不要存所有可能的区间：

• 表结构采用你最初的设计：id_pos、date_begin、date_end、data_type、value
• 定时更新常用区间的聚合值，查询时优先匹配预存的区间，匹配不到再走固定粒度聚合表计算，兼顾性能和灵活性

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