家用 PostgreSQL 调优实战：从默认配置到性能翻倍

一台 16GB 内存的家用服务器，PostgreSQL 跑起来总觉得慢，改配置又怕改坏——这篇文章用真实的配置文件逐项拆解，告诉你哪些参数值得动，动多少合适。

开场：一次"改坏"的调优经历

上周帮朋友调一台家用服务器上的 PostgreSQL，他信誓旦旦说"我照着网上的教程改了 shared_buffers 到 8GB，结果数据库直接起不来了"。我一看配置——16GB 的机器，shared_buffers 给了 8GB，再加上操作系统缓存、其他服务，内存直接 OOM。

这种故事在家庭服务器圈子里太常见了。PostgreSQL 的 postgresql.conf 有几百个参数，但真正需要动的也就十来个。今天我们用一台真实的家用机器配置，逐项拆解。

硬件家底：你的机器够干多大的活

先看看今天的实验对象：

16GB 内存、SSD、12 代酷睿——这是典型的家用 NAS 或者小型工作站配置。够用，但没有太多余量可以挥霍。

第一梯队：内存分配三兄弟

PostgreSQL 的性能调优，80% 就是调内存分配。搞明白三个参数，基本就入门了。

shared_buffers — 数据库的"工作台"

shared_buffers = 2GB

这是 PostgreSQL 自己用来缓存数据页的内存。你可以把它想象成厨师的工作台——台面越大，能同时摊开的食材越多，不用反复跑冰箱拿东西。

经验法则：物理内存的 25%。16GB 的机器给 2GB 是合理的。给太大（比如 8GB），操作系统缓存会被挤掉，反而变慢；给太小，频繁读磁盘，性能拉胯。

验证方法：

SELECT 
    pg_size_pretty(pg_database_size(current_database())) AS db_size,
    pg_size_pretty(pg_total_relation_size('projects')) AS table_size;

如果你的数据库总量小于 shared_buffers，那说明数据全在缓存里，调大没意义。

work_mem — 排序和哈希的"临时工位"

work_mem = 64MB

每次做排序、哈希连接、合并连接时，PostgreSQL 会申请这块内存。想象成临时工位——来一个查询占一个，复杂查询可能占好几个。

坑在这里： 这个值是每个操作算一次，不是每个查询。一条 SQL 带 3 个子查询、每个子查询有排序和哈希，可能吃掉 3 × 2 × 64MB = 384MB。如果同时 20 个连接，理论上最坏情况 20 × 384MB ≈ 7.5GB。

家用建议： 保守点给 32-64MB。64MB 已经不小了，足够大多数查询用。如果你跑复杂分析查询，可以按会话临时调：

SET work_mem = '256MB';  -- 只对当前会话生效

maintenance_work_mem — 维护操作的"大仓库"

maintenance_work_mem = 1GB

VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 这些维护操作用的内存。1GB 给得好——这些操作不常跑，但跑起来需要大内存才能快。

注意： autovacuum 也会用这个值。如果你有几十个表同时 vacuum，每个 1GB，内存可能扛不住。家用场景表少，1GB 没问题。

第二梯队：查询优化器的"导航仪"

这两个参数告诉查询优化器"磁盘有多快"，直接影响它选择哪种执行计划。

random_page_cost — SSD 时代的必调参数

random_page_cost = 1.1

默认值是 4.0，那是给机械硬盘设计的——机械磁盘随机读比顺序读慢 4 倍。但 SSD 的随机读和顺序读差距很小，1.1 是主流推荐值。

不调会怎样？ 优化器会高估随机读的代价，倾向于全表扫描而非索引扫描。你的索引白建了。

验证优化器是否"听你的"：

EXPLAIN ANALYZE 
SELECT * FROM projects WHERE stars > 200000;

看输出里有没有 Index Scan。如果明明有索引却走 Seq Scan，大概率就是 random_page_cost 太高了。

effective_cache_size — 告诉优化器"你能用多少缓存"

effective_cache_size = 10GB

这不是真的分配内存，而是告诉优化器"操作系统大概能帮你缓存多少数据"。它用这个值来估算索引扫描的收益。

经验法则：物理内存的 50-75%。16GB 给 10GB 合理——留 6GB 给操作系统和其他进程。

第三梯队：WAL 和检查点

wal_buffers — 写入的"缓冲池"

wal_buffers = 32MB

