TimescaleDB 超表

TimescaleDB 中使用称为 hypertables 的数据表来存储数据。hypertable（超表）是与数据交互的主要点，因为它提供了可以通过标准 SQL 查询的标准表抽象。 在 TimescaleDB 中创建超表需要两个 SQL 命令：

• CREATE TABLE 使用标准 SQL 语法

• SELECT create_hypertable()

几乎所有与 TimescaleDB 的用户交互都使用超表。插入、更新或删除数据、通过 SELECT 查询数据、更改表、添加新列或索引、与其他表或超表连接等等都可以（并且应该）在超表上执行。

超级表的真正威力来自于许多实际存储数据的单个表的抽象或虚拟视图，称为块。

1. create_hypertable()

从 PostgreSQL 表创建 TimescaleDB 超表，按时间分区，并可选择在一个或多个其他列上进行分区。PostgreSQL 表不能是已经分区的表（声明性分区或继承）。在非空表的情况下，可以使用 migrate_data 选项在超表创建期间迁移数据，尽管这可能需要很长时间并且当表包含外键约束时有一定的限制。

创建后，所有操作，例如ALTER TABLE、SELECT等，仍然对生成的超表起作用。

必需的参数

• relation - 要转换为超表的表的标识符。

• time_column_name - 包含时间值的列的名称以及要分区的主列。time_column 列支持的数据类型：时间戳（TIMESTAMP、TIMESTAMPTZ） 、日期、整数（SMALLINT、INT、BIGINT） ；时间列必须定义为NOT NULL。如果在创建表未指定，create_hypertable 则在执行时自动在表上添加此约束。

可选参数

• partitioning_column - 要分区的附加列的名称。如果提供，则必须提供参数 number_partitions。

• number_partitions - 用于散列分区的数量partitioning_column。必须 > 0。

• chunk_time_interval - 每个块覆盖的事件时间。必须 > 0。默认值为 7 天。

• create_default_indexes - 是否在时间/分区列上创建默认索引，默认为真。
• if_not_exists - 如果表已转换为超表或引发异常，是否打印警告。默认为假。
• partitioning_func - 用于计算分区值的函数。
• associated_schema_name - 内部超表的模式名称。默认为_timescaledb_internal。
• associated_table_prefix - 内部超表块名称的前缀。默认为_hyper
• migrate_data - 设置为 TRUE 以将任何现有数据从relation表迁移到新超表中的块。如果没有此选项，非空表会生成错误。大型表可能需要大量时间来迁移。默认为假。

• time_partitioning_func - 将不兼容的主要时间列值转换为兼容值的函数。函数必须是IMMUTABLE
• replication_factor - 如果设置为 1 或更大，则创建分布式超表。默认为 NULL。创建分布式超表时，请考虑使用create_distributed_hypertable

• data_nodes - 这是用于该表的数据节点集（如果它是分布式的）。这对非分布式超表没有影响。如果未指定数据节点，则分布式超表使用此实例已知的所有数据节点。

返回值

列	类型	说明
hypertable_id	整数	TimescaleDB 中超表的 ID。
schema_name	文本	转换为超表的表的名称。
table_name	文本	转换为超表的表的表名。
created	布尔值	如果创建了超表，则为 TRUE，如果为if_not_exists真且未创建超表，则为 FALSE。

chunk_time_interval 的设置：

• 对于具有时间戳或日期类型的时间列， chunk_time_interval应指定为类型或以微秒 interval为单位的整数值。
• 对于整数类型，chunk_time_interval 必须明确设置，因为数据库不理解每个整数值所代表的语义（秒、毫秒、纳秒等）。因此，如果您的时间列是自 UNIX 纪元以来的毫秒数，并且您希望每个块涵盖 1 天，则应指定chunk_time_interval => 86400000。

