提升查询和 upsert 性能

实时分析应用程序不仅需要快速插入和分析查询。它们在检索单个记录、强制约束或执行 upsert 时也需要高性能，而这是 OLAP/列式数据库所缺乏的。

• 数据分段和排序 - 通过控制数据的物理存储方式来提升性能。
• B 树和哈希索引 - 实验性：使用二级索引来提升点查询性能。

为优化查询性能，TimescaleDB 允许您明确控制数据在列存储中的物理组织方式。通过有效地组织数据，查询可以最大限度地减少磁盘读取并更高效地执行，在可能的情况下使用向量化执行进行并行批处理。

展开图片

• 将相关数据组合在一起以提升扫描效率：将行组织成逻辑分段可确保按特定值过滤的查询只扫描相关数据部分。例如，在上面，查询特定 ID 的速度特别快。
• 在分段内排序数据以加速范围查询：定义一致的顺序可减少查询后排序的需求，从而使基于时间的查询和范围扫描更高效。
• 减少磁盘读取并最大化向量化执行：结构良好的存储布局可实现高效的批处理（单指令多数据，即 SIMD 向量化）和并行执行，从而优化查询性能。

通过结合分段和排序，TimescaleDB 确保列式查询不仅快速，而且资源高效，从而实现高性能实时分析。

在列存储中对数据进行排序对查询的压缩率和性能有很大影响。按维度变化的数据行应该彼此靠近。由于超表包含时序数据，它们按时间进行分区。这使得时间列成为排序数据的完美选择，因为测量值会随着时间的推移而演变。

如果您只使用 orderby 作为列存储设置，您将获得足够好的压缩率以节省大量存储空间，并且您的查询会更快。但是，如果您只使用 orderby，则始终需要使用时间维度访问数据，然后根据其他条件过滤返回的行。

有效访问数据取决于您的用例和查询。您在列存储中对数据进行分段，以匹配您想要访问它的方式。也就是说，以一种使您的查询更容易在正确的时间获取正确数据的方式。当您对数据进行分段以访问特定列时，您的查询将得到优化并产生更好的性能。

例如，要访问有关具有特定 device_id 的单个设备的信息，您可以在 device_id 列上进行分段。这使您能够更快地在列存储中对压缩数据运行分析查询。

例如，对于以下超表

CREATE TABLE metrics (
  time TIMESTAMPTZ,
  user_id INT,
  device_id INT,
  data JSONB
) WITH (
  tsdb.hypertable,
  tsdb.partition_column='time'
);

复制

单个批次（如我们上面看到的）中一起压缩的行数是 1000。如果您的数据块不包含足够的数据来创建足够大的批次，则压缩率会降低。在定义列存储设置时需要考虑这一点。

TimescaleDB 支持并加速使用 hypercore 进行实时分析，同时不遗漏重要
PostgreSQL 功能，包括对标准 PostgreSQL 索引的支持。Hypercore 是一种混合存储引擎，因为它在忠于 PostgreSQL 的同时支持深度分析。全面支持列存储数据上的 B 树和哈希索引，使您能够将点查找速度提升 1,185 倍，强制执行唯一约束，并以 224 倍的速度执行 upsert——所有这些都同时保持列存储压缩和分析性能。

索引是数据库性能优化的基础部分，它们使查询能够快速定位和检索数据，而无需扫描整个表。B 树和哈希索引是 PostgreSQL 最广泛使用的索引类型之一。然而，它们是为不同类型的查询而设计的

• B 树索引：将数据按分层结构排序，非常适合涉及范围（>、<、BETWEEN）和相等（=）查找的查询。B 树索引使您能够检查唯一约束或进行基于范围的过滤。您可以快速检索和过滤相关行，而无需扫描所有数据。

  当查询在索引列中搜索特定值或值范围时，B 树结构使数据库能够快速遍历树并在对数时间（O(log n)）内找到相关记录，与全表扫描相比显著提升了性能。

