时序数据库 TimescaleDB 安装与使用

在这里插入图片描述

TimescaleDB 是一个时间序列数据库，建立在 PostgreSQL 之上。然而，不仅如此，它还是时间序列的关系数据库。使用 TimescaleDB 的开发人员将受益于专门构建的时间序列数据库以及经典的关系数据库 (PostgreSQL)，所有这些都具有完整的 SQL 支持。本文介绍 TimescaleDB 的 CentOS 7 环境源码编译安装与使用。

01 源码安装

安装 TimescaleDB 之前确保你的机器上已经安装好了 PostgreSQL，并且检查安装的 PG 版本与 TimescaleDB 版本兼容情况：https://docs.timescale.com/self-hosted/latest/upgrades/upgrade-pg/
在这里插入图片描述
源码安装需要使用到 CMake 和 GUN 编译器 gcc 等工具，确保机器已经安装了这些工具

1.1 源码安装 TimescaleDB

  1. 拉取 TimescaleDB 源码
git clone https://github.com/timescale/timescaledb
  1. 进入源码目录并切换分支到指定版本
cd timescaledb
git checkout 2.11.1
  1. 执行 bootstrap 引导构建系统
./bootstrap

遇到报错：PostgreSQL was built without OpenSSL support, which TimescaleDB needs for full compatibility
解决方法：重新编译安装 postgresql，增加 --with-openssl 选项
./configure --prefix=/home/randy/soft/postgresql --with-openssl
或者执行 ./bootstrap -DUSE_OPENSSL=0 不使用 openssl
在这里插入图片描述

  1. 进入上一步生成的 build 目录，编译安装 timescaledb
cd build
make && make install

安装完成之后，就可以在 postgresql 插件目录下看到 timescaledb 了
在这里插入图片描述

1.2 修改 PG 配置

完成 TimescaleDB 安装之后，需要修改 postgresql 配置文件增加 timescaledb 预加载库

  1. 找到 pg 配置文件
$ psql -Upostgres -dpostgres -c "SHOW config_file;"
Password for user postgres:config_file
--------------------------------------------------/home/randy/soft/postgresql/data/postgresql.conf
(1 row)
  1. 修改 pg 配置文件
vim /home/randy/soft/postgresql/data/postgresql.conf
# 做如下修改
shared_preload_libraries = 'timescaledb'

修改完成之后重启 pg

1.3 安装 TimescaleDB 插件

  1. 执行如下命令创建 timescaledb 插件
CREATE EXTENSION IF NOT EXISTS timescaledb;
  1. 完成插件创建之后可以使用 \dx 命令查看插件是否成功安装
testdb=# \dxList of installed extensionsName     | Version |   Schema   |                                      Description
-------------+---------+------------+---------------------------------------------------------------------------------------plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural languagetimescaledb | 2.11.1  | public     | Enables scalable inserts and complex queries for time-series data (Community Edition)
(2 rows)

在这里插入图片描述
至此，postgresql + timescaledb 时序数据库就安装完成了

  1. 不想再用这个插件的话，使用如下命令删除插件
DROP EXTENSION IF EXISTS timescaledb;

02 Timescale 基础使用

2.1 创建超表 hypertables

  1. CREATE 创建一个 pg 普通表
testdb=# CREATE TABLE conditions (time        TIMESTAMPTZ       NOT NULL,location    TEXT              NOT NULL,device      TEXT              NOT NULL,temperature DOUBLE PRECISION  NULL,humidity    DOUBLE PRECISION  NULL
);
CREATE TABLE

然后使用 create_hypertable 函数将普通表转化成 hypertable

testdb=# SELECT create_hypertable('conditions', 'time');create_hypertable
-------------------------(1,public,conditions,t)
(1 row)
  1. INSERT 向 hypertable 中插入数据
testdb=# INSERT INTO conditions(time, location, device, temperature, humidity)
SELECT now(), to_char(i, 'FM0000'), to_char(i, 'FM00000'), random()*i, random()*i FROM generate_series(1,10000) i;
INSERT 0 10000
  1. SELECT 查询 hypertable 数据
