将PostgreSQL表转换为TimescaleDB超级表

Question

将PostgreSQL表转换为TimescaleDB超级表

4

我有一个PostgreSQL表，我正在尝试将其转换为TimescaleDB超级表。

表的结构如下：

CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

当我尝试使用以下命令将该表转换为TimescaleDB超级表时：

SELECT create_hypertable(
        'data',
        'event_time',
        chunk_time_interval => INTERVAL '1 hour',
        migrate_data => TRUE
);

我遇到了错误：ERROR: 无法创建唯一索引，因为未包含分区字段 "event_time" 问题1：根据这篇文章，我理解这是因为我指定了一个不包含我正在分区的列 - event_time 的唯一约束条件(pair_id_fkey)。我的理解正确吗？ 问题2：我应该如何更改我的表或超级表以便能够转换它？我添加了一些关于如何使用数据和数据结构的数据。 数据属性和用途：

可能会有多个具有相同事件时间的条目 - 这些条目将具有顺序的 entry_id
- 这意味着如果我有两个条目(event_time 2021-05-18::10:16, id 105, <some_data>) 和 (event_time 2021-05-18::10:16, id 107, <some_data>)，那么具有id 106的条目也将拥有相同的时间戳 2021-05-18::10:16
entry_id 不是由我生成的，我使用唯一约束con1确保我没有插入重复的数据
我主要在event_time上查询数据，例如创建图表和执行其他分析
当前数据库包含大约46亿行，但很快应该包含更多
我想利用TimescaleDB的速度和良好的压缩性能
我不太关心插入性能

我一直在考虑的解决方案：

以某种方式将具有相同时间戳的所有事件打包到一个数组中，并将它们保留在一行中。我认为这会对压缩产生不利影响，并且在查询数据时提供的灵活性较小。此外，我可能最终需要在每次查询时解包数据。
删除唯一约束条件con1 - 那么我如何确保没有重复添加相同的行？
扩展唯一约束条件con1以包括event_time - 这是否会降低性能，同时又会出现意外插入具有entry_id和pair_id但不同event_time的2行数据的错误？ (尽管我认为这不太可能发生)

- sev

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- k_rus · Accepted Answer

您的理解是正确的，UNIQUE (pair_id, entry_id)不能从表中创建超表，因为唯一约束需要包括分区键，即在您的情况下是event_time。

1. 我不明白第一种选项是如何将具有相同时间戳的记录打包到单个记录中来帮助保持唯一性的。 2. 删除唯一约束将允许创建超表，并且如您所提到的，您将失去检查约束的可能性。 3. 添加时间列，例如UNIQUE(pair_id, entry_id, event_time)，是一种常见的方法，但它允许插入具有不同时间戳的重复项，正如您所提到的。它比选项2在插入期间性能更差。您可以用唯一索引替换event_time上的索引（您需要这个索引，因为您查询此列，并且由TimescaleDB自动创建），这样您就可以节省一点儿了。

CREATE UNIQUE INDEX indx ON (event_time, pair_id, entry_id);

手动在每个块表上创建唯一约束，这将确保块内的唯一性，但是不同块中仍然可能存在重复。主要缺点是需要在创建新块时弄清楚如何创建。

TimescaleDB 不支持没有分区键的唯一约束，因为它需要访问所有现有块来检查唯一性，并且会影响性能（或者需要创建一个大型全局索引）。我认为，在时间序列数据中设置唯一约束并不常见，因为它通常与人工生成的基于计数器的标识符相关。