PHP小码农

Appearance

内存结构（Shared Buffer / WAL Buffer） ​

内存是 PostgreSQL 性能的核心。本节把内存分成「共享内存（Shared Memory）」与「本地内存（Local Memory）」两级逐项讲透：共享缓冲区（shared_buffers）、WAL 缓冲、提交日志（CLOG）缓冲、锁表分别管什么，工作内存（work_mem）、维护内存（maintenance_work_mem）、临时缓冲（temp_buffers）分别怎么用，并给出可直接落地的参数配置建议。

前置知识

阅读本节前，建议先了解：进程模型（Postmaster / Backend）

基础概念 ​

PostgreSQL 的内存分为两级，区别在于谁能看到：

层级分配时机归属典型参数
共享内存（Shared Memory）实例启动时一次性分配所有进程共享shared_buffers、wal_buffers
本地内存（Local Memory）会话/操作开始时分配单个进程私有work_mem、maintenance_work_mem、temp_buffers
术语英文名说明
共享缓冲区Shared Buffers缓存数据页（8KB）的主缓存池
WAL 缓冲WAL Buffer暂存 WAL 记录，待写入磁盘
提交日志CLOG, Commit Log记录每个事务的最终状态（提交/回滚）
工作内存work_mem单个排序/哈希操作可用的内存
维护内存maintenance_work_memVACUUM、CREATE INDEX 等维护操作可用内存
临时缓冲temp_buffers临时表数据页的会话级缓存
有效缓存effective_cache_size优化器对「OS 缓存 + 共享缓冲」总量的估计（不是分配量）
目录缓存Catalog Cache每个后端进程缓存系统目录元数据
进程数组ProcArray共享内存中所有活跃事务的快照信息
缓冲描述符Buffer Descriptor每个 buffer 页的元数据（标签、引用计数、脏标志）

最容易误解的参数：effective_cache_size

effective_cache_size 不分配任何内存，它只是告诉优化器「这台机器上可用的文件系统缓存大约有多少」，用来估算索引扫描的成本。把它设成实际内存的 50%~75% 是常见做法，但它不会占用一个字节。把它和 shared_buffers 混为一谈是极高频的误解。

内存分配总览 ​

一个典型生产实例（64GB 内存）的内存布局示意：

text
┌─────────────────────────────────────────────────────────────┐
│                    共享内存（启动时分配）                     │
│  ┌──────────────┐ ┌───────────┐ ┌────────┐ ┌──────────────┐ │
│  │  shared_     │ │ wal_      │ │ CLOG   │ │ 锁表 /       │ │
│  │  buffers     │ │ buffers   │ │ buffer │ │ ProcArray    │ │
│  │  (16GB)      │ │ (16MB)    │ │ (几十  │ │ (随连接数)    │ │
│  │              │ │           │ │  MB)   │ │              │ │
│  └──────────────┘ └───────────┘ └────────┘ └──────────────┘ │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│              本地内存（每进程独立，随会话/操作）               │
│  ┌──────────────┐ ┌──────────────────┐ ┌──────────────────┐ │
│  │  work_mem    │ │ maintenance_     │ │  temp_buffers    │ │
│  │  (4~32MB)    │ │ work_mem         │ │  (8MB)           │ │
│  │  × 排序节点   │ │ (512MB~2GB)      │ │  × 会话数         │ │
│  └──────────────┘ └──────────────────┘ └──────────────────┘ │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  catalog cache（数 MB ~ 数十 MB，随访问目录数增长）     │   │
│  └──────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────┘

关键认识：共享内存是「一份」，本地内存是「每进程一份」。所以 work_mem 的危险不在于它本身的值，而在于它会被乘上「并发连接数 × 每查询的排序/哈希节点数」。

语法与用法 ​

内存参数总表 ​