testdb=# SELECT * FROM conditions ORDER BY time DESC LIMIT 10;time              | location | device |     temperature     |       humidity
-------------------------------+----------+--------+---------------------+----------------------2023-08-03 12:37:00.345665+08 | 0001     | 00001  | 0.24349980974765373 |   0.59486877297972642023-08-03 12:37:00.345665+08 | 0002     | 00002  |  1.8149739913052656 |    0.6162655023691672023-08-03 12:37:00.345665+08 | 0003     | 00003  |   2.400422475569293 |   0.68700570944077912023-08-03 12:37:00.345665+08 | 0004     | 00004  |   2.639553072461581 |   1.94090348497051932023-08-03 12:37:00.345665+08 | 0005     | 00005  |   2.127623537273031 |   3.88715035379826552023-08-03 12:37:00.345665+08 | 0006     | 00006  |  2.3469833801156312 |    4.4115299335274262023-08-03 12:37:00.345665+08 | 0007     | 00007  |  1.0073460031664823 | 0.0168279977406164962023-08-03 12:37:00.345665+08 | 0008     | 00008  |   7.023014897212306 |   0.86792935440220732023-08-03 12:37:00.345665+08 | 0009     | 00009  |   6.744935559863428 |    6.3129489812979682023-08-03 12:37:00.345665+08 | 0010     | 00010  |   9.279208258323166 |    9.451548543523778
(10 rows)
  1. UPDATE 更新 hypertable 数据

更新数据的语句与标准 SQL 一致，如下示例修改指定记录的值

testdb=# SELECT * FROM conditions
WHERE time = '2023-08-03 12:37:00.345665+08' AND location = '0001';time              | location | device |     temperature     |      humidity
-------------------------------+----------+--------+---------------------+--------------------2023-08-03 12:37:00.345665+08 | 0001     | 00001  | 0.24349980974765373 | 0.5948687729797264
(1 row)testdb=# UPDATE conditions SET temperature = 70.2, humidity = 50.0
WHERE time = '2023-08-03 12:37:00.345665+08' AND location = '0001';
UPDATE 1
testdb=# SELECT * FROM conditions
WHERE time = '2023-08-03 12:37:00.345665+08' AND location = '0001';time              | location | device | temperature | humidity
-------------------------------+----------+--------+-------------+----------2023-08-03 12:37:00.345665+08 | 0001     | 00001  |        70.2 |       50
(1 row)

也可以修改该指定范围内的多行记录，如下例子中修改时间 2023-08-03 12:37:00 到 2023-08-03 12:37:05 这 5 秒内的数据如下

testdb=# SELECT * FROM conditions ORDER BY time DESC LIMIT 10;time              | location | device |    temperature     |       humidity
-------------------------------+----------+--------+--------------------+----------------------2023-08-03 12:37:00.345665+08 | 0002     | 00002  | 1.8149739913052656 |    0.6162655023691672023-08-03 12:37:00.345665+08 | 0003     | 00003  |  2.400422475569293 |   0.68700570944077912023-08-03 12:37:00.345665+08 | 0004     | 00004  |  2.639553072461581 |   1.94090348497051932023-08-03 12:37:00.345665+08 | 0005     | 00005  |  2.127623537273031 |   3.88715035379826552023-08-03 12:37:00.345665+08 | 0006     | 00006  | 2.3469833801156312 |    4.4115299335274262023-08-03 12:37:00.345665+08 | 0007     | 00007  | 1.0073460031664823 | 0.0168279977406164962023-08-03 12:37:00.345665+08 | 0008     | 00008  |  7.023014897212306 |   0.86792935440220732023-08-03 12:37:00.345665+08 | 0009     | 00009  |  6.744935559863428 |    6.3129489812979682023-08-03 12:37:00.345665+08 | 0010     | 00010  |  9.279208258323166 |    9.4515485435237782023-08-03 12:37:00.345665+08 | 0011     | 00011  |  1.994685462372594 |    7.361677356344085
(10 rows)testdb=# UPDATE conditions SET temperature = temperature + 0.1
WHERE time >= '2023-08-03 12:37:00' AND time < '2023-08-03 12:37:05';
UPDATE 10000
testdb=# SELECT * FROM conditions ORDER BY time DESC LIMIT 10;time              | location | device |    temperature     |       humidity
-------------------------------+----------+--------+--------------------+----------------------2023-08-03 12:37:00.345665+08 | 0002     | 00002  | 1.9149739913052657 |    0.6162655023691672023-08-03 12:37:00.345665+08 | 0003     | 00003  |  2.500422475569293 |   0.68700570944077912023-08-03 12:37:00.345665+08 | 0004     | 00004  |  2.739553072461581 |   1.94090348497051932023-08-03 12:37:00.345665+08 | 0005     | 00005  | 2.2276235372730313 |   3.88715035379826552023-08-03 12:37:00.345665+08 | 0006     | 00006  | 2.4469833801156313 |    4.4115299335274262023-08-03 12:37:00.345665+08 | 0007     | 00007  | 1.1073460031664824 | 0.0168279977406164962023-08-03 12:37:00.345665+08 | 0008     | 00008  |  7.123014897212306 |   0.86792935440220732023-08-03 12:37:00.345665+08 | 0009     | 00009  |  6.844935559863428 |    6.3129489812979682023-08-03 12:37:00.345665+08 | 0010     | 00010  |  9.379208258323166 |    9.4515485435237782023-08-03 12:37:00.345665+08 | 0011     | 00011  |  2.094685462372594 |    7.361677356344085
(10 rows)
  1. Upsert 插入新数据或更新已存在的数据

