Horton
Menu

Re: Add pg_stat_autovacuum_priority

Andres Freund <andres@anarazel.de>

View thread
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
Date: 2026-04-08T22:23:54Z
Lists: pgsql-hackers 
Hi,

On 2026-04-08 17:59:36 -0400, Andres Freund wrote:
> 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)

(8104 * 1024) / 102292 = 81.13

81 bytes eemed a bit high, given the struct is 48 bytes.

My first thought is that this was from a debug build, where allocations have
more overhead.  And indeed, in an optimized build it's "just" 7248kB, a
per-entry size of 78.78.

A lot of that is probably due to rounding up in aset.c (and perhaps a bit due
to ALLOCSET_SMALL_SIZES).

Since "PgStat Shared Ref" only ever does one type of allocation, it actually
is a good candidate for slab.  In debug that's 7248kB and optimized it's
5632kB, when using a 16kB block size.  The latter is 56 bytes per entry, where
sizeof(PgStat_EntryRef) is 48 bytes. Which is a pretty reasonable allocator
overhead and 16kB seems not too crazy an allocator block size for this?


Note that even if you just \dt in that database, you have a CacheMemoryContext
of 41MB.  If you VACUUM the caches are 121MB.

Greetings,

Andres Freund

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix double-free in pg_stat_autovacuum_scores.

  2. Add LOG_NEVER error level code.

  3. Add pg_stat_autovacuum_scores system view.

  4. Remove recheck_relation_needs_vacanalyze().

  5. Add elevel parameter to relation_needs_vacanalyze().

  6. Teach relation_needs_vacanalyze() to always compute scores.

  7. Refactor relation_needs_vacanalyze().

  8. Add rudimentary table prioritization to autovacuum.

  9. Fix per-relation memory leakage in autovacuum.

  10. Fix recently-understood problems with handling of XID freezing, particularly