参数默认值上下文作用域说明
shared_buffers128MBpostmaster实例数据页主缓存池，需重启
wal_buffers-1（自动）postmaster实例WAL 缓冲大小；-1 表示按 shared_buffers/32 自动推算，上限 16MB
work_mem4MBuser会话/操作每个排序、哈希、聚合节点可用内存
maintenance_work_mem64MBuser会话/操作VACUUM、CREATE INDEX、ALTER TABLE ADD FK
temp_buffers8MBuser会话临时表页缓存
effective_cache_size4GBuser实例仅用于成本估算，不分配内存
min_dynamic_shared_memory0postmaster实例并行查询共享内存下限（PG14+）
huge_pagestrypostmaster实例大页支持：off / try / on
shared_memory_typemmappostmaster实例共享内存实现方式（Linux 常用 mmap）
max_connections100postmaster实例间接决定本地内存总量
sql
-- 一次看清所有内存相关参数的当前值与单位
SELECT name, setting, unit, context,
       pg_size_pretty(
         CASE
           WHEN unit = '8kB' THEN setting::bigint * 8192
           WHEN unit = 'kB'  THEN setting::bigint * 1024
           WHEN unit = 'MB'  THEN setting::bigint * 1024 * 1024
           ELSE NULL
         END
       ) AS human_readable
FROM pg_settings
WHERE name IN ('shared_buffers', 'wal_buffers', 'work_mem', 'maintenance_work_mem',
               'temp_buffers', 'effective_cache_size', 'min_dynamic_shared_memory',
               'huge_pages', 'shared_memory_type')
ORDER BY name;
text
          name           | setting | unit |  context   | human_readable
-------------------------+---------+------+------------+----------------
 effective_cache_size    | 524288  | 8kB  | user       | 4096 MB
 huge_pages              | 0       |      | postmaster | (null)
 maintenance_work_mem    | 65536   | kB   | user       | 64 MB
 min_dynamic_shared_memory | 0     | kB   | postmaster | 0 bytes
 shared_buffers          | 16384   | 8kB  | postmaster | 128 MB
 shared_memory_type      | 1       |      | postmaster | (null)
 temp_buffers            | 1024    | 8kB  | user       | 8 MB
 wal_buffers             | -1      | 8kB  | postmaster | (null)
 work_mem                | 4096    | kB   | user       | 4 MB
(9 rows)
ini
# postgresql.conf
# 典型的 64GB 机器配置：共享内存约占 25%，其余留给 OS 文件缓存

shared_buffers = 16GB              # 约占物理内存 25%
effective_cache_size = 48GB        # 约占物理内存 75%，仅用于成本估算
wal_buffers = 16MB                 # 显式设为上限值（默认自动即为此值）

# 本地内存：谨慎设置，注意乘数效应
work_mem = 16MB                    # 单节点内存；高并发场景不要盲目调大
maintenance_work_mem = 1GB         # VACUUM / CREATE INDEX 用
temp_buffers = 16MB

# 大页：减少 TLB miss，shared_buffers 较大时收益明显
huge_pages = try                   # 先 try，验证后再改 on
bash
# 修改 shared_buffers / wal_buffers 需要重启
sudo systemctl restart postgresql@18-main

# 验证
sudo -u postgres psql -c "SHOW shared_buffers;" -c "SHOW wal_buffers;"

详细说明 ​

共享内存区 ​

共享内存是实例启动时由 Postmaster 一次性分配、所有进程共同读写的区域。它包含以下部分：

区域大小决定因素作用
shared_buffers参数直接指定数据页缓存池，是最大的部分
WAL bufferswal_buffers（默认 1/32 shared_buffers，上限 16MB）暂存 WAL 记录
CLOG buffer固定（约 128KB 页缓存）缓存事务提交状态
锁表随 max_locks_per_transaction × max_connections存放重型锁的持有与等待信息
ProcArray随 max_connections活跃进程/事务快照，MVCC 可见性判断的基础
子事务缓冲随 max_connections缓存子事务状态
通知队列8KB（max_notify_queue_pages 可调）LISTEN/NOTIFY 消息

shared_buffers：数据页缓存 ​

shared_buffers 是 PostgreSQL 自己在用户态管理的数据页缓存，缓存单位是 8KB 的页（Page）。

  • 读路径：后端需要某个页时，先查 shared_buffers；命中则直接返回（无需系统调用）；未命中则从磁盘读入并放入缓存。
  • 写路径：修改页时，先在 shared_buffers 中就地修改并标记为脏页（Dirty Page），由 background writer / checkpointer 择机写回磁盘。
  • 与 OS 缓存的关系：shared_buffers 之外，OS 还会缓存同一批文件（双重缓存 / Double Buffering）。因此 shared_buffers 不宜过大——留足内存给 OS 缓存通常比把它全吃掉更高效。经验值：25% 物理内存。
