Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wGB8B-005xpc-1y for pgsql-hackers@arkaria.postgresql.org; Fri, 24 Apr 2026 07:39:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wGB8A-005EUX-2R for pgsql-hackers@arkaria.postgresql.org; Fri, 24 Apr 2026 07:39:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wGB8A-005EUP-15 for pgsql-hackers@lists.postgresql.org; Fri, 24 Apr 2026 07:39:18 +0000 Received: from mail-ej1-x62e.google.com ([2a00:1450:4864:20::62e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wGB88-00000002Xjw-0H1U for pgsql-hackers@lists.postgresql.org; Fri, 24 Apr 2026 07:39:17 +0000 Received: by mail-ej1-x62e.google.com with SMTP id a640c23a62f3a-b9c745e18a0so1282751066b.1 for ; Fri, 24 Apr 2026 00:39:16 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1777016354; cv=none; d=google.com; s=arc-20240605; b=TXYT43mkR89tPvUcUyNarzX+5mzcuFUdLqPvDrbOWySHWcoKxTRsmRJ/Y/vGvHeALt JUkJ8vFqvk+G/gQ8aJ84Wd/bjVbJ9z3IYhdYpbOmbCIpdTUWfBX8B4tla5k1PuMiPLpt F95xGYtD2gTjI2aLovxddIGiGAyFn982m3nJGV2g91cRe9g0EYa6sgiQ6wKm7lSmslpz payiRUFKsxTbMLz1TysMFYFC7jSSubAV2zZqlzNFKQShlLFz7D28d3Vl3Ujf/KQH84c/ EeZ3VGY9StKjxQ6AqJ3a8bjYtQHs9bbK8+qks+H81TD48IuaxmcT5ihpoQuaGC4R/g8t Ybrg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=WdclTX7BkfrqHVp3rSsBY12In0ePRaZbE4ErYtnqY08=; fh=cApJX5ckWnrMrSxab2W0JZP/w0EkYFhLuRhYxX4jGlM=; b=j6qeQZ+Xfo0WX49NjMEBjX7lxnwBJ90NTH/Qar0zsNqcz9zNRwGdTsG2YyQNpXowAn HZX/b9dYiNW08pl+SbK7OUUzde7r+u2B6KkfGNG56AfSHMGFd7Q64kp2u/aB/FGx8K7/ qyvP6Wozbg197tR3wLiJsg9sFtYIqySVNW+jIVTNr4Ky+lBTqOu/KJssW+DMSjIU+YLG U2OHsdTj4VKZdi9NY7oANAdORQkMtS7EzONJT2UfDvo6Mged6UnH1p7WNDQXTUhU2jTw QZCbgCXDtG3/iWDypf0DqQjRiuLadB5OhPTT1D9qpP0tPw0lE5xkKj4X5+VRIBJwGGVu P0pA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1777016354; x=1777621154; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=WdclTX7BkfrqHVp3rSsBY12In0ePRaZbE4ErYtnqY08=; b=mqyH9zu4/Tc0u7745m8G0D9+t6n1yUvNzi4LD6OWaCZI0AJZzbnZAOMh+QtF677GPz P/rA5IRR6aYDeTfu+q2z3HJnvgma2gUabnzX5XpLXdKliGg6/DZpoLIHxElfXVqCc4Xd wNUoPBg+E0U4kfSFGf69heU2qvXKew8PNuVsl4JwY9n32ThqXLy/GYfPnupKpe//LVKV HmeutScnun3P+8OAEs1VfcUGoBk9ArFX8O4TyLANVZHVHHRC8i+zH6fXZzlNtAbZ3Ly3 QWkXd4o57pbuOCmXieVKeaG+HJ8r3yW0s8z/8U0zvqrxUMwi8YqdcY6BfGk0L3gG595k AbKw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1777016354; x=1777621154; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=WdclTX7BkfrqHVp3rSsBY12In0ePRaZbE4ErYtnqY08=; b=DVpX0kUFt6DwnFxDCicl8E9Paeh5d50ChswUks3GxubsS7nc3XOVGVCLkOtyYFCGJv Q4d/JbWgJKrUwhhYgY58zWiUV78/KVxWL1FjVcMnPDrVuHy9V4nBZHu2w5J4RC/ZZLNp glltfLuOYmm9ZmADfpembHTvjdxFRs20FUTH1Vq172RPbymK6iXTRNHEURkUK2wz0EBP AKCOGM/sdMA5k5hk7qjHiUuu/ZZNVRdl7ieMhtf2STZngu0G5eHUuX9G+nhj7CWVlWl3 7lREvBuvhCl1A92NRnpFwC9B413uBPzB/bsqYqgfQOBoJgPLAO/eMHwdnRSZINpjv7IH y5EQ== X-Forwarded-Encrypted: i=1; AFNElJ/TGB2Pr+3vXMKjLNucpaquwt+2oTTvFRVpaARucUblVLvo0ijHECzFPStoPpoE+ppHWUzGmRPqNWztBOMP@lists.postgresql.org X-Gm-Message-State: AOJu0YxR9S81VgHWeKJj+XJYFZba65NPDE4VRYkxFN/iLHIO/FxVz7xT NEck1lrzE33x0uhgT7yzM2svYWiBlicTjaRtkclWWkI7hHVKN1OWfu2PZ/dJ3+8Oh0UPrrz4rsb cqGv2EPmmT+PMRaqJgIP2Itxb/T2xFRI= X-Gm-Gg: AeBDievkrYKg03GdQ3AFWT/p/l8lCU2wm2Y+QNbWibfREB1W0m51MR4kLfd4FW5pq4N OE9u5SEc7bQojB3F3Qdkg0yxWlrrbqaCs48105ss4wGlO+UN8yfl4oYAlwMFltiJcz+fXjnXCPt GXw5GNtUX5dwa6wcyTyIcCh6mkccPOUmCZIgucL0EIcB4cmN4vmd8Zk8DSuthtkR6Fw/DVmxbAN GyBHCFr38lUt6mBHUm32OlFZbfI0zA5xyTrNNiyi6LgOfABpxwPgwGie6MjHIDPv2QD/wox+VEs RsTdcOslYHMdoIChjtfvj8QClc6tUX5u94FaZ/u8+AD9xcsZ X-Received: by 2002:a17:906:ef04:b0:b9d:ec70:4ad8 with SMTP id a640c23a62f3a-ba422add0d4mr1587939666b.13.1777016353381; Fri, 24 Apr 2026 00:39:13 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: wenhui qiu Date: Fri, 24 Apr 2026 15:39:00 +0800 X-Gm-Features: AQROBzD1NVQBZbBqUB9VGxiFtCk-bzv7zCsLiDGmoY4Xp831cl3zBSj3bWxMF0E Message-ID: Subject: Re: New vacuum config to avoid anti wraparound vacuums To: Mok Cc: David Rowley , "pgsql-hackers@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000080e11c06502fdc3e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000080e11c06502fdc3e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable HI Mok On Fri, Apr 24, 2026 at 2:16=E2=80=AFPM Mok wrote: > > > On Thursday, April 23rd, 2026 at 3:10 PM, David Rowley < > dgrowleyml@gmail.com> wrote: > > > On Fri, 24 Apr 2026 at 01:04, Mok wrote: > > > > > > On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley < > dgrowleyml@gmail.com> wrote: > > > > > > > On Thu, 23 Apr 2026 at 08:19, Mok wrote: > > > > > For example, set to 0.8 a 'standard' vacuum would be triggered > when the table reached 160million with a default 200million setting. > > > > > > > > If that's what you want, why wouldn't you set the > > > > autovacuum_freeze_max_age to 160million? > > > > > > Because that would trigger a 'to-prevent-wraparound' vacuum, which is > what this change is trying to avoid. > > > > Yes, it would. Why do you want to prevent them? I believe a few people > > have been alarmed in the past about the "to prevent wraparound" text > > in pg_stat_activity or when they saw those words in the logs. The > > default 200 million autovacuum_freeze_max_age setting triggers an > > autovacuum when it's less than 10% of the way into exhausting the > > transaction space for the table. What you're proposing with an > > autovacuum_age_scale_factor of 0.1 sounds like it would result in an > > auto-vacuum when only 1% of the transaction ID space is consumed! I > > think you're under the false impression that these anti-wraparound > > vacuums are bad. They're not. > > > > There's some documentation that might be worthwhile reading in [1]. > > > > David > > > > [1] > https://www.postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAP= AROUND > > > > > On large tables they can be quite inconvenient so avoiding them is > preferable. My example of 0.1 is to test the patch if you tried it. The > range for this > > setting is 0.1 -> 1 with the latter effectively rendering the setting > moot. > I don't know where you got that idea from. For example have a table with = 1 > billion records, autovacuum_vacuum_scale_factor =3D 0.01 , > 50+1000000000 *0.01 =3D 10000050 ,you can reduce > autovacuum_vacuum_max_threshold substantially lower than 10000050 =EF=BC= =8C > vacthresh =3D (float4) vac_base_thresh + vac_scale_factor * reltuples; > if (vac_max_thresh >=3D 0 && vacthresh > (float4) vac_max_thresh) > vacthresh =3D (float4) vac_max_thresh; > > There's no fundamental difference between this and your parameter > --00000000000080e11c06502fdc3e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
HI Mok

On Fri, Apr 2= 4, 2026 at 2:16=E2=80=AFPM Mok <gurmokh@protonmail.com> wrote:


On Thursday, April 23rd, 2026 at 3:10 PM, David Rowley <dgrowleyml@gmail.com> wrot= e:

> On Fri, 24 Apr 2026 at 01:04, Mok <gurmokh@protonmail.com> wrote:
> >
> > On Thursday, April 23rd, 2026 at 4:44 AM, David Rowley <dgrowleyml@gmail.com> wrote:
> >
> > > On Thu, 23 Apr 2026 at 08:19, Mok <gurmokh@protonmail.com> wrote:=
> > > > For example, set to 0.8 a 'standard' vacuum wou= ld be triggered when the table reached 160million with a default 200million= setting.
> > >
> > > If that's what you want, why wouldn't you set the > > > autovacuum_freeze_max_age to 160million?
> >
> > Because that would trigger a 'to-prevent-wraparound' vacu= um, which is what this change is trying to avoid.
>
> Yes, it would. Why do you want to prevent them? I believe a few people=
> have been alarmed in the past about the "to prevent wraparound&qu= ot; text
> in pg_stat_activity or when they saw those words in the logs. The
> default 200 million autovacuum_freeze_max_age setting triggers an
> autovacuum when it's less than 10% of the way into exhausting the<= br> > transaction space for the table. What you're proposing with an
> autovacuum_age_scale_factor of 0.1 sounds like it would result in an > auto-vacuum when only 1% of the transaction ID space is consumed! I > think you're under the false impression that these anti-wraparound=
> vacuums are bad. They're not.
>
> There's some documentation that might be worthwhile reading in [1]= .
>
> David
>
> [1] https://www.= postgresql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
>

> On large tables they can be quite inconvenient s= o avoiding them is preferable. My example of 0.1 is to test the patch if yo= u tried it. The range for this=C2=A0
> setting is 0.1 -> 1 = with the latter effectively rendering the setting moot.
I don= 't know where you got that idea from. For example=C2=A0have a table wit= h 1 billion records,=C2=A0autovacuum_vacuum_scale_factor =3D 0.01 ,=C2=A0
50+1000000000 *0.01 =3D=C2=A010000050 ,you can reduce=C2=A0autovacuum_vacuum_max_threshold=C2=A0= =C2=A0substantially lower than 10000050 =EF=BC=8C
vacthresh =3D (float4) va= c_base_thresh + vac_scale_factor * reltup= les;
if (vac_max_thresh >=3D = 0 &&= vacthresh <= span style=3D"color:rgb(212,212,212)">> (float4) vac_max_thresh)
v= acthresh =3D (float4) vac_max_thresh;

There's n= o fundamental difference between this and your parameter
--00000000000080e11c06502fdc3e--