Re: High System CPU Usage on Selects Seemingly Caused By Vacuum of Same Table

There are not that many tables, 175.

There are a decent number of transactions on the database, 35k a second
during the day. I ran a vacuum verbose on table1 today and got this output
on the pages frozen.

frozen: 2813436 pages from table (0.70% of total) had 7028123 tuples frozen

On Fri, Jan 31, 2025 at 5:46 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

> On Fri, Jan 31, 2025 at 5:28 PM Joshua Banton <bantonj(at)gmail(dot)com> wrote:
> > The issue mostly manifests near the end of the "scanning heap" phase of
> vacuuming of one of our largest tables, we'll call table1. RDS Performance
> Insights reports that selects on table1 start to wait on cpu, where
> previously it didn't even show up in the top 25 queries by wait. It doesn't
> always happen, but if there is a larger than usual number of selects on
> table1 it is more likely to happen.
>
> Does this database also have many tables? As in thousands of tables?
>
> I am reminded of this issue:
>
>
> https://www.postgresql.org/message-id/flat/da3205c4-5b07-a65c-6c26-a293c6464fdb%40postgrespro.ru
>
> I've heard of this happening when an aggressive VACUUM updates
> relfrozenxid on a larger table.
>
> --
> Peter Geoghegan
>