数据库docker限制资源：CPU 4核，内存 8G。数据库版本：2.15.1-pg16
表结构和索引如下：
CREATE TABLE dm_tag_value (id BIGINT NOT NULL DEFAULT 0,ds_id BIGINT DEFAULT NULL,tag_name VARCHAR(200) DEFAULT NULL,tag_value VARCHAR(1000) DEFAULT '',tag_time TIMESTAMP DEFAULT NULL,app_time TIMESTAMP DEFAULT NULL,quality INTEGER DEFAULT NULL,create_time TIMESTAMP DEFAULT NULL
);
COMMENT ON COLUMN dm_tag_value.id IS 'id';
COMMENT ON COLUMN dm_tag_value.ds_id IS '数据源id';
COMMENT ON COLUMN dm_tag_value.tag_name IS '位号名';
COMMENT ON COLUMN dm_tag_value.tag_value IS '位号值';
COMMENT ON COLUMN dm_tag_value.tag_time IS '实时数据库tag返回时间';
COMMENT ON COLUMN dm_tag_value.app_time IS '查询实时数据库时间';
COMMENT ON COLUMN dm_tag_value.quality IS '质量码';
COMMENT ON COLUMN dm_tag_value.create_time IS '创建时间';
SELECT create_hypertable('dm_tag_value', by_range('app_time'));
--索引
CREATE INDEX idx_dm_tag_value_time_name ON dm_tag_value (tag_name,app_time desc);
从本地机器读取csv文件数据调用JDBC批量写入测试数据库，每批1W行（官方给出1W-1.5W性能最优，考虑内存占用取1W）。
1. 单线程连接写入
cpu 50%，内存 40%
Total rows written: 10045282
Total write time: 397.35 seconds
Average write speed: 25280.65 rows/second
2. 两线程连接并发写入
cpu 120%，内存 90%
线程1：
Total rows written: 11832048
Total write time: 492.50 seconds
Average write speed: 24024.50 rows/second
线程2：
Total rows written: 11944444
Total write time: 496.92 seconds
Average write speed: 24036.88 rows/second
合计：4.8W/s
3. 空间占用
目前数据量：86738750，平均一天数据量1000W
不压缩情况空间占用如下：15.6G（数据+索引），不压缩的情况下和MariaDB空间占用差不太多，同样数据MariaDB占用：19.6G（MariaDB多了一个主键索引）.
对历史分区压缩后如下，压缩率：8%
4. 查询性能
- count(*) 全量，耗时：6.7s
- count(*) 一天，耗时：1.5s
- 索引列count
select count(*) from dm_tag_value dtv
where
app_time between '2024-06-04 00:00:00.000' and '2024-06-04 23:59:59.000' and
tag_name ='TAG9016_ISYS7.1';
耗时 7ms
- 索引列查询
select * from dm_tag_value where app_time between '2024-06-04 00:00:00.000' and '2024-06-04 23:59:59.000' and tag_name ='TAG9016_ISYS7.1'
耗时 5ms
- 不走索引count
select count(*) from dm_tag_value dtv
where
tag_value > '10.880001068115234'
耗时33s
- 不走索引查询
select * from dm_tag_value dtv
where
tag_value > '10.880001068115234'
耗时70ms
- 分时间段过滤聚合
SELECTdm_tag_value.tag_name,time_bucket(INTERVAL '1 second' * interval_seconds, app_time) AS interval_time,min(concat(cast(app_time as VARCHAR),'|',tag_value)) as app_time_tag_valueFROMdm_tag_valueWHEREdm_tag_value.tag_name = ANY(tag_names)AND app_time >= NOW() - INTERVAL '1 minute' * duration_minutes - INTERVAL '1 second' * delay_secondsAND app_time < NOW() - INTERVAL '1 second' * delay_secondsGROUP BYdm_tag_value.tag_name, interval_time
耗时9ms
- 压缩后对查询性能影响不大。
总结
- 写入性能
在4CPU,8G内存的情况下写入能到4.8W行/S，也就是5W位号的情况下可以做到秒级延时。更多位号的情况下会延时，如要减少延迟时间需要增加资源，单机的话可以提升cpu和内存，把机械硬盘改为SSD固态硬盘（官方的数据多核+大内存+SSD能到20W行/S），非单机的话可以分布式部署提高吞吐。 - 存储空间
不压缩的情况存储空间比MariaDB占用稍小些，但是开启分区压缩后可以节省90%空间。 - 查询性能
正常通过时间或索引列查询毫秒级返回（目前业务场景都是这种），全表扫描查询秒级返回。分区压缩对查询性能影响不大。 - 其他，比如批次大小对写入影响每测（官方给出的是1-1.5W每批性能最优），另外条件限制大内存SSD硬盘等的情况也没测。
附件：时序数据库选型
