时间戳

首页 > 解决方案 > 规范化的最佳实践

timescaledb - 规范化的最佳实践

问题描述

我想知道是否有关于时间序列数据标准化的最佳实践。我在 TimescaleDB 中有一个超表，看起来像这样

                      Table "public.mainsfrequency"
  Column  |            Type             | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
 time     | timestamp without time zone |           | not null |
 host     | text                        |           |          |
 location | text                        |           |          |
 freq     | double precision            |           |          |

host规范化并location分成单独的（非超）表并在上述超表中使用外键是一个好主意吗？我读过不支持使用外键引用超表，但是另一个方向呢？

标签： timescaledb

解决方案

正确，它可以在超表内创建外键以引用普通（非超）表，但不相反。所以这将起作用：

CREATE TABLE host(
  id INT PRIMARY KEY,
  host TEXT,
  location TEXT
);
CREATE TABLE mainsfrequency(
  time TIMESTAMP NOT NULL,
  host_id INT REFERENCES hosts(host_id),
  freq DOUBLE PRECISION
);
SELECT create_hypertable('mainsfrequency','time');

当您决定是在超表中存储host和location值还是将其移动到单独的表中更好时，请考虑将存储多少数据、值的唯一性、如何查询数据以及如何使用超表。

例如，如果文本重复很多，则每行重复的主机名和位置文本将比存储在单独的表中占用更多的空间。但是，如果您将使用压缩，那么您可以节省大量空间，同时将值保留在超表中。

如果您将使用连续聚合来计算和存储聚合，那么您只能使用存储在超表中的值，因为连续聚合不支持连接。例如，如果您想具体化某些位置的聚合，那么如果该位置存储在单独的表中，则无法完成。

因此，以下查询不能放入连续聚合中：

SELECT time_bucket(INTERVAL '1h', time), host, AVG(freq)
FROM mainsfrequency, host
WHERE host_id = id AND location = 'NY'
GROUP BY 1, 2;

要在连续聚合中进行此类查询，它将是：

CREATE TABLE mainsfrequency(
  time TIMESTAMP NOT NULL,
  host TEXT,
  location TEXT,
  freq DOUBLE PRECISION
);
SELECT create_hypertable('mainsfrequency','time');

CREATE MATERIALIZED VIEW mainsfrequency_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '1h', time), host, AVG(freq)
FROM mainsfrequency
WHERE location = 'NY'
GROUP BY 1, 2;

推荐阅读