From: Murthy Nunna <mnunna@fnal.gov>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Vacuum Question
Thread-Topic: Vacuum Question
Thread-Index: Adwrx/R+fs9cwUnnQDGAg7NUd37y4w==
Date: Mon, 22 Sep 2025 15:06:26 +0000
Message-ID: 
 <SJ0PR09MB6688E7136F78DDD70C8D1028B812A@SJ0PR09MB6688.namprd09.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?37excxjvVnIdIv7GOt8jwOynFv2UxnjaGMUSzlTZ7ydO+56Kbe7lkrwFGu7x?=
 =?us-ascii?Q?k30NPWxt1lBxekpuqdf58D+DP4jCfQvr51Ay+EZD7vDUtdnaWLFfwQokXbUk?=
 =?us-ascii?Q?owUkNRBZSRDn4PuxHIoDaCwf3hHseg2ozeR0i0FOzsV8ukGQiU4yRKONJLsL?=
 =?us-ascii?Q?VYQ1J9wbeqd2BTGTA97BWWztm8EdPIebV1/Sfcd1SFPp3E0igaES7WZiWA2K?=
 =?us-ascii?Q?U2Wrz4eIeya1xLqfwCqpnUR1aldwue+1vzv17bfQ72PU3i3N5TVfvDHO7oSK?=
 =?us-ascii?Q?9vtfpJ3itia4MCppPBrHDTmaAUBKbLZWPiCpcsCxiluOx1m91KQAQN2MqLoj?=
 =?us-ascii?Q?1Yj69M+zYuJ8XUjbifqVCeqnjAVFzNhUZqQj0Cae6rtd3hmv034TfAA71fNN?=
 =?us-ascii?Q?mVqVDIfT3Sja51RymxeyPNi0Kfz+rewxfp5UtZ5GSq/gt+3luFtlSBp/OyPt?=
 =?us-ascii?Q?+FN+nyP9QHFX5a8M1vQ81XaANY/p5qE+j2JRm1HPD1ISrH+8978gPkimiVWf?=
 =?us-ascii?Q?7QNoWxF2uzTdEo0iCt1XuFDi2994vBQp+Wu+n1wh373Ckqf1Jlh7MIBuAJfl?=
 =?us-ascii?Q?x9bt1+QSpFu539mjCmzdyVPwj5y7+lmvMoeLJTzgiNRyIY62LIsX/ZmPV1nw?=
 =?us-ascii?Q?deoYoVoVF5X2zxNj4fDrIPDWaZIP3XurQ8U0+i8xxFReexVCc/31yqqi1F6y?=
 =?us-ascii?Q?lIuBPynqACEjo0QuzYY54sPbwGmGb+iJdVtq8tGwr+BRdNyADGAXTqVjOl1W?=
 =?us-ascii?Q?eUW4yoQBsZzUvHMgR3l8dngZK/qbQ8M32LFp6Iq+bJOxpMNGFWSwqI+rfYdP?=
 =?us-ascii?Q?sSYw1ZFJcDRLnpQYeZ6BWqa6PDdytEbgoFKH2BM18ViU08M5/dwOgYKpYcgM?=
 =?us-ascii?Q?VNQk6S3zh3ryp/CbKtIiSv9jYrRzBFDeh90EVJ1pVrTdUBkEMxsZC/O8dUh3?=
 =?us-ascii?Q?fX3iBO00JbDd84VLlIIo1ghzl9ZvjdwEbFMQIwVJeWoAXDLIgGxJ1cPtKb0Q?=
 =?us-ascii?Q?ZjqAwfo6NIbLtq5JS1lhXhq3bxSAsZkMmla+KsjoKEID4PdA1il3OLUWGAKA?=
 =?us-ascii?Q?MeRPmD1uM3Up3BxB0nfx4F6BjxRb5n+W3BFo+1E4dcAH0PFuP+a1xZyPPS0R?=
 =?us-ascii?Q?thnDiZmd0h0RjvGL7dNFag3YUMQXA1Dr1futu7bM068Emo04ZF191WndApNx?=
 =?us-ascii?Q?fH5xdBJz1hry+9hq7b01kBbQfXp1t25C8qWnk1gSlNojhzA1yDPuIGBP+YVj?=
 =?us-ascii?Q?RhNbcIXshLiRF0BN7dYjHO80ZlpQHjoMN/hsvfQN+uraC6N5ofOpNZwPLMnI?=
 =?us-ascii?Q?XiS5GxDK980Vs8fct3NwSju2wOykoW1fM2Fq/2JmUcOSv5Zwge32ayNb+6pP?=
 =?us-ascii?Q?aH4WZZSv24FHMmay1lvBwka+in4fQH8FXdnv0JA9B9PR6J58q+qp+L5VynaY?=
 =?us-ascii?Q?aJFNwN843t88faRR1RWMbpT63ech/q7hI1l9tNGbwrrH3uvIeCktfebc6Cj8?=
 =?us-ascii?Q?0QK3qxq6jwtuZXkOnOR/XrK2snNoVTI9LbPazfGNJWVImj/zcewZIVHn2woU?=
 =?us-ascii?Q?skMMgvPYE0LD9HNg7Tw8e+mFB691bQf2ikU7VjDP?=
