vacuum full shrinks newly created and ordered table size

Posted on

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:

  1. why is the table size is bigger than number of blocks?
  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *