某天凌晨监控告警把值班手机震到发烫磁盘使用率飙到93%业务日志里全是“could not extend file”的报错。第一反应是赶紧找出哪张表在疯涨但用psql敲了几条SQL之后发现统计出来的库大小加起来只有磁盘占用的一半不到。这种“账对不上”的情况做PostgreSQL巡检时不算罕见——数据文件、WAL日志、死元组、TOAST、索引各自占着一块地方只看单个统计函数根本拼不出全貌。这篇文章把我日常排查空间占用的一套完整方法梳理出来从最常用的几个空间统计函数pg_database_size、pg_total_relation_size、pg_relation_size到底各管哪一段到一条SQL扫全库的表和索引排行再到死元组导致的空间膨胀怎么识别和回收最后还会交代WAL、临时文件、TOAST这些容易漏掉的角落。无论你是刚接手PostgreSQL的运维新手还是正在为几千万行大表的膨胀发愁的开发照着这套流程走一遍基本能把“空间去哪了”这件事查得明明白白。1. 空间去哪了先弄懂PostgreSQL的存储结构再查统计1.1 一张表在磁盘上其实是一组文件很多刚接触PostgreSQL的朋友会下意识觉得“一张表对应一个文件删了表空间就少了”。实际上没那么简单PostgreSQL的每个表包括索引在数据目录base/[数据库OID]下都有一个独立的数据文件文件名的数字ID是relfilenode随时可以通过pg_relation_filepath函数查到它在磁盘上的真实路径SELECT pg_relation_filepath(orders); -- 返回类似 base/16384/351576 的路径更麻烦的一点是PostgreSQL默认数据文件超过1GB会自动切分成带_1、_2后缀的分段文件。也就是说一张逻辑上的大表物理上可能是一串几十个文件。在文件系统里用du统计时必须把所有分段都算进去。这个1GB分段的机制主要是为了让文件系统对大文件的管理更友好减少单个超大文件的I/O压力同时也避免依赖老旧内核对大文件上限的限制。除了主数据文件每个表还配套着两个附属forkfsm空闲空间映射记录页内可用空间位置用来快速找到能插入新行的页。vm可见性映射记录哪些页对所有事务都可见用来加速index-only scan。再加上大字段会溢出到TOAST表这个后面专门讲所以“表大小”这个数字至少要由四部分构成主堆文件 fsm/vm TOAST表 关联索引。1.2 为什么统计数字和df看到的占用对不上如果你用pg_database_size把库里所有表加起来发现和磁盘上du看到的占用差了几个GB别慌大概率不是统计错误。数据库级和表级的统计函数只统计数据目录里base目录下的关系文件下面这几块都不算在内WAL日志pg_wal目录每次事务提交都会先写WAL压力大的库里WAL能占好几个GB临时文件排序、哈希、物化操作超过work_mem时会在base目录下生成pgsql_tmp临时文件session结束才清理逻辑复制相关文件例如pg_logical/snapshots等目录VACUUM没回收的死元组这部分虽然包括在关系文件里但统计函数给的是文件实际占用而业务“有效数据”可能只占一半。所以在正式动手排查前先把概念捋清楚PostgreSQL提供的各类size函数是“关系文件在磁盘上占多大”而不是“表里还剩多少有效行”。理解了这一层后面查膨胀、判断是否需要VACUUM FULL思路才会对得上。2. 核心统计函数逐个拆解一个字节都不能对不上2.1 pg_relation_size与pg_total_relation_size一字之差差了好几个GB这组函数是排查单表空间占用时最先要用的。区别一句话就能说清楚pg_relation_size(表名)只返回主堆文件的大小不含索引、不含TOASTpg_table_size(表名)主堆 fsm/vm TOAST仍然不含索引pg_total_relation_size(表名)上面全部再加所有关联索引这才是这张表在数据库里占用的完整账目pg_indexes_size(表名)单独统计这张表上所有索引的总占用。把这些拆开看的价值在于一张涨得很快的表到底是堆数据本身在涨还是索引在膨胀处理方式完全不同。我在实际项目里碰到不止一次排第一的表占了50GB仔细一拆索引就占了30GB其中一个长期没有使用的二级索引直接drop空间瞬间就回来了。写代码或写运维脚本时注意这几个函数的入参是regclass类型传字符串时如果表名不在search_path里或者多个schema下有重名表要带上schema前缀例如pg_total_relation_size(public.orders)。2.2 pg_database_size与pg_size_pretty库级统计和可读性格式化库级统计用pg_database_size(数据库名)返回该库所有关系文件加起来的字节数。需要提醒的是它同样不包含WAL和临时文件。psql里直接执行\l也会显示每个库的大小本质上就是调用了这个函数。有一说一裸字节数在命令行里看实在难读特别是上GB之后。搭配pg_size_pretty格式化即可SELECT pg_database_size(postgres) AS bytes; SELECT pg_size_pretty(pg_database_size(postgres)) AS db_size;反过来如果脚本里接收的是“10GB”这类字符串需要转成数字做比较可以用pg_size_bytes(10GB)。日常写巡检SQL我几乎总是pg_size_pretty和原始字节一起输出原始字节用来排序和精确计算格式化字段用来给人看。这里顺手列个对照表方便以后直接查函数返回内容典型用途pg_database_size整个数据库的关系文件总大小不含WAL/临时文件库级排行pg_relation_size单表主堆文件大小判断数据本体pg_indexes_size单表所有索引大小判断索引开销pg_table_size主堆TOASTfsm/vm分析数据大字段pg_total_relation_size表索引TOAST全算表级完整账单pg_tablespace_size某个表空间总量表空间规划2.3 一张表完整的“空间账单”SQL要一眼看穿某张表是如何构成的执行下面这段SELECT pg_size_pretty(pg_relation_size(public.orders)) AS heap_size, pg_size_pretty(pg_indexes_size(public.orders)) AS index_size, pg_size_pretty(pg_total_relation_size(public.orders)) AS total_size;如果heap_size明显小于total_size说明索引或TOAST吃掉了大头优先去排查索引和超大字段如果heap_size和total_size接近且两者都很大那就是堆数据本身多考虑分区、归档或清理历史数据。这个判断习惯养成了再看复杂的统计报表就不会晕。3. 全局体检SQL一次扫完所有库和所有表3.1 所有数据库的大小排行接到“磁盘快满了”的告警我从来不会直接跑到业务服务器上瞎找文件第一步永远是先查库级排行把目标缩小到具体库SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;注意pg_database视图里还包含template0、template1这些模板库它们虽然平时一般不用也都占着磁盘。排查时看到别惊讶也不要贸然去动template库模板坏了重建很麻烦。3.2 当前库里最大的20张表定位到具体库之后接着就该查表级排行。这段SQL用到pg_stat_user_tables里的relid直接传给size函数能避免拼表名带来的一堆引号问题SELECT schemaname, relname, n_live_tup, pg_size_pretty(pg_table_size(relid)) AS table_size, 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;这里的n_live_tup来自统计信息是估算值不是精确值但用来判断表的量级足够了。把LIMIT改成不限制、导出成CSV就能做成每天定时跑的巡检报表连续观察几天就能看出哪些表在持续增长。3.3 索引占用异常排查一张没用的二级索引能吃几个GB光看表还不够很多空间问题藏在索引里。再来一段索引排行SELECT schemaname, tablename, indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 30;看到某个超大索引后先别急着drop。确认三个问题一是否有查询真正使用该索引二是否存在冗余索引比如某索引是另一个多列索引的前缀子集三该索引是否常年不更新也没人命中。全都确认没问题再考虑删除或重建。4. 最隐蔽的“空间黑洞”死元组、膨胀与VACUUM回收4.1 MVCC机制下DELETE和UPDATE不会立刻释放磁盘这是PostgreSQL新手最容易踩的坑对一张大表执行DELETE删掉了90%的行发现磁盘占用一点没变。原因是PostgreSQL的MVCC并发控制删除一行并不是物理抹掉而是在原行版本上打一个“已删除”标记同时保留旧版本供尚未结束的读事务使用。UPDATE本质上就是DELETE INSERT旧版本同样保留。这些被标记为删除、且不再被任何事务需要的旧行就叫死元组dead tuple。死元组占着页面的空间文件不会自动缩短于是产生三个后果表文件越来越膨胀查询需要扫描的块越来越多性能下降磁盘被无效数据吃掉。4.2 怎么判断一张表已经膨胀了最直接的办法是查pg_stat_user_tables里的死元组数量和自动清理时间SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100 * n_dead_tup / NULLIF(n_live_tup n_dead_tup, 0), 2) AS dead_pct, last_autovacuum, last_vacuum FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY n_dead_tup DESC LIMIT 20;如果n_dead_tup长期保持在一个大数值说明VACUUM跑得不够勤或者有长事务卡住了清理。dead_pct超过20%属于明显的膨胀信号超过50%基本意味着这张表占的空间有一半是废弃数据。再配合一个更直观的“账目对照”看pg_total_relation_size和实际有效行的理论占用差距。我曾经遇到过一张几亿行的大表文件80GB统计出来n_live_tup只有一小部分剩余空间几乎全是历史UPDATE留下的死元组。4.3 VACUUM和VACUUM FULL的取舍一个回收可复用空间一个物理缩减文件搞清楚膨胀来源之后要区分两种回收操作。VACUUM以及自动VACUUM会把死元组标记的空间整理成可复用状态写入fsm新插入的数据可以重新利用这些页面。但这个操作并不把文件末尾的空页截断所以文件系统里看到的表大小不会明显缩小。它的优势是可以在线执行不阻塞读写适合日常维护。VACUUM FULL则会把整个表重写一遍把有效数据压缩到新文件里旧文件丢弃表文件会肉眼可见地缩小。代价是它需要ACCESS EXCLUSIVE锁执行期间这张表完全不能读写。对几十GB的大表执行VACUUM FULL业务中断和磁盘IO风暴都要提前评估。-- 在线整理回收空间供复用不缩文件 VACUUM (VERBOSE, ANALYZE) public.orders; -- 物理缩小文件持锁慎用于生产高峰 VACUUM FULL public.orders;生产环境大表如果确实需要物理缩小且不能停业务可以考虑pg_repack这类工具它基于触发器记录增量重写表时不需要长时间阻塞DML但会在重写期间消耗额外的磁盘空间大约是原表大小的一部分磁盘本来就紧张的话要算好余量。4.4 自动VACUUM的参数调优别让大表成为真空死角PostgreSQL默认开autovacuum触发条件是“阈值 比例”autovacuum_vacuum_threshold默认50加上autovacuum_vacuum_scale_factor默认0.2乘以当前行数。也就是说一张1000万行的表要积累大约200万行死元组才会触发自动清理。这个默认值对中小表合适对几千万行、上亿行的大表来说明显太迟钝。我的做法是给大表单独拍参数保持全局默认不动ALTER TABLE public.orders SET (autovacuum_vacuum_scale_factor 0.01); ALTER TABLE public.orders SET (autovacuum_vacuum_threshold 1000); ALTER TABLE public.orders SET (autovacuum_vacuum_cost_delay 10);设置完之后再回pg_stat_user_tables确认自动清理真的跑起来了SELECT relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE relname orders;VACUUM有IO成本控制cost-based机制本身不会把数据库压垮但超大表的自动VACUUM跑起来很慢中间再碰上业务高峰会产生较多膨胀。巡检时可以用pg_stat_progress_vacuum这个视图看看某张表到底VACUUM到哪个阶段SELECT datname, relname, phase, heap_blks_total, heap_blks_scanned, round(100 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 2) AS scan_pct FROM pg_stat_progress_vacuum;5. 容易被忽略的空间来源WAL、临时文件与TOAST5.1 WAL日志事务的“账本”也有重量PostgreSQL每写一笔数据都要先记WAL崩溃恢复全靠它。pg_wal目录里的日志文件正常情况下会被checkpoint回收复用但如果checkpoint不勤、max_wal_size设置太大或者遇到长时间未提交的长事务WAL就会迅速堆积。用下面的函数直接看WAL目录大小SELECT pg_size_pretty(SUM(size)) AS wal_size FROM pg_ls_waldir();再配合pg_current_wal_lsn和各从节点的接收情况判断是否需要调整max_wal_size或排查长期空闲事务。注意pg_ls_waldir在PostgreSQL 10及以上才有PG 9.x用pg_xlog目录自己du一下即可。对主从同步的架构来说WAL还会先写本地再传给从节点堆积往往意味着某个从节点故障或网络带宽跟不上了这时候直接扩充磁盘不如先解决同步断裂。5.2 TOAST大字段的“仓库”PostgreSQL行是固定大小存储的一行如果因为某个大字段比如长文本、JSONB、数组超过约2KB超出的部分会被自动移到TOAST表里独立存储原行里只留一个指针。表面上这能压住堆文件大小但TOAST本身同样占用磁盘。某些场景下TOAST的占用甚至超过主表比如大量存储JSONB文档但查询又总是全行取出时。用pg_table_size与pg_relation_size的差值就能大致估算TOAST占用。如果想看到具体是哪张TOAST表在变大可以查SELECT n.nspname AS schema, c.relname AS table_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind t -- t 表示TOAST表 ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 10;TOAST表排第一时先别急着删数据重点查一下业务表里是否有大字段长期累积比如把整个大JSON存进单列还不断更新——每次UPDATE都会产生一份新的TOAST版本膨胀速度往往比你想的快。5.3 临时文件与连接副作用当排序、hash join、group by等操作需要的数据超过work_mem时PostgreSQL会在存储上创建临时文件这些文件归在会话名下session断开时清理。如果应用连接池常驻、长事务多临时文件也可能占掉几个GB。检查方式是在系统层看base目录下的pgsql_tmp特征文件或者观察pg_stat_database的temp_files和temp_bytes字段SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_usage FROM pg_stat_database ORDER BY temp_bytes DESC;temp_bytes长期很大说明work_mem或排序SQL有待优化在合理范围内加大work_mem可以直接减少落盘。比如把work_mem从4MB调到64MB很多中等量级的排序根本就不会再写临时文件空间和查询延迟一起降。6. 实战排查路线图从“磁盘告警”到“安全回收”6.1 一条龙排查步骤把前面各部分串成一个可直接照抄的流程我通常按这个顺序走系统层看df -h确认是数据盘还是日志盘告警顺便看一眼数据目录所在分区的inode是否耗尽用pg_database_size查所有库大小排行锁定目标库用pg_total_relation_size查该库前20张最大的表用pg_stat_user_tables查死元组和自动VACUUM状态判断是否膨胀查索引排行、WAL目录大小、临时文件统计补全剩余空间账目结合业务决定清理顺序清理历史数据、DROP废弃索引、VACUUM、必要时VACUUM FULL或pg_repack。这套流程走完基本能把磁盘占用分成“实际业务数据”和“可回收残余”两部分再决定下一步动作。6.2 回收空间的决策顺序和风险评估回收空间的优先级要按“对业务的危险程度”来排。先做无损操作删除确认无用的备份归档、清理临时文件和废弃索引这些不影响业务再做低成本高回报的操作对膨胀严重的表执行VACUUM或VACUUM FULL但必须选择业务低谷窗口提前评估锁等待风险最后才考虑大动作比如对大表做分区、迁移历史数据到归档表或外部存储。如果膨胀极其严重且磁盘快满我对超大表的最后手段是pg_dump逻辑导出再pg_restore这等于用最原始方式重组数据时间成本高但能把空间和碎片一次清干净。6.3 从救火到防火把巡检做成日常经历过几次半夜救火后我给自己的环境都加了这些预防措施每周定时脚本输出库、表、索引、WAL四张排行表对核心大表单独设置autovacuum参数所有删除动作保留7天内可追溯窗口避免误删后无法恢复。监控上盯两个阈值磁盘使用率超过75%报警死元组比例超过25%报警。前者管文件层面后者管膨胀层面两个都管住了磁盘告警基本就不会再半夜响。空间排查这件事做一次容易难的是持续做。把它固化成巡检脚本和监控规则之后你会发现PostgreSQL的空间账目其实相当透明——只是需要先理解它那套文件构成和MVCC机制再熟悉几个size函数和统计视图的组合用法。我把上面的SQL组装成自己的巡检模板后定位一张表的空间构成基本五分钟内搞定希望这套流程对你同样顺手。最后再提醒一句任何大动作之前先确认备份可用空间再紧张也比不上数据安全。
PostgreSQL空间排查指南：从表大小到WAL与死元组
某天凌晨监控告警把值班手机震到发烫磁盘使用率飙到93%业务日志里全是“could not extend file”的报错。第一反应是赶紧找出哪张表在疯涨但用psql敲了几条SQL之后发现统计出来的库大小加起来只有磁盘占用的一半不到。这种“账对不上”的情况做PostgreSQL巡检时不算罕见——数据文件、WAL日志、死元组、TOAST、索引各自占着一块地方只看单个统计函数根本拼不出全貌。这篇文章把我日常排查空间占用的一套完整方法梳理出来从最常用的几个空间统计函数pg_database_size、pg_total_relation_size、pg_relation_size到底各管哪一段到一条SQL扫全库的表和索引排行再到死元组导致的空间膨胀怎么识别和回收最后还会交代WAL、临时文件、TOAST这些容易漏掉的角落。无论你是刚接手PostgreSQL的运维新手还是正在为几千万行大表的膨胀发愁的开发照着这套流程走一遍基本能把“空间去哪了”这件事查得明明白白。1. 空间去哪了先弄懂PostgreSQL的存储结构再查统计1.1 一张表在磁盘上其实是一组文件很多刚接触PostgreSQL的朋友会下意识觉得“一张表对应一个文件删了表空间就少了”。实际上没那么简单PostgreSQL的每个表包括索引在数据目录base/[数据库OID]下都有一个独立的数据文件文件名的数字ID是relfilenode随时可以通过pg_relation_filepath函数查到它在磁盘上的真实路径SELECT pg_relation_filepath(orders); -- 返回类似 base/16384/351576 的路径更麻烦的一点是PostgreSQL默认数据文件超过1GB会自动切分成带_1、_2后缀的分段文件。也就是说一张逻辑上的大表物理上可能是一串几十个文件。在文件系统里用du统计时必须把所有分段都算进去。这个1GB分段的机制主要是为了让文件系统对大文件的管理更友好减少单个超大文件的I/O压力同时也避免依赖老旧内核对大文件上限的限制。除了主数据文件每个表还配套着两个附属forkfsm空闲空间映射记录页内可用空间位置用来快速找到能插入新行的页。vm可见性映射记录哪些页对所有事务都可见用来加速index-only scan。再加上大字段会溢出到TOAST表这个后面专门讲所以“表大小”这个数字至少要由四部分构成主堆文件 fsm/vm TOAST表 关联索引。1.2 为什么统计数字和df看到的占用对不上如果你用pg_database_size把库里所有表加起来发现和磁盘上du看到的占用差了几个GB别慌大概率不是统计错误。数据库级和表级的统计函数只统计数据目录里base目录下的关系文件下面这几块都不算在内WAL日志pg_wal目录每次事务提交都会先写WAL压力大的库里WAL能占好几个GB临时文件排序、哈希、物化操作超过work_mem时会在base目录下生成pgsql_tmp临时文件session结束才清理逻辑复制相关文件例如pg_logical/snapshots等目录VACUUM没回收的死元组这部分虽然包括在关系文件里但统计函数给的是文件实际占用而业务“有效数据”可能只占一半。所以在正式动手排查前先把概念捋清楚PostgreSQL提供的各类size函数是“关系文件在磁盘上占多大”而不是“表里还剩多少有效行”。理解了这一层后面查膨胀、判断是否需要VACUUM FULL思路才会对得上。2. 核心统计函数逐个拆解一个字节都不能对不上2.1 pg_relation_size与pg_total_relation_size一字之差差了好几个GB这组函数是排查单表空间占用时最先要用的。区别一句话就能说清楚pg_relation_size(表名)只返回主堆文件的大小不含索引、不含TOASTpg_table_size(表名)主堆 fsm/vm TOAST仍然不含索引pg_total_relation_size(表名)上面全部再加所有关联索引这才是这张表在数据库里占用的完整账目pg_indexes_size(表名)单独统计这张表上所有索引的总占用。把这些拆开看的价值在于一张涨得很快的表到底是堆数据本身在涨还是索引在膨胀处理方式完全不同。我在实际项目里碰到不止一次排第一的表占了50GB仔细一拆索引就占了30GB其中一个长期没有使用的二级索引直接drop空间瞬间就回来了。写代码或写运维脚本时注意这几个函数的入参是regclass类型传字符串时如果表名不在search_path里或者多个schema下有重名表要带上schema前缀例如pg_total_relation_size(public.orders)。2.2 pg_database_size与pg_size_pretty库级统计和可读性格式化库级统计用pg_database_size(数据库名)返回该库所有关系文件加起来的字节数。需要提醒的是它同样不包含WAL和临时文件。psql里直接执行\l也会显示每个库的大小本质上就是调用了这个函数。有一说一裸字节数在命令行里看实在难读特别是上GB之后。搭配pg_size_pretty格式化即可SELECT pg_database_size(postgres) AS bytes; SELECT pg_size_pretty(pg_database_size(postgres)) AS db_size;反过来如果脚本里接收的是“10GB”这类字符串需要转成数字做比较可以用pg_size_bytes(10GB)。日常写巡检SQL我几乎总是pg_size_pretty和原始字节一起输出原始字节用来排序和精确计算格式化字段用来给人看。这里顺手列个对照表方便以后直接查函数返回内容典型用途pg_database_size整个数据库的关系文件总大小不含WAL/临时文件库级排行pg_relation_size单表主堆文件大小判断数据本体pg_indexes_size单表所有索引大小判断索引开销pg_table_size主堆TOASTfsm/vm分析数据大字段pg_total_relation_size表索引TOAST全算表级完整账单pg_tablespace_size某个表空间总量表空间规划2.3 一张表完整的“空间账单”SQL要一眼看穿某张表是如何构成的执行下面这段SELECT pg_size_pretty(pg_relation_size(public.orders)) AS heap_size, pg_size_pretty(pg_indexes_size(public.orders)) AS index_size, pg_size_pretty(pg_total_relation_size(public.orders)) AS total_size;如果heap_size明显小于total_size说明索引或TOAST吃掉了大头优先去排查索引和超大字段如果heap_size和total_size接近且两者都很大那就是堆数据本身多考虑分区、归档或清理历史数据。这个判断习惯养成了再看复杂的统计报表就不会晕。3. 全局体检SQL一次扫完所有库和所有表3.1 所有数据库的大小排行接到“磁盘快满了”的告警我从来不会直接跑到业务服务器上瞎找文件第一步永远是先查库级排行把目标缩小到具体库SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;注意pg_database视图里还包含template0、template1这些模板库它们虽然平时一般不用也都占着磁盘。排查时看到别惊讶也不要贸然去动template库模板坏了重建很麻烦。3.2 当前库里最大的20张表定位到具体库之后接着就该查表级排行。这段SQL用到pg_stat_user_tables里的relid直接传给size函数能避免拼表名带来的一堆引号问题SELECT schemaname, relname, n_live_tup, pg_size_pretty(pg_table_size(relid)) AS table_size, 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;这里的n_live_tup来自统计信息是估算值不是精确值但用来判断表的量级足够了。把LIMIT改成不限制、导出成CSV就能做成每天定时跑的巡检报表连续观察几天就能看出哪些表在持续增长。3.3 索引占用异常排查一张没用的二级索引能吃几个GB光看表还不够很多空间问题藏在索引里。再来一段索引排行SELECT schemaname, tablename, indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 30;看到某个超大索引后先别急着drop。确认三个问题一是否有查询真正使用该索引二是否存在冗余索引比如某索引是另一个多列索引的前缀子集三该索引是否常年不更新也没人命中。全都确认没问题再考虑删除或重建。4. 最隐蔽的“空间黑洞”死元组、膨胀与VACUUM回收4.1 MVCC机制下DELETE和UPDATE不会立刻释放磁盘这是PostgreSQL新手最容易踩的坑对一张大表执行DELETE删掉了90%的行发现磁盘占用一点没变。原因是PostgreSQL的MVCC并发控制删除一行并不是物理抹掉而是在原行版本上打一个“已删除”标记同时保留旧版本供尚未结束的读事务使用。UPDATE本质上就是DELETE INSERT旧版本同样保留。这些被标记为删除、且不再被任何事务需要的旧行就叫死元组dead tuple。死元组占着页面的空间文件不会自动缩短于是产生三个后果表文件越来越膨胀查询需要扫描的块越来越多性能下降磁盘被无效数据吃掉。4.2 怎么判断一张表已经膨胀了最直接的办法是查pg_stat_user_tables里的死元组数量和自动清理时间SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100 * n_dead_tup / NULLIF(n_live_tup n_dead_tup, 0), 2) AS dead_pct, last_autovacuum, last_vacuum FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY n_dead_tup DESC LIMIT 20;如果n_dead_tup长期保持在一个大数值说明VACUUM跑得不够勤或者有长事务卡住了清理。dead_pct超过20%属于明显的膨胀信号超过50%基本意味着这张表占的空间有一半是废弃数据。再配合一个更直观的“账目对照”看pg_total_relation_size和实际有效行的理论占用差距。我曾经遇到过一张几亿行的大表文件80GB统计出来n_live_tup只有一小部分剩余空间几乎全是历史UPDATE留下的死元组。4.3 VACUUM和VACUUM FULL的取舍一个回收可复用空间一个物理缩减文件搞清楚膨胀来源之后要区分两种回收操作。VACUUM以及自动VACUUM会把死元组标记的空间整理成可复用状态写入fsm新插入的数据可以重新利用这些页面。但这个操作并不把文件末尾的空页截断所以文件系统里看到的表大小不会明显缩小。它的优势是可以在线执行不阻塞读写适合日常维护。VACUUM FULL则会把整个表重写一遍把有效数据压缩到新文件里旧文件丢弃表文件会肉眼可见地缩小。代价是它需要ACCESS EXCLUSIVE锁执行期间这张表完全不能读写。对几十GB的大表执行VACUUM FULL业务中断和磁盘IO风暴都要提前评估。-- 在线整理回收空间供复用不缩文件 VACUUM (VERBOSE, ANALYZE) public.orders; -- 物理缩小文件持锁慎用于生产高峰 VACUUM FULL public.orders;生产环境大表如果确实需要物理缩小且不能停业务可以考虑pg_repack这类工具它基于触发器记录增量重写表时不需要长时间阻塞DML但会在重写期间消耗额外的磁盘空间大约是原表大小的一部分磁盘本来就紧张的话要算好余量。4.4 自动VACUUM的参数调优别让大表成为真空死角PostgreSQL默认开autovacuum触发条件是“阈值 比例”autovacuum_vacuum_threshold默认50加上autovacuum_vacuum_scale_factor默认0.2乘以当前行数。也就是说一张1000万行的表要积累大约200万行死元组才会触发自动清理。这个默认值对中小表合适对几千万行、上亿行的大表来说明显太迟钝。我的做法是给大表单独拍参数保持全局默认不动ALTER TABLE public.orders SET (autovacuum_vacuum_scale_factor 0.01); ALTER TABLE public.orders SET (autovacuum_vacuum_threshold 1000); ALTER TABLE public.orders SET (autovacuum_vacuum_cost_delay 10);设置完之后再回pg_stat_user_tables确认自动清理真的跑起来了SELECT relname, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE relname orders;VACUUM有IO成本控制cost-based机制本身不会把数据库压垮但超大表的自动VACUUM跑起来很慢中间再碰上业务高峰会产生较多膨胀。巡检时可以用pg_stat_progress_vacuum这个视图看看某张表到底VACUUM到哪个阶段SELECT datname, relname, phase, heap_blks_total, heap_blks_scanned, round(100 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 2) AS scan_pct FROM pg_stat_progress_vacuum;5. 容易被忽略的空间来源WAL、临时文件与TOAST5.1 WAL日志事务的“账本”也有重量PostgreSQL每写一笔数据都要先记WAL崩溃恢复全靠它。pg_wal目录里的日志文件正常情况下会被checkpoint回收复用但如果checkpoint不勤、max_wal_size设置太大或者遇到长时间未提交的长事务WAL就会迅速堆积。用下面的函数直接看WAL目录大小SELECT pg_size_pretty(SUM(size)) AS wal_size FROM pg_ls_waldir();再配合pg_current_wal_lsn和各从节点的接收情况判断是否需要调整max_wal_size或排查长期空闲事务。注意pg_ls_waldir在PostgreSQL 10及以上才有PG 9.x用pg_xlog目录自己du一下即可。对主从同步的架构来说WAL还会先写本地再传给从节点堆积往往意味着某个从节点故障或网络带宽跟不上了这时候直接扩充磁盘不如先解决同步断裂。5.2 TOAST大字段的“仓库”PostgreSQL行是固定大小存储的一行如果因为某个大字段比如长文本、JSONB、数组超过约2KB超出的部分会被自动移到TOAST表里独立存储原行里只留一个指针。表面上这能压住堆文件大小但TOAST本身同样占用磁盘。某些场景下TOAST的占用甚至超过主表比如大量存储JSONB文档但查询又总是全行取出时。用pg_table_size与pg_relation_size的差值就能大致估算TOAST占用。如果想看到具体是哪张TOAST表在变大可以查SELECT n.nspname AS schema, c.relname AS table_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind t -- t 表示TOAST表 ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 10;TOAST表排第一时先别急着删数据重点查一下业务表里是否有大字段长期累积比如把整个大JSON存进单列还不断更新——每次UPDATE都会产生一份新的TOAST版本膨胀速度往往比你想的快。5.3 临时文件与连接副作用当排序、hash join、group by等操作需要的数据超过work_mem时PostgreSQL会在存储上创建临时文件这些文件归在会话名下session断开时清理。如果应用连接池常驻、长事务多临时文件也可能占掉几个GB。检查方式是在系统层看base目录下的pgsql_tmp特征文件或者观察pg_stat_database的temp_files和temp_bytes字段SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_usage FROM pg_stat_database ORDER BY temp_bytes DESC;temp_bytes长期很大说明work_mem或排序SQL有待优化在合理范围内加大work_mem可以直接减少落盘。比如把work_mem从4MB调到64MB很多中等量级的排序根本就不会再写临时文件空间和查询延迟一起降。6. 实战排查路线图从“磁盘告警”到“安全回收”6.1 一条龙排查步骤把前面各部分串成一个可直接照抄的流程我通常按这个顺序走系统层看df -h确认是数据盘还是日志盘告警顺便看一眼数据目录所在分区的inode是否耗尽用pg_database_size查所有库大小排行锁定目标库用pg_total_relation_size查该库前20张最大的表用pg_stat_user_tables查死元组和自动VACUUM状态判断是否膨胀查索引排行、WAL目录大小、临时文件统计补全剩余空间账目结合业务决定清理顺序清理历史数据、DROP废弃索引、VACUUM、必要时VACUUM FULL或pg_repack。这套流程走完基本能把磁盘占用分成“实际业务数据”和“可回收残余”两部分再决定下一步动作。6.2 回收空间的决策顺序和风险评估回收空间的优先级要按“对业务的危险程度”来排。先做无损操作删除确认无用的备份归档、清理临时文件和废弃索引这些不影响业务再做低成本高回报的操作对膨胀严重的表执行VACUUM或VACUUM FULL但必须选择业务低谷窗口提前评估锁等待风险最后才考虑大动作比如对大表做分区、迁移历史数据到归档表或外部存储。如果膨胀极其严重且磁盘快满我对超大表的最后手段是pg_dump逻辑导出再pg_restore这等于用最原始方式重组数据时间成本高但能把空间和碎片一次清干净。6.3 从救火到防火把巡检做成日常经历过几次半夜救火后我给自己的环境都加了这些预防措施每周定时脚本输出库、表、索引、WAL四张排行表对核心大表单独设置autovacuum参数所有删除动作保留7天内可追溯窗口避免误删后无法恢复。监控上盯两个阈值磁盘使用率超过75%报警死元组比例超过25%报警。前者管文件层面后者管膨胀层面两个都管住了磁盘告警基本就不会再半夜响。空间排查这件事做一次容易难的是持续做。把它固化成巡检脚本和监控规则之后你会发现PostgreSQL的空间账目其实相当透明——只是需要先理解它那套文件构成和MVCC机制再熟悉几个size函数和统计视图的组合用法。我把上面的SQL组装成自己的巡检模板后定位一张表的空间构成基本五分钟内搞定希望这套流程对你同样顺手。最后再提醒一句任何大动作之前先确认备份可用空间再紧张也比不上数据安全。