Mastering PostgreSQL 12 - Third Edition

Watching VACUUM at work

Now, it is time to see VACUUM in action. I have included this section here because my practical work as a PostgreSQL consultant and supporter (http://www.postgresql-support.com/) indicates that most people only have a very vague understanding of what happens on the storage side.

To stress this point again, in most cases, VACUUM will not shrink your tables; space is usually not returned to the filesystem.

Here is my example, which shows how to create a small table with customized autovacuum settings. The table is filled with 100000 rows:

CREATE TABLE t_test (id int) WITH (autovacuum_enabled = off); 
INSERT INTO t_test 
   SELECT * FROM generate_series(1, 100000); 

The idea is to create a simple table containing 100000 rows. ...