收集时间相关数据，如物联网或可观察性指标、事件、日志或类似数据集，会导致大量数据被拆分为许多不同的系列。
我在之前的初创公司学到的许多经验教训之一是，了解每个数据系列的相应属性至关重要，并且必须能够快速获取这些属性以满足许多用例。
在最佳实践系列的前两期[窄表、中表或宽表布局和单个表与多个表]中，我们探讨了如何设计超表。现在，我们想要开始研究如何快速获取这些信息，从为元数据和系列查找创建单独的表开始。
我们将涵盖以下最佳实践
查询元信息
引用超表数据
在元数据表中进一步指定系列
这些元数据表的有趣之处在于，我们可以轻松快速地解析特定用户和时间范围内的所有可用数据系列。我们还可以呈现重叠系列，这些系列仍然可以是不同的，例如同一设备中的两个温度传感器（是的，这对于不同房间中具有外部、有线连接传感器的设备可能是有意义的——亲身经历过 😉）。
首先，这是我在之前的初创公司想出来的一个术语。我们的用例既简单又复杂。我们从大量设备中收集物联网数据，但设备可以随时更换其传感器（它们是可插拔的）。
系列元数据表通常用于时间序列数据库，它是一种特殊的表，用于存储有关数据系列的元数据。这些元数据可能包括系列名称、标签、系列的开始和结束时间、度量单位或任何其他描述性细节，这些细节可以为系列提供上下文。
对于处理时间序列数据的开发人员来说，系列元数据表有助于提高查询效率和数据组织。通过将这些元数据与实际时间序列数据分开存储，您可以使数据库结构更紧凑，并使管理和搜索数据变得更加容易。它还提供了一种使用额外上下文注释数据的方法，这在分析或可视化数据时非常有用。
一般来说，元数据表存储有关数据库中其他数据的详细信息（元数据）。这可能是有关数据结构的信息，例如表中列的名称和类型，或者可能是有关数据本身的更多描述性信息，例如数据的来源、上次更新时间等。
因此，元数据表和系列元数据表之间的主要区别在于它们存储的信息：通用元数据表可以存储有关任何类型数据的详细信息，而系列元数据表专门存储有关时间序列数据的详细信息。这两个表的目的是为数据库中存储的数据提供额外的上下文和组织，这可以使管理和分析数据变得更加容易。
在时间序列数据库（如 TimescaleDB）中，您可能将实际时间序列数据存储在Timescale 超表（本质上是一个被分成较小分区（称为“块”）的分区表）中，然后使用系列元数据表存储有关该数据的其他信息。
在本系列的前两篇博文中，我们已经了解了表布局可能是什么样子，以及如何实现 GDPR 或类似法规的系统。现在这已经变得“容易”了。设计中更复杂的部分是告诉用户在任何给定的时间在任何给定的建筑中有哪些数据系列可用。
为了稍微澄清一下，这意味着单个建筑可能有多个设备配备温度传感器，比方说四个。我们还可以假设至少其中一个设备在给定时间内更换了温度传感器以修复损坏。
我们的一组可用数据系列可能如下例所示
postgres=# SELECT * FROM series_metadata;
series_id | device_id | sensor_id | ts_start | ts_end | ts_last_seen
-----------+-----------+-----------+------------------------+------------------------+------------------------
1 | 1 | 1 | 2023-01-01 00:00:00+00 | | 2023-02-01 00:00:00+00
2 | 2 | 2 | 2022-12-13 22:00:00+00 | 2023-02-01 20:00:00+00 | 2023-02-01 20:00:00+00
3 | 3 | 3 | 2023-01-15 17:00:00+00 | 2023-01-17 09:00:00+00 | 2023-01-17 09:00:00+00
4 | 3 | 4 | 2023-01-17 09:10:00+00 | | 2023-02-01 00:00:00+00
(4 rows)
我们可以看到，我们有三个设备（device_id）和四个传感器（sensor_id）。接下来，两列（ts_start 和 ts_end）定义了每个数据系列的可用时间范围。ts_end 中的空值表示开放系列，即预期会收到新数据的持续系列。
因此，我们可以看到，第三个设备的传感器仅在第一个系列开始后的两天被更换。最后一列（ts_last_seen）查找特定数据系列的最后一行（时间戳）。我们稍后会回到这个问题。
在我们的示例中，我们想展示客户和建筑物的所有可用数据系列。也就是说，我们需要另外两个表。
为了简单起见，我们使用一个建筑表（具有 customer_id 字段和设备分配表）来表示建筑物中的设备。
postgres=# \d buildings
Table "public.buildings"
Column | Type | Collation | Nullable | Default
-------------+--------+-----------+----------+------------------------------------------------
building_id | bigint | | not null | nextval('buildings_building_id_seq'::regclass)
customer_id | bigint | | not null |
Indexes:
"buildings_pk" PRIMARY KEY, btree (building_id)postgres=# \d assignment_buildings_devices
Table "public.assignment_buildings_devices"
Column | Type | Collation | Nullable | Default
---------------------------------+--------+-----------+----------+--------------------------------------------------------------------------------------
assignment_buildings_devices_id | bigint | | not null | nextval('assignment_buildings_devices_assignment_buildings_devices_i_seq'::regclass)
building_id | bigint | | |
device_id | bigint | | |
Indexes:
"assignment_buildings_devices_pk" PRIMARY KEY, btree (assignment_buildings_devices_id)
"assignment_buildings_devices_building_id_device_id_uindex" UNIQUE, btree (building_id, device_id)
"assignment_buildings_devices_device_id_uindex" UNIQUE, btree (device_id)我省略了这两个表中的实际数据，但提供了一个简短的概述，作为组合表。
客户 | 建筑物 | 设备 |
1 | 1 | 1 |
2 | 2 | 2, 3 |
通过这三个表，我们可以使用一个简单的查询搜索所有可用系列，并且只获取客户 2 的可用数据系列。
postgres=# SELECT sm.*
postgres-# FROM series_metadata sm
postgres-# RIGHT JOIN assignment_buildings_devices abd ON sm.device_id = abd.device_id
postgres-# RIGHT JOIN buildings b ON abd.building_id = b.building_id
postgres-# WHERE b.customer_id = 2;
series_id | device_id | sensor_id | ts_start | ts_end | ts_last_seen
-----------+-----------+-----------+------------------------+------------------------+------------------------
2 | 2 | 2 | 2022-12-13 22:00:00+00 | 2023-02-01 20:00:00+00 | 2023-02-01 20:00:00+00
3 | 3 | 3 | 2023-01-15 17:00:00+00 | 2023-01-17 09:00:00+00 | 2023-01-17 09:00:00+00
4 | 3 | 4 | 2023-01-17 09:10:00+00 | | 2023-02-01 00:00:00+00
(3 rows)
这已经很了不起。另一种方法是遍历存储在超表中的所有行，并尝试动态地找到这些值。这听起来不是一个好方法。
无论如何，这只是初始请求的一部分。
第二部分是在特定时间范围内查找数据系列。而这正是这种方法真正发挥作用的地方。我们可以使用一个小型的辅助函数（我强烈推荐）和 PostgreSQL tzrange 数据类型的魔力快速过滤系列。
CREATE OR REPLACE FUNCTION series_in_range(
series series_metadata,
rangeStart timestamptz, rangeEnd timestamptz
)
RETURNS bool
LANGUAGE SQL
PARALLEL SAFE
STABLE
AS $$
SELECT NOT isempty(
tstzrange(rangeStart, rangeEnd) *
tstzrange(series.ts_start, series.ts_end, '[]')
);
$$;
此函数检查给定的序列候选是否在请求的时间范围内。需要注意的是，该函数被标记为语言 sql，以便查询规划器可以选择与我们构建的查询完全优化它。
有了这些准备，我们现在可以将上面的查询扩展到一个潜在的时间范围——比如2023-01-18之后的所有内容，它只返回匹配的行。它还会自动将 ts_end 列中为 NULL 的值视为开放式。
postgres=# SELECT sm.*
postgres-# FROM series_metadata sm
postgres-# RIGHT JOIN assignment_buildings_devices abd ON sm.device_id = abd.device_id
postgres-# RIGHT JOIN buildings b ON abd.building_id = b.building_id
postgres-# WHERE b.customer_id = 2
postgres-# AND series_in_range(sm, '2023-01-18', now());
series_id | device_id | sensor_id | ts_start | ts_end | ts_last_seen
-----------+-----------+-----------+------------------------+------------------------+------------------------
2 | 2 | 2 | 2022-12-13 22:00:00+00 | 2023-02-01 20:00:00+00 | 2023-02-01 20:00:00+00
4 | 3 | 4 | 2023-01-17 09:10:00+00 | | 2023-02-01 00:00:00+00
(2 rows)为了简化以后的查询，我在上面的查询中添加了另一个小技巧，将开放式和封闭式解析为实际的时间戳，这些时间戳可以稍后传递回数据库，从而更易于查询。我将其放入一个函数中
CREATE OR REPLACE FUNCTION series_in_range_by_customer(
customer_id int8,
rangeStart timestamptz, rangeEnd timestamptz
)
RETURNS SETOF series_metadata
LANGUAGE SQL
PARALLEL SAFE
STABLE
AS $$
SELECT sm.series_id, sm.device_id, sm.sensor_id,
greatest(ts_start, rangeStart) AS ts_start,
least(sm.ts_end, rangeEnd) AS ts_end,
sm.ts_last_seen
FROM series_metadata sm
RIGHT JOIN assignment_buildings_devices abd
ON sm.device_id = abd.device_id
RIGHT JOIN buildings b
ON abd.building_id = b.building_id
WHERE b.customer_id = series_in_range_by_customer.customer_id
AND series_in_range(sm, rangeStart, rangeEnd);
$$;
同样，确保此函数被定义为语言 sql，以便查询规划器可以完全在适当位置进行优化。
结果略有变化，将每个数据序列的开始时间和结束时间交换，以与我们请求的时间窗口保持一致。
postgres=# SELECT * FROM series_in_range_by_customer(2, '2023-01-18', now());
series_id | device_id | sensor_id | ts_start | ts_end | ts_last_seen
-----------+-----------+-----------+------------------------+-------------------------------+------------------------
2 | 2 | 2 | 2023-01-18 00:00:00+00 | 2023-02-01 20:00:00+00 | 2023-02-01 20:00:00+00
4 | 3 | 4 | 2023-01-18 00:00:00+00 | 2023-03-31 08:12:44.033446+00 | 2023-02-01 00:00:00+00
(2 rows)
现在我们知道了外部定义是什么样子，我们需要理解如何引用实际的超表值。在不深入超表的实际布局的情况下（如果你对此感兴趣，请在 窄表、中表或宽表布局 文章中找到更多信息），让我们使用中等表布局样式。
我们的超表可能看起来像下面这样
postgres=# \d temperatures
Table "public.temperatures"
Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+-------------------------------------------------
metric_id | bigint | | not null | nextval('temperatures_metric_id_seq'::regclass)
series_id | bigint | | not null |
recorded_at | timestamp with time zone | | not null |
value | double precision | | not null |
Indexes:
"temperatures_pk" PRIMARY KEY, btree (metric_id)
"temperatures_series_id_recorded_at_uindex" UNIQUE, btree (series_id DESC, recorded_at DESC)
客户 | 建筑物 | 设备 |
1 | 1 | 1 |
2 | 2 | 2, 3 |
在大多数情况下，您需要最新的序列和最新的数据。也就是说，根据最常见的查询模式顺序构建索引是一个真正的加速魔法！
这些类型的外部序列定义的另一个非常有趣的元素是，我们可以使用额外的列扩展 series_metadata，帮助我们进一步指定序列。可能性是无限的。一个额外的元信息是 ts_last_seen 列，每次向我们的数据超表写入新行时都会更新它。
在我的初创公司 clevabit，我们使用它来快速找到所有在给定时间窗口内不活动后可以关闭的序列（对我们来说，是在七天之后）。为了实现这一点，我们每小时运行一个小型的 cron 作业，选择所有 ts_last_seen 小于 now() - interval '7 days' 且 ts_end 为 NULL 的序列，并使用 ts_end 设置为 ts_last_seen 更新该行。
另一种方法可以是提供有关序列存储的指标的信息，例如“cpu_usage”或“mem_free”。如果您选择多表方法，它还可以提供有关序列存储在哪个超表中的信息。如前所述，可能性是广泛的。
您可能希望使用这种设置的原因还有很多。让我们将它们视为它们可能带来的问题或优化。
设备在时间范围 [x, y] 内是否有特定的传感器？
设备在时间范围 [x, y] 内是否有特定的指标？
建筑物在时间范围 [x, y] 内是否有特定的指标？
提供有关时间范围 [x, y] 内类似的平行但不同的数据序列的信息。
通过了解每个序列的精确可用时间范围来优化查询，而不是只使用提供搜索窗口。
减少元数据空间，因为上述方法的替代方法是在超表中的每一行存储所有必要的元数据，这会大量重复信息。
快速查找可用数据序列时间范围。
查找设备和传感器的先前分配（如果您有使用案例，例如，传感器演示单元被发送给不同的客户进行测试）。
最后，您一直等待的问题，您应该这样做吗？
我的答案是：是的，去做吧。
只有在您对磁盘空间节省或快速查找不同数据序列的可能性不感兴趣的情况下，才会有极少数使用案例。为便于使用提供一个表也没有意义，就像我在之前的几集中所做的那样。
为了获得更多影响性能的表优化，我建议您查看 PostgreSQL 性能调优：设计和实现您的数据库模式.
在结束语中，我希望您发现这一部分很有趣。如果您现在迫不及待地想尝试一下，请创建一个免费的 Timescale 帐户或安装 TimescaleDB 本地.