参考链接:
1.https://zhuanlan.zhihu.com/p/689183857
2.https://blog.csdn.net/xingjingb/article/details/129655526
3.https://www.ffutop.com/posts/2024-01-17-timescaledb-chunks-excluded/
测试表结构
建表SQL
CREATE TABLE public.realtime_data10 (
time TIMESTAMPTZ NOT NULL,
meta_terminal_id int8 NOT NULL,
meta_point_id int8 NOT NULL,
byte int2 NULL,
int32 int4 NULL,
long64 int8 NULL,
float32 float4 NULL,
double64 float8 NULL,
"TEXT" varchar NULL,
"ENUM" varchar NULL,
file text NULL,
"BYTE_ARRAY" int2[] NULL,
"INT32_ARRAY" int4[] NULL,
"FLOAT32_ARRAY" float4[] NULL,
"DOUBLE64_ARRAY" float8[] NULL
);
SELECT create_hypertable('realtime_data10', by_range('time', INTERVAL '1 day'));
CREATE INDEX ixmeta_terminal_id ON realtime_data10 (meta_terminal_id, time ASC);
CREATE INDEX ixmeta_point_id ON realtime_data10 (meta_point_id, time ASC);
ALTER TABLE realtime_data10
SET (timescaledb.compress, timescaledb.compress_orderby='time');
ALTER TABLE realtime_data10 SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'meta_point_id'
);
ALTER TABLE realtime_data10
SET (
timescaledb.compress,
timescaledb.compress_segmentby='meta_point_id',
timescaledb.compress_orderby='time ASC'
);
SELECT add_compression_policy('realtime_data10', INTERVAL '7 days');
SELECT add_dimension('realtime_data10', by_hash('meta_terminal_id', 100));
测试结果
空表时,插入速度约60000条/秒
插入2亿条数据后,插入速度约9000~13000条/秒
已知问题
2024-05-20 14:17:31.387 UTC [1050] FATAL: the database system is in recovery mode
2024-05-20 14:18:03.729 UTC [3451] FATAL: the database system is not yet accepting connections
2024-05-20 14:18:03.729 UTC [3451] DETAIL: Consistent recovery state has not been yet reached.