Lazy_vacuum vs nz_manual_vacuum

Answer

During Netezza/PDA boot time, the VACUUM command is invoked to groom the system tables for faster table scans by removing deleted records and shrinking the table sizes.  Since the system is not in-use (ie no user connections or activity) it is able to have exclusive access to the system tables and perform the needed activities to reclaim disk space.

When Netezza/PDA is running, it performs what is termed a ‘lazy_vacuum’ check every 60 secs for database system table growth above 120 KB and will invoke a system table vacuum if it grows above 128 KB.  The VACUUM command will try to obtain an exclusive lock on all system catalog tables before the vacuuming can be performed.  If it is not able to acquire the locks, the system will write a ‘Could not acquire lock for lazy vacuum’ message in pg.log and will try again after some time.

This is an example error message from the pg.log

2016-04-23 06:56:40.929766 PDT [27815]  DEBUG:  QUERY: set enable_lazy_vacuum=true; vacuum check

2016-04-23 06:56:40.937919 PDT [27815]  NOTICE:  Could not acquire lock for lazy vacuum - (_T_OBJECT)

2016-04-23 06:56:40.938264 PDT [27815]  DEBUG:  disconnect: host=127.0.0.1 user=ADMIN database=SYSTEM remotepid=27814 fetype=1

Per the error message above, since the system tables were in-use (lazy_vacuum couldn't acquire the exclusive lock), the lazy_vacuum process did not reclaim disk space.

If the lazy_vacuum process is unable to acquire the needed exclusive lock, then the system catalogs will not be vacuumed, and the space not reclaimed.  In this case, a system restart or manually running the ‘nz_manual_vacuum’ command is required (which requires the NPS system to be in ‘stopped’ state).