postgresql - 当任何 "top"行更改时，Postgres 触发

Question

我有一个大约有 10,000 个用户的表。我想查看按 score 排序的前 10 名，并在列表的组成发生变化或前 10 名的任何分数发生变化时提供实时更新。

使用触发器执行此操作的最佳方法是什么，这是否会成为保持运行的非常昂贵的触发器？

Answer 1

只要您在 score 上有一个索引，拥有一个包含以下内容的 AFTER UPDATE 触发器 FOR EACH ROW 会很便宜:

IF (SELECT count(*)
    FROM (SELECT 1
          FROM users
          WHERE score > NEW.score
          LIMIT 10
         ) top_users
   ) < 10
  OR
   (SELECT count(*)
    FROM (SELECT 1
          FROM users
          WHERE score > OLD.score
          LIMIT 10
         ) top_users
   ) < 10
THEN
   /* your action here */
END IF;

您还需要一个仅包含查询的第二部分的 DELETE 触发器和一个仅包含第一部分的 INSERT 触发器。

我在这里看到的问题是/* your action here */。
这应该是一个很短的操作，比如向队列中添加一些东西，否则您最终可能会遇到长事务和长锁。