真空全收缩新创建的和有序的桌子大小

Question

我已经创建并空了表，并使用generate_series函数填充了表。我看了桌子的尺寸。这是奇怪的64 be，因为每页保持226个整数和有5个块，因此它的大小应该是40 be。

然后我做了一个完整的真空，它的大小下降到40 KB。

问题如下：

1. 为什么表的大小大于块的数目？
2. 新制作的桌子是用全真空做的吗？

create table tbl (data int);
postgres=# insert into tbl (data) SELECT * from generate_series(1,1000);
INSERT 0 1000

postgres=# \d+
                                  List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description 
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | tbl  | table | postgres | permanent   | heap          | 64 kB | 
(1 row)

postgres=# vacuum FULL tbl ;
VACUUM
postgres=# \d+
                                  List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method | Size  | Description 
--------+------+-------+----------+-------------+---------------+-------+-------------
 public | tbl  | table | postgres | permanent   | heap          | 40 kB | 
(1 row)

Answer 1

这是因为VACUUM (FULL)去掉了“自由空间地图”：

SELECT pg_relation_size('tbl', 'main') AS table_size,
       pg_relation_size('tbl', 'fsm') AS free_space_map_size,
       pg_relation_size('tbl', 'vm') AS visibility_map_size;

 table_size │ free_space_map_size │ visibility_map_size 
════════════╪═════════════════════╪═════════════════════
      40960 │               24576 │                   0
(1 row)

VACUUM (FULL) tbl;

SELECT pg_relation_size('tbl', 'main') AS table_size,
       pg_relation_size('tbl', 'fsm') AS free_space_map_size,
       pg_relation_size('tbl', 'vm') AS visibility_map_size;

 table_size │ free_space_map_size │ visibility_map_size 
════════════╪═════════════════════╪═════════════════════
      40960 │                   0 │                   0
(1 row)

这并不是真正的好处，因为一旦表上有更新或删除，就需要自由空间映射，下一次自动真空运行将再次创建它：

VACUUM tbl;

SELECT pg_relation_size('tbl', 'main') AS table_size,
       pg_relation_size('tbl', 'fsm') AS free_space_map_size,
       pg_relation_size('tbl', 'vm') AS visibility_map_size;

 table_size │ free_space_map_size │ visibility_map_size 
════════════╪═════════════════════╪═════════════════════
      40960 │               24576 │                8192
(1 row)

此外，还创建了可见性地图。

请注意，堆本身的大小(“主叉”)始终保持不变。