postgresql - 在 Postgres 中使用日期范围作为聚合时间序列数据的键

Question

我们正在处理时间序列聚合数据，聚合到固定类型的周期，例如从星期二到星期三的几周。

使用 Postgres daterange 类型作为此数据的 key 会被认为是“不好的做法”吗？ (而不是例如使用定义此日期范围的 “PeriodID” 键，然后加入日历维度以标识该日期范围。)

在我看来，单独的 "PeriodID" 只是多余的，因为 daterange 有一个非常明确的定义(在我们的设置中，它对每个观察)。

有没有:

• 性能方面的考虑？
• 其他注意事项？

Answer 1

在抽象层面上，tstzrange 将是此类聚合的完美表示。

您应该考虑的问题是您将如何查询数据，以及如何有效地使用索引。

如果要索引“包含”运算符@> 或“重叠”运算符&&，则不能使用 B 树索引，只能使用 GiST 和 SP- GiST 索引(参见 the documentation )。您必须对此进行基准测试，但很可能使用这样的索引会比在 timestamptz 列上使用 B 树索引慢。索引也可能使用更多空间。

将 timestamptz 与 B 树索引一起使用的一种简单方法是存储范围的下限并像这样查询:

... WHERE weekstart <= atimestamp
      AND weekstart > atimestamp - INTERVAL '1 week'

或者查询重叠区间:

... WHERE weekstart <= endtimestamp
      AND weekstart > starttimestamp - INTERVAL '1 week'