Postgres表按ID实时统计记录数的最快实现方式

300-500 reqs/sec的查询压力属于中低负载，不管是Postgres原生能力还是轻量外置方案都能轻松覆盖，按落地成本从低到高整理可行方案如下，全部满足数秒内延迟的要求：

Postgres原生优化（无额外组件依赖，一致性最高） #

• 基础索引优化打底
  别用select count(id)的写法，直接换成count(*)，Postgres对count(*)有专门优化，不需要判断字段非空，执行效率远高于按字段计数。如果你的查询逻辑是SELECT count(*) FROM 业务表 WHERE 匹配ID = $1，直接给匹配ID字段建普通B树索引即可：

  CREATE INDEX idx_业务表_匹配ID ON 业务表(匹配ID);
  

  这种场景下查询会直接走B树索引的精准范围扫描，不需要回表取数据，单台普通规格的PG实例扛数千QPS这类查询都没有压力，平均延迟通常在1ms以内。如果单表数据量过亿、且匹配ID和数据写入顺序强相关，也可以换成BRIN索引，索引体积会小一个数量级，查询性能损失极小。
• 触发器维护实时计数表
  如果单表写入量很大，走索引扫的延迟已经不能满足要求，可以直接在PG内部维护一张专用计数表：

  CREATE TABLE 匹配ID计数表 (
    匹配ID bigint PRIMARY KEY,
    总条数 bigint NOT NULL DEFAULT 0
  );
  

  给原表加三个行级触发器：插入新行时对应匹配ID的计数值+1，删除行时对应值-1，更新操作如果修改了匹配ID字段，就给旧ID减1、新ID加1。查询时直接按主键查计数表即可，单实例扛几万QPS毫无压力，计数是强一致实时的，没有陈旧延迟。如果单个匹配ID的写入QPS特别高（比如单ID每秒写入超千行）出现行锁竞争，可以给每个ID拆10个分桶计数行，写入时随机选一个桶更新，查询时sum10个桶的总值，就能把锁冲突降到原来的1/10。
• 准实时物化视图
  如果不想加触发器影响写入性能，可以按匹配ID维度建聚合物化视图，搭配pg_cron定时任务每1-2秒做增量刷新（刷新时带CONCURRENTLY参数避免锁表），只更新最近几秒有数据变动的ID计数，资源占用极低，延迟稳定在1-3秒，完全满足要求。

外置缓存/专用存储方案（适合不想侵入PG库表逻辑的场景） #

• 本地进程内缓存
  零成本兜底方案：在业务服务进程内加一层本地缓存，存每个匹配ID的计数值，设置1-3秒的过期时间，请求优先读本地缓存，缓存未命中时再查PG。加个简单的单飞逻辑避免缓存击穿（同一个key同时miss时只放一个请求去查PG），最终打到PG的请求量会降到每秒个位数，哪怕不加任何PG优化都能扛住压力。
• Redis实时计数
  用Redis的String或者Hash结构存储每个匹配ID的计数值，业务写入数据时可以同步更新计数，也可以把更新事件丢到消息队列异步消费更新，查询直接走Redis。怕计数不准的话可以每隔几分钟跑一次PG的真实count值做校准，这个方案扛10w+QPS都没有压力，延迟通常在亚毫秒级，异步更新的话延迟也能控制在1秒内。
• CDC流计算聚合
  如果原表写入量极大（每秒写入超10万行），可以开PG的逻辑复制槽抽取表的增删改变更日志，用轻量流计算任务实时按匹配ID聚合计数，结果存到高速KV里供查询，对PG本身的性能影响几乎为0，延迟稳定在几百毫秒到1秒。

选型建议：如果业务表写入QPS在1万以下，直接给匹配ID建B树索引+本地1秒缓存就足够满足需求，开发量几乎为0；如果写入量更高、追求强一致实时性，优先选PG触发器维护计数表的方案，不需要额外运维组件；如果不想改动PG侧的逻辑，再选Redis+异步更新的方案即可。

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