Postgres 9.3工作负载耗尽内存与交换空间问题求助

看到你在4GB内存、2vCPU的AWS实例上跑PostgreSQL 9.3，还配置了100个schema，负载测试时postgres进程把内存加1GB交换空间都占满了，调小shared_buffers和max_connections也没改善——这个问题得结合多schema场景和PostgreSQL 9.3的特性来拆解，给你几个具体的排查方向和解决建议：

一、先定位内存消耗的真正来源 #

Postmaster父进程本身内存占用很低，真正吃内存的是它fork出的后端子进程（每个连接对应一个），还有autovacuum这类后台worker。你可以用这些工具快速排查：

• 用top或ps aux | grep postgres查看每个postgres进程的RES（常驻内存）和VSZ（虚拟内存），找出内存飙升的进程ID。
• 连接数据库后执行：

  SELECT pid, usename, datname, query, state FROM pg_stat_activity WHERE state = 'active';
  

  把这个结果和进程内存数据对应，看看是不是某些特定schema的查询在疯狂消耗内存。
• 针对可疑进程，用内存上下文分析工具深挖：

  SELECT name, pg_size_pretty(used_bytes) AS used, pg_size_pretty(total_bytes) AS total 
  FROM pg_memory_contexts WHERE pid = <可疑进程ID>;
  

  重点看TopMemoryContext、HashMemoryContext这类是否异常膨胀——这通常是查询操作或缓存累积导致的。

二、针对100个schema的特有问题优化 #

多schema场景下，系统表缓存和查询规划很容易成为内存黑洞：

• 系统表缓存膨胀：100个schema意味着大量的pg_namespace、pg_class、pg_attribute记录，PostgreSQL会把这些系统表数据缓存到shared_buffers或本地缓冲区。如果你的查询频繁跨schema访问，或者每个schema有大量表/索引，缓存会持续增长。建议：
  • 把shared_buffers降到1GB（4GB内存的机器，shared_buffers建议设为内存的1/4~1/3，之前2GB已经偏上限）；
  • 执行SELECT pg_stat_reset_shared('bgwriter');后观察shared_buffers的使用变化，判断是否有缓存无法释放的情况。
• 执行计划缓存累积：如果你的查询是动态生成的（比如每次访问不同schema的同结构表），PostgreSQL会为每个schema生成单独的执行计划，这些计划存在pg_plan_cache里，累积起来占用内存。可以：
  • 执行SELECT pg_stat_reset();后监控计划缓存的增长速度；
  • 尽量在查询中显式指定schema，避免动态生成大量重复计划；
  • 临时设置plan_cache_mode = force_custom_plan（谨慎使用，可能影响性能），强制生成自定义计划而非复用通用计划。

三、检查容易被忽略的内存参数 #

你调整了shared_buffers和max_connections，但这几个参数才是内存泄漏的常见元凶：

• work_mem：这个是每个连接执行排序、哈希操作时的内存配额，默认4MB，但如果查询有多个排序/哈希操作，每个连接会占用多份work_mem。比如max_connections=20，每个连接用3份work_mem，若work_mem设为64MB，总占用就是20364=3.84GB，直接吃光内存！建议降到2~4MB，用EXPLAIN ANALYZE观察是否出现临时文件（出现说明work_mem不够，但总比内存耗尽好）。
• maintenance_work_mem：autovacuum、建索引等维护操作的内存，默认64MB。如果100个schema的表同时触发autovacuum，多个worker会同时占用内存。建议降到32MB，同时设置autovacuum_max_workers = 2（默认3），减少并发维护的内存消耗。
• temp_buffers：每个连接的临时表内存，默认8MB，若查询频繁用临时表，累积起来也很可观，建议保持默认或降到4MB。

四、排查版本bug与内存泄漏 #

PostgreSQL 9.3是2013年的老版本，早已停止维护，存在不少已知的内存泄漏bug：

• 检查自定义函数（尤其是PL/pgSQL或C函数）：用SELECT * FROM pg_stat_user_functions WHERE calls > 0;找出高频调用的函数，单独测试调用时的内存变化，判断是否存在泄漏。
• 优先考虑升级到长期支持版本（比如12或14）：新版本修复了大量内存泄漏问题，对多schema场景的内存管理也有优化，这是从根源解决问题的方案。

五、系统层面的辅助优化 #

• 禁用交换空间：PostgreSQL用交换会严重拖慢性能，还会掩盖内存问题。可以用swapoff -a临时禁用，修改/etc/fstab永久禁用，同时调整oom_score_adj降低postgres进程被系统OOM杀死的概率。
• 调整内核参数：设置vm.overcommit_memory = 2和vm.overcommit_ratio = 90，让系统更合理地分配内存，避免PostgreSQL过度申请内存。

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