商业数据库中的行存储机制SQL Server作为商业数据库会将行数据存储在8KB大小的页page中每一行的结构包含三部分一个小型行头部4字节外加一个空值位图按定义顺序排列的所有固定长度列通过“偏移数组”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作为开源数据库表中的每一行的结构如下一个元组头部tuple header固定23字节包含MVCC多版本并发控制所需的元数据如事务ID、可见性标记等一个空值位图记录哪些字段是空值每字段占 1 位不足 1 字节则补满 1 字节表如果有8个字段就占用1 字节。按定义顺序排列的字段值。与商业数据库最大的不同是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不再需要填充字节因为字段b8字节结束后刚好满足字段a1字节的对齐要求。关键原则字段的顺序应从“占用空间最大”到“占用空间最小”排列8字节对齐规则bigint类型8字节要求其起始位置必须是8的倍数比如8、16、24、32...。这是硬件层面的优化。具体位置计算我们按字段的定义顺序a → b → c逐步计算每个字段的“起始位置”字段aboolean的位置前24字节是头部和空值位图所以列a从第24字节开始存储。boolean占1字节因此字段a占用第24字节结束在第24字节24→24共1字节。字段bbigint的位置字段a结束后下一个可用的起始位置是第25字节。但bigint要求起始位置是8的倍数8的倍数8、16、24、32、40...。 第25字节不是8的倍数25÷83.125余数1不符合要求。 因此需要填充字节直到下一个8的倍数位置。最近的8的倍数是第32字节8×432。 从第25字节到第32字节中间有32-257字节这就是需要填充的7字节。所以字段b从第32字节开始占用8字节32→39字节。字段cboolean的位置字段b结束在第39字节下一个位置是第40字节。boolean只需要1字节对齐任何位置都可以因此直接从第40字节开始占用1字节40→40字节。总大小验证整个行的总大小头部23 空值位图1 字段a1 填充7 字段b8 字段c1 231178141字节。调整顺序后不需要填充如果把bigintb放在第一个字段情况就变了字段b从第24字节开始头部23空值位图12424是8的倍数24÷83符合bigint的对齐要求无需填充。字段b占用24→31字节8字节之后字段aboolean从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; -- 对比两张表的平均行大小 SELECTbad_orderAS tbl, avg(pg_column_size(t)) AS avg_row_bytes FROM bad_order t UNIONALL SELECTgood_order, avg(pg_column_size(t)) FROM good_order t;实际测试结果显示bad_order因列顺序不合理和对齐问题平均每行占用77字节而good_order将“宽字节固定长度字段”放在前面、“可变长度字段”放在最后最大限度减少了填充平均每行仅占用66字节。为什么PostgreSQL的行存储通常更大迁移后PostgreSQL行大小超过商业数据库主要源于三个核心差异元组头部大小PostgreSQL的行头部包含约23字节的MVCC元数据而SQL Server仅为4字节对齐填充PostgreSQL会插入填充字节以保证固定长度类型的对齐而商业数据库则不会可变长度字段开销PostgreSQL中每个可变长度字段都自带4字节的变长头部商业数据库则无此开销。PostgreSQL优化存储空间的核心逻辑包括下面几个方面先放“宽字节固定长度列”如BIGINT、timestamp再放“中等字节固定长度列”如INT接着放“小字节固定长度列”如SMALLINT、BOOLEAN最后放“可变长度列”如VARCHAR、TEXT、NUMERIC。总结从商业数据库迁移到开源数据库PostgreSQL后表体积变大是为了支撑PostgreSQL的核心特性MVCC和跨架构的性能稳定性。这也意味着我们不能期望商业数据库和开源数据库两者的存储大小完完全全“一一对应”而且开源数据库跟商业数据库相比起来还是有一定的差距。