你删掉了 100 万行，磁盘却没释放：数据库里的「尸体」不会自己消失

先说一个我被坑过很多次的现场：一张订单表堆积了两年的数据，DROP TABLE 之后表文件确实删了；但另一张表只是 DELETE 了几百万行，df -h 一看——占用纹丝不动。更离谱的是 VACUUM FULL 跑了半天，磁盘反而先涨后降，业务方在监控群里问「你们是不是在备份？」

不是备份，也不是玄学。这是 MVCC（多版本并发控制）的必然结果：数据库里躺着几百万具「尸体」，而清理它们的动作，被一次没结束的事务死死卡住。

这篇文章不聊怎么写 SQL，就聊一件事：删除和释放空间，是两件完全不同的事，而中间那段距离，就是生产事故的产地。

一、MVCC 的真相：你的删除只是写了个墓碑

先建立正确的心智模型。在 PostgreSQL 里，一行数据在物理上不是一个对象，而是一串版本。当你执行 UPDATE：

-- 原始行
xmin = 100   (由事务 100 创建)
xmax = 0     (还没被删)

-- UPDATE 之后
旧行: xmin=100, xmax=200   -- 事务 200 删掉了它
新行: xmin=200, xmax=0     -- 事务 200 写入了新版本

新版本写到了别的地方（通常在同一个页里，但不在同一行位置），旧版本被标记为「已死」，标记者是 xmax。

关键点来了：旧版本不能立刻物理删除。因为可能还有一个事务（假设 xid=150）正在用旧的快照读这一行，它需要看到「在我开始之前的样子」。所以旧版本要一直保留到「没有任何活跃事务可能看到它」为止。

这就是 xmin horizon（xmin 水位线）的概念：所有活跃事务中最小的事务 ID。所有 xmin < horizon 的行，才算「彻底没人看得见」，才配被回收。

删除是一条墓碑，回收是一场需要全体起立的会议。只要有一个人还坐着，会议就不能散场。

所以 DELETE 之后：

逻辑上：数据不可见了，SELECT count(*) 立刻变少 ✓
物理上：行还在磁盘上，只多了一个 xmax 标记 ✗
空间上：pg_total_relation_size 一动不动 ✗

数据库里成堆的这类死行，行业里有个名字叫 table bloat（表膨胀）。

二、PostgreSQL：VACUUM 不是「清理」，是「标记可复用」

最容易被误解的一点：VACUUM 并不会把空间还给操作系统。它做的是把死元组的空间标记为「可复用」，留给未来新的行使用。

-- 看看哪些表在发胖
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       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;

看到这个输出，运维的心智模型通常会崩一次：total_size 40GB，dead_pct 60%，然后问「不是删了吗，怎么还这么大」。

好消息是，PostgreSQL 有 autovacuum 盯着，默认参数下它会自动跑。坏消息是它的触发阈值和很多现实场景不太合得来：

-- 默认：死元组比例超过 20% 且数量超过 50 行才触发
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold  = 50

-- 大表场景下 20% 意味着几百万死行才启动
-- 改成分数阈值，让大表更早被清理
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold  = 1000
);

还有一个硬核概念：HOT Update。如果被更新的行恰好还在同一个数据页里，PostgreSQL 会直接在页内写新版本并把旧版本标记为死元组（这样索引不用更新，省一次 IO）。所以频繁 UPDATE 的表，页内空间被死元组占满，就会触发「页空间不足 → 新版本被推到别的页 → 索引项也要更新 → 性能悬崖」的连锁反应。这就是为什么 UPDATE 一个字段比 DELETE+INSERT 更轻（因为 HOT 有机会），也解释了为什么某些表的写入会突然变慢——不是因为数据变多了，而是因为页面被撑破了。

三、头号杀手：那个一直没提交的事务

如果你发现 autovacuum 明明开着，n_dead_tup 却一路飙升，且 vacuum 进度常年为 0，那大概率是它被 horizon 卡住了。原因通常是：某个事务开了快照之后一直没结束。

-- 找出卡住 vacuum 的元凶
SELECT pid,
       state,
       now() - xact_start AS xact_age,
       now() - state_change  AS state_age,
       backend_xmin,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
  AND xact_start IS NOT NULL
ORDER BY xact_start;

看到 backend_xmin 是个很老的事务 ID 了吗？它就是那个把水位线钉住的钉子。只要它活着，所有比它新的死元组都得留着。

真实翻车现场：一个报表服务开了 BEGIN 然后忘了 COMMIT，事务在连接池里被复用，快照保持了几小时甚至几天。期间表上 UPDATE 了 2000 万次，表膨胀到 80GB，磁盘报警，VACUUM 干瞪眼。最后 pg_terminate_backend 掐掉它，vacuum 立刻跑起来——但那 80GB 得靠 VACUUM FULL（锁表重建）或者 pg_repack（在线重建）才能真正还回去。

提醒：PostgreSQL 里 idle in transaction 是一种「主动自伤」的行为。开着事务不提交，等于替全库保管垃圾。

四、MySQL InnoDB：尸体存在 Undo Log 里

InnoDB 的模型和 PG 不同，但本质一致：更新和删除产生的旧版本写进 undo log，读事务按 read view 找到对自己可见的版本。

区别在于回收机制：InnoDB 靠 purge 线程 顺着 undo log 往前清理已经没人需要的版本。

