In this recipe, we will be discussing the importance of vacuum and autovacuum in achieving good PostgreSQL performance.
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.
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...