百万级高频更新表实时聚合统计的高效实现与数据库建模咨询

安全阿岳

2026-10-24

高写入场景下近实时观看时长统计的落地方案

核心判断：此前顾虑的触发器效率问题，本质是最初设计的触发器逻辑过于繁重，换成轻量预聚合方案后，现有4核/8G配置的托管Postgres完全可以平稳支撑，不需要额外扩容、更换数据库或者引入第三方组件。

第一步：先把原业务表的基础查询成本打下来

你当前的进度表是典型的时序写入表（每次用户操作按顺序插入新的进度记录），这种表根本不需要建占空间很大的传统B树索引，给表加(user_id, created_at)的BRIN块级范围索引即可。
BRIN索引是块级的范围映射索引，对于按时间顺序写入的时序表，百万级数据的索引体积才几十KB，比传统B树索引小两个数量级，扫描近X天数据的速度和B树几乎没有差别，完全不会出现全表扫描打满IO的问题。

很多人对触发器的坏印象，来自于早年PG版本里触发器要做复杂JOIN、全表扫描的场景，只要把预聚合粒度拆细，触发器的逻辑可以轻到几乎无感知：

新建独立的预聚合表，结构非常简单，主键为(user_id, time_window_start)，每行存储对应用户在固定10分钟窗口内的累计观看秒数：
```
CREATE TABLE user_watch_agg (
  user_id bigint,
  window_start timestamp,
  total_watch_seconds int,
  PRIMARY KEY (user_id, window_start)
);
```
给原业务表加的触发器逻辑，完全不需要读表、不需要做复杂计算：
1. 直接拿到当前INSERT进来的user_id、created_at、watch_seconds三个字段
2. 计算这条记录对应的10分钟窗口的起始时间（比如10:07的记录，对应10:00的窗口）
3. 直接对预聚合表执行单行UPSERT：把当前这条记录的watch_seconds累加到预聚合表对应的窗口行上
单次触发的成本仅比原表的INSERT操作多一次单行的主键查询+更新，没有聚合计算、没有全表扫描，哪怕每秒几百次的进度写入，4核/8G的实例完全可以平稳承载。
关于多节点INSERT的顾虑（播放开始、暂停、结束多次触发）：逻辑上根本没有区别，不管一次播放过程插多少条记录，每条记录的数值都是直接累加到对应窗口，没有额外的判断开销，写入量和原业务的INSERT量完全1:1，不会有额外放大。

统计各用户近X天的累计观看分钟数，只需要对预聚合表做简单的SUM聚合：把近X天内对应用户所有窗口的total_watch_seconds加总，除以60转成分钟即可。
预聚合表的规模比原业务表小两个数量级（10分钟粒度的窗口，哪怕近30天的数据，单用户的记录数也才4000多条），查询走主键索引扫描，毫秒级就能出全量结果，根本不会给数据库带来负载。
选10分钟的窗口粒度是经过平衡的：粒度太粗（比如按天）达不到近实时的要求，粒度太细（比如1分钟）会让预聚合表的写入放大明显上升，10分钟粒度的统计延迟完全满足看板要求，同时把预聚合表的规模控制到最小。

如果对主库的写入延迟有极高要求，连触发器的那点额外开销都想省下来，完全可以用Postgres自带的逻辑复制功能替代触发器：

不需要在主业务事务里加任何逻辑，只需要开启原表的逻辑复制，让PG把原表的WAL变更日志流出来，在旁路攒够1000条变更或者每10秒批一次，批量执行预聚合表的UPSERT操作。
托管Postgres实例基本都默认开启了逻辑复制的开关，不需要自己编译插件，直接用控制台的参数就能开，没有额外的运维成本。
这个方案的主库写入路径上完全没有额外开销，所有的聚合计算都在WAL回放的旁路执行，哪怕批处理有短时压力，也不会阻塞主业务的写入，统计延迟最多10-15秒，完全满足近实时的要求。

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