-- 查看 undo log 量和 purge 滞后
SHOW VARIABLES LIKE 'innodb_undo_log_size%';
SHOW VARIABLES LIKE 'innodb_purge_threads';
SHOW VARIABLES LIKE 'innodb_purge%lag%';

-- 关键指标
-- History list length：undo 里还有多少个未清理的版本
-- 从 information_schema.innodb_metrics 取
SELECT name, count
FROM information_schema.innodb_metrics
WHERE name IN ('trx_rseg_history_len', 'trx_active_transactions')
  AND subsystem = 'trx';

trx_rseg_history_len 这个数字如果持续暴涨，说明 purge 追不上写入速度——通常是大事务（一次删 500 万行）或者长事务造成的。历史链长度一长，读视图要遍历的 undo 链就变长，于是「查一行数据要读 100 个版本」，查询延迟直接起飞。

MySQL 侧的常见解法：

把大事务拆小：每删 5000 行提交一次，别一次删光
用 pt-online-schema-change 或 gh-ost 做「删了再重建表」的动作，让空间直接还给文件系统
调大 innodb_purge_threads（默认 4，高并发写入库通常不够用）
同样：干掉长事务，MySQL 里查 information_schema.innodb_trx 看最老的事务

五、把空间真正还回去：三种手段，一个共同代价

想真正缩小物理文件，办法就三类，全部有代价：

1. VACUUM FULL（PG）：重写整张表，锁表独占，写入期间需要额外一倍磁盘空间。几百 GB 的表在生产上跑这个，运维同学需要先把咖啡泡好。

2. pg_repack / pt-online-schema-change（PG / MySQL）：新建一张紧凑表，把数据搬过去，然后原子切换。几乎无锁，是目前处理重度膨胀的生产标准答案。代价是 IO 压力翻倍，需要预先算好磁盘余量。

3. 删表重建（MySQL）：CREATE TABLE t_new LIKE t; INSERT ... SELECT; DROP TABLE t; RENAME;，粗暴但有效，同样要额外空间。

-- pt-osc 典型用法：只做数据迁移，不做结构变更也能用来去膨胀
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --dry-run \
  --execute \
  --chunk-size=1000 \
  D=mydb,t=orders \
  --progress=hours,1

共同代价一句话：都要额外空间和额外 IO。所以真正的解法不是事后收拾，而是事前不让它胖起来。

六、事前：四条实践

1. 定期跑 vacuum，不要指望 autovacuum 救场 大表改 scale_factor，或者上 pg_cron 定时低峰期手动 vacuum。

-- pg_cron 风格的任务定义
SELECT cron.schedule('nightly-vacuum', '0 3 * * *',
  $$VACUUM (ANALYZE, VERBOSE) orders, order_items$$);

2. 把大事务切碎：批处理删数据，100 万行拆成 200 个 5000 行的事务。好处不只是空间，还有 undo 链长度、锁持有时间、binlog 大小全都会跟着降。

-- Go 里分批删除的骨架
func deleteInBatches(ctx context.Context, db *sql.DB, ids []int64, size int) error {
	for start := 0; start < len(ids); start += size {
		end := start + size
		if end > len(ids) { end = len(ids) }
		chunk := ids[start:end]
		q := `DELETE FROM orders WHERE id = ANY($1)`
		if _, err := db.ExecContext(ctx, q, pq.Array(chunk)); err != nil {
			return err
		}
	}
	return nil
}

3. 治理长事务：给 idle in transaction 设超时（PG 用 idle_in_transaction_session_timeout），给连接池加「事务打开时长」监控，把「开了不提交」变成一条告警。

-- PG 侧：超过 5 分钟的事务直接断开
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

4. 归档而不是删除：冷数据别留在热表里。每周把三个月前的订单 DELETE 掉并归档到分区表或历史库，热表规模可控，膨胀自然可控。这是最省事也最容易被忽略的一条。

七、排查速查表

PG 死元组：pg_stat_user_tables.n_dead_tup、pgstattuple 扩展看精确膨胀率
PG 钉子：pg_stat_activity.backend_xmin 非空且事务很老
PG 事务年龄：age(relfrozenxid)，接近上限就要出大事（事务 ID 回卷，PG 会强制停机）
MySQL 历史链：trx_rseg_history_len、innodb_trx 最老事务
磁盘层：du -sh 真实占用 vs 逻辑表大小，差值就是膨胀

八、最后吐槽两句

我最烦的一种技术咨询开场白是：「删了数据磁盘没释放，数据库是不是有 bug？」

兄弟，这不是 bug，这是 MVCC 的设计承诺：并发读的正确性，是拿磁盘空间换来的。你不能既要多版本并发，又不让人家多占地方，这既不科学也不礼貌。

真正该问的问题是：「我们的删除策略，是打算给谁看？」如果是给「磁盘配额」看的，那是错的；应该是给「保留策略」看的——比如订单保留 90 天，那就 90 天一到就删，删完顺手把表结构管理好，膨胀从来不是问题。

顺带一提，任何声称「删除数据会自动释放磁盘」的说法，都值得当场掏出这篇文甩他脸上。数据库的「删除」更像是给尸体发死亡证明，不是火化。这个比喻不太文雅，但它足够准确。

而那些把 DELETE 写成百万行大事务的代码，我只想说：你们不是在删数据，你们是在给数据库挖坟。

你删掉了 100 万行，磁盘却没释放：数据库里的「尸体」不会自己消失