PostgreSQL内存调优：shared_buffers与work_mem的协同配置

1. 两个参数各管什么：先明确职责边界

做数据库运维的人，迟早要面对这两个参数。刚接触PostgreSQL时，我干过一件蠢事：把shared_buffers从128MB调到8GB，满心期待查询能“飞”起来，结果系统整台机器swap飙升，数据库连接开始大量超时。后来才明白，shared_buffers和work_mem这俩参数，管的是完全不同的两摊事，搞混了必然翻车。

先说shared_buffers。它是PostgreSQL启动时预先分配的一块共享内存区域，作用是缓存数据页。数据库读表时，会把数据文件里的页面加载到shared_buffers里，再返回给查询执行器。下次再读同一页面，数据已经在内存里，就省掉了一次磁盘I/O。这块内存是所有会话共享的，也就是说大家共用一块区域，谁都能读，脏页最终也要由后台进程统一刷到磁盘。

work_mem这个参数则完全不同。它不为数据页服务，只在排序、哈希连接、去重、哈希聚合这几类操作中发挥作用。执行器发现一条查询需要把一批结果排序，而结果集大小可能超过work_mem，就会把中间结果写到磁盘临时文件上。等到排序结束，再合并这些临时文件。写入磁盘意味着排序路径上的整段性能崩塌，这是work_mem调优要解决的核心矛盾。

两者的职责边界理顺以后，很多困惑就解开了。shared_buffers像是公司的公共休息区，谁都可以进去，但空间再大也只是给访问过的数据页提供一个落脚点。work_mem像是每个员工各自手里的工作台，你这个会话做复杂计算时能铺多开，就看个人桌面的尺寸。桌子太小，东西就得搬到走廊上（磁盘临时文件），来回搬运，效率可想而知。

简单总结我的经验：shared_buffers决定缓存层能装多少数据，work_mem决定单个操作能在内存里完成多少工作。两个参数都调高，系统不一定变快，因为它们的瓶颈点完全不同。把两者放在一起调优，首先要理解它们在同一个进程里如何共同消耗内存，以及它们各自能达到的收益上限。

注意：work_mem在PostgreSQL中是按“操作次数”分配的，这个细节经常被误解。同一会话同时跑三个排序，每个排序都可能单独使用work_mem，内存消耗是倍增关系。后面章节会具体算这笔账。

2. 先动shared_buffers：找到单机缓存的最优水位

2.1 25%经验法则成立的前提

PostgreSQL社区流传一个说法：shared_buffers建议设置为物理内存的25%。这只是一个粗略上限，原因在于PostgreSQL有双重缓存机制。数据库自身的shared_buffers是一层缓存，操作系统文件系统的page cache是另一层。如果shared_buffers占掉太多内存，操作系统留给文件缓存的余地就变小了，而文件系统缓存对顺序扫描、wal日志写入仍然很关键。

抛开复杂的理论，我给出一个判断标准：服务器上有两个可观测指标，一是shared_buffers的缓存命中率，二是操作系统层面的实际交互耗时。如果shared_buffers命中率已经能维持在99%左右，再去调大shared_buffers不会带来额外收益，因为剩下的那点未命中部分主要来自大规模顺序扫描，这种场景本来就不适合在共享缓冲里做文章。

以一台内存64GB、16核、SSD磁盘的机器为例，运行OLTP类型的业务。最初shared_buffers设为8GB，pg_stat_database里的blks_hit和blks_read算下来命中率在98.5%左右，但距离理想值仍有距离。当时业务负载有两个显著特征：一是热点表不大，整表大概是4-5GB，二是频繁的索引回表查询量很高。这种情况下，把shared_buffers从8GB调到16GB，命中率立刻跳到99.5%以上，平均查询延迟下降了一些，效果很明显。

反过来，如果业务以大量全表扫描为主，比如数仓类场景，shared_buffers调到25%以上基本没意义。全表扫描的数据像流水一样经过shared_buffers，还没来得及再次被访问就被后续页面冲走了，缓存再多也是徒劳，反而占用内存还挤压了work_mem和连接并发所需的空间。

所以25%经验法则的前提是：你的热点数据总量小于物理内存的25%，并且业务以“反复读取”为主要访问模式。满足这些条件，25%才值得尝试。

