做我这一行的人大概都有过类似的体验深夜被监控告警吵醒磁盘用量亮红灯业务方在群里催而你连“哪个库最大、哪张表最大”都还只能现场翻文档。我到现在还留着一个习惯——接手任何一套 PostgreSQL 环境第一件事不是调参数而是先把全实例的空间分布跑一遍。因为这决定了后面所有工作容量规划、迁移成本、慢查询排查、甚至备份策略全都建立在“空间到底被谁占了”这个基础上。这篇文章就把 PostgreSQL 里查看数据库和表占用空间大小的整套方法整理出来从内置函数的统计口径讲起给出四级排名 SQL数据库、模式、表、索引再深入 TOAST、膨胀检测这些进阶内容最后聊聊我在生产环境踩过的坑。刚入门的开发看了能马上用老手也能从坑清单里检查一下自己有没有漏东西。1. 先搞清楚“大小”的统计口径不然 SQL 会骗你1.1 最常用的几个尺寸函数各算各的账PostgreSQL 内置了一组空间统计函数输入一个对象 identifier通常写法是schema.table::regclass返回以字节为单位的bigint。最常用的有这么几个pg_relation_size(oid)只统计关系主分叉main fork对普通表来说就是“表数据本身”不包含索引也不包含 TOAST。pg_table_size(oid)统计表的完整空间包含 TOAST、TOAST 索引、空闲空间映射fsm和可见性映射vm但不包含普通索引。pg_total_relation_size(oid)最常用的“总大小”等于表数据 全部索引 TOAST基本可以理解为删掉这张表之后能释放的空间量级。pg_indexes_size(oid)某张表上所有索引占用的总大小。pg_database_size(oid)整个数据库的总大小。用公式拆开看就清楚了pg_total_relation_size pg_table_size pg_indexes_size pg_table_size 主表数据 TOAST 数据 TOAST 索引 fsm vm很多新手直接拿pg_relation_size当“表大小”用发现和pg_total_relation_size差了一大截就怀疑是不是有脏数据或者数据文件损坏。其实大概率只是你的查询漏掉了索引和 TOAST。把统计边界先定清楚后面的所有查询才不会被误导。1.2 一张表不止一个文件fork 机制决定了统计方式深入一点看PostgreSQL 在磁盘上并不是为每张表只存一个文件。一张普通表大概会对应这样几类文件main主数据文件存放行的主体内容。fsm空闲空间映射记录哪些页面还有空位方便后续插入时复用。vm可见性映射帮助索引扫描跳过没有变化的数据页。toast当某一行太大、装不下时大字段会被压缩并转移到附属表里这个附属表就是 TOAST。fsm 和 vm 文件通常非常小但它们真实存在也会计入前面几个函数的返回值。以后你进数据目录做du -sh排查时看到一堆带着_fsm、_vm后缀的文件不要慌它们是表结构的一部分不是垃圾文件。理解 fork 的存在才能解释为什么pg_relation_size(main)和磁盘上看到的文件大小对不上。1.3 系统目录查空间的底座执行空间查询时我们离不开几个系统表。pg_class存的是所有表、索引、序列、TOAST 表的元数据pg_namespace存 schema 信息pg_database存数据库列表。里面有个字段relkind必须重视r普通表m物化视图i索引tTOAST 表S序列我见过一份别人写的 schema 空间聚合 SQL没有过滤relkind结果把索引、序列全算进去某个 schema 的“总大小”虚高了几十个 GB排查了半天才发现是统计口径错了。所以凡是做模式级或实例级聚合第一步永远是过滤掉非表对象。2. 直接抄作业数据库、模式、表、索引四级空间排名 SQL2.1 第一级整个实例里哪个数据库最占地方想知道一台 PostgreSQL 服务器上所有数据库的大小这条 SQL 就够了SELECT datname, pg_size_pretty(pg_database_size(oid)) AS db_size FROM pg_database ORDER BY pg_database_size(oid) DESC;pg_size_pretty会把字节数格式化成可读的kB、MB、GB否则返回的数字大到你懒得看。注意这条查询会连模板库一起列出来template0、template1通常很小不影响判断。如果实例上千库重点看最上面几位即可。某次我在一个压测环境里跑这条发现光一个模拟业务的库就占了全实例 80% 的空间后面所有优化都集中到那个库上了。2.2 第二级库确定后定位是哪个业务 schema库里面往往有多个 schema把 schema 级空间聚合出来能直接帮你划清责任范围SELECT n.nspname AS schema_name, pg_size_pretty(SUM(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_% GROUP BY n.nspname ORDER BY SUM(pg_total_relation_size(c.oid)) DESC;这里过滤relkind IN (r, m)就是把普通表和物化视图纳入统计排除索引、序列这些派生对象。排除pg_%是去掉系统 schema避免把内置对象算进业务统计里。实际跑出来的结果经常很有戏剧性你以为某个模块最肥结果一查真正的大头在另一个没人维护的临时 schema 里。2.3 第三级当前库里最大的 20 张表锁定 schema 之后就要看具体的表。我最常用的表排序 SQL 长这样SELECT schemaname, relname, pg_size_pretty(pg_relation_size(relid)) AS heap_size, pg_size_pretty(pg_table_size(relid)) AS table_with_toast, pg_size_pretty(pg_indexes_size(relid)) AS index_size, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;pg_stat_user_tables视图能让你直接拿到relid、schemaname、relname省得自己再 JOINpg_class和pg_namespace。LIMIT 20是因为大部分环境里空间都集中在少数的几张表上看前 20 足够。注意我把 heap、索引、总大小三列都列出来了因为只看总大小会掩盖一个事实有些表本身不大但索引失控膨胀得比表还大。这种情况在后面专门讲。2.4 第四级索引有时比表还能吃索引是空间问题里最容易被忽视的一环。单独给索引排个名你会发现不少惊喜SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 20;有一种典型场景某张业务表只有几百万行按理说不算大但因为频繁更新某几个字段索引反复分裂、膨胀索引文件比表数据文件大好几倍。用上面这条 SQL 一眼就能抓出来。除此之外重复索引也是常见问题——同一组字段建了索引又建唯一约束白白多占一份空间。索引排名列表拉出来之后顺手检查一下有没有重复定义是很划算的事。2.5 一张直接的“空间构成表”一次看清一张表的钱花在哪如果你只想单独看某张表的构成可以这样 SQLSELECT pg_size_pretty(pg_relation_size(c.oid)) AS heap_size, pg_size_pretty(pg_table_size(c.oid) - pg_relation_size(c.oid)) AS toast_and_map_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.oid public.big_table::regclass;toast_and_map_size列是用pg_table_size减去pg_relation_size得到的约等于 TOAST 相关占用加上少量的 fsm/vm。如果这列数值异常高说明你表里可能有大量大字段或者 TOAST 表长期没被回收。这个 SQL 在评估“我到底要不要拆表”“要不要把大字段挪到对象存储”时特别有用。3. TOAST、死元组与膨胀为什么删了一堆数据磁盘却没变小3.1 大字段去哪了TOAST 机制详解PostgreSQL 的 TOASTThe Oversized-Attribute Storage Technique机制专门处理超宽字段。通俗讲当一行数据的某个字段太大通常是 text、bytea、jsonb、几何类型这类可变长类型接近大约 2KB 阈值时PostgreSQL 会尝试压缩字段值如果压缩后还是放不下就把字段拆分后放进一张附属表这张附属表就是前面反复提到的 TOAST 表。你查询pg_table_size时TOAST 的大小已经被算进去了所以它不算“隐藏空间”。但问题在于很多人不知道它的存在看到主表不大就以为一切都正常。TOAST 真正引发麻烦的情况有两种一是某个表里有大量的高压缩率字段TOAST 表本身增长极快二是 TOAST 表对应的索引膨胀。想找出 TOAST 占用最大的表可以执行SELECT c.oid::regclass AS table_name, pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size FROM pg_class c WHERE c.reltoastrelid 0 ORDER BY pg_relation_size(c.reltoastrelid) DESC LIMIT 20;如果某张表的 TOAST 大小接近甚至超过主表你就应该认真考虑这个字段真的必须存在关系库里吗是不是可以用外部对象存储我在一个项目里就见过把用户操作日志的整段 JSON 直接塞进表里结果一张 200GB 的表TOAST 占了 150GB后面改成只存摘要和对象地址空间立刻降了下来。3.2 死元组膨胀DELETE 和 UPDATE 不删旧数据PostgreSQL 的 MVCC 机制决定了DELETE或UPDATE并不会立即物理删除旧版本数据而是把旧版本标记为“死元组”留待之后的 VACUUM 清理。因此一张表即使删了大半的数据只要 VACUUM 没跑磁盘空间就不会还给你表文件依然维持原来的体积。索引膨胀的情况比表更严重。表数据页回收后页面里的空位可以被后续插入复用但索引页的空位通常更碎片化而且 autovacuum 对索引的处理相对保守。所以频繁更新的表即使行数不变索引也可能膨胀到初始大小的一倍以上。这也是为什么我一直强调判断空间问题不能只看总行数和表文件大小要看死元组比例和实际空闲空间。3.3 磁盘空间什么时候才会真正归还给操作系统很多人疑惑autovacuum 已经跑过了为什么磁盘占用还是没降原因是普通 VACUUM 只做“空间内回收”——把死元组清理掉让页面可以重复使用但文件长度不会收缩空间并不会还给操作系统。要让表文件真正变小手段包括VACUUM FULL重写整张表把现有元组密集排列到新文件中旧文件删除。代价是过程中需要约等于表大小的额外磁盘空间并且会持有排他锁阻塞读写。TRUNCATE直接截断表瞬间释放空间但代价是全表清空需谨慎。DROP TABLE/DROP INDEX删除对象空间立即归还。CLUSTER按指定索引重排表也能压缩空间但同样需要排他锁。明白了这点你就能理解为什么在线业务里“VACUUM FULL 是最后手段”。空间告警时优先评估哪些表可以删除或归档而不是无脑对最大表执行 VACUUM FULL。我在生产环境里见过有人对大表跑 VACUUM FULL结果磁盘在高峰期直接写满反而把数据库弄挂了一次。4. 用 pgstattuple 和 pg_freespacemap 做精细体检算清膨胀率再动手4.1 pgstattuple统计表的真实文件使用情况前面的查询只能告诉你“表多大”无法告诉你“表里有多少空间其实是死元组或者空洞”。要量化膨胀率需要用到 contrib 扩展pgstattupleCREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple(public.my_table::regclass);返回结果里几个关键字段值得盯住table_len表文件总字节数。tuple_len存活元组实际占用的字节数。dead_tuple_len死元组占用的字节数。free_space页面里剩余的空闲字节数。tuple_percent存活元组占表文件的比例。如果一张表table_len显示 100GB但tuple_percent只有 40%说明里面超过一半的空间不是可用数据。这时再结合业务判断如果是高频更新表死元组多说明 autovacuum 没跟上如果free_space很高但死元组不高可能是页面碎片化严重。有一点必须提醒pgstattuple是扫描整个表文件做统计的大表上跑一次可能相当耗时也会产生额外 IO。建议先在非高峰时段、挑几张嫌疑最大的表跑不要一上来就全库排查。4.2 大表场景下的快速近似版本对于几十 GB 甚至上百 GB 的大表全量扫描太奢侈。官方还提供了近似版本pgstattuple_approxSELECT * FROM pgstattuple_approx(public.my_table::regclass);它基于采样和页级统计速度会快很多结果有一定误差但对于判断“要不要做 VACUUM FULL / 重新规划表结构”这种决策精度完全够用。我的使用习惯是先用pg_total_relation_size快速排名锁定额外可疑的大表再用pgstattuple_approx估一波膨胀只有对确定要处理的小表才跑完整版。4.3 用 pg_freespacemap 看空闲页面分布另一个有用的扩展是pg_freespacemap它读取表或索引的空闲空间映射CREATE EXTENSION IF NOT EXISTS pg_freespacemap; SELECT count(*) AS pages, pg_size_pretty(sum(avail)::bigint) AS free_bytes FROM pg_freespacemap(public.my_table::regclass);这能估算一张表里有多少空白页可供复用。如果free_bytes很大说明 autovacuum 已经清理了不少死元组空间其实可复用只是因为并发和碎片导致不能完全利用。这种情况下与其冒险 VACUUM FULL不如先调整 autovacuum 参数或者等业务低峰期再做整理。4.4 判断要不要 VACUUM FULL我的经验阈值关于“多大膨胀才值得 VACUUM FULL”没有绝对标准但我个人会参考几条经验线tuple_percent低于 50%同时表文件超过 10GB值得规划一次整理。dead_tuple_percent持续高位说明 autovacuum 触发阈值可能设置不合理。索引文件通过pgstatindex检查如果某个索引的dead_tuple_percent长期超过 30%优先重建索引比全表 VACUUM FULL 便宜得多。执行前必须评估磁盘剩余空间能否容纳重写副本锁窗口有没有业务容忍度。记住VACUUM FULL 不是日常维护手段而是“体检发现问题后做一次外科手术”的思路。日常真正要依赖的是让 autovacuum 和工作参数处于健康状态。5. 我在生产库上踩过的坑六个最容易出问题的细节5.1 没加 schema 前缀查错对象还不自知pg_relation_size(some_table)这类写法依赖search_path的解析。如果当前会话的search_path同时指向多个 schema表名有歧义它找到的可能不是你脑子里想的那张表。我踩过一次项目里有测试库和正式库同名表在测试会话里没消除歧义查询结果偏小差点误判正式表的空间。后来所有脚本一律写schema.table::regclass彻底杜绝这个问题。5.2 权限不足导致大小统计不完整空间统计函数并不是对任何角色都返回完整数据。普通用户调用pg_database_size时如果对某个数据库没有 CONNECT 权限函数会返回报错或者看不到对应库的大小。pg_total_relation_size类似对没有相应权限的表也不会给你完整结果。所以遇到“某个库大小始终显示为 0 或报 permission denied”的情况先检查角色权限别急着怪查询语句。5.3 把 reltuples 当成实时行数pg_class.reltuples只是上一次 VACUUM 或 ANALYZE 时的估计值不是实时行数。有人写统计脚本时用reltuples除以表大小去算“平均行宽”结果 DELETE 大量数据后根本没更新数值严重失真。需要行数时用COUNT(*)或者容忍 VIEW 里的估计名不要用reltuples。5.4 数据库大小不含 WAL、归档和日志pg_database_size统计的是数据库对象占用的空间不包含pg_wal目录下的 WAL 文件也不包含归档目录、数据库错误日志。磁盘告警时如果pg_database_size排名表看起来一切正常但磁盘还是满了请立刻检查pg_wal目录的大小以及是否有归档堆积。有时候真正吃满磁盘的不是数据而是写不出去的 WAL这种情况靠查表大小根本查不出来。5.5 单位陷阱byte 数字大得离谱格式化后才是人话所有返回字节数。如果你直接拿原始数字看很容易把几百 GB 的表误读成“几十万 KB”。我习惯所有脚本统一用pg_size_pretty包裹汇总展示但底层排序用原始字节数不然格式化后的字符串排序会出问题——10 GB和9 GB按文本排就错了。所以规范是展示格式化排序用函数原始返回值。5.6 只盯表大小忘了索引和 TOAST 才是大头最有代表性的一次排障经历业务反馈“数据库膨胀到 500GB”我按表排名一看最大表不过 80GB按理对不上。后来把索引排名和 TOAST 排名拉出来才发现两张 20GB 的表各自挂了 150GB 以上的膨胀索引再加上一张大字段日志表的 TOAST 占掉 150GB。三部分叠起来正好补上了“消失”的空间。从那以后我的空间排查 SQL 永远把 heap、index、toast 三列同时展示不再单看表总大小。最后再分享一个小习惯我每周会固定跑一次四级空间排名把结果存下来做趋势对比。空间不是查一次就完的静态指标它像水位一样不断变化长期记录之后扩容判断、清理节奏都能提前规划真到告警那天也不用慌。这套查询逻辑你熟练了以后建议把它们整理成自己常用的运维脚本遇到在线问题的时候直接调用就好。