接手一个数据库第一件事绝不是急着写业务SQL而是先把这个库里到底有多少张表、每张表里是什么结构、哪些字段是关键的摸得门儿清。在PostgreSQL里这套“查户口”的活儿全靠一组叫pg_catalog的系统表来完成。很多刚接触PG的同学对pg_class、pg_attribute、pg_type这几个表望而生畏觉得JOIN起来太绕人。这几个月我在好几个项目里反复用这些表帮同事搭自动数据字典、排查大表膨胀、整理表结构清单越用越觉得这套东西值得写一篇完整的“抄作业”指南。今天这篇我就围绕pgtable这套元数据体系把查询思路、实战场景和踩坑点一次说清楚。1. 先搞清楚pgtable到底是什么PostgreSQL的“表档案室”很多人第一次听到“pgtable”以为它是一个独立的插件或者第三方工具其实不是。它更像是一个约定俗成的说法指的是PostgreSQL内部那一套用来记录表、字段、类型、索引等元数据的系统表集合。核心成员包括pg_class、pg_attribute、pg_type、pg_namespace、pg_namespace注意拼写、pg_constraint、pg_index等。这些表全部挂在pg_catalogschema下面普通用户不会直接改它们但通过SELECT查询是完全允许的——这也是我们做表结构分析的主要数据来源。1.1 一切的起点是pg_classpg_class是整张元数据网的中枢每一条记录代表一张表、一个索引、一个序列或者一个视图。真正决定一条记录是哪种对象类型的是relkind字段。这个字段的取值很有讲究relkind取值含义补充说明r普通表最常见的堆表我们说的pgtable主要就是它v视图查询时虚拟生成的物理上不存数据m物化视图有实体存储但查询逻辑是视图式的i索引依附于某张表不能独立存在S序列常用来生成自增主键tTOAST表存储大字段的溢出数据一般不直接操作c组合类型由CREATE TYPE生成的复合类型我接触过的不少项目里开发人员习惯性地用relkindr过滤普通表结果发现物化视图没被统计进去后面排查半天才想起来relkind里还有m这个取值。这个细节看似小影响却不小——在做表清单汇总时少统计了物化视图会导致存储容量的估算严重偏低。1.2 系统表之间怎么关联一张地图看懂看pg_class只是第一步真正要用起来必须把它跟其他几张表JOIN起来。最常用的关联路径有三条pg_class跟pg_namespace关联拿表所在的schema名。pg_namespace存的是命名空间也就是我们平时写的schema关联键是pg_class.relnamespace pg_namespace.oid。pg_class跟pg_attribute关联拿表的字段信息。pg_attribute里每条记录是一个字段关联键是pg_class.oid pg_attribute.attrelid再按attnum排序就能还原出建表时的字段顺序。pg_class跟pg_type关联拿字段的数据类型。pg_attribute.atttypid pg_type.oid拿到类型名后再用format_type()函数把长度、精度这类信息拼出来。刚开始用这套表的时候最直观的感受是“字段太多了根本记不住”。我自己的习惯是先只挑最关键的十来列比如relname、relkind、attname、atttypid、attnum、attnotnull、attstattarget等等遇到具体问题再逐步增加字段。不要试图一次把所有列都背下来按需查询才是最有效的学习方式。提示PostgreSQL的系统表结构在不同大版本之间基本稳定但偶尔会有细微调整。如果是长期维护某个老版本环境建议先执行\d pg_class看一下当前版本的字段情况避免根据记忆写出失效的SQL。2. 把表信息“捞”出来的核心SQL套路这一节是整篇博文里最值得直接复用的一部分。我会按“从易到难”的顺序给出三组可以直接复制运行的查询覆盖最常见的表结构分析需求。每一组SQL我都搭配一个真实的使用场景方便大家理解它的用途。2.1 先拿一张所有表的清单最简单的需求是把当前实例里所有普通表列出来附带所属schema、拥有者和大致行数估算。下面这个查询是我最常用的“起步SQL”SELECT n.nspname AS schema_name, c.relname AS table_name, pg_get_userbyid(c.relowner) AS table_owner, c.reltuples::bigint AS approx_rows, 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, m) -- 普通表 物化视图 AND n.nspname NOT LIKE pg_% -- 排除系统自带schema AND n.nspname information_schema ORDER BY n.nspname, c.relname;这里几个容易被忽略的点第一reltuples是上次ANALYZE之后的估算行数不是实时的。如果发现估算值和实际值差距巨大说明这张表很久没有做统计信息更新了这时候应该先去跑ANALYZE而不是怀疑SQL写错了。第二pg_total_relation_size(c.oid)统计的是表数据加索引、TOAST的总大小比pg_relation_size更全面。第三过滤条件里把pg_%和information_schema排除掉是因为系统表本身就存在pg_catalog和information_schema里如果不过滤清单里会混入大量我们并不关心的系统对象。我在某电商系统里用这个查询做过一次“全库表盘点”几百张表的总行数、总大小全部打印出来交给运维同事他们拿去跟监控平台的指标做了一次比对很快发现了几张“高增长但不怎么被查询”的冷表为后续归档方案提供了直接依据。2.2 拿到单张表的完整字段结构单表结构分析比全库清单更常用。比如某个业务方说“麻烦帮我看下订单表有哪些字段”你用\d当然能看但这只能看一张表而且不方便批量导出。用系统表查询可以一次性把字段名、类型、是否非空、默认值、注释全部拉出来SELECT a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull AS is_not_null, COALESCE(pg_get_expr(d.adbin, d.adrelid), ) AS default_value, col_description(a.attrelid, a.attnum) AS column_comment FROM pg_attribute a LEFT JOIN pg_attrdef d ON d.adrelid a.attrelid AND d.adnum a.attnum WHERE a.attrelid public.orders::regclass AND a.attnum 0 AND NOT a.attisdropped ORDER BY a.attnum;public.orders::regclass这个写法是PG里一个非常好用的语法糖它能把表名转换成对应的OID省去了先查pg_class再关联的步骤。如果表名不存在会直接报错也算一种简单的存在性校验。attnum 0的过滤条件同样关键。pg_attribute里不仅有真正的字段还有捏造的虚拟字段比如tableoid、xmin、ctid这些系统列它们的attnum小于等于0必须排除掉否则结果里会多出七八行看起来很奇怪的记录。attisdropped标记字段是否被DROP对于被删除过字段的表如果不过滤会带出一些“幽灵字段”干扰后续的数据读写映射。字段注释的获取方式也有讲究。col_description(a.attrelid, a.attnum)从系统表里读注释比走information_schema.columns的udt_name更轻量。如果一张表的注释远远多于字段说明这些字段是被人力维护过的——这类表往往接入过某个数据字典工具结构准确性通常更高。2.3 把索引、主键、外键一起拉出来字段结构只是表的一部分索引和约束信息同样重要。下面这条SQL把某张表的索引信息一次性汇总出来包括索引名称、索引字段、是否唯一、是否主键SELECT ic.relname AS index_name, i.indisunique AS is_unique, i.indisprimary AS is_primary, pg_get_indexdef(i.indexrelid) AS index_definition FROM pg_index i JOIN pg_class ic ON ic.oid i.indexrelid WHERE i.indrelid public.orders::regclass ORDER BY i.indisprimary DESC, ic.relname;pg_get_indexdef()函数会把索引的完整定义拼出来比如CREATE UNIQUE INDEX orders_pkey ON public.orders USING btree (id)这段字符串拿来直接作为变更脚本的一部分都行。索引字段的顺序也包含在这个定义里不需要再去解析indkey数组大大简化了查询逻辑。主键的识别逻辑需要注意indisprimary为true的索引在PG里只会出现在主键约束上它跟UNIQUE约束的区别在于主键索引的非NULL约束并不体现在pg_index里而是体现在pg_attribute.attnotnull字段上。所以在做“哪些字段是非空”的分析时主键字段的attnotnull也一定是true。注意如果一张表的数据量很大索引数量也很多pg_get_indexdef会生成很大的字符串。在批量导出数据字典时建议不要一股脑把全部索引定义打印在文档里而是先读索引名再按需展开定义避免文档难读。3. 实战场景我把pgtable元数据用在了三个高频任务上光看查询语句还是不够的更重要的是理解这些语句能解决什么实际问题。我挑三个我真实落地过的场景来讲这些代码我都是放在自动化脚本里直接跑的不需要人工干预。3.1 自动生成全库数据字典数据字典这东西很多公司有但大多是从PDM工具导出的跟生产库的真实结构经常对不上。我做过一个定期跑的脚本每周自动生成一份全库的数据字典直接输出成Markdown格式放到内部Wiki上。核心逻辑就是把第2节里的字段查询改成不带WHERE条件的全库版本然后按schema、表、字段三级汇总。具体实现思路不复杂先查所有schema和表名再循环每张表执行字段查询。如果表数量很多循环可能会有性能压力但实际测试下来几百张表的场景完全能接受。生成文档时我会把“有主键”“有注释”“无索引”这些属性做成标记列一眼就能看出哪些表的质量较差。在这个任务里我踩过一个有意思的坑全库字段查询如果不限制attnum 0系统字段如ctid也会进字典文档导致每张表都多出几行无效记录。第一次生成的文档被同事嘲笑了说“你的字典连ctid都录进去了”。从那以后我再没忘记过这个过滤条件。3.2 定位大表和膨胀表数据库变慢很多时候不是机器性能不够而是某张表的膨胀太严重。PG的MVCC机制决定了被更新过的旧行不会立即清理需要等VACUUM回收。膨胀率一高磁盘IO和查询扫描成本都会飙升。用元数据可以快速排查膨胀严重的表。核心指标是pg_stat_user_tables视图里的n_dead_tup死元组数量和n_live_tup活元组数量。结合pg_class.relpages可以算出表的物理页数进而判断它跟实际数据量是否匹配SELECT schemaname, relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(n_dead_tup * 100.0 / n_live_tup, 2) ELSE 0 END AS dead_ratio, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables WHERE n_dead_tup 1000 ORDER BY n_dead_tup DESC LIMIT 20;实际排查中发现last_autovacuum为NULL的表大概率是“从来没被自动清理过”常见原因要么是autovacuum被手动关闭要么是表太大导致清理进度异常。对于这些表我会先看它的膨胀率如果超过30%再考虑手动VACUUM FULL。但这里要提醒一句VACUUM FULL会获取表级锁大表执行期间业务写入会受阻一定要安排在业务低峰期操作。3.3 找出没有被索引的“裸表”有些业务表建完之后完全没有索引查询全靠全表扫描。在数据量小的时候没问题一旦数据量上来慢查询就会开始拖垮数据库。用系统表可以非常直观地找出这类表SELECT n.nspname AS schema_name, c.relname AS table_name, c.reltuples::bigint AS approx_rows FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace LEFT JOIN pg_index i ON i.indrelid c.oid WHERE c.relkind r AND i.indexrelid IS NULL AND n.nspname NOT LIKE pg_% AND n.nspname information_schema ORDER BY c.reltuples DESC;LEFT JOIN配合IS NULL是经典的“找没有关联记录”的写法。运行结果里如果有一张大表排在前面而它上面连一个索引都没有那基本可以断定它会是个隐患。某次在给一个内部系统做巡检时我就是靠这个SQL发现了一张月增量超过百万行的日志表连一个索引都没有。后来问业务方他们说当初只是临时记录数据用的没人想到会用SQL去查它。加了一个查询频率最高的时间字段做索引后慢查询直接从几秒降到了几十毫秒。这个排查思路几乎不用成本却能带来很直接的收益。4. 常见坑与排查技巧我记录下来的DEBUG日志用pgtable查元数据逻辑本身不复杂但实际操作中总有些环境差异和权限问题会绊人一下。这一节我整理几个高频问题附上排查思路。4.1 查出来的结果为什么是空的最常见的原因有两个权限不足、schema过滤条件写错。PostgreSQL对系统表的权限管控比较严格。普通用户只能看到自己有权限的对象如果一个schema连USAGE权限都没有那么即使pg_class里有记录查询JOIN到pg_namespace后也不会返回任何结果。这种情况下最直接的排查方式是检查当前用户的角色SELECT current_user, current_setting(search_path)。如果是普通业务账号建议用超级用户账号执行元数据查询或者在授权时给业务账号加上GRANT USAGE ON SCHEMA pg_catalog TO xxx。注意pg_catalog默认对所有用户开放只读但pg_class的可见性依然受对象权限影响。另一个常见原因是search_path没有设置好。直接写c.relname而不带schema前缀时PG会根据search_path去解析表名如果目标表在pg_catalog之外的schema里而search_path又没有包含它查询可能会把pg_catalog.pg_class和同名的其他表混淆。稳妥的做法是在查询里显式写上schema前缀或者用正则表达式过滤结果时多注意schema的拼写。4.2 视图和物化视图的边界问题前面提到过relkind取值v和m分别代表视图和物化视图。做“全库表清单”时如果把这三个值混在一起统计表格数量会虚高。有些团队习惯把报表用的视图跟业务表放在同一个schema里统计出来的东西如果直接拿去规划备份策略备份体积会被严重高估。排查这个问题的方法很简单查询时把relkind放到WHERE条件里分几个类别分别统计即可。更细致的做法是用CASE WHEN把relkind翻译成人话输出结果里加一列类别名一眼就知道每个对象是什么类型。4.3 表名大小写和特殊字符的坑PostgreSQL对标识符的处理规则是不带引号的表名会被折叠成小写。如果建表时用了Orders这种带引号的方式那么实际存储的名字就是大写开头的Orders。在系统表里查询时必须用单引号包裹精确大小写-- 正确写法 SELECT * FROM pg_class WHERE relname Orders; -- 错误写法查不到任何记录 SELECT * FROM pg_class WHERE relname orders;如果表名还包含特殊字符比如user_order_2024_01这类带数字的使用::regclass转换时要注意转义。更稳妥的做法是先查pg_class拿到OID再用OID去关联其他表避免字符串解析带来的问题。这类大小写问题我也踩过。某个项目里有一位同事用存储过程动态建表表名里混了大小写。后来做数据字典脚本时用LOWER(relname)做统一处理才把问题掩盖住。但这样做只能用于展示一旦要生成实际的ALTER语句大小写错误会导致执行失败。4.4 处理历史遗留的“脏数据”pg_attribute里有一种比较隐蔽的情况字段曾经存在后来被DROP掉但pg_attribute里仍保留一条记录只是attisdropped变成了true。这些幽灵字段不仅干扰查询结果还会让format_type()函数返回空值。处理方式我已经在前面提过务必加上NOT a.attisdropped过滤。还有一种“脏数据”是重复的约束名。不同schema下两张表可能有相同名字的约束关联查询时如果只按约束名做JOIN会出现笛卡尔积。解决思路是多加一个constraint_namespace作为关联维度不要偷懒只关联单个字段。5. 从一张表出发的小扩展定时巡检和告警脚本说完了单个查询我再提供一个可以直接落地成脚本的完整方案。很多运维巡检工作本质上就是定时跑一组元数据SQL把结果跟阈值对比然后决定是否告警。这里说的不是复杂运维平台而是一个用Shell加psql就够用的轻量方案。假设我要实现一个每日巡检任务找出所有“超过7天没有autovacuum”的大表如果膨胀率超过20%就发送告警消息。核心脚本可以这样写#!/bin/bash DB_NAMEmydb DB_HOST127.0.0.1 THRESHOLD20 psql -h $DB_HOST -d $DB_NAME -Atc SELECT schemaname || . || relname || : dead_ratio || round(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 2) FROM pg_stat_user_tables WHERE last_autovacuum IS NULL OR last_autovacuum now() - interval 7 days | while read line; do # 这里可以把$line发给告警渠道比如企业微信机器人、钉钉机器人 echo [ALERT] $line done这个脚本有几个小细节值得说明第一-Atc参数表示输出无表头、无对齐的纯文本方便脚本解析。第二NULLIF(n_live_tup, 0)可以防止除数为零报错这是写SQL时很容易遗漏的边界条件。第三把告警逻辑放在while read循环里方便做多行的逐条处理。当然pg_stat_user_tables视图只是“当前统计信息”的入口。如果库负载很高做全库巡检时统计信息视图会有一定开销建议在业务低峰期调度比如凌晨两点到六点之间。真实的告警脚本还会加上时间窗口判断避免一打开电脑就被满屏告警淹没。6. 我的体会与后续可以怎么扩展写这篇博文时我把这几年用pgtable元数据的经验重新梳理了一遍。最大的体会是系统表看起来复杂但只要分清主次先从pg_class入手再逐步关联其他表使用门槛其实很低。真正有难度的是想清楚自己要解决什么问题——是统计大表是看字段缺口还是巡检膨胀目标明确之后SQL组合自然就出来了。不要指望一篇文档能背下所有系统表字段更高效的方式是“按需查询边查边查文档”用多了自然就记住了。后续想在这个方向更进一步的读者可以关注几个扩展点。一是把元数据查询封装成函数做成一个内部工具库下次遇到同样需求直接调用。二是结合定时任务自动生成数据字典发布到内部知识库让开发人员随时能看到最新表结构省去反复问DBA的人工沟通成本。三是把元数据跟业务元数据打通比如字段注释里嵌入业务负责人用来做数据治理的责任认领。这条路走下去pgtable就不只是“查表的工具”而是一套数据治理体系的基础底盘了。最后分享一个我一直用的习惯凡是用到pg_class和pg_attribute的查询我都会先加一层视图或CTE把OID字段统一处理好再往后JOIN。这样既方便调试也方便其他同事接手这些SQL避免一长串JOIN让人看得头大。数据库的世界里元数据永远是最有规律也最容易被人忽略的宝藏多花点时间在里面回报往往比想象中更高。