时序数据库配置优化咨询：7亿条数据查询性能提升方案探讨

我正在寻求时序数据库配置优化建议，以处理大规模时序数据。现有约20000个时序剖面，涵盖一年时长，采用每小时4个时间戳的季度分辨率，总计约7亿条记录。当前使用带TimescaleDB扩展的Azure PostgreSQL服务器。

配置详情 #

硬件规格 #

• 4 vCores
• 16 GiB内存
• 512 GB存储

数据库结构 #

包含两张核心表：

• 负载剖面表loadprofilepool，字段为(id, time, value, sensor_id)
• 传感器表sensor_table，字段为(id, sensor_id)

负载剖面表已创建两个索引：(sensor_id, time)和sensor_id

示例查询 #

常用聚合查询语句如下：

SELECT AVG(value), time
FROM public.loadprofilepool
WHERE sensor_id IN (
    SELECT id 
    FROM public.sensor_table
    ORDER BY RANDOM()
    LIMIT 500
)
GROUP BY time;

注：此示例通过随机生成sensor_id列表模拟不同传感器集合的查询，实际场景中sensor_id列表来自其他渠道，数量范围从几个到数千不等。

数据分布 #

• 当前每个传感器对应24*4*365条记录（覆盖一年时长，季度分辨率），总计20000个传感器
• 未来将接入实时传感器数据，数据分布将随具体传感器类型有所变化

性能现状 #

查询期间CPU使用率不超过20%，内存使用率稳定在约40%。但查询速度未达预期：提取10至1000个剖面并按时间戳聚合生成时序数据需5秒至数分钟，目标是实现数千个传感器的聚合查询在几秒内完成。

待解答问题 #

1. 当前配置是否为处理该规模及类型时序数据的最优方案？若不是，能否推荐合适的替代方案？我已考虑过NoSQL数据库、基于Zarr或NetCDF文件的云存储，但不确定哪种更适配需求。
2. 如何优化当前配置以提升查询速度？是否有特定的TimescaleDB或PostgreSQL配置项、索引策略或查询编写技巧可以改善性能？

我已尝试过创建不同类型的索引，并对loadprofilepool表执行聚类操作，但效果未达预期。

感谢您的帮助，任何建议或指导均将不胜感激。

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