2.2 使用“双缓存命中率”判断是否调到位

要判断shared_buffers是否真的到位，不能只看数据库那一层的命中率。我把这种观察方法叫“双缓存命中率”：既要看PostgreSQL的缓存命中率，也要看操作系统层读取数据时有没有产生真实磁盘I/O。

具体操作上，跑一条SQL前先用EXPLAIN (ANALYZE, BUFFERS)把执行计划跑出来，抓取Buffers: shared hit和Buffers: read这两个字段。前者代表页面在shared_buffers里命中的次数，后者代表需要从磁盘或操作系统page cache重新装载的次数。注意，这里read并不完全等于磁盘读，如果数据已被操作系统缓存，耗时不会太吓人。想看清真正的物理I/O，还需要配合看pg_statio_user_tables视图里heap_blks_read的变化。

我习惯在压测脚本里加入一个采样逻辑，每隔几十秒记录一次：

SELECT current_setting('shared_buffers') AS shared_buffers_setting, sum(heap_blks_read) AS heap_blks_read, sum(heap_blks_hit) AS heap_blks_hit, round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_ratio FROM pg_statio_user_tables;

结合系统层面的iostat观察实际磁盘读，能算明白一件事：PostgreSQL这层命中率已经很高，但如果操作系统page cache命中率反而很低，说明shared_buffers分配过激，把page cache空间压缩得太厉害了。这个情况下即使数据库命中率好看，系统整体响应也不快，因为WAL写入和临时文件读写都在跟系统抢I/O。

我的经验数值是这样的：对于最常见的OLTP环境，shared_buffers命中率能到99%以上，同时真实磁盘读取次数很少，说明水位合适。如果命中率只有95%左右，先别急着调大shared_buffers，而是要检查是否存在大范围顺序扫描的查询或低效的索引使用。把查询优化掉，可能比硬加内存更有效。

曾经在一个生产系统上，我观察到一个奇怪现象：shared_buffers命中率98%，但磁盘I/O依然居高不下。排查后发现，有个定时任务在做大表全量统计，每次扫描几十GB的数据。这些数据完全无法被shared_buffers有效缓存，每次都要走真实磁盘。把那个定时任务优化成增量统计后，问题迎刃而解，shared_buffers反而可以调小一点。

2.3 从脏页刷盘节奏反推缓存容量

dirty page的刷盘节奏，是判断shared_buffers设置合理与否的另一个视角。PostgreSQL把脏页写入磁盘的动作由后台检查点进程和bgwriter进程协调完成。shared_buffers很大，意味着脏页可以攒下很多再一次性刷盘，这能减少写放大，但也带来了两个隐患：checkpoint时大量脏页集中刷盘，瞬间I/O压力飙升；系统崩溃时，刷盘的数据越多，恢复时间越长。

针对第一个隐患，我通常用pg_stat_bgwriter视图观察：

SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, maxwritten_clean FROM pg_stat_bgwriter;

checkpoints_req如果明显大于checkpoints_timed，说明检查点经常因为wal文件达到max_wal_size而被强制触发，而不是按时间周期正常触发。这个时候shared_buffers偏大或max_wal_size偏小，就会导致周期性I/O尖峰。

针对这个情况，我在一台64GB内存的机器上，原本shared_buffers=16GB，强制检查点频繁出现，checkpoint_write_time平均达到十几秒。把shared_buffers降到12GB，同时在postgresql.conf里把max_wal_size从1GB提升到4GB，配合checkpoint_completion_target设为0.9，检查点I/O尖峰自然平缓了很多，业务感知到的延迟波动明显减小。

所以shared_buffers并不是越大越好。它像一个蓄水池，池子太大会让出水节奏变得难以控制。常用生产配置的合理区间是物理内存的20%到25%，但如果你的机器上还跑着别的服务，比如监控agent、备份进程，务必要给它们留足空间。

3. 再谈work_mem：这个参数的隐形成本比想象中高

3.1 一次操作一份内存：并发放大效应

work_mem常被误认为“一个会话只吃一份work_mem”。实际不是。PostgreSQL文档说得清楚，这个参数用于排序、哈希连接和聚合操作，每个操作节点都可能单独申请一份work_mem。一个查询计划里有多个排序节点，每个节点独立分配，这会造成并发放大效应。