Upsert 只能在存在唯一索引或唯一约束的表中生效，可以使用 ALTER TABLE … ADD CONSTRAINT … UNIQUE 语句为已经存在的 hypertable 创建唯一约束

testdb=# ALTER TABLE conditionsADD CONSTRAINT conditions_time_locationUNIQUE (time, location);
ALTER TABLE
testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |
Indexes:"conditions_time_location" UNIQUE CONSTRAINT, btree ("time", location)"conditions_time_idx" btree ("time")
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)

插入新数据或更新已存在的数据使用 INSERT INTO … VALUES … ON CONFLICT … DO UPDATE 语句实现

testdb=# SELECT * FROM conditions
WHERE time = '2023-08-03 12:37:00.345665+08' AND location = '0001';time              | location | device | temperature | humidity
-------------------------------+----------+--------+-------------+----------2023-08-03 12:37:00.345665+08 | 0001     | 00001  |        70.3 |       50
(1 row)testdb=# INSERT INTO conditions
VALUES ('2023-08-03 12:37:00.345665+08', '0001', '00001', 70.2, 50.1)
ON CONFLICT (time, location) DO UPDATESET temperature = excluded.temperature,humidity = excluded.humidity;
INSERT 0 1
testdb=# SELECT * FROM conditions
WHERE time = '2023-08-03 12:37:00.345665+08' AND location = '0001';time              | location | device | temperature | humidity
-------------------------------+----------+--------+-------------+----------2023-08-03 12:37:00.345665+08 | 0001     | 00001  |        70.2 |     50.1
(1 row)

也可以只执行插入操作，如果记录已存在则直接跳过，该操作使用 INSERT INTO … VALUES … ON CONFLICT DO NOTHING 语句实现

testdb=# INSERT INTO conditionsVALUES (NOW(), 'new', '00001', 70.1, 50.0)ON CONFLICT DO NOTHING;
INSERT 0 1
testdb=# SELECT * FROM conditions WHERE location = 'new';time              | location | device | temperature | humidity
-------------------------------+----------+--------+-------------+----------2023-08-04 10:33:16.698018+08 | new      | 00001  |        70.1 |       50
(1 row)testdb=# INSERT INTO conditions
VALUES ('2023-08-04 10:33:16.698018+08', '0001', '00001', 71, 50.1)
ON CONFLICT DO NOTHING;
INSERT 0 1
testdb=# SELECT * FROM conditions WHERE location = 'new';time              | location | device | temperature | humidity
-------------------------------+----------+--------+-------------+----------2023-08-04 10:33:16.698018+08 | new      | 00001  |        70.1 |       50
(1 row)
  1. DELETE 删除数据

删除数据的语句与标准 SQL 一致 DELETE FROM …

testdb=# SELECT * FROM conditions WHERE location = 'new';time              | location | device | temperature | humidity
-------------------------------+----------+--------+-------------+----------2023-08-04 10:33:16.698018+08 | new      | 00001  |        70.1 |       50
(1 row)testdb=# DELETE FROM conditions WHERE location = 'new';
DELETE 1
testdb=# SELECT * FROM conditions WHERE location = 'new';time | location | device | temperature | humidity
------+----------+--------+-------------+----------
(0 rows)
  1. DROP 删除 hypertable 只需要使用 PG 命令删除基表（普通表）即可
