PostgreSQL中ALTER命令执行后占用空间的定位与清理咨询

一、空间定位与清理步骤 #

1. 排查当前空间使用情况 #

先明确数据库及各表的空间占用，定位异常占用：

-- 查看当前数据库总占用空间
SELECT pg_size_pretty(pg_database_size(current_database()));

-- 按大小排序查看所有用户表的总占用（含索引、TOAST表）
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

2. 清理ALTER TABLE失败残留的临时表 #

执行ALTER TABLE ... ALTER COLUMN TYPE时，PostgreSQL会创建表的临时副本用于重写数据，若操作中途终止，部分临时表可能残留（命名多以pg_temp_或pg_toast_temp_开头）：

-- 查找所有临时表及占用空间
SELECT c.relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'pg_temp' OR c.relname LIKE 'pg_toast_temp_%';

找到残留的临时表后，直接删除：

DROP TABLE IF EXISTS pg_temp_XXXXXX; -- 替换为实际查到的临时表名

3. 清理未归档的WAL日志 #

ALTER操作会生成大量WAL日志，若未及时归档会占用空间：

-- 检查待归档的WAL大小
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_replay_lsn())) AS pending_wal;

确认归档完成后，手动触发检查点并切换WAL，清理旧日志：

SELECT pg_switch_wal();
SELECT pg_checkpoint();

4. 回收原表的膨胀空间 #

操作中断后原表可能产生数据膨胀，执行VACUUM回收空闲空间：

VACUUM VERBOSE table_name;

若需要彻底回收空间（需预留少量临时空间），可执行：

VACUUM FULL VERBOSE table_name;

二、优化列类型修改方案（避免空间耗尽） #

直接执行ALTER TABLE ... ALTER COLUMN TYPE会一次性重写全表，对超大表不友好，建议采用分批迁移方案：

1. 添加新的BIGINT列并填充数据

-- 添加新列，避免默认值锁表，采用分批更新
ALTER TABLE table_name ADD COLUMN id_new BIGINT;

-- 分批更新（每次处理10000行，循环执行直到无NULL值）
WITH batch AS (
    SELECT id FROM table_name WHERE id_new IS NULL LIMIT 10000
)
UPDATE table_name SET id_new = id WHERE id IN (SELECT id FROM batch);

2. 同步索引与约束

-- 复制原id列的索引（如果存在）
CREATE INDEX idx_table_name_id_new ON table_name(id_new);

-- 若原id是主键，先删除旧主键，再将新列设为主键
ALTER TABLE table_name DROP CONSTRAINT pk_table_name_id;
ALTER TABLE table_name ADD CONSTRAINT pk_table_name_id PRIMARY KEY(id_new);

3. 切换列名并清理旧列

-- 交换列名，让新列替代原id的角色
ALTER TABLE table_name RENAME COLUMN id TO id_old;
ALTER TABLE table_name RENAME COLUMN id_new TO id;

-- 确认业务正常后，删除旧列
ALTER TABLE table_name DROP COLUMN id_old;

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