Feedback-ID: id4a34324:Fastmail
Date: Wed, 8 Apr 2026 17:59:36 -0400
From: Andres Freund <andres@anarazel.de>
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Alexander Lakhin <exclusion@gmail.com>,
	Sami Imseih <samimseih@gmail.com>,
 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
	Robert Treat <rob@xzilla.net>, satyanarlapuram@gmail.com,
	pgsql-hackers <pgsql-hackers@postgresql.org>, tndrwang@gmail.com
Subject: Re: Add pg_stat_autovacuum_priority
Message-ID: <3nob5fjwrar3shffl5yo7im4qlnxxa475pi6fohemodsvwpl5g@l5cbmz5dqtjm>
References: 
 <CAA5RZ0vRP-W2wJD2OxEb-=VGj2sp5pMCqHQg9YJiuDVPhaY5jQ@mail.gmail.com>
 <adQsdvPPNviWMCXb@nathan>
 <5684f479-858e-4c5d-b8f5-bcf05de1f909@gmail.com>
 <3077290.1775668665@sss.pgh.pa.us>
 <adaWuTR7oCKodH7k@nathan>
 <3097984.1775674400@sss.pgh.pa.us>
 <adan_E0o69p81lIj@nathan>
 <3101163.1775676098@sss.pgh.pa.us>
 <u3akkfw3vaocviudt6f7ft4kxjc2273rh3tfhz5rfwg6jrcc2t@wurwws6wgjjz>
 <adbHYQMZ0pHQttWa@nathan>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <adbHYQMZ0pHQttWa@nathan>
Archived-At: 
 <https://www.postgresql.org/message-id/3nob5fjwrar3shffl5yo7im4qlnxxa475pi6fohemodsvwpl5g%40l5cbmz5dqtjm>
Precedence: bulk

Hi,

On 2026-04-08 16:23:45 -0500, Nathan Bossart wrote:
> On Wed, Apr 08, 2026 at 04:40:03PM -0400, Andres Freund wrote:
> > Note that the whole cached state does automatically get reset at the end of
> > the transaction (AtEOXact_PgStat()->pgstat_clear_snapshot()), just like it did
> > before the shmem stats stuff.
>
> I see a lot of memory used for the pgStatEntryRefHash table, too (e.g., ~16
> MB for 100K tables).

That's not the stats snapshot data. That's basically a semi-permanent pin on
the shared stats entry so that we don't continually need to do lookups in the
shared stats hash table. Without that you'd have a *lot* of contention on the
shared hash table and the DSA for the stats entries themselves.

That's right now just the price you pay for accessing and generating stats. It
was way more before shared memory stats (as in a factor of 10 or so, and it
was happening way more often and it caused the full database's stats worth of
memory usage even if you just accessed a portion of the stats).


It's possible that we could more frequently clean out references, but when to
precisely do that and to how much is not a trivial problem - we haven't
tackled it for our catcache/relcaches either.


I don't think it should be quite 16MB for 100k tables though? I see

┌────────────────────────┬────────────────┐
│          name          │ pg_size_pretty │
├────────────────────────┼────────────────┤
│ PgStat Shared Ref      │ 8104 kB        │
│ PgStat Shared Ref Hash │ 4097 kB        │
│ CacheMemoryContext     │ 1024 kB        │
└────────────────────────┴────────────────┘

after doing

SELECT sum(score) FROM pg_stat_autovacuum_scores;

in this database:

SELECT relkind, count(*) FROM pg_class GROUP BY relkind;
┌─────────┬────────┐
│ relkind │ count  │
├─────────┼────────┤
│ S       │      1 │
│ i       │    182 │
│ r       │ 102292 │
│ t       │     43 │
│ v       │    167 │
└─────────┴────────┘
(5 rows)


> What's interesting is that I cannot reproduce similar usage with views like
> pg_stat_all_tables.

Hm? That would be very surprising.  Is it possible you used LIMIT 1 or such?
The way the pg_stat_all_tables view works it only accesses stats data for
returned rows (because it does all the stats lookups with individiual columns,
which also makes it really slow, but avoids having to form datums for not
returned columns).


in a new session:

postgres[3109075][1]=# SELECT sum(seq_tup_read) FROM pg_stat_all_tables LIMIT 10;
┌────────────┐
│    sum     │
├────────────┤
│ 3223043706 │
└────────────┘
(1 row)

Time: 723.223 ms
postgres[3109075][1]=# SELECT name, pg_size_pretty(total_bytes) FROM pg_backend_memory_contexts ORDER BY total_bytes DESC LIMIT 3;
┌────────────────────────┬────────────────┐
│          name          │ pg_size_pretty │
├────────────────────────┼────────────────┤
│ PgStat Shared Ref      │ 8104 kB        │
│ PgStat Shared Ref Hash │ 4097 kB        │
│ CacheMemoryContext     │ 1024 kB        │
└────────────────────────┴────────────────┘

I.e. byte for byte identical to the av case.


> If memory was not a concern, I think the "bool *may_free" idea would be
> fine.  But assuming it is, we will probably need to do something more
> creative.

I don't think you're going to be able to quickly do anything about this.  I
think this is a completely independent issue of the *may_free thing.

Greetings,

Andres Freund