PostgreSQL中时间序列记录的高效均匀采样
我一直在开发一个应用程序，它的核心是存储大量数据，这些数据主要是通过使用外键和时间戳字段来组织的。表自己的主键是基于UUID的，将外键与单个记录本身的UUID组合在一起，并且它有一个使用JSONB类型的主数据字段，因为它可以接收任意数据。该表可以看到频繁的、定期的插入和周期性的删除，旧数据会随着时间的推移而变得稀疏，但是对于每个外键，可能会有数万条记录分布在数十万条记录中，或者对于其他外键，可能会有数百万条其他记录。
这一切都非常简单，但是当开始编写从该表生成数据图的代码时，我遇到了一个难题。
如何确保API不会返回太多数据呢？数据点过多只意味着发送的数据比用户可能需要的要多，这也会导致绘图工具不得不处理比它想要的更多的数据，以获得快速、响应的性能。
如何才能高效地查找数据而不运行查询则需要大量的时间呢？在我们的示例中，数据由API返回到基于Reaction的前端，而Snappy应用程序的性能取决于Snappy API的性能。
在应用程序历史的早期，我得出了一个解决方案。如果我对要查询的数据点数量有一个最大上限，比如500个，那么我可以查询与查询匹配的记录总数，然后一个小整数除法会给我一个查询时使用的间隔。
一旦确定了记录计数，就可以计算用于查询记录样本的间隔。
也就是说，如果有5000个数据点，我想要对其中的500个数据点进行采样，那么我需要每10条记录进行一次查询。要找到那个间隔，看起来就像这样：
一旦有了间隔，PostgreSQL就可以使用一种技术来选择该间隔上的记录。ROW_NUMBER()是一个窗口函数，它为结果集中的每一行分配一个序列号。一旦为每条记录分配了一个单调递增的序列号，就可以在WHERE子句中使用该编号。
这招奏效了！当您希望从某个结果集中选择每第n条记录，并且希望让数据库而不是您的应用程序来执行工作时，这是一种可行的通用技术。与将所有数据放入应用程序并使其负责对数据进行排序和修剪不需要的行相比，在数据库中进行这样的数据管理几乎总是更快、占用的资源更少。
这种方法有几个性能问题，当表开始显着增长时，这些问题就会变得明显。
首先，拉计数并不便宜。MySQL维护表的全局记录计数，作为其MyISAM数据格式的一部分。然而，PostgreSQL对其表使用了一种称为多版本并发控制策略，这实质上意味着数据库的不同视图可能会看到不同的行集。因此，没有单一的、简单的记录计数可供它依靠。因此，当您在PostgreSQL中对表中的记录进行计数时，数据库需要实际遍历数据并对所有可见记录进行计数。
如果您只是想估计表中的总行数，有一种方法可以非常便宜地获得：
但是，当您只想计算记录的一个子集，并且该值只是一个估计值时，这是不起作用的。这是查询规划器使用的估计值，因此它通常应该始终在实际值的10%左右，但它不太可能完全匹配，除非表大小更改很少。还有其他计数策略，但它们都有权衡或固有的不准确性，因此对于此用例，仅为了在计算所需的查询间隔时获得要使用的记录计数是无法回避的前期时间和资源成本。
该技术的第二个开销部分是在WHERE子句中结合使用ROW_NUMBER()和模数(%)。这意味着数据库在运行查询时必须遍历每条可能的记录，以便找出哪些记录满足WHERE子句。因此，如果有150000条记录，但一个人只想要其中的500条，那么所有150000条记录仍将被扫描。
这些因素加在一起，使得这种方法对于作为驱动UI的API的一部分特别运行的查询来说变得异常缓慢。
这是一个使用先前技术在真实数据库上进行查询的真实示例，并且此示例的优点在于，它使用的索引(跨数据字段的BTREE索引，因为在生产中，我们将结果限制为具有一种特定类型数据的字段)在我运行此示例时已经预热并缓存在数据库的工作集中，因此此结果是此技术在此数据库上的最佳情况。如果该索引不可用或未使用，考虑到该索引筛选器拒绝了近180,000行，则速度会更慢。这太慢了，不能通过API请求直接触发，因为用户将等待半分钟，才能让数据开始显示在他们的浏览器中。
事实证明，PostgreSQL提供了一个高性能选项来采样表中的随机数据集。可以将TABLESAMPLE子句放在查询的FROM部分中，该查询将对表的子集进行采样。
这将返回大约5%的MyTable行的大致随机集合。如果需要特定的行数，有一个扩展可以提供，即TSM_SYSTEM_ROWS。
这将从表中返回500行的随机集合。可以在使用TABLESAMPLE的查询中使用WHERE子句，以便只选择感兴趣的行，但TABLESAMPLE在WHERE子句之前应用，这使得此方法不适合我的用例。例如：
这将首先从整个数据集中选择500个随机行，然后尝试从该集中查找与WHERE子句匹配的记录。这可能会导致查询只返回非常少且相当不可预测的实际需要的数据行数。此外，因为记录是随机的，所以不能保证它们在整个数据集中均匀分布。如果出于统计原因查询数据，这可能很好，但当为图表提取数据时，这并不理想。
因此，虽然TABLESAMPLE可能是在整个表中选择随机记录集的一种非常快速的方法，但当我们需要一组均匀分布于整个数据集的行，而只针对表总数据的一部分，并且我们希望对其选择的行数有一些可预测的控制时，它就不起作用了。
当要解决的问题是随机选择表行时，还有其他可用的解决方案，但对于选择N个或接近N个均匀分布的数据点，这些解决方案中没有一个特别有用，并且从中可以找到的灵感有限。
作为快速概述，最简单的方法是只在WHERE子句中插入与随机数的比较：
这将选择总行的大约10%，但它需要全表扫描。如果需要特定数量的随机行，可以在ORDER BY子句中使用随机化来实现。这要慢得多，特别是在大表上，因为全表扫描之后是某种整表扫描，但它确实可以工作：
如果表使用递增的整数主键进行索引，并且键中没有太多间隙，则可以通过使用ID范围内生成的随机数序列来执行相当快速的随机记录选择。
如果遥测表使用整数主键编制索引，并且它有650000条记录，几乎没有间隙(没有删除任何块)，则上面的代码将遍历生成的一系列整数，并将生成一组介于1到650000之间的不同的随机整数。您可以将FROM子句中的代码视为生成随机数组的for循环的SQL版本。然后，连接将这些数字用作记录ID，它将返回限制为500条记录的最终集合。我们拉出500条以上的记录，以防出现一些缺口，以避免找不到500条记录。考虑到已经提到的注意事项，此技术非常快速，虽然它不能应用于使用UUID索引的表的情况，但我认为可以推广此实现思想中的核心概念，以提供适用于时间索引数据的非常快速的解决方案。
我得出的解决方案可以在几毫秒内返回表中某个数据子集的时间分布样本。它利用PostgreSQL通过函数生成系列数据的能力，以及在构建查询时API服务器内部的一些数学运算，以创建一个查询，该查询在正确索引后，将非常快速地返回均匀分布的数据。
这个想法取决于这样一个事实，即所有记录都带有一个时间戳，该时间戳保留了它们在时间序列中的位置。因此，如果数据库本身可以生成一系列均匀分布在整个数据范围内的时间戳，则查询可以返回一个时间戳和下一个时间戳之间的间隔行。这将导致一组行数不超过所需的最大值集，但如果间隔太小而无法在每个行内查找数据，则行数可能会更小，并且PostgreSQL处理此查询的速度快得出奇。
因此，如果我们想要从数据库中提取从2020-09-01 00：00：00：00到2020-09-15 23：59：59期间的500个特定类型的数据点，那么第一步是计算出在这些开始时间戳和结束时间戳之间需要多少间隔才能获得500步。这是一个简单的数学运算：
此方法利用PostgreSQL Generate_Series()函数，该函数获取起始值、结束值和间隔，并从它们生成一组值。
但是，PostgreSQL提供了一种方便的机制来绕过子查询的这一限制。PostgreSQL允许用户定义自定义类型。使用此功能，可以定义同时包含JSONB和没有时区实体的时间戳的类型，对于上面遇到的限制而言，这将被视为单个数据项。
然后，可以重写查询以返回此自定义类型的单个实例，从而消除错误并使其按预期工作。
当针对与原始的基于row_number()的解决方案相同的数据集进行评估时：
我会注意到，平均来说，它实际上并没有那么快。我用来执行这些测试的数据库实例是一个非常小的t3.microAmazon RDS实例，因此计时结果有一定的可变性，大多数结果从比本例稍微快一点到大约9ms不等，平均接近8ms。此外，如果在执行查询时索引不在高速缓存中，则在上述测试环境中，第一次执行时的查询性能对于该第一次查询下降到约40ms。实际上，对于我们的应用程序，这个查询最终平均只比原来的查询快大约3250倍。
为您的数据集适当地调优您的数据库，并且您的缓冲区足够大，以容纳一个合理的工作集，这一点很重要。如果数据库引擎必须从存储中提取非常大量的数据，那么等待IO操作就会影响整体查询性能。
对于缺少(data？'；load_avg'；：：text)过滤器的基本查询，执行时间进一步减少了一半。
这在我们的用例中用处有限，因为我们希望能够具体选择正在绘制的数据，但它只是用来说明基本技术可以有多快。
这比我开始通过更好的解决方案解决这个问题时所希望或期望的要快得多。允许数据库生成间隔，然后接受数据库在该间隔中找到的第一条记录，以不能保证数据点之间的绝对精确周期为代价，快速生成跨总时间集均匀分布的结果集。在实验测试中，数据点往往分布在时间间隔周围，但偶尔会有几对点，其中一个落在时间间隔的末尾，下一个落在开始附近，反之亦然。
如果需要数据点之间的精确周期，此技术仍然可以提供出色的结果。只需在LIMIT子句前添加一个ORDER BY T.CREATED_AT，就可以保证每个数据点都是在其间隔中找到的第一个数据点。即。
而使用ORDER BY时，所选择的数据点虽然与前一个集合有显著重叠，但由该特定查询的间隔(2592秒约为43.2分钟)分隔得更加严格。
使用order by会降低性能，因为它会强制数据库对每个间隔中的结果进行排序。然而，成本不一定是令人担忧的原因：
前面提到的关于数据库调优的警告尤其适用于此版本的查询。因为涉及到排序，所以它对工作集中的数据的差异更加敏感。但是，即使在最坏的情况下，没有数据在工作集中，这个查询仍然比基于row_number()的解决方案快几倍。
SQL和PostgreSQL都是工具包，几乎总是有多种方法可以做到这一点，而且随着人们在期望的结果上投入更多的时间，就有可能得到更深层次的细微差别和改进。对于从较大的表中选择时间分布的行样本，可能有比此更好的技术，但此技术比更幼稚的版本快得多，并且它以提供极佳用户体验的速度准确地交付了我们的应用程序所需的结果。同时，还有一些事情可能会让它变得更快，特别是在整个数据集增长的情况下。例如，由于查询始终集中在单个服务器ID上，因此按server_id进行分区可能是成功的。
不过，最后，自由使用PostgreSQL的解释分析来理解数据库引擎在执行查询时所做的工作，以及实验和大量失败，以及随着解决方案的改进而逐步进行的小改进，使我们从一个返回大量数据的解决方案转变为一个返回大量数据，但等待这些结果的时间长得离谱的解决方案，而这个解决方案仍然以比我开始重新实现该查询时所期望的速度更快的速度返回了很好的结果，这将使我们从一个返回大量数据的解决方案转变为一个仍然以比我开始重新实现该查询时所期望的速度更快的速度返回大量结果的解决方案。
