PostGIS大表更新后磁盘膨胀恢复及空间占用优化咨询

需要更新PostGIS数据库中cj_geometry表的groundgeometry字段，使用的SQL语句如下：

UPDATE 
  cj_geometry 
SET 
  groundgeometry = CASE WHEN NOT ST_IsValid(groundgeometry) THEN ST_Multi(
    ST_CollectionExtract(
      ST_MakeValid(groundgeometry), 
      3
    )
  ) ELSE ST_MakeValid(groundgeometry) END

该表约有6500万条数据，执行约1小时后因磁盘空间耗尽终止查询，重启数据库后，数据库体积大幅膨胀但未新增数据。现咨询两个问题：

1. 如何将数据库恢复至原有大小？
2. 该更新操作为何占用大量磁盘空间，如何避免？

1. 恢复数据库至原有大小 #

数据库膨胀主要来自未清理的WAL日志、UPDATE产生的死元组，以及未释放的临时文件，按以下步骤处理：

步骤1：强制触发检查点，清理WAL日志 #

首先执行检查点，将内存中的脏页写入磁盘，并标记可回收的WAL日志：

CHECKPOINT;

如果开启了WAL归档，确保归档进程已完成所有未归档的日志，之后可以用pg_archivecleanup工具清理pg_wal目录下已归档的旧日志：

pg_archivecleanup -d $PGDATA/pg_wal/ 0000000100000000000000XX

（替换0000000100000000000000XX为实际的日志文件名前缀）

若未开启归档，可临时调整wal_keep_segments参数（需重启数据库）减少保留的WAL数量，之后再次执行CHECKPOINT释放空间。

步骤2：清理表中的死元组 #

终止的UPDATE会在表中留下大量死元组，需要清理：

• 若磁盘空间足够，执行VACUUM FULL重写表，彻底回收空间（注意：该操作会锁表，执行期间表无法读写）：

VACUUM FULL cj_geometry;

PostgreSQL 12+版本可添加并行参数加速：

VACUUM (FULL, PARALLEL 4) cj_geometry;

• 若磁盘空间不足，推荐使用pg_repack工具（需提前安装），它无需锁表且仅需少量额外空间即可重写表回收空间：

pg_repack -d your_db_name -t cj_geometry

步骤3：清理临时文件 #

检查数据库数据目录下的pg_temp目录，手动删除未自动清理的临时文件（确保数据库已重启，无活跃事务）。

2. 更新操作占用大量磁盘空间的原因及避免方法 #

原因分析 #

1. PostgreSQL UPDATE机制：PostgreSQL中UPDATE并非直接修改原数据，而是标记旧元组为删除，再插入新元组。6500万条数据的全表更新会产生等量的死元组，这些死元组在被清理前会持续占用磁盘空间。
2. WAL日志堆积：所有数据变更都会写入WAL日志以保证崩溃恢复，全表更新会生成海量WAL日志；事务终止后，数据库需保留相关WAL直到完成检查点，进一步加剧空间占用。
3. 几何处理额外开销：ST_MakeValid、ST_CollectionExtract等函数会生成新的几何对象，部分修复后的几何数据体积可能比原数据更大；同时处理大量几何数据时，数据库可能生成临时文件存储中间结果。

避免方法 #

方法1：分批更新，减少一次性空间占用

不要全表一次性更新，按主键/ID范围分批处理，每批更新后立即清理死元组和WAL：

-- 示例：每次更新10000条，循环执行直到无行更新
WITH batch AS (
  SELECT id FROM cj_geometry
  WHERE (NOT ST_IsValid(groundgeometry) OR ST_MakeValid(groundgeometry) <> groundgeometry)
  LIMIT 10000
  FOR UPDATE SKIP LOCKED
)
UPDATE cj_geometry g
SET groundgeometry = CASE 
  WHEN NOT ST_IsValid(g.groundgeometry) THEN ST_Multi(ST_CollectionExtract(ST_MakeValid(g.groundgeometry), 3))
  ELSE ST_MakeValid(g.groundgeometry)
END
FROM batch b
WHERE g.id = b.id;

-- 每批更新后执行检查点和清理
CHECKPOINT;
VACUUM cj_geometry;

方法2：缩小更新范围，只处理需要变更的行

原SQL会更新所有行，即使几何数据已经有效且无需修改。添加WHERE条件仅更新实际需要修复的行：

UPDATE 
  cj_geometry 
SET 
  groundgeometry = CASE WHEN NOT ST_IsValid(groundgeometry) THEN ST_Multi(
    ST_CollectionExtract(
      ST_MakeValid(groundgeometry), 
      3
    )
  ) ELSE ST_MakeValid(groundgeometry) END
-- 仅更新几何无效或修复后有变化的行
WHERE NOT ST_IsValid(groundgeometry) OR ST_MakeValid(groundgeometry) <> groundgeometry;

方法3：使用新表替换原表，避免死元组

创建新表存储修复后的数据，再替换原表，这种方式不会产生死元组：

-- 创建新表，复制原表结构和约束
CREATE TABLE cj_geometry_new (LIKE cj_geometry INCLUDING ALL);

-- 分批插入修复后的数据（避免一次性占用过多空间）
INSERT INTO cj_geometry_new
SELECT 
  id,
  CASE WHEN NOT ST_IsValid(groundgeometry) THEN ST_Multi(ST_CollectionExtract(ST_MakeValid(groundgeometry), 3))
       ELSE ST_MakeValid(groundgeometry)
  END AS groundgeometry,
  -- 其他字段原样复制
  other_column1, other_column2
FROM cj_geometry
WHERE id BETWEEN 1 AND 1000000;

-- 所有数据插入完成后，原子性交换表名
BEGIN;
ALTER TABLE cj_geometry RENAME TO cj_geometry_old;
ALTER TABLE cj_geometry_new RENAME TO cj_geometry;
COMMIT;

-- 验证数据无误后删除旧表
DROP TABLE cj_geometry_old;

方法4：调整数据库参数减少临时开销

• 增大work_mem和maintenance_work_mem参数，减少几何处理时临时文件的生成（会话级别临时调整，仅对当前会话生效）：

SET work_mem = '64MB';
SET maintenance_work_mem = '256MB';

• 若无需严格的崩溃恢复（不推荐生产环境），可临时设置wal_level = minimal减少WAL日志量，但需重启数据库，操作完成后需改回原参数。

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