timescaledb 压缩调研

这篇是对timescale 超表压缩的调研

支持版本

pg10+ & timescale version 1.5+

压缩方式

必须是超表
压缩之后，就无法进行增删改操作（除非解压）

自动压缩


ALTER TABLE point_history_0 SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = '${cloumn}'
);

# 配置压缩并告诉系统压缩大于7天的块
SELECT add_compress_chunks_policy('point_history_0', INTERVAL '7 days');

# 配置压缩策略并告诉系统压缩大于20天的块
SELECT add_compress_chunks_policy('point_history_0', INTERVAL '20 days');

# 删除原有的压缩策略
select remove_compress_chunks_policy('point_history_0', true)

手动压缩

1
2
3

SELECT show_chunks('${tablename}', older_than => INTERVAL '3 days');

SELECT compress_chunk( '<chunk_name>');

解压

# 获取压缩jobid
SELECT job_id, table_name
FROM _timescaledb_config.bgw_policy_compress_chunks p
INNER JOIN _timescaledb_catalog.hypertable h ON (h.id = p.hypertable_id);

# 停止job
SELECT alter_job_schedule(<job_id>, next_start=>'infinity');

# 解压数据块

SELECT decompress_chunk('_timescaledb_internal._hyper_2_2_chunk');

# 解压某个表的全部数据块
SELECT decompress_chunk(i) from show_chunks('point_history_0') i;

# 重新压缩

SELECT alter_job_schedule(<job_id>, next_start=>now());

ps: 如果不停止job就直接解压的话，解压完成的数据块又会被压缩

压缩效果

测试使用了pg12 & tsdb version = 1.7+的新容器
测试数据使用了开发库 tsdb_3 数据库的数据，主要数据量来自 point_history_0 表（1kw）
压缩效果如下
- 未压缩时chunk大小 2.1g
- 压缩后chunk大小 500m+

查询效率

这里采用了两个独立的docker timescaledb 容器

a.（pg11 & tsdb version = 1.4.1）这个是原开发库
b.（pg12 & tsdb version = 1.7+）这个是新测试库
如果忽略了版本之间的查询差异，正确设置了压缩关联的列，那么查询效率不会有太大影响

随机取了10个point_id ,在两个库的device_point_0表做 point_id in查询
- a 存在索引point_id 无压缩，查询数据86w+，耗时15s+
- b1 没有point_id索引(timescale 会自动把关联列增加索引)，压缩关联字段为point_id, 查询数据86w+,耗时10s+

存在问题

目前官方提供的支持压缩的镜像是 pg12 + timescale 1.7.1,如果直接从pg11 换到pg12存在问题，可以通过备份还原的方式
- 备份
  - pg_dump -U postgres -Ft tsdb_3 > "$path/tsdb_3-$cur_time.tar"
  - pg_dump -U backup_user –disable-triggers=true xx > xx.dmp
- 还原
  - psql -U postgres -d shop_Database < filename
  - pg_restore -U postgres -f filename
暂未调研timescale/timescaledb:latest-pg11镜像更换timescale version 1.5+的方式，以及可能存在的适配问题
docker 升级：
- docker stop container
- 保持挂载目录一致，镜像换成 timescale/timescaledb 其他不变，启动新容器
- sudo docker exec -it testdbupdate1 psql -U postgres -X
- ALTER EXTENSION timescaledb UPDATE;
- \dx