On Thu, Oct 8, 2020, 1:16 PM Jean-Marc Lessard < jean-marc.less...@ultra-ft.com> wrote:
> I have a large table (billions of records) which has not been vacuum and > bloated. > > Vacuum scale factor was left at the default. > > > > I ran a vacuum on a DEV system and it makes several passes (scanning > heap/vacuuming indexes/vacumming heap) which take more than an hour each. > > On a PROD system, I may have to kill the job midway. > > Should I reduce the autovacuum_work_mem of my session? Currently 1GB > Increase if you can. You want to maximize the work being done before it needs to pause. Have you tuned any settings related to vacuum? If your I/O system can handle it, turning cost delay very low, especially PG12+ where it can be less than 1ms, may be helpful. Otherwise you might reduce to 1ms and also increase cost limit so you do more work before stopping. >
