“这条SQL救过我好几次命。”很多PG运维老兵都说过类似的话。做PostgreSQL DBA日常工作说白了就是跟连接、慢查询、锁、膨胀、索引较劲。这套“PostgreSQL DBA最常用SQL”不是什么高深理论而是我从一线故障排查、日常巡检、性能调优里一点点攒出来的实战清单覆盖了从连接信息、实例状态、数据库对象、索引运维、性能定位到VACUUM膨胀监控的全部高频场景适合刚接手PG运维的同学也适合从MySQL转过来想快速上手的兄弟更欢迎老DBA拿去做对照补充。我选SQL有个硬标准能一条查清楚绝不写三行子查询能直接解释现象绝不拐弯抹角。下面这些SQL全部在PostgreSQL 12到16系列上实测过没有版本强绑定绝大部分9.6以后都能用个别涉及新特性的地方我会单独标注。建议你按章节收藏日常巡检和故障救火时直接抄作业。1. 为什么每个PostgreSQL DBA都需要一张SQL速查清单1.1 DBA日常工作的真实场景PostgreSQL DBA的一天很少是岁月静好的。最典型的几个场景早上到公司业务方说“系统卡死了”你一看pg_stat_activity里几十个active查询堆积在那里或者半夜告警“连接数超限”应用连不上库你第一个想到的是查max_connections够不够、有没有会话没有被回收再或者磁盘告警数据库目录涨了好几个G你把表按大小排个序结果发现某个日志表的膨胀率高得离谱。这些场景有个共同点你手上必须有一批“命中即用”的SQL能在30秒内回答“现在库处于什么状态”“谁是罪魁祸首”“我下一步该做什么”。等到现场去查文档、翻系统视图字段含义黄花菜都凉了。有人觉得DBA核心能力是调参、是架构、是备份恢复这些当然重要但所有上层动作的起点都是先用SQL把现状看清楚。巡检脚本也好、监控面板也好本质上就是把下面这些SQL封装成自动化。所以这张清单与其说是“命令大全”不如说是PG运维的“第一现场探针”。1.2 清单选录的三个标准写这篇文章之前我翻了一遍自己这些年在生产环境实际执行过的SQL淘汰掉那些“理论正确但实战很少用”的留下的都满足三个标准。第一高频。要么是每周巡检必跑的要么是故障时第一反应就该查的。像SELECT * FROM pg_stat_activity这种你只要管PG几乎每天都在看。第二低学习成本。能用一条SQL解决就不要拼接复杂嵌套。但注意这不是说不写长SQL而是说每条SQL解决一个明确问题结果集一眼能看懂。比如排查锁等待一条带两个JOIN的查询就够别整出存储过程。第三高信息密度。最好一次查询能把“状态、时间、关联对象、等待事件”一次带出来省得查完一条再查下一条。以pg_stat_activity为例最好直接用SELECT pid, usename, state, wait_event_type, wait_event, query_start, query把关键字段全列出少一次交互就少一分焦虑。1.3 这些SQL背后的核心系统视图PostgreSQL的灵魂在于系统视图清单里的SQL几乎全是在读它们。pg_stat_activity进程/会话状态所有连接和查询的第一现场。pg_stat_database库级统计包括连接数、事务数、块读写、冲突、死锁。pg_stat_user_tables/pg_stat_all_tables表级统计活着和死亡元组数、扫描次数、vacuum时间。pg_stat_user_indexes/pg_stat_all_indexes索引使用情况扫描次数、读取元组数。pg_stat_statements需要安装扩展提供SQL维度的资源消耗聚合慢查询定位的头号帮手。pg_locks锁信息配合pg_stat_activity定位阻塞链路。pg_class、pg_namespace、pg_attribute目录表看表结构、算大小、查重复索引都绕不开。知道每个视图是干嘛的你才能真正理解那些SQL为什么这么写而不是死记硬背。2. 连接与实例信息先搞清楚“我连的是谁”2.1 查看当前连接与会话状态接手一台PG服务器第一件事永远是看连接。我最常用的就是这条SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event, query_start, xact_start, left(query, 200) AS query FROM pg_stat_activity;字段不必全记关键是这几个pid会话进程ID后面pg_cancel_backend、pg_terminate_backend要用。state会话状态。active表示正在执行查询idle表示空闲idle in transaction表示事务内空闲这种最容易卡锁idle in transaction (aborted)表示事务内出错必须回滚否则连接废了。wait_event_type和wait_event等待事件。Lock说明在等锁IO说明在读盘Client说明等客户端发消息Activity可能是空闲或后台自动任务。我踩过最深的坑是idle in transaction堆积。应用代码里开了事务忘记提交或回滚连接就一直挂着事务状态开始毫不起眼但积累多了会把max_connections直接打满还会阻止VACUUM清理死元组导致表无限膨胀。所以只要发现应用报“too many open connections”第一反应就是查有没有大量idle in transaction。想快速看连接数分布用分组查询SELECT usename, application_name, state, count(*) FROM pg_stat_activity GROUP BY usename, application_name, state ORDER BY count(*) DESC;这条能一眼看出是哪个应用、哪个用户、哪种状态占用了大量连接。我每次排障都先跑它再决定要不要清会话。2.2 实例版本与运行参数连上库之后确认版本和关键配置是例行公事。SELECT version(); SHOW server_version; SHOW config_file; SHOW hba_file; SHOW data_directory; SHOW max_connections; SHOW shared_buffers; SHOW work_mem;建议把config_file、hba_file、data_directory一条查出来尤其是半夜接手别人环境时得先知道配置文件在哪、数据目录在哪才能从容处理。show和select current_setting()是等价的但show更简洁。这里有个容易忽略的细节很多参数是postgresql.conf里的但改了不生效。查当前生效值要看视图pg_settingsSELECT name, setting, unit, context, source FROM pg_settings WHERE name IN (max_connections, shared_buffers, work_mem, maintenance_work_mem);context列会告诉你这个参数是postmaster要重启、sighupreload生效还是superuser会话级可改。如果改了配置没生效先看context别急着重启。比如shared_buffers改完必须重启实例而log_min_duration_statement只需要SELECT pg_reload_conf();。2.3 快速评估当前负载连接数多不代表负载高关键是看active会话在干什么。我用这条看压力SELECT count(*) FILTER (WHERE state active) AS active_cnt, count(*) FILTER (WHERE state idle) AS idle_cnt, count(*) FILTER (WHERE state idle in transaction) AS idle_in_xact_cnt, count(*) FILTER (WHERE state idle in transaction (aborted)) AS idle_in_xact_aborted_cnt FROM pg_stat_activity;如果active_cnt长期接近甚至超过CPU核数说明有并发压力如果idle_in_xact_cnt高优先查锁和事务如果全是idle可能是连接池配置过大或者应用没释放连接。再配合看最长查询SELECT pid, usename, state, now() - query_start AS duration, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state active ORDER BY duration DESC LIMIT 10;这条直接按“已经跑了多久”排序把最危险的长查询暴露出来。配合EXPLAIN ANALYZE去看这些查询的执行计划就能往下定位索引和统计信息的问题。3. 数据库与对象元数据看清家底3.1 数据库、Schema、表清单一次摸清psql里有\l、\dn、\dt这些快捷命令但自动化脚本、远程排查时SQL更通用。先把所有库和相关大小列出来SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_database ORDER BY pg_database_size(datname) DESC;xact_commit和xact_rollback能看出事务提交/回滚比例如果回滚比例异常高大概率应用层有大量异常事务。当前库下的表清单我习惯用这条SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || . || tablename)) AS total_size FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY pg_total_relation_size(schemaname || . || tablename) DESC LIMIT 30;注意pg_tables只是“看有哪些表”的轻量视图算大小用的是pg_total_relation_size()函数。这个函数包含表数据、索引、TOAST、物化视图等关联对象的总大小比pg_relation_size()只看主表数据要全面得多。不少新人只算主表漏了索引和TOAST结果磁盘告警查了好大一圈才发现是索引膨胀。3.2 表结构和字段定位生产环境里经常遇到“这个字段在哪个表里”的问题尤其几百张表的业务库。用这条在information_schema里搜字段名SELECT table_schema, table_name, column_name, data_type, character_maximum_length FROM information_schema.columns WHERE column_name ILIKE %order_id% AND table_schema NOT IN (pg_catalog, information_schema) ORDER BY table_schema, table_name;想快速看某张表的完整结构可以直接查pg_attribute比\d 表名更适合脚本读取SELECT attname AS column_name, format_type(atttypid, atttypmod) AS data_type, attnotnull AS not_null, attnum AS ordinal FROM pg_attribute WHERE attrelid public.users::regclass AND attnum 0 ORDER BY attnum;用public.users::regclass这种写法能把表名自动转换为OID是PostgreSQL特有的小技巧省得再去pg_class里查。3.3 找出“异常膨胀”的对象这里先埋个伏笔后面第6节会详细展开膨胀原理。但日常巡检我先用一条简单SQL快速筛出异常表SELECT schemaname, relname, n_live_tup, n_dead_tup, round(n_dead_tup * 100.0 / GREATEST(n_live_tup, 1), 2) AS dead_pct, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE n_live_tup 10000 AND n_dead_tup 1000 ORDER BY dead_pct DESC LIMIT 20;n_dead_tup是死亡元组数n_live_tup是存活元组数。如果死亡比例超过20%甚至50%说明VACUUM跟不上或者有长事务卡住了清理。last_autovacuum如果很久没有更新也要重点排查。4. 索引运维新增、验证、清理4.1 看一个表有哪些索引以及用没用上索引问题是PG运维里的重灾区。先看表上有哪些索引SELECT indexname, indexdef FROM pg_indexes WHERE schemaname public AND tablename orders ORDER BY indexname;只看“有没有索引”不够关键是“索引到底被用上没”。用pg_stat_user_indexesSELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 30;idx_scan是从这个索引开始扫描的次数idx_tup_read是扫描中读取的索引项数量idx_tup_fetch是回表取行的数量。如果一个索引长期idx_scan 0基本可以判断它是个“僵尸索引”要么没被查询用到要么根本没意义。但注意刚建好还没被统计的索引也会是0不能一看到0就无脑删要和业务确认。4.2 创建索引的几个关键细节创建索引看起来就一句CREATE INDEX但生产环境里推荐使用CONCURRENTLYCREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);普通CREATE INDEX会持有一把ShareLock虽然允许并发读但会阻塞写操作INSERT/UPDATE/DELETE。对大表来说这个锁可能持续几十秒甚至几分钟业务直接报错。CONCURRENTLY方式不会锁表代价是不能在事务里执行且耗时更久。重建索引同理少用REINDEX用REINDEX INDEX CONCURRENTLY idx_xxx;能避免重建期间对表的长时间锁。我在生产环境踩过坑直接REINDEX一张千万级的大表结果业务写操作被阻塞近一分钟号主直接在群里开喷。从那以后我的重建索引操作规范就一句话“大表一律CONCURRENTLY非特殊情况绝不裸REINDEX。”另外两个低频但极有用的索引变体部分索引Partial IndexCREATE INDEX idx_orders_paid ON orders (created_at) WHERE status paid;如果业务查询基本都是WHERE statuspaid这种索引体积小很多效率更高。包含列索引INCLUDECREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (total_amount);让索引直接覆盖查询需要的列减少回表。中年DBA的劝告别只看顺序字段建索引多根据实际WHERE和ORDER BY组合来设计。索引不是越多越好是“用得上的才留”。4.3 找出重复索引和未使用索引PG不会主动提醒你“这个索引是重复的”。排查重复索引的核心逻辑是根据索引的底层列组合去重。可以用这个经典SQL按索引列分组SELECT schemaname, relname, string_agg(attname, , ORDER BY attnum) AS index_columns, count(*) AS duplicate_count, indexrelname FROM pg_stat_user_indexes GROUP BY schemaname, relname HAVING count(*) 1;这只是粗糙版本更精确的做法是把索引定义归一化后分组。实际工作中我发现最容易出现的重复场景是先建了一个单列索引idx_a后来业务变化又建了(a, b)复合索引单列索引就成了冗余。未使用索引排查我限定条件避免误杀SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE idx_scan 0 AND indexrelname NOT LIKE %_pkey AND indexrelname NOT LIKE %_key ORDER BY pg_relation_size(indexrelid) DESC;排除主键和唯一约束对应的索引因为它们承担了约束功能不能因为扫描少就删除。剩下的若有明显体积大且扫描为零的索引和业务确认后可以DROP。我处理过一次600GB库清理掉三个重复索引直接回收了近20GB空间。5. 性能排查从“现象”到“SQL”的实战SQL5.1 慢查询定位先开日志再统计优化慢查询第一步是知道慢在哪。PG慢日志开关很简单ALTER SYSTEM SET log_min_duration_statement 1000; ALTER SYSTEM SET log_autovacuum_min_duration 1000; SELECT pg_reload_conf();但这只解决“接下来”的慢查询记录。要想看历史聚合必须用pg_stat_statements扩展CREATE EXTENSION IF NOT EXISTS pg_stat_statements;注意这个扩展需要提前加到shared_preload_libraries里并重启实例ALTER SYSTEM SET shared_preload_libraries pg_stat_statements;重启后就可以查询最耗时的SQL了SELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms, round((total_exec_time / 1000)::numeric, 2) AS total_sec, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;在PG 13以前字段叫total_timePG 13开始拆成total_exec_time和total_plan_time两个版本我都写出兼容写法使用时注意版本差异。拿到慢SQL之后的常规动作是EXPLAIN (ANALYZE, BUFFERS)看执行计划观察有没有全表扫描、有没有错误使用索引、有没有Sort消耗巨大。5.2 活动会话与锁等待用一条SQL揪出“元凶”锁等待是PG运维里最让人头大的问题之一。PG 9.6以后提供了pg_blocking_pids(pid)函数可以直接返回“这个PID被哪些PID阻塞”排查效率高了一个量级。我最常用的锁排查SQL长这样SELECT blocked.pid AS blocked_pid, blocked.usename AS blocked_user, blocked.application_name AS blocked_app, blocked.query_start AS blocked_start, blocking.pid AS blocking_pid, blocking.usename AS blocking_user, blocking.application_name AS blocking_app, blocking.state AS blocking_state, blocking.query_start AS blocking_start, current_query FROM pg_stat_activity blocked JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS blocking_pid ON true JOIN pg_stat_activity blocking ON blocking.pid blocking_pid WHERE blocked.wait_event_type Lock AND blocked.state active;注意PG 14之前取进程当前SQL用的字段叫current_queryPG 14开始统一为query实际使用按版本替换。这条SQL把“谁在等待、等谁、等多久”一把梭查出来。执行之后优先看blocking_state如果阻塞源是idle in transaction说明有人开了事务没提交代码问题可以联系会话方处理如果阻塞源是active说明对方在跑一个长查询要考虑是不是等锁的资源能容忍这个时间。确定要清理时先用温和的取消再考虑终止SELECT pg_cancel_backend(pid); SELECT pg_terminate_backend(pid);pg_cancel_backend发信号取消当前查询类似按CtrlC只对active查询有效如果取消不掉或者会话是idle in transaction状态只能用pg_terminate_backend强行断开连接。我处理极端情况时会先cancel等3秒没反应再terminate。直接terminate虽然干脆但会丢事务应用端要有自动重连重试机制否则容易雪崩。5.3 缓存命中率与IO判断PG性能最大瓶颈通常是磁盘IO。先看库级块命中率SELECT datname, blks_read, blks_hit, round((blks_hit * 100.0 / NULLIF(blks_hit blks_read, 0)), 2) AS hit_ratio FROM pg_stat_database WHERE datname NOT IN (template0, template1) ORDER BY hit_ratio ASC;blks_hit表示共享缓冲区直接命中的块数blks_read表示要从磁盘读的块数。正常情况下命中率应当超过99%。如果某个库命中率偏低优先排查两条线一是shared_buffers设置是不是太小二是应用有没有大量未走索引的全表扫描。再结合表级缓存情况定位“读盘大户”SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 20;seq_scan是全表扫描次数。如果一个大表seq_scan狂涨且seq_tup_read巨大基本可以断定有查询在“裸扫”。这不是说全表扫描一定不好小表全表扫描反而更高效但大表全扫代价就高了要优先确认是不是少建了索引。6. 膨胀监控与VACUUM这是PG独有的必修课6.1 为什么PG表会“越用越胖”PG的多版本并发控制MVCC机制决定了每次UPDATE不会改原行而是写一个新版本元组旧版本保留给正在读取的旧事务DELETE也不会立刻物理删除数据而是标记为死亡元组。这些死亡元组不清理的话表文件会越来越大扫描成本越来越高就是所谓的“膨胀”。清理死亡元组的动作叫VACUUM。PG的autovacuum默认开着但恶劣情况下会跟不上比如高频UPDATE/DELETE、超长事务、表非常大导致autovacuum跑不完。所以DBA必须自己掌握查膨胀的SQL。死亡元组比例监控是这个主题里的核心SQLSELECT schemaname, relname, n_live_tup, n_dead_tup, round(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 2) AS dead_pct, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_user_tables WHERE n_live_tup 50000 AND n_dead_tup 2000 ORDER BY dead_pct DESC LIMIT 30;我的经验阈值是这样dead_pct超过20%要留意超过50%就要主动人工介入。如果last_autovacuum是NULL或者日期非常老说明自动清理根本没跑过优先检查autovacuum是否被关闭或者表级autovacuum_enabled是不是被设为false。还有一个隐藏杀招pg_stat_progress_vacuum视图PG 9.6加入能看到正在跑的VACUUM进度SELECT pid, relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed, index_vacuum_count, max_dead_tuples, num_dead_tuples FROM pg_stat_progress_vacuum;这是判断“autovacuum卡住还是正常推进”的关键依据。如果num_dead_tuples一直不降而max_dead_tuples已经到达上限说明autovacuum在反复循环可能是列表内存不够或者并发太慢可以考虑人工VACUUM或调大autovacuum_work_mem、autovacuum_max_workers。6.2 手工VACUUM与ANALYZE的正确姿势现场救火时手工执行VACUUM (VERBOSE, ANALYZE) public.orders;VERBOSE会输出每张表的处理细节ANALYZE会同时刷新统计信息。注意对大表手工VACUUM尽量错峰执行虽然VACUUM不阻塞读写但会消耗IO和CPU极端膨胀时可能影响业务。如果膨胀已经严重到普通VACUUM都无能为力——普通VACUUM只能回收“页尾”的空间页中碎片往往无法合并——就得重建表或索引。最有效的手段是-- 重建表 ALTER TABLE public.orders SET (autovacuum_enabled false); CREATE TABLE public.orders_new (LIKE public.orders INCLUDING ALL); INSERT INTO public.orders_new SELECT * FROM public.orders; DROP TABLE public.orders; ALTER TABLE public.orders_new RENAME TO public.orders; ALTER TABLE public.orders SET (autovacuum_enabled true);这种操作风险极高必须在维护窗口做而且插入期间要暂停业务并确认外键、权限、序列、触发器等一并处理。低危场景下更推荐用pg_repack扩展在线重建它能用触发器在后台把数据挪到新表不用停机。但它对PG版本兼容性和使用条件有要求我建议新手先别在生产环境直接试先在测试库验证一遍。索引膨胀则用REINDEX INDEX CONCURRENTLY前面已经提过。6.3 autovacuum关键参数调整心得核心参数其实就几个autovacuum_max_workers并行worker数默认3。autovacuum_vacuum_threshold触发阈值默认50。autovacuum_vacuum_scale_factor按表大小比例默认0.2。autovacuum_work_mem每个worker可用内存默认-1取maintenance_work_mem。autovacuum_naptime检查间隔默认60s。触发条件的算法是死亡元组数 threshold scale_factor * reltuples。比如表100万行默认就是超过50 0.2 * 1000000 200050才触发。对小表没问题对大表0.2的比例就显得太迟钝很多人把autovacuum_vacuum_scale_factor调成0.05甚至0.01同时调大autovacuum_work_mem。但没有万能参数。我见过有人把scale_factor调得极小导致autovacuum一天到晚在跑磁盘IO持续高位。正确做法是按表设置ALTER TABLE public.orders SET (autovacuum_vacuum_scale_factor 0.05, autovacuum_vacuum_threshold 1000);把“大表高频变更”和“小表任意变更”分开管理比全局一刀切合理得多。7. 日常维护分析、重载、变更追踪7.1 统计信息过期的锅怎么查怎么补PG的查询优化器依赖表和索引的统计信息。如果统计信息严重过期优化器会选错执行计划产生“平时好好的SQL突然变慢”的灵异现象。这时候ANALYZE是最快的解药。查哪些表长时间没被分析SELECT schemaname, relname, n_tup_ins, n_tup_upd, n_tup_del, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE last_analyze IS NULL OR last_autoanalyze IS NULL ORDER BY relname;如果有大量变更但last_autoanalyze久远就要手动补一口ANALYZE VERBOSE public.orders;生产经验大表统计信息更新最好也错峰执行ANALYZE会扫描表样本同样占用IO。如果遇到“DROP COLUMN后统计信息不更新”的情况全新版本的ANALYZE能解决。另外注意PG 12以后支持ALTER TABLE ... SET STATISTICS控制采样行数对极端分布的数据可以调高ALTER TABLE public.orders ALTER COLUMN customer_id SET STATISTICS 2000;7.2 救火操作取消与终止会话的时机前面讲过pg_cancel_backend和pg_terminate_backend这里补充批量清理场景。如果一堆会话卡死需要批量终止可以拼出“杀进程SQL”SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE application_name batch_worker AND state idle in transaction;这条会直接终止所有匹配条件的会话。务必在测试环境先确认过滤条件能匹配到预期进程避免误杀。别忘了排除自己SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid pg_backend_pid();但这条会把所有业务会话都断开影响面巨大除非是准备停库维护否则绝对不会执行。个人救火顺序是先cancel长查询保留会话再等几秒观察是否恢复不行才terminate。terminate之后应用连接池会有感知触发重连通常能恢复但也会有一波连接风暴。所以终止前最好看一眼连接池配置确认能撑住瞬时重连。7.3 变更前后的一层保护事务与回滚DBA手滑删数据是事故高发场景。PG有个好习惯所有结构性变更和数据变更都可以包在事务里BEGIN; ALTER TABLE public.orders ADD COLUMN remark text; -- 检查发现不对回滚 ROLLBACK;事务包着DDLPG是支持的而且回滚干净利落。我在生产环境改表结构时几乎都是事务化操作改完先SELECT验证一片确认无误再COMMIT。但要小心CREATE INDEX CONCURRENTLY和REINDEX CONCURRENTLY不能在事务里执行这两个例外要单独提交。另外有个小技巧变更前先记录当前状态SELECT schemaname, relname, n_live_tup, n_dead_tup, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables WHERE relname orders;变更后再跑一次对比差值能快速确认变更是否产生了大量死元组或异常大小增长及时安排后续VACUUM。8. 常见问题排查清单DBA速查表8.1 六种高频故障的定位路径记录一下我自己处理过的大量故障整理成一张“现象 - 核心SQL - 处理方向”的速查表。这张表管用的原因在于大部分PG故障都可以归到这六类里面。现象核心SQL处理方向连接数打满SELECT count(*), state FROM pg_stat_activity GROUP BY state;清idle in transaction、调大max_connections、检查连接池配置磁盘涨得异常查表大小排序SQL SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;定位膨胀表和日志表VACUUM或归档清理慢查询突然增多SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;EXPLAIN AN ALYZE补索引 / 修正统计信息系统卡住、CPU打满活动会话按时长排序SQL pg_stat_progress_vacuum找长查询和语句取消或终止排查autovacuum应用报“锁等待超时”锁等待JOIN查询 pg_blocking_pids(pid)找阻塞源取消或终止阻塞会话修应用事务VACUUM跑不完SELECT * FROM pg_stat_progress_vacuum; 查长事务SQL排查长事务、调autovacuum_work_mem、维护窗口人工VACUUM排查长事务的SQL也补充一下SELECT pid, usename, application_name, xact_start, now() - xact_start AS xact_age, state FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start ASC LIMIT 20;这里必须强调很多看似“VACUUM跑不动”的问题根源其实是长事务。长事务持有最老快照导致旧版本元组不能被清理表膨胀持续加剧。遇到这种情况先处理业务长事务再谈VACUUM参数。8.2 几条“红线”操作千万别碰做DBA久了最怕的不是不会写SQL而是手痒乱执行。下面这些操作我全部见过引发事故列出来当你我的共同禁区。不要在生产库直接执行DROP INDEX或DROP TABLE必须先用事务包好并在测试环境跑一遍DDL脚本。虽然PG的DDL可以放进事务但一旦COMMIT就真没了除非有备份别赌。不要在高峰期对核心大表执行裸VACUUM FULL。VACUUM FULL会重写表并获取ACCESS EXCLUSIVE锁等于让这张表在窗口期内完全不可读写业务直接中断。要清膨胀就用pg_repack或维护窗口。不要轻易ALTER SYSTEM改全局参数再reload尤其是改shared_buffers、max_connections这种重启生效的参数。改完要立即确认pg_settings里的context并且观察一段时间再下结论。不要直接编辑postgresql.conf而不做备份每次修改前先cp postgresql.conf postgresql.conf.bak。不要无脑删“未使用索引”。前面说过主键和唯一约束的索引不能删刚建的索引统计还没起来统计信息过期导致优化器不用索引也可能表现为idx_scan0。删之前先ANALYZE一下再结合业务确认。8.3 巡检脚本的积木把常用SQL组合成习惯这些SQL单看每个都简单组合起来就是一套完整的巡检流程。我自己的巡检节奏是半小时一次核心就是几条SQL轮着跑先看活动会话和连接分布再看慢查询TOP然后看锁等待和长事务最后看表大小排名和死元组比例。全部跑完不超过两分钟。如果你想要更省事可以把他们封装成一个视图或者函数。比如把最常用的“活跃会话等待事件耗时”做成视图CREATE OR REPLACE VIEW v_active_queries AS SELECT pid, usename, application_name, state, wait_event_type, wait_event, now() - query_start AS query_age, left(query, 200) AS query_snippet FROM pg_stat_activity WHERE state active ORDER BY query_age DESC;再比如把“锁等待链路”做成函数方便直接调用。这样做的好处是紧急情况下手一抖也不会写错。我个人建议至少把v_active_queries和v_blocking_chains固化下来。结尾工具箱之外说点实在的最后分享一个我自己的习惯所有生产操作都留“痕迹”。执行前把SQL复制到操作记录里执行后再把返回的关键数值连接数、死元组比例、锁等待PID截图或记录。这样做不是为了应付审计而是事后复盘时能准确知道“改前是什么状态、改后变化了多少、下一次阈值该定多少”。没有这些基线数据所谓调优就是盲人摸象。另外刚接触PG的朋友别贪多。我建议先把第2节、第5节和第8节的SQL练熟这三块覆盖了80%的故障现场。剩下的索引设计和VACUUM调参等你对系统视图越来越有感觉之后再慢慢深入。工具是死的排查思路是活的。每一条SQL背后都是对PG运行机制的一次理解积累多了你也能像我一样面对“数据库又卡了”的时候心里第一时间就跳出三条候选SQL。