企业官网建设流程全解析

线上最刺激的事情，往往不是新功能上线，而是某个风和日丽的下午，系统突然开始卡顿。CPU占用飙到百分之九十多，业务接口普遍延迟三到五秒，监控群里一片哀嚎：数据库是不是出问题了？这时候如果你能在一分钟之内指出是哪条SQL、哪个会话、哪把锁在作怪，整个团队悬着的心就能落回嗓子眼。

这篇文章要聊的就是PostgreSQL环境下做这件事的核心手段：怎么快速找出慢查询、长运行查询和阻塞查询。适合被线上问题逼到墙角的后端开发，也适合刚接手PostgreSQL运维、还没摸清排查门道的DBA。我会把实际排查链路和常用SQL直接给出来，配合原理说明，让不熟悉PostgreSQL内部机制的人也能照着操作。文章里涉及的工具全部是PostgreSQL自带的，不需要额外安装软件，生产环境可以直接用。

1. 慢查询诊断的第一块拼图：pg_stat_statements的配置与解析

1.1 为什么慢查询日志不够用

很多刚接触PostgreSQL的人第一反应是打开慢查询日志。log_min_duration_statement这个参数确实能把执行超过阈值的SQL打到日志里，但它解决不了我遇到的大部分问题：它是一个事后的、被动的记录器。查询已经跑完了你才知道它慢，线上业务卡顿的当下，日志里可能什么都没有——因为最慢的那个查询还没跑完呢。更麻烦的是慢查询日志不开的话，历史慢SQL数据就是空白；开了之后如果阈值设定不合理，日志文件一夜之间能膨胀到好几个GB，把自己宝贵的排查窗口给淹没掉。

所以我的习惯是：慢查询日志可以开，但真正的战略武器是pg_stat_statements扩展。它是一个随数据库实例运行的统计模块，能够跟踪所有SQL语句的执行计划、调用次数、总耗时、平均耗时、返回行数等关键指标。它的核心优势在于：数据实时存在共享内存里，随时可以查，还能按总耗时、平均耗时排序，快速找出哪些SQL是真正值得优化的"大块头"。

1.2 开启与重置统计

要让pg_stat_statements生效，得修改配置文件（通常是postgresql.conf），然后重启数据库实例。具体的配置项如下：

# postgresql.conf shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all

• shared_preload_libraries = 'pg_stat_statements'：必须在数据库实例启动时把模块预加载到共享内存里。这里有个坑，如果你只执行CREATE EXTENSION而不修改这个参数，扩展虽然装了，但统计功能不会真正开启。
• pg_stat_statements.max = 10000：最多跟踪多少条不同的SQL模板。超出之后，新的SQL模板无法进入统计，旧数据可能不会被淘汰，你会看到计数器滚得很慢。生产环境我一般建议设置5000到10000，够用且不浪费内存。
• pg_stat_statements.track = all：跟踪所有SQL，包括存储过程中的语句。默认值是top，只跟踪顶层SQL，如果是all则连函数内部的SQL也记录。

修改配置之后重启实例，然后执行：

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

这里要特别提醒：通过CREATE EXTENSION创建扩展只代表目录对象的建立，真正的数据捕获依赖开机时的共享库加载。两者缺一不可。

如果你改了代码、优化完一批SQL之后想重新统计，可以执行：

SELECT pg_stat_statements_reset();

这条命令会把累计数据清零，方便你对比优化前后效果。我在性能调优的时候习惯先重置再压测，这样出来的数据完全是干净的，不用心算排除历史干扰。

1.3 核心字段解读与两个容易踩的坑

下面这条查询基本是我固定在导航栏里的，日常慢SQL盘点就靠它：

SELECT calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms, round(max_exec_time::numeric, 2) AS max_ms, rows, left(query, 80) AS query_preview FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

字段含义很直白：calls是调用次数，total_exec_time是累计总耗时（毫秒），mean_exec_time是平均耗时，max_exec_time是单次最大耗时，rows是累计返回的行数。我判断一个SQL要不要优化，主要看两个指标：总耗时占比高说明它消耗了系统大量时间，平均耗时长说明单次执行本身就慢。有一种情况很迷惑人，总耗时不低但平均耗时很低——这意味着SQL被调用了几十万次，本身不算慢，但架不住次数多。这种优化思路就不是改SQL，而是改成批量处理或者加缓存，从减少调用次数入手。

