PostgreSQL/timescale-性能测试

参考链接：
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.