Re: vacuum an all frozen table

On Tue, 2024-05-21 at 14:46 -0700, Senor Cervesa wrote:
> I'd like to understand what's happening here and whether there is
> anything I can do to improve the situation.
>
> PostgreSQL v11.22 (yeah, I know. Needs upgrade)
>
> The following 3 autovacuum log entries show a vacuum of an append only
> table that has not had any changes since the end of 5/10/2024. There is
> only 1 page not skipped in each instance yet it takes over 1100 seconds
> to complete. Visibility map shows all frozen. The associated TOAST table
> is similar in numbers except that it completes in sub-second times.
>
> I understand that the vacuum is occurring due to age of
> pg_class.relfrozenxid for the table but what exactly is it referring to
> in these cases? Can that also be frozen or similar? Should I add
> autovacuum_freeze_max_age=400000000 or higher to relopts do reduce
> vacuum frequency.
>
>
> 2024-05-17 09:56:57.167 GMT "" "" LOG:  automatic aggressive vacuum of
> table "workdb1.public.log_entry_20240510": index scans: 0
>          pages: 0 removed, 53551748 remain, 0 skipped due to pins,
> 53551747 skipped frozen
>          tuples: 0 removed, 242384013 remain, 0 are dead but not yet
> removable, oldest xmin: 3245896267
>          buffer usage: 107117593 hits, 123159244 misses, 3 dirtied
>          avg read rate: 856.853 MB/s, avg write rate: 0.000 MB/s
>          system usage: CPU: user: 151.07 s, system: 638.29 s, elapsed:
> 1122.92 s

Strange. Could you run

VACUUM (VERBOSE, FREEZE) public.log_entry_20240510;

and show the result? Perhaps that contains some clues.

Yours,
Laurenz Albe