Postgresql – When will autovacuum work in PostgreSQL

autovacuumpostgresql

I tested the autovacuum in PostgreSQL. So I create a table company and insert 100 records. Then I checked the table size like,

ganapathy=> \dt+ company 
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description 
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 54 kB |

After that I update all the records in a table like,

ganapathy=> update company set address = 'California' where id between 1 and 100;

Then I checked the table size, It gives the size like this,

ganapathy=> \dt+ company 
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description 
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 72 kB |

So checked the configuration file of postgresql.conf, In that the autovacuum is already enabled,

autovacuum = on

But the autovacuum has not worked. So after two days I checked the table size again,
But even though the table size has not changed.

ganapathy=> \dt+ company 
                     List of relations
 Schema |  Name   | Type  |   Owner   | Size  | Description 
--------+---------+-------+-----------+-------+-------------
 public | company | table | ganapathy | 72 kB |

So I want to know,

  1. Why its not working for me?
  2. When the autovacuum process will work in PostgreSQL?

Please help me.

Thanks in Advance…!

Best Answer

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

PostgreSQL documentation

AUTOVACUUM is the standard form of VACUUM run automatically. If you insist on freeing unused space from tables, then do manual VACUUM FULL.

Related Question