pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM FREEZE vs plain VACUUM
9+ messages / 4 participants
[nested] [flat]
* VACUUM FREEZE vs plain VACUUM
@ 2025-07-17 22:03 Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-17 22:26 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
2025-07-18 01:13 ` Re: VACUUM FREEZE vs plain VACUUM Rui DeSousa <rui.desousa@icloud.com>
2025-07-18 04:31 ` Re: VACUUM FREEZE vs plain VACUUM Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 9+ messages in thread
From: Ron Johnson @ 2025-07-17 22:03 UTC (permalink / raw)
To: pgsql-admin
PG 14.18
For a given table, after VACUUM FREEZE, age(relfrozenxid) == 0, while after
plain VACUUM, the age(relfrozenxid) == 50000000 (the system's
vacuum_freeze_min_age).
Fifteen minutes later, that table's age(relfrozenxid) has increased.
Eventually, it will hit autovacuum_freeze_max_age and autovacuum will do
its thing.
Does VACUUM FREEZE do something extra or special than to defer autovacuum
for an extra 50,000,000 transactions?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: VACUUM FREEZE vs plain VACUUM
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
@ 2025-07-17 22:26 ` David G. Johnston <david.g.johnston@gmail.com>
2025-07-17 23:29 ` Re: VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2025-07-17 22:26 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> Does VACUUM FREEZE do something extra or special than to defer autovacuum
> for an extra 50,000,000 transactions?
>
It effectively resets the pseudo-counter(s) that autovacuum uses to
determine when next it should perform an aggressive scan. Or, put
differently, it does exactly what autovacuum would do when the
pseudo-counter(s) hit their thresholds. The act of doing that thing
effectively resets said counters to zero at that moment (absent concurrent
activity).
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: VACUUM FREEZE vs plain VACUUM
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-17 22:26 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
@ 2025-07-17 23:29 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-18 01:23 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ron Johnson @ 2025-07-17 23:29 UTC (permalink / raw)
To: pgsql-admin
On Thu, Jul 17, 2025 at 6:26 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>>
>> Does VACUUM FREEZE do something extra or special than to defer autovacuum
>> for an extra 50,000,000 transactions?
>>
>
> It effectively resets the pseudo-counter(s) that autovacuum uses to
> determine when next it should perform an aggressive scan. Or, put
> differently, it does exactly what autovacuum would do when the
> pseudo-counter(s) hit their thresholds. The act of doing that thing
> effectively resets said counters to zero at that moment (absent concurrent
> activity).
>
That seems to be what I said. Or am I still missing something?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: VACUUM FREEZE vs plain VACUUM
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-17 22:26 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
2025-07-17 23:29 ` Re: VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
@ 2025-07-18 01:23 ` David G. Johnston <david.g.johnston@gmail.com>
2025-07-18 03:14 ` Re: VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2025-07-18 01:23 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Thu, Jul 17, 2025 at 6:26 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>>>
>>> Does VACUUM FREEZE do something extra or special than to defer
>>> autovacuum for an extra 50,000,000 transactions?
>>>
>>
>> It effectively resets the pseudo-counter(s) that autovacuum uses to
>> determine when next it should perform an aggressive scan. Or, put
>> differently, it does exactly what autovacuum would do when the
>> pseudo-counter(s) hit their thresholds. The act of doing that thing
>> effectively resets said counters to zero at that moment (absent concurrent
>> activity).
>>
>
> That seems to be what I said. Or am I still missing something?
>
Well, it would defer autovacuum freeze for 60,000,000 if no new rows were
inserted into your table in the subsequent 10,000,000 transactions…and
autovacuum would run (but not aggressively) if you performed a bunch of
deletes or updates…
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: VACUUM FREEZE vs plain VACUUM
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-17 22:26 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
2025-07-17 23:29 ` Re: VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-18 01:23 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
@ 2025-07-18 03:14 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-18 03:27 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ron Johnson @ 2025-07-18 03:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
On Thu, Jul 17, 2025 at 9:23 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
>> On Thu, Jul 17, 2025 at 6:26 PM David G. Johnston <
>> david.g.johnston@gmail.com> wrote:
>>
>>> On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>>>>
>>>> Does VACUUM FREEZE do something extra or special than to defer
>>>> autovacuum for an extra 50,000,000 transactions?
>>>>
>>>
>>> It effectively resets the pseudo-counter(s) that autovacuum uses to
>>> determine when next it should perform an aggressive scan. Or, put
>>> differently, it does exactly what autovacuum would do when the
>>> pseudo-counter(s) hit their thresholds. The act of doing that thing
>>> effectively resets said counters to zero at that moment (absent concurrent
>>> activity).
>>>
>>
>> That seems to be what I said. Or am I still missing something?
>>
>
> Well, it would defer autovacuum freeze for 60,000,000 if no new rows were
> inserted into your table in the subsequent 10,000,000 transactions…and
> autovacuum would run (but not aggressively) if you performed a bunch of
> deletes or updates…
>
Even on that just-frozen, never-modified table, age(relfrozenxid) grows
towards autovacuum_freeze_max_age.
(dba.v_child_xid_age is a view into pg_class that full joins itself to also
show relfrozenxid_age of the toasts associated with tables. That's not
relevant now, so only showing these two columns.)
I did VACUUM FREEZE on css.document_comment_annotation_rp11_y2015m03 and
cds.cdsdocument_rp11_y2015m03
but plain VACUUM on cds.cdsdocument_rp11_y2015m03.
Eventually, relfrozenxid_age of those tables will hit
vacuum_freeze_min_age, and autovacuum will run on them. It'll just happen
sooner on cds.cdsdocument_rp11_y2015m03.
TAPb=# select table_name, relfrozenxid_age
from dba.v_child_xid_age
where table_name like '%_y2015%'
order by 2
limit 5;
table_name | relfrozenxid_age
-----------------------------------------------+------------------
css.document_comment_annotation_rp11_y2015m03 | 74300
cds.cdsdocument_rp11_y2015m03 | 75237
cds.cdsdocument_rp11_y2015m09 | 50074419
cds.cdssubbatch_rp11_y2015m03 | 145069780
cds.cdssubbatch_rp11_y2015m09 | 145069780
(5 rows)
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: VACUUM FREEZE vs plain VACUUM
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-17 22:26 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
2025-07-17 23:29 ` Re: VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-18 01:23 ` Re: VACUUM FREEZE vs plain VACUUM David G. Johnston <david.g.johnston@gmail.com>
2025-07-18 03:14 ` Re: VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
@ 2025-07-18 03:27 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2025-07-18 03:27 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
On Thursday, July 17, 2025, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
>
> Even on that just-frozen, never-modified table, age(relfrozenxid) grows
> towards autovacuum_freeze_max_age.
>
I stand corrected. Of course pg_class.relfrozenxid wouldn’t continually
change until an unfrozen tuple appeared…
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: VACUUM FREEZE vs plain VACUUM
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
@ 2025-07-18 01:13 ` Rui DeSousa <rui.desousa@icloud.com>
2 siblings, 0 replies; 9+ messages in thread
From: Rui DeSousa @ 2025-07-18 01:13 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin
> On Jul 17, 2025, at 6:03 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
>
> Does VACUUM FREEZE do something extra or special than to defer autovacuum for an extra 50,000,000 transactions?
>
Yes. Vacuum freeze removes the xmin on tuples that no longer need it. i.e. The xmin is not required by any other in-flight transaction.
I don’t know what you mean by defer auto vacuum. Vacuum freeze is a mechanism to recycle transaction ids; not to initiate an auto vacuum.
It sounds like you have autovacuum_freeze_max_age set too low for your environment.
i.e. Here is what I currently use:
autovacuum_freeze_max_age=800000000
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: VACUUM FREEZE vs plain VACUUM
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
@ 2025-07-18 04:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-18 06:16 ` Re: VACUUM FREEZE vs plain VACUUM Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2025-07-18 04:31 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin
On Thu, 2025-07-17 at 18:03 -0400, Ron Johnson wrote:
> Does VACUUM FREEZE do something extra or special than to defer autovacuum
> for an extra 50,000,000 transactions?
What it does is set vacuum_freeze_table_age, vacuum_freeze_min_age,
vacuum_multixact_freeze_table_age and vacuum_multixact_freeze_min_age to 0:
if (params.options & VACOPT_FREEZE)
{
params.freeze_min_age = 0;
params.freeze_table_age = 0;
params.multixact_freeze_min_age = 0;
params.multixact_freeze_table_age = 0;
}
So it's going to be an aggressive VACUUM. To quote the documentation:
An aggressive scan differs from a regular VACUUM in that it visits every
page that might contain unfrozen XIDs or MXIDs, not just those that might
contain dead tuples.
And it is going to freeze all tuples that are visible to everybody.
The latter will advance "relfrozenxid" and "relminmxid" for the table,
unless there is an open transaction or something similar that prevents
freezing of a tuple.
To answer your question: the extra thing it does is that it even visits
table pages that have the all-visible flag set, that is, they contain no
dead tuples. That means that it will do more work and use more
of your system's resources.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: VACUUM FREEZE vs plain VACUUM
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-18 04:31 ` Re: VACUUM FREEZE vs plain VACUUM Laurenz Albe <laurenz.albe@cybertec.at>
@ 2025-07-18 06:16 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2025-07-18 06:16 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin
On Fri, 2025-07-18 at 06:31 +0200, Laurenz Albe wrote:
> On Thu, 2025-07-17 at 18:03 -0400, Ron Johnson wrote:
> > Does VACUUM FREEZE do something extra or special than to defer autovacuum
> > for an extra 50,000,000 transactions?
>
> To answer your question: the extra thing it does is that it even visits
> table pages that have the all-visible flag set, that is, they contain no
> dead tuples. That means that it will do more work and use more
> of your system's resources.
... and the biggest impact is that it will modify more tuples, some of them
unnecessarily (because they will be deleted or updated before they reach
an age of 50 million transactions), and that will cause more writing
disk I/O on your system.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2025-07-18 06:16 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-17 22:03 VACUUM FREEZE vs plain VACUUM Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-17 22:26 ` David G. Johnston <david.g.johnston@gmail.com>
2025-07-17 23:29 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-18 01:23 ` David G. Johnston <david.g.johnston@gmail.com>
2025-07-18 03:14 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-18 03:27 ` David G. Johnston <david.g.johnston@gmail.com>
2025-07-18 01:13 ` Rui DeSousa <rui.desousa@icloud.com>
2025-07-18 04:31 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-18 06:16 ` 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