PCK.AUTOMIC_ANALYTICS: Space usage issue using "Shrink DB" option on delete action
Article ID: 248511
Updated On:
Products
Issue/Introduction
Regarding Analytics action pack (PCK.AUTOMIC_ANALYTICS)
When selecting the “Shrink DB” option when executing the delete process, the end result shows an increase in disk space utilization.
What is causing this additional space to be retained? The intention is to reduce the size of the DB to free up space as we remove old data from the database.
Environment
Release : 12.3.3
Component : Analytics
Cause
From the PostgreSQL documentation on vacuum:
"Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table."
Resolution
When running the "shrink" from the package, what it is doing is running the command 'vacuum' for a postgres db.
This doesn't actually return any space, what this does is mark no longer used space as re-usable so that subsequent insert or update statements can use that space.
If you need to reclaim space, you need to run the command vacuum with the FULL option.
Additionally, running 'vacuum' causes fragmentation which increases the size of the DB. In order to clear all of the fragmentation and reclaim disk space, you need to run vacuum with the FULL option.
Please discuss this further with your DBA as they will better be able to assist you with this.
Feedback
