1. 系统视图不是另一批表先搞懂它们的底层逻辑接触过一段时间的PostgreSQL之后你会发现一个很有趣的现象明明客户端里执行\d看到的表、视图、索引、序列都在一个列表里摆着但你直接用SQL去查pg_tables、pg_indexes这些视图时感觉它们就像一套隐藏的管理后台。很多刚入门的人会下意识觉得这些视图就是DBA专用的元数据表查一查、看一看就行了。但实际上系统视图这套东西在水面下的设计逻辑比表面看起来要有意思得多。先说一个最容易被忽略的点系统视图本身不存数据。你查pg_views看到的每一行其实都来自数据库内部的目录表catalog tables比如pg_class、pg_namespace、pg_attribute这些。系统视图本质上只是把分散在多个目录表里的信息通过预定义的JOIN和筛选逻辑拼装成了一个看起来很方便的查询入口。换句话说你查的是视图真正干活的是那些啃硬骨头的底层目录表。为什么要这么设计直接暴露目录表不行吗理论上可以但实践上很糟糕。目录表里的字段命名极其晦涩比如pg_class.relfilenode、pg_attribute.atttypid你光看字段名根本不知道它是什么意思。而系统视图把这些字段包装成了人类友好的名字比如pg_tables.schemaname、pg_tables.tablename、pg_indexes.indexdef打开就能看懂。这是PostgreSQL为所有使用者提供的一层通用翻译层。另一个容易踩坑的认知误区是把系统视图当作静态快照。实际上这些视图是实时计算的。你每一次SELECT * FROM pg_stat_activity数据库都会现场去扫描当前所有后端进程的状态然后拼出结果给你。正是这种活的特性让它成为排查问题时的利器——你能看到当前这一刻系统正在发生什么。但同时也要意识到如果系统压力巨大频繁查询这类视图本身也会带来一点开销这一点我们后面专门展开。在继续往下讲之前我建议你先在自己的库里执行几条最有代表性的命令感受一下系统视图的即查即用。-- 查看当前库里所有的用户表 SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY schemaname, tablename; -- 查看当前所有活跃的后端连接 SELECT pid, usename, datname, state, query FROM pg_stat_activity WHERE state active;这两条命令第一条给你库里到底有什么表、是谁建的的静态全景第二条给你现在谁在连库、正在跑什么SQL的动态快照。两者合在一起就是系统视图最典型的两大使用场景摸清底细和实况监控。这篇文章我就围绕这两大场景把PostgreSQL系统视图的常用套路、实战案例和容易踩的坑一次讲透。2. 我排查问题时最常用的十张核心视图按场景逐个说说实话PostgreSQL自带的系统视图有上百张但你真正常用的其实没那么多。与其把\dv列出来的清单挨个背一遍不如按我遇到什么问题时、我会去查谁来组织记忆。这样才是真正的实战逻辑。2.1 看清库里的对象pg_tables、pg_views、pg_indexes、pg_sequences这四个视图负责回答库里有什么这一类问题。它们结构相似都有schemaname字段可以用来区分对象在哪个schema下。实际排查中我经常遇到一种情况某张表明明在库里存在但程序就是提示relation does not exist。这时候第一反应不是去翻代码而是查一下这张表到底在哪个schema下。SELECT schemaname, tablename, tableowner, tablespace FROM pg_tables WHERE tablename orders;注意pg_tables默认会把PostgreSQL自带的系统表也列进来它们的schemaname通常是pg_catalog。所以做统计报表的时候一定记得加一个schemaname NOT IN (pg_catalog, information_schema)的过滤条件否则你统计出来的业务表数量会包含一大堆系统内部对象数字完全不可信。2.2 看表结构设计的元老级三剑客pg_class、pg_attribute、pg_namespace这三张是真正的底层目录表虽然名字不叫视图但它们的信息量和灵活性在我眼里是排第一梯队的。举个典型例子你想找出来所有字段名以_time结尾的表用pg_tables就完全做不到因为它根本没有字段级别的信息。SELECT n.nspname AS schema_name, c.relname AS table_name, a.attname AS column_name FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_attribute a ON a.attrelid c.oid WHERE a.attname LIKE %\_time AND c.relkind r AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY schema_name, table_name;这段SQL里出现了两个关键点。第一pg_class.relkind r表示普通表如果你想查视图需要改成v查序列用S查物化视图则是m。这个字段是判断对象类型最重要的过滤器。第二JOIN条件是靠oid关联的。pg_namespace.oid是每个schema的唯一标识pg_class.relnamespace指向它pg_attribute.attrelid指向pg_class.oid。这套OID关联体系是整个PostgreSQL系统目录的基础。弄懂了它你基本就具备了自由定制查询的能力——系统视图没覆盖到的元数据组合方式你都能自己拼出来。2.3 动态监控的第一张表pg_stat_activity这是最出名的系统视图没有之一。每当我听到数据库卡了连接数满了某个SQL跑了好几个小时第一反应永远是先看一眼pg_stat_activity。SELECT pid, usename, datname, state, wait_event_type, wait_event, query_start, query FROM pg_stat_activity WHERE datname 你的业务库 ORDER BY query_start ASC;这张视图有几个字段值得逐一说透。state字段表示当前连接的状态常见的值有active正在执行、idle空闲、idle in transaction事务内空闲、fastpath function call等。其中idle in transaction是需要特别警惕的状态它意味着连接已经开启了事务但既不提交也不回滚就挂在那里。这种连接多了会疯狂占用事务ID最终导致数据库进入事务ID耗尽的保护性停机。wait_event_type和wait_event组合起来告诉你这个会话在等什么。如果是Lock类型说明它在等待另一把锁如果是IO类型说明它在等磁盘。query_start是这条SQL开始执行的时间。两个query_start相差极远的active会话很可能就是慢查询的铁证。我这里分享一个自己的排查习惯每次收到数据库慢的告警时我不会先看慢查询日志而是先执行上面这条SQL按query_start升序排列把最老的几个active会话抓出来看。因为慢查询日志是事后记录而这视图抓的是正在发生的现场。两者结合着用才能还原完整的故障时间线。2.4 摸清表大小pg_class.relpages 与 pg_total_relation_sizepg_stat_all_tables这类视图确实提供了seq_scan、idx_scan这些统计信息但要说查表的物理大小我反而更推荐直接用函数而不是翻统计视图。因为统计视图的数值可能不是实时刷新的。SELECT schemaname, tablename, pg_total_relation_size(schemaname || . || tablename) AS total_size_bytes, pg_size_pretty(pg_total_relation_size(schemaname || . || tablename)) AS total_size FROM pg_tables WHERE schemaname public ORDER BY total_size_bytes DESC;pg_total_relation_size返回的是表本身、TOAST表、索引加在一起的总字节数。做空间治理的时候我几乎只用这个函数因为表实际占了多少磁盘才是运维真正要关心的数字。如果你想知道为什么表会附带一个TOAST表简单说PostgreSQL为了支持超长字段比如大文本、JSONB里塞了大对象会把大字段的值压缩并行外存储到附属的TOAST表里。你看到一张只有几个字段的表可能在磁盘上其实是两张表。这也是为什么有时候你DROP一张表释放的磁盘空间比预想的大。2.5 查询计划与执行统计pg_stat_statements严格来说pg_stat_statements是PostgreSQL的一个扩展模块不是默认就有的系统视图。但它实在太重要了几乎所有生产环境的性能排查都绕不开它所以我把它和系统视图放在一起讲。安装方式很简单-- 需要超级用户权限执行 CREATE EXTENSION IF NOT EXISTS pg_stat_statements;安装后它会在postgresql.conf里由shared_preload_libraries加载。我见过不少新手只执行了CREATE EXTENSION却没配置shared_preload_libraries结果重启后视图又空了其实是没有被正确加载进共享内存所有统计归零。这张视图干的事就是把数据库执行过的SQL按语句文本归一化后统计各自的执行次数、总耗时、平均耗时、缓冲命中数等。比如你想找出过去一段时间里总耗时最长的10条SQLSELECT queryid, calls, total_exec_time AS total_time_ms, mean_exec_time AS mean_time_ms, rows, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;注意字段名total_exec_time在不同版本有差异PostgreSQL 13及以前叫total_time14开始改成了total_exec_time。如果你用老版本复制网上的新版本SQL就会报字段不存在这一点很容易被忽略。2.6 锁与阻塞链路pg_locks 与 pg_blocking_pids()出现SQL一直卡住不动的情况八成是锁等待。这时要查的视图是pg_locks但直接看它非常累因为每一行是一条锁记录而不是一个会话。更高效的做法是用pg_blocking_pids()函数传一个pid进去直接得到谁堵住了我的pid数组。SELECT a.pid AS blocked_pid, a.query AS blocked_query, unnest(pg_blocking_pids(a.pid)) AS blocking_pid, b.query AS blocking_query FROM pg_stat_activity a LEFT JOIN pg_stat_activity b ON b.pid ANY(pg_blocking_pids(a.pid)) WHERE a.state active AND a.pid pg_backend_pid() AND pg_blocking_pids(a.pid) {};这条SQL非常实用我遇到死锁或者长事务阻塞时基本都会先跑一遍。注意unnest(pg_blocking_pids(a.pid))这个写法因为函数返回的是一个数组直接SELECT出来只能看到一行数组用unnest展开后才能和pg_stat_activity按pid做关联从而拿到阻塞方的SQL文本。从看到了一串数字pid到看到了阻塞方的完整SQL排查效率完全不在一个量级。2.7 查看索引使用率pg_stat_user_indexes索引建了很多到底哪些真的被用到了哪些是纯属自我安慰这个问题的答案藏在pg_stat_user_indexes里。SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY idx_scan ASC;idx_scan是这条索引被用于扫描的次数。如果一张业务表上有好几个索引其中有的idx_scan长期为零基本可以断定它没被查询优化器选中。不过注意一个细节有些索引只在特定写入模式下用于约束检查比如唯一索引即便idx_scan不高也不能随便删因为它还承担着保证数据唯一的职责。2.8 看那些统计信息视图时的心态准备pg_stat_all_tables 的滞后问题pg_stat_all_tables提供了seq_scan、idx_scan、n_tup_ins、n_tup_upd这些累计计数器看起来很美但有一个关键问题这些统计数值不是严格实时的。PostgreSQL的统计信息采集器会把后端的活动周期性刷新到统计表里而不是每次操作立刻同步。正常情况下延迟很小但在高并发场景下你看到数字可能跟真实状态存在几秒钟的滞后。所以我的建议是pg_stat_all_tables适合做趋势观察不适合做精确判断。比如这张表最近是不是写得很频繁你可以对比不同时间点的n_tup_ins差值但你要据此判断某个事务是否已经提交完成那还得去看更可靠的pg_stat_activity和事务日志。3. 与 information_schema 的正面交锋什么时候该用哪套很多人在写跨数据库兼容的代码时会优先选择查information_schema因为它是由SQL标准定义的理论上在MySQL、PostgreSQL、SQLite这些数据库里都有。这个思路本身没错但放在PostgreSQL里有一个很现实的代价information_schema的查询效率远不如pg_catalog而且它不包含PostgreSQL特有的功能信息。我给你一个真实对比。假设我想知道某张表的所有列名和数据类型用information_schema.columns是标准写法SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema public AND table_name orders;但同样的需求用pg_attribute加pg_type来查SELECT a.attname AS column_name, t.typname AS data_type, NOT a.attnotnull AS is_nullable FROM pg_attribute a JOIN pg_type t ON t.oid a.atttypid WHERE a.attrelid public.orders::regclass AND a.attnum 0 AND NOT a.attisdropped;两种写法都能拿到列信息但后者可以额外带出更多PostgreSQL独有的细节比如列的存储策略a.attstorage、列的排序规则a.attcollation、列的默认值表达式等。追求标准兼容性时用前者追求深度控制时用后者。information_schema还有一个常见限制它不会显示TOAST表、分区子表这些内部对象也不会显示物化视图。如果你运营的系统里大量使用了物化视图你会发现information_schema对它们完全是视而不见的。这时候直接用pg_class配合relkind m来查询才是正解。那么实际项目中怎么选我提供一个我自己用下来比较顺手的分工原则写与应用开发相关的通用元数据查询时优先用information_schema因为语义清晰、字段命名标准将来换数据库时迁移成本低。写运维监控、性能排查、空间治理相关的查询时直接用pg_catalog或相关统计视图因为它们更完整、更快、更贴近真实存储结构。需要兼顾标准和深度时先查information_schema拿到结构骨架再用pg_catalog补充细节。两者不是二选一而是互补。另外提醒一个小坑information_schema.columns里的is_nullable返回的是字符串YES/NO如果你在Python或者Java里用布尔类型去接收容易触发类型转换错误。而pg_attribute里的attnotnull是真正的布尔类型少一层别扭。4. 一次深夜磁盘告警系统视图实战排障全流程讲再多视图清单不如走一遍真实场景。上个月某天晚上十一点我接到某公司一套业务系统的告警数据库所在磁盘使用率超过90%眼看要触发只读保护了。4.1 第一步查表大小排序锁定大对象我的第一反应是跑那套pg_total_relation_size的排序SQL从大到小看看磁盘空间都被谁吃掉了。结果发现排在前面的是一个名字极其普通的表log_events大小约48GB。然后紧接着第二名也很有意思是一张名为order_snapshots的表约30GB。log_events是业务侧的埋点日志表设计上就允许快速膨胀order_snapshots则是订单快照表理论上应该只保留最近一段时间的快照但显然它并没有按预期清理。这里有一个值得展开的细节我用pg_total_relation_size拿到的是表索引TOAST的整体大小但如果想进一步拆开看可以用pg_table_size只算表本体不含索引再配上pg_indexes_size只算索引。继续深挖后我发现log_events的索引大小竟然占了将近12GB——它的索引建得过多字段上好几个单列索引都没什么实际作用。4.2 第二步对历史连接做人口普查磁盘问题往往只是表象背后是写入量过大。我接着查了pg_stat_activity看看当前有哪些会话正在疯狂写入。SELECT pid, usename, application_name, state, query_start, query FROM pg_stat_activity WHERE datname business_db ORDER BY query_start ASC;结果发现有一个来自批处理任务的长连接已经跑了一个多小时正在持续往log_events里做INSERT。再结合刚发现的12GB冗余索引基本可以断定一个问题这批任务在频繁写入的同时还需要维护一大堆低效索引进一步放大了磁盘占用。4.3 第三步用 pg_stat_user_indexes 找出僵尸索引既然怀疑索引冗余那就用pg_stat_user_indexes验证。查出来的结果让我很惊讶log_events上有5个索引其中3个的idx_scan在最近七天里都是0。这3个索引完全派不上用场纯粹在增加写入成本和磁盘占用。除了这三张表还要说说索引清理的保守做法。我当时的处理不是直接DROP因为这毕竟是生产表。我先在预发环境里模拟了同样的读写负载做了查询计划的对比确认没有一条核心查询路径依赖这几个索引后才在一个维护窗口里执行了DROP INDEX CONCURRENTLY逐个删除不锁表。4.4 第四步清理后验证清理完三个冗余索引后pg_total_relation_size立刻显示log_events缩小了约11GB。而order_snapshots那边的30GB则不是索引问题是真实的数据积累。我翻了一下表里的时间戳字段发现最老的数据已经是一年半前了。和业务确认后制定了按月归档删除的清理任务并且把原本永久保留的默认策略改成了只保留6个月。整个过程结束后我用一条SQL做了最终复盘把这次触发的三个根因都串起来了SELECT relname AS table_name, pg_size_pretty(pg_table_size(c.oid)) AS table_size, pg_size_pretty(pg_indexes_size(c.oid)) AS index_size, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size FROM pg_class c WHERE c.relkind r AND c.relname IN (log_events, order_snapshots);结果是两张表的index_size都停留在了一个合理水位。这个案例最大的教训是告警只能告诉你哪里爆了系统视图的作用是告诉你为什么会爆。没有pg_stat_user_indexes那些僵尸索引根本不会浮出水面没有pg_total_relation_size你也没办法量化清理收益。5. 查询系统视图时的隐形成本与注意事项系统视图好用是好用但我还是要泼一盆冷水它们不是普通业务表查询方式和频率都需要讲究。否则你本来就是在排查性能问题结果自己的查询又变成了新的性能问题。5.1 pg_stat_activity 会把查询者自己也算进去一个小坑极其隐蔽。当你执行SELECT * FROM pg_stat_activity时正在运行的会话列表里包含着执行这条查询本身的会话。所以在做监控告警规则时如果你统计active连接数会发现永远比预期多一个。标准解法是在WHERE条件里加上pid pg_backend_pid()过滤掉自己。5.2 高频轮询统计视图会放大监控开销有些团队喜欢每个10秒就用pg_stat_activity做一次全量抓取然后存到监控系统里。这在连接数只有几十个的时候没什么问题但一旦连接数上千每次全量扫描所有后端进程再拼装结果不但会让监控库本身产生大量IO还可能干扰生产库的稳定性。我自己的经验是把轮询频率从10秒改成30秒甚至60秒配合state字段的过滤条件只抓active和idle in transaction这两种关键状态。真要分析历史趋势时再用pg_stat_statements的累计数据而不是反复抓快照。5.3 pg_stat_statements 可能泄露敏感SQL文本生产环境开启pg_stat_statements后任何能登录到这个库的用户只要有权限查询该视图都能看到所有SQL的规范化文本。所谓规范化是指把具体参数值替换为占位符比如SELECT * FROM orders WHERE id $1但表名、列名、查询结构仍然是明文。如果系统里有需要保密的数据表名或字段名就得小心了。建议通过配置pg_stat_statements.max和pg_stat_statements.track参数来控制统计粒度必要时给业务账号收回对这张视图的访问权。5.4 统计信息视图的重启归零特性pg_stat_*系列里很多计数器的值会随着数据库重启而清零。也就是说你不能拿累计值去跨重启做趋势分析否则会得到一条断裂的曲线。解决方法是把监控系统的时间序列数据库TSDB看作唯一可信的历史数据源数据库里的累计值只用于短周期内的横向对比。这一点在做自动化报表时很容易被人忽略等重启后数字跳变才发现历史基线没了。5.5 不要在生产环境随意做跨库JOIN系统视图本身是库内查询但你如果要对比多个数据库的元数据有的同学会图省事用dblink或postgres_fdw把一个库的pg_class跟另一个库的pg_class做JOIN。这个操作在数据量小的时候没问题但一旦业务库对象上万FDW拉取元数据的速度会慢得让你怀疑人生。更好的办法是在两边的库里各跑一套独立的元数据导出查询然后把结果拉到监控端做归并。6. 进阶玩法把系统视图组合成自己的监控工具最后分享一点从个人实践中沉淀出来的进阶用法。系统视图单张看都挺朴素但组合起来就是一套完整的数据库体检工具。我不太建议直接去装一堆重型监控组件因为对于中小规模的系统来说系统视图自带的信息已经能覆盖大部分预警需求。6.1 一次查询完成数据库健康体检我自己在服务器上保存了一个固定脚本每次觉得数据库状态可疑时都会先跑一遍。它的核心逻辑是同时检查四件事连接数水位、活动事务最老时长、长事务个数、序列剩余量。这里分享其中最值得关注的两个。-- 检查是否有长时间未提交的事务超过10分钟 SELECT pid, xact_start, now() - xact_start AS xact_age, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND now() - xact_start interval 10 minutes ORDER BY xact_start ASC;注意xact_start字段的用途它记录的是当前事务的开始时间。如果一个连接长时间停在idle in transaction状态那xact_start会非常老即使它在pg_stat_activity里看起来只是发呆。这条视角能看到普通连接数监控看不到的危险信号。-- 检查事务ID消耗进度 SELECT age(datfrozenxid) AS xid_age, datname FROM pg_database ORDER BY age(datfrozenxid) DESC;pg_database里的datfrozenxid是数据库里最老的活动事务ID水线。age()函数算出来的数字越大说明事务ID越接近回卷临界点。我见过某系统因为漏配了autovacuum_freeze_max_age导致事务ID过快耗尽最后只能停库修复。提前用这个查询盯着能避免很多灾难。6.2 视图函数的高阶组合模式如果你想做一个阻塞实时看板用pg_locks加pg_stat_activity加pg_class三张视图联合查询能直接输出哪张表被哪条SQL锁住了。下面这个写法我一直在用SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, cls.relname AS locked_table, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_locks l ON l.pid blocked.pid JOIN pg_class cls ON cls.oid l.relation JOIN pg_stat_activity blocking ON blocking.pid ANY(pg_blocking_pids(blocked.pid)) WHERE blocked.state active AND cls.relname IS NOT NULL AND blocked.pid pg_backend_pid();这个查询把进程-锁-表名-SQL四者拉平在一行里出现锁等待时一眼就能看出是谁在卡谁、卡在哪张表、执行的是什么SQL。配合告警平台只要查出来非空就触发通知效果比直接监控pg_locks里那一大堆locktype relation的记录好得多——因为那些记录里有很多是正常的行级锁和意向锁不看上下文根本没法判断是不是问题。6.3 用视图做自动化巡检报告如果你有定时任务能力我建议把前面讲到的几个查询整合到一个SQL脚本里每天凌晨跑一次输出三个数字最大表Top10列表、长时间运行的事务数量、最近一天执行最慢的SQL Top5。输出到日志文件后每周扫一眼趋势很多隐患会在爆发前就露出苗头。这套方案不需要安装额外组件不侵入业务库唯一的依赖是PostgreSQL自身的系统视图体系——而这恰恰是它最可靠的地方。系统视图不是DBA的玩具而是每个深入使用PostgreSQL的人都应该掌握的通用能力。我到现在仍然保持着遇到问题先查视图再查日志的排查顺序因为这个顺序几乎总能让我在最短时间内还原出问题的全貌。