sql
-- 观察 buffer 缓存命中率（生产应长期高于 99%）
SELECT sum(blks_hit)                                            AS hits,
       sum(blks_read)                                           AS reads,
       round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS hit_ratio_pct
FROM pg_stat_database
WHERE datname IS NOT NULL;
text
   hits    |  reads  | hit_ratio_pct
-----------+---------+---------------
 184729355 | 1204857 |         99.35
(1 row)
sql
-- 按数据库看谁在制造磁盘读（命中率低的库需要关注）
SELECT datname,
       blks_read,
       blks_hit,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct,
       temp_files, pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY blks_read DESC
LIMIT 5;
text
 datname  | blks_read |  blks_hit  | hit_pct | temp_files | temp_size
----------+-----------+------------+---------+------------+-----------
 demo     |   1183204 |  182040011 |   99.35 |       1204 | 28 GB
 postgres |     21653 |    2689344 |   99.20 |          0 | 0 bytes
(2 rows)

双重缓存与大页

PostgreSQL 4.1 起 shared_buffers 不再依赖 System V 共享内存，Linux 上默认用 mmap（shared_memory_type = mmap）。因为 OS 也会缓存同一批文件，设置超大 shared_buffers 反而可能出现「同一页在 PG 缓存和 OS 缓存各存一份」的浪费。这正是社区建议把它控制在 25% 的原因。

huge_pages = try 可以让 Linux 用 2MB 大页映射共享内存，显著减少 TLB miss，对 shared_buffers ≥ 8GB 的实例收益明显。

WAL buffers：WAL 记录的暂存区 ​

WAL 记录的写入路径是：后端 → WAL buffer（共享内存）→ WAL 段文件（磁盘）。wal_buffers 决定这个中间缓冲的大小。

场景wal_buffers 建议
默认（自动）无需设置，服务端按 shared_buffers/32 推算，上限 16MB
大量小事务、写密集显式设为 16MB（即上限），减少 WAL 写入者等待
大批量导入可配合 commit_delay / commit_siblings 做组提交
sql
-- 查看 WAL 生成与同步情况
SELECT wal_records,
       wal_fpi,
       pg_size_pretty(wal_bytes) AS wal_generated,
       wal_buffers_full,
       stats_reset
FROM pg_stat_wal;
text
 wal_records | wal_fpi | wal_generated | wal_buffers_full |          stats_reset
-------------+---------+---------------+------------------+-------------------------------
     2948112 |   18402 | 1863 MB       |              412 | 2026-09-21 09:12:03.117204+08
(1 row)

wal_buffers_full 是「因 WAL 缓冲满而被迫等待刷盘」的次数。如果这个数字持续快速增长，说明 wal_buffers 偏小或磁盘写入能力不足。

CLOG buffer 与事务状态 ​

事务的最终状态（提交 / 回滚 / 进行中）记录在提交日志（CLOG, Commit Log）中，物理文件位于 pg_xact/。共享内存里有一小块 CLOG 缓冲用于加速访问。

sql
-- CLOG 文件与事务状态的关系：文件名为事务 ID 段号
SELECT pg_relation_filepath('pg_class') AS pg_class_file;

-- 查看当前事务与下一个将分配的事务 ID
SELECT pg_current_xact_id()                          AS current_xid,
       pg_current_xact_id_if_assigned()              AS current_xid_if_assigned,
       age(relfrozenxid)                             AS oldest_xid_age
FROM pg_class
ORDER BY age(relfrozenxid) DESC
LIMIT 1;
text
    current_xid    | current_xid_if_assigned | oldest_xid_age
-------------------+-------------------------+----------------
 738291            | 738291                  |        1842391
(1 row)

XID 环绕与 CLOG 空间

pg_xact/ 的大小由「已分配但未冻结的事务 ID 数量」决定。若长期不 VACUUM 导致 age(relfrozenxid) 逼近 20 亿，pg_xact/ 会持续膨胀，最终达到 autovacuum_freeze_max_age 触发强制冻结——这就是「事务 ID 环绕（XID Wraparound）」问题的物理表现。监控 age(datfrozenxid) 是 DBA 的必修课。

sql
SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 2) AS pct_to_wraparound
FROM pg_database ORDER BY xid_age DESC;

锁表与 ProcArray ​

