Feedback-ID: ie3de48e3:Fastmail
Date: Wed, 12 Nov 2025 19:26:59 +0100
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Sbob <sbob@quadratum-braccas.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: vacuum process running for more than 2 days, still in scanning
 heap phase
Message-ID: <202511121819.yuoha4w6sprz@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <0a1745be-c54d-43b9-a90f-71e4c721c17c@quadratum-braccas.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202511121819.yuoha4w6sprz%40alvherre.pgsql>
Precedence: bulk

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)