原文作者： depesz
翻译：Maleah
最近在IRC，Slack和Reddit看到许多讨论表明，人们认为使用int4/integer的字节数比int8/integer少4个字节。事实并非如此，让我来解释原因。
首先快速的测试。在我64位的电脑上运行一些Ryzen处理器：
$ select version();
version
────────────────────────────────────────────────────────────────────────────────────────────────────
PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-7ubuntu2) 11.2.0, 64-bit
(1 row)
$ create table test8 (id int8);
CREATE TABLE
$ create table test4 (id int4);
CREATE TABLE
$ insert into test8 select generate_series(1,1000000) i;
INSERT 0 1000000
$ insert into test4 select generate_series(1,1000000) i;
INSERT 0 1000000
$ \dt+ test*
List of relations
Schema │ Name │ Type │ Owner │ Persistence │ Access method │ Size │ Description
────────┼───────┼───────┼────────┼─────────────┼───────────────┼───────┼─────────────
public │ test4 │ table │ depesz │ permanent │ heap │ 35 MB │
public │ test8 │ table │ depesz │ permanent │ heap │ 35 MB │
(2 rows)
在单列的int4/int8数据类型上插入1百万行，结果表的大小是完全相同的！
在32位的系统上也测试下：
postgres=# select version();
version
-------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.5 (Debian 13.5-0+deb11u1) on i686-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 32-bit
(1 row)
...
postgres=# \dt+ test*
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+----------+-------------+-------+-------------
public | test4 | table | postgres | permanent | 31 MB |
public | test8 | table | postgres | permanent | 35 MB |
(2 rows)
有趣的是，在这种情况下，表的大小减少了4MB。它“应该”是多少呢？但是仍然有27MB的数据“下落不明”，不能解释在我正常的电脑中为什么使用int4和int8占用相同的空间。
为什么会这样？是什么让它以这种方式工作的？
答案是：性能。由于性能原因，PG将数据对齐到和“架构相关”大小。意味着，在64位的计算机上，对齐是8个字节。
究竟什么是对齐？对齐意味着最小的分配单位是8个字节，并且假如技术上可行的话，PG将不会将单个值拆分为大于8字节的块。
这意味着：假如在你的表中是4字节的列，无论如何都会使用8字节。如果你已经有两个int4的列，它们都适合8个字节，只使用这一个。
但是-如果你有int4，下一列的长度超过4个字节-PG不会将另一列拆分为“一部分在前一个8字节块的4个字节中，一部分在下一个”-一切都将进入下一个8B块中。
我们也可以通过一些简单的测试（从现在开始所有的测试都是运行在64位计算机）观察：
$ create table test as select i::int4 as i1, i::int4 as i2 from generate_series(1,1000000) i;
SELECT 1000000
$ \dt+ test
List of relations
Schema │ Name │ Type │ Owner │ Persistence │ Access method │ Size │ Description
────────┼──────┼───────┼────────┼─────────────┼───────────────┼───────┼─────────────
public │ test │ table │ depesz │ permanent │ heap │ 35 MB │
(1 row)
在这里你可以看到，当我创建两列int4，它和单列int8使用相同的空间。现在让我们猜一下当使用int4+int8列创建表时会发生什么？如果我的计算正确的话，应该使用~43MB：
$ create table test48 as select i::int4 as i1, i::int8 as i2 from generate_series(1,1000000) i;
SELECT 1000000
$ \dt+ test48
List of relations
Schema │ Name │ Type │ Owner │ Persistence │ Access method │ Size │ Description
────────┼────────┼───────┼────────┼─────────────┼───────────────┼───────┼─────────────
public │ test48 │ table │ depesz │ permanent │ heap │ 42 MB │
(1 row)
有些许差别，但是正如你看到的和我预测的还是挺接近的。
你也可以看到对于更短的类型来说也是相同的情况。除非它们可以适合在先前或者之后的列中，否则INT2 (2 byte)或者BOOL (1 byte)也将会占用整个8字节：
$ create table test2 as select (i % 32000)::int2 as i2 from generate_series(1,1000000) i;
SELECT 1000000
$ \dt+ test2
List of relations
Schema │ Name │ Type │ Owner │ Persistence │ Access method │ Size │ Description
────────┼───────┼───────┼────────┼─────────────┼───────────────┼───────┼─────────────
public │ test2 │ table │ depesz │ permanent │ heap │ 35 MB │
(1 row)
$ create table testb as select 'true'::bool as b from generate_series(1,1000000) i;
SELECT 1000000
$ \dt+ testb
List of relations
Schema │ Name │ Type │ Owner │ Persistence │ Access method │ Size │ Description
────────┼───────┼───────┼────────┼─────────────┼───────────────┼───────┼─────────────
public │ testb │ table │ depesz │ permanent │ heap │ 35 MB │
(1 row)
一共是35MB。
为什么会这样？从我收集到的答案来看是：性能。我不清楚更底层的细节，但是基于我的理解，处理器依据架构块的大小来处理数据。64位的处理器64个字节上工作。这意味着如果你想在int4的值（即8字节块的一部分）执行某些操作，你必须增加操作使其它32位归零。
区别太小了很容易操作，并且将会被负载中的随机波动相形见绌。高负载机器可能会有不同。
所以，通过转换成更短字节的数据类型使用更少的磁盘空间是可能的。但是你必须对表的列的顺序非常小心。当设计表时或许是可能的或者容易的，但是一旦你部署了app就不再合理，并且现在你改变schema来容纳新的功能。
还有一个因素需要去考虑。请注意1百万的数据行，在表中插入8MB的数据，表的大小是35MB。其余的是什么呢？
除了正常可见的列，在PostgreSQL中每一行都有一些额外的系统列字段。并且它们占用空间。我们可以看到：
$ SELECT
a.attname,
t.typname,
t.typlen,
a.attnum
FROM
pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
WHERE
a.attrelid = 'test8'::regclass
ORDER BY
attnum;
attname │ typname │ typlen │ attnum
──────────┼─────────┼────────┼────────
tableoid │ oid │ 4 │ -6
cmax │ cid │ 4 │ -5
xmax │ xid │ 4 │ -4
cmin │ cid │ 4 │ -3
xmin │ xid │ 4 │ -2
ctid │ tid │ 6 │ -1
id │ int8 │ 8 │ 1
(7 rows)
每一行都有tableoid, cmax, xmax, cmin 和 ctid（在数据文件中，tableoid和ctid不是真实存在的）。我们在表中的“真实”的列有：https://paste.depesz.com/s/77i
看到这些信息是可能的：
$ select tableoid, cmax, xmax, cmin, xmin, ctid, id from test8 limit 1;
tableoid │ cmax │ xmax │ cmin │ xmin │ ctid │ id
──────────┼──────┼──────┼──────┼──────┼───────┼────
307696 │ 0 │ 0 │ 0 │ 1773 │ (0,1) │ 1
(1 row)
你也可以在文档中找到所有这些列的描述，但是通常情况下不用担心它们。
事实是在数据文件中有许多内容（比如checksums），意味着你可以通过将int8类型转化成int4类型“节省”下来的每行4个字节是微不足道的。
从好的方面来说-如果你已经向表中增加了一个4字节的列，紧接着增加另一个4字节的列（磁盘空间方面）基本上是免费的。
让我们看一些真实的生活表：
=> select 'col_' || a.attnum, a.atttypid::regtype, a.attlen from pg_attribute a where a.attrelid = 'accounts'::regclass and attnum > 0;
?column? │ atttypid │ attlen
══════════╪═════════════════════════════╪════════
col_1 │ bigint │ 8
col_2 │ text │ -1
col_3 │ timestamp without time zone │ 8
col_4 │ timestamp without time zone │ 8
col_5 │ text │ -1
col_6 │ timestamp without time zone │ 8
col_7 │ bigint │ 8
col_8 │ text │ -1
col_9 │ bigint │ 8
col_10 │ bigint │ 8
col_11 │ bigint │ 8
col_12 │ bigint │ 8
col_13 │ text │ -1
col_14 │ text │ -1
col_15 │ text │ -1
col_16 │ bigint │ 8
col_17 │ bigint │ 8
col_18 │ boolean │ 1
col_19 │ text │ -1
col_20 │ text │ -1
col_21 │ text │ -1
col_22 │ text │ -1
col_23 │ text │ -1
col_24 │ text │ -1
col_25 │ boolean │ 1
col_26 │ boolean │ 1
col_27 │ text │ -1
col_28 │ text │ -1
col_29 │ text │ -1
col_30 │ text │ -1
col_31 │ text │ -1
col_32 │ bigint │ 8
col_33 │ bigint │ 8
col_34 │ text │ -1
col_35 │ bigint │ 8
col_36 │ text │ -1
col_37 │ text │ -1
col_38 │ text │ -1
col_39 │ text │ -1
col_40 │ bigint │ 8
col_41 │ text │ -1
col_42 │ bigint │ 8
col_43 │ bigint │ 8
(43 rows)
attnum > 0的条件是隐藏系统列。Attlen = -1意味着数据的长度是随着数据的量的改变而变化。
由于这些文本，估计行的大小是复杂的，但是让我们假设一行只占据了8字节的块。鉴于列的排序只有25和26列可以在单个8字节的块中。所以总共的行大小（不包括系统数据）是42 * 8 bytes = 336 bytes。
现在，如果你已经将每个int8改为int4，我可以将第9-12列放到2个8字节的块中，16-17列放到一个，对于32、33和42、43也一样。总共：296字节。这意味着我每行可以节省40字节。请注意我假设23个文本列不会超过8个字节。猜猜它的可能性有多大🙂。
也可以通过简单的运行explain看到你的列实际上（平均）的宽度：
=> explain select * from accounts;
QUERY PLAN
═══════════════════════════════════════════════════════════════════
Seq Scan on accounts (cost=0.00..3979.23 rows=100323 width=1113)
(1 row)
每一行都超过1KB。在此节省40个字节或多或少等于舍入误差。
所以在某些情况下，使用int4/int2是有益的。可以通过使用较小的数据类型来节省一些磁盘空间。但是差异并没有那么大，需要仔细规划，可能会不切实际或者甚至不可能，并且假如较小的整型范围成为问题，将来会导致问题。
最后一点-当我在写int*列时，对于float4/float8（又名float/double）也同样适用。
2022-02-13
