1. 先看四组数字慢数据库的第一轮体检很多人拿到一台慢得离谱的 PostgreSQL 数据库第一反应就是冲进postgresql.conf里一顿改参数shared_buffers 拉满、work_mem 调大、max_connections 干到 2000。我干这行十多年这类开局盲调的案例见得太多了十有八九最后都调出了新问题。先说一个真实场景。前年我接手过一个电商业务库服务器配置其实不差——16 核 CPU、64GB 内存、SSD 磁盘但 PostgreSQL 装完基本就是默认配置跑起来的。业务跑了两三个月平时看着还行某次促销活动流量一上来数据库 CPU 直接飙到 98%慢查询日志里一大堆两秒以上的 SQL连监控页面打开都转圈。我上去做的第一件事不是改参数而是先跑了一轮体检把下面这四组数字全部看了一遍。1.1 第一组连接数与活跃会话连接数是最容易被忽视的先兆指标。很多人只知道看 CPU 和内存却不知道数据库其实是被挤死而不是被压死的。先用下面这条 SQL 看一下当前连接状态SELECT state, wait_event_type, wait_event, count(*) FROM pg_stat_activity GROUP BY state, wait_event_type, wait_event ORDER BY count(*) DESC;输出里重点看几个 stateactive正在执行 SQL 的会话。idle连接还在但啥事没干。这种连接本身不消耗多少 CPU但占着连接名额。idle in transaction事务开了不提交也不回滚一直挂着。这是最坑的状态后面我会单独讲。wait_event_type Lock说明会话在等锁排队排到怀疑人生。那次排查时我看到的数字是总共 280 个连接其中 active 只有 30 个左右idle in transaction 占了 90 多个剩下的全是 idle。数据库自身能处理的并发其实远没有那么高大部分会话都堵在事务没提交和锁等待上。1.2 第二组缓存命中率缓存命中率这个概念我可以给你一句话解释PostgreSQL 要读一个数据页时先去自己的内存缓冲区shared buffers里找找到了就是命中找不到就得去磁盘上翻翻磁盘的速度比内存慢好几个数量级。查缓存命中率用这条 SQLSELECT blks_hit AS 内存命中次数, blks_read AS 磁盘读取次数, round(blks_hit::numeric / (blks_hit blks_read) * 100, 2) AS 缓存命中率 FROM pg_stat_database WHERE datname current_database();99% 以上算正常低于 99% 就得警惕了。如果是 95% 甚至更低优先怀疑两件事shared_buffers 配得太小或者某些大表在频繁做全表扫描根本没机会复用缓存。我见过一个运营报表库命中率常年 93%查出来原因是有人每天凌晨跑全量汇总任务把所有表的缓存都冲了个干干净净白天业务来查就又得重新去磁盘搬数据。1.3 第三组慢查询与锁等待慢查询这块先确认你有没有开日志记录SHOW log_min_duration_statement;如果这个值是-1说明慢查询日志根本没开等于让数据库裸奔。建议一开始先设为 1000ms也就是超过 1 秒的 SQL 全部记录下来跑上一两天再根据实际情况收紧到 500ms 或者更短。锁等待则是很多慢查询的幕后黑手。SQL 本身可能只要 20ms但前面有个事务一直锁着那行数据它就得在后面排队等一等等个十几秒。用这条 SQL 看所有等待中的会话SELECT pid, datname, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type IS NOT NULL;wait_event_type Lock 的就是锁等待后面pg_stat_activity里会显示它具体在哪把锁上排队配合 pg_locks 可以把它等的那把锁精确找出来。这个排查链路我在第 4 部分细讲。1.4 第四组系统层指标数据库自己的指标看完了还得看操作系统层面。我的习惯是不管远程还是本机先跑这么几步free -h iostat -x 1 3 uptimefree 看有没有 swap 使用——一旦开始 swap数据库性能会断崖式下跌那种延迟你从应用层的请求耗时曲线上能看得一清二楚。iostat 看磁盘的%util和await如果%util长期在 90% 以上说明磁盘已经是瓶颈了这时候你调数据库参数意义不大得先想想是不是该降低 IO 量、加内存或者换磁盘。uptime 看 load average如果 load 比 CPU 核数还高很多基本上就是 CPU 排队了。这四组数字看完我心里基本对数据库的状态有个谱了。什么状态该动参数、什么状态该改 SQL、什么状态该先处理连接和事务方向就出来了。2. postgresql.conf 里真正值得动手的几项体检做完确认不是连接堆积、不是磁盘 IO 爆掉再往下走就是配置文件调优。你要知道PostgreSQL 默认配置的定位是在任何环境下都能跑起来它保证的是下限不是最优。对一台确定规格的服务器下面这几个参数才是真正值得动手的。2.1 shared_buffers 与 effective_cache_size先盘明白内存预算shared_buffers 是 PostgreSQL 自己的共享内存缓冲区所有表数据和索引的页都会经过这里。默认值只有 128MB这在小内存 VPS 上没问题但对真正的业务服务器来说明显不够。业界一个比较稳妥的经验值物理内存的 25% 左右上限一般不超过 8~10GB再往上收益会明显递减因为 PostgreSQL 还需要依赖操作系统页缓存来兜底。假设服务器是 64GB 内存shared_buffers 设 16GB 是合理的如果是 8GB 的小机器设 2GB 左右就行。另一个很容易被忽略的参数是effective_cache_size。它跟 shared_buffers 完全不是一回事——它告诉 PostgreSQL 查询规划器这台机器上操作系统页缓存大概能给你提供多少内存。说白了这是一个估算值用于帮助规划器判断走索引还是走全表扫描更划算。通常可以设成物理内存的 60%~75%比如 64GB 内存就设 48GB。这两个参数搞明白了很多新手就不会再把它们混着调。shared_buffers 管的是 PostgreSQL 自己的缓存池effective_cache_size 管的是给规划器的信心值。2.2 work_mem 与 maintenance_work_mem排序和索引的代价work_mem 是用来做排序、哈希连接、临时表的会话级内存。注意关键词会话级。也就是说每个连接执行涉及排序或哈希操作时都可能单独分配一份 work_mem 大小的内存。这不是全局值不能简单按服务器内存大就调大来理解。默认 4MB 对大多数 OLTP 查询来说够用但碰上一些需要排序几十万行的查询4MB 很快就会溢出到临时文件性能断崖式下跌。我曾经处理过一个报表查询排序量很大临时文件写了几百 MB查询跑了 27 秒。把 work_mem 从 4MB 调到 64MB 之后临时文件直接消失查询降到 1.2 秒。但这里有个必须算的账。假设一台 8GB 内存的服务器max_connections 200你把 work_mem 调到 64MB极端情况下 200 个连接同时做排序内存占用就是200 × 64MB 12.8GB再加上 shared_buffers 和其他开销8GB 内存瞬间爆掉。所以 work_mem 的合理值不是拍脑袋定的我一般这么算安全值 ≈ (可用内存 - shared_buffers - 系统预留) / 预期并发排序连接数大多数通用业务场景work_mem 处在 16MB 到 64MB 之间是比较稳的一个区间前提是你的连接数没有失控。连接池做好、并发压下来work_mem 可以适当放大连接数失控的话任何 work_mem 都救不回来。maintenance_work_mem 则不一样它专门用于维护性操作——VACUUM、CREATE INDEX、添加外键等。这些操作通常一次并发量不高可以把值给大一些64MB 到 1GB 都常见。索引重建和 VACUUM 的快慢非常依赖这个值我一般直接设 256MB 起步大表环境直接 1GB。2.3 checkpoint 与 WAL磁盘 IO 抖动的根源讲 WAL 和 checkpoint我尽量用大白话。PostgreSQL 任何修改数据的操作会先写入 WAL 日志预写日志数据页本身先在内存里变脏等到 checkpoint 或 buffer 写满时才刷到磁盘。checkpoint 就是那个把脏页刷下去的动作。问题在于如果 checkpoint 触发得太频繁或者刷得不够分散磁盘会在某个时间点突然承受一大波写入压力表现出来就是数据库每隔一段时间就有一次 IO 尖峰应用层请求延迟跟着一起抖动。这个现象我调试过好多次现象是CPU 不高、内存不高但每过几分钟就卡一下。关键参数checkpoint_timeout默认 5 分钟一般不宜低于 5 分钟很多生产库用 10~15 分钟。max_wal_size默认只有 1GB这个值太小时checkpoint 会被 WAL 写满逼着提前发生。建议按你的业务写入量调大8GB 以上都常见。checkpoint_completion_target控制 checkpoint 期间脏页刷盘要刷多快默认 0.9 已经很合理表示尽量分散在整个 checkpoint 周期内刷完。还有wal_buffers默认 16MB 其实是够用的不用刻意动。除非你明确知道自己写了什么大事务否则这个参数很少是瓶颈。2.4 autovacuum延迟炸弹autovacuum 可能是最容易被忽视、爆起来又最致命的系统。PostgreSQL 的 MVCC 机制决定了更新和删除的行并不会立刻从物理文件中消失而是标记为死亡版本dead tuple。autovacuum 的职责就是清理这些死元组保持表和索引不会持续膨胀。默认的autovacuum_vacuum_scale_factor是 0.2意思是表中 20% 的元组变成死亡版本时触发一次 vacuum。对大表来说这个触发条件太迟钝了。比如一张 10 亿行的订单表20% 就是 2 亿行死元组在触发之前表已经膨胀得不成样子索引变得巨大查询性能直线下降。我处理过不少这样的案例一个表行数没怎么涨查询却越来越慢查pg_stat_user_tables里的n_dead_tup发现都两三千万了。这个问题的修复不是马上跑VACUUM FULL那会对业务造成锁等待和磁盘暴增而是先把 autovacuum 的触发阈值调敏感比如autovacuum_vacuum_scale_factor 0.05。或者在很小的表上直接用autovacuum_vacuum_threshold控制最小触发量。再手动对热点表补一轮VACUUM。关于 autovacuum 的参数调整我的建议是默认值不适合大表调大autovacuum_work_mem或 maintenance_work_mem来加快 vacuum 速度调低 scale_factor 来提高清理频率。具体数值没有银弹得看着监控数据调比如 vacuum 进程经常出现在慢查询里就说明它干得太吃力了。2.5 改动参数的顺序与生效方式参数改完不是全部要重启。shared_buffers这类需要重启work_mem、effective_cache_size、max_wal_size这类是 SIGHUP 级别的reload一下就行pg_ctl reload修改完用SHOW验证一下有没有实际生效SHOW shared_buffers; SHOW work_mem; SHOW max_wal_size;我的经验是这些参数千万不要一次性全部改完。每改一组用 pgbench 或者业务压测脚本跑一轮记录响应时间、TPS、IO 指标观察一两天再继续动下一组。一次动太多出了问题你根本不知道是哪个参数造成的。这个习惯救过我很多次也建议所有做 DBA 相关工作的人养成。3. 查询与索引从慢 SQL 实测中总结的几种典型问题参数调优是给数据库搭好地基但地基好了SQL 本身不行照样白搭。一个 PG 性能调优的检查清单里查询侧和索引侧的排查有时候比参数调整带来的收益更大、见效也更快。3.1 EXPLAIN ANALYZE 应该怎么读很多刚入门的朋友看到 EXPLAIN 输出就懵。我的建议是其他列可以暂时不看先把actual time和rows看懂。下面是我造的一个简单例子。假设订单表orders有 500 万行我们要查某个客户最近一个月的订单EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id 12345 AND order_time 2024-06-01 ORDER BY order_time DESC;执行计划里如果看到这样一段Seq Scan on orders (cost0.00..131234.00 rows1 width100) (actual time12.834..384.210 rows158 loops1) Filter: ((customer_id 12345) AND (order_time 2024-06-01)) Rows Removed by Filter: 4999842翻译过来就是数据库把 500 万行全部怼了一遍最终只剩了 158 行其余 4999842 行都被 filter 干掉了。actual time 从 12ms 到 384ms说明它扫完之后还要逐个过滤整体耗时 384ms 就是这么来的。看到这种计划如果你的业务经常查某个客户的数据第一反应应该是customer_id上有没有索引没有的话建一个CREATE INDEX idx_orders_customer_time ON orders (customer_id, order_time DESC);重建完再 EXPLAIN ANALYZE执行计划会变成Index Scan using idx_orders_customer_time on orders (cost0.43..217.40 rows158 width100) (actual time0.038..0.162 rows158 loops1)从 384 毫秒降到 0.16 毫秒接近 2000 倍的提升而且不是靠什么高深技术就是加了一条复合索引。这种例子在真实业务里太多了所以我才强调排查慢 SQL 的第一个动作一定是 EXPLAIN ANALYZE亲眼看一下数据库到底是怎么干的。3.2 索引失效的高频场景索引建了但没走也是经常让人抓狂的事。我总结过最常见的几种索引失效函数包裹列WHERE DATE(order_time) 2024-06-01这种写法如果order_time上建了普通索引索引用不上因为数据库必须先对每一行执行DATE()函数才能比较。解决方案是改写成范围查询WHERE order_time 2024-06-01 AND order_time 2024-06-02或者建表达式索引CREATE INDEX ON orders (DATE(order_time))。隐式类型转换比如WHERE customer_id 12345如果customer_id是 bigint 而 12345 是字符串类型PostgreSQL 默认会把列类型转成字符串再做比较或者反过来一旦列被转换索引就失效了。建议保持参数类型和列类型一致或者用::bigint显式转换。左模糊LIKE %abc普通 B-tree 索引无法加速-- 你懂的前导通配符查询这类需求因为索引是有序排列的只有知道了前缀才能快速定位。LIKE abc%前缀匹配能走索引LIKE %abc就走不了。必须做这种查询的话考虑 pg_trgm 的 GIN 索引。OR 条件WHERE a 1 OR b 2且 a 和 b 分别有独立索引时规划器未必能很好地把两个索引合并起来。比较稳妥的办法是写成UNION ALL或者建复合索引具体情况要 EXPLAIN 验证。3.3 统计信息过期与 ANALYZE还有一类情况是索引建了、SQL 写法也没有问题执行计划依然选错。这时候多半是统计信息过期了规划器不知道真实的数据分布。PostgreSQL 的规划器依赖pg_statistic里的统计信息来估算每个过滤条件能筛掉多少行。如果表刚经历了大范围的 update/delete统计信息还停留在很久之前规划器就可能高估或低估结果集大小然后选一个烂计划。我自己踩过一次很深的坑一张表每天夜里批量更新一批行的状态字段但统计信息三个月没更新。结果有一条 SQL 按状态字段过滤实际只匹配 300 行规划器却估算出 500 万行于是老老实实做了全表扫描每次查询都要扫两三分钟。运行ANALYZE之后执行计划变成走索引查询 30 毫秒完成。所以如果你开着 autovacuum但查询仍然莫名变慢先手动跑一下ANALYZE orders;然后再 EXPLAIN ANALYZE确认执行计划和执行时间有没有变化。这个动作不用花钱、不用重启、几乎无风险却是我在日常调优中使用频率最高的手段之一。3.4 索引不是越多越好最后必须泼一盆冷水索引不是越多越好。每个索引都在占用磁盘空间每次写入INSERT/UPDATE/DELETE都要同步维护所有相关索引写入放大是实打实的。我的原则是先通过慢查询日志找到真正需要优化的 SQL再针对性地建索引建完用 EXPLAIN ANALYZE 验证确认收益后保留没收益就删掉。一个表如果索引超过 5~6 个你就要开始怀疑是否有冗余了。特别是那些为了可能用上而建的索引淘汰掉往往能让写入性能明显回升。4. 连接、并发与连接池数据库不是被查死的是被挤死的前面说了我接手那个电商库时连接数 280但很多连接都挂着不动。数据库为什么会被人为挤死这个机制很有必要讲透。4.1 max_connections 调大为什么是饮鸩止渴PostgreSQL 的连接模型是每个连接一个进程。也就是说每个连接都会占用一块真实的内存解析上下文、排序缓存、各种游标、临时内存等等加起来通常会达到几 MB复杂场景更高。200 个连接就是 200 个进程哪怕它们什么都不干内存管理、进程调度、上下文切换的成本也相当可观。更关键的是数据库的并发能力是有上限的。CPU 有核数IO 有带宽能同时高效执行的查询数量有限。连接数 100 时可能有 20 个并发查询正在执行连接数调到 1000 时大部分连接只是在排队等待 CPU 和锁响应时间反而更差因为调度开销变大了。所以max_connections我的习惯是按业务实际需要来定不要把上限开得太大。这是数据库的一个天花板真正要解决的是应用层怎么合理复用连接而不是让数据库多扛一倍的连接。4.2 连接池的正确姿势从应用的视角看一次请求建一条新连接是最浪费的做法。正确姿势是使用连接池——应用内连接池 数据库前端的连接池代理。应用层连接池Java 的 HikariCP、Go 的 pgxpool、Python 的 SQLAlchemy 连接池等让应用复用有限数量的长连接。很多数据库连接数爆棚本质上是应用层忘记配连接池或者连接池配太大了。中间层连接池比如 pgbouncer它在数据库前面当代理应用连 pgbouncerpgbouncer 再去连 PostgreSQL。可以把大量短连接聚合成少量长连接特别适合那种每个请求都开一个连接的脚本场景。我在那个电商库里做的其中一个改动就是把应用层的连接池最大连接数从 200 压到 50再叠加 pgbouncer 把后端连接控制在 40 个左右。数据库的连接数从 280 降到 80 以内CPU 负载反而明显下降了——因为省下的全是进程切换和内存管理开销。4.3 锁等待与 idle in transaction回到那个 90 多个 idle in transaction 的案例。当时的现象是有一个定时任务每跑一次就开一个事务中途因为某些业务数据问题抛异常退出了但事务没有 catch 住没有回滚也没有提交连接就这么一直挂着。这个事务持有了一批行级锁。其他会话更新这些行时就得在后面排队等它释放锁。这类问题的清理和预防有一套组合拳马上找出 idle in transaction 的会话评估后可以pg_terminate_backend(pid)强制终结掉。应用层加超时控制防止事务无限挂起。数据库层加上idle_in_transaction_session_timeout比如设 60 秒超时自动断开。这个参数不影响正常短事务但对排查问题、保护现场非常有用。所有涉及锁的操作加上lock_timeout一般几千毫秒就够了防止无期限地排队等锁。排查锁等待我习惯用这么一条经典 SQL找出到底是谁堵着谁SELECT blocking.pid AS blocker_pid, blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.query AS blocking_query FROM pg_locks blocked JOIN pg_stat_activity blocked_act ON blocked.pid blocked_act.pid JOIN pg_locks blocking ON blocking.locktype blocked.locktype AND blocking.database IS NOT DISTINCT FROM blocked.database AND blocking.relation IS NOT DISTINCT FROM blocked.relation AND blocking.page IS NOT DISTINCT FROM blocked.page AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple AND blocking.pid ! blocked.pid JOIN pg_stat_activity blocking_act ON blocking.pid blocking_act.pid WHERE NOT blocked.granted;这个 SQL 输出的就是某个会话正握着锁不放另一个会话在后面排队。看到它你也就明白为什么数据库明明没满业务却卡死了——问题根本不在查询性能而在锁调度。5. 实用速查清单20 条检查项表格前面讲了很多原理和案例最后我把平时排查 PostgreSQL 性能问题时会过一遍的关键项整理成一张速查表。这张表不是理论清单是我在这些年的实践中一步步筛出来的基本覆盖了从操作系统到查询语句的各个层面。你可以把它当作一个体检报告模板每次遇到性能问题就逐项过一遍。分层检查项推荐基线检查方式系统层是否存在 swap 使用无free -h系统层磁盘 IO 利用率%util低于 80%iostat -x 1系统层单机 PostgreSQL 数据目录所在磁盘是否独立独立为佳df -h连接层当前连接数与 max_connections 比例长期低于 70%pg_stat_activitySHOW max_connections连接层idle in transaction 会话数量接近 0SELECT ... WHERE state idle in transaction连接层是否启用 idle_in_transaction_session_timeout建议启用如 60 秒SHOW idle_in_transaction_session_timeout连接层应用是否使用了连接池是应用配置检查缓存层缓存命中率高于 99%pg_stat_database的 blks_hit/blks_read缓存层shared_buffers 设置物理内存约 25%不超过 8~10GBSHOW shared_buffers缓存层effective_cache_size 设置物理内存 60%~75%SHOW effective_cache_size内存层work_mem 是否导致临时文件大量产生临时文件少无大量 disk sort观察临时文件目录或EXPLAIN ANALYZE内存层maintenance_work_mem 大小256MB 起步SHOW maintenance_work_memWAL 层max_wal_size 是否过小导致频繁 checkpoint按写入量设 4~16GBSHOW max_wal_size 观察 IO 尖峰WAL 层checkpoint_completion_target0.9 左右SHOW checkpoint_completion_target维护层autovacuum 是否开启onSHOW autovacuum维护层大表 n_dead_tup 是否持续走高无明显膨胀查询pg_stat_user_tables查询层慢查询日志是否已开启log_min_duration_statement 设为 1000ms 或更低SHOW log_min_duration_statement查询层pg_stat_statements 是否已安装建议安装SELECT * FROM pg_stat_statements LIMIT 1查询层慢 SQL 是否都做过 EXPLAIN ANALYZE是且无全表扫描大表逐条验证查询层统计信息是否过期最近 ANALYZE 过SELECT last_analyze FROM pg_stat_user_tables这张表你在排查时对着走一遍大概率能找到问题的大方向。不过基线并不是硬性规定具体业务要具体修正。比如一个内存只有 2GB 的轻量应用shared_buffers 就不可能 25% 地去配要给操作系统留足余量一个写密集型系统对 WAL 参数的感受也会和读多写少的系统完全不同。这套检查项配合一个习惯效果会更好把pg_stat_statements开起来。它会把所有 SQL 的调用次数、总耗时、平均耗时、IO 开销等数据统计出来按月分析哪个 SQL 是真正拉低整体性能的。很多人在性能优化上费了好大劲结果发现优化的目标 SQL 根本不是业务里最常跑的那条原因就是没有数据支撑的时候谁都容易拍脑袋。开起来很简单shared_preload_libraries里加上pg_stat_statements重启再CREATE EXTENSION pg_stat_statements;剩下的就交给时间去积累数据。我每接手一个新的 PostgreSQL 实例第一个开启的扩展几乎都是它这比任何调优参数都更早、更准确地告诉我系统真正的压力在哪。