PostgreSQL时序传感器数据表存储优化与性能提升咨询

我拥有3个PostgreSQL 14.9版本的表，用于存储2670个数据点的传感器数据。数据以自动生成的INSERT语句批量写入（每次写入3天的数据块），语句格式如下：

INSERT INTO monitoring_data.time_series_numerical (timestamp, property_id, value) 
VALUES ('timestamp', 'property_id', 'value')
...
ON CONFLICT (timestamp, property_id) DO NOTHING;

这最终导致数据库运行越来越慢，甚至需要释放新存储空间才能正常访问。3张表的磁盘空间占用情况如下：

通过pgAdmin 4导出的3张表CREATE脚本如下：

CREATE TABLE IF NOT EXISTS monitoring_data.time_series
(
    "timestamp" timestamp without time zone NOT NULL,
    property_id character varying(255) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT time_series_pkey PRIMARY KEY ("timestamp", property_id),
    CONSTRAINT time_series_property_id_fkey FOREIGN KEY (property_id)
        REFERENCES monitoring_data.properties (property_id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT
)

• varchar(255)类型的property_id示例：c0022:93EA6CCA3B7D1829_AnalogInput1.status.voltage，用于标识数据点

time_series_numerical表：

CREATE TABLE IF NOT EXISTS monitoring_data.time_series_numerical
(
    -- 继承自monitoring_data.time_series: "timestamp" timestamp without time zone NOT NULL,
    -- 继承自monitoring_data.time_series: property_id character varying(255) COLLATE pg_catalog."default" NOT NULL,
    value double precision NOT NULL,
    CONSTRAINT time_series_numerical_pkey PRIMARY KEY ("timestamp", property_id),
    CONSTRAINT time_series_numerical_timestamp_property_id_fkey FOREIGN KEY ("timestamp", property_id)
        REFERENCES monitoring_data.time_series ("timestamp", property_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
    INHERITS (monitoring_data.time_series)

time_series_alphabetical表：与time_series_numerical结构一致，仅value列类型不同：

value text COLLATE pg_catalog."default" NOT NULL,

为确保向继承表写入记录时满足外键约束，我创建了触发器及配套函数，在每条记录INSERT前执行以下逻辑：

BEGIN
    INSERT INTO monitoring_data.time_series (timestamp, property_id)
    SELECT NEW.timestamp, NEW.property_id
    WHERE NOT EXISTS (
        SELECT 1 FROM monitoring_data.time_series 
        WHERE timestamp = NEW.timestamp
        AND property_id = NEW.property_id 
    );
    RETURN NEW;
END;

设计time_series表的初衷是简化对底层不同数据类型测量值的访问，同时确保所有timestamp和property_id条目仅存储一次。但实际运行未达预期，导致数据重复存储（一次在time_series表，一次在对应测量表），且time_series表内每条条目甚至重复两次。

查询示例：

SELECT *
FROM monitoring_data.time_series_numerical
WHERE property_id = 'c0022:9FF441820C58912B_HeatPump.configuration.flowTemperature'  -- 替换为实际ID
AND timestamp >= '2024-09-01 00:00:00'
AND timestamp < '2024-09-02 00:00:00'
ORDER BY timestamp;

执行EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)的结果：

"Index Scan using time_series_numerical_pkey on monitoring_data.time_series_numerical  (cost=0.57..1153041.19 rows=4516 width=72) (actual time=0.516..633099.399 rows=5725 loops=1)"
"  Output: ""timestamp"", property_id, value"
"  Index Cond: ((time_series_numerical.""timestamp"" >= '2024-09-01 00:00:00'::timestamp without time zone) AND (time_series_numerical.""timestamp"" < '2024-09-02 00:00:00'::timestamp without time zone) AND ((time_series_numerical.property_id)::text = 'c0022:9FF441820C58912B_HeatPump.configuration.flowTemperature'::text))"
"  Buffers: shared read=199057"
"Settings: search_path = 'monitoring_data'"
"Planning Time: 0.485 ms"
"JIT:"
"  Functions: 2"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 2.138 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 2.138 ms"
"Execution Time: 633108.973 ms"

我刚接触PostgreSQL，在实施解决方案前寻求建议。

我目前有两个思路，但想了解其他可行方案及该场景下的最佳实践：

• 删除time_series表所有条目，移除两张继承表的外键约束、触发器及函数，保留通过time_series表访问继承表的能力，同时大幅减少存储空间占用。
• 使用分区功能，基于timestamp将数据拆分到多个表以加速查询。

我仍不清楚索引占用空间过大的原因，以及如何缩减索引占用空间。

• PostgreSQL版本14.9
• 除主键外无其他索引；所有索引均为B-tree索引
• 无死元组；已启用autovacuum
• 已安装扩展：postgis、fuzzystrmatch、uuid-ossp
• 已确定需分离time_series_numerical和time_series_alphabetical表

内容的提问来源于stack exchange，提问作者Julian_k