原文作者： depesz
翻譯：Maleah
最近在IRC，Slack和Reddit看到許多討論表明，人們認(rèn)為使用int4/integer的字節(jié)數(shù)比int8/integer少4個(gè)字節(jié)。事實(shí)并非如此，讓我來(lái)解釋原因。
首先快速的測(cè)試。在我64位的電腦上運(yùn)行一些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數(shù)據(jù)類型上插入1百萬(wàn)行，結(jié)果表的大小是完全相同的！
在32位的系統(tǒng)上也測(cè)試下：
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。它“應(yīng)該”是多少呢？但是仍然有27MB的數(shù)據(jù)“下落不明”，不能解釋在我正常的電腦中為什么使用int4和int8占用相同的空間。
為什么會(huì)這樣？是什么讓它以這種方式工作的？
答案是：性能。由于性能原因，PG將數(shù)據(jù)對(duì)齊到和“架構(gòu)相關(guān)”大小。意味著，在64位的計(jì)算機(jī)上，對(duì)齊是8個(gè)字節(jié)。
究竟什么是對(duì)齊？對(duì)齊意味著最小的分配單位是8個(gè)字節(jié)，并且假如技術(shù)上可行的話，PG將不會(huì)將單個(gè)值拆分為大于8字節(jié)的塊。
這意味著：假如在你的表中是4字節(jié)的列，無(wú)論如何都會(huì)使用8字節(jié)。如果你已經(jīng)有兩個(gè)int4的列，它們都適合8個(gè)字節(jié)，只使用這一個(gè)。
但是-如果你有int4，下一列的長(zhǎng)度超過(guò)4個(gè)字節(jié)-PG不會(huì)將另一列拆分為“一部分在前一個(gè)8字節(jié)塊的4個(gè)字節(jié)中，一部分在下一個(gè)”-一切都將進(jìn)入下一個(gè)8B塊中。
我們也可以通過(guò)一些簡(jiǎn)單的測(cè)試（從現(xiàn)在開(kāi)始所有的測(cè)試都是運(yùn)行在64位計(jì)算機(jī)）觀察：
$ 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)
在這里你可以看到，當(dāng)我創(chuàng)建兩列int4，它和單列int8使用相同的空間。現(xiàn)在讓我們猜一下當(dāng)使用int4+int8列創(chuàng)建表時(shí)會(huì)發(fā)生什么？如果我的計(jì)算正確的話，應(yīng)該使用~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)
有些許差別，但是正如你看到的和我預(yù)測(cè)的還是挺接近的。
你也可以看到對(duì)于更短的類型來(lái)說(shuō)也是相同的情況。除非它們可以適合在先前或者之后的列中，否則INT2 (2 byte)或者BOOL (1 byte)也將會(huì)占用整個(gè)8字節(jié)：
$ 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。
為什么會(huì)這樣？從我收集到的答案來(lái)看是：性能。我不清楚更底層的細(xì)節(jié)，但是基于我的理解，處理器依據(jù)架構(gòu)塊的大小來(lái)處理數(shù)據(jù)。64位的處理器64個(gè)字節(jié)上工作。這意味著如果你想在int4的值（即8字節(jié)塊的一部分）執(zhí)行某些操作，你必須增加操作使其它32位歸零。
區(qū)別太小了很容易操作，并且將會(huì)被負(fù)載中的隨機(jī)波動(dòng)相形見(jiàn)絀。高負(fù)載機(jī)器可能會(huì)有不同。
所以，通過(guò)轉(zhuǎn)換成更短字節(jié)的數(shù)據(jù)類型使用更少的磁盤(pán)空間是可能的。但是你必須對(duì)表的列的順序非常小心。當(dāng)設(shè)計(jì)表時(shí)或許是可能的或者容易的，但是一旦你部署了app就不再合理，并且現(xiàn)在你改變schema來(lái)容納新的功能。
還有一個(gè)因素需要去考慮。請(qǐng)注意1百萬(wàn)的數(shù)據(jù)行，在表中插入8MB的數(shù)據(jù)，表的大小是35MB。其余的是什么呢？
除了正常可見(jiàn)的列，在PostgreSQL中每一行都有一些額外的系統(tǒng)列字段。并且它們占用空間。我們可以看到：
$ 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（在數(shù)據(jù)文件中，tableoid和ctid不是真實(shí)存在的）。我們?cè)诒碇械摹罢鎸?shí)”的列有：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)
你也可以在文檔中找到所有這些列的描述，但是通常情況下不用擔(dān)心它們。
事實(shí)是在數(shù)據(jù)文件中有許多內(nèi)容（比如checksums），意味著你可以通過(guò)將int8類型轉(zhuǎn)化成int4類型“節(jié)省”下來(lái)的每行4個(gè)字節(jié)是微不足道的。
從好的方面來(lái)說(shuō)-如果你已經(jīng)向表中增加了一個(gè)4字節(jié)的列，緊接著增加另一個(gè)4字節(jié)的列（磁盤(pán)空間方面）基本上是免費(fèi)的。
讓我們看一些真實(shí)的生活表：
=> 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的條件是隱藏系統(tǒng)列。Attlen = -1意味著數(shù)據(jù)的長(zhǎng)度是隨著數(shù)據(jù)的量的改變而變化。
由于這些文本，估計(jì)行的大小是復(fù)雜的，但是讓我們假設(shè)一行只占據(jù)了8字節(jié)的塊。鑒于列的排序只有25和26列可以在單個(gè)8字節(jié)的塊中。所以總共的行大小（不包括系統(tǒng)數(shù)據(jù)）是42 * 8 bytes = 336 bytes。
現(xiàn)在，如果你已經(jīng)將每個(gè)int8改為int4，我可以將第9-12列放到2個(gè)8字節(jié)的塊中，16-17列放到一個(gè)，對(duì)于32、33和42、43也一樣。總共：296字節(jié)。這意味著我每行可以節(jié)省40字節(jié)。請(qǐng)注意我假設(shè)23個(gè)文本列不會(huì)超過(guò)8個(gè)字節(jié)。猜猜它的可能性有多大??。
也可以通過(guò)簡(jiǎn)單的運(yùn)行explain看到你的列實(shí)際上（平均）的寬度：
=> explain select * from accounts;
QUERY PLAN
═══════════════════════════════════════════════════════════════════
Seq Scan on accounts (cost=0.00..3979.23 rows=100323 width=1113)
(1 row)
每一行都超過(guò)1KB。在此節(jié)省40個(gè)字節(jié)或多或少等于舍入誤差。
所以在某些情況下，使用int4/int2是有益的。可以通過(guò)使用較小的數(shù)據(jù)類型來(lái)節(jié)省一些磁盤(pán)空間。但是差異并沒(méi)有那么大，需要仔細(xì)規(guī)劃，可能會(huì)不切實(shí)際或者甚至不可能，并且假如較小的整型范圍成為問(wèn)題，將來(lái)會(huì)導(dǎo)致問(wèn)題。
最后一點(diǎn)-當(dāng)我在寫(xiě)int*列時(shí)，對(duì)于float4/float8（又名float/double）也同樣適用。
2022-02-13
