出于性能的考虑，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?