testdb=# DROP TABLE conditions;
DROP TABLE

2.2 编辑超表 hypertable

hypertable 的修改和标准 SQL 一致，可以使用 ALTER TABLE 相关语句实现：http://www.postgres.cn/docs/12/ddl-alter.html

  1. 增删列

增加列 column 时，如果没有默认值即 NULL 时，新增操作可以很快完成；但是默认值为非空的时候，就需要花大量时间填充所有分区中每条记录新增列的值。

另外，无法对已经被压缩的 hypertable 进行增删列操作，如果要进行该操作需要先解压

testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |
Indexes:"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)testdb=# ALTER TABLE conditions
ADD COLUMN test_column DOUBLE PRECISION NULL;
ALTER TABLE
testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |test_column | double precision         |           |          |
Indexes:"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)testdb=# ALTER TABLE conditions
DROP COLUMN test_column;
ALTER TABLE
testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |
Indexes:"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)
  1. 重命名列或表
testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |test_column | double precision         |           |          |
Indexes:"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)testdb=# ALTER TABLE conditions RENAME COLUMN test_column TO ts_column;
ALTER TABLE
testdb=# ALTER TABLE conditions RENAME TO weather;
ALTER TABLE
testdb=# \d conditions
Did not find any relation named "conditions".
testdb=# \d weatherTable "public.weather"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |ts_column   | double precision         |           |          |
Indexes:"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON weather FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)

2.3 修改 hypertable 分区间隔

  1. 普通表转化成 hypertable 时指定分区时间间隔 chunk_time_interval

如果创建 hypertable 是没有指定分区间隔，默认值是 7 天；可以通过查询 _timescaledb.catalog 查看分区间隔的当前设置

testdb=# CREATE TABLE conditions (time        TIMESTAMPTZ       NOT NULL,location    TEXT              NOT NULL,device      TEXT              NOT NULL,temperature DOUBLE PRECISION  NULL,humidity    DOUBLE PRECISION  NULL
);
CREATE TABLE
testdb=# SELECT create_hypertable('conditions', 'time');create_hypertable
-------------------------(2,public,conditions,t)
(1 row)testdb=# SELECT h.table_name, c.interval_lengthFROM _timescaledb_catalog.dimension cJOIN _timescaledb_catalog.hypertable hON h.id = c.hypertable_id;table_name | interval_length
------------+-----------------conditions |    604800000000
(1 row)

可以看到没有指定 chunk_time_interval 的 hypertable 分区间隔为 604800000000 / 1000 / 1000 / 60 / 60 / 24 = 7，interval_length 的单位为微秒（microsecond）

创建 hypertable 时我们通过 chunk_time_interval 指定分区间隔为 1 天

testdb=# DROP TABLE conditions;
DROP TABLE
testdb=# CREATE TABLE conditions (time        TIMESTAMPTZ       NOT NULL,location    TEXT              NOT NULL,device      TEXT              NOT NULL,temperature DOUBLE PRECISION  NULL,humidity    DOUBLE PRECISION  NULL
);
CREATE TABLE
testdb=# SELECT create_hypertable('conditions','time',chunk_time_interval => INTERVAL '1 day'
);create_hypertable
-------------------------(3,public,conditions,t)
(1 row)testdb=# SELECT h.table_name, c.interval_lengthFROM _timescaledb_catalog.dimension cJOIN _timescaledb_catalog.hypertable hON h.id = c.hypertable_id;table_name | interval_length
------------+-----------------conditions |     86400000000
(1 row)
  1. 通过 set_chunk_time_interval 函数修改 hypertable 分区间隔

需要注意的是修改分区时间间隔只会再新建的分区中生效，已经创建的分区不会受到影响。所以如果创建了一个很长时间的分区间隔例如 1 年，然后你想修改成一个更小的分区间隔，那这个更小间隔只会在一年后生效了，这时候只能新建一个表设置分区间隔，然后迁移数据了。

