Any experiences of Postgres "full DB vacuum" in a liv...

[IainOllerenshaw]

We've been implementing some significant changes to the volume of data that we bring into Adobe Campaign and how long we keep and purge both transactional and operational data. As a result, we've purged over 100GB of data from our Adobe hosted, Postgres Adobe Campaign Classic database.

While this has been great for stability and performance, we're still taking up a significant amount of physical storage due to the way Postgres manages physical disk allocation.

Adobe has recommended a "full DB vacuum", in order to reclaim the space, but I'm pretty worried about the amount of downtime this will require to our live service.

Does anyone have any experience of running a "full DB vacuum" in a live environment? Any thoughts or feedback that you could share? Are there genuine benefits that even make this worthwhile?

We're on v7 9128, hosted in Adobe AWS, if that helps at all.

Thank you!

[Craig_Thonis]

Hi Lain,

Running a full vacuum is a necessary maintenance step when using a PostGres database. That said, how often you run the vacuum and the benefit that you will see from it depends upon the amount of bloat in your system. Depending on the size of your database it can take several hours as you mentioned, so performing a full vacuum would really only be recommended one a year at the most. You can run vacuums against individual tables as well if there are only a few tables to target.

Regards,

Craig

[kapilKochar · Accepted solution]

Hi , 

As per my experience with full vacuums it's definitely helps. we were facing issue where import jobs were taking too much time ~2/3hr and after running this vacuums there was significant reduce of time for import jobs ~ 30min. There are some best practice which you need to follow if you planning for the same. Also, timing for running this is also very important make sure heavy jobs either import or exports workflows are not running at that time and less number of active users. 

Note : First run always take time so after first run schedule it on regular basis. 

click this and you will find some best practice suggested by Adobe. 

Hope this helps.