【PostgreSQL系列】PostgreSQL性能优化

💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。

• 推荐:kwan 的首页,持续学习,不断总结,共同进步,活到老学到老
• 导航
  • kwan 的解忧杂货铺:全面总结 java 核心技术,jvm,并发编程 redis,kafka,Spring,微服务等
  • 常用开发工具系列:常用的开发工具,IDEA,Mac,Alfred,Git,typora 等
  • 数据库系列:详细总结了常用数据库 mysql 技术点,以及工作中遇到的 mysql 问题等
  • 新空间代码工作室:提供各种软件服务,承接各种毕业设计,毕业论文等
  • 懒人运维系列:总结好用的命令,解放双手不香吗?能用一个命令完成绝不用两个操作
  • 数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂

非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨

PostgreSQL 作为一款功能强大的开源关系型数据库，其默认配置通常是为了适应广泛的通用场景而设计的。然而，在生产环境中，针对特定工作负载进行适当的参数调优可以显著提升数据库性能。

一、WAL(预写式日志)相关参数优化

1. wal_buffers 参数

wal_buffers参数控制 WAL(Write-Ahead Logging)缓冲区的大小，默认值为 4MB。WAL 缓冲区用于存储尚未写入磁盘的 WAL 数据，增大此值可以减少磁盘 I/O 操作。

ALTER SYSTEM SET wal_buffers = '16MB';

优化建议：

• 对于写入密集型工作负载，建议设置为 shared_buffers 的 1/32 到 1/64
• 最大不超过 WAL 段文件大小(通常为 16MB)
• 过大的 wal_buffers 会占用过多内存，但不会带来额外性能提升

2. max_wal_size 参数

max_wal_size参数控制 WAL 文件的最大总大小，默认值为 1GB。

ALTER SYSTEM SET max_wal_size = '4GB';

优化建议：

• 对于高写入负载的系统，增大此值可以减少检查点频率
• 通常设置为 checkpoint_timeout 期间预计产生的 WAL 量的 2-4 倍
• 需要确保有足够的磁盘空间存放 WAL 文件

二、检查点相关参数优化

1. checkpoint_timeout 参数

checkpoint_timeout参数设置自动检查点之间的最长时间间隔，默认值为 5 分钟。

ALTER SYSTEM SET checkpoint_timeout = '30min';

优化建议：

• 增大此值可以减少检查点频率，降低 I/O 负载
• 但过长的间隔会导致崩溃恢复时间变长
• 通常设置在 15-30 分钟之间较为合适

2. checkpoint_completion_target 参数

checkpoint_completion_target参数控制在检查点间隔内完成检查点的目标比例，默认值为 0.5。

ALTER SYSTEM SET checkpoint_completion_target = 0.9;

优化建议：

• 设置为 0.9 意味着检查点将在 90%的时间间隔内完成
• 较高的值可以使检查点 I/O 更均匀地分布
• 有助于避免 I/O 突发对系统性能的影响

三、I/O 性能相关参数优化

1. random_page_cost 参数

random_page_cost参数设置规划器对非顺序获取磁盘页面的成本估计，默认值为 4.0。

ALTER SYSTEM SET random_page_cost = 4.0;

优化建议：

• 对于 SSD 存储，可以设置为 1.0-2.0
• 对于高端存储阵列，可以设置为 2.0-3.0
• 较低的 random_page_cost 会使查询规划器更倾向于使用索引扫描

2. effective_io_concurrency 参数

effective_io_concurrency参数设置 PostgreSQL 预期的并发 I/O 操作数量，默认值为 1。

ALTER SYSTEM SET effective_io_concurrency = 3;

优化建议：

• 对于 SSD 或 RAID 阵列，可以设置为 2-4
• 此参数影响预读行为和维护工作
• 过高的值会导致不必要的 I/O 预取

四、内存相关参数优化

1. maintenance_work_mem 参数

maintenance_work_mem参数设置维护操作(如 VACUUM、CREATE INDEX 等)使用的最大内存量，默认值为 64MB。

ALTER SYSTEM SET maintenance_work_mem = '256MB';

优化建议：

• 对于大型数据库，可以设置为 256MB-1GB
• 较大的值可以加速维护操作
• 但需要确保系统有足够的可用内存

2. work_mem 参数

work_mem参数设置每个查询操作(如排序或哈希表)可以使用的内存量，默认值为 4MB。

ALTER SYSTEM SET work_mem = '16MB';

优化建议：

• 对于复杂查询较多的系统，可以适当增大
• 但过大的值可能导致内存使用失控
• 可以考虑在会话级别为特定查询设置更高的值

五、参数调整后的配置重载

修改这些参数后，通常不需要重启 PostgreSQL 服务，只需执行以下命令即可重新加载配置：

SELECT pg_reload_conf();

注意事项：

• 某些参数更改需要完全重启 PostgreSQL 服务才能生效
• 在生产环境中应用更改前，应在测试环境中验证
• 可以使用SHOW命令验证参数是否已正确应用

六、参数调优的综合策略

1. 循序渐进原则

参数调优应该采取渐进的方式，每次只调整一个或少数几个参数，观察系统行为变化后再决定下一步调整。

2. 监控与评估

使用 PostgreSQL 的内置统计视图(如 pg_stat_activity、pg_stat_bgwriter)和扩展(如 pg_stat_statements)来监控参数调整的效果。

3. 工作负载特性考虑

不同的应用场景需要不同的优化策略：

• OLTP 系统：关注短事务性能和高并发
• 数据仓库：关注大查询和复杂分析性能
• 混合负载：需要平衡各种需求

七、常见问题与解决方案

1. 检查点引起的性能波动

症状：系统周期性变慢，I/O 等待时间周期性增加。
解决方案：调整 checkpoint_timeout 和 checkpoint_completion_target，使检查点 I/O 更均匀分布。

2. 内存不足问题

症状：频繁的磁盘交换，响应时间不稳定。
解决方案：合理设置 work_mem 和 maintenance_work_mem，确保不超过系统可用内存。

3. WAL 相关瓶颈

症状：高写入负载下性能下降明显。
解决方案：优化 wal_buffers 和 max_wal_size，考虑使用更快的存储设备存放 WAL。

觉得有用的话点个赞 👍🏻 呗。
❤️❤️❤️本人水平有限，如有纰漏，欢迎各位大佬评论批评指正！😄😄😄

💘💘💘如果觉得这篇文对你有帮助的话，也请给个点赞、收藏下吧，非常感谢!👍 👍 👍

🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧！🌙🌙🌙