第一个坑：字段名兼容性。PostgreSQL 13及以后版本，累计时间字段是total_exec_time、mean_exec_time、max_exec_time；但12及更早版本，字段名是total_time、mean_time、max_time。如果你在旧版本上查total_exec_time，会直接报字段不存在。写脚本的时候一定要判断版本，别换了个环境就翻车。

第二个坑：queryid不是稳定不变的。pg_stat_statements默认通过哈希算法给每种SQL模板生成一个queryid，但它可以被环境因素影响，比如search_path的变化、参数绑定的类型差异，甚至PostgreSQL小版本升级。这意味着你监控告警里记录的queryid可能在某次升级后全部对不上号，导致历史对比失效。真要长期监控，建议通过SQL文本特征去匹配，而不是死磕queryid。

2. 正在发生的"现在时"问题：pg_stat_activity的实时战场

2.1 从连接列表里捞出真正有问题的会话

如果说pg_stat_statements解决的是"过去哪条SQL最恶劣"，那么pg_stat_activity解决的是"现在系统为什么卡"。它是PostgreSQL暴露系统活动会话的窗口，每一行代表一个后端进程。线上卡顿发生的时候，第一件事就是查这个视图。

我常用的"战场侦察"SQL长这样：

SELECT pid, usename, state, wait_event_type, wait_event, now() - xact_start AS xact_age, now() - query_start AS query_age, left(query, 120) AS current_query FROM pg_stat_activity WHERE state IS NOT NULL ORDER BY query_start ASC;

关键字段逐个说：

• pid：后端进程ID，也是后面执行pg_cancel_backend(pid)、pg_terminate_backend(pid)时需要的参数。
• state：会话当前状态。active表示正在执行查询；idle表示空闲，连接活着但没在跑任务；idle in transaction表示事务已开启但处于空闲状态，比如Java代码里开了事务忘了提交；fastpath function call表示正在执行fastpath函数调用；disabled表示该会话的统计跟踪被禁用。
• wait_event_type和wait_event：表示会话正在等待什么。这是判断瓶颈的关键，wait_event_type分为Lock、Activity、BufferPin、Client、Extension、IO、IPC、Timeout、LWLock等大类。
• xact_start：当前事务开始时间，now() - xact_start就是事务已经跑了多久。
• query_start：当前查询开始时间，now() - query_start就是查询已经跑了多久。

实际使用中，我习惯按query_start升序排列，最早的查询排在最上面。如果看到某个查询的query_age已经超过了业务正常水平，基本就是它拖慢了系统。

2.2 基于wait_event的等待事件初判

wait_event_type是一个很值得展开的字段。很多人只知道查pg_stat_activity看state，却忽略wait_event，等于只看了一半信息。

举个例子，同样是state = active的两个会话，一个wait_event_type = Client，wait_event = ClientRead，意思是它在等应用端发指令过来，其实没在干活；另一个wait_event_type = IO，wait_event = DataFileRead，意思是它在等磁盘把数据页读上来，这是真正的I/O瓶颈。这两者处理方式完全相反：前者是应用层逻辑问题，后者要查磁盘性能、索引命中率或是否发生了大范围全表扫描。

wait_event_type = Lock时，表示会话在等一把锁。这种等待的瓶颈不在CPU也不在磁盘，而在另一个持锁的会话。这时候就得去分析锁阻塞了，也就是下一章节的内容。

想快速判断哪种等待占主导，可以配合系统视图做聚合统计：

SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state = 'active' GROUP BY wait_event_type, wait_event ORDER BY count(*) DESC;

如果聚合结果里LWLock类占比极高，比如WALWriteLock、buffer_mapping，那多半和并发写压力或checkpoint频繁触发有关。如果IO类占比高，大概率存在慢盘或大量随机读写。

2.3 idle in transaction：比慢查询更隐蔽的定时炸弹

