PostgreSQL中数万条记录的高效余额计算可扩展方案

问题背景 #

我需要在PostgreSQL的一张存有数万条记录的表中，按profileid计算多维度余额。每条记录包含多个字段，用于判定其对各类余额的贡献值，要求计算复杂度为O(N)（N为当前profileid关联的记录总数）。

已尝试的方案 #

• 拉取全量记录到后端计算：扩展性极差，当单个profileid关联记录超过10000条时，数据传输耗时占比极高，且我们仅需最终余额结果，不需要原始记录。
• 编写PostgreSQL聚合查询计算：单profile记录较多时扩展性优于后端计算，但查询复杂度高，仅3-4个并发请求就会耗尽数据库CPU资源。
• 编写PL/pgSQL函数遍历记录计算：这是我计划下一步尝试的方向。

核心问题与疑问 #

核心问题 #

如何在对数据库资源消耗友好的前提下，实现高效的余额计算？

疑问 #

• 上述三种方案是否合理？
• 是否遗漏了其他可行的优化方案？
• 用PL/pgSQL函数遍历记录计算，性能能否优于现有的聚合查询，是否值得投入时间尝试？

补充信息 #

数据表结构 #

CREATE TABLE entries (
  profileid bigint NOT NULL,
  programid bigint NOT NULL,
  ledgerid text NOT NULL, -- 在programid维度之上提供更细的粒度划分
  startdate timestamptz,
  enddate timestamptz,
  amount numeric NOT NULL
)

预期返回格式 #

需要针对指定profileid，按(programid, ledgerid)维度拆分返回余额数据，返回结构定义如下：

RETURNS TABLE (
    programid bigint, 
    ledgerid text, 
    currentbalance numeric, 
    pendingbalance numeric, 
    expiredbalance numeric, 
    spentbalance numeric
)

余额计算规则 #

四个余额字段通过对entries记录的条件算术运算得到，例如：负金额仅计入spentbalance；expiredbalance由金额为正且enddate晚于当前时间的记录求和生成等。

我已经编写了一个包含多个COALESCE(SUM(CASE WHEN ... amount), 0)语句的复杂聚合查询，但不确定将计算逻辑迁移到PL/pgSQL函数是否能带来性能提升。尝试编写函数时，遇到了遍历记录后如何返回指定结构结果的问题，是否需要使用临时表？但该查询每秒需要执行数十次，临时表的开销似乎过于繁琐。

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