先从一个我经常被问到的场景说起吧。某天凌晨两点监控告警弹出来某台 PostgreSQL 实例所在磁盘使用率已经超过 85%。我登录服务器看了一眼数据目录确实膨胀得厉害但打开 psql 准备清理时突然发现自己有点拿不准——到底是哪个数据库、哪张表吃了大部分空间MySQL 用惯了information_schema.tables就能看到行数和数据长度到了 PostgreSQL 好像不能直接照抄这套思路有些视图里根本没现成的字段甚至查出来的数字和du -sh对不上。这篇文章就是把PostgreSQL 查看数据库及表中数据占用空间大小这件事彻底讲透从统计视图的原理到数据库级、表级、索引级、TOAST 级的具体查询方法再到空间异常时的深层排查思路。无论你是刚从 MySQL 迁移过来、正在做容量规划还是已经遇到磁盘告警需要立刻定位问题这篇文章都可以当作一份能直接照着操作的排查手册。1. 为什么 PostgreSQL 的空间统计看起来总是不准先说一个常见的误区。很多人在 PostgreSQL 里想查表大小第一反应是先看一眼系统视图比如pg_stat_user_tables发现里面没有类似 MySQLDATA_LENGTH的字段于是觉得无从下手。还有人在网上搜到SELECT reltuples, relpages FROM pg_class WHERE relname some_table拿relpages乘以 8 就当作表的大小结果发现这个数字往往比实际小很多甚至和pg_database_size对不上。问题出在哪呢pg_class.relpages和reltuples是优化器做执行计划时的估算依据它们由ANALYZE或者VACUUM更新采样粒度取决于配置参数default_statistics_target和采样比例。换句话说这是一份体检报告而不是实时监控数据。如果你插入了几千万行但还没触发自动分析relpages可能还停留在旧值。用它估算空间只能当作数量级参考不能作为清理和容量决策的依据。PostgreSQL 里真正权威的空间数据必须靠调用统计函数现场计算而不是读某个静态字段。这跟 MySQL 的习惯完全不同但理解之后你会觉得这个设计反而清晰函数直接扫描表对应的物理文件元数据返回的是当前实际大小。常用的有pg_database_size()、pg_relation_size()、pg_total_relation_size()、pg_table_size()、pg_indexes_size()等配合pg_size_pretty()输出成人类可读格式。所以正确的心法是能直接算的就别去读统计信息表。统计视图用来观察热度、死行数量、扫描次数等变化趋势空间大小一律用函数实时拿。2. 数据库级空间占用一条 SQL 直接出结果查看某个数据库的实际磁盘占用核心就是pg_database_size()。它的参数可以是数据库名或 OID返回单位是字节。-- 查看当前数据库大小 SELECT pg_size_pretty(pg_database_size(current_database())); -- 查看指定数据库大小 SELECT pg_size_pretty(pg_database_size(mydb));想一次性列出实例里所有数据库的占用可以用下面这条按大小倒序排列一屏看全SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;执行输出类似database_name | db_size ------------------------ mydb | 12 GB appdb | 3581 MB postgres | 7913 kB template1 | 7913 kB template0 | 7739 kB注意template0、template1、postgres这几个系统库通常都很小如果它们体积异常增大多半是有人把业务表建到了模板库或者postgres库里这种表放错抽屉的现象在生产环境并不少见建议顺手排查。这里有个经常让新手困惑的问题为什么pg_database_size()算出来的值和du -sh $PGDATA看到的目录大小对不上因为数据库大小函数统计的是该数据库关联的堆表、索引、TOAST、系统目录等文件但不包括所有数据库共享的 WAL 日志、临时文件、逻辑复制槽累积的数据也不包括尚未清理的旧版本文件。一个 10GB 的实例WAL 峰值可能额外占掉好几 GB。所以看到两者有出入是正常的磁盘告警时要记得并行检查pg_wal/目录和pg_stat_replication里复制槽的状态。如果还要更细一步想看看某个数据库里到底哪一类对象占了主要空间可以执行SELECT rpad(r.relname, 32) AS relation_name, CASE WHEN r.relkind i THEN index WHEN r.relkind t THEN toast ELSE table END AS type, pg_size_pretty(pg_total_relation_size(r.oid)) AS total_size FROM pg_class r JOIN pg_namespace n ON n.oid r.relnamespace WHERE n.nspname public ORDER BY pg_total_relation_size(r.oid) DESC LIMIT 30;pg_total_relation_size(r.oid)比pg_relation_size(r.oid)更全面它把表本身的堆文件、TOAST 文件、TOAST 索引、所有普通索引都一起算进去了下面一节我专门拆开讲。3. 表级空间占用拆解每个统计函数的边界必须先搞清楚PostgreSQL 表空间统计和 MySQL 一个很大的不同是一张表的物理存储可能由多个文件构成。主表数据存放在一个堆文件里如果表里有大字段如text、jsonb、bytea超出行阈值的部分会被压缩挪到 TOAST 表另外还有 TOAST 表自带的索引再加上用户创建的普通索引。每个部分在磁盘上是独立的文件。为了把这几个部分分开PostgreSQL 提供了四个配套函数我在实际排查中几乎天天用先给它们划清边界函数统计范围典型用途pg_relation_size(relation)只统计主堆表本身不含 TOAST、不含索引查看裸数据文件大小pg_table_size(relation)主堆表 TOAST 表 TOAST 索引查看这张表到底存了多少业务数据pg_indexes_size(relation)该表所有普通索引的总大小评估索引开销pg_total_relation_size(relation)主堆表 TOAST TOAST 索引 普通索引查看表在磁盘上的总占用举一个实际例子。我在一张订单表上建了 6 个索引其中几个还是联合索引查出来的结果是SELECT pg_size_pretty(pg_relation_size(orders)) AS heap_size, pg_size_pretty(pg_table_size(orders)) AS table_plus_toast_size, pg_size_pretty(pg_indexes_size(orders)) AS indexes_size, pg_size_pretty(pg_total_relation_size(orders)) AS total_size;heap_size | table_plus_toast_size | indexes_size | total_size ------------------------------------------------------------------ 1249 MB | 1251 MB | 2856 MB | 4106 MB一眼就能看出问题索引比数据本身还大两倍多。索引体积膨胀除了业务上确实需要多索引之外更常见的原因是索引页碎片化和死元组残留这种只能靠重建索引解决。看到indexes_size明显超过heap_size时就该优先考虑对高频写入表做REINDEX而不是一上来就加硬件。单独查看一张表最大的几个分区或 TOP N 大表用这条语句会更直观SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_table_size(relid)) AS table_size, pg_size_pretty(pg_indexes_size(relid)) AS index_size, n_live_tup, n_dead_tup FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;这里把pg_stat_user_tables里的活元组数n_live_tup和死元组数n_dead_tup一起带出来是因为后面排查膨胀时这两个数字非常关键。需要提醒的是n_live_tup是估算值不是精确值但它能反映数量级。如果你想看得更细还想知道某个表对应的索引分别占多大可以这样SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size FROM pg_indexes WHERE tablename orders ORDER BY pg_relation_size(indexname::regclass) DESC;pg_indexes视图是直观的入口但注意indexname是文本类型要::regclass转一下类型才能传给pg_relation_size()。这个细节不处理会直接报错我在刚接触时也踩过。4. 空间异常时的深层排查死元组、TOAST 和索引膨胀才是磁盘杀手如果你执行上面的 TOP N 查询发现某张表pg_total_relation_size()已经大得不正常而业务数据量明明没那么大那大概率是下面三种情况之一死元组堆积、TOAST 大字段膨胀、索引碎片化。这三种问题要么靠VACUUM要么靠REINDEX要么靠VACUUM FULL但处理方法和代价完全不同必须先用排查确认是哪一种不能盲目操作。4.1 死元组堆积PostgreSQL 特有的旧版本残留PostgreSQL 的 MVCC 机制决定了每次UPDATE或DELETE并不会立即从物理文件里抹掉旧数据而是在原行上标记为死元组等待VACUUM回收。如果表写入频繁而 autovacuum 没有及时触发死元组会不断累积表物理文件越来越大但SELECT count(*)出来的行数却很少——这就是典型的表膨胀。判断一张表有没有严重死元组堆积最直接的方法是对比上面查询结果里的n_live_tup和n_dead_tup或者单独执行SELECT relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(n_dead_tup::numeric / n_live_tup, 2) ELSE 0 END AS dead_ratio FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY n_dead_tup DESC;当死元组数量达到活元组的 20%、50% 甚至更高时这张表的膨胀概率非常高。常规VACUUM能回收死元组占用的空间供后续复用但不会把物理文件缩小。也就是说跑完VACUUM后n_dead_tup会下降但pg_relation_size()不一定变小。如果希望把表文件真正压缩回去就需要VACUUM FULL这个操作会重写整张表并获取ACCESS EXCLUSIVE锁生产环境建议先确认维护窗口或者考虑使用pg_repack这种在线重建工具。4.2 TOAST 表大字段悄悄占据的空间PostgreSQL 对超长字段典型如text、jsonb、bytea有独立的 TOAST 机制。当一行数据超过约 2KB 时PG 会把大字段压缩并挪到 TOAST 表中原表只保留一个指针。你直接查询表时感觉不到这个分离但物理空间可能被 TOAST 吃了大头。想查看某张表 TOAST 到底占了多少可以用如下 SQLSELECT pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size FROM pg_class c WHERE c.relname orders;如果reltoastrelid是 0说明这个表没有 TOAST 文件。更完整的写法是把主表大小、TOAST 大小、索引大小一起列出来SELECT pg_size_pretty(pg_relation_size(orders)) AS heap_size, pg_size_pretty(pg_relation_size( (SELECT reltoastrelid FROM pg_class WHERE relname orders) )) AS toast_size, pg_size_pretty(pg_indexes_size(orders)) AS index_size;我曾经排查过一张内容管理系统的文章表业务看到只有 200 万行但表占了 80GB。查完之后发现主表只有 5GBTOAST 却有 60 多GB原因是编辑历史把整篇 HTML 原文和 JSON 快照都存进了jsonb字段一个版本就几十 KB日积月累非常惊人。这种场景里 TOAST 大小本身就是合理的业务结果压缩空间不在于 VACUUM而在于业务归档策略。4.3 索引膨胀重建比瞎等更有效索引膨胀的机制和堆表膨胀类似高频UPDATE和DELETE会让索引页产生大量碎片和死条目索引文件只会变大不会自动缩水。单独看表的index_size和heap_size之比能初步判断。如果索引大小接近甚至超过表数据大小并且业务写入量大一般建议做一次REINDEX INDEX或者REINDEX TABLE。-- 重建某张表的所有索引 REINDEX TABLE orders; -- 只重建特定索引 REINDEX INDEX idx_orders_created_at;从 PostgreSQL 12 开始REINDEX支持CONCURRENTLY选项可以在不阻塞读写的情况下重建索引代价是更耗时、占用更多临时空间。对于大表我更倾向在生产环境用并发重建避免长时间锁表拖垮业务。需要留意的是重建索引期间临时文件可能占掉不少磁盘空间本来紧张时要先估算余量。4.4 从零实测一张膨胀表的完整排查流程结合前面所有工具我分享一下遇到单表膨胀时的标准排查顺序这个流程在多次实战中帮我快速定位了问题先跑 TOP N 大表查询拿到total_size、table_size、index_size、n_live_tup、n_dead_tup判断是整体偏大还是某个部分偏大。如果n_dead_tup / n_live_tup很高先跑VACUUM (ANALYZE)回收死元组。普通 vacuum 代价低、不锁表可以放心执行。再次查询pg_total_relation_size()。如果文件大小没变但n_dead_tup明显下降说明物理空间已被回收等待复用如果磁盘压力依然大再考虑VACUUM FULL。如果index_size明显不合理且pg_stat_user_tables里idx_scan很低而idx_tup_read很高基本可以判断索引利用率不高或碎片化严重需要检查索引设计或做REINDEX INDEX CONCURRENTLY。如果toast_size几乎是整张表的主力重点查业务大字段的写入频率和保留策略而不是急着 vacuum。这个流程的好处是先做代价最低、风险最小的操作再到高代价操作每一步都有数据支撑不会出现凌晨对着一张热表执行VACUUM FULL把业务锁死的事故。5. 排查空间问题时必须避开的三个操作误区空间排查本身不难难的是在排查之后做出错误操作。以下三个坑我见过不止一次值得单独拉出来说。5.1ANALYZE只能刷新统计信息不能回收任何空间有人看到表很大觉得跑一下分析、刷新统计空间是不是就释放了这是误解。ANALYZE的作用是更新pg_statistic里的数据分布信息帮助优化器生成更好的执行计划它完全不触碰数据文件。对应地VACUUM才会回收死元组空间。我在很多生产环境里遇到过磁盘告警之后值班同事跑了ANALYZE然后看空间没有任何变化才意识到跑错了命令。正确做法是先VACUUM再按需ANALYZE可以直接写VACUUM (ANALYZE)。5.2VACUUM FULL会锁表别在生产时段直接执行VACUUM FULL会把表重写一遍并持有ACCESS EXCLUSIVE锁期间任何读写操作都会被阻塞。对于一张几十 GB 的热表这可能是致命的。如果必须压缩空间优先使用pg_repack这类在线工具或者安排在明确的维护窗口内执行。我自己的经验是能用分区表迁移归档解决的问题绝不动用VACUUM FULL。5.3 别忽视pg_wal和复制槽的独立占用有时候数据库本身并不大但磁盘还是满了原因往往在pg_wal/。WAL 文件的大小受max_wal_size、checkpoint_completion_target和备份/复制槽状态影响如果一个逻辑复制槽长期不被消费PostgreSQL 会一直保留对应的 WAL磁盘就会被撑爆。查看方法-- 查看 WAL 目录总大小 SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir(); -- 查看复制槽状态 SELECT slot_name, slot_type, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_size FROM pg_replication_slots;如果发现某个slot_name对应的active是 false 但保留的 WAL 很大基本可以判断是残留槽没有清理。清理前一定要确认不会影响下游消费方否则会破坏复制链路。6. 一张实用速查表把常用查询和适用场景全列出来最后把这次讲到的查询整理成一张速查表方便你在排查时直接对号入座。查询目标核心 SQL备注当前数据库大小SELECT pg_size_pretty(pg_database_size(current_database()));实时计算所有数据库大小排序SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;一屏看全单表总占用SELECT pg_size_pretty(pg_total_relation_size(tablename));包含索引和 TOAST单表数据部分SELECT pg_size_pretty(pg_table_size(tablename));主表TOAST单表索引部分SELECT pg_size_pretty(pg_indexes_size(tablename));索引总大小单表 TOAST 单独大小SELECT pg_size_pretty(pg_relation_size(c.reltoastrelid)) FROM pg_class c WHERE c.relname tablename;reltoastrelid0则无 TOASTTOP N 大表SELECT ... FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT N;附带死元组数据便于判断膨胀TOP N 大索引从pg_indexes视图配合pg_relation_size()查询注意::regclass转换WAL 目录大小SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir();磁盘空间排查时别漏掉复制槽保留量SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) FROM pg_replication_slots;复制槽长期不消费会撑爆磁盘个人实际体会是空间排查不要只盯着一个函数要把pg_total_relation_size()、pg_stat_user_tables、WAL 目录三者的数据放在一起看。很多表不大但磁盘满了的怪象最后都出在那些容易被忽略的角落比如复制槽、临时文件、残留的旧版本数据文件。养成定期跑一遍 TOP N 大表查询的习惯远比等到告警再手忙脚乱要好。到这里关于 PostgreSQL 空间占用的查询与排查方法就全部讲完了。最后再分享一个小技巧如果你经常需要查看这些数据可以把它封装成一个视图或者只读函数放到公共模式里团队内所有人查空间都能用同一套口径避免每个人写的 SQL 口径不统一、数值对不上。我自己就是建了一个叫public.view_relation_sizes的视图每周定时跑一次把结果存下来做容量增长趋势分析很方便。