PostgreSQL修改列类型后的数据对齐、行存储及优化疑问

场景背景 #

我参考《Calculating and saving space in PostgreSQL》优化了表t2的列顺序以减少填充，之后将部分boolean类型列改为smallint类型，发现列顺序未变，但wind_direction列因不再符合4字节对齐规则产生了填充。针对该场景，以下是相关问题的解答：

1. PostgreSQL如何处理因行占用空间增大需更新的所有行？ #

当执行ALTER COLUMN TYPE导致行尺寸增大时，PostgreSQL会重写表中每一行数据：

• 原行的存储空间无法容纳转换后的数据，数据库会为每行分配新的物理存储位置，写入转换后的新数据；
• 旧行会被标记为“死元组”，后续由VACUUM（或AUTOVACUUM）进程清理回收空间；
• 操作全程会持有表的ACCESS EXCLUSIVE排他锁，在重写完成前，除少数维护操作外，其他读写请求都会被阻塞。

2. ALTER COLUMN TYPE与手动创建新表并迁移数据的区别 #

两者核心差异体现在锁机制、一致性、空间利用和操作复杂度上：

• 锁影响：ALTER COLUMN TYPE全程持有排他锁，大表操作会导致长时间业务阻塞；手动迁移可通过分批插入、INSERT ... SELECT配合低级别锁（或并行导出导入），大幅缩短阻塞时间。
• 数据一致性：ALTER COLUMN TYPE是原子操作，要么全部完成要么回滚，无中间不一致状态；手动迁移需自行处理增量数据（如迁移期间的新写入），否则可能丢失数据，需额外通过触发器、停写等方式保障一致性。
• 磁盘空间：ALTER COLUMN TYPE会产生大量死元组，需额外磁盘空间存储新旧行，直到VACUUM清理；手动迁移直接写入新表，原表可在验证后删除，空间利用更可控。
• 操作复杂度：ALTER COLUMN TYPE仅需单条命令，操作简单；手动迁移需完成建表、数据导入、索引/约束重建等多步骤，操作繁琐但灵活性更高（可同时调整列顺序、优化存储）。

3. PostgreSQL是否有相关内置优化？ #

PostgreSQL针对这类场景提供了部分优化，但存在局限性：

• 轻量级类型变更：仅当类型变更为无需重写数据的“安全转换”（如text转varchar且未修改长度限制）时，可避免全表重写；但boolean转smallint这类需要计算转换的操作，必须全表重写。
• 自动对齐处理：PostgreSQL会自动按数据类型的对齐规则分配填充，但列类型变更导致对齐规则变化时，不会自动调整列顺序优化空间——若要重新优化，需手动通过建表迁移的方式调整列顺序。
• 死元组清理：重写表产生的死元组会由AUTOVACUUM自动清理；若需立即回收空间，可手动执行VACUUM FULL（但该操作同样会持有排他锁）。

实验代码 #

create table t2(
    ts timestamptz NOT NULL,
    is_hot boolean NOT NULL,
    is_windy boolean NOT NULL,
    humidity smallint NOT NULL,
    wind_direction real NOT NULL
);
-- 填充数据
alter table t2
    alter column is_hot type smallint using CASE WHEN is_hot THEN 1 ELSE 0 END;

内容的提问来源于stack exchange，提问作者Hannes Kindströmmer