Postgres hstore 用于时间序列

Question

Postgres hstore 用于时间序列

6

我对Postgres不熟悉，正在尝试使用hstore扩展。需要一些指导。我需要支持各种产品的基本时间序列数据报告。我有大量以“时间戳，值”格式存储的每个产品的数据。每个产品的数据都在一个csv文件中。

我考虑使用hstore将这些数据存储在键值格式中。假设所有单个产品的时间序列数据都可以存储在单个hstore对象中。我需要能够按特定时间查询此数据，例如给定时间时产品的价值是多少？还需要运行简单的查询，例如检索产品成本超过100美元的时间。我打算拥有一个带有产品ID列和一个hstore列的表。但我不是很清楚如何使其工作：

需要从存在于csv中的数千个时间戳，值记录中加载hstore列。每当我们获得新的csv时，就应该添加hstore。
表需要存储productId和相应的TimeSeries数据。请问是否使用hstore会有帮助？如果是，则如何像上面解释的那样从csv加载数据？此外，随着数据增长，插入/更新hstore的性能是否会受到影响，请分享您的经验。

- zing

我同意Edmnud的观点。hstore不是这项工作的好选择。如果时间值在hstore中，您无法有效地使用b-tree索引。更重要的是，更新hstore将需要在新的行版本中重新编写整个hstore，与仅在子表中插入/更新/删除单个值相比，这非常昂贵。如果值在hstore中，则无法使用排除约束来防止时间重叠。我看不出在这里使用hstore的理由，但有很多不使用的理由。 - Craig Ringer

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Edmund · Accepted Answer

我觉得你应该从一个简单的、规范化的架构开始，特别是因为你是新手。比如这样：

CREATE TABLE product_data
(
    product TEXT,  -- I'm making an assumption about the types of your columns
    time TIMESTAMP,
    value DOUBLE PRECISION,

    PRIMARY KEY (product, time);
);

我会牢记使用hstore等类似选项，当您的数据变得足够大时，效率比简单性更重要。但请注意，所有选项都有效率上的权衡。

您知道要支持多少数据吗？每个产品的不同时间戳数量？

您想运行哪些其他查询？如果产品具有许多不同的时间戳，则查询单个产品成本超过100美元的时间会从对（product，value）的索引中受益。 其他选项 hstore最有用的是如果您想在一行中存储任意键值对的表集合。您可以在此处使用它，为每个产品创建一行，并将该产品的每个不同时间戳作为产品表中的一个键。缺点是hstore中的键和值均为文本，而您的键是时间戳，您的值是某种数字。因此，类型检查会有一定的减少，并且需要进行某些类型转换的成本会有所增加。另一个可能的缺点是，hstore上的一些查询可能无法非常有效地使用索引。上面的表可以使用简单的btree索引进行范围查询（例如，您想要提取产品的两个日期之间的值）。但是，hstore索引要受到更多限制；您可以在hstore列上使用gist或gin索引，以查找所有具有特定键的行。

另一个选项（我已经玩过并且在我的一些数据库中进行实验）是数组。基本上，每个产品将具有一个值数组，并且每个时间戳都映射到数组中的一个索引。如果时间戳完全规则，则这很容易。例如，如果您的所有产品每天每小时都有一个值，您可以使用如下表：

CREATE TABLE product_data
(
    product TEXT,
    day DATE,
    values DOUBLE PRECISION[], -- An array from 0 to 23.

    PRIMARY KEY (product, day);
);

您可以构建视图和索引来使查询该表变得较为容易。(我在http://ejrh.wordpress.com/2011/03/20/vector-denormalisation-in-postgresql/上写了一篇关于这种技术的博客文章。)但我的建议仍然是:从一个简单的表开始，然后在需要时探索提高效率的方法。