举一个实际案例。某个报表系统跑一个复杂查询，执行计划里有一个merge join（需要排序）、一个order by子句和一个distinct操作，三个节点各需要work_mem。当时配置work_mem=64MB，三个节点加起来就需要192MB。跑这个查询的会话有30个，理论上最坏情况下内存需求量是5.7GB。如果只按“每个连接64MB”来估算内存，显然会严重低估。

在设置work_mem之前，必须先搞清楚并发模型。用这个公式估算最坏情况内存代价：

总内存风险 = work_mem × 每查询平均操作节点数 × 活跃并发查询数

举个例子，work_mem定为64MB，一条查询平均3个排序/哈希节点，活跃并发50个，那么理论最坏内存风险是64MB × 3 × 50 = 9.6GB。这几GB内存是从进程私有内存里出的，操作系统无法像回收page cache那样随时压缩它们，一旦超支，就会被OOM Killer盯上。

所以work_mem的核心矛盾在于：调大它对单个排序操作确实有效，但在高并发下内存消耗呈线性甚至倍增增长。这个代价必须在调整前算清楚。

3.2 通过临时文件与EXPLAIN锁定“吃排序”的查询

怎么判断work_mem是否需要调大？最直接的方法是观察临时文件。

PostgreSQL中，排序或哈希超出work_mem的部分会落到临时文件中，这可以从两个角度观察：一是EXPLAIN (ANALYZE, BUFFERS)计划里的Sort Method字段，二是pg_stat_database视图中的temp_files和temp_bytes。

先在单个SQL上做排查：

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ORDER BY ...;

如果计划输出包含Sort Method: external merge Disk: 2432kB，说明这次排序已经溢出到磁盘。注意2432kB是实际落盘的大小，而work_mem决定的是“允许在内存里做多少”。比如work_mem=4MB，这个排序需要约6.5MB空间，就会有一部分落盘。

接着对整个实例做全局排查：

SELECT datname, temp_files, temp_bytes, round(temp_bytes::numeric / nullif(temp_files, 0), 0) AS avg_bytes_per_file FROM pg_stat_database WHERE temp_files > 0 ORDER BY temp_bytes DESC;

temp_bytes如果长期增长明显，说明数据库中整天都在把排序操作“倒腾”到磁盘。这本身就会引入大量I/O，因为临时文件写入很快，但读取合并时要随机访问，SSD还好，机械盘几乎扛不住。

实际优化时我不会一把梭把所有排序都塞进内存。合理流程是：先定位最耗时的查询，看EXPLAIN确认排序类型和实际需要多大内存，再评估这条语句的执行频率。如果是慢查询偶尔跑，比如月报统计，直接对会话级别或事务级别把work_mem调大更安全，不用动全局设置。如果是高频OLTP查询，就只能优化SQL本身，比如减少不必要的order by，或者在表设计上加索引避免排序。

3.3 调work_mem的“洋葱式”策略

给一个实际的调整案例。有一次接手一个业务库，temp_bytes每天都在涨，整体排序落盘很频繁。当时我看到数据库配置文件里work_mem=4MB，查询负载里有大量group by聚合，排序需求也确实高。直接用ALTER SYSTEM SET work_mem = '64MB'是最常见的做法，但我没有这么做。

先把最吃排序的一条SQL拎出来，用EXPLAIN (ANALYZE, BUFFERS)跑一遍，发现这条SQL的order by需要约18MB内存。当时并发大概30个类似查询，如果把work_mem直接调到64MB，风险不高，约2GB的增量还能接受。但后面还有一个隐患，这条SQL同时还有hash join节点，hash join也会用work_mem。实际每个会话消耗的内存可能是两倍work_mem，也就是128MB。

调整时我用了分步方案：

• 第一步：把work_mem从4MB调到16MB，先覆盖70%的排序场景，观察临时文件类型是否减少。
• 第二步：一周后看到temp_bytes下降了不少，但还有少量高频语句仍然落盘，通过pg_stat_statements确认主要来源。
• 第三步：针对剩余语句，在会话级别临时调参，验证提升效果后，再决定全局值是否继续上调。

