Question :
I have created and empty table and populated it with generate_series function. I looked at the table size. and it is oddly 64KB, because each page keeps 226 integers and there are 5 blocks respectively, thus its size should be 40KB.
Then I did a full vacuum and its size dropped to 40 KB.
The questions:
- why is the table size is bigger than number of blocks?
- what does full vacuum do a newly created table?
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 :
That is because VACUUM (FULL)
got rid of the “free space mapâ€:
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)
That is not really a gain, because the free space map will be needed as soon as there are updates or deletes on the table, and the next autovacuum run will create it again anyway:
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)
In addition, the visibility map was created.
Note that the size of the heap itself (the “main forkâ€) stayed the same all the while.