对齐规则太 “苛刻”，PostgreSQL表变大的 3 个核心原因

对齐规则太 “苛刻”，PostgreSQL表变大的 3 个核心原因

相同的表结构和数据，在商业数据库中存储紧凑，到了PostgreSQL里却会明显变大？ 为什么有些数据库管理员（DBA）在将Oracle、SQL Server和DB2等商业数据库迁移到PostgreSQL后表占用的磁盘空间增加20%-40%？

本文将通过实际示例说明“对齐”与“填充”是如何造成这种差异的。

商业数据库中的行存储机制

SQL Server作为商业数据库，会将行数据存储在8KB大小的页（page）中，每一行的结构包含三部分：

1. 一个小型行头部（4字节，外加一个空值位图）；
2. 按定义顺序排列的所有固定长度列；
3. 通过“偏移数组”（offset array）管理的可变长度列。

注意：SQL Server不对固定长度类型强制执行对齐规则。这意味着你可以在表中先定义一个BIT列、再定义一个BIGINT列、最后再定义一个BIT列，SQL Server会将这些字节紧密排列，不会产生空间浪费。

 

通过实际代码来看具体效果：

-- SQL Server 代码
CREATETABLE T_BadOrder
(
    a BIT,        -- 占用1字节
    b BIGINT,     -- 占用8字节
    c BIT         -- 占用1字节
);

INSERTINTO T_BadOrder VALUES (1, 42, 0);

-- 计算数据占用（payload）大小：结果为10字节
SELECTDATALENGTH(a) + DATALENGTH(b) + DATALENGTH(c) AS payload_bytes
FROM T_BadOrder;

-- 查看物理行的平均大小：结果为16字节（4字节行头部 + 10字节数据载荷 + 2字节空值位图掩码的字段计数）
SELECT avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('T_BadOrder'), -1, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA';

可以看到，数据占用总计10字节，加上行头部和空值位图后，总大小为16字节。这里的重点是：字段a和字段b之间没有插入任何填充字节。

PostgreSQL中的行存储机制

PostgreSQL作为开源数据库，表中的每一行的结构如下：

1. 一个元组头部（tuple header，固定23字节），包含MVCC（多版本并发控制）所需的元数据（如事务ID、可见性标记等）；
2. 一个空值位图；（记录哪些字段是空值，每字段占 1 位，不足 1 字节则补满 1 字节，表如果有8个字段就占用1 字节）。
3. 按定义顺序排列的字段值。

与商业数据库最大的不同是：PostgreSQL会强制要求数据类型对齐，具体规则如下：

• BOOLEAN类型需要1字节对齐；
• SMALLINT类型需要2字节对齐；
• INT类型需要4字节对齐；
• BIGINT、double precision（双精度浮点数）、timestamp（时间戳）类型需要8字节对齐。

如果某一字段的起始位置不符合对应的对齐要求，PostgreSQL会自动插入“填充字节”（padding bytes），将该字段“推”到正确的对齐边界上。我们用跟前文相同的表结构来测试：

-- PostgreSQL 代码
CREATE TABLE t_bad
(
    a boolean,    -- 1字节（后续需补7字节填充）
    b bigint,     -- 8字节（需8字节对齐，因此前一列要补填充）
    c boolean     -- 1字节
);

INSERT INTO t_bad VALUES (true, 42, false);

-- 查看实际行大小：结果为41字节
SELECT pg_column_size(t) AS row_bytes
FROM t_bad t;

在这个例子中，元组头部（23 字节）+ 空值位图（1 字节）= 前 24 字节，PostgreSQL先为字段a分配1字节，然后插入7字节填充，这样字段b才能从8字节对齐边界开始存储；字段b之后是1字节的字段c。仅数据占用的大小就已经超过了SQL Server，还没算上PostgreSQL本身更大的元组头部。

但如果我们根据“填充需求”调整字段的顺序，存储占用会显著减少：

-- PostgreSQL 代码
CREATE TABLE t_good
(
    b bigint,     -- 8字节
    a boolean,    -- 1字节（无需填充！）
    c boolean     -- 1字节
);

INSERT INTO t_good VALUES (42, true, false);

-- 查看实际行大小：结果为34字节（减少了7字节，因为无需为任何列插入填充）
SELECT pg_column_size(t) AS row_bytes
FROM t_good t;

调整后，行大小从41字节降至34字节，核心原因就是字段a不再需要填充字节，因为字段b（8字节）结束后，刚好满足字段a（1字节）的对齐要求。

关键原则：字段的顺序应从“占用空间最大”到“占用空间最小”排列！

8字节对齐规则：bigint类型（8字节）要求其起始位置必须是8的倍数（比如8、16、24、32...）。这是硬件层面的优化。

具体位置计算

我们按字段的定义顺序（a → b → c）逐步计算每个字段的“起始位置”：

1. 字段a（boolean）的位置：
   前24字节是头部和空值位图，所以列a从第24字节开始存储。
   boolean占1字节，因此字段a占用第24字节，结束在第24字节（24→24，共1字节）。

