Re: pg_dump: VACUUM and REINDEXING

On Sat, 2022-05-07 at 12:06 +0200, Guillaume Lelarge wrote:
> Le sam. 7 mai 2022 à 10:21, Ron <ronljohnsonjr(at)gmail(dot)com> a écrit :
> >  On 5/6/22 21:35, Hasan Marzooq wrote:
> > > I've some questions around Backup & Restore.
> > >
> > >  1: Is it necessary to perform a VACUUM and REINDEXING operation after restoring the
> > > dump from Postgres 9.6 to Postgres 13? The dump size could be 1/2 TB to 1 TB.
> >
> > Perform VACUUM after there have been many updates and deletes.  There have been zero
> > updates and deleted after pg_restore; therefore, no need to vacuum.
>
> I disagree. You're right about the "zero updates and deletes", so no need to vacuum for bloat.
> But you need vacuum to get the visibility map of each relation, so that the planner can use index-only scans.

But from PostgreSQL v13 on, autovacuum is also triggered by INSERTs.
So I'd say that there is nothing to do after restoring a pg_dump, except
to wait until autovacuum is done.

Yours,
Laurenz Albe