1

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)

1
  • If you really want to micromanage the crap out of your database, you at least need to tell us the version.
    – jjanes
    Jan 23, 2022 at 19:18

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.