出于性能的考虑，PostgreSQL在存储数据时会“对齐”从而会出现实际空间占用大于行大小的情况 **bool vs int** ``` \set AUTOCOMMIT on drop table size1; drop table size2; drop table size3; drop table size4; drop table size5; create table size1(c1 bool,c2 int4); create table size2(c1 bool,c2 bool,c3 bool,c4 bool,c5 int4); create table size3(c1 int4,c2 int4); create table size4(c1 bool,c2 int8); create table size5(c1 int8,c2 int8); insert into size1 select true,x from generate_series(1,1000000) x; insert into size2 select true,true,true,true,x from generate_series(1,1000000) x; insert into size3 select x,x from generate_series(1,1000000) x; insert into size4 select true,x from generate_series(1,1000000) x; insert into size5 select 1,x from generate_series(1,1000000) x; select pg_relation_size('size1')/1024/1024; select pg_relation_size('size2')/1024/1024; select pg_relation_size('size3')/1024/1024; select pg_relation_size('size4')/1024/1024; select pg_relation_size('size5')/1024/1024; explain (analyze,buffers) select * from size1; explain (analyze,buffers) select * from size2; explain (analyze,buffers) select * from size3; explain (analyze,buffers) select * from size4; explain (analyze,buffers) select * from size5; ``` 看起来bool类型只占1个字节，int占4个字节，表size1的占用照理应该会比size2和size3小，但实际上这3个表的空间占用是一样的，同理size4和size5空间占用也一样 原因是size1的c1+c2=5字节，会被对齐为8个字节，同理，size4的c1+c2=9字节，会被对齐为16字节 ``` [local:/opt/data5012]:5012 pg12@testdb=# \set AUTOCOMMIT on [local:/opt/data5012]:5012 pg12@testdb=# drop table size1; explain (analyze,buffers) select * from size1; explain (analyze,buffers) select * from size2; explain (analyze,buffers) select * from size3; explain (analyze,buffers) select * from size4; explain (analyze,buffers) select * from size5; DROP TABLE Time: 12.435 ms [local:/opt/data5012]:5012 pg12@testdb=# drop table size2; DROP TABLE Time: 12.087 ms [local:/opt/data5012]:5012 pg12@testdb=# drop table size3; DROP TABLE Time: 11.194 ms [local:/opt/data5012]:5012 pg12@testdb=# drop table size4; DROP TABLE Time: 12.837 ms [local:/opt/data5012]:5012 pg12@testdb=# drop table size5; DROP TABLE Time: 14.203 ms [local:/opt/data5012]:5012 pg12@testdb=# create table size1(c1 bool,c2 int4); CREATE TABLE Time: 6.194 ms [local:/opt/data5012]:5012 pg12@testdb=# create table size2(c1 bool,c2 bool,c3 bool,c4 bool,c5 int4); CREATE TABLE Time: 6.840 ms [local:/opt/data5012]:5012 pg12@testdb=# create table size3(c1 int4,c2 int4); CREATE TABLE Time: 5.583 ms [local:/opt/data5012]:5012 pg12@testdb=# create table size4(c1 bool,c2 int8); CREATE TABLE Time: 5.788 ms [local:/opt/data5012]:5012 pg12@testdb=# create table size5(c1 int8,c2 int8); CREATE TABLE Time: 5.921 ms [local:/opt/data5012]:5012 pg12@testdb=# [local:/opt/data5012]:5012 pg12@testdb=# insert into size1 select true,x from generate_series(1,1000000) x; INSERT 0 1000000 Time: 899.676 ms [local:/opt/data5012]:5012 pg12@testdb=# insert into size2 select true,true,true,true,x from generate_series(1,1000000) x; INSERT 0 1000000 Time: 910.105 ms [local:/opt/data5012]:5012 pg12@testdb=# insert into size3 select x,x from generate_series(1,1000000) x; INSERT 0 1000000 Time: 868.352 ms [local:/opt/data5012]:5012 pg12@testdb=# insert into size4 select true,x from generate_series(1,1000000) x; INSERT 0 1000000 Time: 956.063 ms [local:/opt/data5012]:5012 pg12@testdb=# insert into size5 select 1,x from generate_series(1,1000000) x; INSERT 0 1000000 Time: 1003.157 ms (00:01.003) [local:/opt/data5012]:5012 pg12@testdb=# [local:/opt/data5012]:5012 pg12@testdb=# select pg_relation_size('size1')/1024/1024; ?column? ---------- 34 (1 row) Time: 0.393 ms [local:/opt/data5012]:5012 pg12@testdb=# select pg_relation_size('size2')/1024/1024; ?column? ---------- 34 (1 row) Time: 0.130 ms [local:/opt/data5012]:5012 pg12@testdb=# select pg_relation_size('size3')/1024/1024; ?column? ---------- 34 (1 row) Time: 0.106 ms [local:/opt/data5012]:5012 pg12@testdb=# select pg_relation_size('size4')/1024/1024; ?column? ---------- 42 (1 row) Time: 0.121 ms [local:/opt/data5012]:5012 pg12@testdb=# select pg_relation_size('size5')/1024/1024; ?column? ---------- 42 (1 row) Time: 0.185 ms [local:/opt/data5012]:5012 pg12@testdb=# [local:/opt/data5012]:5012 pg12@testdb=# [local:/opt/data5012]:5012 pg12@testdb=# explain (analyze,buffers) select * from size1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on size1 (cost=0.00..15354.75 rows=1092975 width=5) (actual time=0.010..88.148 rows=1000000 loops=1) Buffers: shared hit=4425 Planning Time: 0.156 ms Execution Time: 122.615 ms (4 rows) Time: 122.950 ms [local:/opt/data5012]:5012 pg12@testdb=# explain (analyze,buffers) select * from size2; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on size2 (cost=0.00..14425.50 rows=1000050 width=8) (actual time=0.007..88.430 rows=1000000 loops=1) Buffers: shared hit=3155 read=1270 dirtied=1270 written=1238 Planning Time: 0.047 ms Execution Time: 126.848 ms (4 rows) Time: 127.103 ms [local:/opt/data5012]:5012 pg12@testdb=# explain (analyze,buffers) select * from size3; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Seq Scan on size3 (cost=0.00..14425.50 rows=1000050 width=8) (actual time=0.007..109.314 rows=1000000 loops=1) Buffers: shared hit=811 read=3614 dirtied=3682 written=3584 Planning Time: 0.039 ms Execution Time: 147.463 ms (4 rows) Time: 147.937 ms [local:/opt/data5012]:5012 pg12@testdb=# explain (analyze,buffers) select * from size4; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Seq Scan on size4 (cost=0.00..17299.20 rows=1189320 width=9) (actual time=0.013..108.319 rows=1000000 loops=1) Buffers: shared hit=2446 read=2960 dirtied=2965 written=2928 Planning Time: 0.041 ms Execution Time: 143.815 ms (4 rows) Time: 144.340 ms [local:/opt/data5012]:5012 pg12@testdb=# explain (analyze,buffers) select * from size5; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Seq Scan on size5 (cost=0.00..15407.10 rows=1000110 width=16) (actual time=0.010..95.226 rows=1000000 loops=1) Buffers: shared hit=5406 Planning Time: 0.148 ms Execution Time: 135.238 ms (4 rows) Time: 135.595 ms [local:/opt/data5012]:5012 pg12@testdb=# ``` **参考资料** [How much disk space you can save by using INT4/INT instead of INT8/BIGINT?](https://www.depesz.com/2022/02/13/how-much-disk-space-you-can-save-by-using-int4-int-instead-of-int8-bigint/)