Postgres通过两表创建的新表磁盘占用远小于原表是否正常？

你的解释是否合理？ #

完全合理。PostgreSQL中删除列的操作并非立即从磁盘上移除旧列的数据，只是将这些列标记为不可访问，旧数据仍会占据磁盘空间；再加上表在日常增删改操作中产生的碎片化（比如更新、删除行留下的空间空洞），就会导致原表的磁盘占用远大于实际有效数据的大小。

而你通过SELECT + INNER JOIN创建的新表，只包含查询指定的有效列和关联后的有效行，没有冗余数据和碎片化，所以尺寸远小于原表总和。执行VACUUM FULL后原表大幅缩小，也直接印证了这一点——该命令会彻底重建表，回收所有碎片化空间和标记为删除的列数据，让表的磁盘占用匹配实际有效数据量。

如何验证新表未丢失数据？ #

可以通过以下几种方式验证：

• 核对行数：执行SELECT COUNT(*) FROM 新表，再执行SELECT COUNT(*) FROM 表A INNER JOIN 表B ON 你的关联条件，两者结果应完全一致。
• 抽样对比数据：随机选取若干关键行（比如通过主键或唯一标识），分别查询原表关联结果和新表数据，确认内容完全匹配。例如：

  -- 原表关联结果
  SELECT * FROM 表A JOIN 表B ON 表A.id = 表B.a_id WHERE 表A.id = 123;
  -- 新表对应行
  SELECT * FROM 新表 WHERE id = 123;
  

• 聚合值校验：对数值型、字符型字段做聚合统计，对比原表关联结果和新表的统计值是否一致。例如：

  -- 原表聚合
  SELECT SUM(数值列), COUNT(非空列), MAX(日期列) FROM 表A JOIN 表B ON 你的关联条件;
  -- 新表聚合
  SELECT SUM(数值列), COUNT(非空列), MAX(日期列) FROM 新表;
  

如何进行PostgreSQL磁盘空间优化？ #

• 使用VACUUM FULL回收空间：如你已执行的操作，它会重建表并彻底释放冗余空间，但注意该操作会锁表，适合业务低峰期执行。
• 日常使用普通VACUUM：不带FULL的VACUUM不会锁表，会回收碎片化空间并标记为可复用（不会立即还给操作系统），适合日常维护，建议配合自动清理（autovacuum）开启。
• 启用表压缩：PostgreSQL 12及以上版本支持表级压缩，创建表或修改表时可开启：

  -- 修改现有表开启压缩
  ALTER TABLE 表名 SET (compression = 'lz4');
  -- 创建新表时开启压缩
  CREATE TABLE 新表名 (...) WITH (compression = 'lz4');
  

  压缩能显著减少磁盘占用，同时对查询性能影响较小。
• 考虑分区表：如果数据量极大，可将大表拆分为分区表，不仅能优化空间利用，还能提升查询和维护效率。

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