testdb=# SELECT set_chunk_time_interval('conditions', INTERVAL '12 hours');set_chunk_time_interval
-------------------------(1 row)testdb=# SELECT h.table_name, c.interval_lengthFROM _timescaledb_catalog.dimension cJOIN _timescaledb_catalog.hypertable hON h.id = c.hypertable_id;table_name | interval_length
------------+-----------------conditions |     43200000000
(1 row)

2.4 hypertable 创建索引

在一个 hypertable 中创建索引分为两步：

  1. 确定该超表的分区列有哪些，其中 time 列是所有 hypertable 表的分区列，所以创建索引必须包含该列；另外，在创建 hypertable 表时可以通过 partitioning_column 字段指定空间分区列。
  2. 创建的索引组合必须包括所有分区列，在此基础上增加其他列
  1. 创建索引

hypertable 中索引的创建仍是使用标准的 SQL 语句 CREATE INDEX / CREATE UNIQUE INDEX，例如，如下例子中在 conditions 超表的 time 和 device 列上建立索引 idx_device_time

testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |
Indexes:"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)testdb=# CREATE UNIQUE INDEX idx_device_time
ON conditions(device, time);
CREATE INDEX
testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |
Indexes:"idx_device_time" UNIQUE, btree (device, "time")"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)

idx_device_time 索引中包含了所有分区列 time，而 device 不是分区列，如果创建的索引组合不包含分区列，会抛出错误，如下 idx_device 索引仅建立在 device 列上

testdb=# CREATE UNIQUE INDEX idx_device
ON conditions(device);
ERROR:  cannot create a unique index without the column "time" (used in partitioning)
testdb=#
  1. 删除索引

hypertable 中删除索引仍是使用标准的 SQL 语句 DROP INDEX，删除 conditions 超表中的索引 idx_device_time

需要注意的是DBMS为主键约束和唯一约束自动创建的索引是无法删除的

testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |
Indexes:"idx_device_time" UNIQUE, btree (device, "time")"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)testdb=# DROP INDEX idx_device_time;
DROP INDEX
testdb=# \d conditionsTable "public.conditions"Column    |           Type           | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+---------time        | timestamp with time zone |           | not null |location    | text                     |           | not null |device      | text                     |           | not null |temperature | double precision         |           |          |humidity    | double precision         |           |          |
Indexes:"conditions_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON conditions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 1 (Use \d+ to list them.)
  1. 已建索引的普通表转化为 hypertable，空间分区列 partitioning_column 必须包含在已建的索引中

例如，我们在普通表 basic_table 中已经在 device_id 和 time 列上建立了索引 idx_deviceid_time，那么创建以该表为基表的 hypertable 的分区列需要包含在索引列中，即分区列仅能为 time 与 device_id 的组合。

下例中，将已经创建好索引的 basic_table 表转化为仅以 time 为分区列的 hypertable

testdb=# CREATE TABLE basic_table(time TIMESTAMPTZ,user_id BIGINT,device_id BIGINT,value FLOAT
);
CREATE TABLE
testdb=# CREATE UNIQUE INDEX idx_deviceid_timeON basic_table(device_id, time);
CREATE INDEX
testdb=# \d basic_tableTable "public.basic_table"Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------time      | timestamp with time zone |           |          |user_id   | bigint                   |           |          |device_id | bigint                   |           |          |value     | double precision         |           |          |
Indexes:"idx_deviceid_time" UNIQUE, btree (device_id, "time")testdb=# SELECT * from create_hypertable('basic_table', 'time');
NOTICE:  adding not-null constraint to column "time"
DETAIL:  Time dimensions cannot have NULL values.hypertable_id | schema_name | table_name  | created
---------------+-------------+-------------+---------4 | public      | basic_table | t
(1 row)testdb=# \d basic_tableTable "public.basic_table"Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------time      | timestamp with time zone |           | not null |user_id   | bigint                   |           |          |device_id | bigint                   |           |          |value     | double precision         |           |          |
Indexes:"idx_deviceid_time" UNIQUE, btree (device_id, "time")"basic_table_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON basic_table FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()

也可以将已经创建好索引的 basic_table 表转化为以 time 和 device_id 为分区列的 hypertable