结构大小由谁决定作用
锁表（Lock Table）max_locks_per_transaction × (max_connections + max_prepared_transactions)存储重型锁的持有/等待记录
ProcArraymax_connections + max_worker_processes + autovacuum_max_workers + 若干活跃事务列表，MVCC 快照的基础
谓词锁表（Predicate Lock Table）max_pred_locks_per_transaction可串行化隔离级别使用
sql
-- 观察锁的当前持有情况
SELECT mode, count(*) AS locks
FROM pg_locks
GROUP BY mode
ORDER BY locks DESC;
text
      mode       | locks
-----------------+-------
 AccessShareLock |    42
 RowExclusiveLock|     8
 ExclusiveLock   |     2
(3 rows)
sql
-- 关键：共享内存中锁槽位是否充足（观察是否有"out of shared memory"风险）
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN ('max_locks_per_transaction', 'max_pred_locks_per_transaction',
               'max_connections', 'max_prepared_transactions')
ORDER BY name;
text
            name             | setting | unit |                   short_desc
-----------------------------+---------+------+------------------------------------------------
 max_connections             | 200     |      | Sets the maximum number of concurrent connections.
 max_locks_per_transaction   | 64      |      | Sets the maximum number of locks per transaction.
 max_pred_locks_per_transaction | 64   |      | Sets the maximum number of predicate locks per tra
 max_prepared_transactions   | 0       |      | Sets the maximum number of simultaneously prepared
(4 rows)

"out of shared memory" 错误的根源

高并发场景下若出现 ERROR: out of shared memory 并提示 You might need to increase max_locks_per_transaction，说明锁槽位不够了。这个参数需要重启才能生效（postmaster 上下文），所以生产环境应预先按「单事务最多访问的对象数 × 并发事务数」估算。

分区表特别容易踩这个坑：一个分区表若有 1000 个分区，一条不剪枝的查询会一次性请求上千个锁。

本地内存区 ​

本地内存随进程创建、随进程销毁，不占共享内存。

work_mem：排序与哈希的内存 ​

work_mem 是单个操作节点可用的内存上限，不是每条连接，也不是每个查询：

使用场景用途超出后行为
ORDER BY快速排序转为外部归并排序，写临时文件
DISTINCT去重哈希分批处理
GROUP BY（哈希聚合）哈希表转为排序聚合
哈希连接（Hash Join）建哈希表分批（Batches），可能多轮扫描
IN / EXISTS 子查询哈希哈希表分批

乘数效应：一个查询可以有多个排序/哈希节点，且并行查询每个 worker 各自分配。所以实际峰值可能是 work_mem × 节点数 × worker 数 × 并发查询数。

sql
-- 观察单条查询实际用了多少 work_mem（需要 EXPLAIN ANALYZE）
EXPLAIN (ANALYZE, BUFFERS)
SELECT status, count(*)
FROM orders
GROUP BY status
ORDER BY count(*) DESC;
text
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------
 Sort  (cost=218345.12..218345.62 rows=200 width=16) (actual time=892.451..892.456 rows=6 loops=1)
   Sort Key: (count(*)) DESC
   Sort Method: quicksort  Memory: 27kB
   ->  HashAggregate  (cost=218330.12..218332.12 rows=200 width=16) (actual time=892.401..892.405 rows=6 loops=1)
         Group Key: status
         Batches: 1  Memory Usage: 2081kB
         ->  Seq Scan on orders  (cost=0.00..185123.00 rows=6640224 width=9) (actual time=0.012..498.220 rows=6640224 loops=1)
 Planning Time: 0.412 ms
 Execution Time: 892.612 ms

关键看 Sort Method: quicksort Memory: 27kB（内存排序，未落盘）与 Batches: 1（哈希聚合一轮完成，未溢出）。如果出现 external merge Disk: 512000kB 或 Batches: 8，说明 work_mem 不足。

sql
-- 全局调整（新会话生效）
ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

-- 会话内临时提高（例如跑一次报表）
SET work_mem = '128MB';
EXPLAIN (ANALYZE) SELECT ... ;
RESET work_mem;

-- 更精细：按用户/按数据库设定
ALTER ROLE analyst SET work_mem = '64MB';
ALTER DATABASE reporting SET work_mem = '32MB';

work_mem 不是越大越好

很多人看到「排序落盘」就把 work_mem 调到 1GB。在 500 连接、每查询 4 个排序节点的实例上，理论峰值是 1GB × 4 × 500 = 2TB——这会导致 OOM Killer 直接杀掉数据库进程。