Content-Type: multipart/alternative;
	boundary="_000_SJ0PR09MB6688E7136F78DDD70C8D1028B812ASJ0PR09MB6688namp_"
MIME-Version: 1.0
X-OriginatorOrg: fnal.gov
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: SJ0PR09MB6688.namprd09.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 65db60cf-8232-4825-216c-08ddf9e99a59
X-MS-Exchange-CrossTenant-originalarrivaltime: 22 Sep 2025 15:06:26.8366
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 9d5f83d3-d338-4fd3-b1c9-b7d94d70255a
X-MS-Exchange-Transport-CrossTenantHeadersStamped: BY5PR09MB5923
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/SJ0PR09MB6688E7136F78DDD70C8D1028B812A%40SJ0PR09MB6688.namprd09.prod.outlook.com>
Precedence: bulk

--_000_SJ0PR09MB6688E7136F78DDD70C8D1028B812ASJ0PR09MB6688namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Version 14.13

I have a large database 22 TB, and it has lot of tables. Most of the tables=
 do not change (static). But the age(relfrozenxid) of those tables keep inc=
reasing because there are some other tables in the database that are update=
d. The size of these large static tables are about 200 GB on an average. An=
d to prevent transaction ID wrap around, I have been doing manual vacuum ta=
ble by table (couple of tables a day due to limited WAL disk space). Each t=
able generates WAL size of 90% of the tablesize approx.
e.g
Tablesize =3D 200 GB. Time takes to run vacuum =3D 1 hour 45 minutes. WAL g=
enerated 182 GB

I tried VACUUM FREEZE also, but the WAL generated and time it takes is no s=
ignificantly different.

Following is an example output of a table vacuum:

vacuumdb: vacuuming database "large_db"
INFO:  aggressively vacuuming "public.tab_111"
INFO:  launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO:  table "tab_111": found 0 removable, 527846215 nonremovable row versi=
ons in 15396753 out of 15396753 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 954951860
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 131.12 s, system: 174.14 s, elapsed: 4111.88 s.
INFO:  aggressively vacuuming "pg_toast.pg_toast_17386"
INFO:  table "pg_toast_17386": found 0 removable, 32180684 nonremovable row=
 versions in 7981550 out of 7981550 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 955034530
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 52.96 s, system: 87.86 s, elapsed: 2104.04 s.

Is there a way I can minimize WAL generation? My issue is amount of WAL rat=
her than time it takes to run. Since it is not locking the table I do not m=
ind long run time.
I know one way is to pgdump/restore but it takes a long time and further to=
 that I have to rebuild replicas.