testdb=# SELECT * FROM create_hypertable('basic_table','time',partitioning_column => 'device_id',number_partitions => 4
);
NOTICE:  adding not-null constraint to column "time"
DETAIL:  Time dimensions cannot have NULL values.hypertable_id | schema_name | table_name  | created
---------------+-------------+-------------+---------5 | public      | basic_table | t
(1 row)testdb=# \d basic_tableTable "public.basic_table"Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------time      | timestamp with time zone |           | not null |user_id   | bigint                   |           |          |device_id | bigint                   |           |          |value     | double precision         |           |          |
Indexes:"idx_deviceid_time" UNIQUE, btree (device_id, "time")"basic_table_time_idx" btree ("time" DESC)
Triggers:ts_insert_blocker BEFORE INSERT ON basic_table FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()

但是，我们无法将不在索引组合中的 user_id 作为创建 hypertable 时的分区列

要解决这个问题，要确保在转化为超表之前建立的所有索引都要包含 hypertable 的分区列，所以将已存在的索引中加入 user_id 即可（重建索引可以重建一个索引，然后删除老的索引，避免使用 reindex 重建索引，因为该过程是阻塞的，一般大表不建议使用这个命令 ）

testdb=# \d basic_tableTable "public.basic_table"Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------time      | timestamp with time zone |           |          |user_id   | bigint                   |           |          |device_id | bigint                   |           |          |value     | double precision         |           |          |
Indexes:"idx_deviceid_time" UNIQUE, btree (device_id, "time")testdb=# SELECT * FROM create_hypertable('basic_table','time',partitioning_column => 'user_id',number_partitions => 4
);
NOTICE:  adding not-null constraint to column "time"
DETAIL:  Time dimensions cannot have NULL values.
ERROR:  cannot create a unique index without the column "user_id" (used in partitioning)
testdb=# DROP INDEX idx_deviceid_time;
DROP INDEX
testdb=# CREATE UNIQUE INDEX idx_userid_deviceid_timeON basic_table(user_id, device_id, time);
CREATE INDEX
testdb=# \d basic_tableTable "public.basic_table"Column   |           Type           | Collation | Nullable | Default
-----------+--------------------------+-----------+----------+---------time      | timestamp with time zone |           |          |user_id   | bigint                   |           |          |device_id | bigint                   |           |          |value     | double precision         |           |          |
Indexes:"idx_userid_deviceid_time" UNIQUE, btree (user_id, device_id, "time")testdb=# SELECT * FROM create_hypertable('basic_table','time',partitioning_column => 'user_id',number_partitions => 4
);
NOTICE:  adding not-null constraint to column "time"
DETAIL:  Time dimensions cannot have NULL values.hypertable_id | schema_name | table_name  | created
---------------+-------------+-------------+---------12 | public      | basic_table | t
(1 row)

如果文章对你有帮助，欢迎一键三连 👍 ⭐️ 💬 。如果还能够点击关注，那真的是对我最大的鼓励 🔥 🔥 🔥 。

参考资料

https://docs.timescale.com/self-hosted/latest/install/installation-source/
https://docs.timescale.com/use-timescale/latest/hypertables/create/
PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等-阿里云开发者社区
https://zhuanlan.zhihu.com/p/57120174

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/131916.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

数据库与身份认证

数据库与身份认证

1. 数据库的基本概念 1.1 什么是数据库 数据库&#xff08;database&#xff09;是用来组织、存储和管理数据的仓库。 当今世界是一个充满着数据的互联网世界&#xff0c;充斥着大量的数据。数据的来源有很多&#xff0c;比如出行记录、消费记录、浏览的网页、发送的消息…
阅读更多...
防火墙(Firewall)

防火墙(Firewall)

目录 一、概述 二、iptables 三、iptable的用法 一、概述 防火墙的作用 用于保护内网安全的一种设备 依据规则进行防护 用户定义规则 允许或拒绝外部用户访问 防火墙分类 逻辑上划分&#xff0c;防火墙可以大体分为主机防火墙和网络防火墙主机防火墙&#xff1a;针对…
阅读更多...
Redis缓存设计与性能优化

Redis缓存设计与性能优化

多级缓存架构 缓存设计 缓存穿透 缓存穿透是指查询一个根本不存在的数据&#xff0c; 缓存层和存储层都不会命中&#xff0c; 通常出于容错的考虑&#xff0c; 如果从存储层查不到数据则不写入缓存层。缓存穿透将导致不存在的数据每次请求都要到存储层去查询&#xff0c; 失去…
阅读更多...
WebDAV之π-Disk派盘 + BubbleUPnP