这个“洋葱式”策略的要点在于，不要试图一次修改到位。你无法在一开始就知道所有查询的内存需求分布，贸然设置过大的全局值，效果可能是灾难性的。特别是那些刚被释放的排序内存，如果正好是内存碎片敏感的场景，可能会导致无法预期的问题。

提示：work_mem相关的排序溢出，在EXPLAIN结果里看Sort Method就能判断。如果是quicksort memory，说明排序操作全部在内存中完成，不需要额外调整。只有出现external merge Disk时，才值得考虑调大work_mem或改写SQL。

4. 两个参数如何联动：从内存预算到参数调整顺序

4.1 明确shared_buffers与work_mem的内存边界

前面分别讲了两个参数的职责，实际调优时它们处在同一个内存池里，必须同时做预算。shared_buffers在启动时通过共享内存分配，归属于内核的SysV共享内存或mmap映射；work_mem则由每个后端进程在堆内存中分配，不在启动时预占。两者加在一起如果超过了物理内存总量，系统就开始swap，性能直接崩。

在一台32GB内存的机器上，我画过一张简单的内存预算表：

表里work_mem并发风险的计算，就要用到前文那个公式。假设work_mem=16MB，每条复杂查询3个排序/哈希节点，活跃并发10个，那么风险是16MB × 3 × 10 = 480MB，离4GB还很远。实际上4GB是按更极端的场景算的，比如work_mem=64MB、15个活跃查询、每个4个节点的情况。

PostgreSQL 9.5以后，每个进程还可以通过huge_pages占用更多内存，这个细节也值得关注。开启huge_pages可以减少TLB miss，但同时让共享内存分配变成“按大页取整”，如果bigger page size导致实际占用超过shared_buffers设置值，内存浪费会翻倍。在虚拟化环境里，这个现象尤其常见。

4.2 调整顺序与典型错误案例

参数调整的顺序比大多数人想象的更重要。shared_buffers是全局缓冲，先调整它会影响整个I/O模型；work_mem是会话级参数，对某个查询的效果尤其敏感。我在实践中遵循“先全局、后局部”的顺序：

• 先调整shared_buffers，确认数据库层面的命中率和操作系统I/O达到一个可以接受的状态。
• 再调整work_mem，而且是先从会话级别或特定查询级别入手，观察效果稳定后，再考虑改全局值。
• 每调整一次，至少观察24小时，结合业务周期判断，而不是下班前草草改完就回家。

一个很典型的错误案例，是我以前在某台服务器上把两个参数同时调大。原配置shared_buffers=1GB、work_mem=4MB，业务反馈某些查询特别慢。当时我把shared_buffers调到10GB、work_mem调到128MB，结果第二天早上发现数据库被OOM Killer杀掉重启了。复盘时计算非常清楚：服务器16GB内存，shared_buffers直接占了10GB，剩下6GB要负担操作系统和几十个wal sender进程，再加一堆排序查询的内存消耗，完全不够用。如果当时shared_buffers只调到4GB，work_mem调到64MB，结果就会安全得多。

还有一个容易忽略的关键点：PostgreSQL的autovacuum进程同样会消耗内存。虽然它主要用于清理死元组，但在扫描大表时也可能产生排序和哈希操作。给autovacuum保留足够的内存余量，能避免因为系统整体内存紧张导致自动清理跟不上，进而引发表膨胀、索引膨胀等连锁问题。

4.3 用pg_stat_statements做参数调整的前后对比

任何参数调整，最终都要拿数据说话。我习惯在调整前把pg_stat_statements和pg_stat_database相关指标打点保存，调整后再对比，看temp_bytes是否下降、平均查询耗时是否减少、脏页刷盘节奏是否平稳。

pg_stat_statements需要预先加载，在postgresql.conf中设置：

shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all

重启后，用下面这条SQL查看排序、临时文件消耗排名靠前的查询：

SELECT calls, round(total_exec_time::numeric, 2) AS total_exec_time_ms, round(mean_exec_time::numeric, 2) AS mean_exec_time_ms, round((local_blks_written + temp_blks_written)::numeric / nullif(calls, 0), 2) AS writes_per_call, query FROM pg_stat_statements ORDER BY temp_blks_written DESC LIMIT 20;