WAL（Write-Ahead Log）是 PostgreSQL 的"记账本"，所有写操作先写这里再落盘。默认值只有几百 KB，高并发写入时会成为瓶颈。

32MB 是个好值。 对于家用场景，写入并发不高，已经绰绰有余。

checkpoint_timeout 和 max_wal_size

checkpoint_timeout = 15min
max_wal_size = 1GB

检查点是 PostgreSQL 把脏页刷到磁盘的时机。默认 5min 改成 15min，减少了检查点频率，避免 I/O 突刺。max_wal_size = 1GB 配合 15 分钟间隔，给足了缓冲空间。

类比： 想象你在记笔记，检查点就是"每隔一段时间把草稿整理到正式笔记本"。整理得太频繁，打断思路；间隔太长，草稿堆太多，整理时手忙脚乱。15 分钟是个平衡点。

其他值得关注的参数

effective_io_concurrency

effective_io_concurrency = 200

SSD 时代必调。默认值是针对机械盘的，改成 200 让 PostgreSQL 可以同时发起更多 I/O 请求，充分利用 SSD 的并行能力。

max_connections

max_connections = 20

家用服务器 20 个连接够了。每个连接都占内存（约 10MB），连接数太多反而拖慢性能。如果你用连接池（如 PgBouncer），可以进一步降低 PostgreSQL 端的连接数。

log_min_duration_statement

log_min_duration_statement = 1000

超过 1 秒的 SQL 自动记录到日志。调优期间设成 0（记录所有），调完改回 1000。这是你发现慢查询的最简单工具。

调优前后对比：不是所有参数都有明显效果

老实说，在数据量小（几千条）、并发低（单用户）的家用场景，很多调优效果不明显。PostgreSQL 的默认配置已经很保守很好了。

真正能感知到差异的场景：

结论： 小数据量下，调优是锦上添花；大数据量下，调优是雪中送炭。

踩坑记录

坑 1：shared_buffers 不是越大越好

有篇文章说"shared_buffers 给物理内存的 75%"，照着 16GB 的机器给 12GB——结果系统直接 OOM。PostgreSQL 在启动时会 fork 出多个进程，每个进程都会共享这块内存，但操作系统需要足够的空闲内存来做页面缓存。

正确做法：25%，绝对上限 40%。

坑 2：work_mem 是"每个操作"不是"每个连接"

很多人看到 work_mem = 64MB 就觉得"一个连接最多用 64MB"。错。一条复杂 SQL 可能同时触发多个排序和哈希操作，每个都吃一份 work_mem。

查看当前会话内存使用：

SELECT pid, usename, application_name, 
       pg_size_pretty(pg_stat_get_backend_memory_contexts(pid)) 
FROM pg_stat_activity;

坑 3：改完配置别忘了 reload

# 不需要重启的参数（大部分参数）
sudo systemctl reload postgresql

# 需要重启的参数（shared_buffers、max_connections 等）
sudo systemctl restart postgresql

不确定要不要重启？看配置文件里注释有没有 (change requires restart)。

配置模板：16GB 家用服务器

直接抄作业：

# === 内存 ===
shared_buffers = 4GB          # 物理内存的 25%
work_mem = 64MB               # 足够大多数查询
maintenance_work_mem = 1GB    # VACUUM/CREATE INDEX 用
effective_cache_size = 10GB   # 物理内存的 60-75%

# === I/O ===
random_page_cost = 1.1        # SSD 必调
effective_io_concurrency = 200  # SSD 并行 I/O
wal_buffers = 32MB            # WAL 缓冲

# === 检查点 ===
checkpoint_timeout = 15min
max_wal_size = 1GB

# === 连接 ===
max_connections = 20          # 家用足够

# === 日志 ===
log_min_duration_statement = 1000  # 慢查询阈值 1 秒

总结

PostgreSQL 调优不是玄学，是算术：

1. shared_buffers 给 25% 内存，别贪
2. work_mem 保守给，按需临时调
3. random_page_cost 改 1.1，SSD 时代必须
4. effective_cache_size 告诉优化器你有多少缓存
5. 其他参数，家用场景默认值基本够用

最重要的一句话：先测量，再调优。 EXPLAIN ANALYZE 是你最好的朋友，别凭感觉改配置。

下篇预告：当数据量上百万条，PostgreSQL 的查询计划选择会发生什么变化？索引策略又该怎么调？