WebDAV之π-Disk派盘 + BubbleUPnP

BubbleUPnP是一款功能强大的Android播放器，支持UPnP/DLNA多屏互动。它可以将手机内容投屏到电视大屏上，与家人和朋友一起共享。此外，BubbleUPnP还提供了丰富的音乐和影视资源，您可以在线搜索并播放喜欢的内容。 以下是BubbleUPnP的一些主要特点： 1. 支持Chromecast和转码…
阅读更多...
在PHP8中向数组添加元素-PHP8知识详解

在PHP8中向数组添加元素-PHP8知识详解

在php8中向数组添加元素有多种方法&#xff0c;在这里主要讲解几个常用的方法&#xff1a;使用方括号[]添加元素、使用array_unshift()函数&#xff0c;向数组的头部添加元素、使用array_push()函数&#xff0c;向数组的尾部添加元素、使用array_splice()函数添加元素。 1、使用…
阅读更多...
【新版】系统架构设计师 - 软件架构设计＜SOA与微服务＞

【新版】系统架构设计师 - 软件架构设计＜SOA与微服务＞

个人总结&#xff0c;仅供参考&#xff0c;欢迎加好友一起讨论 架构 - 软件架构设计&#xff1c;SOA与微服务&#xff1e; 考点摘要 面向服务SOA&#xff08;★★★★&#xff09;微服务&#xff08;★★★★&#xff09; 基于/面向服务的&#xff08;SOA&#xff09; 在SO…
阅读更多...
【深度学习-注意力机制attention 在seq2seq中应用】

【深度学习-注意力机制attention 在seq2seq中应用】

注意力机制 为什么需要注意力机制attention机制的架构总体设计一、attention本身实现评分函数 attention在网络模型的应用-Bahdanau 注意力加性注意力代码实现 为什么需要注意力机制 这是一个普通的seq2seq结构&#xff0c;用以实现机器对话&#xff0c;Encoder需要把一个输入的…
阅读更多...
Linux下的系统编程——信号（十一）

Linux下的系统编程——信号（十一）

前言&#xff1a; 信号在我们的生活中随处可见&#xff0c; 如&#xff1a;古代战争中摔杯为号&#xff1b;现代战争中的信号弹&#xff1b;体育比赛中使用的信号枪...... 他们都有共性&#xff0c;信号是信息的载体&#xff0c;Linux/UNIX 环境下&#xff0c;古老、经典的通信…
阅读更多...
SpringBoot系列(12)：SpringBoot集成log4j2日志配置

SpringBoot系列(12)：SpringBoot集成log4j2日志配置

最近项目上有使用到log4j2日志模板配置&#xff0c;本文简单总结一下之前的学习笔记&#xff0c;如有纰漏之处&#xff0c;请批评指正。 1. log4j2日志依赖 使用log4j2日志模板时&#xff0c;需要引入相关依赖&#xff0c;下边的两种依赖方式均可。 1.1 使用sl4j依赖时 <…
阅读更多...
Mapbox加载arcgis的底图

Mapbox加载arcgis的底图

成果图 这种底图基本上都是按照raster来加载的&#xff0c;主要就是知道地址了&#xff0c;拼参数 具体参数请参考官网 https://developers.arcgis.com/rest/services-reference/enterprise/export-map.htm 源码 我的服务列表是这样的 http://XXXX:XXXX/arcgis/rest/services/…
阅读更多...
电子游戏冷知识

电子游戏冷知识

电子游戏一直在试图用技术还原一个真实或虚幻的世界&#xff0c;并在其中演绎和倾诉人类种种的情感和欲望。 对信息技术发展的贡献 游戏推动了芯片、网络、VR/AR等领域的技术进步和创新。根据中科院的研究报告&#xff0c;游戏技术对芯片产业的科技进步贡献率是14.9%&#xff…
阅读更多...
Android13 大屏设备底部显示TaskBar并NavagatonBar居右

Android13 大屏设备底部显示TaskBar并NavagatonBar居右