temp_blks_written直接反映了排序/哈希操作落盘的数据块数量。这个字段在调整前后对比，能直观看出work_mem调大后，有多少临时写入被内存在内存里消化掉了。

要注意，pg_stat_statements的数据会不断累积，对比前最好用pg_stat_statements_reset()做一次清零，然后按固定时间窗口收集数据。否则旧数据会把新调整的效果“稀释”掉，看不出变化。

5. 常见故障与排查实录：从参数异常到系统级抖动

5.1 共享内存不足与系统调用失败

PostgreSQL启动时如果报could not map anonymous shared memory或者out of memory，多数情况是操作系统共享内存限制没有放开，而不是shared_buffers真的超了。Linux系统通过kernel.shmmax和kernel.shmall控制共享内存上限。

遇到这类问题，先别急着改数据库配置，查系统参数：

sysctl kernel.shmmax sysctl kernel.shmall

如果生产需要大幅调高shared_buffers，例如超过16GB，还要关注vm.overcommit_memory设置。当vm.overcommit_memory=2时，系统对内存分配非常严格，会导致PostgreSQL申请共享内存失败。这种情况下要么调大系统允许的内存分配上限，要么把overcommit模式改回0（启发式）或1（总是允许）。需要明确的是，overcommit_memory=1虽然能避免共享内存分配失败，但会让数据库在后端进程排序时更容易触发OOM，属于权衡选择，不建议无脑改。

5.2 OOM Killer频繁误杀后端进程

Linux OOM Killer的选择策略并非每次都那么“合理”。在数据库服务器上，它可能优先杀掉内存占用最大的进程，而占内存最大的往往就是PostgreSQL的某个后端进程。杀掉后整个连接中断，业务侧马上报错。

遇到OOM问题，先用dmesg确认被杀的进程名称和当时的内存状态：

dmesg -T | grep -i oom

如果被杀的确实是postgres进程，说明系统物理内存吃紧。此时从内存预算表倒推一下，是shared_buffers占比过高，还是work_mem并发风险失控，又或者是max_connections设置得过低导致内存碎片化。

有个细节很多人容易忽略：连接池中间件与PostgreSQL的每个连接配对，本身也要消耗内存。如果连接数设置得很大，但实际活跃查询少，后端进程的空闲内存也会常驻内存中。虽然这些进程不干活时占用的内存不大，但几千个连接加起来也是一笔客观开销，这在内存预算时要先算进去。

提示：可以在postgresql.conf中设置shared_buffers后，同时在操作系统层用cgroup限制PostgreSQL的内存使用上限吗？可以，但要做好，因为cgroup的限制和数据库内部的shared_buffers计算没有联动，设得太紧可能会让数据库在正常负载下直接OOM，设得太松又起不到保护作用。我在生产上很少用cgroup限制PG内存，更倾向于在连接池层限制最大连接数、控制并发。

5.3 调整后性能反而下降：可能是参数与工作负载不匹配

调参后性能不升反降，在我接触的案例里常见原因有几个。

一是work_mem设置过大导致单条排序变快，但内存被大量占用，系统整体开始swap。表现是某条SQL变快了，但整个业务都在变慢。排查方法是观察swap使用率，以及vmstat的si和so列。只要发现swap in很久没有归零，基本可以断定是内存预算出了问题。

二是shared_buffers调的过大，刷新脏页的时间变长。pg_stat_bgwriter中的checkpoint_write_time如果飙高，说明检查点期间大量脏页集中刷盘，导致I/O尖峰。这种情况下即使缓存命中率很高，用户侧的延迟抖动也明显。解决方法是下调shared_buffers并配合调整checkpoint参数。

三是参数没有配合工作负载特征。比如某个库的查询很少用到排序和group by，但有人“顺手”把work_mem调到1GB，结果对性能没有任何正向影响，只是白白占用内存。work_mem不是越大越好，它只对使用排序和哈希的查询有作用。没有这类查询，调节它基本是无用功。

5.4 常见问题速查表

这张表覆盖了我在生产环境中见过的大多数问题。前几项出现的频率最高，尤其是“调整后性能反而下降”和“频繁OOM”这两类，往往就是联调两个参数时没有做好内存预算导致的。

5.5 调参后“缓冲收益递减”的迹象