慢查询肉眼可见，不算最可怕；真正让PostgreSQL社区老手都头皮发麻的，是idle in transaction（事务中空闲）。

这种状态发生在应用开启了事务、执行了一部分SQL、然后既不提交也不回滚，连接就这么吊着。表面上看这个会话什么都没做，似乎人畜无害；但它持有的事务快照会阻碍其他会话的vacuum清理旧数据，导致表膨胀。更严重的是，如果一个idle in transaction会话持有某把锁，其他所有需要这把锁的查询都会被堵住，系统表现就是——莫名其妙地越来越慢，查pg_stat_activity又看不到任何"正在执行的慢查询"。

排查命令很简单：

SELECT pid, usename, state, now() - xact_start AS idle_tx_age, left(query, 120) AS last_query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start ASC;

只要发现idle_tx_age超过业务容忍阈值（我一般设定为30秒到60秒），基本就是代码里事务没正确关闭。这时候可以找开发同学拉出对应的代码路径，把事务边界改对。紧急情况下可以pg_terminate_backend(pid)强制断开，让持锁的事务回滚，但这是止血不是治病。

PostgreSQL也提供了预防参数，后面第4章会详细讲。这里先记结论：idle_in_transaction_session_timeout这个参数一定得设，它能在事务空闲超过指定秒数后自动断开会话，比人肉运维靠谱得多。

3. 阻塞链路的分层定位：pg_locks锁视图与阻塞树分析

3.1 锁的基本逻辑与granted/queued

PostgreSQL的锁机制核心思路是"先到先得"，但锁类型之间存在兼容性矩阵。普通读写不冲突，但ACCESS EXCLUSIVE锁（比如ALTER TABLE、TRUNCATE、VACUUM FULL）几乎和所有锁互斥。当一个会话持有某把锁，另一个会话请求不兼容的锁时，后者就会进入等待状态，这个等待就体现在pg_stat_activity的wait_event_type = Lock里。

pg_locks视图实时显示当前数据库中的锁信息。核心字段包括：

• locktype：锁的类型，常见的有relation（表级锁）、tuple（行级锁）、transactionid（事务ID锁）、virtualxid（虚拟事务ID锁）、page（页级锁）等。
• mode：锁的模式，从弱到强有ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE。
• granted：布尔值，true表示这个锁请求已经被满足（持有锁），false表示这个锁请求还在排队等待。
• pid：持有或等待锁的后端进程ID。
• relation：如果是关系锁，这里是对应的表OID，需要通过pg_class转换才能得到表名。

排查阻塞的关键就是找"两个会话针对同一个对象，一个granted = true、另一个granted = false"的组合。granted = false的会话，就是被卡住的那个。

3.2 一条SQL找出阻塞源头

直接查pg_locks原始数据很容易看花眼，因为你既要处理大量行级锁，还要面对重复的锁请求记录。我习惯用递归CTE把它整理成阻塞树，从被阻塞的会话一路回溯到最顶上的"元凶"。

下面这条查询是我压箱底的存货，能直观输出"谁在堵谁"：

WITH RECURSIVE lock_tree AS ( SELECT pid, locktype, mode, granted, relation::regclass AS relname, transactionid AS txid, 0 AS depth, ARRAY[pid] AS path FROM pg_locks WHERE granted = false AND locktype IN ('relation', 'tuple', 'transactionid', 'virtualxid') UNION ALL SELECT l.pid, l.locktype, l.mode, l.granted, l.relation::regclass AS relname, l.transactionid AS txid, lt.depth + 1 AS depth, lt.path || l.pid AS path FROM pg_locks l JOIN lock_tree lt ON l.pid = lt.pid WHERE l.granted = true ) SELECT pid, locktype, mode, granted, COALESCE(relname::text, txid::text) AS target, depth, path FROM lock_tree ORDER BY path;

简单解释一下递归逻辑：先从所有处于等待状态的锁请求（granted = false）出发，然后顺着同一个pid往上找它已经持有的、对其他会话构成阻塞的锁（granted = true），一层层往上爬。最终你会看到一条会话链：最底层的是受害者，最顶层的是阻塞源头。拿到顶层的pid之后，再对应到pg_stat_activity去查它正在执行什么SQL、开了多久事务。

