Re: Faster pg_resore with autovacuum off?

On Sun, Jul 28, 2024 at 8:34 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Sat, 2024-07-27 at 15:48 -0400, Ron Johnson wrote:
> > > If your machine is too weak to handle the combined workload of restore
> +
> > > autovacuum, that might get you something. Otherwise, you are just
> cheating:
> > > the restore might be faster, but the database is not usable before
> autovacuum
> > > and autoanalyze have proessed all restored tables.
> >
> > I 100% disagree with this: autovacuum is not necessary during pg_restore
> > (presuming there's only one database in the instance).
>
> It is not necessary during restore, but it is necessary if you want to use
> the database after the restore. Well, you can manually VACUUM and ANALYZE
> the
> database, but why not do it automatically?
>
> I think that there are very few good reasons to ever disable autovacuum,
> and
> a restore is not among them.
>

My experience is that autovacuum=off pg_restore -Fd -j$BigNum + vacuumdb
-Zj$BigNum is faster than letting autoanalyze=on pg_restore -Fd -j$BigNum.

> > Heck, it might not even be vital if you carefully monitor the other
> databases
> > in the instance.
>
> ?
>

Tables in a freshly vacuum database won't need vacuuming again "for a
while" unless they're really, really busy. Analyzing is a different story,
but targeted manual analyzes work just as well.