如果把调参看作一个持续迭代的过程，会注意到缓冲收益递减的现象。shared_buffers从1GB调到8GB，命中率上升明显；但继续从8GB调到16GB，命中率可能只涨零点几个百分点。work_mem也是一样，从4MB调到64MB，临时文件大幅减少；但从64MB调到256MB，减少量很小。

出现收益递减说明当前参数设置已经接近该工作负载的最优区间。此时投入时间去优化SQL、索引和表结构，性价比远高于继续调大内存参数。我曾经在一个实例上把shared_buffers从12GB调到16GB，命中率几乎没动，但后台进程的刷盘压力明显变大。回头分析主要热点表，发现加一个联合索引就能消除一半以上的seq scan，整体性能提升远超那4GB共享内存。

参数调优不是追求“一次性把数值拉到最高”，而是寻找每一层资源的边际收益平衡点。每次调整前先问自己：当前瓶颈到底在哪？是I/O不够快，排序落盘太多，还是索引低效导致扫描量超标？答案不同，调整的方向也完全不一样。

6. 实操调优流程：从基线到落地的一整套方法

6.1 采集基线：没有数据的调优都是拍脑袋

任何调优操作前都要建立可对比的基线。我在调参前固定采集以下几类数据：

• 工作负载概况：通过pg_stat_statements取top SQL，记录平均耗时、调用次数、临时块读写量。
• 缓存效率：查询pg_statio_user_tables，记录heap_blks_read和heap_blks_hit。
• 后台进程健康度：记录pg_stat_bgwriter的checkpoint相关字段。
• 系统资源：vmstat记录CPU、内存、swap、I/O等待，iostat记录磁盘请求队列长度。
• 业务高峰区间：明确一天中哪些时段并发最高，调参时尽量避开。

基线数据最好持续采集至少三天。数据库系统一天内不同时段的负载差异很大，只拿半天数据做参考，很可能被特殊时段的数据带偏。

6.2 实际操作步骤记录

用一台配置为64GB内存、16核CPU、SSD磁盘的机器举例。假设PostgreSQL版本14，主要业务是OLTP混合报表。

第一步，查看当前参数：

SHOW shared_buffers; SHOW work_mem; SHOW max_connections; SHOW effective_cache_size;

假设初始状态为shared_buffers=128MB，work_mem=4MB，max_connections=200，effective_cache_size=48GB。先检查pg_stat_statements是否有数据，没有就先配置并重启。这事一次做完后，再继续后续步骤。

第二步，调整shared_buffers。当前机器的热点数据大概8GB左右，按25%计算是16GB。但观察到pg_stat_bgwriter存在周期性的checkpoint尖峰，保守起见先把shared_buffers设为12GB。同时把max_wal_size从1GB调整到4GB，checkpoint_completion_target设为0.9。

重启后观察两天，pg_stat_database显示shared_buffers命中率约99.3%，比以前提升明显，且checkpoint写入时间较尖峰下降约一半。这个水位可以接受。

第三步，排查work_mem。查看pg_stat_database.temp_files，发现每天都在产生几千个临时文件。通过EXPLAIN (ANALYZE, BUFFERS)抽查前20条高频查询，发现有的排序需要16MB左右，有的哈希需要32MB左右。

先计算并发风险。业务高峰活跃查询约40个，其中约10条复杂查询平均每个有2个排序/哈希节点。如果把work_mem设为32MB，最坏风险是32MB × 2 × 10 = 640MB，加上shared_buffers 12GB、连接池开销约2GB、系统开销约8GB，总内存约22.6GB，远低于64GB，余量非常充足。

于是把work_mem设为32MB，同时观察temp_files的变化。半个月后，temp_bytes总量下降约70%，原先高频出现的排序落盘语句明显减少。个别报表查询因为涉及大表全量排序，仍然需要更大的内存，这些查询在会话级别单独调参处理。

第四步，评估autovacuum。长时间运行时发现autovacuum在凌晨处理大表时，偶发自身排序性能不足。PostgreSQL允许单独设置autovacuum_work_mem，默认继承work_mem。直接把它设为64MB，避免自动清理被排序落盘拖慢。

第五步，持续监控并固化结果。调参不是睡一觉就完事，要建立周度巡检，对比temp_files、checkpoint_write_time、heap_blks_read等指标有没有回退趋势。如果业务在扩张、数据量在增长，参数水位可能需要按赛季调整。

