PostgreSQL shared_buffers配置建议及查询性能优化咨询

一、shared_buffers的合理调整值 #

针对你120GB内存的CentOS服务器，推荐将shared_buffers设为20GB-32GB，常规建议是系统内存的25%左右（也就是30GB），这个区间能平衡PostgreSQL自身缓存和Linux系统页缓存的协作效率。

PostgreSQL不会完全依赖自身的shared_buffers，Linux的页缓存会负责缓存数据文件的大部分内容，所以shared_buffers不需要分配过大——如果把大部分内存都分给它，会挤占系统页缓存的空间，反而可能降低整体缓存效率。

二、调整shared_buffers时的注意事项 #

• 系统内存预留：必须给操作系统、其他运行的服务（比如应用程序、监控进程）预留足够内存，至少留10%-20%的内存（12GB-24GB），避免触发OOM（内存不足杀死进程）。
• 工作负载类型：如果是OLTP（高频小事务）场景，shared_buffers可以稍低（比如20GB）；如果是OLAP（复杂分析查询）场景，可适当上调到32GB，但超过这个值后，缓存命中率的提升会非常有限。
• WAL与checkpoint压力：你的max_wal_size=5GB且archive_mode=off，shared_buffers越大，checkpoint时需要刷到磁盘的脏页就越多，可能会造成IO突增。建议同步调整checkpoint_completion_target=0.9，让脏页刷写更平缓，降低IO峰值。
• NUMA架构影响：如果你的服务器是NUMA架构，要注意避免PostgreSQL跨NUMA节点分配内存，可通过numactl绑定进程到特定节点，或者适当降低shared_buffers的大小，避免跨节点内存访问带来的性能损耗。
• 系统页缓存协同：shared_buffers + 系统页缓存的总占比不要超过可用内存的90%，留足够空间给其他进程和系统操作。

三、其他查询性能优化措施 #

查询语句与执行计划优化 #

• 开启慢查询日志：设置log_min_duration_statement=100ms，记录所有耗时超过100ms的查询，针对性优化。
• 优化索引：给频繁用于WHERE过滤、JOIN关联、ORDER BY排序的字段创建合适的索引（B-tree索引适合等值/范围查询，GIN/GIST适合数组、全文检索等场景），但不要过度创建索引——索引会增加写入时的IO开销。
• 精简查询逻辑：避免SELECT *，只查询需要的字段；优化JOIN条件，避免不必要的笛卡尔积；复杂查询可以拆分为CTE或临时表，降低单条查询的复杂度。
• 更新统计信息：定期执行ANALYZE（或VACUUM ANALYZE），让查询优化器获取最新的表数据分布，生成更高效的执行计划。

内存相关配置调整 #

• work_mem：用于单个排序、哈希操作的内存，OLAP场景可调整为64MB-256MB（默认是4MB），但要注意并发数——如果有100个并发查询都进行排序，总内存占用会是100*256MB=25GB，要预留足够内存避免OOM。
• maintenance_work_mem：用于VACUUM、CREATE INDEX等维护操作的内存，建议设为4GB-8GB（默认是64MB），加快这些后台操作的速度，减少对业务的影响。
• effective_cache_size：设为系统内存的70%-80%（也就是84GB-96GB），这个参数是告诉优化器“系统大概有多少内存可以用于缓存数据”，帮助优化器更准确地选择索引扫描还是顺序扫描。

磁盘与IO优化 #

• 把WAL日志（pg_wal目录）放在独立的SSD磁盘上，WAL的写操作是顺序写，用SSD可以大幅提升写入速度，同时避免和数据文件的随机IO竞争。
• 开启pg_stat_statements扩展：这个扩展可以跟踪所有查询的执行次数、耗时、IO开销等详细数据，是定位性能瓶颈的核心工具。
• 定期VACUUM：清理表中的死元组，避免表膨胀，同时释放磁盘空间，提升查询效率。

连接与并发优化 #

• 控制max_connections：不要设得过大（比如超过200），过多的连接会导致进程上下文切换频繁，内存占用飙升。建议使用连接池（比如PgBouncer）来管理连接，提升并发效率。

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