最近在清理 PG 数据库的一些大表时，发现 truncate 和 delete 相关表之后，数据库所占用的磁盘空间并没有减小，于是就去查了下背后的原理。

首先查询数据库中表所占的空间，按照从大到小的顺序排列，确定要处理的表，优先排查大表。

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

一。DELETE 的行为特点

二。TRUNCATE 的行为特点

三。DROP 的行为特点

四。对比总结

五。背后的原因

PostgreSQL 使用的是 MVCC（多版本并发控制），它不会立即删除旧数据，而是：

• 对于每一次 UPDATE / DELETE：
• 原来的行被标记为“死元组”；
• 新的行（或删除标记）会追加在后面；
• 所以表文件会不断膨胀，哪怕你已经“删除”了很多数据。

六。评估回收磁盘空间

1. 整体评估的 SQL 语句

SELECT
  relname AS table,
  pg_total_relation_size(relid) AS total_bytes,
  pg_relation_size(relid) AS data_bytes,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS data_size,
  n_live_tup,
  n_dead_tup,
  round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup + 1), 2) AS dead_tuple_pct,
  round(100.0 * pg_relation_size(relid)::numeric / pg_total_relation_size(relid), 2) AS data_utilization_pct
FROM pg_stat_user_tables
WHERE relname = 'your_table_name';

2.字段释义表

3.评估是否需要回收空间（关键逻辑）：

死元组比例高 → 表示可能需要 VACUUM 或 VACUUM FULL

dead_tuple_ratio = n_dead_tup / (n_live_tup + n_dead_tup)

• 超过 10%：建议手动 VACUUM
• 超过 30% 且表很大：考虑 VACUUM FULL 或表重建

total_size 明显大于 data_size → 表空间“膨胀”

bloat_ratio = data_size / total_size

• 如果 data_size 占比 < 60%，说明表文件中大量空洞未被重用
• 膨胀严重（< 40%），但死元组少 → 考虑 VACUUM FULL 或 DROP + CREATE
• 考虑是索引或 TOAST 膨胀，如果索引过大 → 考虑 REINDEX 或重建索引

综合评估建议表

七。拓展补充

1.表索引、数据、总大小对比

SELECT
  relname AS table_name,
  pg_size_pretty(pg_relation_size(relid)) AS data_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  round(100.0 * pg_indexes_size(relid) / nullif(pg_total_relation_size(relid), 0), 2) AS index_pct
FROM pg_stat_user_tables
WHERE relname = 'biz_stock_pretreatment'
ORDER BY pg_total_relation_size(relid) DESC;

2.查询表索引所占空间大小，及查询时用的次数，对于使用次数过低但占用空间较大的考虑清除

SELECT
  t.relname AS table_name,
  i.relname AS index_name,
  pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
  pg_relation_size(i.oid) AS index_size_bytes,
  idx_stat.idx_scan AS index_scans,
  pg_size_pretty(pg_table_size(t.oid)) AS table_size,
  array_to_string(array_agg(a.attname), ', ') AS index_columns,
  ix.indisunique AS is_unique,
  ix.indisprimary AS is_primary
FROM pg_class t
JOIN pg_index ix ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
LEFT JOIN pg_stat_user_indexes idx_stat ON idx_stat.indexrelid = i.oid
LEFT JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
WHERE t.relkind = 'r'
  AND t.relname = 'biz_stock_pretreatment'
GROUP BY t.relname, i.relname, pg_size_pretty(pg_relation_size(i.oid)), pg_relation_size(i.oid)
       , idx_stat.idx_scan, pg_size_pretty(pg_table_size(t.oid)), ix.indisunique, ix.indisprimary
ORDER BY index_size_bytes DESC;