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 1wG7fx-005u8p-1k for pgsql-hackers@arkaria.postgresql.org; Fri, 24 Apr 2026 03:57:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wG7fw-004B8C-16 for pgsql-hackers@arkaria.postgresql.org; Fri, 24 Apr 2026 03:57:56 +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 1wG7fv-004B84-2y for pgsql-hackers@lists.postgresql.org; Fri, 24 Apr 2026 03:57:56 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wG7ft-00000002W6z-2qDr for pgsql-hackers@lists.postgresql.org; Fri, 24 Apr 2026 03:57:55 +0000 Received: by mail-ed1-x52b.google.com with SMTP id 4fb4d7f45d1cf-65c4152313fso10149297a12.1 for ; Thu, 23 Apr 2026 20:57:53 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1777003072; cv=none; d=google.com; s=arc-20240605; b=So8Y5Ys25pahKFFUo1kLO8GPECo1Eg8t1gc5oKwYpXn6zn2Ne6z66sobf6cLOwYdlh cDv2l46kEIFfACzNy/UcUCN3tT9d1x1G8bzqcNMHEV99iPJGAaR84lSywE3HEnfu4F6o k8YkpztXLvJvHkDP1p2/zhL7mHED3Dp+rUwX/W+U/M7qexArLg5ytn7XCeVOVBBsS1tw evDQmNk8fYO8GyFcq+pcDks/0+qEFV3CCFhB8Smfus936hFmiE43BLjBmTFRKAVu7j4r WaOHH/naQtv7rtd92GDx5j5YA61rgYnbU3YydGCBJ1AY+rfBcrdhg2IyLD34xi1B30t6 g4jQ== 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=YC86MdGNzUSpboyROOw5aPFNUh5XX7kkgWz83WQLMOg=; fh=+cdNiDkuLse1l9QqPeahuJnRAwQVKxC2tapFS48FUNY=; b=kFKJ+5YtukmKfQ+qxgjedNi9thlhoEVNi6IdXTJ2SA575xT5dUip4/GgqYNXctafqY E9rYoP0Kx+dMgC29Fa1Is6qvRdz/0CaQVyN93MCgS5UgeYnBAKj4ZzKAMARMMP1ubz7M 742Wsel++0uGNwZu/HZgRyxqfrfXxMzLDTlXR5NaBjlfyLL/csYRL+pmjAXmVgoLbMXF /YnHCCj/gouvvP271hbxhW71yHAar63BnQNVV6vgnc5coYEQcgPjkyW6EZhiLy7w2y7q FBdnBOF1qGmYy78gXUx0YVXmPsstQ++9BRUhWQTnPrzB3YpTEIYPvuOIi6Me4YmNaM3x gE+g==; 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=1777003072; x=1777607872; 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=YC86MdGNzUSpboyROOw5aPFNUh5XX7kkgWz83WQLMOg=; b=V+rRSHxwD4qBK0PtHUJ7JfdSZeETScFzqecI0P7TDqkEo59+CT61JHah3DOtzfoDO2 pvwh+TVK4tcY8IOXWWBD3EbFgEpGtlhpHWCsJ1EcQkoJMx1qy7vSyx8HaQGpdgGw0sjU 8BGtlP0FffqTzMX40csLOIdW6Hkz/zDFDorKq4fEEMcugeHNtrKX3+/9rdN81ykGBm+R Mpl5IiDraUGrtpSntOyhMzAOtTcskMAOc/Lroo2kBjJDQI7zk5vYNhGyKB3v7Hv67lr6 s0iLSkRbqYLsUNs8DwdGNfNZLszOlNq0T1LDfojumIbFBSOPMH3QnhGhSYJdrnyFQUX/ r96Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1777003072; x=1777607872; 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=YC86MdGNzUSpboyROOw5aPFNUh5XX7kkgWz83WQLMOg=; b=cxyj+F0fvQ+czy+DrgWC7MbRycqtVMzNNEGvF2TVpWGKASc0It1mBEc257EoFFR3LV 9BApdHmnG+L8FenL0ijuYwh8ToxVzXm99DLp8LDZopROnqMMI1zh/LtLcV2fdr/D2Fb5 SQ03SNXwxVvdvfhhCxddQDO2/NLiVJcplWksUDEcQiaNjAMdJZ6Fgenr2n1lwSm9qGmQ LiYzLwtEpvoA8RrpdR5q4osZSnuFZFWk3V9nLZIkAqbf80BUy8cBcu6BCCmM8EnNbHwn jahmhFdD0zDfUapKwM8bn2b38RwWTdXVJPLTUbC7elPJP+qjT3G4Ya3MHF2Ibj6n4JyY EBTw== X-Gm-Message-State: AOJu0Yxmvx9Ha4P/qbTP8sKPG5l7bY+UB5sKQuQAjBiMWE5qROtzhasK 5mf0vSzTzMHZEM27i4B6JB87LKpKi+NMCZxN9E+zV3A6iMfvB/HzjvhWPS/dTDU5KnWnUdrH5RI EjoQkDtL3CSziUpo/QjwGHwb7T2As7AMULjjMjlc= X-Gm-Gg: AeBDietdIRdCOUG7CNDz9+AUio7cNblK9ESdgcJilB/QTLMxs60L4cIH0nTUeJNTJFW rP8MDYMjIK2vC7CRs3HlBgSleNj6kG9tzy/V7pw9Tn6WjsXcyZBmhx23P16kRoDGeQMyqk2WYVj 2OD+w1mxQijZQ+LNjeSIldVrRT9pK9xj95z5mELF7f79O3XdkLT5MkgVJQr+p8dqrCXPU6vZPVs BTkcMrUgGXhBnKlYWClO0vHjPWRWmZHVz2Yy3FB5YfrBR9eJwfX7RJn/T16ONZVLMxktw2aZ/nF mAz6whirb2I4WkxAx7SD5O6hLcftlHmfwvB51qtVYbZB+1r7 X-Received: by 2002:a17:907:961b:b0:b9d:33b5:6ba1 with SMTP id a640c23a62f3a-ba41c1b6fbdmr1521180666b.15.1777003072088; Thu, 23 Apr 2026 20:57:52 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: wenhui qiu Date: Fri, 24 Apr 2026 11:57:40 +0800 X-Gm-Features: AQROBzBHC_hyVeZBVAlricRoqOh9-q3NhH0hCZ7EdU2McAlXfERIE3Q3OKw48GA Message-ID: Subject: Re: New vacuum config to avoid anti wraparound vacuums To: Mok Cc: "pgsql-hackers@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000e06d3406502cc4de" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e06d3406502cc4de Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Mok > I have an idea for managing vacuums. When managing vacuums it can sometimes be a struggle to manage the config settings for them, especially when trying to avoid anti > wraparound vacuums. Some tables are massive and needs scale factor settings in the fractions of percent, sometimes, with no guarantee that enough actions cross that > threshold. Increasing the autovacuum_freeze_max_age to keep up with an growing number of transactions across an instance is just kicking the can down the road. Engineers run > backfills that throw off any calculations you may use to work out these scale factors and using the same values across an instance is too simple while managing individually > for tables can be too messy. Or you run a cron type job so you don't have to think about it at all. I think you can refer to these two parameters https://www.postgresql.org/docs/18/runtime-config-vacuum.html#GUC-AUTOVACUU= M-VACUUM-MAX-THRESHOLD https://www.postgresql.org/docs/18/runtime-config-vacuum.html# vacuum_freeze_table_age If you're concerned about wraparound, you can reduce the value of `VACUUM-VACUUM-MAX-THRESHOLD`,vacuum_freeze_table_age, Shouldn't you be focusing on why the blocked table can't be vacuum-freeze? Thanks On Thu, Apr 23, 2026 at 10:10=E2=80=AFPM David Rowley 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 > > > --000000000000e06d3406502cc4de Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Mok
> I have an idea for managing vacuums. = When managing vacuums it can sometimes be a struggle to manage the config s= ettings for them, especially when trying to avoid anti
> wraparound = vacuums. Some tables are massive and needs scale factor settings in the fra= ctions of percent, sometimes, with no guarantee that enough actions cross t= hat
> threshold. Increasing the autovacuum_freeze_max_age to keep up= with an growing number of transactions across an instance is just kicking = the can down the road. Engineers run
> backfills that throw off any = calculations you may use to work out these scale factors and using the same= values across an instance is too simple while managing individually
&g= t; for tables can be too messy. Or you run a cron type job so you don't= have to think about it at all.
I think you can refer to thes= e two parameters
If you're concerned about wrap= around, you can reduce the value of `VACUUM-VACUUM-MAX-THRESHOLD`,vacuum_freeze_table_age,=C2=A0Should= n't you be focusing on why the blocked table can't be vacuum-freeze= ?


Thanks

On Thu, Apr 23, 2026 at 10:10=E2=80=AFPM David Rowley <dgrowleyml@gmail.com> wrote:
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 would be= triggered when the table reached 160million with a default 200million sett= ing.
> >
> > 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, w= hich 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" t= ext
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.postg= resql.org/docs/18/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND


--000000000000e06d3406502cc4de--