PostgreSQL中预处理语句是否遵循shared_buffers内存限制？

shared_buffers仅管控PostgreSQL的数据页缓存，prepared statements的解析计划、参数缓存等内存并不在其覆盖范围内——这些内存属于进程私有内存或共享内存的其他区域，所以仅靠shared_buffers无法阻止这类内存持续增长。以下从服务器端和pgx驱动端给出具体限制方案：

一、PostgreSQL服务器端配置调整 #

• 限制单会话Prepared Statements数量
  修改max_prepared_statements参数，该参数控制每个数据库会话可创建的Prepared Statements上限，默认值为0（无限制）。建议根据业务场景设置合理阈值，比如：

  max_prepared_statements = 1000
  

  超过阈值后，新创建的Prepared Statements会触发驱动的LRU淘汰或报错，避免无限制堆积。修改后需重启PostgreSQL生效。

• 全局内存参数调优

  • work_mem：控制单个排序、哈希操作的内存上限，过高会导致高并发场景下内存暴涨。根据业务并发量调低，比如设置为work_mem = 4MB（注意：该参数是单操作内存，总占用为work_mem × 并发操作数）。
  • maintenance_work_mem：管控VACUUM、CREATE INDEX等维护操作的内存，若无需大规模维护任务，可从默认64MB调低至32MB。
  • shared_memory_size：PostgreSQL 16新增参数，直接限制整个实例的共享内存总大小，设置后会自动适配shared_buffers、work_mem等相关参数，避免共享内存过度占用。比如服务器物理内存为16GB时，可设置：

    shared_memory_size = 8GB
    

• 定期清理闲置Prepared Statements
  PostgreSQL不会自动清理未使用的Prepared Statements，可通过pg_prepared_statements视图排查闲置语句，手动清理：

  -- 清理当前会话所有Prepared Statements
  DEALLOCATE ALL;
  -- 清理指定语句
  DEALLOCATE my_prepared_stmt;
  

  也可编写定时任务，基于pg_prepared_statements的last_used字段，清理超过阈值未使用的语句。

二、pgx驱动（v5.7.2）端优化 #

• 复用Prepared Statements，避免重复创建
  确保同一查询语句仅创建一次Prepared Statement，在多请求中复用——若代码每次请求都调用Prepare方法而不复用，会导致大量重复语句堆积。

• 配置缓存容量限制
  pgx的ConnConfig提供PrepareCacheCapacity参数，控制单连接的Prepared Statement缓存上限（默认512）。可根据业务调整，比如设置为200，超过后自动LRU淘汰旧缓存：

  config, err := pgx.ParseConfig("postgres://user:pass@host/db")
  if err != nil {
      // 错误处理
  }
  config.PrepareCacheCapacity = 200
  conn, err := pgx.ConnectConfig(context.Background(), config)
  

• 显式清理闲置语句
  当某个Prepared Statement不再使用时，调用Deallocate手动释放内存：

  err := conn.Deallocate(context.Background(), "my_prepared_stmt")
  

• 限制连接池大小
  过多数据库连接会导致每个连接持有独立的Prepared Statements，总内存暴涨。调整pgx连接池的MaxConns参数，建议设置为CPU核心数×2 + 1：

  config.MaxConns = 10 // 根据服务器CPU核心数调整
  

三、额外排查建议 #

• 用pg_top或ps aux监控PostgreSQL进程内存占用，结合pg_stat_activity和pg_prepared_statements视图，定位占用内存最多、创建最频繁的语句。
• 检查应用端是否存在连接泄漏，未关闭的连接会持续持有Prepared Statements导致内存堆积，确保pgx连接池正确使用。

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