PostgreSQL插件TimeScaleDB基本使用
PostgreSQL插件TimeScaleDB基本使用 - 岛dao - 博客园
TimescaleDB：快速入门教程很多时候，我们需要快速的处理一些与时间相关性比较高的海量数据，比如IOT、监控、事件 - 掘金
Timescale 文档 | 执行高级分析查询 - TimescaleDB 时序数据库 官方文档
--下载timescaledb的安装文件，安装之后pg实例要重启
--本文件在postgresql18.3.1和timescaledb2.24.0上测试通过（Windows平台）
--安装timescaledb扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;
--查看timescaledb扩展的安装情况
SELECT extname AS name, extversion AS version, n.nspname AS schema FROM pg_extension e JOIN pg_namespace n ON e.extnamespace = n.oid;
：超表默认是行存储。但是，通过add_columnstore_policy这类策略，你可以轻松地让系统在数据变冷后，自动、无缝地将其转换为列式存储。你无需关心转换的具体过程，TimescaleDB的后台作业会替你完成。
--创建超表
CREATE TABLE hypertable_example(
time TIMESTAMPTZ,
user_id BIGINT,
device_id BIGINT,
value FLOAT
) WITH (
tsdb.hypertable,
tsdb.partition_column='time', --分区列，时间
tsdb.segmentby = 'device_id', --经常查询的列，一般为设备编号等
tsdb.orderby = 'time DESC', --分区时排序列
tsdb.chunk_interval='30 days' --分区大小
);
-- 创建普通表，不带主键，ts时间字段不要时区
CREATE TABLE p_2025
(
id bigint NOT NULL,
mn character varying(50) COLLATE pg_catalog."default",
nh3 numeric(18,3),
h2s numeric(18,3),
voc numeric(18,3),
so2 numeric(18,3),
wd numeric(18,3),
sd numeric(18,3),
qy numeric(18,3),
fs numeric(18,3),
fx numeric(18,3),
ts timestamp without time zone
)
-- 把普通表p_2025转为超表，指定时间间隔对超级表进行分区，即按时间分成多个文件，粒度看数据量大小，30天可能比较好，或1g大小比较好。day和days都可以
SELECT create_hypertable( 'p_2025', by_range('ts', INTERVAL '30 days') );
SELECT create_hypertable( 'p_2025', by_range('ts')); 默认7天，即604800000000微秒
--如果上一步设置的粒度不合适，下面命令可修改为90天
SELECT set_chunk_time_interval('p_2025', INTERVAL '90 days');
--查询超表的chunk粒度，单位是微秒
SELECT h.table_name, c.interval_length FROM _timescaledb_catalog.hypertable h
JOIN _timescaledb_catalog.dimension c ON h.id = c.hypertable_id
WHERE h.table_name = 'p_2025_t';
--查询表存储有几个chunk了，以及各chunk的大小，空表时返回0行
SELECT * FROM timescaledb_information.chunks WHERE hypertable_name = 'p_2025';
一般表不用创建主键，
当你用 create_hypertable 创建超表时，TimescaleDB 会自动在时间列上创建一个索引。这能确保所有基于时间的范围查询（如 WHERE time > now() - interval '1 day'）都有一个基本的性能保障。
--查询表的chunk列表
SELECT * FROM timescaledb_information.chunks WHERE hypertable_name = 'p_2025_t'
--查询chunk的大小，根据chunk的名称
SELECT pg_size_pretty(pg_total_relation_size('_timescaledb_internal._hyper_4_11_chunk'));
--查询一个表所有chunk的大小
SELECT pg_size_pretty(pg_total_relation_size('_timescaledb_internal.'|| chunk_name))
FROM timescaledb_information.chunks WHERE hypertable_name = 'p_2025_t'
--查询是否启用了压缩
SELECT * FROM timescaledb_information.hypertable_compression_settings WHERE hypertable::text LIKE '%p_2025_t%';
--查询压缩配置
SELECT hypertable_name, compression_enabled FROM timescaledb_information.hypertables WHERE hypertable_name = 'p_2025_t';
--注意：备份恢复后，压缩策略job可能要重新配置
--启用压缩，选择mn设备编号作为压缩字段，看具体业务，ts是时间列即分区列
ALTER TABLE p_2025_t SET ( timescaledb.compress, timescaledb.compress_segmentby = 'mn', -- 设备标识
timescaledb.compress_orderby = 'ts DESC' ) -- 时间排序
-- 自动压缩 30 天前的数据，压缩之后更新和删除需解压，故自动压缩旧数据，新数据是热的
SELECT add_compression_policy('p_2025_t', INTERVAL '30 days');
-- 查看压缩策略
SELECT * FROM timescaledb_information.jobs WHERE hypertable_name = 'p_2025_t';
--查询压缩前后的大小
SELECT chunk_name, pg_size_pretty(before_compression_total_bytes) as before, pg_size_pretty(after_compression_total_bytes) as after,
round(100 * (1 - after_compression_total_bytes::numeric / nullif(before_compression_total_bytes, 0)), 2) as ratio
FROM chunk_compression_stats('p_2025_t');
时间桶函数：按小时进行汇总，查询nh3的合计值，h2s的平均值
下面是按1小时统计，也可以2 hour，也可day，week，moth，year等
SELECT time_bucket('1 hour', ts) AS date, sum(nh3) AS nh3, avg(h2s) as h2s
FROM "public"."p_2025t"
where mn='47268246100163' and ts >= '2025-01-01' AND ts < '2025-01-02'
GROUP BY date ORDER BY date
时间桶函数：按小时进行汇总，查询nh3的合计值，h2s的平均值，如果指定时间没有数据，则补null
SELECT time_bucket_gapfill ('1 hour', ts) AS date, sum(nh3) AS nh3, avg(h2s) as h2s
FROM "public"."p_2025t"
where mn='47268246100163' and ts >= '2025-01-01' AND ts < '2025-01-02'
GROUP BY date ORDER BY date
--取每个设备mn的最后一条数据，所有mn号保存在另一表t_mn中
SELECT data.* FROM t_mn v
INNER JOIN LATERAL (
SELECT * FROM p_2025t l
WHERE l.mn = v.mn
ORDER BY ts DESC LIMIT 1
) AS data
ON true
ORDER BY v.mn, data.ts DESC;
--取每个设备mn的最后一条数据，mn表不单独保存，直接从数据表中取得，效率比mn单独在表中保存低一点
SELECT data.* FROM (select max(mn) as mn from p_2025t group by mn) v
INNER JOIN LATERAL (
SELECT * FROM p_2025t l
WHERE l.mn = v.mn
ORDER BY ts DESC LIMIT 1
) AS data
ON true
ORDER BY v.mn, data.ts DESC;
Windows平台上更改postgresql数据目录位置：
方案1：打开注册表：计算机\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\postgresql-x64-18，修改ImagePath项中服务启动目录
方案2：创建符号链接 mklink /D 链接路径 真实路径
浙公网安备 33010602011771号