PostgreSQL如何重新初始化空间占用异常的超大表

以下方案针对不需要保留表内现有数据、仅需将表重置为空可用状态的场景，按操作成本从低到高排序：

• 前置排查：先解决操作阻塞问题
  你之前执行VACUUM FULL、REINDEX、TRUNCATE全部无响应，90%以上原因是存在长事务、闲置复制槽或未提交的两阶段事务持有表级共享锁，阻塞了需要排他锁的DDL操作。先执行以下SQL定位阻塞源：

  -- 查询持有目标表锁的阻塞会话
  SELECT
    blocking.pid AS block_pid,
    blocking.query AS block_sql,
    blocking.xact_start AS block_xact_start,
    blocking.state AS block_state
  FROM pg_stat_activity blocking
  JOIN pg_locks block_lock ON blocking.pid = block_lock.pid
  WHERE block_lock.relation = '替换为你的目标表名'::regclass
    AND block_lock.granted = true
    AND block_lock.mode IN ('AccessShareLock','RowShareLock','RowExclusiveLock');
  

  对查询到的运行时长超过1小时、状态为idle in transaction的会话，和业务确认无影响后执行SELECT pg_terminate_backend(上面查到的block_pid);终止。清理完阻塞后可直接尝试执行TRUNCATE TABLE 替换为你的目标表名 RESTART IDENTITY;，如果能瞬间执行完成，后续无需做其他操作。

• 方案1：无停机锁风险的同名表替换法（推荐优先使用，无需停库）
  如果清理阻塞会话后TRUNCATE仍然卡住，直接用新空表替换：

  1. 基于现有目标表创建结构完全一致的空表，包含所有索引、约束、默认值、触发器配置：

     CREATE TABLE 替换为你的目标表名_new (LIKE 替换为你的目标表名 INCLUDING ALL);
     

  2. 在短事务内执行表名切换，锁表时间仅毫秒级，业务几乎无感知：

     BEGIN;
     ALTER TABLE 替换为你的目标表名 RENAME TO 目标表名_bak;
     ALTER TABLE 替换为你的目标表名_new RENAME TO 替换为你的目标表名;
     COMMIT;
     

  3. 切换完成后业务访问的就是全新的空表，旧的膨胀表目标表名_bak可以在业务低峰期执行DROP TABLE 目标表名_bak;删除，删除时如果仍有阻塞，重复前面的阻塞会话排查步骤即可。

• 方案2：停库级强制重置（适用于锁问题完全无法排查、需要最快恢复业务的场景）
  注意：操作前必须做全库物理备份，操作需要停止数据库服务，全程预计5-10分钟

  1. 执行CHECKPOINT;强制刷脏页，然后用fast模式停止PG服务：pg_ctl stop -D 你的PG数据目录路径 -m fast
  2. 启动psql单用户模式连接目标数据库，查询目标表的物理文件路径：

     SELECT pg_relation_filepath('替换为你的目标表名');
     

     例如返回结果为base/13591/24579，对应数据目录下$PGDATA/base/13579/路径下所有前缀为24579的文件（就是你看到的227个1GB分片文件，以及对应的_fsm、_vm后缀文件、该表所有索引的关联文件）
  3. 将上述所有匹配的大文件移动到备份目录（不要直接删除，留回滚余地），然后在原路径创建一个权限正确的空主文件：

     touch $PGDATA/base/13591/24579
     chown postgres:postgres $PGDATA/base/13591/24579
     chmod 600 $PGDATA/base/13591/24579
     

  4. 正常启动PostgreSQL服务，连接后执行TRUNCATE TABLE 替换为你的目标表名 RESTART IDENTITY;，此时底层文件为空，TRUNCATE会瞬间完成，验证表读写正常、大小符合预期后，再删除之前移动到备份目录的大文件。

问题根因说明：42万条记录占用227GB空间属于极端表膨胀，通常是因为库内存在数天以上未提交的长事务、未释放的逻辑复制槽、未结束的两阶段提交事务，导致dead tuple无法被常规VACUUM清理，持续占用存储空间。重置完成后建议监控长事务运行时长，设置idle_in_transaction_session_timeout参数自动清理闲置超时事务，避免再次出现同类问题。

内容的提问来源于stack exchange，提问作者Arnaud_Jean