sql - 有效计算当前活跃用户数

Question

我目前一直在研究如何有效地计算应用程序上任何给定“页面”上的活跃用户数。我正在使用 PostgreSQL 并有一个这样的表:

CREATE TABLE user_is_viewing_page (
  user_id BIGINT,
  page_id BIGINT,
  timestamp TIMESTAMP
);

每个用户将每 10 秒左右使用他们正在查看的页面的 ID 向服务器发送一次 POST，并将新行插入到数据库中。

我必须计算任何给定页面上“活跃”用户数量的当前查询是:

SELECT COUNT(DISTINCT user_id)
FROM user_is_viewing_page WHERE page_id = 1 
                          AND timestamp > CURRENT_TIMESTAMP - INTERVAL '10 seconds';

我想知道使用此查询的最有效方法是什么。请记住，我需要相当频繁地访问此计数(每页每 5-10 秒)。

• 我应该使用查询创建实体化 View 吗？ (知道我必须经常访问它)
• 是否应该在每次需要时自行运行查询？
• 为我的表编制索引以允许快速查找的最佳方法是什么？

Answer 1

物化 View 不会有帮助，因为您的查询需要基于最新的数据，并且您必须像查询一样频繁地刷新 MV。

基于触发器的解决方案 是另一种选择:保持辅助表与当前每页计数保持同步。但我希望(您的许多)写入操作的额外成本比读取操作的 yield 高很多。所以我也会排除这种可能性。

当您使用一张大 table 操作时，我建议使用 partial index :

CREATE INDEX foo ON user_is_viewing_page (page_id, timestamp)
WHERE timestamp > '2014-12-29 23:30:00'::timestamp;  -- start with 'now'

查询(主要是您已有的):

SELECT COUNT(DISTINCT user_id)
FROM   user_is_viewing_page
WHERE  page_id = 1 
AND    timestamp > LOCALTIMESTAMP - INTERVAL '10 sec';

CURRENT_TIMESTAMP 也可以。但是 LOCALTIMESTAMP 对您的设置更有意义。 Per documentation:

CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone.

查找部分索引本身的成本与使用完整索引的成本基本相同。但是由于您的表应该大，部分索引应该比完整索引小很多，这将更适合并留在 RAM 中并且通常更快。如果您有足够的 RAM，请将性能与没有 WHERE 条件的简单、大的完整索引进行比较。

随着时间的推移，部分索引的优势明显减弱。以您选择的时间间隔在 WHERE 条件中创建一个具有更新时间戳的新索引，然后删除旧索引。查询将立即启动新的(较小的)索引，因此可以轻松删除旧索引。这些相关答案中概述了可能的自动化方法并提供了更多解释:

• Best way to exclude outdated data from a search in PostgreSQL
• Indexed ORDER BY with LIMIT 1

您可能需要将索引的确切 WHERE 条件添加到您的查询中(尽管看似多余)以说服查询计划者使用部分索引是安全的。特别是 prepared statements(包括 plpgsql 函数中的所有语句)，其中要比较的实际时间戳是参数化的，否则 Postgres 不能将部分索引用于 generic query plan .

在上面的示例中，您将向查询添加 WHERE 条件:

AND timestamp > '2014-12-29 23:30:00'::timestamp -- matches index condition exactly

可以在 the linked answer above 中找到更通用的解决方案.

旁白:我不会使用“timestamp”作为标识符，因为它是一个基本类型名称。