TimescaleDB 超表

苍恩

2023-12-01

关于超表

超表（hypertable）是具有特殊功能的PostgreSQL表，可以很容易地处理时间序列数据。与它们交互就像与普通PostgreSQL表交互一样，但在幕后，超表会自动按时间将数据划分为块。
在TimescaleDB中，超表与普通PostgreSQL表可以一起存在。超表用来存储时序数据，这样可以提高插入和查询的性能，而且可以访问一些有用的时间序列特性。普通PostgreSQL表用来存储其它关系型数据。

超表分区

每个超表都由称为块（chunk）的子表组成。每个块被分配了一定的时间范围，并且只包含该范围内的数据。如果超表也通过空间分区，则每个块也会被分配一个空间值的子集。

时间分区

超表的每个块只保存来自特定时间范围的数据。当向一个还没有数据块的时间段插入数据时，TimescaleDB会自动创建一个数据块来存储它。
默认情况下，每个块覆盖7天，也可以根据需要进行更改。例如，设置“chunk_time_interval”为1天，则每个块存储同一天的数据，不同日期的数据存储在不同的块中。

时间分区的最佳实践

块的大小会影响插入和查询性能，我们需要一个足够小的块来适应内存，使得无需从磁盘读取即可插入和查询最新数据。但也不能有太多小且填充稀疏的块，这可能会影响查询计划时间和压缩。
对块大小的建议是让25％的内存大小能够存储每个活跃超表中的一个块及块上的索引。我们可以从数据速率来估算所需的间隔，例如，如果每天写入大约2GB的数据并具有64GB的内存，应该将间隔设置为1周。如果每天在一台计算机上写入大约10 GB的数据，应该将时间间隔设置为1天。

空间分区

空间分区是可选的。当空间分区开启时，会使用两个维度将数据划分为块：时间维度和空间维度。空间维度可以指定分区的数量，数据会按照在该维度上的哈希值分配到对应分区。
例如，假设使用设备id作为空间分区列，对于每一行，会根据设备id列的值计算出哈希值，然后将行插入该哈希值的对应分区。

空间分区的最佳实践

通常不建议对非分布式超表进行空间分区。仅当有多个物理磁盘且每个磁盘与单独的表空间相对应时有用。如果在没有此设置的情况下按空间进行分区，则会增加查询计划的复杂性而不会增加I/O性能。

超表索引

默认情况下，创建超表时会自动创建索引，可以通过将create_default_indexes选项设置为false来阻止创建索引。

默认索引为：

在所有超表上，按时间降序的索引
在具有空间分区的超表上，空间和时间字段上的联合索引

超表上的唯一索引必须包含这个表的所有分区列。

对超表的基本操作

创建超表

创建超表分为两个步骤：

创建一个PostgreSQL表
将其转换为TimescaleDB超表

创建一个PostgreSQL表

超表用于时间序列数据，所以需要一个保存时间值的列。可以是时间戳、日期或整数。

CREATE TABLE conditions (
   time        TIMESTAMPTZ       NOT NULL,
   location    TEXT              NOT NULL,
   temperature DOUBLE PRECISION  NULL,
   humidity    DOUBLE PRECISION  NULL
);

转换为TimescaleDB超表

指定要转换的表的名称，以及保存时间值的列名。

SELECT create_hypertable('conditions', 'time');

更改超表的块间隔

检查当前设置的块间隔

SELECT h.table_name, c.interval_length
  FROM _timescaledb_catalog.dimension c
  JOIN _timescaledb_catalog.hypertable h
    ON h.id = c.hypertable_id;

结果如下（单位是微秒）：

table_name | interval_length
-----------+-----------------
metrics    |    604800000000
(1 row)

创建超表时，更改块的间隔

默认的块间隔为7天，创建表时可以通过chunk_time_interval参数来指定间隔：

SELECT create_hypertable(
  'conditions',
  'time',
  chunk_time_interval => INTERVAL '1 day'
);

更改现有超表上的块间隔长度

SELECT set_chunk_time_interval('conditions', INTERVAL '24 hours');

更改超表

向超表中添加列

ALTER TABLE conditions
  ADD COLUMN humidity DOUBLE PRECISION NULL;

重命名超表

ALTER TABLE conditions
  RENAME TO weather;

超表上的唯一索引

在超表上创建唯一的索引

创建唯一索引时，它必须包含超表的所有分区列。

例如，超表hypertable_example是根据时间和设备id进行分区的。那么可以根据时间和设备id创建唯一索引：

CREATE UNIQUE INDEX idx_deviceid_time
  ON hypertable_example(device_id, time);

还可以根据时间、用户id和设备id创建唯一索引：

CREATE UNIQUE INDEX idx_userid_deviceid_time
  ON hypertable_example(user_id, device_id, time);

从具有唯一索引的表创建超表

如果在将表转换为超表之前在已经存在唯一索引，则反过来也有相同的限制，只能按唯一索引中的列对表进行分区。

例如，在hypertable_example表上已经存在设备id和时间上的唯一索引，那么可以将表转换为按time分区的超表：

SELECT * from create_hypertable('hypertable_example', 'time');

也可以将表转换为按time和device_id分区的超表：

SELECT * FROM create_hypertable(
  'hypertable_example',
  'time',
  partitioning_column => 'device_id',
  number_partitions => 4
);

删除超表

执行以下命令会删除属于超表的所有数据块：

DROP TABLE <TABLE_NAME>;