Watching VACUUM at work

After this introduction, 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://postgresql-support.de/) 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 that shows how to create a small table with customized autovacuum settings. The table is filled with 100,000 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 ...

Get Mastering PostgreSQL 11 - Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.