TimescaleDB | 时序数据库

为啥需要时序数据库

● 关系数据库单表数据量大后查询慢（关系数据库单表数据几千万上亿后查询变慢明显）

● 时序数据特点，高写入并发，写入后很少修改

● 数据压缩、自动清理能力

为啥使用TimescaleDB

● 灵活性：TimescaleDB支持标准SQL，对于习惯SQL的团队来说更容易上手，且能更好地与现有的分析工具和框架集成（我们的业务存在多指标关联分析行转列等需求可以方便处理）。

● 扩展性：作为PostgreSQL的扩展，TimescaleDB继承了其强大的生态系统和可扩展性，适用于从小型到超大规模的数据集（比如大模型需要的向量数据库和搜索场景需要的倒排索引和相关性排序）。

● 运维与学习成本：如果团队对PostgreSQL或Mysql熟悉，转向TimescaleDB的迁移成本相对较低。

● 生产成本：同时支持关系表和时序表，不需要部署两套数据库。

● 局限性：相比原生时序数据库如InfluxDB、TDengine等，大数据量时性能略低，存储占用空间更大。

一些概念

TimescaleDB：本质是Postgresql数据库的扩展，Postgresql原本关系数据库的能力依旧在，可以同时支持时序表和关系表。

分区（partition）：把数据按时间或者其他维度划分成多个物理表（chunk），每个表就是一个分区。

时序表（超表hypertable）：本质上是Postgresql的分区表，相比于分区表提供了自动分区、元数据管理、查询优化、插入优化、数据压缩保留策略、连续聚合能力等。

VACUUM：主要用于清理数据库中的死元组，以及回收空间以提高存储效率和性能。

本地部署

docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=123456 -v /home/timescaledb/data:/var/lib/postgresql/data timescale/timescaledb

客户端访问

可以用DBeaver + PostgreSQL驱动

日常使用

普通表的DML及DDL等操作兼容Postgresql语法。

创建时序表

-- 1. 创建普通表
CREATE TABLE IF NOT EXISTS dm_tag_value (
                              tag_name VARCHAR(200)  NOT NULL ,
                              tag_value VARCHAR(1000) DEFAULT '',
                              time TIMESTAMP  NOT NULL
);
COMMENT ON COLUMN dm_tag_value.tag_name IS '位号名';
COMMENT ON COLUMN dm_tag_value.tag_value IS '位号值';
COMMENT ON COLUMN dm_tag_value.time IS '时间';

-- 2. 创建超表
-- 默认按周分区(字段类型不同默认值不同)
SELECT create_hypertable('dm_tag_value', by_range('time'));

-- 指定分区间隔
SELECT create_hypertable('dm_tag_value', by_range('time', INTERVAL '7 hours'));

-- 修改时间间隔，修改只对未来数据有效（等现有分区结束）
SELECT set_chunk_time_interval('dm_tag_value', INTERVAL '24 hours');

-- 高级能力，时间字段在大字段里面
CREATE TABLE my_table (
   metric_id serial not null,
   data jsonb,
);

CREATE FUNCTION get_time(jsonb) RETURNS timestamptz AS $$
  SELECT ($1->>'time')::timestamptz
$$ LANGUAGE sql IMMUTABLE;

SELECT create_hypertable('my_table', by_range('data', '1 day', 'get_time'));

-- 添加更多分区维度
SELECT add_dimension('m_tag_value', by_hash('tag_name', 4));

查看分区

SELECT show_chunks('dm_tag_value');

-- 根据时间过滤
SELECT show_chunks('dm_tag_value', created_before => INTERVAL '3 months');
SELECT show_chunks('dm_tag_value', older_than => DATE '2017-01-01');

-- 直接查元数据
SELECT *
FROM timescaledb_information.chunks
WHERE hypertable_name = 'dm_tag_value';

查询数据量和空间占用

-- 表数据行数（表大了后count(*)会很慢给，不一定能查出来）
SELECT approximate_row_count('dm_tag_value');

-- 空间占用
SELECT hypertable_size('dm_tag_value');

--更多细节
SELECT * FROM hypertable_detailed_size('dm_tag_value') ORDER BY node_name;
--按分区
SELECT * FROM chunks_detailed_size('dm_tag_value')
  ORDER BY chunk_name, node_name;
  

--获取所有表占用的空间
SELECT hypertable_name, hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass)
  FROM timescaledb_information.hypertables;


-- 查看分区压缩情况
SELECT * FROM hypertable_compression_stats('dm_tag_value');
SELECT pg_size_pretty(after_compression_total_bytes) as total
  FROM hypertable_compression_stats('dm_tag_value');
        

-- 其他方法
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- 按大小排序
SELECT
    table_name,
    total_size,
    table_size,
    indexes_size,
    -- 抽取数值并转换单位为字节数
    CASE
        WHEN total_size LIKE '%kB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint 
        WHEN total_size LIKE '%MB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint * 1024 
        WHEN total_size LIKE '%GB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint * 1024 * 1024 
        ELSE regexp_replace(total_size, '[^0-9]', '', 'g')::bigint
    END AS total_size_k
FROM
(   
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes_size
FROM pg_stat_user_tables where relname like '%_chunk'
)
ORDER BY total_size_k DESC;


--查看未压缩分区容量
select sum(total_size_m) from (
SELECT
    CASE
        WHEN total_size LIKE '%kB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint / 1024
        WHEN total_size LIKE '%MB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint  
        WHEN total_size LIKE '%GB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint * 1024 
        ELSE regexp_replace(total_size, '[^0-9]', '', 'g')::bigint
    END AS total_size_m
FROM
(   
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes_size
FROM pg_stat_user_tables where relname in (SELECT chunk_name
FROM timescaledb_information.chunks
WHERE range_start >= '2024-07-18' AND range_end < '2024-07-20')
))

--查看压缩分区容量
select sum(total_size_m) from (
SELECT
    CASE
        WHEN total_size LIKE '%kB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint / 1024
        WHEN total_size LIKE '%MB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint  
        WHEN total_size LIKE '%GB' THEN regexp_replace(total_size, '[^0-9]', '', 'g')::bigint * 1024 
        ELSE regexp_replace(total_size, '[^0-9]', '', 'g')::bigint
    END AS total_size_m
FROM
(   
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_r