postgresql - 使用 postgres 的 Web 分析模式

Question

我正在构建一个网络分析工具并使用 Postgresql 作为数据库。我不会在每次用户访问时插入 postgres，而是每 5 秒只插入聚合数据:

time    country    browser    num_visits
========================================
0       USA        Chrome     12
0       USA        IE         7
5       France     IE         5

如您所见，我每 5 秒插入多行(每个维度组合一个)。

为了减少查询中需要扫描的行数，我考虑根据分辨率使用上述架构的多个表:5SecondResolution、30SecondResolution、5MinResolution、...、1HourResolution。现在，当用户询问最后一天时，我将转到比 5 秒分辨率表小的小时分辨率表(尽管我也可以使用那个 - 它只是要扫描更多行)。

现在，如果小时分辨率表有 0、1、2、3 小时的数据，...但是用户要求查看从 1:59 到 8:59 的小时趋势。为了获取 1:59-2:59 期间的数据，我可以对不同的分辨率表进行多次查询，因此我从 1MinResolution 获得 1:59:2:00，从 30MinResolution 获得 2:00-2:30 等。 AFAIU 我已经将一个查询交易到一个巨大的表(有许多相关行要扫描)，将多个查询交易到中型表 + 在客户端组合结果。

这听起来像是一个很好的优化吗？对此还有其他考虑吗？

Answer 1

Now what if the hour resolution table has data on hours 0,1,2,3,... but users asks to see hourly trend from 1:59 to 8:59. In order to get data for the 1:59-2:59 period I could do multiple queries to the different resolutions tables so I get 1:59:2:00 from 1MinResolution, 2:00-2:30 from 30MinResolution and etc.

如果您希望结果准确，则不能这样做。想象一下，如果他们要求从 01:30 到 04:30 解决一个小时的问题。您正在想象您将从 5 秒(或 1 分钟)res 表中获取前半小时和后半小时，然后从一小时表中获取其余部分。

问题是一小时表偏移了半小时，所以答案实际上是不正确的；每小时将从 2:00 到 3:00 等，当用户想要 2:30 到 3:30 时。当您转向更粗糙的分辨率时，这是一个更严重的问题。

因此:这是一种非常合理的优化技术，但前提是您将用户的搜索开始精度限制为聚合表的分辨率。如果他们想要一个小时的解决方案，请强制他们选择 1:00、2:00 等，并且不允许设置分钟。如果他们想要 5 分钟的分辨率，让他们选择 1:00、1:05、1:10 等。您不必以同样的方式限制结束精度，因为不完整的结束间隔不会影响结束之前的数据，并且在显示时很容易被标记为不完整。 “当前日期”、“到目前为止的小时数”等。

如果您限制起始精度，您不仅会给出正确的结果，还会大大简化查询。如果您也限制了 end 精度，那么您的查询纯粹是针对聚合表的，但是如果您想要“最新”数据，则很容易编写如下内容:

SELECT blah, mytimestamp
FROM mydata_1hour
WHERE mytimestamp BETWEEN current_date + INTERVAL '1' HOUR AND current_date + INTERVAL '4' HOUR
UNION ALL
SELECT sum(blah), current_date + INTERVAL '5' HOUR
FROM mydata_5second
WHERE mytimestamp BETWEEN current_date + INTERVAL '4' HOUR AND current_date + INTERVAL '5' HOUR;

...甚至使用多个级别的联合来满足对较粗分辨率的请求。