我的数据库并不慢。
它只是被饿着。
我盯着一条理论上只该跑几毫秒的查询。
它跑了 42 秒。
没有流量激增。
没有错误发布。
没有缺失索引。
只有一个安静又羞辱人的事实：
PostgreSQL 完全是在按我说的做。
只是我从来没告诉过它任何有用的信息。
我只改了 postgresql.conf 里的一行。
下一次执行：0.38 秒。
同一条查询。
同一份数据。
同一台机器。
整整 100 倍。
很多人都相信一个关于性能的谎言。
只要 PostgreSQL 查询慢了，脑内的排查清单通常是这样的：
- 少索引了？
- 索引用错了？
- 要不要复合索引？
- 要不要部分索引？
- SQL 重写一遍？
- ORM 是不是在作恶？
- 顺便骂一句已经离职的同事
我全做了。
我加了在 EXPLAIN 里看起来很漂亮的索引。
我重写了 join。
我拆了子查询。
我像个疯狂科学家一样跑各种变体。
查询还是慢得离谱。
真正让人不舒服的是这一点：
执行计划是对的，执行过程是错的。
PostgreSQL 知道该怎么做。
它只是没内存把事情做好。
这条把我整破防的查询，并不稀奇。
- 1200 万行
- 一个大聚合
- 两个 join
- 一个时间条件
每天在真实系统里跑上几千次的那种。
SELECT u.id,
COUNT(o.id) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id;
索引都在：
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at);
EXPLAIN ANALYZE 看起来也完全合理。
但执行时间就是离谱。
为什么？
真正的凶手很安静：磁盘。
PostgreSQL 在不断地往磁盘写临时文件。
不是因为查询写错了。
而是因为内存上限被设定成了一个注定要受苦的值。
开箱即用的 PostgreSQL 假设你：
- 在一台很小的机器上
- 有多个应用争抢内存
- 安全性优先于性能
这些默认值保守到近乎残忍。
毁掉一切的，就是这一行：
work_mem = 4MB
四兆。
而且是每一个操作。
这意味着：
一旦超过 4MB，就会被迫落盘。
在现代工作负载下，4MB 已经不是“保守”。
而是失职。
PostgreSQL 实际执行路径大概是这样：
Query
|
Hash Join
|
+--------+--------+
| |
Users Table Orders Table
| |
Index Scan Seq Scan
|
Hash Aggregate
|
Disk Spill
这个 Disk Spill，就是性能的坟墓。
每一次超过 4MB，PostgreSQL 都会把中间结果写成临时文件。
不管你是 SSD 还是 NVMe，磁盘依然比内存慢几个数量级。
我只改了一行：
work_mem = 256MB
重启 PostgreSQL。
再跑一次同样的查询。
结果看起来像作弊。
之前：
- 执行时间：42.3 秒
- 临时文件：1.8 GB
- CPU：基本在发呆
- 磁盘：惨叫
之后：
- 执行时间：0.38 秒
- 临时文件：0
- CPU：终于在干活
- 磁盘：一片安静
没有重写 SQL。
没有新索引。
没有黑魔法。
只是给了足够的内存，让它在 RAM 里把活干完。
这件事让人不舒服，是因为它戳穿了我们的认知。
我们习惯把数据库当黑盒：
内存配置看起来：
但数据库是物理系统。
它搬字节。
它分配内存。
它撞磁盘。
你要是把它饿着，再优雅的 SQL 也救不了你。
我做了一个简单但让我很生气的对比测试：
| work_mem |
执行时间 |
临时磁盘 |
| 4MB |
42.3s |
1.8GB |
| 64MB |
6.9s |
220MB |
| 256MB |
0.38s |
0B |
这甚至都算不上优化。
这只是人道主义救援。
PostgreSQL 的默认配置假设你：
- 只有 1GB 内存
- 在共享主机上
- 跑的是不可预测的工作负载
而你的生产数据库很可能是：
在这种机器上用默认配置跑现代系统，
就像开着跑车，却因为说明书说“安全”，一直挂在一档。
还有一个大多数开发者从没真正理解过的点：
PostgreSQL 的内存不是一个池。
它是按用途切碎的。
简化模型是这样：
总内存
|
+-- shared_buffers
|
+-- work_mem × 活跃操作数
|
+-- maintenance_work_mem
|
+-- OS cache
几乎所有人都只盯着 shared_buffers。
这是个常见错误。
真正会爆炸的是 work_mem。
它是乘法。
如果你有：
- 20 个并发查询
- 每个查询 3 个 sort / hash
- work_mem = 256MB
理论上你就可能吃掉 15GB 内存。
这就是 PostgreSQL 默认值很低的原因。
也是你必须有意识地调的原因。
很多人不敢调，是因为一个恐惧：
“万一 PostgreSQL 把内存吃爆了怎么办？”
这个恐惧是合理的。
但解决方案不是永远用默认值。
而是理解你的负载。
我选 256MB 不是拍脑袋。
我的过程是这样的：
第一步：看并发
SELECT COUNT(*)
FROM pg_stat_activity
WHERE state = 'active';
峰值活跃查询：12。
第二步：看执行计划
每个查询有：
- 一个 hash aggregate
- 一个 hash join
也就是两个 work_mem 消费点。
第三步：算账
12 × 2 × 256MB ≈ 6GB。
机器有 32GB 内存。
完全安全。
为什么索引没救我？
因为索引只能减少 I/O。
它解决不了内存不足。
我的瓶颈在聚合，不是查找。
如果你的查询在：
那内存比索引更重要。
我经常听到一句话：
“我们用的是 NVMe，磁盘很快。”
不。
磁盘只是比机械硬盘快。
它依然比 RAM 慢得多。
RAM 带宽是几十 GB/s。
磁盘延迟是微秒级。
PostgreSQL 的临时文件，是同步的痛苦。
最危险的反模式，我见过太多次：
- 查询慢
- 加索引
- 还是慢
- 再加索引
- 数据库开始臃肿
- 写入变慢
- vacuum 变痛苦
- 所有人一脸懵逼
与此同时：
work_mem = 4MB
几乎没有团队会认真 review postgresql.conf。
它就那样躺着，好几年不动。
但正是这个文件，决定了你的数据库是在内存里工作，还是在磁盘上挣扎。
我现在的规则很简单。
在改 SQL 之前，先问：
索引，只解决其中一种。
我更喜欢这样理解 PostgreSQL：
工作台太小，活就只能在地上干。
慢，而且狼狈。
那一行配置，几个月后仍在持续给我回报：
- 应用 p99 延迟下降
- 磁盘利用率降低
- IO wait 减少
- 高负载下的查询时间更稳定
没有改一行应用代码。
当然，这不是万能药。
如果你的查询是：
那它救不了你。
但如果执行计划看起来很正常，执行却慢得离谱，
先看内存。
我现在用的检查清单是：
在加索引之前：
- 看 work_mem
- 看临时文件
- 看磁盘 I/O
- 看并发
索引，永远排在最后。
最后再重复一遍那一行，防止你滑走了：
work_mem = 256MB
负责任地调。
但一定要调。
数据库是诚实的。
它永远在按你的配置行事。
如果性能很差，通常不是 PostgreSQL 蠢。
而是我们从没告诉过它：
它到底可以用多少内存。
这锅，只能我们自己背。
如果你想了解更多关于数据库性能调优的实践技巧，欢迎来 云栈社区 交流讨论，这里聚集了许多热衷于后端架构与数据库技术的开发者。
发表于 昨天 08:18
|
查看: 7|
回复: 0