正确做法：

  1. 全局保持保守值（4~16MB）。
  2. 对特定的重查询用会话级 SET work_mem 单独放行。
  3. 用 log_temp_files = 0 记录所有落盘的临时文件，定位需要放行的查询。

maintenance_work_mem：维护操作内存 ​

maintenance_work_mem 供「维护类操作」使用，影响的是速度而非查询本身：

操作用途
VACUUM记录待清理的元组 TID，越大越能一次扫完
CREATE INDEX加快 B-tree 构建（也是 max_parallel_maintenance_workers 的限制来源）
ALTER TABLE ADD FOREIGN KEY验证阶段
REINDEX重建索引
sql
-- 观察 VACUUM 是否因内存不足而多轮扫描
-- maintenance_work_mem 越大，"index scans" 轮数越少
VACUUM (VERBOSE) orders;
text
INFO:  vacuuming "public.orders"
INFO:  launched 4 parallel vacuum workers
INFO:  scanned index "orders_pkey" to remove 1842 row versions
INFO:  "orders": removed 1842 row versions in 12043 pages
INFO:  index "orders_pkey" now contains 6640224 row versions in 27120 pages
INFO:  "orders": found 1842 removable, 6640224 nonremovable row versions in 83201 pages
DETAIL:  0 dead row versions cannot be removed yet.
sql
-- 全局设置（autovacuum 用；默认 64MB 对生产偏小）
SELECT name, setting, unit, boot_val
FROM pg_settings
WHERE name = 'maintenance_work_mem';
text
        name           | setting | unit | boot_val
-----------------------+---------+------+----------
 maintenance_work_mem  | 65536   | kB   | 65536
(1 row)

注意：autovacuum 单独有一个 autovacuum_work_mem 参数，若为 -1（默认）则使用 maintenance_work_mem 的值。给 autovacuum_work_mem 单独设值可以避免 autovacuum 与手工 CREATE INDEX 争抢内存。

ini
# postgresql.conf
maintenance_work_mem = 1GB          # 手工维护操作用
autovacuum_work_mem = 256MB         # 自动清理专用，避免与手工操作抢内存

temp_buffers：临时表缓存 ​

temp_buffers 是每个会话缓存临时表页的数量，默认 8MB。它只对临时表（CREATE TEMP TABLE）和「自动创建的临时文件元数据」有效——排序/哈希落盘的文件不经过 temp_buffers。

sql
-- 大数据量的临时表计算，可临时提高
SET temp_buffers = '64MB';

CREATE TEMP TABLE tmp_agg AS
SELECT customer_id, count(*), sum(amount)
FROM orders
GROUP BY customer_id;

SELECT count(*) FROM tmp_agg;
RESET temp_buffers;
sql
-- 检查临时文件与临时表的使用情况
SELECT datname,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_total,
       round(temp_bytes / nullif(temp_files, 0) / 1024.0 / 1024.0, 1) AS avg_temp_mb
FROM pg_stat_database
WHERE datname IS NOT NULL AND temp_files > 0
ORDER BY temp_bytes DESC;
text
 datname | temp_files | temp_total | avg_temp_mb
---------+------------+------------+-------------
 demo    |       1204 | 28 GB      |        23.8
(1 row)

catalog cache：系统目录缓存 ​

每个后端进程都会缓存自己访问过的系统目录（如 pg_class、pg_attribute）的内容，称为目录缓存（Catalog Cache）。它的好处是避免每次都查系统表，代价是：

  • 访问对象越多，缓存越大（可达数十 MB）。
  • DDL 操作（ALTER TABLE）会让相关缓存项失效，可能引起大量后端同时重建缓存（Catalog Cache Bloat）。
sql
-- 查看当前会话的目录缓存统计
SELECT cache_name,
       num_hits,
       num_misses,
       num_entries,
       pg_size_pretty(avg_calls) AS dummy
FROM pg_stat_database
LIMIT 0;   -- pg_stat_database 无此列，用下面这个

-- 单会话内存上下文明细（含 catalog cache）
SELECT name,
       pg_size_pretty(total_bytes) AS total,
       pg_size_pretty(used_bytes)  AS used
FROM pg_backend_memory_contexts
ORDER BY total_bytes DESC
LIMIT 15;
text
            name            |  total  |  used
