PostgreSQL海量多表数据按小时聚合求和方案咨询

给每个客户单独创建物化视图的设计本身就是跨客户全局聚合场景的反模式，靠关联数千张视图逐列求和的写法，不仅会触发SQL语句长度上限，执行阶段的SQL解析、元数据拉取、执行调度开销也会高到完全不可用，属于架构选型走偏带来的问题，不需要在多表拼接的思路上死磕。

1. 基于原始业务大表直接构建全局小时聚合物化视图（首选方案） #

你当前1万客户规模日增24万条数据，年增量仅8700余万，这个量级只要给原始表做好基础的分区、索引，完全不需要拆数千张单客户视图来提性能：

• 先给原始业务大表按hour字段做时间范围分区，所有时间维度的查询、聚合只会扫描命中的时间分区，不会触发全表扫描
• 直接创建全局聚合物化视图，逻辑极简，不存在SQL过长问题：

CREATE MATERIALIZED VIEW mv_hour_total_consume
AS
SELECT
  hour,
  SUM(amount) AS total_amount
FROM 海量业务大表
GROUP BY hour;

• 配置增量刷新规则：每小时客户数据写入完成后，仅刷新物化视图中对应小时的分区数据，单次刷新的计算量就是1万条记录求和，毫秒级即可完成，查询时直接读这张单表，性能远高于拼接数千张视图的方案。
• 原有的单客户查询性能需求不需要靠独立物化视图支撑：直接在原始业务大表上创建(customerid, hour)联合索引，单客户查指定时间范围消费数据的性能，和查独立客户物化视图没有可感知的差异，还能省掉数千张视图的存储、日常刷新维护成本。

2. 临时过渡方案（暂不调整现有单客户视图架构时用） #

放弃多表JOIN逐列求和的写法，改用UNION ALL拼接所有单客户物化视图的数据后统一聚合，不要硬编码全量视图的拼接SQL：

• 查数据库内置的系统元数据表，过滤得到所有客户专属物化视图的表名，通过存储过程动态生成拼接SQL：循环给每个客户视图拼接SELECT hour, amount FROM [对应客户物化视图名]片段，片段之间用UNION ALL连接，最外层套GROUP BY hour, SUM(amount)的聚合逻辑，直接执行动态SQL写入新的全局聚合物化视图即可，不会触发SQL长度限制。
• 注意这个方案只是临时兜底：每新增一个客户就要新建视图、更新动态拼接逻辑，存储碎片多、维护成本极高，只要条件允许尽快切换到方案1。

3. 长期扩容预案 #

如果后续客户规模涨到10万级、日增数据破千万，只需要在原始数据写入链路加一层轻量预聚合：每小时数据写入完成后，先计算当前小时所有客户的总消费额，写入一张独立的小时维度聚合结果表，后续所有跨客户的时间维度统计都直接查这张表——单小时全量聚合结果仅1条记录，哪怕存10年数据也不到9万条，完全不存在性能瓶颈。

避坑提醒：不要为了单客户查询场景随便把同结构数据拆成数千张分表/分视图，这种拆分仅在单表数据量破亿、且99%以上请求都是单客户维度查询的场景下才考虑，你当前的数据规模远达不到拆表阈值，拆完反而会把全局统计类需求的路径完全堵死。

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