我正在设计一个 Postgres 表来存储大量时间序列数据,并试图找出构造列的最佳方法。我看过这样的答案,但由于它已经有近 10 年的历史了,我想看看是否有任何我应该注意的新事物。
时间序列数据来自多个来源(src_id
示例中就是如此)。每个来源每分钟会有一个数据点,每个数据点有许多不同的测量值。测量值表示特定分钟的温度、湿度等。不过,为了便于举例,我将它们抽象为“测量值 A”、“测量值 B”等。目前需要支持的测量类型有 20 种,将来还会添加更多。
数据量达到数十亿行。绝大多数写入操作将为当前分钟添加新行。典型的读取查询将针对特定源、时间窗口和测量类型。我还计划对我选择的任何表进行分区,也许将其划分为一个月的分区。
选项 1) 平桌
我可以实现一个简单的平面表。但有一个缺点,随着我随着时间的推移添加更多测量类型,我将不得不用新列更新表格。它也从 23 列开始,这似乎走错了路。
TABLE data_points (id, src_id, timestamp , measurement_a, measurement_b, ...)
(1 , 1 , 2024-01-01 00:00:00, 100 , 6.8 , ...)
(2 , 2 , 2024-01-01 00:00:00, 55 , 0.1 , ...)
选项 2)键值对
这样可以将列数减少到一定数量,因此在添加新测量值时,我无需用新列更新表格。但是行数会多得多(开始时是 20 倍,因为我从 20 种测量类型开始)。
TABLE data_points (id, src_id, timestamp , meas_type, meas_value)
(1 , 1 , 2024-01-01 00:00:00, A , 100 )
(2 , 1 , 2024-01-01 00:00:00, B , 6.8 )
...
(3 , 2 , 2024-01-01 00:00:00, A , 55 )
(4 , 2 , 2024-01-01 00:00:00, B , 0.1 )
...
选项 3)两个表
我可以让一个表存储 src_id 和时间戳,另一个表存储测量数据。这类似于键值对,只是分成两个表,这样我就不必重复和src_id
列timestamp
了。
不过,这可能会使分区变得有点棘手。此外,所有读取都必须进行连接,而我更关心性能而不是整体数据库大小,所以也许这不值得付出开销?
TABLE data_point_times (id, src_id, timestamp )
(1 , 1 , 2024-01-01 00:00:00)
(2 , 1 , 2024-01-01 00:00:00)
TABLE data_point_values (id, data_point_time_id, meas_type, meas_value)
(1 , 1 , A , 100 )
(2 , 1 , B , 6.8 )
...
(3 , 2 , A , 55 )
(4 , 2 , B , 0.1 )
...
选项 4)jsonb
我可以使用 jsonb 实现“两全其美”的效果;列数固定,行数较少。但这可能存在一些我不知道的缺点?
TABLE data_points (id, src_id, timestamp , data )
(1 , 1 , 2024-01-01 00:00:00, {"measurement_a": 100, "measurement_b": 6.8, ... })
(2 , 2 , 2024-01-01 00:00:00, {"measurement_a": 55 , "measurement_b": 0.1, ... })
任何帮助是极大的赞赏!
我推荐选项 1。
选项 2 的问题在于,PostgreSQL 中每个表行除了数据之外还有至少 23 个字节的开销,因此表会变得更大。
选项 3 可能比选项 2 更差,
src_id
而且timestamp
很小,因此就存储空间而言,与选项 2 相比,您不会获得太大的好处。如果您有数百个测量值,则可以考虑选项 4 的解决方案。
我将使用 MQTT 术语:“topic”代表传感器的唯一标识符(例如 src_id、measurement_id),“timestamp”显然是,“value”是测量本身。
与时间序列一样,我假设您需要绘制数据图、生成报告并计算某个传感器在一段时间内的值的汇总。实现此操作速度的最佳方法(事实上,唯一方法)是具有良好的引用局部性,即按(主题、时间戳)的顺序在磁盘上组织表格。
完成后,检索 WHERE topic=... AND timestamp BETWEEN ... AND ... ORDER BY timestamp 的行
只需要顺序读取,不需要到处随机读取来抓取每一行
不需要排序,因为行已经按照要求的顺序排列
选项 1:平面表将是一场维护的噩梦,特别是当您添加具有与其他测量类型不同的新源时。
选项 2:键值对是经典的做法,每个人都这么做,主要是因为它有效。
Postgres 不实现自动聚类表,但它具有仅索引扫描,因此 Postgres 解决方案是使用覆盖索引(主题、时间戳、值),该索引将按正确顺序组织并满足要求。上述查询将是快速的仅索引扫描,并且表本身可能永远不会被读取。
选项 3:两个表不允许按(主题、时间戳)对数据进行聚类,因此在获取时间戳范围时需要随机读取,速度会很慢。
选项 4:jsonb 可能有用。如果要将行打包到 jsonb 中以节省空间,您也可以改用 hstore。要完全实现这一点,您应该使用尽可能短的键,存储在单独的表中,这样“measurement_a”就变成了“1”之类的东西。这不能通过外键来强制执行。缺点是 jsonb 有点慢,即使您只需要一个测量值,它也需要读取所有测量值的数据,但总表大小会更小。我会对这一点说“嗯”。
请注意,您不需要单独的主键:(主题,时间戳)是唯一的,并且不为空,因此它可以是一个有用的主键。除非您想允许两个不同的测量具有相同的(主题,时间戳),但这会有点令人惊讶。
所以我会选择带有覆盖索引的选项 2。
MySQL/InnoDB 或其他支持索引组织表的数据库将使存储需求减半,因为它不需要存储表和索引,因为索引就是表。
我还建议尝试一个专门用于时间序列的数据库。有好几个。我正在使用 Clickhouse,所以我不会评论其他的。
这个想法是,如果你在时间序列的上下文中删除你实际上不需要的功能,比如更新、交易等,那么你可以获得更低的开销。
缺点:
优点:
索引组织表按正确顺序存储行,解决了引用局部性问题
列式存储将一列的所有值放在同一个位置,从而实现相当好的数据压缩。
例如,主题按顺序存储,这会导致相同值的长序列压缩到几乎为零。时间戳单调增加,因此增量压缩效果很好。如果值不嘈杂,您通常也会得到长常量序列。在我的时间序列数据上,我获得了大约 11 倍的压缩,即每行存储大约 1 个字节。因此,更多的表适合缓存。
在 22 亿行表中对 2 亿行的主题执行“每天的 max(value)”之类的查询需要 1.5 秒。这对于绘图非常有用。如果太慢,它还支持自动聚合物化视图。
这取决于您计划如何处理数据。
说实话,20 个测量值并不是很多列。每分钟读取 1 次并不算多,而且表中的数十亿行并不比数百行多(来自一位处理过如此大表的人)可怕。事实上,每分钟读取 1 次意味着每台设备每年只有 500,000 行。因此,您必须至少同时运行 2,000 台设备才能在一年内实现您提到的数十亿行。
选项1
无论如何,常规的“平面”表可以很好地处理该数量级的行,甚至可以处理数万亿行。
如果您的用例涉及混合操作、计算或聚合您计划存储的一些测量值,那么我倾向于选择选项 1,因为它将为您提供最大的灵活性,以查询其原生数据类型的测量值。它还允许数据库系统根据这些列的数据及其类型分别创建适当的统计数据,这有助于查询规划器在制定有效的查询计划来执行上述查询时做出更好的决策。
有多种方式可以最少化地处理表格模式的改变,例如添加新的测量值。
选项 4
如果您打算仅使用数据库来按原样存储数据,并按原样检索数据,然后将其显示在其他地方,那么这也可能是不错的选择。与选项 1 相比,它的优势在于,随着更多测量的添加等,您不必真正管理任何架构更改。(虽然在选项 1 中管理起来确实非常简单,所以在我看来,这并不是什么问题。)
对 JSON 进行装箱和拆箱可能会产生一些可以忽略不计的开销,但根据您一次检索的行数以及您如何使用它们,这可能并不重要。
选项 2
与 bobflux 的建议不同,我不会尝试将键值对存储在 RDBMS 中,因为 RDBMS 不是键值存储。这样做会不规范,并且会产生一些缺点,例如不正确的统计数据会影响性能,并且如果您需要对数据进行操作、计算或聚合,查询会变得更加困难。
我认为根本不需要考虑这个选项,但如果你最终想要走这条路,那么请至少切换到键值存储数据库系统,这样你就可以从设计用于处理该结构的数据库引擎中受益。