----------------------------+---------+--------
 CacheMemoryContext         | 12 MB   | 8601 kB
 TopPortalContext           | 8192 kB | 8192 kB
 MessageContext             | 1024 kB | 316 kB
 TopTransactionContext      | 512 kB  | 128 kB
 PortalContext               | 256 kB  | 64 kB
(5 rows)

CacheMemoryContext 就是目录缓存所在的内存上下文。若它异常大（几百 MB），通常意味着该会话访问了大量不同的对象（典型：遍历所有分区、大量动态 SQL）。

内存参数配置建议 ​

按机器规格给出参考配置：

物理内存shared_bufferseffective_cache_sizework_memmaintenance_work_memwal_buffers
4 GB1 GB3 GB4 MB128 MB16 MB
16 GB4 GB12 GB8 MB512 MB16 MB
64 GB16 GB48 GB16 MB1 GB16 MB
256 GB64 GB192 GB16~32 MB2 GB16 MB
ini
# postgresql.conf
# 64GB / 16 核 机器的完整内存配置示例

# ── 共享内存（需重启） ──
shared_buffers = 16GB
wal_buffers = 16MB

# ── 优化器估算（不占内存） ──
effective_cache_size = 48GB

# ── 本地内存（可动态改） ──
work_mem = 16MB
maintenance_work_mem = 1GB
autovacuum_work_mem = 256MB
temp_buffers = 16MB

# ── 大页（需重启，配合内核 vm.nr_hugepages） ──
huge_pages = try

# ── 内存相关的辅助限制 ──
max_connections = 300
max_parallel_workers_per_gather = 2        # 并行度越高，work_mem 总占用越多
hash_mem_multiplier = 2.0                  # PG13+：哈希节点可用 work_mem 的倍数

# ── 落盘监控 ──
log_temp_files = 0                         # 记录所有临时文件，便于定位 work_mem 不足
sql
-- 估算实例的本地内存最坏情况（用于容量规划）
SELECT current_setting('work_mem')::int / 1024                       AS work_mem_mb,
       current_setting('max_connections')::int                       AS max_conn,
       current_setting('max_parallel_workers_per_gather')::int       AS par_per_query,
       current_setting('work_mem')::int / 1024
         * current_setting('max_connections')::int
         * current_setting('max_parallel_workers_per_gather')::int   AS worst_case_mb;
text
 work_mem_mb | max_conn | par_per_query | worst_case_mb
-------------+----------+---------------+---------------
          16 |      300 |             2 |          9600
(1 row)

9.6GB 的最坏情况内存占用——这就是为什么「连接数 × work_mem」必须纳入容量规划。用连接池把数据库侧连接数降到 100 以内，这个数字会降到 3.2GB。

实战示例 ​

示例一：诊断一次「排序落盘」问题 ​

sql
-- 第 1 步：开启临时文件日志，定位哪些查询在落盘
ALTER SYSTEM SET log_temp_files = 0;   -- 0 表示记录所有临时文件
ALTER SYSTEM SET log_min_duration_statement = '1s';
SELECT pg_reload_conf();
bash
# 第 2 步：从日志中找到落盘的查询及其临时文件大小
grep -E "temporary file" /var/log/postgresql/postgresql-18-main.log | tail -5
text
2026-09-22 10:12:03.117 CST [1301] LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp1301.0",
         size 536870912
2026-09-22 10:12:03.117 CST [1301] STATEMENT:  SELECT customer_id, sum(amount) FROM orders
         GROUP BY customer_id ORDER BY 2 DESC;
2026-09-22 10:12:05.882 CST [1301] LOG:  duration: 2841.117 ms  statement: SELECT customer_id, ...
sql
-- 第 3 步：用 EXPLAIN 确认是哪个节点落盘
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(amount) AS total
FROM orders
GROUP BY customer_id
ORDER BY total DESC
LIMIT 20;
text
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=298412.51..298412.56 rows=20 width=24) (actual time=2841.102..2841.104 rows=20 loops=1)
   ->  Sort  (cost=298412.51..299287.51 rows=350000 width=24) (actual time=2841.100..2841.101 rows=20 loops=1)
         Sort Key: (sum(amount)) DESC
         Sort Method: external merge  Disk: 524288kB      ← 落盘 512MB
         ->  HashAggregate  (cost=248291.00..260041.00 rows=350000 width=24) (actual time=2612.441..2718.882 rows=348291 loops=1)
               Group Key: customer_id
               Batches: 1  Memory Usage: 4096kB              ← 哈希聚合本身未溢出
               ->  Seq Scan on orders  (cost=0.00..185123.00 rows=6640224 width=16) (actual time=0.014..812.201 rows=6640224 loops=1)
 Planning Time: 1.204 ms
 Execution Time: 2841.556 ms
