如何在PostgreSQL中缓存最近1000行的运行平均值以加速查询

1. 先优化原查询：给index列创建索引 #

你的原查询重复扫描旧行，大概率是因为index列没有建立索引。给index列创建B-tree索引后，PostgreSQL就能直接通过索引快速定位到最近1000行，无需扫描全表：

CREATE INDEX idx_a_index ON A(index);

创建索引后，原查询的执行计划会变成通过索引反向扫描取最后1000行，性能会大幅提升，这是最基础且有效的优化。

2. 使用物化视图+触发器实现实时缓存平均值 #

如果查询频率极高，不想每次都计算，可以用PostgreSQL的物化视图存储最近1000行的总和与数量（直接存平均值也可以，但增量更新总和+数量更灵活），再通过触发器实现插入新行时自动更新缓存：

步骤1：创建物化视图初始化缓存 #

CREATE MATERIALIZED VIEW mv_recent_1000_avg AS
SELECT 
    COALESCE(sum(id), 0) AS sum_id,
    COALESCE(count(id), 0) AS cnt
FROM (
    SELECT id FROM A ORDER BY index DESC LIMIT 1000
) AS sub;

步骤2：创建触发器函数实现增量更新 #

CREATE OR REPLACE FUNCTION update_recent_avg()
RETURNS TRIGGER AS $$
DECLARE
    oldest_id INT;
BEGIN
    -- 把新行的id加入总和与计数
    UPDATE mv_recent_1000_avg
    SET sum_id = sum_id + NEW.id, cnt = cnt + 1;

    -- 如果计数超过1000，移除最早的那行数据
    IF (SELECT cnt FROM mv_recent_1000_avg) > 1000 THEN
        -- 找到第1001行的id（即需要移除的最早行）
        SELECT id INTO oldest_id
        FROM A
        WHERE index = (
            SELECT min(index) 
            FROM (SELECT index FROM A ORDER BY index DESC LIMIT 1001) AS sub
        );

        -- 从总和中减去最早行的id，计数减1
        UPDATE mv_recent_1000_avg
        SET sum_id = sum_id - oldest_id, cnt = cnt - 1;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤3：绑定触发器到表A #

CREATE TRIGGER trg_update_recent_avg
AFTER INSERT ON A
FOR EACH ROW EXECUTE FUNCTION update_recent_avg();

步骤4：查询缓存的平均值 #

之后直接查询物化视图就能得到结果，无需再扫描原表：

SELECT sum_id / cnt AS avg_id FROM mv_recent_1000_avg;

3. 定时刷新物化视图（适合非实时场景） #

如果对实时性要求不高，比如允许几分钟的延迟，可以用pg_cron扩展定时刷新物化视图，避免触发器的开销：

步骤1：安装pg_cron扩展 #

CREATE EXTENSION pg_cron;

步骤2：设置定时任务 #

比如每分钟刷新一次：

SELECT cron.schedule('refresh-recent-avg', '* * * * *', 'REFRESH MATERIALIZED VIEW mv_recent_1000_avg;');

步骤3：查询缓存结果 #

同样直接查询物化视图即可：

SELECT avg_id FROM mv_recent_1000_avg;

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