I have a database currently sitting on ~6B live tuples, ~4B dead tuples.
I've ran the VACUUM command for 3 days now, and then suddenly my internet died. Meaning i lost all progress. How can i speed up the VACUUM speed? It's currently running on AWS RDS.
Is there a way to run command in background, e.g. disconnect/turn off PC?
Btw running on 9.3
You may want to turn on AUTO VACUUM? AWS Docs suggests that as a best practice
Set the autovacuum and once it is done, you can turn it off if you wish.
If you want to really run it in a terminal and then disconnect, refer to this answer: How to execute a query from psql without waiting for the result?
You can use something like "screen" or "tmux" to preserve your session when your internet hangs up on you. You just reconnect, then run screen -x, and pick up where you left off. But that doesn't work if you were running psql locally to issue the vacuum command. Can you keep a t2.nano instance is the same availability zone as your RDS instance, so you can ssh to it, run "screen", then psql from there to RDS?
But it probably doesn't matter for this case. VACUUM does not interact with the session which launches it until it is finished (unless you did VERBOSE). So it is probably still happily running along, unaware that the controlling session has disappeared. It will discover that only when it tries to send a completion message, which means after it has already completed. Are you sure the VACUUM is not still running? With RDS, I suppose you can't just run "top" and see, but you should be able to check the "pg_stat_activity" view.
Even if the VACUUM did abort, all is not lost. Any work it did on cleaning the indexes did not roll back. When you repeat it, it will still have to go through the motions of cleaning the indexes, but finding them already clean it will generate a lot less write activity and likely be a lot faster. And if it needs to go through many rounds of index cleaning (due to maintenance_work_mem being insufficient), then any of those rounds which were completed before the abort means that the work done on table cleaning also was not lost.
As for your title question rather than your body question, what kinds of indexes do you have on the table, and what are your settings for all the vacuum parameters, and maintenance_work_mem?
Also, why is autovac not taking care of this for you?
You are not doing yourself any favors by using an obsolete version of PostgreSQL.
