vacuum full shrinks newly created and ordered table size



  • 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)



  • 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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2