主页 / dba / 问题 / 291775
Accepted
sev
Asked: 2021-05-19 04:11:08 +0800 CST

将 PostgreSQL 表转换为 TimescaleDB 超表

  • 772

我有一个 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: cannot create a unique index without the column "event_time" (used in partitioning)

问题 1：从这篇文章https://stackoverflow.com/questions/55312365/how-to-convert-a-simple-postgresql-table-to-hypertable-or-timescale-db-table-usi我的理解是这个是因为我指定了一个唯一约束 (con1)，它不包含我要分区的列 - event_time。那是对的吗？

问题 2：我应该如何更改我的表或超表才能进行转换？我已经添加了一些关于我计划如何使用数据和数据结构的数据。

数据属性和用法：

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

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

  • 以某种方式将所有具有相同时间戳的事件打包到一个数组中，并将它们保持在一行中。我认为这会对压缩产生不利影响，并且在查询数据时提供较少的灵活性。此外，我可能最终不得不解压缩每个查询的数据。
  • 删除唯一约束 con1 - 那么我如何确保我不会两次添加同一行？
  • 扩展唯一约束 con1 以包含 event_time - 这不会以某种方式降低性能，同时会导致我不小心插入 2 行的 entry_id 和 pair_id 但不同的 event_time 的错误？（我怀疑这可能会发生）
postgresql timescaledb

2 个回答

  1. Best Answer

    这里有几个问题和答案：

    1. 这是唯一约束（con1）的问题，而不是外键约束，但这可能是一个错字。
    2. 是的，这是一个问题，因为不包括时间分区列，我的第一个问题是，entry_id 似乎它可能是来自其他系统的时间的代理，你可以只使用 pair_id, time 作为你的唯一约束吗？会有多条不同entry_ids相同pair_ids和相同时间的记录吗？您可能可以使用按 pair_id 分组的 count(*) 来运行查询，时间来解决这个问题...请注意，三列唯一约束可能有效，这是一个时间如何分配的问题，它是由您在写时间还是由上游系统？如果它是上游系统，那么我不会太担心重复。
    3. 数组的东西有点类似于压缩，但压缩比简单的数组打包更有效的算法不同。
    4. 找出适用于pair_id、time 或pair_id、entry_id、time 的唯一约束可能比尝试在查询时将数据减少到唯一集更有效。并且三列唯一约束不会对性能产生巨大影响。这些列的顺序应该根据查询模式来选择，你通常有什么样的 where 子句？即在pair id或类似的东西以及时间上？还是只是时间？
    5. 最后，考虑到表的大小，我强烈建议在不迁移数据的情况下创建超表，因此创建一个单独的表，使其成为超表，然后做一个小工作来迁移数据。migrate_data = true 确实适用于更小的表，有一个外部工作意味着您还可以引入一些并行性，这将使其运行得更快。
    • 3

  2. 我的理解是，这是因为我指定了一个唯一约束（pair_id_fkey），它不包含我正在分区的列 - event_time。那是对的吗？

    是的。

    问题 2：我应该如何更改我的表或超表才能进行转换？我已经添加了一些关于我打算如何使用这些数据以及下面数据结构的数据。

    我认为您可以通过消除约束来保持结构。您可以使用avg,last或first与一些time_bucket结合使用。

    以某种方式将所有具有相同时间戳的事件打包到一个数组中，并将它们保持在一行中。

    这就是 Timescale 压缩在幕后所做的事情，请查看.

    删除唯一约束 con1 - 那么我如何确保我不会两次添加同一行？

    正如我之前提到的，您可能可以使用last或first避免重复。

    扩展唯一约束 con1 以包含 event_time - 这不会以某种方式降低性能，同时会导致我不小心插入 2 行的 entry_id 和 pair_id 但不同的 event_time 的错误？（我怀疑这可能会发生）

    first从查询中保留orlast并避免约束会更快。但如果性能不是问题，我认为它会起作用。您也可以组合time, entry_id, and pair_id，因为约束接受多个列。

    • 2

相关问题