Schema 管理 - 《TimescaleDB 中文文档》

数据索引 Indexing Data
- 最佳实践
- 默认索引 Default Indexes
创建触发器 Creating Triggers
添加约束 Adding Constraints
半结构化数据使用JSON格式支持 JSON support for semi-structured data
- 索引整个JSONB结构 Indexing the entire JSONB structure
- 索引JSONB中的某字段 Indexing individual fields within a JSONB
更改、更新表结构 Altering/updating table schemas
表空间的存储管理 Storage management with tablespaces
- 如何为超表块(chunks)分配表空间 How hypertable chunks are assigned tablespaces

注意、本节中的主要问题是PostgreSQL相关，包括索引、触发器、JSON，与TimescaleDB的关系不大，只是强调在TimescaleDB中可以使用。具体的使用方法可以参见对应版本的PostgreSQL

设计适当的表对象是使用PostgreSQL的关键。创建适当的索引和表架构可以显着提高性能（相反，设计错误的架构可以导致显着的性能下降）。

TimescaleDB支持PostgreSQL支持的所有表对象，包括数据类型，索引和触发器。

请注意，有时具有灵活的架构非常有用，尤其是在存储半结构化数据时（例如，存储来自IoT传感器的读数来收集不同的测量值）。对于这些情况，TimescaleDB也支持PostgreSQL JSON和JSONB数据类型。

在本节中，我们提供了有关如何在表上创建适当的索引，触发器，约束和表空间以及如何适当利用JSON和JSONB数据类型的详细示例和最佳实践。

TIP: 获取有关数据库各方面信息的最常见方法之一是通过终端运行 psql 。有关更多信息，请参见PostgreSQL文档。

数据索引 Indexing Data

TimescaleDB支持PostgreSQL索引类型的范围，并且在超表（PostgreSQL docs）上创建、更改或删除索引将传播到其所有组成块。

通过SQL CREATE INDEX 命令建立数据索引。例如，

CREATE INDEX ON conditions (location, time DESC);

这可以在将表转换为超表之前或之后进行。

TIP: 对于列通常为NULL的稀疏数据，建议向索引添加WHERE列IS NOT NULL子句（除非您经常搜索丢失的数据）。例如，

CREATE INDEX ON conditions (time DESC, humidity)
  WHERE humidity IS NOT NULL;

这将创建一个更紧凑，更有效的索引。

最佳实践

我们的经验表明，对于时间序列数据，最有用的索引类型取决于您的数据。

对于索引具有离散（有限基数）值的列（例如，您最可能使用“等于”或“不等于”比较器的位置），我们建议使用这样的索引（以示例的超表条件为例）：

CREATE INDEX ON conditions (location, time DESC);

对于所有其他类型的列，即具有连续值的列（例如，您最有可能使用“小于”或“大于”比较器的列），索引应采用以下形式：

CREATE INDEX ON conditions (time DESC, temperature);

在索引中具有时间 DESC 列规范可以按列值和时间进行有效查询。例如，上面定义的索引将优化以下查询：

SELECT * FROM conditions WHERE location = 'garage'
  ORDER BY time DESC LIMIT 10

要理解为什么应该以这种方式定义复合索引，请考虑一个示例，其中包含两个locations（“office和“garage”）以及time和temperature：

(location, time DESC) 的索引将按以下顺序组织：

garage-4
garage-3
garage-2
garage-1
office-3
office-2
office-1

(time DESC, location) 的索引将按以下顺序组织：

4-garage
3-garage
3-office
2-garage
2-office
1-garage
1-office

可以认为索引的btree是从最高有效位开始向下构造的，因此它首先在第一个字符上匹配，然后在第二个上匹配，依此类推，而在上面的示例中，它们方便地显示为两个独立的元组。

