Re: pg_dump: VACUUM and REINDEXING

Hello!

Thanks Guillaume and Ron!

I understand REINDEXING is not required, and as Guillaume highlighted,
vacuum will still be needed after pg_restore.

Is it ok to perform a "standard" vacuum or do we need a "FULL" vacuum after
pg_restore?

Also, I think finding tables which have dead rows and then performing
vacuum on those tables only to save some time/processing here.

@Ron: Yes, we're using --jobs=`nproc` and it has significantly improved the
pg_dump/pg_restore processes. I see there is a similar option "parallel'
with VACUUM as well.

Thanks!

Hasan

On Sat, 7 May 2022 at 18:07, Guillaume Lelarge <guillaume(at)lelarge(dot)info>
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:
>>
>> Hello!
>>
>> 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.
>
>
> --
> Guillaume.
>