postgresql - 时间序列的数据库设计

Question

我大约每 10 分钟插入 ~50 条具有相同时间戳的记录。
这意味着大约每小时 600 条记录或每天 7.200 条记录或每年 2.592.000 条记录。
用户想要检索时间戳最接近请求时间的所有记录。

设计 #1 - 一个在时间戳列上有索引的表:

    CREATE TABLE A (t timestamp, value int);
    CREATE a_idx ON A (t);

单个插入语句创建约 50 条具有相同时间戳的记录:

    INSERT INTO A VALUES (
      (‘2019-01-02 10:00’, 5),
      (‘2019-01-02 10:00’, 12),
      (‘2019-01-02 10:00’, 7),
       ….
    )

获取最接近请求时间的所有记录
(我使用 PostgreSQL 中可用的函数 greast()):

    SELECT * FROM A WHERE t =
(SELECT t FROM A ORDER BY greatest(t - asked_time, asked_time - t) LIMIT 1)

我认为这个查询效率不高，因为它需要全表扫描。
我打算按时间戳对A表进行分区，每年分区1个，但是上面的近似匹配还是会很慢。

设计 #2 - 创建 2 个表:
第一张表:保留唯一的时间戳和自动递增的 PK，
第二张表:保存第一张表PK上的数据和外键

    CREATE TABLE UNIQ_TIMESTAMP (id SERIAL PRIMARY KEY, t timestamp);
    CREATE TABLE DATA (id INTEGER REFERENCES UNIQ_TIMESTAMP (id), value int);
    CREATE INDEX data_time_idx ON DATA (id);

获取最接近请求时间的所有记录:

SELECT * FROM DATA WHERE id =
(SELECT id FROM UNIQ_TIMESTAMP ORDER BY greatest(t - asked_time, asked_time - t) LIMIT 1)

它应该比设计 #1 运行得更快，因为嵌套选择扫描较小的表。
这种方法的缺点:
- 我必须插入 2 个表而不是一个
- 我失去了按时间戳对 DATA 表进行分区的能力

你能推荐什么？

Answer 1

我会采用 tje 单表方法，也许按年份分区，以便更容易摆脱旧数据。

像这样创建一个索引

CREATE INDEX ON a (date_trunc('hour', t + INTERVAL '30 minutes'));

然后像您编写的那样使用您的查询，但添加

AND date_trunc('hour', t + INTERVAL '30 minutes')
  = date_trunc('hour', asked_time + INTERVAL '30 minutes')

附加条件作为过滤器，可以使用索引。