Please note, I have autovacuum turned on and it is doing what it is suppose=
d to do on tables that change. So, there is no issue there.

I very much appreciate any help/advice you can provide.

--_000_SJ0PR09MB6688E7136F78DDD70C8D1028B812ASJ0PR09MB6688namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:12.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;
	font-weight:normal;
	font-style:normal;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Version 14.13<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I have a large database 22 TB, and it has lot of tab=
les. Most of the tables do not change (static). But the age(relfrozenxid) o=
f those tables keep increasing because there are some other tables in the d=
atabase that are updated. The size
 of these large static tables are about 200 GB on an average. And to preven=
t transaction ID wrap around, I have been doing manual vacuum table by tabl=
e (couple of tables a day due to limited WAL disk space). Each table genera=
tes WAL size of 90% of the tablesize
 approx.<o:p></o:p></p>
<p class=3D"MsoNormal">e.g<o:p></o:p></p>
<p class=3D"MsoNormal">Tablesize =3D 200 GB. Time takes to run vacuum =3D 1=
 hour 45 minutes. WAL generated 182 GB<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I tried VACUUM FREEZE also, but the WAL generated an=
d time it takes is no significantly different.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Following is an example output of a table vacuum:<o:=
p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">vacuumdb: vacuuming database &quot;large_db&quot;<o:=
p></o:p></p>
<p class=3D"MsoNormal">INFO:&nbsp; aggressively vacuuming &quot;public.tab_=
111&quot;<o:p></o:p></p>
<p class=3D"MsoNormal">INFO:&nbsp; launched 1 parallel vacuum worker for in=
dex cleanup (planned: 1)<o:p></o:p></p>
<p class=3D"MsoNormal">INFO:&nbsp; table &quot;tab_111&quot;: found 0 remov=
able, 527846215 nonremovable row versions in 15396753 out of 15396753 pages=
<o:p></o:p></p>
<p class=3D"MsoNormal">DETAIL:&nbsp; 0 dead row versions cannot be removed =
yet, oldest xmin: 954951860<o:p></o:p></p>
<p class=3D"MsoNormal">Skipped 0 pages due to buffer pins, 0 frozen pages.<=
o:p></o:p></p>
<p class=3D"MsoNormal">CPU: user: 131.12 s, system: 174.14 s, elapsed: 4111=
.88 s.<o:p></o:p></p>
<p class=3D"MsoNormal">INFO:&nbsp; aggressively vacuuming &quot;pg_toast.pg=
_toast_17386&quot;<o:p></o:p></p>
<p class=3D"MsoNormal">INFO:&nbsp; table &quot;pg_toast_17386&quot;: found =
0 removable, 32180684 nonremovable row versions in 7981550 out of 7981550 p=
ages<o:p></o:p></p>
<p class=3D"MsoNormal">DETAIL:&nbsp; 0 dead row versions cannot be removed =
yet, oldest xmin: 955034530<o:p></o:p></p>
<p class=3D"MsoNormal">Skipped 0 pages due to buffer pins, 0 frozen pages.<=
o:p></o:p></p>
<p class=3D"MsoNormal">CPU: user: 52.96 s, system: 87.86 s, elapsed: 2104.0=
4 s.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Is there a way I can minimize WAL generation? My iss=
ue is amount of WAL rather than time it takes to run. Since it is not locki=
ng the table I do not mind long run time.<o:p></o:p></p>
<p class=3D"MsoNormal">I know one way is to pgdump/restore but it takes a l=
ong time and further to that I have to rebuild replicas.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Please note, I have autovacuum turned on and it is d=
oing what it is supposed to do on tables that change. So, there is no issue=
 there.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I very much appreciate any help/advice you can provi=
de.<o:p></o:p></p>
</div>
</body>
</html>

--_000_SJ0PR09MB6688E7136F78DDD70C8D1028B812ASJ0PR09MB6688namp_--