开头干后端和运维的跟 PostgreSQL 打交道久了迟早会碰上这么一件事某个业务表不知道谁建的、某张表占了多少磁盘、某个慢查询卡在哪个锁上、某个字段有没有索引。遇到这些问题翻文档找不到现成答案但你查几个系统表几秒钟就能定位。PostgreSQL 的系统表就是数据库自己的“档案室”所有元数据——表结构、字段、索引、约束、角色、会话、统计信息全都存在这里面。这篇内容就围绕“postgresql常见系统表”展开把pg_catalog里那些高频使用的系统表拆开讲清楚包括它们各自管什么、关键字段怎么理解、实际查询怎么写。适合刚接触 PostgreSQL 的开发者快速入门也适合已经在用但遇到排查需求、想系统梳理一遍的运维和 DBA。看完你会发现与其遇到问题瞎猜不如直接学会查这些表效率高得多。1. 核心思路系统表到底是怎么组织起来的1.1 系统表存放在哪里pg_catalogPostgreSQL 的表结构信息不算什么“黑盒数据”它们就存在当前数据库实例的一套特殊表里这套表的集合叫pg_catalog也就是我们常说的系统表。你可以在任意一个数据库里执行SELECT tablename FROM pg_tables WHERE schemaname pg_catalog ORDER BY tablename;会看到pg_class、pg_attribute、pg_namespace、pg_database、pg_roles、pg_settings、pg_stat_activity这一长串名字。看起来很多但真正需要掌握的没那么多。理解它们的关键是先建立两条主线静态元数据描述“数据库里有什么”比如有哪些表、字段、索引、约束。动态运行信息描述“数据库现在怎么样”比如谁在连、哪些查询在跑、表有没有膨胀、锁堵在哪里。系统表本身也是普通表只是 PostgreSQL 在初始化集群的时候自动创建、并对普通用户默认开放了查询权限。你不需要记太多内部原理只要知道它们是“查元数据的入口”就够了。1.2 PostgreSQL 的 OID 机制连接系统表的钥匙系统表之间频繁通过一个叫oid的字段互相关联。OID 是“对象标识符”可以理解成数据库内每个对象的身份证号。比如pg_class里每张表都有一个唯一的oidpg_attribute里每个字段通过attrelid指向所属表的oid。实际查询的时候你经常会写类似这样的连接条件SELECT ... FROM pg_class c JOIN pg_attribute a ON a.attrelid c.oid ...这条关联逻辑就是理解系统表之间关系的主干。很多新手第一次看系统表觉得乱其实就是没先搞清楚 OID 这条隐藏的“外键线”。搞明白之后系统表在你眼里就不再是一张张孤立的表而是一张清晰的元数据网络。1.3 命名空间与可见性规则另一个关键概念是pg_namespace它在中文社区里常被叫“命名空间”也就是 schema。PostgreSQL 里对象的完整名称是“schema.对象名”而pg_namespace表就负责记录这些 schema 的信息。查询业务表的时候一般建议显式带上 schema 条件SELECT relname FROM pg_class WHERE relnamespace public::regnamespace;用::regnamespace这种类型转换可以省去手动去pg_namespace里查 oid 的步骤写起来更干净。习惯这个“带 namespace 过滤”的写法之后你会发现系统表查询结果会精准很多因为一个实例里不同 schema 可能有很多同名表。提示regclass、regnamespace这类“注册类型”直接把对象名转成 OID是写系统表查询时非常顺手的小工具能省不少 join。2. 高频系统表逐个拆解每个表管什么、关键字段怎么读这一部分会把我在实际工作中用得最多的几张系统表单独拎出来讲。注意我讲的重点不是“把所有字段列一遍”而是告诉你“这张表解决什么问题、字段之间怎么关联、典型场景怎么查”。2.1 表与字段pg_class pg_attribute pg_namespacepg_class是系统表里的“主表”它记录所有表和类对象的信息。不只是业务表索引、序列、视图这些也会在里面留一条记录。关键字段字段含义relname对象名字表名、索引名等relnamespace所属 schema 的 OIDrelkind对象类型r表示普通表v表示视图i表示索引S表示序列p表示分区表m表示物化视图relowner属主角色的 OIDreltuples当前统计信息中的估算行数relpages占用的页数每页通常 8KBrelhasindex表是否有索引pg_attribute记录每个字段的信息attrelid指向pg_class.oidattname是字段名atttypid是字段类型的 OIDattnum是字段在表中的位置编号。注意一个细节每个表都会有 3 个隐藏的系统列比如ctid、xmin、xmax它们的attnum是负数查询时一般要加条件attnum 0否则你会看到莫名其妙的记录。典型查询查看某个表有哪些字段、类型、是否允许为空。SELECT a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, NOT a.attnotnull AS is_nullable, d.description AS column_comment FROM pg_attribute a LEFT JOIN pg_description d ON d.objoid a.attrelid AND d.objsubid a.attnum WHERE a.attrelid public.user::regclass AND a.attnum 0 ORDER BY a.attnum;这里用到了pg_description这是注释表通过objoid指向对象的 OIDobjsubid区分是表级注释还是字段级注释。如果你想看某张表的注释把objsubid置为 0 查询即可。2.2 数据库与角色pg_database pg_roles pg_userpg_database记录数据库集群里所有数据库的信息。字段不多但datname和datdba是最常用的。查询实例里有哪些数据库SELECT datname, pg_encoding_to_char(encoding) AS encoding FROM pg_database;pg_roles记录所有角色信息包括登录权限、超级用户权限、连接数上限等。注意所有角色都在这里面而pg_user只是pg_roles的一个视图只展示rolcanlogin true的登录角色并且把密码字段隐藏掉了。日常查“哪些用户可以登录”直接用pg_user更清晰SELECT usename, usesuper, valuntil FROM pg_user;权限类系统表还有pg_authid真实密码存储处普通用户不可读和pg_auth_members角色成员关系。从排查角度看大多数情况下你不需要直接查pg_authid能查到“谁有什么角色、能不能登录”就够了。2.3 索引与约束pg_index pg_constraint先说索引。pg_index的结构比较特殊它是“pg_class 的附属表”专门记录索引的细节。关键字段字段含义indexrelid索引自身的 OID关联pg_classindrelid索引依附的表 OID关联pg_classindisunique是否唯一索引indisprimary是否是主键索引indkey索引字段的 attnum 数组indkey是 int2vector 类型可以简单理解成一个数组。比如一个索引建在字段(id, created_at)上indkey就是1 2指向表的第 1 和第 2 个字段。实际使用中我们很少直接解析indkey而是用 PostgreSQL 提供的pg_get_indexdef()函数直接生成索引定义语句SELECT c.relname AS index_name, pg_get_indexdef(i.indexrelid) AS index_definition FROM pg_index i JOIN pg_class c ON c.oid i.indexrelid WHERE i.indrelid public.user::regclass;这个查询的输出直接就是一条可读性很高的索引定义比如CREATE INDEX ... ON public.user USING btree (...),比你自己拼字段名方便得多。约束信息存储在pg_constraint里。主键、外键、唯一约束、检查约束都在这张表里contype字段用来区分类型常见取值包括p主键、f外键、u唯一、c检查、x排他。查某张表上的约束SELECT conname, contype, pg_get_constraintdef(oid) AS constraint_def FROM pg_constraint WHERE conrelid public.order::regclass;注意PostgreSQL 里主键和唯一约束会自动创建索引所以有时候你会看到pg_index里多了几条索引记录不要惊讶那是约束的“配套设施”。2.4 统计信息与活动会话pg_stat_activity 家族所有动态运行信息都集中在pg_stat_*视图里。这个“家族”不是一张表而是一套视图但它们在日常排查中的价值非常高。先认识最核心的几张pg_stat_activity当前所有数据库会话连接的状态包括正在执行的 SQL、等待事件、客户端地址等。pg_stat_user_tables每个用户表的增删改查次数、顺序扫描次数、死元组数、活元组数等。pg_stat_user_indexes索引扫描次数、读取的元组数等。pg_stat_database整个数据库级别的连接数、事务提交回滚次数、缓存命中率等。比如查看当前正在执行的查询和它们的时长SELECT pid, usename, client_addr, state, now() - query_start AS query_duration, query FROM pg_stat_activity WHERE state idle ORDER BY query_start;这个查询是 DBA 排查慢查询的“第一板斧”。后面讲实操的时候我还会给更多可复用的组合查询。pg_stat_user_tables里的n_dead_tup字段尤其重要它表示表中死元组的数量。死元组过多通常意味着 autovacuum 没有及时工作会导致表膨胀SQL 变慢。你可以用下面这条查询快速找“膨胀可疑表”SELECT schemaname, relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup n_dead_tup 0 THEN round(n_dead_tup * 100.0 / (n_live_tup n_dead_tup), 2) ELSE 0 END AS dead_ratio FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;2.5 其他配合使用的系统表除了上面那些还有几张系统表/视图经常作为“辅助工具”出现pg_settings当前会话或实例的所有配置参数比如work_mem、max_connections、shared_buffers。排障的时候最先查的就是它。pg_locks当前实例中的所有锁信息包括锁类型和锁住的对象。它跟pg_stat_activity一起查能看出会话之间是谁堵了谁。pg_views/pg_indexes这两个其实都是视图但查询起来非常直观适合不想记底层字段的人。pg_extension记录已安装的扩展比如postgis、pg_stat_statements是否启用。pg_proc存储过程和函数信息排查自定义函数问题时用得上。3. 实操过程7 个日常排查必用查询前面介绍的是理论这一部分直接上实战。下面这 7 个查询是我自己在生产环境和日常开发中反复用到的可以说每一句都踩过坑或者解决过实际问题。你可以直接复制保存当成自己的“系统表速查手册”。3.1 查看某张表的结构和注释业务交接或者接手老项目的时候第一件事通常是“看看这张表里都有什么字段”。方法很多但从系统表里写查询可以顺带把字段注释也带出来SELECT a.attnum, a.attname AS field_name, format_type(a.atttypid, a.atttypmod) AS field_type, NOT a.attnotnull AS is_nullable, COALESCE(d.description, ) AS field_comment FROM pg_attribute a LEFT JOIN pg_description d ON d.objoid a.attrelid AND d.objsubid a.attnum WHERE a.attrelid public.order_info::regclass AND a.attnum 0 ORDER BY a.attnum;查询结果会以字段顺序列出每个字段的类型、是否可空、注释内容。注意format_type()函数会把类型内部的修饰信息格式化比如varchar(255)会直接显示完整长度这比看atttypid原始 OID 直观很多。3.2 查看某张表的索引和大小检查一张表有没有走索引、索引有哪些最常见的操作是查pg_indexes视图或者用pg_index自己 join。我更推荐直接查pg_indexes因为 PostgreSQL 已经帮你把信息都格式好了SELECT indexname, indexdef FROM pg_indexes WHERE schemaname public AND tablename order_info;如果还想带上索引大小可以 joinpg_class然后调用pg_size_pretty()格式化字节数SELECT i.relname AS index_name, pg_size_pretty(pg_relation_size(c.oid)) AS index_size FROM pg_index idx JOIN pg_class i ON i.oid idx.indexrelid JOIN pg_class c ON c.oid idx.indrelid WHERE c.relname order_info ORDER BY pg_relation_size(c.oid) DESC;pg_relation_size()是 PostgreSQL 里非常方便的函数传入表的 OID 或者 regclass 都能返回以字节为单位的体积。3.3 查看表空间占用 Top N数据库磁盘满了需要立刻找出哪些表占空间最多。这个需求几乎每个 DBA 都会遇到。组合pg_class、pg_namespace与pg_total_relation_size()函数即可SELECT n.nspname AS schema_name, c.relname AS table_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind IN (r, p) AND n.nspname NOT LIKE pg_% ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20;pg_total_relation_size()是“表本身 索引 TOAST 表”的总大小跟pg_relation_size()只算表数据不同。实际排查磁盘占用的时候务必用pg_total_relation_size()否则你会漏掉索引占用的空间。经验很多“表不大但数据库很大”的问题根因往往是索引膨胀所以查询 Top N 时同时看表和索引才是完整思路。3.4 查看当前锁和阻塞链数据库突然卡住新 SQL 全部堆积不用想八成是锁等待。先查pg_stat_activity看有没有长事务SELECT pid, state, wait_event_type, wait_event, now() - xact_start AS xact_age, now() - query_start AS query_age, query FROM pg_stat_activity WHERE state idle ORDER BY xact_start;如果发现有会话卡了很久进一步关联pg_locks找谁阻塞了谁。一个比较简化的查询SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query, blocking.query AS blocking_query FROM pg_stat_activity blocked JOIN pg_locks l1 ON l1.pid blocked.pid AND NOT l1.granted JOIN pg_locks l2 ON l2.locktype l1.locktype AND l2.database l1.database AND l2.relation l1.relation AND l2.pid blocked.pid AND l2.granted JOIN pg_stat_activity blocking ON blocking.pid l2.pid WHERE blocked.state idle;这条查询的逻辑是先找到“没有获得锁”的会话granted false再找到“持有同一把锁”的会话把他们配对起来。生产环境定位死锁或锁等待时非常有用。实际使用中如果发现阻塞链很长建议优先处理blocking_pid对应的会话。3.5 查看连接数和最大连接数连接数被打满也是经典故障。PostgreSQL 默认连接数通常是 100一旦应用连接池配置不合理很容易打满。查当前连接数和最大连接数SELECT (SELECT count(*) FROM pg_stat_activity) AS current_connections, (SELECT setting FROM pg_settings WHERE name max_connections) AS max_connections;还可以按数据库维度看每个库的连接分布SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname ORDER BY count(*) DESC;看到某个库连接数异常高下一步应该去查是哪些客户端 IP 在连接SELECT client_addr, count(*) FROM pg_stat_activity GROUP BY client_addr ORDER BY count(*) DESC;排查连接数问题的时候这一套组合拳基本够了。3.6 查看表的扫描与读写情况应用上线之后你经常需要判断“哪些表查得最频繁”“哪些索引一直用不上”。前一个问题查pg_stat_user_tables后一个问题查pg_stat_user_indexes。找出被频繁顺序扫描的表可能缺索引SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;找出从没被用过的索引可以评估是否删除SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE idx_scan 0 AND schemaname NOT LIKE pg_% ORDER BY relname;注意pg_stat_*视图的数据是从统计采样和计数器来的在清理统计信息或数据库重启后会重新累计。判断“索引从未使用”要结合一段足够长的时间窗口观察不能刚上线一天就急着删索引。3.7 查看配置参数与 wal 相关设置有时候跟别人讨论性能问题上来第一句话就是“你们work_mem多少、shared_buffers多大”这种时候直接查pg_settings不要一个个去翻配置文件SELECT name, setting, unit, short_desc FROM pg_settings WHERE name IN (shared_buffers, work_mem, maintenance_work_mem, max_connections, effective_cache_size, wal_level);pg_settings还可以按类别过滤比如想知道所有与 vacuum 相关的参数SELECT name, setting FROM pg_settings WHERE name LIKE %vacuum%;每个参数还带一个context字段表示这个参数属于哪个级别postmaster、sighup、superuser、user判断修改后是否需要重启直接看这个字段最靠谱。4. 常见问题与排查技巧实录系统表用久了总会碰到一些“看起来对但结果不对”的情况。这里面既有逻辑问题也有权限问题还有统计信息过期的问题。下面这些坑我自己基本都踩过逐个列出来帮你提前避雷。4.1 为什么查 pg_class 看不到业务表一个很常见的问题在pg_class里where relname user查不到任何结果。原因多半是表建在public模式但你没加 schema 过滤或者当前search_path里默认只搜了某个不含目标表的 schema。另一个可能你把表建在了别的数据库里。每个数据库的pg_catalog是实例级共享的还是库级隔离的正确答案是“系统表是集群级的但在每个数据库里通过 OID 映射出不同的可见性”最直观的表现就是你在 A 库里执行查询看不到 B 库里用户建的表因为pg_class里记录的是“属于当前数据库可访问范围的对象”。也可能你执行了DROP TABLE但没 commit事务隔离导致自己查不到。排查思路先确认当前current_database()然后显式加上 schema 条件最后确认事务是否已经提交。4.2 pg_class.relname 和表名大小写问题PostgreSQL 里不带引号创建的表名会自动转为小写。如果你的表名是OrderInfo双引号创建的那relname里存的也是OrderInfo而不是小写。这时候你用relname orderinfo查询就查不到。建议在系统表关联业务表时统一用::regclass转换SELECT * FROM pg_class WHERE oid public.OrderInfo::regclass;用::regclass的好处是让 PostgreSQL 自己解析标准对象名自动处理大小写和搜索路径比手动拼字符串可靠得多。4.3 统计视图为 0 或者长期不变pg_stat_activity一般实时性没问题但pg_stat_user_tables里的计数有可能长时间不变化。这种时候先检查是不是采集周期本身的问题统计信息不是实时刷新而是由统计收集器定期汇报。默认配置下视图展示的是“上一次收集器刷新时的值”并非精确到秒的实时值。另一个原因是track_count off被显式关闭了。查看配置SHOW track_counts;如果不是on需要修改配置文件并重启实例。如果确认开启但数值还是没有变化可能是静态数据表里没有 DML 操作计数当然不增长。4.4 pg_locks 关联查询结果太多或者一直查不到锁信息在系统表里非常容易出现“信息过载”一条会话可能持有多个锁一个查询语句会涉及多个锁对象关联查询一不小心就会返回几百行。这时候优先过滤锁类型SELECT * FROM pg_locks WHERE locktype relation;如果一直查不到锁而业务确实卡住了想一想是不是锁在了 advisory lock 或者行级锁上。行级锁不会出现在pg_locks中行锁存储在堆内surface 上查不到不代表没有锁等待。判断行锁拥堵可以借助pg_stat_activity里wait_event transactionid来观察。4.5 权限不足Query failed: permission denied普通开发账号默认能查很多系统表但并非全部。pg_authid密码表和pg_largeobject这类涉及安全信息的表默认不可读。如果业务账号需要读取这些表需要给账号授予特定权限但生产环境一般不建议放开。更安全的做法是创建一个专用的只读视图暴露必要字段给开发人员而不是开放底层系统表。实用补丁如果只是想看当前用户自己的角色属性用SELECT * FROM pg_roles WHERE rolname current_user通常不需要访问pg_authid。4.6 一张常用查询速查表到这里信息量已经不小了我把最常用的需求浓缩成一张速查表方便你保存到自己的笔记里。排查需求推荐查询的系统表/视图查看所有数据库pg_database查看所有登录角色pg_user / pg_roles查看表结构pg_attribute pg_description查看索引定义pg_indexes / pg_index查看约束定义pg_constraint查看当前会话和慢查询pg_stat_activity查看数据库级别的统计pg_stat_database查看表级别的访问统计pg_stat_user_tables查看索引使用情况pg_stat_user_indexes查看表大小 Top Npg_class pg_namespace pg_relation_size()查看锁等待和阻塞链pg_locks pg_stat_activity查看配置参数pg_settings查看扩展插件pg_extension查看函数/存储过程pg_proc这张表我建议你在需要的时候看一眼比记一堆字段名好用。随着接触的查询场景增多你还能慢慢总结出更适合自己业务的“快速排查”版。4.7 独家避坑技巧多用视图和函数少手写底层 join我见过很多同事写系统表查询动辄 join 四五张表。其实 PostgreSQL 提供了大量现成的格式化函数和视图很多时候根本不用自己拼。比如pg_get_indexdef()、pg_get_constraintdef()、pg_get_viewdef()都能把一个对象的定义完整输出成 SQL 文本。这类函数不但省事还能避免你漏算某些构成索引的额外列如INCLUDE列比手动拼凑字段安全得多。另外想要查看当前数据库的“对象概况”可以直接用\d这个 psql 元命令它在内部其实也是查询系统表。比如\d public.order_info会展示字段、索引、触发器和外键信息非常全。系统表查询和\d命令搭配使用日常开发的效率能提升很多。结尾系统表这个东西你用得越多越觉得“早该系统学一遍”。我在实际排障中最深的体会是与其背命令不如理解它背后的数据组织方式——知道 OID 怎么关联、统计视图从哪来、锁信息怎么配对遇到新问题就不会慌。这篇内容里列出的查询都是在真实场景里打磨过的建议你拿到自己库里先跑一遍看看数据和业务能对上多少。另外PostgreSQL 的系统表不止这些pg_publication、pg_partitioned_table、pg_replication_slots这些在分区表、逻辑复制场景里也很有用等基础篇熟悉了后续可以继续往深了挖。
