Hi All,
I would like to share a recent finding we’ve discovered with 2.34, with the introduction of the new audit service Home - DHIS2 Documentation We’d found that database backups were increasing in size at an unprecedented rate.
To share some numbers the database without Analytics generated was about 100Gb and the backup was compressed to about 5gb. After the upgrade to 2.34 the compressed backup was growing about 1gb every three days while the amount of data collected was not increasing at a rate that would warrant that increase in size. Comparing the current db to a backup before 2.34 we discovered that the new audit table nearly directly corresponded to the nightly increase.
By default the audit table adds a row for every CREATE, UPDATE and DELETE action and on active instance this table grows quite fast. For this example it’s at 25gb for only one month of data collection.
To confirm that the audit table was the likely culprit I truncated the audit table on a test environment and the backup size went from 26gb to 6gb. I suspect that jsonb field on audit table doesn’t compress well and that is why there is a linear relationship between the audit table and the backup size.
No real action to take here, just wanted to share with community about why your database backups may be growing in size.
Also to echo Calle’s finding. Upgrading to Posgtres 13 on this server reduced the size of the db on disk from 300Gb to 200Gb, this is with Analytics tables generated.
Thanks,
Dan
Dan Cocos
Chief Technology Officer
2900 K St NW, Suite 507, Washington D.C. 20007
[www.baosystems.com]