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.94.2) (envelope-from ) id 1v0id8-00HLhJ-Lf for pgsql-admin@arkaria.postgresql.org; Mon, 22 Sep 2025 15:39:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v0id7-0019ay-3T for pgsql-admin@arkaria.postgresql.org; Mon, 22 Sep 2025 15:39:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v0id6-0019aq-N0 for pgsql-admin@lists.postgresql.org; Mon, 22 Sep 2025 15:39:04 +0000 Received: from mail-oa1-x34.google.com ([2001:4860:4864:20::34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v0id2-002DU1-0q for pgsql-admin@postgresql.org; Mon, 22 Sep 2025 15:39:04 +0000 Received: by mail-oa1-x34.google.com with SMTP id 586e51a60fabf-321289dee84so2201777fac.1 for ; Mon, 22 Sep 2025 08:39:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758555539; x=1759160339; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=JGcuz9u+NAJHbj/wN1YUtopvFzxiRfrKdyhMA2DGFjY=; b=m1g8GPDa6dd7xucegbZ/kZFo7UD2jxwTMtLTidJX3kroAuomkoDtvJgd9VJUvGOoEb 8AyHfQA31haj29+yfVkixCqPH6lxZ9PvdrS59LIQNr7pQ2Dba3edZJH7Vw8X09SClxfS R/gXl4K2QnOEU5JgUQsudMrDqxtWx316U8WFZ+hIWNurajcEntTiRiIKXSWdO6FxofF1 dkplgdrRO5SH0rCe8L4NA1rxjprGna4QpTb0+qC+aI4VeFM32dbJLYQgJ7AIcqY52/jO Pte4iSWRnTgf6/bMdjK5QWivbMS6R0HwdxNpILwHeoNMlgCoR9OHtF5t6WyARcQUQ1Kf x1Ow== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758555539; x=1759160339; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=JGcuz9u+NAJHbj/wN1YUtopvFzxiRfrKdyhMA2DGFjY=; b=fOCd9P9MmENomcJVvIA0Wey6JRhhBLeYpg0MalPJshsO+p5Va5bV8TCrAlDfgqlrHe GFz6Ryv8ZnteBeS2ZhlgPlnbz8BANDYtbvPo+vd/IF+vuAdPdU/CyJT5VKT4Qu5OnZ/B Ws0JbRmKpsBxb4VbJaCQP56FExF6aO8MvLsRnB5tNRD0qk27X2TpUMmNcbd+Lf3GcbrH HOPJk1Wgl/j4Nc8CdPPaov0g83UNFsQNpAQ5qpAK0lnyIJ4cwYdXeRmJQVcytsgBh77v tMOX93xM58xn2HfJ4s0Q0rPDjby+RGBYqPGmmrQ/uWHiK1e1G5vUr8SWxFaj6B9aKyHg TRrA== X-Gm-Message-State: AOJu0Yyluu8rZFrO0fviyTummaoWFqyA+hW9hkK14dcNz6XN2FRkLrFU XsriAmGqJdcDCEHeoGnYV4abd9fKmY054FQpesQLV1r03Ozzue3IlZzmeMaBNg+cg4VQxGqMx0p aMyhYbQQXfyN3BMWkNui4kfC2eo6DZtm1hkKw X-Gm-Gg: ASbGncuxvd6iPuV6XPqirQgVEtXFU6NQGXktSWF56kH/0OIafbE/nDxe+3vJiBgpOrl xoKgF2W3XfxnPzjlMtXFB+6uMkXliPbdXDQXvgJjewvps5sI3BXcOPNyX1oPlr8Xjnsla/gmZKo B1XzL3JeLGvINi/4NQl9b3QdPBlsWrHtA5NpRg+uHYCjJl+MBubJh9HPFcd3YQtNTws3ytgVJFC MMamg9c X-Google-Smtp-Source: AGHT+IFvzemdmNwzMnJmN/WEYBCOFhX8nKyeTtjwNvgY2dOGZqJgdQ8i4O+el6SKIkVsaYMyBBjvVWnkuDo3SVD96Is= X-Received: by 2002:a05:6870:492:b0:314:b6a6:6877 with SMTP id 586e51a60fabf-33bb66dc2ffmr7122580fac.45.1758555539313; Mon, 22 Sep 2025 08:38:59 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 22 Sep 2025 11:38:48 -0400 X-Gm-Features: AS18NWDCde6hB13nHiiWJM2v5lf0u_3fv0QgVJU8RoV3euWMOysu29uVkU1mgXY Message-ID: Subject: Re: Vacuum Question To: pgsql-admin , Murthy Nunna Content-Type: multipart/alternative; boundary="0000000000003d1863063f659e81" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003d1863063f659e81 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I think you asked the same question 11 years ago. =F0=9F=98=80 - Seriously, though, 200GB is less than 1% of 22TB. There are bigger problems if you're running that razor-thin on disk space. - Is the transaction rate on the active tables sooo high that there's a real chance of wrap-around? - According to my interpretation of the docs, if you VACUUM FREEZE the big static tables, then you won't need to vacuum them again, nor worry about wrap-around problems. https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAR= OUND > PostgreSQL reserves a special XID, FrozenTransactionId, which *does not > follow the normal XID comparison rules* and is *always considered older > than every normal XID*. Frozen row versions are treated as if the > inserting XID were FrozenTransactionId, so that they *will appear to be *= *=E2=80=9Cin > the past=E2=80=9D** to all normal transactions regardless of wraparound i= ssues*, > and so such row versions will be valid until deleted, no matter how long > that is. On Mon, Sep 22, 2025 at 11:06=E2=80=AFAM Murthy Nunna wro= te: > 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 increasing because there are some other tables in the database that > are updated. The size of these large static tables are about 200 GB on an > average. And to prevent transaction ID wrap around, I have been doing > manual vacuum table by table (couple of tables a day due to limited WAL > disk space). Each table 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 > generated 182 GB > > > > I tried VACUUM FREEZE also, but the WAL generated and time it takes is no > significantly 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 > versions in 15396753 out of 15396753 pages > > DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 95495186= 0 > > 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: 95503453= 0 > > 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 > rather than time it takes to run. Since it is not locking the table I do > not mind 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 > supposed to do on tables that change. So, there is no issue there. > > > > I very much appreciate any help/advice you can provide. > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000003d1863063f659e81 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I think you asked the same=C2=A0question 11 years ago= .=C2=A0=F0=9F=98=80
  • Seriously, though, 200GB is less than= 1% of 22TB.=C2=A0 There are bigger problems if you're running that raz= or-thin on disk space.
  • Is the transaction rate on the active tables= sooo=C2=A0high that there's a real chance of wrap-around?
  • Acco= rding to my interpretation of the docs, if you VACUUM FREEZE the big static= tables, then you won't need to vacuum them again, nor worry about wrap= -around problems.
PostgreSQL=C2= =A0reserves a special XID,=C2=A0FrozenTransactionId, which d= oes not follow the normal XID comparison rules and is always conside= red older than every normal XID. Frozen row versions are treated as if = the inserting XID were=C2=A0FrozenTransactionId, so that they will appear to be=C2=A0=E2=80=9Cin the past=E2=80=9D=C2=A0to all normal transactions regardless of wraparound issues= , and so such row versions will be valid until deleted, no matter how long = that is.

On Mon, Sep 22, 2025 at 1= 1:06=E2=80=AFAM Murthy Nunna <mnunna@= fnal.gov> wrote:

Version 14.13

=C2=A0

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.

e.g

Tablesize =3D 200 GB. Time takes to run vacuum =3D 1= hour 45 minutes. WAL generated 182 GB

=C2=A0

I tried VACUUM FREEZE also, but the WAL generated an= d time it takes is no significantly different.

=C2=A0

Following is an example output of a table vacuum:=

=C2=A0

vacuumdb: vacuuming database "large_db"=

INFO:=C2=A0 aggressively vacuuming "public.tab_= 111"

INFO:=C2=A0 launched 1 parallel vacuum worker for in= dex cleanup (planned: 1)

INFO:=C2=A0 table "tab_111": found 0 remov= able, 527846215 nonremovable row versions in 15396753 out of 15396753 pages=

DETAIL:=C2=A0 0 dead row versions cannot be removed = yet, oldest xmin: 954951860

Skipped 0 pages due to buffer pins, 0 frozen pages.<= u>

CPU: user: 131.12 s, system: 174.14 s, elapsed: 4111= .88 s.

INFO:=C2=A0 aggressively vacuuming "pg_toast.pg= _toast_17386"

INFO:=C2=A0 table "pg_toast_17386": found = 0 removable, 32180684 nonremovable row versions in 7981550 out of 7981550 p= ages

DETAIL:=C2=A0 0 dead row versions cannot be removed = yet, oldest xmin: 955034530

Skipped 0 pages due to buffer pins, 0 frozen pages.<= u>

CPU: user: 52.96 s, system: 87.86 s, elapsed: 2104.0= 4 s.

=C2=A0

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.

I know one way is to pgdump/restore but it takes a l= ong time and further to that I have to rebuild replicas.

=C2=A0

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.

=C2=A0

I very much appreciate any help/advice you can provi= de.



--
Death to <Redacted>, and butter sauce= .
Don't boil me, I'm still alive.
<Redacted>= lobster!
--0000000000003d1863063f659e81--