PostgreSQL CPU 高的常见原因及排查思路如下一、最常见原因1.慢查询 / 缺少索引大表全表扫描、未使用索引的 WHERE/JOIN 条件复杂聚合、窗口函数、递归 CTE 消耗大量 CPU排查pg_stat_statements查看总耗时/调用次数最高的 SQL2.并发连接过多连接数远超 CPU 核心数上下文切换开销大大量空闲连接也会占用内存和少量 CPU排查pg_stat_activity查看活跃/空闲连接数3.锁竞争与等待长时间持有锁导致其他查询排队看似卡死实则 CPU 在等待死锁检测、行级锁竞争排查pg_lockspg_stat_activity关联分析4.VACUUM / AUTOVACUUM 频繁运行表膨胀严重autovacuum 持续工作大量死元组需要清理排查pg_stat_user_tables查看n_dead_tup、last_vacuum、last_autovacuum5.checkpoint / WAL 写入压力checkpoint 过于频繁刷脏页压力大checkpoint_completion_target配置不当排查pg_stat_bgwriter日志中是否有 checkpoint starting 过于频繁二、其他可能原因表格原因说明大量短连接连接建立/断开开销建议用连接池PgBouncer不合理的配置shared_buffers过小导致频繁磁盘 I/O 间接推高 CPUwork_mem过小导致大量磁盘排序数据类型隐式转换导致索引失效走全表扫描触发器/存储过程逻辑复杂在数据库层做过多业务计算逻辑复制槽滞后导致 WAL 堆积后台进程持续工作外部扩展/FDW外部表查询拉取大量数据在本地处理三、快速排查命令sql-- 1. 查看当前活跃查询及运行时间 SELECT pid, usename, state, query_start, now() - query_start AS duration, query FROM pg_stat_activity WHERE state active AND query NOT LIKE %pg_stat_activity% ORDER BY duration DESC; -- 2. 查看慢查询统计需先安装 pg_stat_statements SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; -- 3. 查看表膨胀和 vacuum 情况 SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY n_dead_tup DESC; -- 4. 查看锁等待 SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype blocked_locks.locktype JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid blocking_locks.pid WHERE NOT blocked_locks.granted;四、建议的解决方向定位具体进程用top -H -p $(pgrep postgres)看是哪个 PostgreSQL 后端进程占 CPU再对应到具体 SQL优化 SQL加索引、改写查询、分区大表调整配置shared_buffers、work_mem、effective_cache_size、maintenance_work_mem控制连接使用 PgBouncer 连接池治理表膨胀手动 VACUUM FULL注意锁表或调整 autovacuum 参数升级硬件如确实是计算密集型查询考虑垂直扩容