在哈希分区的情况下（换句话说，如果number_partitions大于零），可以选择指定自定义分区函数。如果未指定自定义分区函数，则使用默认分区函数。默认分区函数为给定类型调用 PostgreSQL 的内部散列函数（如果存在）。因此，自定义分区函数可用于没有原生 PostgreSQL 散列函数的值类型。分区函数应采用单个anyelement类型参数并返回正integer哈希值。请注意，此散列值不是分区 ID，而是插入值在维度键空间中的位置，然后在分区之间进行划分。

示例：

将表转换conditions为超表，仅在列上进行时间分区time：

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

将表转换conditions为超表，设置chunk_time_interval为 24 小时。

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

将表conditions转换为具有时间分区time和空间分区（4 个分区）的超表location：

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

与上面相同，但使用自定义分区函数：

SELECT create_hypertable('conditions', 'time', 'location', 4, partitioning_func => 'location_hash');

将表conditions转换为超表。conditions如果已经是超表，则不要发出警告：

SELECT create_hypertable('conditions', 'time', if_not_exists => TRUE);

时间间隔：

TimescaleDB 用户最常见的问题之一是围绕配置chunk_time_interval ， TimescaleDB 的当前版本支持手动和自动调整其时间间隔。使用手动设置的时间间隔，用户应该chunk_time_interval 在创建他们的超表时指定一个（默认值为 1 周）。用于新块的间隔可以通过调用来改变set_chunk_time_interval()。

选择时间间隔的关键属性是属于最近间隔的块（包括索引）（或块，如果使用空间分区）适合。因此，我们通常建议设置间隔，使这些块不超过主内存的 25%。

要确定这一点，需要大致了解数据速率。如果每天写入大约 2 GB 的数据并且有 64 GB 的内存，那么将时间间隔设置为一周会很好。如果每天在同一台机器上写入 10 GB，则将时间间隔设置为一天是合适的。如果数据分批加载更多，此间隔也将保持 - 例如，每周批量加载 70 GB 数据，其中数据对应于整个一周的记录。

虽然使块更小而不是太大通常更安全，但将间隔设置得太小会导致很多块，这对应于某些类型查询的计划延迟增加。

空间分区：

在大多数情况下，建议用户不要使用空间分区。但是，如果创建分布式超表，则创建空间分区很重要。

2.show_chunks()

获取与超表关联的块列表。函数接受以下必需和可选参数。

必需的参数

名称	类型	说明
relation	字符串	从中选择块的超表或连续聚合。

可选参数

名称	类型	说明
older_than	任何	应显示比此时间戳更旧的任何完整块的截止点的规范。
newer_than	任何	应显示比此时间戳更新的任何完整块的截止点规范。

older_than和newer_than 参数可以通过两种方式指定：

• 区间类型：截止点计算为now() - older_than和 类似now() - newer_than。如果提供了 INTERVAL 并且时间列不是 TIMESTAMP、TIMESTAMPTZ 或 DATE 之一，则会返回错误。
• 时间戳、日期或整数类型：截止点以 TIMESTAMP / TIMESTAMPTZ / DATE 或 SMALLINT / INT / BIGINT 的形式明确给出。时间戳或整数的选择必须遵循超表时间列的类型。

当使用两个参数时，函数返回结果两个范围的交集。例如，指定newer_than => 4 months并older_than => 3 months显示 3 到 4 个月大的所有完整块。同样，指定newer_than => '2017-01-01'并older_than => '2017-02-01'显示“2017-01-01”和“2017-02-01”之间的所有完整块。指定不会导致两个范围之间重叠交集的参数会导致错误。

示例

获取与表关联的所有块的列表：

SELECT show_chunks('conditions');

从超过 3 个月的超表中获取所有块：

SELECT show_chunks('conditions', older_than => INTERVAL '3 months');

从 2017 年之前的超表conditions中获取所有块conditions：

SELECT show_chunks('conditions', older_than => DATE '2017-01-01');

3. drop_chunks()

删除时间范围完全落在指定时间之前（或之后）的数据块。以与show_chunks 函数相同的样式显示已删除的块的列表。

块开始和结束时间的限制，开始时间总是在结束时间之前。如果一个块的结束时间早于older_than时间戳，或者如果newer_than给定，它的开始时间比newer_than时间戳新，则删除一个块。

