I have ran this on a ambari database used by on one of the 500 node cluster HDP cluster. It ran for more than 15 hours but without any success. I analyzed the ambari server logs to check where it is taking most of the time, it seems to be spending more time on batch deletes on ambari.alert_notice, ambari.alert_current ,ambari.alert_history table.
To improve the performance of the db cleanup, I have created the index on the ambari.alert_notice table..
-bash-4.1$ psql -U ambari -d ambari
Password for user ambari:
Type "help" for help.
ambari=> CREATE INDEX alert_notice_idx ON ambari.alert_notice(history_id);
After this i ran re-loaded my ambari database from the backup and ran the db-cleanup, it took only less than 2 min to complete the cleanup.
Also to reclaim the disk space and reindex after the cleanup, i ran the following commands as super user "postgres"