另一种更快但稍微粗糙的做法是直接关联系统函数，输出每个会话"在等谁的什么锁"：

SELECT blocked.pid AS blocked_pid, blocked_query.query AS blocked_query, blocking.pid AS blocking_pid, blocking_query.query AS blocking_query, now() - blocking_query.xact_start AS blocking_tx_age FROM pg_locks blocked JOIN pg_locks blocking ON blocking.locktype = blocked.locktype AND blocking.locktype IN ('relation', 'tuple', 'transactionid') AND blocking.database = blocked.database AND blocking.relation = blocked.relation AND blocking.transactionid = blocked.transactionid AND blocking.pid <> blocked.pid AND blocking.granted JOIN pg_stat_activity blocked_query ON blocked_query.pid = blocked.pid JOIN pg_stat_activity blocking_query ON blocking_query.pid = blocking.pid WHERE NOT blocked.granted;

这条基于自连接的查询把"阻塞者"和"被阻塞者"并列排开，适合快速向开发同学解释：你看，就是blocking_pid这个会话拿着锁不放，把blocked_pid给卡住了。

3.3 处理真实阻塞案例时的几个判断要点

锁是数据库里最微妙的东西，遇到阻塞问题不要急着kill，先花十秒钟判断一下情况。我的经验是分三类处理：

第一类，短期锁等待，比如频繁的高并发写入同一行记录。等待几十毫秒到几百毫秒正常，不构成问题，不用管。

第二类，会话持有锁但事务长时间不结束。比如开发同学手动开了一个事务，查了两条数据就放着不管了，然后其他应用更新同一张表全部卡死。这种直接联系对应负责人，让他提交或回滚事务。联系不上、业务已经挂了的情况下，才考虑用pg_terminate_backend强杀。

第三类，锁等待伴随极端长查询。比如一条全表更新跑了十分钟，所有后续写入都被它堵住。这时候不是简单kill的问题，而是要判断数据一致性：更新已经部分完成，强杀后事务回滚要耗费时间，期间锁还在；如果等它自己跑完，业务还要继续忍受阻塞。我的经验是，如果更新语句已经耗时超过预估的几倍且没有快完成的迹象，果断杀掉，回滚的成本通常比无限期等下去更可控。

还有一个日常容易忽略的场景：vacuum进程和业务查询互相阻塞。自动vacuum加的是SHARE UPDATE EXCLUSIVE锁，理论上和普通查询的ACCESS SHARE锁兼容，但如果业务里有人手动执行了VACUUM FULL或者REINDEX——那用的是ACCESS EXCLUSIVE锁，会和一切读写冲突，系统会瞬间卡死。这类锁等待往往是最突然的，排查时需要特别留意pg_stat_activity里有没有autovacuum或手动VACUUM会话，它们很容易被认为是"无害的后台进程"而被忽略。

4. 定位之后：cancel还是terminate，以及如何防止问题再次发生

4.1 pg_cancel_backend与pg_terminate_backend的正确使用场景

定位到肇事会话之后，很多人第一反应就是执行kill。PostgreSQL里有两个内置函数，用途完全不同：

• pg_cancel_backend(pid)：发送取消信号，请求该后台进程取消当前正在执行的查询命令，但保留连接。如果是一个长查询卡住了，只是想让这条SQL停下来，用这个。它不会把事务回滚，但会把当前这条语句中断，事务处于"待处理"状态，由应用决定是提交还是回滚。
• pg_terminate_backend(pid)：直接终止后端进程，等价于断开连接。已经开启的事务会立即回滚，所有持有的锁全部释放。用于事务卡死、锁无法释放、连接半死不活的场景。

实际经验告诉我一个原则：能cancel就别terminate。cancel相对温和，相当于按了Ctrl+C；terminate是拔电源，会引发应用层的连接中断异常，如果应用没有做重试机制，用户会看到断连错误。但如果会话处于idle in transaction状态，cancel对它没有效果，因为它根本没有正在执行的查询可以取消，这时候只能terminate。

