主页 / dba / 问题 / 107207
Accepted
guest82
Asked: 2015-07-17 07:15:57 +0800 CST

如何存储时间序列数据

  • 772

我有一个我认为是一个时间序列数据集（如果我错了，请纠正我），它有一堆相关的值。

一个示例是对汽车进行建模并在旅途中跟踪其各种属性。例如：

时间戳 | 速度 | 行驶距离 | 温度 | ETC

存储这些数据的最佳方式是什么，以便 Web 应用程序可以有效地查询字段以查找最大值、最小值并随时间绘制每个数据集？

我开始了一种天真的方法来解析数据转储并缓存结果，这样就不必存储它们了。然而，在玩了一会儿之后，由于内存限制，这个解决方案似乎无法长期扩展，如果要清除缓存，那么所有数据都需要重新解析和重新缓存。

此外，假设每秒跟踪一次数据，很少有可能超过 10 小时的数据集，通常建议通过每 N 秒采样一次来截断数据集吗？

postgresql architecture

2 个回答

  1. Best Answer

    确实没有一种“最好的方法”来存储时间序列数据，它实际上取决于许多因素。但是，我将主要关注两个因素，它们是：

    (1) 这个项目有多严重，值得你努力优化模式？

    (2) 你的查询访问模式到底是什么样的？

    考虑到这些问题，让我们讨论一些模式选项。

    平桌

    使用平面表的选项与问题(1)有更多关系，如果这不是一个严肃或大规模的项目，您会发现不用过多考虑架构会容易得多，并且只需使用平板，如：

    CREATE flat_table(
  trip_id integer,
  tstamp timestamptz,
  speed float,
  distance float,
  temperature float,
  ,...);

    我推荐这门课程的情况并不多，只有当这是一个小项目，不值得你花很多时间。

    尺寸和事实

    因此，如果您已经清除了问题(1)的障碍，并且您想要一个性能更高的模式，那么这是首先要考虑的选项之一。它包括一些基本的规范化，但从测量的“事实”数量中提取“维度”数量。

    本质上，您需要一张表格来记录有关旅行的信息，

    CREATE trips(
  trip_id integer,
  other_info text);

    和一个记录时间戳的表格，

    CREATE tstamps(
  tstamp_id integer,
  tstamp timestamptz);

    最后是所有测量的事实，以及对维度表的外键引用（即meas_facts(trip_id)引用trips(trip_id)和meas_facts(tstamp_id)引用tstamps(tstamp_id)）

    CREATE meas_facts(
  trip_id integer,
  tstamp_id integer,
  speed float,
  distance float,
  temperature float,
  ,...);

    起初这似乎不是那么有帮助，但是如果您有例如数千个并发行程，那么他们可能都在第二次每秒进行一次测量。在这种情况下，您必须每次为每次旅行重新记录时间戳，而不是仅使用表中的单个条目tstamps。

    用例：如果您正在记录许多并发行程的数据，并且您不介意同时访问所有测量类型，那么这种情况会很好。

    由于 Postgres 是按行读取的，因此在您想要的任何时候，例如，在speed给定时间范围内的测量值，您必须从表中读取整行meas_facts，这肯定会减慢查询速度，尽管如果您正在使用的数据集是不要太大，那么你甚至不会注意到差异。

    拆分你的测量事实

    为了进一步扩展最后一部分，您可以将测量结果分成单独的表格，例如，我将在表格中显示速度和距离：

    CREATE speed_facts(
  trip_id integer,
  tstamp_id integer,
  speed float);

    和

    CREATE distance_facts(
  trip_id integer,
  tstamp_id integer,
  distance float);

    当然，您可以看到如何将其扩展到其他测量。

    用例：因此，这不会给您带来极大的查询速度，当您查询一种测量类型时，可能只会线性增加速度。这是因为当您要查找有关速度的信息时，您只需要从speed_facts表中读取行，而不是在表的行中出现的所有额外的、不需要的信息meas_facts。

    因此，您只需要阅读关于一种测量类型的大量数据，就可以获得一些好处。对于您建议的以一秒为间隔的 10 小时数据的情况，您只会读取 36,000 行，因此您永远不会真正从中找到显着的好处。但是，如果您要查看大约 10 小时的 5,000 次行程的速度测量数据，那么现在您正在查看 1.8 亿行数据。只要您一次只需要访问一种或两种测量类型，此类查询的速度线性提高可能会产生一些好处。

    数组/HStore/ & TOAST

    您可能不需要担心这部分，但我知道它确实很重要的情况。如果您需要访问大量时间序列数据，并且您知道需要在一个巨大的块中访问所有这些数据，则可以使用一种结构，该结构将利用TOAST Tables，它本质上将您的数据存储在更大的压缩中段。只要您的目标是访问所有数据，这将导致更快地访问数据。

    一个示例实现可能是

    CREATE uber_table(
  trip_id integer,
  tstart timestamptz,
  speed float[],
  distance float[],
  temperature float[],
  ,...);

    在这个表中，tstart将存储数组中第一个条目的时间戳，每个后续条目将是下一秒的读数值。这需要您管理一个应用软件中每个数组值的相关时间戳。

    另一种可能是

    CREATE uber_table(
  trip_id integer,
  speed hstore,
  distance hstore,
  temperature hstore,
  ,...);

    您将测量值添加为（键，值）对（时间戳，测量）。

    用例：这种实现可能最好留给更熟悉 PostgreSQL 的人，并且前提是您确定您的访问模式需要是批量访问模式。

    结论？

    哇，这比我预期的要长得多，对不起。:)

    本质上，有很多选择，但您可能会通过使用第二个或第三个来获得最大的收益，因为它们适合更一般的情况。

    PS：您最初的问题暗示您将在收集完所有数据后批量加载数据。如果您要将数据流式传输到 PostgreSQL 实例，则需要做一些进一步的工作来处理数据摄取和查询工作负载，但我们将把它留到另一个时间。;)

    • 34

  2. 它的2019 年，这个问题值得一个更新的答案。

    • 该方法是否是最好的，我会让您进行基准测试和测试，但这里有一种方法。
    • 使用名为timescaledb的数据库扩展
    • 这是安装在标准 PostgreSQL 上的扩展，可以很好地处理在存储时间序列时遇到的几个问题

    以你为例，首先在 PostgreSQL 中创建一个简单的表

    步骤1

    CREATE TABLE IF NOT EXISTS trip (
    ts TIMESTAMPTZ NOT NULL PRIMARY KEY,
    speed REAL NOT NULL,
    distance REAL NOT NULL,
    temperature REAL NOT NULL
)

    第2步

    • 把它变成timescaledb世界中所谓的超表。
    • 简单来说，就是一张大表，在一定的时间间隔内不断的分成小表，比如说一天，每个小表被称为一个chunk

    • 尽管您可以在查询中包含或排除它，但在运行查询时这个迷你表并不明显

      SELECT create_hypertable('trip', 'ts', chunk_time_interval => 间隔'1 小时', if_not_exists => TRUE);

    • 我们上面所做的就是获取我们的行程表，每小时根据“ts”列将其划分为小块表。如果您添加 10:00 到 10:59 的时间戳，它们将被添加到 1 个块中，但 11:00 将被插入到一个新块中，这将无限进行。

    • 如果您不想无限地存储数据，您也可以使用 DROP 超过 3 个月的数据块

      SELECT drop_chunks（间隔'3个月'，'旅行'）；

    • 您还可以使用如下查询获取迄今为止创建的所有块的列表

      SELECT chunk_table, table_bytes, index_bytes, total_bytes FROM chunk_relation_size('trip');

    • 这将为您提供迄今为止创建的所有迷你表的列表，如果需要，您可以从此列表中对最后一个迷你表运行查询

    • 您可以优化查询以包含、排除块或仅对最后 N 个块进行操作等

    • 6

相关问题