sql
-- 第 4 步：只对这条查询放行更多 work_mem（而不是全局调大）
SET work_mem = '512MB';

EXPLAIN (ANALYZE) SELECT customer_id, sum(amount) AS total
FROM orders GROUP BY customer_id ORDER BY total DESC LIMIT 20;
text
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Limit  (cost=298412.51..298412.56 rows=20 width=24) (actual time=1204.882..1204.884 rows=20 loops=1)
   ->  Sort  (cost=298412.51..299287.51 rows=350000 width=24) (actual time=1204.880..1204.881 rows=20 loops=1)
         Sort Method: top-N heapsort  Memory: 26kB               ← 内存中完成
         ...
 Execution Time: 1205.117 ms                                            ← 从 2841ms 降到 1205ms
sql
-- 第 5 步：为这个报表用户持久化该设置（会话级生效）
ALTER ROLE analyst SET work_mem = '512MB';
RESET work_mem;   -- 自己的会话恢复默认

示例二：验证 shared_buffers 的实际效果 ​

sql
-- 建一个大表，保证其体积远超 shared_buffers（128MB）
CREATE TABLE buf_demo AS
SELECT g AS id, repeat('x', 100) AS pad
FROM generate_series(1, 5000000) AS g;

SELECT pg_size_pretty(pg_total_relation_size('buf_demo')) AS table_size;
text
 table_size
------------
 715 MB
(1 row)
sql
-- 第 1 次全表扫描：全部从磁盘读（shared_buffers 只有 128MB）
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT count(*) FROM buf_demo;
text
                         QUERY PLAN
--------------------------------------------------------------
 Aggregate  (cost=108333.00..108333.01 rows=1 width=8)
   Buffers: shared read=91556                  ← 全部走磁盘读
   ->  Seq Scan on buf_demo  (cost=0.00..95833.00 rows=5000000 width=0)
         Buffers: shared read=91556
sql
-- 第 2 次扫描：大部分页已在 shared_buffers 中命中
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT count(*) FROM buf_demo;
text
                         QUERY PLAN
--------------------------------------------------------------
 Aggregate  (cost=108333.00..108333.01 rows=1 width=8)
   Buffers: shared hit=1503 read=90053          ← 约 1503 页命中缓存
   ->  Seq Scan on buf_demo  (cost=0.00..95833.00 rows=5000000 width=0)
         Buffers: shared hit=1503 read=90053
bash
# 清理环境
sql
DROP TABLE buf_demo;

shared read=91556 与 shared hit=1503 的对比直观说明：shared_buffers 只能缓存住表的一部分。表体积 715MB 而缓存 128MB，所以第二次扫描仍要读 90053 页。这也解释了为什么「把小热表留在缓存里」比「盲目调大 shared_buffers」更有效。

示例三：查看单个会话的内存分布 ​

sql
-- 找出内存占用最高的会话（通过 pg_backend_memory_contexts 需在同会话内查询，
-- 因此这里先用 pg_stat_activity 定位可疑会话，再切过去细查）
SELECT pid,
       now() - backend_start                AS lifetime,
       pg_size_pretty(...)                  AS dummy
FROM pg_stat_activity
WHERE backend_type = 'client backend'
LIMIT 0;
sql
-- 在当前会话内查看内存上下文明细（按占用排序）
SELECT name,
       pg_size_pretty(total_bytes) AS total_bytes,
       pg_size_pretty(used_bytes)  AS used_bytes,
       (total_bytes - used_bytes) / 1024 AS free_kb
FROM pg_backend_memory_contexts
ORDER BY total_bytes DESC
LIMIT 10;
text
         name          | total_bytes | used_bytes | free_kb
-----------------------+-------------+------------+---------
 CacheMemoryContext    | 8601 kB     | 7804 kB    |     797
 TopMemoryContext      | 1024 kB     | 316 kB     |     708
 ExecutorState         | 512 kB      | 384 kB     |     128
 TopTransactionContext | 256 kB      | 108 kB     |     148