2. 字段b（bigint）的位置：
   字段a结束后，下一个可用的起始位置是第25字节。
   但bigint要求起始位置是8的倍数（8的倍数：8、16、24、32、40...）。  第25字节不是8的倍数（25÷8=3.125，余数1），不符合要求。  因此需要填充字节，直到下一个8的倍数位置。最近的8的倍数是第32字节（8×4=32）。 从第25字节到第32字节，中间有32-25=7字节，这就是需要填充的7字节。所以字段b从第32字节开始，占用8字节（32→39字节）。

3. 字段c（boolean）的位置：
   字段b结束在第39字节，下一个位置是第40字节。
   boolean只需要1字节对齐（任何位置都可以），因此直接从第40字节开始，占用1字节（40→40字节）。

总大小验证

整个行的总大小=头部（23）+ 空值位图（1）+ 字段a（1）+ 填充（7）+ 字段b（8）+ 字段c（1）= 23+1+1+7+8+1=41字节。

调整顺序后不需要填充

如果把bigint（b）放在第一个字段，情况就变了：

• 字段b从第24字节开始（头部23+空值位图1=24），24是8的倍数（24÷8=3），符合bigint的对齐要求，无需填充。
• 字段b占用24→31字节（8字节），之后字段a（boolean）从32字节开始（1字节对齐，无需填充），字段c从33字节开始，总大小减少7字节。

可变长度字段的影响

当表中包含可变长度字段时，情况更有意思。两者的处理差异如下：

• 在SQL Server中，可变长度数据通过行末尾的“偏移数组”管理；
• 在PostgreSQL中，每一个可变长度值（如TEXT、VARCHAR、BYTEA、NUMERIC等）都自带一个4字节的“变长头部”（varlena header）。

我们通过下面两个表的表结构对比来看看实际影响：

-- PostgreSQL 代码：列顺序不合理的表
CREATETABLE bad_order
(
    a boolean,
    b bigint,
    c int,
    d timestamp,
    e smallint,
    f varchar(20),
    g numeric(18,2)
);

-- PostgreSQL 代码：列顺序合理的表
CREATETABLE good_order
(
    b bigint,
    d timestamp,
    c int,
    e smallint,
    a boolean,
    g numeric(18,2),
    f varchar(20)
);

-- 插入100万条测试数据
INSERTINTO bad_order (a,b,c,d,e,f,g)
SELECT
  (i % 2 = 0),  -- 布尔值：true/false交替
  (random()*1e9)::bigint,  -- 随机大整数
  (random()*1e5)::int,     -- 随机整数
  to_timestamp(1420070400 + (random()*1e6)::int),  -- 随机时间戳
  (random()*32000)::int::smallint,  -- 随机小整数
substr(md5(random()::text), 1, (random()*20)::int),  -- 随机长度字符串（1-20字符）
  ((random()*1e7)::bigint)::numeric / 100.0-- 随机数值（保留2位小数）
FROM generate_series(1,1000000) i;  -- 生成1-1000000的序列作为循环变量

-- 将bad_order的数据按合理列顺序插入good_order
INSERTINTO good_order
SELECT b,d,c,e,a,g,f FROM bad_order;

-- 对比两张表的平均行大小
SELECT'bad_order'AS tbl, avg(pg_column_size(t)) AS avg_row_bytes FROM bad_order t
UNIONALL
SELECT'good_order', avg(pg_column_size(t)) FROM good_order t;

实际测试结果显示：bad_order因列顺序不合理和对齐问题，平均每行占用77字节；而good_order将“宽字节固定长度字段”放在前面、“可变长度字段”放在最后，最大限度减少了填充，平均每行仅占用66字节。

为什么PostgreSQL的行存储通常更大？

迁移后PostgreSQL行大小超过商业数据库，主要源于三个核心差异：

1. 元组头部大小：PostgreSQL的行头部包含约23字节的MVCC元数据，而SQL Server仅为4字节；
2. 对齐填充：PostgreSQL会插入填充字节以保证固定长度类型的对齐，而商业数据库则不会；
3. 可变长度字段开销：PostgreSQL中每个可变长度字段都自带4字节的变长头部，商业数据库则无此开销。

PostgreSQL优化存储空间的核心逻辑包括下面几个方面：

• 先放“宽字节固定长度列”（如BIGINT、timestamp）；
• 再放“中等字节固定长度列”（如INT）；
• 接着放“小字节固定长度列”（如SMALLINT、BOOLEAN）；
• 最后放“可变长度列”（如VARCHAR、TEXT、NUMERIC）。

总结

从商业数据库迁移到开源数据库PostgreSQL后表体积变大是为了支撑PostgreSQL的核心特性（MVCC）和跨架构的性能稳定性。这也意味着，我们不能期望商业数据库和开源数据库两者的存储大小完完全全“一一对应”，而且开源数据库跟商业数据库相比起来还是有一定的差距。

 

本文版权归作者所有，未经作者同意不得转载。