现在，使用 WHERE location ='garage' and time > = 1 and time <4 ，使用上面的形式要好得多：给定位置的所有读数都是连续的，因此索引的第一位精确地找到了所有度量从“garage”中提取，然后我们可以使用 time 来进一步缩小所选集合的范围。对于后者，您必须查看所有 time 记录[1,4），然后在每个时间记录中再次找到合适的设备。效率低得多。

另一方面，请考虑是否我们的条件是否要求temperature > 80，尤其是该条件是否匹配大量值。您仍然需要搜索与匹配的所有时间值，但是在每一组中，您的查询还捕获了值的（可能很大）子集，而不仅仅是一个不同的值。

TIP: 如果要将索引定义为UNIQUE或PRIMARY KEY，必须将time列以及分区列（如果存在）定义为索引的一部分。也就是说，使用我们的运行示例，您可以仅在{time，location}字段上定义唯一索引，或包含其他列（例如 temperature ）。也就是说，时序数据中使用UNIQUE索引比传统的关系数据模型中要低得多。

默认索引 Default Indexes

默认情况下TimescaleDB在超表创建时会自动创建时间索引

CREATE INDEX ON conditions (time DESC);

另外，如果create_hypertable命令除了指定时间（例如，location列）外还指定了一个可选的“空间分区”，TimescaleDB将自动创建以下索引：

CREATE INDEX ON conditions (location, time DESC);

这个默认的行为可以在执行 create_hypertable 指令时覆写。

创建触发器 Creating Triggers

TimescaleDB支持全部PostgreSQL触发器，在超表上创建、更改或删除触发器会将这些更改传播到超表的所有组成块中。

在下面的示例中，假设您要创建一个与 conditions 相同结构的新表 error_conditions ，但仅存储被视为错误的记录，其中应用程序通过发送 temperature或 humidity的值 >= 1000 来表示传感器错误。

因此，我们将采用两步。首先，让我们创建一个将错误数据插入第二张表的函数：

CREATE OR REPLACE FUNCTION record_error()
  RETURNS trigger AS $record_error$
BEGIN
 IF NEW.temperature >= 1000 OR NEW.humidity >= 1000 THEN
   INSERT INTO error_conditions
     VALUES(NEW.time, NEW.location, NEW.temperature, NEW.humidity);
 END IF;
 RETURN NEW;
END;
$record_error$ LANGUAGE plpgsql;

其次，创建一个触发器，每当在超表中插入新行时，该触发器将调用此函数。

CREATE TRIGGER record_error
  BEFORE INSERT ON conditions
  FOR EACH ROW
  EXECUTE PROCEDURE record_error();

现在，所有数据都插入到conditions中，但是任何被认为错误的行也将添加到 error_conditions 中。
TimescaleDB支持触发器的全部范围：BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE。有关更多信息，请参见PostgreSQL文档。

添加约束 Adding Constraints

除了引用超表中的值到其他表上的外键约束外，超表支持所有标准PostgreSQL约束类型。在超级表上创建，删除或更改约束将同步到块中，同时还要考虑与约束相关的任何索引。例如，可以按以下方式创建表：

CREATE TABLE conditions (
    time       TIMESTAMPTZ
    temp       FLOAT NOT NULL,
    device_id  INTEGER CHECK (device_id > 0),
    location   INTEGER REFERENCES locations (id),
    PRIMARY KEY(time, device_id)
);
SELECT create_hypertable('conditions', 'time');

该表仅允许使用正的设备ID，非零温度读数，并且将保证每个设备的唯一时间值。它还通过外键约束引用 locations 表中的值。请注意，用于分区的时间列默认情况下不允许 NULL 值。如果缺少，TimescaleDB将自动向这些列添加 NOT NULL 约束。

有关如何管理约束的其他信息，请参见PostgreSQL文档。

半结构化数据使用JSON格式支持 JSON support for semi-structured data

TimescaleDB可以使用PostgreSQL中可用的任何数据类型，包括JSON和JSONB。这些数据类型对于包含用户定义的字段（即，由各个用户定义且随用户而异的字段名称）的数据最有用。我们建议以半结构化方式使用它：

CREATE TABLE metrics (
  time TIMESTAMPTZ,
  user_id INT,
  device_id INT,
  data JSONB
);

上面的模型架构演示了使用JSON时的一些最佳做法：

诸如time，user_id和device_id之类的通用字段被拉到JSONB结构之外并存储为列。这是因为在表列上的字段访问比在JSONB结构内部更有效。存储效率也更高。
我们使用JSONB数据类型（即以二进制格式存储的JSON），而不是JSON数据类型。 JSONB数据类型在存储开销和查找性能上都更优。

TIP: 人们通常将JSON用于稀疏数据，而不是用户定义的数据。对于大多数数据集，我们不建议在TimescaleDB内部使用这种用法（除非数据极其稀疏，例如一行中超过95％的字段为空）。相反，我们建议使用NULLable字段，并在可能的情况下建议在ZFS之类的压缩文件系统上运行。

索引整个JSONB结构 Indexing the entire JSONB structure

如果需要索引JSONB其中的所有字段时，最好使用GIN索引。 PostgreSQL文档很好地描述了JSON数据上可用的GIN索引的不同类型。如有疑问，最好使用默认的GIN运算符，因为它允许更强大的查询：

CREATE INDEX idxgin ON metrics USING GIN (data);

请注意，该索引只会优化WHERE子句使用？，？＆、? |或@>运算符的查询（有关这些运算符的说明，请参见PostgreSQL文档中的此表）。因此，您应该确保适当地构造查询语句。

索引JSONB中的某字段 Indexing individual fields within a JSONB

有时，JSONB列具有公共字段，其值可用于单独索引。这样的索引对于对字段值，多列索引和特殊类型的索引（例如，使用字段值作为postGIS地理类型）的排序操作可能很有用。单个字段值的索引的另一个优点是，它们通常小于整个JSONB字段的GIN索引。要创建这样的索引，通常在访问该字段的表达式上使用部分索引非常有用。例如，

CREATE INDEX idxcpu
  ON metrics(((data->>'cpu')::double precision))
  WHERE data ? 'cpu';

在此示例中，被索引的表达式是将数据JSONB对象强制转换为double型的 cpu 字段。通过存储（小得多的）双精度值而不是字符串，强制类型转换减小了索引的大小。 WHERE子句可确保索引中包含的唯一行是包含 cpu 字段的行（即 data ？'cpu' 返回true）。通过不包括没有 cpu 字段的行，这也可以减少索引的大小。请注意，为了使查询使用索引，它在WHERE子句中包含 data ? 'cpu' 。
上面的表达式可以与多列索引一起使用（例如，添加 time DESC 作为前导列）。但是请注意，要启用仅索引扫描，您需要将数据作为列，而不是完整表达式((data->>'cpu')::double precision)

更改、更新表结构 Altering/updating table schemas

TimescaleDB支持使用 ALTER TABLE 命令来修改超级表的架构。对超表的更改会同步对每个基础块的结构进行更改。
如果此更改需要重写基础数据，则可能存在比较耗时的操作。如果修改是添加一个默认值为NULL的字段（如果未指定DEFAULT子句，则默认值为NULL）；这样的修改是比较快的。你可以在Postgresql的文档中找到详细信息PostgreSQL documentation on ALTER TABLE。

表空间的存储管理 Storage management with tablespaces

管理员可以使用表空间来管理超表的存储。表空间是文件系统上存储数据库对象（例如表和索引）的位置。查看表空间上的标准PostgreSQL文档 (PostgreSQL documentation on tablespaces) 以获取更多信息，包括如何创建表空间。
由于一个超表包含许多块，因此每个块都可以放置在特定的表空间中，从而使该超表可以跨多个磁盘增长。为此，TimescaleDB允许在超表上附加和分离表空间。创建新的块时，运行时将选择超表的附加表空间之一来存储块的数据。因此，典型的用例是当表空间用尽磁盘空间时，将表空间与超表分离，并附加一个具有可用空间的新表空间。可以使用 show_tablespaces 命令查看超级表的附加表空间。

如何为超表块(chunks)分配表空间 How hypertable chunks are assigned tablespaces

可以在多个维度上划分一个超表，但是只有一个维度用于确定分配给特定超表块的表空间。如果超表具有一个或多个哈希分区（“空间”）维，则使用第一个哈希分区维。否则，将使用第一时间维度。这种分配策略确保只要附加到该超表的表空间列表保持不变，就可以将按哈希分区的超表按哈希分区并置在一起的块。模运算用于选择一个表空间，因此分区可能比表空间更多（例如，如果有两个表空间，则第三个分区将使用第一个表空间）。

TIP: 请注意，与超表的分区相比，附加的表空间更多，可能会使一些表空间未使用，直到其中一些表空间被分离或添加了其他分区。对于哈希分区表尤其如此。

仅按时间分区的超表将连续添加新分区，因此将以类似于循环的方式将块分配给表空间。