Subject: vacuum process running for more than 2 days, still in scanning heap phase

From: Sbob <[email-masked]>
Date: Wed, 12 Nov 2025 11:09:36 -0700

All;

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

The process shows as active in pg_stat_activity, and pg_blocking_pids 
returns no blockers

select pg_blocking_pids(1581295);
  pg_blocking_pids
------------------
  {}
(1 row)

A select from from pg_stat_progress_vacuum where pid = 1581295; shows 
the phase as 'scanning heap' and the heap_blks_total, heap_blks_scanned, 
max_dead_tuples and num_dead_tuples  have shown the same values for at 
least the past hour

pid                | 1581295
datid              | 16398
datname            | prod_server_1
relid              | 22532
phase              | scanning heap
heap_blks_total    | 571437
heap_blks_scanned  | 344577
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples    | 155388267
num_dead_tuples    | 199013

We actually tried a pg_terminate_backend on it and it does not die

Thanks in advance for any advice

---

From: Álvaro Herrera <[email-masked]>
Date: Wed, 12 Nov 2025 19:26:59 +0100

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?

> max_dead_tuples    | 155388267

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.

> 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.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)

---

From: Sbob <[email-masked]>
Date: Wed, 12 Nov 2025 11:33:00 -0700

On 11/12/25 11:26 AM, Álvaro Herrera wrote:
> This is not autovacuum, we ran a vacuum freeze manyally
>
> Version 15

> 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
> Maybe if an index is corrupt in that way, it would also explain why it

I'll give it a shot, thanks

---

From: Pavan Kumar <[email-masked]>
Date: Wed, 12 Nov 2025 12:59:36 -0600

Hello Sbob,

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

-- 

*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  *

---

From: Álvaro Herrera <[email-masked]>
Date: Wed, 12 Nov 2025 20:01:15 +0100

> This is not autovacuum, we ran a vacuum freeze manyally

Oh, well, I guess this might be very slow if it decides to freeze a lot
of tuples.  Maybe have a look at the rate at which it generates WAL.

Why did you run vacuum freeze anyway?

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"We’ve narrowed the problem down to the customer’s pants being in a situation
 of vigorous combustion" (Robert Haas, Postgres expert extraordinaire)

---

From: Sbob <[email-masked]>
Date: Wed, 12 Nov 2025 12:08:37 -0700

> Why did you run vacuum freeze anyway?

working with a new client, have not yet uncovered why but they run these 
vacuum freeze ops daily