• 哈希索引：专为精确匹配查找（=）而设计，并使用哈希函数将值映射到唯一的磁盘位置。当搜索特定 ID 时，哈希索引允许以最小的开销直接访问数据，并提供最快的结果

  当查询搜索单个值（例如按事务 ID 搜索事务）时，哈希索引甚至比 B 树索引更快，因为它们不需要树遍历并且具有摊销常数时间（O(1)）查找。哈希索引不支持范围查询，它们特别适用于频繁查询的唯一键的情况。

这些索引方法的性能优势来自为高效键搜索而优化的数据结构。这导致更少的磁盘页面读取，从而在定位特定数据点或强制唯一性时减少 I/O 峰值。

PostgreSQL 提供了 多种索引类型。例如，默认的 B 树、哈希、GIN 和 BRIN，都作为索引访问方法（IAM）实现。PostgreSQL 提供了 表访问方法（TAM） 接口用于表存储。

展开图片

默认情况下，TimescaleDB 将数据存储在行存储（rowstore）中的标准 PostgreSQL 行式表中，使用默认的 heap TAM。为了使 heap TAM 与列存储（columnstore）一起工作，TimescaleDB 集成了 PostgreSQL TOASTTOAST 将列式数据存储为压缩数组。然而，查询列式数据会返回压缩的、不透明的数据。为了支持正常查询，TimescaleDB 将 DecompressChunk 扫描节点添加到 PostgreSQL 查询计划中，以便动态解压缩数据。但是，heap TAM 只索引压缩值，而不索引原始数据。

Hypercore TAM 在后台处理解压缩。这使得 PostgreSQL 能够使用标准接口进行索引、收集统计信息、强制约束和按引用锁定元组。这还允许 PostgreSQL 内置的扫描节点（如顺序扫描和索引扫描）在列存储上操作。自定义扫描节点用于分析查询性能优化，包括向量化过滤和聚合。

Hypercore TAM 支持 B 树和哈希索引，使得在列存储上的点查找、upsert 和唯一约束强制执行更加高效。我们的基准测试表明性能有了显著提升

• 将点查找查询速度提升 1,185 倍，以检索单个记录。
• 检查唯一约束时，插入速度提升 224.3 倍。
• upsert 速度提升 2.6 倍。
• 范围查询速度提升 4.5 倍。

为压缩数据添加 B 树和哈希索引可以显著加快查找和插入速度，但这会带来一个权衡：由于额外的索引结构导致存储使用量增加。

B 树和哈希索引在以下情况下特别有用：

• 您需要在非 SEGMENTBY 键上进行快速查找。例如，按 UUID 查询特定记录。
• 压缩数据上的查询延迟是您应用程序的瓶颈。
• 您频繁更新历史数据并需要高效的唯一性强制执行。

但是，在以下情况下请考虑存储权衡：

• 您的查询已从列存储最小/最大索引或 SEGMENTBY 优化中受益。
• 您的工作负载优先考虑压缩效率而非查找速度。
• 您主要运行聚合和范围扫描，而索引可能无法提供显著的加速。

为使用二级索引加速查询，请在列存储策略上启用 hypercore TAM

Hypercore TAM 现在已在超表中的所有列存储数据块上激活。

在策略中启用 hypercore TAM 后，当表数据块从行存储转换为列存储时，索引会重建。当您查询数据时，PostgreSQL 查询规划器会在行存储和列存储上使用这些索引。

您向超表添加哈希索引和 B 树索引的方式与常规 PostgreSQL 表相同

• 哈希索引

  CREATE INDEX readings_metric_uuid_hash_idx ON readings USING hash (metric_uuid);

  复制
• B 树索引

  CREATE UNIQUE INDEX readings_metric_uuid_metric_uuid_uploaded_at_idx
  ON readings (metric_uuid, uploaded_at);

  复制

