Re: pg_dump: VACUUM and REINDEXING - Mailing list pgsql-general

From Hasan Marzooq
Subject Re: pg_dump: VACUUM and REINDEXING
Date
Msg-id CANvhf8JKzDw3gzA7WaabcfijaoTRPsUPgOkMFVLqDvprk-eNFA@mail.gmail.com
Whole thread Raw
In response to Re: pg_dump: VACUUM and REINDEXING  (Guillaume Lelarge <guillaume@lelarge.info>)
Responses Re: pg_dump: VACUUM and REINDEXING
List pgsql-general
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@lelarge.info> wrote:
Le sam. 7 mai 2022 à 10:21, Ron <ronljohnsonjr@gmail.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.

pgsql-general by date:

Previous
From: Durumdara
Date:
Subject: PLPGSQL - extra column existence in trigger
Next
From: "David G. Johnston"
Date:
Subject: Re: PLPGSQL - extra column existence in trigger