PostgreSQL空间占用排查：用内置函数快速查看库表大小

接手过PostgreSQL数据库的人迟早都会遇到同一个问题业务还没跑起来磁盘先告警了或者一张几千万行的大表看着逻辑行数不算多一统计发现堆加索引占了十几个G。这时候你就得搞清楚数据库里每个库、每张表到底把空间花到哪去了。这篇文章不聊怎么安装PostgreSQL也不讨论调优参数专门讲怎么看数据库和表的占用空间大小——用到的都是PG自带的系统函数pg_database_size、pg_relation_size、pg_total_relation_size以及配套的pg_size_pretty。无论你是刚接手PG的运维、后端开发还是要做容量规划和存储瓶颈排查的DBA把这几个函数加SQL组合理解透就能在几分钟内把空间盘清楚。1. 为什么一张“不大”的表磁盘里却占了好几个G先说清PG的空间构成很多人第一次用PostgreSQL查表大小都会懵我明明只存了200万行每行也就一两百字节用脚趾头算撑死几百MB为什么pg_total_relation_size查出来是2GB这里面藏着PG存储模型的两个关键点一张表物理上不只是“一张表”而且PG的MVCC机制会留下大量你看不见的历史版本。1.1 一个表在磁盘上究竟“拆”成了哪几份PostgreSQL里每个表对象在磁盘$PGDATA/base/数据库OID/目录下对应着一个或多个实际数据文件。普通表的主文件叫“堆表文件”所有行数据按8KB页面的格式存放在里面。如果表很大PG会按1GB一个文件来切分超过1GB后自动出现.rel2、.rel3这样的后续文件所以你在系统层面看到一个大表往往是“一堆文件”。除了主堆文件每个表还至少跟着两个附加文件一个是空闲空间映射文件文件名带_fsm记录每个页面里有多少可复用空间供INSERT寻找合适页面一个是可见性映射文件文件名带_vm标记哪些页面的所有行对所有事务都可见用来加速CLOUDSCAN? 不是加速INDEX ONLY SCAN和VACUUM。这两个文件通常只有很小的固定大小但它们是物理存在。然后还有两类重量级“附属品”TOAST表和索引。TOAST表存放从主表溢出的“大字段”后面专门讲。索引是一个完全独立的数据文件按照B-Tree等结构存储键值和行指针索引大小可能超过堆表本身。所以一张表的全部物理占用正确的公式是总大小 主堆文件 TOAST表及其索引 该表所有索引 FSM VM对应到PG内置函数这个关系被封装成了pg_total_relation_size它是pg_table_size和pg_indexes_size之和。pg_table_size又包含了主堆、TOAST堆、TOAST索引以及附加文件。换句话说你如果只查pg_relation_size那看到的是“纯主堆”不含索引和TOAST这最容易造成误判。1.2 MVCC与TOAST空间比你想象大两个隐含原因第一个原因是MVCC。PostgreSQL的UPDATE不是“原地修改”而是“逻辑上删除旧版本、插入新版本”。被UPDATE覆盖的旧版本行会变成死元组dead tuple仍然占据页面的物理空间直到VACUUM确认没有任何活跃事务需要它之后才会标记为可复用。DELETE也一样删除动作只对行打删除标记空间不会立即还给文件系统。换句话说一张频繁增删改的表物理文件里的“死数据”可能比有效数据还多。这就是所谓“表膨胀”。业务上你看到表里只有3000万行可表占用了80GBVACUUM回收后可能只有20GB。要是长期没有VACUUM又遇到大量UPDATE膨胀就更夸张。所以查空间时不能只记录一个“大小数值”还要结合死元组数量判断这张表是不是“虚胖”。第二个原因是TOAST机制。PostgreSQL单行数据如果太大大约是超过2KB并且在压缩后仍无法塞进约2/3的8KB页面会把大的变长字段text、jsonb、bytea这类拆成小块放到附属的TOAST表里。TOAST表也是独立物理文件同样也可能有索引文件、同样会膨胀。很多人在SQL里只查主表大小忽略了TOAST占的空间结果发现一个大jsonb字段多的表怎么查都不对——其实大头在TOAST里。2. 核心函数PG查库和表大小全靠这几个内置函数PostgreSQL和MySQL不太一样MySQL习惯直接查information_schema.tables里的data_length、index_lengthPG里没有这种字段。PG提供了一套尺寸函数体系参数可以传表名、数据库名、甚至OID返回值是以字节为单位的bigint再通过pg_size_pretty格式化成人类可读的字符串。2.1 五个核心函数一张表看懂先把最常用的函数整理一下后续所有SQL都围绕它们展开函数输入对象返回内容是否包含索引是否包含TOASTpg_database_size(datname)数据库名/OID整个数据库所有对象的物理空间包含包含pg_relation_size(relation)表/索引名/OID主堆文件大小仅该对象自身不包含TOAST堆pg_table_size(relation)表名/OID表堆 TOAST堆 TOAST索引 FSM VM不包含普通索引包含pg_indexes_size(relation)表名/OID该表上所有索引总大小包含不涉及pg_total_relation_size(relation)表名/OIDpg_table_size pg_indexes_size包含包含所以日常最推荐用的就是pg_total_relation_size它回答的是“这张表全家桶占了多少空间”。而pg_relation_size更常用于单看某个索引或某个数据分片。最后一个格式化函数pg_size_pretty(bigint)接收字节数输出5432 kB或1 GB这种字符串不加它看原始字节数会看瞎眼。2.2 基本用法与参数细节最简单的用法直接在psql里执行SELECT pg_size_pretty(pg_database_size(postgres)); SELECT pg_size_pretty(pg_total_relation_size(orders)); SELECT pg_size_pretty(pg_indexes_size(orders)); SELECT pg_size_pretty(pg_relation_size(orders));几个容易踩的细节参数是字符串时PG会自动把它转成regclass类型。如果表不在当前search_path下要写带schema限定的名字例如public.orders或ods.user_login_log。表名如果大小写混合或含特殊字符用双引号OrderDetails。如果不想硬编码当前库名可以用current_database()动态传入pg_database_size(current_database())。psql里也有快捷方式\l可以列出所有数据库大小\dt可以列出当前schema所有表的大小。不过\dt里的Size列只显示pg_table_size不包含普通索引所以做索引占用分析不能只看它。pg_size_pretty返回的是带单位的字符串不适合在程序里做数值比较或排序排序时应该用原始字节数值最后展示时再格式化。我把这些函数记熟之后基本不依赖图形化工具。DataGrip、DBeaver这类客户端虽然也有可视化表大小功能但在线上环境、或者要批量处理几十张表时SQL脚本永远是最快、最灵活的方案。3. 现成SQL合集从集群、数据库到单表一次盘清有了上面的函数接下来直接抄作业。下面的几条SQL我基本每个PG项目都会用到按场景整理好了。3.1 整个实例里所有数据库大小排行要摸清整个实例的磁盘占用第一件事先看所有数据库的排行SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS pretty_size, pg_database_size(datname) AS raw_size FROM pg_database ORDER BY raw_size DESC;注意这里list出的大小的确是这个数据库所有物理文件的合计但有几个东西不计入WAL日志pg_wal不算在任何库里临时文件也不算。如果磁盘都快满了WAL往往是隐形大户后面专门说。3.2 当前库里所有业务表按总大小排序连接进入目标库后用这条SQL列出所有业务表包括表索引TOAST的大小排行SELECT n.nspname AS schema_name, c.relname AS table_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_relation_size(c.oid)) AS heap_size, pg_size_pretty(pg_indexes_size(c.oid)) AS index_size, pg_total_relation_size(c.oid) AS raw_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind IN (r, m) -- 普通表 物化视图 AND n.nspname NOT IN (pg_catalog, information_schema) AND n.nspname NOT LIKE pg_toast% ORDER BY raw_size DESC LIMIT 30;几点说明relkind r表示普通表m表示物化视图p表示分区父表。如果加p注意分区父表本身不存储数据总大小通常为0需要继续看它的子分区。排除pg_catalog、information_schema和pg_toast前缀是为了不把系统表和TOAST对象列出来否则结果里会混进一堆pg_toast_开头的奇怪名字。如果你只关心用户业务表把LIMIT调到50也行但更重要的是先找到Top 20。3.3 单独看索引和TOAST的占用索引占用经常被忽略。一个写着业务辅助索引较多的表索引体积很容易接近甚至超过主表。想找出全库“最肥”的索引SELECT schemaname, relname AS table_name, indexrelname AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, pg_relation_size(indexrelid) AS raw_size FROM pg_stat_user_indexes ORDER BY raw_size DESC LIMIT 20;这条SQL能帮你快速发现类似“一张8600万行的流水表上建了9个索引其中两个联合索引加起来占了快40GB”这种状况。索引不是越多越好尤其对写入频繁的表每一个额外索引都是写放大。TOAST占用怎么看比较两个值pg_table_size - pg_relation_size差值基本就是TOAST堆索引的大小。想精确到具体表的TOAST大小可以查pg_class里的reltoastrelid再对TOAST表调用pg_relation_sizeSELECT c.relname AS table_name, pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size FROM pg_class c WHERE c.relkind r AND c.reltoastrelid 0 ORDER BY pg_relation_size(c.reltoastrelid) DESC LIMIT 20;如果某张表的TOAST膨胀厉害通常说明这个表里有很宽的大文本、JSONB字段而且往往是先压缩后存储压缩率不高。遇到这种情况空间优化思路要转向“大字段是否非要落库”“能不能拆到对象存储”“历史数据是否要归档”单纯VACUUM只能解决死元组解决不了真正的大数据量存储本身。3.4 顺带查一下表的健康度膨胀与死元组只看大小还不够我更建议你每次盘空间时同时看下死元组数量判断大表是不是“虚胖”SELECT schemaname, relname AS table_name, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(100 * n_dead_tup::numeric / n_live_tup, 2) ELSE 0 END AS dead_tup_ratio, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY n_dead_tup DESC LIMIT 20;这里的n_live_tup和n_dead_tup来自统计信息是估算值准确度取决于ANALYZE的频率不一定完全精确但用来初筛“哪些表需要VACUUM”绰绰有余。如果一张表死元组几十万甚至上千万那它一定需要一个及时的VACUUM空间也多半已经被撑大了。可以再配合pgstattuple扩展做精确评估但这个扩展属于高级工具基础场景不强制使用。4. 实战复盘磁盘告警后我是怎么定位到那张“吞空间”的大表接下来进实战。一个最常见的场景监控突然报“数据盘使用率超过85%”你要在最短时间内找出哪张表、甚至哪个数据库在捣乱。4.1 标准排查路径按顺序来我个人的排查顺序固定为四步顺序不建议乱换第一步确认是哪个磁盘分区。如果PG的数据目录和WAL目录在不同磁盘可能出现数据盘没满、WAL盘满了的情况。先df -h看清楚再做数据库内排查。第二步看整个实例的WAL目录大小。很多人会在这里卡住明明库里所有库加起来才100GB磁盘却用了500GB。这时候先看pg_wal目录du -sh $PGDATA/pg_walWAL日志在归档开启且归档不及时、或者checkpoint较远时可能积压几十甚至上百GB。如果WAL已经很大根因通常在频繁检查点、过大的wal_keep_size或复制槽积压。这属于空间排查中绝对不能跳过的一环。第三步进库里按数据库大小排行锁定是哪个库。第四步连接目标库按表大小排行锁定是哪些表再看这些表的死元组判断是否膨胀。4.2 一个真实例子拆解举个我处理过的案例。某业务库磁盘使用率达到92%库里一共6个库其中一个“核心交易库”就有450GB。连接进去后执行前面的“所有业务表按总大小排序”Top 1是一张名为trade_flow的流水表pg_total_relation_size显示245GB。具体拆解开pg_relation_size主堆大约146GBpg_indexes_size大约68GB剩下约31GB是TOAST相关看到146GB主堆第一反应是查行数。用count(*)太慢先看pg_stat_user_tables里的n_live_tup大约6000万。如果是6000万正常订单行单行平均宽度应该在200~500字节加上页面填充开销合理的堆大小应该在20~30GB左右。146GB明显超出现在有效数据的量级死元组也到了几千万结论很明确这是一张长期高频UPDATE/DELETE之后没有有效VACUUM的膨胀表。当时值完夜班后凌晨维护窗口执行了VACUUM (FULL) trade_flow120分钟完成主堆从146GB降到27GB全库空间直接释放了100多GB。这个案例说明看见“大表”别急着扩容磁盘先分清是真数据大还是死数据多。4.3 定位到表之后空间怎么安全回收回收空间有几种手段各自适用场景不同普通VACUUM只清理死元组、更新统计信息、让内部空间可复用但不会把文件尾部没使用的页面释放给操作系统。如果你只是想让查询更快、防止事务ID回卷用这个。VACUUM (FULL)重建整个表文件压缩到最小会释放磁盘空间。但执行期间需要ACCESS EXCLUSIVE锁表完全不可读写。几百GB的大表可能耗时数小时只能选维护窗口。CLUSTER按指定索引排序重写表效果类似VACUUM FULL但要求有索引同样重度锁表。pg_repack可以在线消除膨胀不需要长时间阻塞读写但要求表有主键或唯一索引且执行期间需要额外的磁盘空间。生产环境如果确实没法停读写pg_repack是首选。我个人的建议是小表可以直接VACUUM FULL大表先评估业务窗口万不得已不要轻易在生产大表上直接FULL。日常维持把autovacuum调好比事后救火重要得多。5. 容易踩的坑和排查技巧记录这部分是长期踩坑后的记录前方高能每一条都是真实工作里容易翻车的地方。5.1 删了那么多行磁盘空间竟然没释放这是新手问得最多的问题。DELETE掉80%的行df一看磁盘占用纹丝不动就开始怀疑是不是删错了。实际上在PG里DELETE只是标记死元组文件系统层面的空间不会被归还。你需要VACUUM把死元组占用的页面标记为可复用再进一步VACUUM FULL才能物理收缩文件。如果一张表是“每次清理时把历史数据全删掉”更推荐用TRUNCATE而不是DELETE因为TRUNCATE直接重置文件瞬间释放空间。这也是为什么我喜欢强调空间查看不能只看瞬时值要结合你最近做过什么操作去解释数值。5.2 pg_relation_size查出来怎么和du/文件大小对不上遇到过好几次pg_relation_size(big_table)返回20GB但到数据目录找对应文件名用ls -l一看才12GB或者32GB。解释是PG函数返回的是逻辑大小按页面的“使用中/已分配”关系计算和文件实际大小有差异。表文件可能因为VACUUM FULL或扩展过程产生空洞物理文件大小通常不会比逻辑大小小太多但有时会看到文件更大因为文件中包含FSM、VM附加段以及预留的段文件。更重要的一个点你找的filenode未必对应表当前OID。如果执行过TRUNCATE或者ALTER TABLE表的relfilenode会变化旧物理文件可能还残留在base目录里等待清理。这时候按表名去找文件会找错。正确做法是用查询SELECT pg_relation_filepath(public.big_table);拿到真实相对路径再去看文件。这是我踩过多次的坑分享出来帮大家省点时间。5.3 VACUUM FULL会锁表生产环境别直接上VACUUM FULL的锁是ACCESS EXCLUSIVE也就是最重的那档锁和ALTER TABLE级别一样。一张几千万行的表在上面执行VACUUM FULL期间所有读写都会堵住长则数十分钟、短也够业务超时报警。有些不善的DBA习惯“空间不够就FULL一把”然后业务半夜哀嚎这种教训太常见了。更安全的做法优先保证autovacuum工作正常设置合理的autovacuum_vacuum_scale_factor新版还支持基于阈值的参数让它平时就把死元组清掉。必须物理收缩时使用pg_repack它在重写表时通过触发器持续同步增量数据业务连接基本不受影响只是重写期间CPU/IO会明显抬升。执行之前务必确认磁盘剩余空间大于表的大小因为重建期间需要一份新副本的空间否则会卡在中途。5.4 监控脚本与趋势分析的一点建议最后说说比“看一次大小”更有价值的事把空间查询做成定期巡检并记录历史趋势。我个人的做法是每周在每台实例上跑一次核心业务表的排行SQL把“库名、表名、总大小、堆大小、索引大小、死元组数”写入一张专门的监控统计表留存三个月。这样能看到哪张表在持续变大、哪个索引增长异常、哪次大版本变更后空间突增。趋势比瞬时值更说明问题。单看大小你只知道现状结合趋势你才能判断这是正常业务增长、还是膨胀失控、又或者是慢SQL导致的临时空间积压。这套脚本用最简单的psql加cron就能实现不需要额外引入复杂的监控系统。PostgreSQL本身提供的系统视图和函数已经足够强大关键是你会不会用、有没有形成自己的排查套路。我个人在实际操作中最深的体会是空间排查技巧本身不难难的是养成“先分清数据构成、再判断是否异常、最后对症处理”的思维习惯。下次再碰到磁盘告警别急着删数据扩磁盘先跑一遍文中的SQL把账算清楚再动手。