如果您有尚未更新为使用 hypercore TAM 的现有数据块，要使用 B 树和哈希索引，您可以通过调用 ALTER TABLE _timescaledb_internal._hyper_1_1_chunk SET ACCESS METHOD hypercore; 来更改现有数据块的表访问方法。

索引对于高选择性查询特别有用，例如通过其标识符检索唯一事件。例如

SELECT 
    created_at,
    device_id,
    temperature
FROM readings 
WHERE metric_uuid = 'dd19f5d3-d04b-4afc-a78f-9b231fb29e52';

复制

没有索引，查询会扫描并过滤整个数据集，导致执行缓慢和高 I/O 使用。最小/最大稀疏索引在处理增量数字 ID 时很有帮助，但对于随机数字值或 UUID 则效果不佳。

在 metric_uuid 上设置哈希索引以启用直接查找。这通过仅解压缩相关数据段来显著提升性能。

CREATE INDEX readings_metric_uuid_hash_idx ON readings USING hash (metric_uuid);

复制

启用哈希索引和 hypercore TAM 后，相同的 SELECT 查询性能提升 1,185 倍；哈希索引的执行时间为 10.9 毫秒，而 B 树索引为 12.57 毫秒，相比之下，未优化的查询为 12,915 毫秒。

实时应用程序中的一个常见用例是回填或更新旧数据。例如，传感器在批量上传过程中发生故障或暂时断开连接后，稍后会重新发送数据。为避免可能的重复记录，在存储数据之前，您必须检查数据库中是否已存在该数据。

为防止重复条目，您可以使用主键强制执行唯一性。主键约束通过唯一索引强制执行，使冲突检查变得快速。没有索引，验证唯一性涉及扫描和解压缩可能大量的数据。这会显著减慢插入速度并消耗过多的 IOPS。超表上的 UNIQUE 约束还必须包含超表分区键。

以下 UNIQUE 使用 B 树索引。

CREATE UNIQUE INDEX readings_metric_uuid_metric_uuid_created_at_idx
    ON readings (metric_uuid, created_at);

复制

回填历史数据的可能策略包括

• 当行不存在时插入数据:

  确保没有重复的插入语句如下所示

  INSERT INTO readings VALUES (...) ON CONFLICT (device_id, created_at) DO NOTHING;

  复制

  我们的基准测试表明，这使得插入速度提升 224.3 倍，将执行时间从 289,139 毫秒减少到 1,289 毫秒。

• 插入缺失数据或更新现有数据:

  upsert 是一种数据库操作，如果行不存在则插入新行，如果发生冲突则更新现有行。这使您能够重新摄取新版本的行，而不是执行单独的更新语句。没有索引，系统需要扫描和解压缩数据，大大降低了摄取速度。使用主键索引，冲突行可以直接在列存储中的压缩数据段内定位。

  以下查询尝试插入一条新记录。如果 metric_uuid 和 created_at 值相同的记录已存在，则它会使用新记录中的相应值更新 temperature。

  INSERT INTO readings VALUES (...) ON CONFLICT DO UPDATE SET temperature = EXCLUDED.temperature;

  复制

  Timescale 基准测试表明，这使得 upsert 速度提升 2.6 倍，将执行时间从 24,805 毫秒减少到 9,520 毫秒。

为了定期报告设备超过临界温度的次数，您可以计算温度超过的次数，并按设备 ID 分组。

SELECT
    device_id,
    COUNT(temperature)
FROM readings
WHERE temperature > 52.5 
GROUP BY device_id;

复制

您会看到类似以下内容

为了加快此查询速度，请使用部分 B 树索引。也就是说，一个 B 树索引只包含满足特定 WHERE 条件的行。这使得索引更小，对于匹配该条件的查询更高效。例如，要为超过 52.5 的温度读数创建部分 B 树索引

CREATE INDEX ON readings (temperature) where temperature > 52.5;

复制

与在列存储中使用稀疏最小/最大索引相比，Timescale 基准测试表明 B 树索引查询速度快 4.5 倍。