出於效能的考慮，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/)

PostgreSQL DBA(190) - 行大小和空間佔用

相關文章