Why it is sometimes good to restore a production database
We have noticed this in several environments, but postgres is not so good in the auto vacuum. It always starts with a warning that the diskspace for the database it reaching its max. We noticed that when restoring that database to the acceptance environment the size would be significant reduced. See the screenshots below. We did a restore on this production environment. As one can see the database size get's halved. Strange thought that the size goes down, up and after a day goes down again. I can not explain that one. Table index is also a huge difference. Here also the initial drop and again a drop after a day. Can not explain that one either. My advise: check regularly when you do a restore to the acceptance of a production database if the sizes are very different. We have made a support request to Mendix, but they have at the moment no answer yet on why this is happening. Regards, Ronald Catersels
On the MendixCloud autovacuum is turned on, but the operation you need it to reclaim the whitespace caused by fragmentation of the database is FullVacuum. This operation is not performed by Mendix, because it still requires a shutdown of the database.
In order to reclaim the whitespace and remove the fragmentation you should perform the the backup and restore action.
The cause of all this is fragmentation of the database. Their advise was to limit the commits to the database only to objects you really want to store, otherwise user for instance non-persistent objects or do not commit.
Wouter van Maasakker
Postgres documentation says:
PostgreSQL's VACUUM command must be run on a regular basis for several reasons:
To recover disk space occupied by updated or deleted rows.
To update data statistics used by the PostgreSQL query planner.
To protect against loss of very old data due to transaction ID wraparound.
I would suggest to make it a configurable schedule, so we can run it for example overnight or in the weekend.