请注意，因为当且仅当它们的时间范围完全落在指定时间戳之前（或之后）时才会删除块，所以剩余数据可能仍然包含在指定时间戳之前（或之后）的时间戳。

只能根据时间间隔删除块。它们不能基于空间分区被删除。

必需的参数

名称	类型	说明
relation	字符串	要从中删除块的超表或连续聚合。
older_than	间隔	应删除任何早于此时间戳的完整块的截止点的规范。

可选参数

名称	类型	说明
newer_than	间隔	应删除比此时间戳更新的任何完整块的截止点的规范。
verbose	布尔值	设置为 true 会显示有关重新排序命令进度的消息。默认为假。

older_than和newer_than参数可以通过两种方式指定：

• 区间类型：截止点计算为now() - older_than和 类似now() - newer_than。如果提供了 INTERVAL 并且时间列不是、 或之一TIMESTAMP，则会返回错误。TIMESTAMPTZ、DATE
• 时间戳、日期或整数类型：截止点以TIMESTAMP/ TIMESTAMPTZ/DATE或 SMALLINT/ INT/的形式显式给出BIGINT。时间戳或整数的选择必须遵循超表时间列的类型。

当使用两个参数时，函数返回结果两个范围的交集。例如，指定newer_than => 4 months并older_than => 3 months删除所有 3 到 4 个月大的完整块。同样，指定newer_than => '2017-01-01'并older_than => '2017-02-01'删除“2017-01-01”和“2017-02-01”之间的所有完整块。指定不会导致两个范围之间重叠交集的参数会导致错误。

示例

从conditions超表中删除超过 3 个月的所有块：

SELECT drop_chunks('conditions', INTERVAL '3 months');

从 conditions 中删除未来 3 个月以上的所有块。这对于纠正使用不正确时钟摄取的数据很有用：

SELECT drop_chunks('conditions', newer_than => now() + interval '3 months');

从超表conditions中删除 2017 年之前所有块：

SELECT drop_chunks('conditions', '2017-01-01'::date);

删除超表中从 2017 年之前的所有块，其中时间列以 UNIX 纪元的毫秒数给出：

SELECT drop_chunks('conditions', 1483228800000);

从 conditions中删除 3 个月前和 4 个月前的所有块：

SELECT drop_chunks('conditions', older_than => INTERVAL '3 months', newer_than => INTERVAL '4 months')

在所有超表中删除 3 个月前的所有块：

SELECT drop_chunks(format('%I.%I', hypertable_schema, hypertable_name)::regclass, INTERVAL '3 months')
  FROM timescaledb_information.hypertables;

4. set_chunk_time_interval()

在超表上设置chunk_time_interval。创建新块时使用新间隔，并且现有块上的时间间隔不会更改。

必需的参数

名称	类型	说明
hypertable	字符串	Hypertable 更新间隔
chunk_time_interval		每个新块覆盖的事件时间

chunk_time_interval的有效类型取决于用于超表time列的类型：

time column type	chunk_time_interval type	Time unit
TIMESTAMP	INTERVAL	days, hours, minutes, etc
	INTEGER or BIGINT	microseconds
TIMESTAMPTZ	INTERVAL	days, hours, minutes, etc
	INTEGER or BIGINT	microseconds
DATE	INTERVAL	days, hours, minutes, etc
	INTEGER or BIGINT	microseconds
SMALLINT	SMALLINT	The same time unit as the time column
INT	INT	The same time unit as the time column
BIGINT	BIGINT	The same time unit as the time column

可选参数

名称	类型	说明
dimension_name		设置分区数的时间维度的名称

dimension_name只有当超表具有多个时间维度时，您才需要使用参数。

示例

对于 TIMESTAMP 列，设置chunk_time_interval为 24 小时：

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

对于表示为自 UNIX 纪元以来的毫秒数的时间列，设置chunk_time_interval为 24 小时：

SELECT set_chunk_time_interval('conditions', 86400000);

胜象大百科