Feedback-ID: i374947ac:Fastmail
Message-ID: <db9db68e-412a-46c5-bae9-614a3bfb2e34@quadratum-braccas.com>
Date: Wed, 12 Nov 2025 11:33:00 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: vacuum process running for more than 2 days, still in scanning
 heap phase
Cc: pgsql-admin@lists.postgresql.org
References: <202511121819.yuoha4w6sprz@alvherre.pgsql>
Content-Language: en-US
From: Sbob <sbob@quadratum-braccas.com>
In-Reply-To: <202511121819.yuoha4w6sprz@alvherre.pgsql>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/db9db68e-412a-46c5-bae9-614a3bfb2e34%40quadratum-braccas.com>
Precedence: bulk


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