说明：以上参数数值仅针对示例机器。实际落地时需要根据业务模型、物理内存、并发规模和OS环境重新计算。不要把一个环境下的“最优值”照搬到另一台机器上，那样的配置大概率不匹配。

6.3 使用ALTER SYSTEM还是手改配置文件

PostgreSQL有多个途径修改参数：ALTER SYSTEM写入postgresql.auto.conf、直接改postgresql.conf、用ALTER DATABASE ... SET、用ALTER ROLE ... SET、以及会话级SET。从可维护性角度，我推荐遵循以下原则：

• 全局参数：用ALTER SYSTEM，优点是集中在一个文件，迁移和回滚都容易。
• 用户级参数：如果某个业务账号的查询有明显特征，用ALTER ROLE ... SET work_mem，只为该用户生效，隔离性好。
• 数据库级参数：类似地，不同数据库负载不同，可以用ALTER DATABASE隔离。
• 会话级参数：仅用于临时验证效果，不建议写入应用代码长期依赖。

例如：

ALTER SYSTEM SET shared_buffers = '12GB'; ALTER SYSTEM SET work_mem = '32MB'; ALTER SYSTEM SET max_wal_size = '4GB'; ALTER SYSTEM SET checkpoint_completion_target = '0.9'; SELECT pg_reload_conf();

shared_buffers修改后必须重启实例才能生效；work_mem和max_wal_size可以在线生效，pg_reload_conf()后即可。这个差异要在操作前想清楚，避免发现改了半天没效果后再白等一次重启窗口。

7. 调优过程中我总结的几条原则

看了这么多细节，最后沉淀几条带个人色彩的原则。它们不是来自文档，而是踩过坑之后的反刍。

第一，先抓最痛的点。调优前用慢查询日志和pg_stat_statements搞清楚到底慢在哪里。如果排序落盘是主要问题，集中调work_mem；如果是大量随机读，集中调shared_buffers加索引。不要两个参数一起猛调，那样出了问题很难定位是谁引起的。

第二，强制预算导向。给实例做内存预算不是可选动作。物理内存、shared_buffers、work_mem并发风险、连接开销、系统开销、page cache预留，这六项都要写清楚。某一次我看到团队里有人把shared_buffers设到物理内存的80%，机器还跑着两个备份脚本，结果第三天数据库就崩了。没有预算表，调优就是在赌博。

第三，调参动作要小，观察周期要长。一次只改一个参数，改完至少观察一个业务周。PostgreSQL的参数之间有不少隐性联动，比如shared_buffers变了，checkpoint行为会跟着变；work_mem变了，排序落盘量会减少，I/O模式又会变化。观察周期太短，很难分辨到底哪个环节发生了变化。

第四，数据库版本差异不容忽视。不同版本对参数的解释和默认值有小差异，例如PostgreSQL 14以后引入了一些更精细的memory context跟踪，对分析内存耗费有帮助；PostgreSQL 17以后对work_mem的某些行为也有调整。生产环境大版本升级后，旧的调优档位不能直接照搬，需要重新评估。

第五，善用自动化监控工具。手工巡检容易遗忘，建议把关键指标接入监控系统，设定阈值告警。比如，当temp_bytes日增长突增时、checkpoint_write_time连续几个周期走高时，自动触发提醒。把调优从一次“突发救火”变成持续工程，才能让参数始终保持在一个相对健康的区间。

PostgreSQL参数调优不是玄学，做了充分监控、谨慎调整、及时复盘之后，大概率能在一个合理水位上稳定运行。但也要有心理准备——新业务上线、数据量爆发式增长后，之前的好方案可能会不合身。这时候重新走一遍从基线采集到参数验证的流程，比盲目沿用旧配置要可靠得多。

这套方法下来，我对两个参数的体感是：shared_buffers是地基，决定数据能被缓存多少；work_mem是操作台，决定排序哈希能铺多开。地基不稳，上面无论怎么堆内存都容易塌；操作台太小，再大的地基也救不了溢出的临时文件。先把地基铺稳、再把操作台调合身，整个实例的内存和I/O效率才会在一个健康的状态上长期运行。