时间戳

首页 > 解决方案 > Postgres：如何有效地对以下随机事件 id 进行分桶（小时、config_id、sensor_id）

postgresql - Postgres：如何有效地对以下随机事件 id 进行分桶（小时、config_id、sensor_id）

问题描述

我有一个有 4 列的大表“测量”：

measurement-service=> \d measurement
                                Table "public.measurement"
        Column         |            Type             | Collation | Nullable | Default 
-----------------------+-----------------------------+-----------+----------+---------
 hour                  | timestamp without time zone |           | not null | 
 config_id             | bigint                      |           | not null | 
 sensor_id             | bigint                      |           | not null | 
 event_id              | uuid                        |           | not null | 
Partition key: RANGE (hour)
Indexes:
    "hour_config_id_sensor_id_event_id_key" UNIQUE CONSTRAINT, btree (hour, config_id, sensor_id, event_id)
Number of partitions: 137 (Use \d+ to list them.)

分区名称示例：“measurement_y2019m12d04”

然后我通过 COPY 将许多事件作为 CSV 插入到一个临时表中，然后我从那里使用 ON CONFLICT DO NOTHING 将表直接复制到分区中。

例子：

CREATE TEMPORARY TABLE 'tmp_measurement_y2019m12d04T02_12345' (
  hour timestamp without timezone,
  config_id bigint,
  sensor_id bigint,
  event_id uuid
) ON COMMIT DROP;
[...]
COPY tmp_measurement_y2019m12d04T02_12345 FROM STDIN DELIMITER ',' CSV HEADER;
INSERT INTO measurement_y2019m12d04 (SELECT * FROM tmp_measurement_y2019m12d04T02_12345) ON CONFLICT DO NOTHING;

我想我只通过发送包含同一小时数据的 CSV 来帮助 postgres。也在那个小时内，我删除了 CSV 中的所有重复项。因此 CSV 仅包含唯一行。

但是我在不同的时间发送了很多批次。没有顺序。它可以是今天、昨天、最后一周的时间。等等。

到目前为止，我的方法效果很好，但我认为我现在已经达到了极限。插入速度变得很慢。当 CPU 空闲时，我有 25% 的 i/o 等待。子系统是具有数 TB 的 RAID，使用非 SSD 的磁盘。

maintenance_work_mem = 32GB
max_wal_size = 1GB
fsync = off
max_worker_processes = 256
wal_buffers = -1
shared_buffers = 64GB
temp_buffers = 4GB
effective_io_concurrency = 1000
effective_cache_size = 128GB

每天每个分区大约 20gb 大，包含不超过 500m 行。通过维护每个分区的唯一索引，我再次复制了数据。

另一方面，查找速度很快。

我认为限制是在（小时，config_id，sensor_id）中使用相当随机的UUID维护btree。我不断地改变它，把它写出来，必须重新阅读。

我想知道，如果有另一种方法。基本上我想要（小时，config_id，sensor_id，event_id）的唯一性，然后每个（小时，config_id，sensor_id）快速查找。

我正在考虑删除唯一索引，并且只有一个超过（小时，config_id，sensor_id）的索引。然后在读者方面提供唯一性。但它可能会减慢阅读速度，因为当我通过（小时，config_id，sensor_id）查找时，event_id 不能再通过索引传递。它必须访问实际行以获取 event_id。

或者我通过哈希索引提供唯一性。

欢迎任何其他想法！

谢谢你。

标签： postgresql

解决方案

当您执行插入时，您应该指定一个与要插入的表的索引匹配的 ORDER BY：

INSERT INTO measurement_y2019m12d04 
    SELECT * FROM tmp_measurement_y2019m12d04T02_12345 
        order by hour, config_id, sensor_id, event_id

只有当这未能提供足够的改进时，我才会考虑您列出的任何其他选项。

哈希索引不提供唯一性。您可以使用排除约束对其进行模拟，但我认为它们的效率较低。排除约束支持 DO NOTHING，但不支持 DO UPDATE。因此，只要您的用例不会演变为需要 DO UPDATE，那么您在这方面会做得很好，但我仍然怀疑它是否真的能解决问题。如果您的瓶颈是更新索引的 IO，那么哈希只会使情况变得更糟，因为它旨在将您的数据分散到整个地方，而不是将其集中在一个小的可缓存区域中。

您还提到了并行处理。对于插入临时表，这可能没问题。但我不会并行执行 INSERT...SELECT 。如果 IO 是您的瓶颈，那可能只会让情况变得更糟。当然，如果在我的 ORDER BY 建议之后 IO 不再是瓶颈，那么忽略这部分。

推荐阅读