PostgreSQL RDS只读实例高查询负载下存储空间异常占用与释放问题咨询

这种情况不属于常规预期，但属于特定负载下可能出现的场景，具体原因和对应的现象拆解如下：

核心原因：查询触发的临时文件操作 #

只读实例虽然不处理业务写入，但当执行大量复杂查询时，PostgreSQL会因内存不足生成临时文件：

• 当查询需要处理大表排序、哈希连接、分组聚合等操作，而work_mem配置的内存不足以承载中间结果时，数据库会将数据写入磁盘临时文件，直接导致Write IOPS上升和存储空间占用。
• 这类计算密集型查询会大量消耗CPU资源，当并发量较高时，很容易将CPU打满至100%。
• 查询执行完成后，PostgreSQL会自动清理临时文件，这就对应了你观察到的“存储空间某一时刻自动释放”的现象。

其他可能的次要原因 #

• RDS后台维护操作：AWS RDS会在后台执行日志归档、快照同步、监控数据采集等操作，这些操作会产生少量写入IO，但一般不会导致CPU满载，除非和查询负载叠加。
• 统计信息更新：部分只读实例（比如与主库同步的只读副本）可能会触发统计信息的轻量更新，不过这类操作的资源消耗极低，通常不会引发上述明显异常。

结论与优化建议 #

1. 这种现象并非只读实例的正常状态，说明当前查询负载已经超出实例的处理能力，需要优化。
2. 优化方向：
   • 排查慢查询日志，定位生成大量临时文件的复杂查询，通过添加索引、拆分查询、调整work_mem参数等方式优化SQL性能。
   • 借助pg_stat_statements扩展分析查询的临时文件生成情况，针对性优化。
   • 根据负载情况升级实例CPU/内存规格，减少临时文件的生成概率。
   • 确认RDS维护窗口时间，避免与业务流量高峰重叠。

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