1. 从 psql 出发给 pgtable 找一个顺手的信息入口第一次听到“pgtable”这个说法是在一次结构盘点的中途。当时要统计一个库里的几百张表图形客户端一页页翻得人发晕旁边的同事随口说了句“直接去问 pgtable 不就行了。”我愣了一下才明白他指的是 PostgreSQL 里一切跟表结构、表状态相关的系统信息。严格来说PG 里并没有一条命令叫 pgtable真正的东西是psql的元命令以及pg_tables、pg_class、pg_stat_user_tables这一整套系统视图和系统目录。但这名字很有画面感它把一堆散落在文档里的知识点压缩成了一个符号想知道表的情况就去查 pgtable。刚接触这套东西的人往往习惯打开图形客户端逐张表点开看。表少还好表一多就会非常痛苦。我印象很深的一次为了找一张缺少主键的表我在界面里挨个点点了几十张之后自己都忘了刚看过哪张。从那以后我就给自己定了一条规矩凡是和表有关的信息一律回到psql用元命令和 SQL 去查。别小看这一步转变命令行给出的结果是可以复制、可以排序、可以再加工的而图形界面里大多数时候只能看不能算。1.1 起步阶段最值得记住的三个元命令psql里与表直接相关的元命令日常使用频率最高的是这三个\dt、\d、\d。\dt列出当前数据库的所有表只显示 schema、表名、类型和 owner。它本质上是查询pg_tables视图的快捷方式胜在快适合刚进一个陌生库时先扫一遍全貌。\d 表名查看某张表的结构包括字段、类型、默认值、约束、索引、触发器。这是排查日常问题用得最多的一个。\d 表名在\d的基础上补充注释、表空间、IO 统计、最后 analyze 时间等信息。写变更方案前我习惯先执行它把完整结构带注释一起存档。\dt的输出很容易看懂但真正好用的地方是可以带通配符。比如我只想找名字带order的表可以执行\dt *order*这个通配符规则和 shell 接近表多的时候能快速缩小范围。另一个技巧是和\d配合先\dt找到候选表再逐个\d看细节。这一套组合在排查问题的时候非常稳定我到现在遇到陌生库还是这个步骤。1.2 三条能显著改善体验的 psql 设置很多人没意识到psql有几个设置项会直接改变查表体验。第一次执行\d看到结果被截断或者挤在一行里其实不是数据问题是显示环境没调好。我一般会先执行这三条\pset format wrapped \x auto \timing onformat wrapped让结果集中的长文本自动折行避免一列超长把整行撑出屏幕。x auto相当于自动扩展模式当一行的列太宽时自动把结果竖着展开每列一行。查表信息和查看锁等待时这个设置几乎能救命的。timing on会把每条 SQL 的执行时间打印在末尾。它在我下面要做“为什么这条查询慢”的分析时很有用能直观看到哪一步是耗时大头。\d一族命令虽然好用但它们给的是“此刻的表结构”没法回答这一类问题这个库里哪张表最大、哪张表一直没有被 vacuum、哪些表的外键没有配套索引。一旦需要把这些信息组合起来看就必须绕过快捷命令去查系统视图本身。这也就是 pgtable 从“命令”变成“一套方法论”的转折点。2. 三条查表路径pg_tables、pg_class、information_schema 如何分工PostgreSQL 的表信息查询路径其实有三条看起来互相重叠实际各有分工。不懂的人往往只记其中一个用到后面才发现短板。我这里把它们放在一起做一次对比。2.1 pg_tables最直观的“表面层”pg_tables是一个系统视图专门服务于“当前数据库里有哪些表”这个问题。它把pg_class、pg_namespace等底层目录做了封装屏蔽了 OID、系统内部标记等细节。我早期最常用的查询是SELECT * FROM pg_tables WHERE schemaname public;这张视图返回的列包括schemaname、tablename、tableowner、tablespace、hasindexes、hasrules、hastriggers等。对刚接触系统目录的人来说这是最好的起点因为它把“表”理解成了日常概念建在哪个 schema 下、叫什么名字、属于谁、有没有触发器。但用一段时间就会发现pg_tables给不了统计信息。它不告诉你表有多少行、占多大空间、上次 vacuum 是什么时候。想在一条 SQL 里同时拿出 “表名 行数 磁盘大小”pg_tables自身做不到需要回到底层目录去 join。2.2 pg_class一切表信息的地基pg_class是 PostgreSQL 里最核心的目录之一它不只存表还存索引、视图、序列、物化视图、外部表、分区表等对象的元数据。每一行代表一个“关系”relation我们平时说的表在 PG 内部其实就是relkind r的一行记录。这个转换带来的好处是你能用一套统一的目录体系理解所有对象。常用relkind值对照如下relkind含义常见对象类型r普通表CREATE TABLE 建出来的表p分区父表分区表的根节点v视图CREATE VIEWm物化视图CREATE MATERIALIZED VIEWf外部表CREATE FOREIGN TABLEi索引CREATE INDEXS序列CREATE SEQUENCE手工查pg_class时最关键的三步是用relnamespace关联出 schema用relowner关联出属主用relkind过滤出真正关心的对象类型。一个万能起点查询是这样SELECT n.nspname AS schema, c.relname AS table_name, c.relkind, pg_get_userbyid(c.relowner) AS owner, c.reltuples::bigint AS est_rows 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_% AND n.nspname information_schema ORDER BY n.nspname, c.relname;这里我用relkind IN (r, p)把普通表和分区父表一起捞出来同时过滤掉系统 schema。reltuples是优化器做估算时用的行数不是实时准确值但对找大体量级已经够了。真追求准确行数得用COUNT(*)或者等最新的ANALYZE跑完再看统计信息。2.3 information_schema给跨库迁移和报表准备的标准化方案information_schema.tables是 SQL 标准里的表信息视图PG 实现得也比较完整。它的优势是字段语义稳定TABLE_NAME、TABLE_TYPE、TABLE_SCHEMA这些名字在不同数据库里长得一样。我一般只在两种场景下用它一是写跨数据库兼容的脚本二是给不懂 PG 内部目录的人提供报表。但它有两个明显的短板。第一它不包含pg_class.reltuples这类统计字段拿不到行数预估值。第二查询路径更长性能上通常不如直接查系统目录尤其在对象非常多的大型实例里用information_schema做频繁查询会让人明显感到慢。这三者我现在的用法简单说就是对外交付用information_schema快速排查用pg_tables深度诊断和运维统计一律回pg_class打底。理解这一点之后看任何第三方工具生成的“表清单”就都不会懵了因为它们多半只是在包一层查询而已。3. 直接能抄的表信息 SQL 模板日常运维最常用的四组下面这四组 SQL 是我在多个环境里反复用过的每一条都解决一类高频问题。该换的表名、schema 名我会标注清楚可以直接抄走。3.1 表清单 估算行数 总大小 注释做表盘点时我最怕的就是只给一张表名的空列表。没有大小、没有行数、没有注释的表清单价值直接砍半。下面这条可以一次把信息带全SELECT n.nspname AS schema, c.relname AS table_name, c.reltuples::bigint AS est_rows, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, obj_description(c.oid) AS table_comment FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind r AND n.nspname public ORDER BY pg_total_relation_size(c.oid) DESC;注意pg_total_relation_size计算的是表本身加索引加 TOAST 的总占用所以用它排出来的顺序才是业务上关心的“物理存储占用”。如果只想看表数据本身的体积用pg_relation_size(c.oid)。obj_description取的是COMMENT ON TABLE写进去的注释没有注释会返回 NULL看起来有点刺眼但正好提醒我哪些表一直没补充负责人信息。3.2 主键、外键和配套索引的检查很多慢查询问题根子不在 SQL 本身而是外键列上缺索引。PostgreSQL 不像某些数据库会自动给外键列建索引如果漏建删除或更新父表时子表一侧就要做全表扫描。下面的查询能把一张表的主外键关系完整显示出来SELECT conrelid::regclass AS table_name, conname, pg_get_constraintdef(oid) AS constraint_def FROM pg_constraint WHERE connamespace public::regnamespace AND contype IN (p, f) ORDER BY conrelid::regclass::text, contype;pg_get_constraintdef是这里的关键函数它能把约束定义还原成接近原始 SQL 的样子比如PRIMARY KEY (id)或FOREIGN KEY (user_id) REFERENCES users(id)。我在做删表、改表结构之前会先跑一遍这个查询把所有跟目标表相关的外键约束都找出来防止ALTER TABLE ... DROP COLUMN因为依赖关系失败。配合外键检查的还有一条用于找出没有索引的外键列工作量稍大需要先用pg_constraint解析出外键字段再和pg_index比对。网上能搜到不少现成版本我建议直接把它存成一张视图每个月跑一次专门盯新增表有没有忘了建外键索引。3.3 找出长时间没被 vacuum 的表表膨胀是 PG 运维里最隐蔽的问题之一表现是数据量看着没涨多少磁盘占用却一直在涨。要快速判断哪些表已经很久没被清理可以用这一条SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_user_tables WHERE schemaname public AND (last_vacuum IS NULL OR last_autovacuum IS NULL) ORDER BY n_dead_tup DESC;看到last_vacuum和last_autovacuum都是 NULL说明从统计信息重置之后这张表就没被 vacuum 过。如果同时n_dead_tup还很高基本可以断定这张表正处于膨胀通道上。把这条查询定为周任务比等着实例磁盘报警要省心得多。3.4 查看最近被访问过的表反过来还有一种常见问题是“这么多表到底哪些还在用”。想安全下线一张表不能只看有没有人写代码还要看线上有没有查询访问。统计视图里有两个字段正好用得上SELECT schemaname, relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables WHERE schemaname public ORDER BY (seq_scan idx_scan) DESC;seq_scan是顺序扫描次数idx_scan是索引扫描次数。两者都极低的表要么是刚上线要么是基本没人用。观察两周数据再结合应用日志基本能确认一张表是否可以进入归档流程。需要提醒的是这个统计视图的数据是从统计信息重置或者实例启动后开始累计的所以“活跃度低”必须放在一个足够长的时间窗口里看才有意义。4. 用统计视图和锁视图给表做一次“体检”把表创建出来、写进数据只是第一步。一个表跑上几个月之后它到底健康不健康是胖了、瘦了还是被锁卡住了这些信息系统并不会直接告诉你。好在 PG 提供了一整套统计视图相当于给每张表配了一张病历卡。4.1 先看 pg_stat_user_tables 的数据结构pg_stat_user_tables是 PostgreSQL 统计模块里最核心的表级视图。它把我上面零散用到的字段集中到了一起顺序扫描数、索引扫描数、插入/更新/删除行数、存活元组数、死亡元组数、最后 vacuum 时间等。我把这个视图当作表的“体检报告首页”。至少要学会看这几个字段的组合关系n_live_tup和n_dead_tup活元组和死元组的大致数量顺序扫描越多的表这里越容易积累死元组。n_tup_hot_updHOT 更新次数数值高说明索引层面压力小这是好事。last_vacuum和last_analyze最近一次手动清理和分析的时间如果长期不变就该人工介入了。seq_scan和idx_scan判断一张表是否缺少合适索引的最快依据。idx_scan长期为 0 而seq_scan很高基本可以断定访问路径有问题。我常用的体检查询长这样SELECT schemaname, relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY (seq_scan idx_scan) DESC LIMIT 30;这个查询能快速在一堆表里挑出“最忙的几张”再针对它们做索引和清理检查。我曾经靠它在一周内定位到一张月增长几个 GB 的表它的n_dead_tup一直保持在几百万的量级而last_vacuum已经一个多月没更新了。原因很简单这张表上的频繁更新触发了更激进的写入但 autovacuum 的阈值还没到。手动跑一次VACUUM后磁盘占用肉眼可见地掉了下来。4.2 查锁等待定位“表为什么半天打不开”表体检的另一项是锁状态。表现很典型一条SELECT或者UPDATE卡在那里迟迟不返回应用日志里全是锁等待超时。这时候去翻应用代码没意义先查pg_locks和pg_stat_activity把一眼就能看出来的阻塞链找出来。我常用的锁排查 SQL 是SELECT a.pid, a.state, a.query, l.locktype, l.mode, l.granted, c.relname FROM pg_locks l JOIN pg_class c ON c.oid l.relation JOIN pg_stat_activity a ON a.pid l.pid WHERE l.locktype relation AND c.relname NOT LIKE pg_% ORDER BY l.granted, a.query_start;这个结果里granted为t的是持有锁的会话granted为f的则是在等待锁的会话。一次真实的排障经历是某张订单表每天凌晨做批量更新期间一个报表任务要读同一张表结果读请求一直等。通过上面这条 SQL我直接看到模式为 ShareLock的会话持有时间长且granted t而报表会话处于idle in transaction的等待状态。定位之后把批量更新的批次拆小并错开报表触发时间问题立刻消失。遇到更复杂的死锁PG 日志里会打印死锁检测报文里面包含两个进程的语句栈。别只盯着pg_locks看把log_lock_waits打开并设置合理的deadlock_timeout才是根治手段。4.3 膨胀了怎么进一步确认统计视图里n_dead_tup很高只能说明死元组多不能直接等同于“表一定膨胀”。要确认物理文件里确实有大量废弃空间需要借助pgstattuple扩展CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple(orders);这会真的去扫描表文件返回dead_tuple_percent、free_percent等字段。如果dead_tuple_percent超过 20%基本可以确定这张表需要一次真正的VACUUM FULL来把空间还给操作系统。注意VACUUM FULL会拿到表的排他锁期间表无法正常读写所以我一般安排在维护窗口做并且先通过pg_replication_slots和备库延迟检查确认没有复制任务正在追数据。5. 表管理里那些容易踩的坑和补救习惯最后这部分说的是我自己的教训以及旁观别人踩坑之后总结出来的经验。每一条都真实发生过代价有大有小但共同点是它们如果能被提前发现完全不用走到“加班恢复”那一步。5.1 随手DROP TABLE ... CASCADE的隐形杀伤力CASCADE这个关键字很顺手但它不只是“删一张表”。它会连带删除依赖这张表的视图、物化视图、外键约束、函数等对象。我有一次只是想把一张临时表删掉顺手加了CASCADE结果把基于它建的一堆分析视图全带走了。从那以后我定了一个原则非必要不加CASCADE非要加先执行pg_depend查询把依赖对象清单列出来再看一遍。分析视图本身就是可重建的实际损失不算大但重建成本和时间浪费完全没必要。5.2 外键列不建索引后果等到凌晨才爆发前面提过 PG 不会自动给外键列建索引这条光讲不够我说一个具体场景父表是用户表子表是订单表订单表有三四个外键都指向不同父表。平时单条插入没感觉。某天业务做数据归档在父表上做批量DELETE子表侧因为没有索引每一行删除都要全表扫描匹配直接拖垮数据库。事后补索引十分钟就解决但当晚已经熬掉三个小时。所以我现在每次上线新表都自动跑一遍外键索引检查。凡是建了外键约束的列默认补一个索引这应该成为 write model 表的默认规范。5.3 看到last_autovacuum是 NULL先别慌着手动 VACUUMpg_stat_user_tables.last_autovacuum为空看着很吓人但有两种可能一是表真的长期没被自动清理二是统计信息被重置过或者实例才刚启动不久。先看pg_stat_bgwriter或系统日志确认 autovacuum 进程本身是否正常再决定要不要手动干预。连续被误导两次之后我养成了同时查看pg_stat_database里autovacuum相关计数的习惯避免一看到 NULL 就陷入“救火模式”。5.4 把表信息查询沉淀成固定视图省掉重复劳动这些查询用熟了之后我并没有每次都敲一遍。更值得做的是挑出自己真正高频使用的几条创建成数据库内部的视图或者存成一个.sql脚本放进常用工具集。比如我常年保留一张排查用的视图CREATE OR REPLACE VIEW admin_table_health AS SELECT schemaname, relname, seq_scan, idx_scan, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;再把锁查询、外键索引检查也固化成脚本。这样每次新环境部署后跑一遍初始化脚本就拥有一套和管理界面差不多的表信息查询能力。谁再问“pgtable 到底怎么用”我直接甩这个脚本比从零讲一遍效率高得多。5.5 处理表信息时最怕把“预估”当“精确值”reltuples是估算值pg_stat_user_tables里的很多数字也是统计模块的近似值。把它们当成精确行数去写方案会闹笑话。我也犯过这种错拿reltuples判断某张表是空表结果清理了一堆以为没用的数据最后发现表里其实有几十万行只是统计信息早过期了。后来凡是涉及“到底有多少行”的决策要么COUNT(*)实测要么先手动ANALYZE把统计刷新一遍。估算用于排名和趋势判断实测用于决策执行这两者必须分开。把 pgtable 这套东西理解透之后再看 PostgreSQL 会比以前踏实很多。它不再是一堆漂浮在 SQL 之上的抽象概念而是一个能拆开、能量化、能体检的实体。我是建议每个和 PG 打交道的人都花点时间把这些系统视图当老朋友处熟平时可能没感觉关键时候它们能帮你在几分钟内回答别人几小时都查不出来的问题。