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

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