I am trying to find answers for my below few queries which will help me in fine tuning my postgres DB. I did some googling but was not able to find answer.
1) Out of vacuum or Auto vacuum which is more resource intensive operation?
2) Is there a way to run Auto vacuum only when there is unusually heavy update activity?
3) How can I find out based on setting when my auto vacuum will start next time, based on threshold.
1) If you don't count your own time as a resource, then you should always be able to hand-craft a vacuum schedule which uses fewer total resources than autovacuum does. If you do count your own time, this is almost surely not worthwhile.
2) Other than manually or algorithmically turning it on or off, no. Nor would it make sense to do that. A database that sees only a very steady predictable load with no unusual spikes still needs vacuuming. However, if you do spend a lot of time coming up with a highly optimized hand-crafted vacuum schedule, then autovacuum will never have any vacuuming to do except under unusual activity not anticipated in your custom schedule; so in this sense what you want is automatically the case.
3) It isn't clear whether you mean when an autovacuum worker will start up in a given database, or when any given table will start to be vacuumed. The first is determined by autovacuum_naptime, unless autovacuum_max_workers becomes the limiting factor. The second is determined by when the table crosses the threshold, with a granularity of autovacuum_naptime, except when autovacuum_max_workers becomes the limiting factor. How accurately you can predict this depends on how steady the activity on that table is, and whether the vacuum tasks are generally overloaded or are mostly idle.
1) they perform the same task so use the same resources, as autovacuum is run when as it is needed it can use less resources at any one time, whereas if you disable it and manually run vacuum once a week then you effectively accumulated the work and do it all at one time.
2 + 3) 23.1.6 of the manual explains autovacuum and how the relevant settings work to determine when it does it's work.
