Vacuum and autovacuum

In this recipe, we will be discussing the importance of vacuum and autovacuum in achieving good PostgreSQL performance.

Getting ready

As aforementioned, PostgreSQL is based on MVCC. As a net result, we will have all the non-visible tuples beside visible tuples, which occupy the underlying disk storage. As of now, these non-visible tuples have no use, and if we could reclaim or reuse the non-visible tuple's disk storage, that would make the disk utilization more effective.

How to do it...

Let's experiment with the usage of VACUUM by creating a sample table and executing a few SQL statements that generate non-visible tuples or dead tuples.

Connect to your database using psql as a super user and then execute the following command:

$ psql -h localhost -U postgres 
postgres=# CREATE EXTENTION  pg_freespacemap; 
CREATE 

Now create a test table as follows:

postgres=# CREATE TABLE test(t INT); 
CREATE 

For demonstration of the VACUUM process, let's turn off autovacuum on this...