我正在设计一个 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, ... })
任何帮助是极大的赞赏!