pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuuming TOAST after pg_restore
5+ messages / 3 participants
[nested] [flat]
* Vacuuming TOAST after pg_restore
@ 2023-12-18 22:26 Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ron Johnson @ 2023-12-18 22:26 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
PG 14.10
In the immediate post-pg_restore vaccumdb, can I skip immediate vacuuming
of TOAST tables?
Heck, can I skip the immediate VACUUM, and only do an ANALYZE (then go back
later and VACUUM)?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuuming TOAST after pg_restore
@ 2023-12-18 22:40 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2023-12-18 22:40 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Dec 18, 2023 at 3:26 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> PG 14.10
>
> In the immediate post-pg_restore vaccumdb, can I skip immediate vacuuming
> of TOAST tables?
>
> Heck, can I skip the immediate VACUUM, and only do an ANALYZE (then go
> back later and VACUUM)?
>
There has never been directions to perform an immediate vacuum
post-restore. A restore doesn't perform deletions. Recovering from a
physical backup will benefit from whatever vacuuming the cluster needed at
the time it was backed up.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuuming TOAST after pg_restore
@ 2023-12-18 23:14 Ron Johnson <ronljohnsonjr@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2023-12-18 23:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Dec 18, 2023 at 5:41 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Mon, Dec 18, 2023 at 3:26 PM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> PG 14.10
>>
>> In the immediate post-pg_restore vaccumdb, can I skip immediate vacuuming
>> of TOAST tables?
>>
>> Heck, can I skip the immediate VACUUM, and only do an ANALYZE (then go
>> back later and VACUUM)?
>>
>
> There has never been directions to perform an immediate vacuum
> post-restore. A restore doesn't perform deletions. Recovering from a
> physical backup will benefit from whatever vacuuming the cluster needed at
> the time it was backed up.
>
I'm sure I saw that it was recommended for the purpose of setting the FSM.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuuming TOAST after pg_restore
@ 2023-12-19 00:56 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2023-12-19 00:56 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Dec 18, 2023 at 4:14 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Mon, Dec 18, 2023 at 5:41 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Mon, Dec 18, 2023 at 3:26 PM Ron Johnson <ronljohnsonjr@gmail.com>
>> wrote:
>>
>>> PG 14.10
>>>
>>> In the immediate post-pg_restore vaccumdb, can I skip immediate
>>> vacuuming of TOAST tables?
>>>
>>> Heck, can I skip the immediate VACUUM, and only do an ANALYZE (then go
>>> back later and VACUUM)?
>>>
>>
>> There has never been directions to perform an immediate vacuum
>> post-restore. A restore doesn't perform deletions. Recovering from a
>> physical backup will benefit from whatever vacuuming the cluster needed at
>> the time it was backed up.
>>
>
> I'm sure I saw that it was recommended for the purpose of setting the FSM.
>
>
"Visibility map bits are only set by vacuum, but are cleared by any
data-modifying operations on a page."
https://www.postgresql.org/docs/current/storage-vm.html
I presume the VM refers to the main table only anyway so it gets updated
regardless of whether TOAST is included in the vacuum.
Possibly you'd want to deal with tables with GIN indexes as well...though
I'm unsure how those work exactly with bulk loading (or at all really...)
I don't know how the FSM would come into play.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuuming TOAST after pg_restore
@ 2023-12-19 07:18 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2023-12-19 07:18 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, 2023-12-18 at 17:26 -0500, Ron Johnson wrote:
> PG 14.10
>
> In the immediate post-pg_restore vaccumdb, can I skip immediate vacuuming of TOAST tables?
>
> Heck, can I skip the immediate VACUUM, and only do an ANALYZE (then go back later and VACUUM)?
You don't have to do that at all, autovacuum will do it for you (ANALYZE if the
tables contain more than 50 rows, and VACUUM if they contain more than 1000 rows).
It just takes some more time to let autovacuum do it.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-12-19 07:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-18 22:26 Vacuuming TOAST after pg_restore Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-18 22:40 ` David G. Johnston <david.g.johnston@gmail.com>
2023-12-18 23:14 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-19 00:56 ` David G. Johnston <david.g.johnston@gmail.com>
2023-12-19 07:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox