MIME-Version: 1.0
References: <202511121819.yuoha4w6sprz@alvherre.pgsql>
 <db9db68e-412a-46c5-bae9-614a3bfb2e34@quadratum-braccas.com>
In-Reply-To: <db9db68e-412a-46c5-bae9-614a3bfb2e34@quadratum-braccas.com>
From: Pavan Kumar <pavan.dba27@gmail.com>
Date: Wed, 12 Nov 2025 12:59:36 -0600
Message-ID: 
 <CA+M0sHE3aEbH1ZyO_zczkEHM5tnQx1guv4nSoLpRmsdSbPwz1w@mail.gmail.com>
Subject: Re: vacuum process running for more than 2 days, still in scanning
 heap phase
To: Sbob <sbob@quadratum-braccas.com>
Cc: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000dd4aa606436a5f19"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BM0sHE3aEbH1ZyO_zczkEHM5tnQx1guv4nSoLpRmsdSbPwz1w%40mail.gmail.com>
Precedence: bulk

--000000000000dd4aa606436a5f19
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello Sbob,

try to use pg_repack. you need to install extension in the database.
SET maintenance_work_mem =3D '2GB';
VACUUM (VERBOSE, PARALLEL 4) my_table; >> adjust parallel based on your
CPU's
I hope this helps

On Wed, Nov 12, 2025 at 12:33=E2=80=AFPM Sbob <sbob@quadratum-braccas.com> =
wrote:

>
> On 11/12/25 11:26 AM, =C3=81lvaro Herrera wrote:
> > On 2025-Nov-12, Sbob wrote:
> >
> >> We have a vacuum process that has been running for 2 days, the table
> >> is 12GB in total size and vacuum_cost_delay is at 0
> > Is it autovacuum?  Because if so, the autovacuum_vacuum_cost_delay
> > setting would be used instead of this one.  Also check the table config
> > (\d+) in case there are autovacuum settings there, which could make
> > vacuum slower on this particular table.
> >
> > What PG version again?
> >
> > This is not autovacuum, we ran a vacuum freeze manyally
> >
> > Version 15
>
>
>
> >> heap_blks_total    | 571437
> >> heap_blks_scanned  | 344577
> >> heap_blks_vacuumed | 0
> >> index_vacuum_count | 0
> >> max_dead_tuples    | 155388267
> >> num_dead_tuples    | 199013
> > Yeah, this seems really slow.  Maybe have a look at the wait events in
> > pg_stat_activity to see if you can figure out what is holding it back.
> pg_stat_activity shows no wait events and shows state as active
> >> We actually tried a pg_terminate_backend on it and it does not die
> > Hmm, maybe there's something going wrong with it.  I've seen corrupted
> > btree indexes make a vacuum go into infinite loops because of loops in
> > the index structure.  I would take a few backtraces with gdb or such.
> > Maybe if an index is corrupt in that way, it would also explain why it
> > doesn't interrupt.
>
>
> I'll give it a shot, thanks
>
>
>
>
>
>

--=20


*Regards,#!  Pavan Kumar----------------------------------------------*-
*Sr. Database Administrator..!*
*NEXT GENERATION PROFESSIONALS, LLC*
*Cell    #  267-799-3182 #  pavan.dba27 (Gtalk)  *
*India   # 9000459083*

*Take Risks; if you win, you will be very happy. If you lose you will be
Wise  *

--000000000000dd4aa606436a5f19
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello=C2=A0Sbob,<div><br></div><div>try to use pg_repack. =
you need to install extension in the database.</div><div>SET maintenance_wo=
rk_mem =3D &#39;2GB&#39;;</div><div>VACUUM (VERBOSE, PARALLEL 4) my_table; =
&gt;&gt; adjust parallel based on your CPU&#39;s</div><div>I hope this help=
s</div></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr">On Wed, Nov 12, 2025 at 12:33=E2=80=AFPM Sbob=
 &lt;<a href=3D"mailto:sbob@quadratum-braccas.com">sbob@quadratum-braccas.c=
om</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><br>
On 11/12/25 11:26 AM, =C3=81lvaro Herrera wrote:<br>
&gt; On 2025-Nov-12, Sbob wrote:<br>
&gt;<br>
&gt;&gt; We have a vacuum process that has been running for 2 days, the tab=
le<br>
&gt;&gt; is 12GB in total size and vacuum_cost_delay is at 0<br>
&gt; Is it autovacuum?=C2=A0 Because if so, the autovacuum_vacuum_cost_dela=
y<br>
&gt; setting would be used instead of this one.=C2=A0 Also check the table =
config<br>
&gt; (\d+) in case there are autovacuum settings there, which could make<br=
>
&gt; vacuum slower on this particular table.<br>
&gt;<br>
&gt; What PG version again?<br>
&gt;<br>
&gt; This is not autovacuum, we ran a vacuum freeze manyally<br>
&gt;<br>
&gt; Version 15<br>
<br>
<br>
<br>
&gt;&gt; heap_blks_total=C2=A0 =C2=A0 | 571437<br>
&gt;&gt; heap_blks_scanned=C2=A0 | 344577<br>
&gt;&gt; heap_blks_vacuumed | 0<br>
&gt;&gt; index_vacuum_count | 0<br>
&gt;&gt; max_dead_tuples=C2=A0 =C2=A0 | 155388267<br>
&gt;&gt; num_dead_tuples=C2=A0 =C2=A0 | 199013<br>
&gt; Yeah, this seems really slow.=C2=A0 Maybe have a look at the wait even=
ts in<br>
&gt; pg_stat_activity to see if you can figure out what is holding it back.=
<br>
pg_stat_activity shows no wait events and shows state as active<br>
&gt;&gt; We actually tried a pg_terminate_backend on it and it does not die=
<br>
&gt; Hmm, maybe there&#39;s something going wrong with it.=C2=A0 I&#39;ve s=
een corrupted<br>
&gt; btree indexes make a vacuum go into infinite loops because of loops in=
<br>
&gt; the index structure.=C2=A0 I would take a few backtraces with gdb or s=
uch.<br>
&gt; Maybe if an index is corrupt in that way, it would also explain why it=
<br>
&gt; doesn&#39;t interrupt.<br>
<br>
<br>
I&#39;ll give it a shot, thanks<br>
<br>
<br>
<br>
<br>
<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><font color=3D"#990000"><b><font face=3D"&#39;trebuchet ms&#39;, =
sans-serif">Regards,<br><br>#!=C2=A0 Pavan Kumar<br>-----------------------=
-----------------------</font></b>-<br><b><font face=3D"&#39;trebuchet ms&#=
39;, sans-serif">Sr. Database Administrator..!</font></b></font><br><span s=
tyle=3D"border-collapse:collapse;color:rgb(34,34,34);font-family:arial,sans=
-serif;font-size:13px;line-height:19px"><b><span style=3D"font-size:7.5pt;f=
ont-family:Georgia,serif;color:white;background-color:red">NEXT GENERATION =
PROFESSIONALS, LLC</span></b></span><br><font face=3D"&#39;trebuchet ms&#39=
;, sans-serif" color=3D"#990000"><b>Cell =C2=A0 =C2=A0#=C2=A0 267-799-3182 =
#=C2=A0 pavan.dba27 (Gtalk)=C2=A0=C2=A0</b></font><div><font face=3D"&#39;t=
rebuchet ms&#39;, sans-serif" color=3D"#990000"><b>India =C2=A0 # 900045908=
3</b></font><br><br><blockquote style=3D"margin:0px 0px 0px 0.8ex;border-le=
ft:1px solid rgb(204,204,204);padding-left:1ex"><b><font face=3D"&#39;trebu=
chet ms&#39;, sans-serif" color=3D"#000099">Take Risks; if you win, you wil=
l be very happy. If you lose you will be Wise =C2=A0</font></b></blockquote=
></div></div>

--000000000000dd4aa606436a5f19--