了解如何在大型时间序列数据集中查询设备的新时间戳和数据，通常是许多应用程序开发人员面临的挑战。我们研究数据，确定适当的模式，并创建应该使查询快速返回的索引。当查询没有我们预期的那么快时，很容易混淆，因为 PostgreSQL 中的索引应该帮助您的查询快速返回 - 对吗？在大多数情况下，这个问题的答案肯定是“正确的”。使用适当的索引，PostgreSQL在检索查询数据方面通常非常有效。在这篇文章中总是有一些我们没有时间讨论的细微差别（不要创建太多索引，确保统计数据保持新等），但一般来说，正确的索引会显着提高 SQL 数据库的查询性能，包括 PostgreSQL。在我们深入研究如何使用索引在大型时间序列数据库中有效地查找特定记录之前，我想确保我们讨论的是同一件事。在本文中，所有对索引的引用都特指B-tree 索引。这些是所有主要 OLTP 数据库支持的常见的索引，它们非常擅长跨大小表定位特定的数据行。PostgreSQL 实际上支持许多不同的索引类型，可以帮助各种类型的查询和数据（包括以时间戳为中心的数据），但从这里开始，我们只讨论 B-tree 索引。
在我们的 TimescaleDB Slack 社区频道和 StackOverflow 等其他开发人员论坛（示例）中，开发人员经常想知道为什么在 PostgreSQL 中查询新值很慢，即使看起来存在正确的索引以使查询“快速”？答案在于PostgreSQL查询规划器的工作方式。它并不总是按照您所期望的方式使用索引，我们将在下文中讨论。为了演示PostgreSQL如何在大型时间序列表上使用索引，让我们使用一组虚构的数据。对于这些示例查询，假设我们的应用程序正在跟踪一个卡车车队，只要卡车有一个单元连接，传感器就会每分钟报告几次数据。有时卡车会丢失信号，导致数据在几小时或几天后发送。尽管该应用程序肯定会涉及更多，并且具有用于跟踪时间序列和业务相关数据的更复杂的模式，但让我们关注其中两个表。该表跟踪作为车队一部分的每辆卡车。即使对于一个非常大的公司，这个表通常也只包含几万行。
|
卡车
编号
|
制作
|
模型
|
重量
等级
|
date_acquired |
活动
状态
|
1
|
福特
|
单人卧铺
|
小号
|
2018-3-14
|
true
|
| 2 |
特斯拉
|
双人卧铺 |
特大号
|
2019-2-18
|
false
|
...
|
...
|
...
|
...
|
...
|
... |
对于下面的查询，我们将假设该表有大约 10,000 辆卡车，其中大部分当前处于活动状态并每分钟记录几次数据。读取hypertable存储了每辆卡车随时间传送的所有数据。数据通常在一分钟内发送几次，但旧的数据可能会在卡车失去与手机服务的连接或发射机故障时到达。对于本例，我们将展示一个宽格式的表模式，并只展示几列数据以保持简单。许多物联网应用程序为每组读数存储多种类型的数据点。当您创建 TimescaleDB 元数据表，将自动创建时间戳列上的索引，除非您明确告诉create_hypertable()函数不要这样做。对于该truck_reading表，默认索引应类似于：CREATE INDEX ix_ts ON truck_reading (ts DESC);这个索引(或者至少是一个首先使用时间列的复合索引)对于涉及到时间的基本查询都是必要的，强烈推荐用于hypertable chunk管理。像MIN(ts)或MAX(ts)这样单独涉及时间的查询可以很容易地从这个索引中得到满足。但是，如果我们想知道特定卡车的小或大读数，PostgreSQL 将无法快速找到该信息。考虑以下搜索特定卡车新读数的查询：SELECT * FROM truck_reading WHERE truck_id = 1234 ORDER BY ts DESC LIMIT 1;如果truck_reading表只有默认的时间戳索引（ix_ts如上），那么 PostgreSQL 没有有效的方法来获取这辆特定卡车的新数据行。相反，它必须从头开始读取索引（近的时间戳首先基于索引顺序）并检查每一行以查看它是否1234包含truck_id.如果这辆卡车近报告了，PostgreSQL 多只需要读取几千行，查询仍然“快”。如果卡车在几个小时或几天内没有记录数据，PostgreSQL 可能必须读取数十万或数百万行数据，然后才能找到truck_id = 1234.为了证明这一点，我们创建了一个包含约 2000 万行数据的样本数据集（1 周，10,000 辆卡车），然后删除了近 12 小时的truck_id = 1234.在下面的 EXPLAIN 输出中，我们可以看到 PostgreSQL 必须扫描整个索引并过滤掉超过 153 万行与我们正在搜索的 `truck_id` 不匹配的行。更令人担忧的是 PostgreSQL 必须处理的数据量才能正确检索我们要求的一行数据 -大约 184MB 的数据！（23168 个缓冲区 x 每个缓冲区 8kb）QUERY PLAN
------------------------------------------
Limit (cost=0.44..289.59 rows=1 width=52) (actual time=189.343..189.344 rows=1 loops=1)
Buffers: shared hit=23168
->Index Scan using ix_ts on truck_reading
(cost=0.44..627742.58 rows=2171 width=52)
(actual time=189.341..189.341 rows=1 loops=1)|
Filter: (truck_id = 1234)
Rows Removed by Filter: 1532532
Buffers: shared hit=23168
Planning: Buffers: shared hit=5
Planning Time: 0.116 ms
Execution Time: 189.364 ms
如果您的应用程序必须为每个查询做这么多工作，那么随着数据的增长，它很快就会成为简单查询的瓶颈。因此，对于我们的应用程序的典型查询模式，我们必须拥有正确的索引。在此示例中（以及在许多实际应用程序中），我们至少应该创建一个包含truck_id和的其他索引ts。这将允许更有效地搜索基于时间的关于特定卡车的查询。示例索引如下所示：CREATE INDEX ix_truck_id_ts ON truck_reading (truck_id, ts DESC);通过创建此索引，PostgreSQL 可以非常快速地找到特定卡车的新记录，无论它是几秒钟前或几周前报告的数据。使用与上述相同的数据集，返回truck_id = 1234 12小时前数据点的相同查询仅读取40kb 数据！由于我们创建了适当的索引，因此必须读取的数据减少了 ~ 4600 倍，更不用说毫秒级的执行时间了！QUERY PLAN
----------------------------------------
Limit (cost=0.56..1.68 rows=1 width=52) (actual time=0.015..0.015 rows=1 loops=1)
Buffers: shared hit=5
-> Index Scan using ix_truck_id_ts on truck_reading (cost=0.56..2425.55 rows=2171 width=52) (actual time=0.014..0.014 rows=1 loops=1)
Index Cond: (truck_id = 1234)
Buffers: shared hit=5
Planning:
Buffers: shared hit=5
Planning Time: 0.117 ms
Execution Time: 0.028 ms
需要明确的是，两个查询都使用索引来搜索行。不同之处在于如何使用索引来查找我们想要的数据。个查询必须过滤元组，因为只有时间戳是索引的一部分。过滤发生在从磁盘读取元组之后，这意味着更多的工作只是试图找到正确的数据。相反，第二个查询使用索引的两个部分 (truck_id和ts) 作为索引条件的一部分。这意味着只有与约束匹配的行才会从磁盘中读取。在这种情况下，这是一个非常小的数字，查询速度要快得多！不幸的是，即使有这两个目标索引，也有一些常见的时间序列 SQL 查询不会像大多数开发人员期望的那样执行。
truck_id开放式查询在不指定特定时间范围或设备约束（在我们的示例中）的情况下查找数据点（个、后一个、近的）。这些类型的查询给计划者留下了很少的选择，因此它假设它必须在计划时扫描整个索引。这可能不是真的，但 PostgreSQL 在执行查询并开始查找数据之前无法真正知道。当表被分区时，这尤其困难，因为实际的索引是与每个表分区独立存储的。因此，整个表没有全局索引来标识truck_id分区中是否存在特定（在我们的例子中）。再一次，当 PostgreSQL 规划器在规划阶段没有足够的信息时，它假定每个分区都需要被查询，这通常会导致规划时间增加。考虑如下查询，它要求对特定的早阅读truck_id：SELECT * FROM truck_reading WHERE truck_id=1234 ORDER BY ts LIMIT 1;有了两个索引（(ts DESC)和(truck_id, ts DESC)），感觉这应该是一个快速查询。但是因为元数据表是按时分区的，所以规划器初假设它必须扫描每个块。如果您有很多分区，则计划时间会更长。如果truck_reading表正在积极接收新数据，查询的执行仍然会“快速”，因为答案可能会在个块中找到并快速返回。但如果truck_id=1234从未报告任何数据或已离线数周，PostgreSQL 将不得不计划然后扫描每个块的索引。该查询将使用每个分区上的复合索引来快速确定卡车没有记录，但它仍然需要花时间来计划和执行查询。相反，我们希望尽可能避免做不必要的工作，并避免这种查询反模式的可能性。
许多查询也可能受到基数增加的负面影响，随着数据量的增长和跟踪的单个项目的增加而变得更慢。下面的选项 1-4 是很好的查询示例，它们在中小型数据集上表现良好，但通常会随着数量和基数的增加而变慢。truck_id这些查询尝试利用元数据表上的索引来“逐步”遍历时序表。但是，随着需要查询的项目越来越多，迭代通常会变慢，因为索引太大而无法有效地放入内存中，导致 PostgreSQL 频繁地与磁盘交换数据。了解这两种类型的查询可能不会在每种情况下都表现得很好，让我们检查五种不同的方法来获取时间序列表中每个项目的新记录。在大多数情况下，这些选项中的至少一个将适用于您的数据。需要明确的是，两个查询都使用索引来搜索行。不同之处在于如何使用索引来查找我们想要的数据。个查询必须过滤元组，因为只有时间戳是索引的一部分。过滤发生在从磁盘读取元组之后，这意味着更多的工作只是试图找到正确的数据。相反，第二个查询使用索引的两个部分 (truck_id和ts) 作为索引条件的一部分。这意味着只有与约束匹配的行才会从磁盘中读取。在这种情况下，这是一个非常小的数字，查询速度要快得多！不幸的是，即使有这两个目标索引，也有一些常见的时间序列 ，SQL 查询不会像大多数开发人员期望的那样执行。
当我们跳入下面的 SQL 示例时，一个简短的警告词。始终记住，您的开发数据库不太可能具有与生产数据库相同的容量、基数和事务吞吐量。我们在下面展示的任何一个示例查询都可能在较小的、不太活跃的数据库上表现得非常好，只是在生产中表现得比预期的要差。好在尽可能与生产环境相似的环境中进行测试。如何做到这一点超出了本文的范围，但一些选项可能是：
将一键式数据库分叉与您的Timescale Cloud实例一起使用，轻松制作生产副本以进行测试和学习。通常使用尽可能接近生产的数据！
将您的生产数据库备份和恢复到批准的位置，并对数据进行匿名化，保持相似的基数和行统计信息。ANALYZE任何数据更改后始终显示该表。
考虑重用您的模式并生成大量大容量、高基数的示例数据generate_series()
（可能使用我们系列中关于在 PostgreSQL 内部生成更真实的示例数据的一些想法）。
无论您选择哪种方法，请始终记住，具有 100 条项目的 100 万行时间序列数据的数据库与每隔几秒报告 10,000 个项目的具有 100 亿行时间序列数据的数据库的行为将大不相同。既然我们已经讨论了索引如何帮助我们查找数据并回顾了一些可能比平时慢的查询模式，现在是时候编写一些 SQL 并讨论何时可能适合使用每个选项。SQL 是一种强大的语言。不幸的是，每个允许用 SQL 编写查询的数据库通常都有稍微不同的功能来完成类似的工作，或者根本不支持 SQL 标准，否则这些标准将允许像我们一直在讨论的那样进行高效的“后一点”查询。但是，在几乎所有支持 SQL 查询语言的数据库中，您都可以运行此查询以获取卡车记录数据的近时间。在大多数情况下，这在大型数据集上表现不佳，因为该GROUP BY子句会阻止使用索引。SELECT max(time) FROM truck_reading GROUP BY truck_id;由于 PostgreSQL 中不会使用索引，因此不建议将这种方法用于大容量/高基数据集。但是，即使效率不高，它也会得到您期望的结果。如果您有这样的查询，请考虑下面列出的其他选项之一如何更适合您的查询模式。给任何 PostgreSQL 数据库开发人员的简单的建议之一就是学习如何使用 LATERAL JOIN。在其他一些数据库引擎（如 SQL Server）中，这些被称为 APPLY 命令，但它们基本上做同样的事情 - 为外部查询产生的每一行运行内部查询。因为它是一个 JOIN，所以内部查询可以利用来自外部查询的值。（虽然这类似于相关子查询，但它不是一回事。）当您作为开发人员或管理员大致了解外部查询将返回多少行时，LATERAL JOIN 是一个很好的选择。对于几百或几千行，只要有正确的索引，这种模式很可能会很快返回您的“近”记录。 SELECT * FROM truck_reading
WHERE truck_id = t.truck_id ORDER BY t.truck_id DESC;LATERAL JOIN 查询的方便之处在于，可以对外部查询应用额外的过滤来识别要检索数据的特定项目。在大多数情况下，关系业务数据 ( trucks) 将是一个较小的表，具有更快的查询时间。分页也可以更有效地应用于较小的表（即OFFSET 500 LIMIT 100），这进一步减少了内部查询需要执行的总工作量。不幸的是，LATERAL JOIN 查询的一个缺点是它可能至少以两种方式容易受到我们上面讨论的高基数问题的影响。首先，如果外部查询返回的项目比内部表的数据多得多，则此查询将循环遍历内部表，做更多的工作。例如，如果该truck表有 10,000 个卡车条目，但其中只有 1,000 个曾经报告过读数，则查询将循环遍历内部查询，比它需要的多 10 倍。选项 3：TimescaleDB SkipScan免责声明：此方法仅在安装 TimescaleDB 扩展时有效。如果您还没有使用它，您可以在我们的文档页面中找到更多信息。LATERAL JOIN 是处理迭代查询时手头上的一个很好的工具。然而，正如我们刚刚讨论的那样，当迭代外部查询的项目时，它们并不总是好的选择，这会导致内部查询经常执行，寻找不存在的数据。这时候使用阅读表本身来获取不同的项目和相关数据可能是有利的。特别是，当我们要查询某个时间段内（例如近 24 小时）上报数据的卡车时，这很有帮助。虽然我们可以在上面的内部查询中添加一个过滤器 ( WHERE ts > now() - INTERVAL '24 hours')，但我们仍然必须遍历 every truck_id，其中一些可能在过去 24 小时内没有报告数据。因为我们已经创建了ix_truck_id_ts上面按truck_idand排序的索引，ts DESC所以许多 PostgreSQL 开发人员尝试的常用方法是使用DISTINCT ONPostgreSQL 查询。SELECT DISTINCT ON (truck_id) * WHERE ts > now() - INTERVAL '24 hours' ORDER BY truck_id, ts DESC;如果您在没有 安装TimescaleDB的情况下尝试此操作，它的性能将不会很好-即使我们的索引看起来数据排序正确且易于“跳转”！这是因为，从 PostgreSQL 14 开始，在查询执行阶段没有可以“遍历”索引以查找特定键的每个实例的功能。相反，PostgreSQL 本质上是读取所有数据，按ON列分组，然后过滤掉除行以外的所有数据（基于顺序）。但是，安装 TimescaleDB 扩展（2.3 或更高版本）后，只要存在正确的索引并且与查询的顺序相同，查询的DISTINCT ON工作效率就会更高。这是因为 TimescaleDB 扩展添加了一个名为“SkipScan ”的新查询节点，一旦找到另一个键值，它将开始按顺序扫描具有下一个键值的索引。(SkipScan) 好的部分之一是它适用于任何具有 B 树索引的 PostgreSQL 表。它不一定是TimescaleDB 元数据表！索引的使用方式有一些细微差别，所有这些都在上面链接的博客文章中进行了概述。如果您不（或不能）安装 TimescaleDB 扩展，仍然有一种方法可以查询truck_reading表以有效地返回每个truck_id.在 PostgreSQL Wiki 上有一个专门介绍 Loose Index Scan的页面。它演示了一种使用递归 CTE 查询来基本上完成 TimescaleDB (SkipScan) 节点的工作的方法。编写起来几乎没有那么简单，而且返回多行也更困难（它与 DISTINCT 查询不同），但它确实提供了一种更有效地使用索引为每个项目检索一行的方法。这种方法的大缺点是使用递归 CTE 返回多列数据要困难得多（而且在大多数情况下，根本不可能返回多行）。因此，虽然一些开发人员将此称为跳过扫描查询，但它并不容易让您检索大容量表的所有行数据，例如 TimescaleDB 提供的 (SkipScan) 查询节点。/*
* Loose index scan via https://wiki.postgresql.org/wiki/Loose_indexscan
*/SELECT min(ts) AS time FROM truck_reading SELECT (SELECT min(ts) FROM truck_reading WHERE ts > t.ts) FROM t WHERE t.ts IS NOT NULL ) SELECT ts FROM t WHERE ts IS NOT NULL SELECT null WHERE EXISTS(SELECT 1 FROM truck_reading WHERE ts IS NULL);有时，特别是对于大型、高基数据集，上述选项对于日常操作来说不够高效。随着数据量和基数的增长，查询所有项目的后读数，或近 24 小时内未报告值的设备将无法满足您的期望。在这种情况下，更好的选择可能是维护一个表，该表存储每个设备的后读数，因为它被插入到原始时间序列表中，以便您的应用程序可以查询一个小得多的数据集以获取新值。为了跟踪和更新日志表，我们将在原始数据（超）表上创建一个数据库触发器。“等一下！你刚才是说我们要创建一个数据库触发器吗？不是每个人都说你不应该使用它们吗？”这是真的。触发器在 SQL 世界中经常得到不好的评价，老实说，这通常是有道理的。正确使用和正确实现，数据库触发器可以非常有用，并且对 SELECT 性能的影响小。插入和更新性能会受到影响，因为每个事务都需要做更多的工作。性能影响可能会也可能不会影响您的应用程序，因此测试是必不可少的。下面的 SQL 提供了如何实现这种日志记录的小示例。关于如何为您的特定应用程序好地实施此选项，有很多考虑因素。彻底测试您添加到数据库中数据处理的任何新流程。创建一个表来存储新数据。如果您只想存储每辆卡车读数的新时间戳，这可以轻松地将值插入到truck表上的新字段中将表的 FILLFACTOR 更改为 90%，因为它将是 UPDATE 重的创建一个触发器函数，如果卡车不存在该行，则插入该行；如果该卡车的行在表中已有条目，则更新值（ON CONFLICT）在数据元数据表上启用触发器。这种方法的关键是只跟踪必要的内容，减少 PostgreSQL 作为摄取原始数据的整体事务的一部分必须做的工作量。如果您的应用程序每秒更新 100,000 台设备的值（并且您正在跟踪 50 列数据），则可能需要使用不同的触发方法。如果这是您经常看到的那种数据量，我们假设您的团队中有一位经验丰富的 PostgreSQL DBA 来帮助管理和维护您的应用程序数据库 - 并帮助您确定日志记录表方法是否适用于可用的服务器资源。/*
* The logging table alternative. The PRIMARY KEY will create an
* index on the truck_id column to make querying for specific trucks more efficient
*/
CREATE TABLE truck_log (
truck_id int PRIMARY KEY REFERENCES trucks (truck_id),
milage int,
fuel int,
latitude float8,
longitude float8
);
/*
* Because the table will mostly be UPDATE heavy, a slightly reduced
* FILLFACTOR can alleviate maintenance contention and reduce
* page bloat on the table.
*/
ALTER TABLE truck_log SET (fillfactor=90);
/*
* This is the trigger function which will be executed for each row
* of an INSERT or UPDATE. Again, YMMV, so test and adjust appropriately
*/
CREATE OR REPLACE FUNCTION create_truck_trigger_fn()
RETURNS TRIGGER LANGUAGE PLPGSQL AS $BODY$
BEGIN
INSERT INTO truck_log VALUES
(NEW.truck_id, NEW.time, NEW.milage, NEW.fuel, NEW.latitude, NEW.longitude)
ON CONFLICT (truck_id) DO UPDATE SET last_time=NEW.time,
milage=NEW.milage,
fuel=NEW.fuel,
latitude=NEW.latitude,
longitude=NEW.longitude;
RETURN NEW;
END
$BODY$;
/*
* With the trigger function created, actually assign it to the truck_reading
* table so that it will execute for each row
*/
CREATE TRIGGER create_truck_trigger
BEFORE INSERT OR UPDATE ON truck_reading
FOR EACH ROW EXECUTE PROCEDURE create_truck_trigger_fn();
有了这些部分，新表将开始接收新的数据行，并在摄取数据时更新后的值。查询这张表将比搜索数亿行高效得多。
|
需要匹配索引 |
受更高基数的影响 |
插入性能可能会受到影响 |
选项 1：分组依据 |
|
X |
|
选项 2：横向连接 |
X |
X |
|
选项 3：TimescaleDB SkipScan |
X |
X |
X（如果需要添加索引） |
选项 4：递归 CTE |
X |
X |
X（如果需要添加索引） |
选项 5：记录表 |
|
|
X |
无论您采用哪种方法，希望这些选项之一将帮助您采取下一步措施来提高应用程序的性能。
原文链接：https://mp.weixin.qq.com/s/tMa5eQ3d_5WY_-2ioNIGHA