1. 为什么要关心系统视图接手一套新环境数据库里所有重要的信息都藏在系统视图里。很多人第一次接触 PostgreSQL 系统视图都是从pg_stat_activity开始的——某个应用报连接数不够、数据库卡住了老同事让你跑一句select * from pg_stat_activity看看有没有奇怪的会话占着资源。跑完之后你会意识到原来数据库自己一直在记录“体检数据”只是平时没人去看。这些数据就放在系统视图里。PostgreSQL 把“当前有哪些连接”“每张表被读了多少次”“有没有长事务”“锁等多久了”“复制延迟多少”之类的运行状态整理成一张张只读的视图供你用 SQL 查询。它们不占额外的业务表空间不要求你装额外的插件少数几个视图除外也不需要专门的客户端工具psql 连上去就能查。系统视图解决的是一类很实际的问题你怎么快速知道数据库现在到底怎么了。做开发的同学通过系统视图可以确认自己是不是踩了锁、能不能找到慢 SQL做运维的同学通过系统视图可以做容量评估、做变更前的健康检查架构师在做技术选型或方案对比时也需要用系统视图来核实真实负载情况而不是拍脑袋。这篇文章不是把官方文档抄一遍而是按我实际使用频率来梳理哪些视图真的值得记住遇到具体问题时该查哪几张表以及我在生产环境踩过哪些坑。1.1 系统视图和系统表是什么关系PostgreSQL 把自身元数据存在pg_catalog模式下面里面既有表也有视图。表是底层存储比如pg_class记录表和索引的基本信息pg_attribute记录每个字段的定义pg_namespace记录模式信息。这些表上的数据严格来说你也可以直接select字段不少命名也偏工程师风格用起来不太友好。视图则是在这些表之上封装好的窗口比如pg_tables、pg_indexes、pg_views名字直观字段也整理过专门给人查。像pg_stat_user_tables这种“统计类视图”底层是动态统计数据里面存的是数据库运行时的计数器和采样结果并不是纯粹的元数据。所以你可以这样理解系统表是“底账”系统视图是“报表”。你日常应该优先查视图少碰系统表。在官方文档里凡是名字以pg_开头、同时relkind v的对象基本都属于系统视图范畴。一个比较常见的误解是以为系统视图必须superuser才能查实际上 PostgreSQL 默认给public开放了查询权限普通用户一样能查大部分视图只是能看到的行数会因权限有所裁剪这个后面细说。1.2 先建立一张自己的“体检SQL清单”上手最快的方式不是背视图名而是建立一份属于自己的常用查询清单。我刚接触 PostgreSQL 的时候把平时排查问题用到的 SQL 存在一个pg_daily_check.sql文件里每次接到告警先跑一遍基本能定位八成问题。我建议你也这么做。打开 psql先用下面几条语句建立手感-- 1. 看这个库的整体状态 SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database WHERE datname current_database(); -- 2. 看当前所有会话 SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event, now() - state_change AS state_duration, left(query, 120) AS query FROM pg_stat_activity WHERE state IS NOT NULL ORDER BY state_change; -- 3. 看每张表的访问情况和膨胀迹象 SELECT schemaname, relname, seq_scan, idx_scan, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;这三条是很好的起点。它们分别对应“库的健康度”“当前活动”“表和索引的热度”。之后你在实际工作中会逐渐发现很多更复杂的排查本质上都是在给这几类查询加条件、加关联、加统计函数。2. 常用系统视图怎么分类记忆PostgreSQL 的系统视图非常多如果一个个零散记忆很容易记混。我自己习惯把它们分成三类描述静态结构的、描述实时状态的、描述历史统计的。分好类之后遇到问题先判断属于哪一类再去对应的视图里翻效率高很多。2.1 描述“家底”的结构类视图这一组视图回答的问题是库里有哪些库、有哪些模式、有哪些表、有哪些索引、字段类型是什么。pg_database集群里所有数据库的基本信息包括数据库名、所有者、字符集、datfrozenxid事务冻结边界。pg_namespace所有模式schema的列表。pg_class所有表、索引、视图、序列等对象的底层记录信息很全但字段晦涩。pg_tables包装后的“用户表清单”包含表名、所属模式、是否有分区、是否带WITH选项等。pg_indexes索引清单包含索引定义语句。pg_views和pg_matviews普通视图和物化视图的清单物化视图还多一个是否已填充的字段。结构类视图最大的价值是帮你快速摸清环境。举个例子刚到一个新项目想确认某个库下面到底有哪些业务表表都建在哪个 schema 里直接执行SELECT schemaname, tablename, tableowner, tablespace FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY schemaname, tablename;想确认某张大表的字段结构除了\d你也可以直接查information_schema.columns或者pg_attribute但实际工作中\d已经足够。所以结构类视图里真正高频率用的是pg_tables、pg_indexes、pg_matviews因为它们已经把多张底层表关联好了查询成本低结果直观。顺便提醒一点information_schema里的tables、columns这类视图是为了兼容 SQL 标准而存在的结构固定跨数据库通用性好但它不含 PostgreSQL 特有的统计信息和表内部细节。所以跨库兼容场景用information_schema深入排查 PostgreSQL 自身问题时用pg_catalog下的视图两者不冲突。2.2 描述“此时此刻”的状态类视图这一组回答的问题是现在数据库里正在发生什么有没有会话在跑有没有锁在等复制有没有追上主库。pg_stat_activity当前所有后端进程的状态包含 pid、用户、数据库、客户端地址、当前执行的 SQL、等待事件、事务开始时间等。这是整个 PostgreSQL 排查体系里最重要的一张视图没有之一。pg_locks当前锁信息包含锁类型、锁模式、是否已获得授权关联的 pid 和事务 ID。配合pg_stat_activity可以还原一条完整的锁等待链。pg_stat_replication流复制中 standby 节点的状态包含发送 LSN、写入 LSN、刷盘 LSN、回放 LSN以及各候选节点的同步状态。pg_stat_wal_receiver只读节点视角下的接收状态查看接收进程的连接信息和落后程度。pg_stat_ioPG16 引入的 IO 统计视图可以按 backend 类型和 IO 上下文查看物理读、写、扩展操作的耗时和次数。状态类视图的时效性不太一样。pg_stat_activity、pg_locks是即时状态每次查询都会重新读取当前进程信息所以是实时的。pg_stat_replication也是当前值延迟几秒就能反映出来。这里有一个所有新手都会踩的坑状态类视图大多数是“实时反映当前活动”但也包含“自统计重置以来的累计值”的字段比如pg_stat_database里的xact_commit、tup_inserted这些是累计计数器不是实时速率。如果你要判断“最近一分钟是不是有大量写入”正确的做法是连续取两次快照做差值而不是直接看那个很大的累计值。2.3 描述“历史行为”的统计类视图这一组回答的问题是过去一段时间里系统的行为模式是什么样的哪些 SQL 消耗最高哪些索引根本没人用。pg_stat_user_tables每张用户表上的顺序扫描次数、索引扫描次数、插入/更新/删除行数、活元组数、死元组数、最后一次 vacuum 和 analyze 的时间。判断表膨胀、vacuum 健康度主要靠它。pg_stat_user_indexes每个索引的扫描次数、读取行数、取回行数。找无用索引、判断索引命中率靠它。pg_stat_database数据库级累计指标包括提交/回滚事务数、读写块数、临时文件大小。pg_stat_bgwriter后台写进程的统计信息包括检查点次数、缓冲区写入次数。pg_stat_statements不是默认视图需要安装pg_stat_statements扩展。它记录每条 SQL 的调用次数、总耗时、平均耗时、共享缓冲区读写、本地临时文件读取等是分析慢 SQL 最靠谱的入口之一。统计类视图一般都会有一个reset操作pg_stat_statements可以用pg_stat_statements_reset()清空其他很多统计视图也提供了对应的重置函数。不过生产环境不要轻易 reset历史累计数据对排查“是不是从某个变更之后开始变慢”非常有用一旦清零对比基线就没了。3. 四个真实场景怎么用系统视图定位问题死记视图列表没意义关键是知道什么场景查什么。下面我用四个在生产环境里反复出现的场景来说每一个都给出可以直接拷贝的 SQL 和排查思路。3.1 场景一数据库变慢连接数告警数据库慢应用超时监控告警“连接数接近上限”这时候别急着重启数据库先看pg_stat_activity。我遇到过很多次问题根本不是硬件不够而是几十个会话卡在同一个地方不动比如都在等一把锁或者都在跑一个低效的大查询。第一步先看当前有多少会话、各自处于什么状态SELECT state, count(*), min(now() - state_change) AS min_idle_since FROM pg_stat_activity WHERE state IS NOT NULL GROUP BY state ORDER BY count(*) DESC;state字段常见的值有active、idle、idle in transaction、fastpath function call、disabled。这里最值得警惕的是idle in transaction——会话开启了事务但迟迟不提交或回滚。它自己没干什么事却可能占着事务 ID堵住 autovacuum 的推进还会阻止其他会话清理死元组。我见过一个典型案例应用代码里有一段逻辑获取数据库连接后先begin然后去调外部接口外部接口超时设置是 30 秒但连接池却一直没有释放这个事务。结果高峰期几百个会话全卡在idle in transaction其他正常请求拿不到连接。当时就是用pg_stat_activity按 state 分组瞬间看到了异常分布再查具体卡住的会话SELECT pid, usename, client_addr, backend_start, xact_start, now() - xact_start AS xact_age, state, wait_event_type, wait_event, left(query, 200) AS query FROM pg_stat_activity WHERE state idle in transaction ORDER BY xact_start;找到具体的 pid 之后先联系业务方确认是否可以安全终止会话。如果确认可以用pg_terminate_backend(pid)结束对应会话注意这个函数返回true只代表发送了终止信号不代表进程立刻结束观察一下再确认。不要上来就杀所有会话尤其别在没确认业务影响的情况下重启整个数据库实例。3.2 场景二某张表被锁死了所有更新都在等锁问题大概是 PostgreSQL 运维里最常被问到的。现象很统一某张表的 UPDATE 突然全卡住应用侧不断报“锁等待超时”。这时候要做的是把pg_locks和pg_stat_activity关联起来找出“谁锁了谁”。我自己比较推荐先跑这个经典查询SELECT blocked.pid AS blocked_pid, blocked.usename AS blocked_user, blocked.query AS blocked_query, blocker.pid AS blocking_pid, blocker.usename AS blocking_user, blocker.query AS blocking_query FROM pg_stat_activity AS blocked JOIN pg_stat_activity AS blocker ON blocker.pid ANY(pg_blocking_pids(blocked.pid)) WHERE blocked.state IS NOT NULL AND blocked.wait_event_type Lock;pg_blocking_pids(pid)是 PostgreSQL 提供的一个辅助函数它返回阻塞指定 pid 的所有后端进程的 pid 组成的数据。用它来关联pg_stat_activity比直接解析pg_locks的字段简单得多而且能在多级阻塞场景下直接给出链的起点。看到结果后通常阻塞源是一个“开了事务但没提交”的会话或者是一个正在跑全表更新的长事务。如果阻塞源是业务侧临时会话确认无人使用后可以pg_terminate_backend如果是真实业务在更新大表那就只能等它跑完同时评估索引是否缺失、更新条件是否有问题。另一个常见情况是 DDL 锁。比如有人执行了ALTER TABLE哪怕只是加个字段在事务未提交前对目标表的AccessExclusiveLock会让所有普通读写全部排队。这种锁比较隐蔽因为 DDL 通常执行很快但事务开着不提交锁就一直不释放。遇到这个问题除了看pg_stat_activity的活跃查询之外还要注意xact_start很早但query很短的情况那大概率是应用程序没有及时提交事务。3.3 场景三磁盘快满了怀疑是表膨胀表膨胀是 PostgreSQL 长年运行的普遍问题。简单解释一下原理PostgreSQL 使用 MVCC 机制一条被 UPDATE 或 DELETE 的旧版本行并不会立刻物理消失而是先标记为“死元组”事务并发结束后由 VACUUM 负责回收。如果 autovacuum 没跟上死元组越积越多表占用的物理磁盘空间就会持续膨胀。判断表是否膨胀主要看两个指标n_dead_tup是否持续增长以及last_autovacuum是否长时间没更新。下面这条 SQL 基本能看出全库“最危险的几张大表”SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100 * n_dead_tup::numeric / NULLIF(n_live_tup n_dead_tup, 0), 2) AS dead_pct, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables WHERE n_live_tup n_dead_tup 100000 ORDER BY n_dead_tup DESC LIMIT 20;dead_pct超过 20% 甚至 50%基本可以确认膨胀已经比较严重。如果last_autovacuum是 “-” 或者时间非常久远说明 autovacuum 根本没跑到这张表上或者被某种原因卡住了例如长事务导致不能清理死元组或者表的空闲空间映射块连续较多。触发手动 VACUUM 时的判断我可以给一个实用建议优先跑普通VACUUM它不会持有排他锁不阻塞业务。VACUUM FULL会重写整个表并持有AccessExclusiveLock在业务高峰期跑VACUUM FULL很可能直接把系统干停摆。如果膨胀到必须收缩物理文件建议安排在维护窗口期并且提前确认复制延迟可以接受。膨胀问题通常还伴随着另一个信号idx_scan很低而seq_scan很高说明即使有索引优化器也可能因为统计信息陈旧而选择了顺序扫描。看到这种情况第一步不是加索引而是先ANALYZE一下相关表让统计信息重新生效。3.4 场景四主从复制延迟越来越大复制延迟在 PostgreSQL 里不算罕见但排查时要注意一个点到底是网络传输慢还是只读节点回放慢。pg_stat_replication提供了不同阶段的 LSN正好能把这两类问题区分开。看主库上的复制状态SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn)) AS replay_lag FROM pg_stat_replication;主库上能看到每个 standby 对应的sent_lsn已发送、write_lsnstandby 已写入操作系统、flush_lsnstandby 已刷盘、replay_lsnstandby 已回放。如果sent_lsn明显落后于主库当前写入位点那大概率是网络带宽或主库 WAL 发送进程的问题。如果sent_lsn已经跟上而replay_lsn落后很多说明 standcuby 上的回放进程在等什么常见原因是 standby 端有长查询在跑冲突了或者 standby 磁盘 I/O 太差回放速度跟不上。在 standby 节点上可以配合看pg_stat_activity里walreceiver进程的等待事件再结合系统负载判断是 I/O 瓶颈还是 CPU 瓶颈。如果确认是回放慢常见做法是调大max_standby_streaming_delay但这是缓解手段根本解法通常是检查 standby 上的长查询和索引缺失。4. 系统视图使用中最容易踩的坑4.1 统计数字不是实时的这是新手最容易犯的错。看到n_dead_tup很大就立刻判断膨胀严重看到pg_stat_user_tables里的计数跟业务实际不一致就怀疑统计丢了其实这些数字的刷新有固定机制。pg_stat_activity是实时的因为它是直接读取当前进程状态的。但pg_stat_user_tables这类统计视图是在事务提交之后才更新对应的计数器。也就是说一个事务在运行中产生的更新还没提交之前你看到n_tup_upd可能还是旧值。另外last_vacuum、last_autovacuum、last_analyze、last_autoanalyze这些时间字段也依赖后台进程真正执行完对应动作之后才会写进去。还有一个容易忽略的点pg_stat_statements中 SQL 的平均耗时会受系统时钟和采样周期影响它记录的是整个执行过程的累计耗时不包含网络传输时间所以不能直接等同于应用看到的总耗时。所以排查问题时我建议用“两次快照差值”的方法看趋势而不是单次绝对值。比如要判断某个时段内的写入速率-- 第一次取快照 SELECT tup_inserted, tup_updated, tup_deleted, now() AS sample_time FROM pg_stat_database WHERE datname current_database();间隔几分钟后再执行一次前后两个tup_inserted之差除以时间差就是这段时间的平均写入速率。这个方法比任何单一的累计值都可靠。4.2 pg_stat_statements 为空或全是 0很多人在需要分析慢 SQL 时发现pg_stat_statements里一条数据都没有或者所有耗时全都是 0。原因通常不是 SQL 本身没有执行而是扩展没启用正确。pg_stat_statements不是默认开启的。要让它生效必须在postgresql.conf中设置shared_preload_libraries pg_stat_statements注意这个参数修改后必须重启 PostgreSQL 才生效。光执行CREATE EXTENSION pg_stat_statements;不行因为扩展对应的共享库没有提前加载到共享内存里。我身边已经不止一个人在这里踩坑以为执行完建扩展就万事大吉结果查询还是空。另外有一个比较容易忽略的细节如果你需要blk_read_time和blk_write_time有有效数值还要确认track_io_timing参数是on。默认情况下这个参数是off即使 SQL 执行了大量磁盘读取这两个字段也会显示 0。在生产环境调整track_io_timing是安全的但会带来一点点额外的性能开销实测下来通常可以接受。还有一个权限问题普通用户查询pg_stat_statements时默认只能看到自己的 SQL。需要看全库所有 SQL 的话要授予相应角色pg_read_all_stats权限或者直接使用超级用户查询。排查慢 SQL 时我一般会先看总耗时排序再关注平均耗时和调用次数。有些 SQL 调用次数极多但单次很快累计耗时也不低这种通常不是“慢 SQL”而是“频繁调用”解决思路可能要从应用层合并请求或者加缓存入手。SELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, rows, round(shared_blks_hit::numeric * 8192 / 1024 / 1024, 2) AS hit_mb, round(shared_blks_read::numeric * 8192 / 1024 / 1024, 2) AS read_mb FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;4.3 普通用户看不到别人的查询PostgreSQL 从 9.6 开始对非超级用户隐藏了pg_stat_activity中的query、query_start、state_change等字段值。这是默认行为目的是保护不同用户之间的 SQL 隐私。如果你需要让某个运维账号能看到所有用户的活动 SQL可以把它加进pg_read_all_stats角色GRANT pg_read_all_stats TO your_ops_role;反过来如果你觉得默认权限过于开放希望普通用户连“自己数据库里有哪些表”都看不到那要理解清楚对系统视图的权限实际上由底层系统表和视图的 ACL 控制一般不建议在生产环境乱动。更稳妥的保护边界是通过pg_hba.conf或独立业务账号做访问控制而不是试图收紧系统表的默认权限。还有一个容易被误解的点pg_stat_activity里的query字段显示的内容会受track_activity_query_size参数限制默认是 1024 字节超出部分会被截断。如果应用里都是几千字的长 SQL你在视图里看到的只是 SQL 的前半段。需要保留更长上下文时可以调大这个参数但要注意每个后端进程都会按这个大小占用内存不宜调得过大。4.4 视图名相近但含义完全不同pg_stat_user_tables和pg_user_tables很容易混淆。前者是统计视图关注访问量、死元组、vacuum 时间后者是对象清单视图关注表名、所有者、是否分区。名字只差一个stat性质完全不一样。pg_stat_bgwriter和pg_stat_io也经常被搞混。pg_stat_bgwriter统计的是后台写进程的检查点和缓冲区写入行为pg_stat_io是 PG16 之后按 IO 类型细粒度的统计。两者都不直接反映业务 SQL 的性能而是反映实例整体 I/O 行为。建议查文档时重点看pg_stat_*前缀它代表“运行时统计”不带stat的pg_*视图则多数描述对象结构。遇到不确定的视图我的习惯是先用\d view_name看一下它的列注释很多视图字段都写清楚了含义比硬记靠谱。5. 从视图到监控体系单个系统视图查询能解决突发问题但真正稳定运维需要把系统视图串成体系。我自己在项目里会把这些查询整理进监控脚本定时采集并贮存到独立的监控库然后围绕几个核心指标做告警。重点看的指标不外乎这几类连接数使用率、idle in transaction会话数、最长事务时间、等待事件分布、n_dead_tup总量增长趋势、复制延迟、pg_stat_statements里 Top SQL 的平均耗时变化。这些全部能从系统视图查到原始数据。有一点值得注意系统视图查询本身也会产生数据库负载尤其是联合多张大视图做实时聚合的时候。比如在内网监控频率很高的场景下可以适当拉长采集间隔或者把采集脚本放到从库上执行避免对主库造成额外压力。最后提一个我自己的习惯凡是修改postgresql.conf里的参数比如开启pg_stat_statements、调整track_activity_query_size都会顺手在变更记录里写明“改动后需要重启”或“仅需 reload”避免过两个月回头看配置发现不生效。这个习惯虽然简单但真的能帮你省掉很多排查时间。系统视图就像数据库的仪表盘平时不一定要一直盯着但遇到问题的时候它们是最好的第一现场。先把pg_stat_activity、pg_locks、pg_stat_user_tables、pg_stat_replication、pg_stat_statements这几张常用视图用熟练你就能覆盖大多数日常排查场景了。