(4 rows)
sql
-- 若有正在执行的大查询，可以看到它申请的上下文
SET work_mem = '64MB';
EXPLAIN (ANALYZE)
SELECT g, repeat('y', 200) FROM generate_series(1, 5000000) AS g ORDER BY 2 LIMIT 10;
sql
-- 执行期间另开一个会话查询当前后端的内存（需要 pid）
-- 注意：pg_backend_memory_contexts 只显示当前会话
SELECT sum(total_bytes) AS total,
       pg_size_pretty(sum(total_bytes)) AS total_pretty
FROM pg_backend_memory_contexts WHERE name LIKE '%Executor%';
text
   total   | total_pretty
-----------+--------------
 283115520 | 270 MB
(1 row)

注意事项 ​

  • effective_cache_size 不分配内存：它只是给优化器的提示。设得过大或过小都会导致执行计划偏差（前者偏向索引扫描，后者偏向顺序扫描）。
  • work_mem 是每个操作节点而非每条连接：一个查询可能有多个排序/哈希节点，并行查询时每个 worker 还会各自分配。容量规划必须乘上这些系数。
  • shared_buffers、wal_buffers、huge_pages 需要重启：它们是 postmaster 上下文，pg_reload_conf() 对它们无效。
  • shared_buffers 不是越大越好：超过物理内存 40% 通常收益递减甚至变差，因为挤占了 OS 文件缓存。
  • temp_buffers 不影响排序/哈希落盘：落盘文件走 OS 文件系统缓存，与 temp_buffers 无关，不要用调大 temp_buffers 来解决排序落盘。
  • maintenance_work_mem 是「单操作」而非「每进程叠加」：但若有多个并发的 CREATE INDEX，每个都会各自使用一份。
  • autovacuum_work_mem 独立于 maintenance_work_mem：在 autovacuum 频繁的实例上应单独设置，避免两者争抢。
  • huge_pages = on 若内核未配置大页会启动失败：先用 try（尽力而为），确认 /proc/meminfo 中 HugePages_Total 足够后再改 on。
  • 共享内存总量由 shared_buffers 决定，但实际占用更大：还要加上锁表、ProcArray、WAL buffer 等，规划时留 10%~20% 余量。
  • out of shared memory 是锁槽位不足而非内存不足：提高 max_locks_per_transaction（需重启），不是加内存。
  • max_connections 间接放大了所有本地内存：它属于 postmaster 上下文，降低它同样需要重启。
ini
# postgresql.conf
# 不推荐：把 work_mem 调到极大来「解决」所有排序问题
# work_mem = 2GB              -- 不推荐，会 OOM
sql
-- 不推荐：依赖 shared_buffers 缓存整张大表
-- ALTER SYSTEM SET shared_buffers = '100GB';   -- 不推荐
-- 推荐做法：用合适的索引减少扫描量，而不是加大缓存

最佳实践 ​

  • shared_buffers 设为物理内存的 25%：这是经过长期验证的平衡点，把剩下的内存留给 OS 文件缓存。
  • effective_cache_size 设为物理内存的 50%~75%：它影响索引扫描的成本估算，设得贴合实际能让优化器做出更好的选择。
  • work_mem 保守设置 + 按需放行：全局 8~16MB，对报表类用户/数据库用 ALTER ROLE / ALTER DATABASE 单独提高。
  • 开启 log_temp_files = 0：把所有落盘的临时文件记入日志，这是发现 work_mem 不足最直接的手段。
  • maintenance_work_mem 给足：512MB~2GB 能显著加快 VACUUM 与索引创建；同时把 autovacuum_work_mem 单独设置为 256MB 左右。
  • 用连接池压低数据库侧连接数：把 max_connections 控制在 100~300，是控制本地内存总量的最有效手段。
  • 大内存实例启用大页：huge_pages = try 先试，验证后可改 on，减少 TLB miss。
  • 容量规划要用最坏情况公式：work_mem × max_connections × 并行度 作为本地内存的上限约束，确保不超过物理内存的剩余部分。
  • 监控三个指标：pg_stat_database.blks_hit/blks_read（缓存命中率，应 > 99%）、temp_bytes（临时文件增长趋势）、pg_stat_wal.wal_buffers_full（WAL 缓冲压力）。
  • 不要在业务高峰期改内存参数：shared_buffers 相关的重启会中断所有连接；动态参数也应分批灰度。

下一节

继续学习：磁盘文件结构

参考链接 ​