Обсуждение: vacuum process running for more than 2 days, still in scanning heap phase

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

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?

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

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

On 11/12/25 11:26 AM, Álvaro 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

On 2025-Nov-12, Sbob wrote:

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

On 11/12/25 12:01 PM, Álvaro Herrera wrote:
> On 2025-Nov-12, Sbob wrote:
>
>> 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?


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


>