Android 13大屏设备时底下显示任务栏以及虚拟按键靠右的问题&#xff0c; 当前需求是去掉底部任务栏的显示&#xff0c;并把虚拟按键导航栏居中显示。 修改前的效果&#xff1a; 修改后的效果&#xff1a; 通过查看源码逻辑&#xff0c;可以发现只需把isTablet相关的逻辑和…
阅读更多...
Vue3路由

Vue3路由

文章目录 Vue3路由1. 载入vue-router 库2. 实例2.1 Vue.js vue-router 实现单页应用2.2 router-link创建链接2.3 router-view显示与url对应组件2.4 <router-link> 相关属性 Vue3路由 1. 载入vue-router 库 Vue.js 路由需要载入vue-router 库 安装直接下载地址&#xf…
阅读更多...
【陕西理工大学-数学软件实训】数学实验报告（8）（数值微积分与方程数值求解）

【陕西理工大学-数学软件实训】数学实验报告（8）（数值微积分与方程数值求解）

目录 一、实验目的 二、实验要求 三、实验内容与结果 四、实验心得 一、实验目的 1. 掌握求数值导数和数值积分的方法。 2. 掌握代数方程数值求解的方法。 3. 掌握常微分方程数值求解的方法。 二、实验要求 1. 根据实验内容&#xff0c;编写相应的MATLAB程序&#xff0c…
阅读更多...
阿里云服务器部署安装hadoop与elasticsearch踩坑笔记

阿里云服务器部署安装hadoop与elasticsearch踩坑笔记

2023-09-12 14:00——2023.09.13 20:06 目录 00、软件版本 01、阿里云服务器部署hadoop 1.1、修改四个配置文件 1.1.1、core-site.xml 1.1.2、hdfs-site.xml 1.1.3、mapred-site.xml 1.1.4、yarn-site.xml 1.2、修改系统/etc/hosts文件与系统变量 1.2.1、修改主机名解…
阅读更多...
【案例教学】华为云API对话机器人的魅力—体验AI垃圾分类机器人

【案例教学】华为云API对话机器人的魅力—体验AI垃圾分类机器人

云服务、API、SDK&#xff0c;调试&#xff0c;查看&#xff0c;我都行 阅读短文您可以学习到&#xff1a;人工智能AI自言语言的情感分析、文本分词、文本翻译 1 IntelliJ IDEA 之API插件介绍 API插件支持 VS Code IDE、IntelliJ IDEA等平台、以及华为云自研 CodeArts IDE&a…
阅读更多...
16G FC SFP+ SW光模块应用解析

16G FC SFP+ SW光模块应用解析

随着云计算、大数据和物联网等技术的迅猛发展&#xff0c;数据传输速率不断提高。传统的铜缆传输面临带宽瓶颈和信号衰减等问题&#xff0c;而光纤传输凭借其高带宽、低损耗等优势成为了现代通信的主要选择。易天光通信的16G SFP SW 多模光模块作为高性能光纤传输设备&#xff…
阅读更多...
数据分析三剑客之Pandas

数据分析三剑客之Pandas

1.引入 前面一篇文章我们介绍了numpy&#xff0c;但numpy的特长并不是在于数据处理&#xff0c;而是在它能非常方便地实现科学计算&#xff0c;所以我们日常对数据进行处理时用的numpy情况并不是很多&#xff0c;我们需要处理的数据一般都是带有列标签和index索引的&#xff0…
阅读更多...
【PHY】3GPP UE能力类别的变化

【PHY】3GPP UE能力类别的变化

博主未授权任何人或组织机构转载博主任何原创文章&#xff0c;感谢各位对原创的支持&#xff01; 博主链接 本人就职于国际知名终端厂商&#xff0c;负责modem芯片研发。 在5G早期负责终端数据业务层、核心网相关的开发工作&#xff0c;目前牵头6G算力网络技术标准研究。 博客…
阅读更多...
I2C总线协议

I2C总线协议

什么是I2C I2C&#xff08;Inter-Integrated Circuit&#xff09;&#xff0c;也可以叫IIC、I2C&#xff0c;译作集成电路总线&#xff0c;是两线式串行通信总线&#xff0c;用于设备间的通讯等&#xff0c;标准情况下最高传送速率达100Kbps。顾名思义&#xff0c;I2C通讯只需…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023