执行之前最好先确认身份，看清楚是自己业务库的连接还是其他核心系统的连接。跨团队动别人的会话，一定要先告知再操作，最好留个执行记录。我见过有人因为随手杀了一个正在执行大事务的会话，导致那个业务模块直接瘫痪两小时的场景——不是技术问题，是人和人的问题。

4.2 系统级超时防护配置

一次两次靠人肉排查可以，长期靠人肉就是运维事故。定位慢查询和阻塞查询的最终目的，是通过配置让系统在问题发生时自动止血。PostgreSQL提供了一组超时参数，强烈建议逐项配置：

# postgresql.conf statement_timeout = 30s lock_timeout = 5s idle_in_transaction_session_timeout = 30s

这三个参数是PostgreSQL DBA的护身三件套，每个都有具体场景：

• statement_timeout：单条语句执行超过30秒直接报错中断。防止一条SQL无限期运行耗尽系统资源。需要评估业务里合法的长查询，比如某些月初跑批任务可能超过这个阈值，可以单独在事务级别设置SET LOCAL statement_timeout = '10min'覆盖。
• lock_timeout：等待锁超过5秒自动放弃。这是防止阻塞扩散的利器。拿锁请求通常要排队，如果排队超过阈值，数据库直接抛出canceling statement due to lock timeout错误，请求方不用傻等。
• idle_in_transaction_session_timeout：事务空闲超过30秒自动断开。对应前面说的idle in transaction场景，从根上解决事务不提交导致的膨胀和锁堆积。

这几个参数对数据库运行没有任何负面影响，只是把异常情况显式暴露出来。别担心误杀正常业务，正常的事务根本撑不到这些阈值。设完之后一定要做一轮业务侧压测，确认没有合理请求被误伤。

有一个很小的坑值得提醒：statement_timeout和lock_timeout可以分别设置，但它们都作用于当前会话。如果用的是连接池软件（比如PgBouncer），连接复用可能导致某个连接带着上一个会话的超时设置继续服务下一个会话。稳妥的办法是在连接池侧初始化语句执行SET或者用数据库角色默认设置。

4.3 从日志和监控上建立日常防护

排查工具再强，被动响应始终是被动的。现在PostgreSQL环境我一般会主动建立一套日常健康检查脚本，用最简单的SQL定期扫描"异常会话"。

我自己的巡检脚本长这样（通过crontab每5分钟跑一次，结果推到监控告警）：

SELECT 'long_query' AS alarm_type, pid, usename, now() - query_start AS dur FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '30 seconds' UNION ALL SELECT 'idle_in_transaction' AS alarm_type, pid, usename, now() - xact_start AS dur FROM pg_stat_activity WHERE state = 'idle in transaction' AND now() - xact_start > interval '30 seconds' ORDER BY dur DESC;

只要这个查询结果不为空，就说明系统里存在符合预警条件的会话。长期巡检下来，你会发现很多问题在用户感知之前就已经暴露了苗头。另外pg_stat_statements的数据也建议定期归档，比如每周导出一份Top SQL耗时排行，观察趋势变化。性能问题从来不是突然出现的，它会在数据里留下痕迹，关键是你要养成看数据的习惯。

慢查询、长运行查询、阻塞查询，这三类问题虽然在PostgreSQL里表现为不同的视图、不同的等待事件、不同的锁类型，但它们的排查思路是一致的：先通过pg_stat_statements看历史画像，再用pg_stat_activity确认现场，然后靠pg_locks追根溯源找到阻塞源头，最后用超时配置和巡检脚本让同类问题不再轻易发生。我把这套流程跑顺之后，处理线上数据库问题的平均耗时从原来的半个点缩小到了几分钟，大部分情况下甚至不用上服务器，光靠三个视图就能完成诊断。你把这套东西在自己的环境里过一遍，也能达到同样效果。排查SQL先查真实数据，我在生产环境处理完锁阻塞问题后顺手跑了一次EXPLAIN ANALYZE确认前面那批慢SQL是否走了正确的索引，这一步往往能发现不少读语句背后其实缺索引，十几分钟的事情能省下后续无数个被线上问题打断的下午。