Re: pgstat vs aset

> On Apr 9, 2026, at 07:04, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> In [1] I looked at pgstat memory usage after sort of a complaint by Nathan.
> The conversion of "PgStat Shared Ref" to slab seems like an improvement we
> obviously should make [2].
>
> In the email I also showed how much CacheMemoryContext using a lot of memory
> after doing a database wide VACUUM (with ~100k tables), not very surprising.
>
>
> I didn't immediately look at the next few entries:
> ┌────────────────────────┬─────────────┬───────────────┬────────────┬─────────────┬────────────┐
> │ name │ total_bytes │ total_nblocks │ free_bytes │ free_chunks │ used_bytes │
> ├────────────────────────┼─────────────┼───────────────┼────────────┼─────────────┼────────────┤
> │ CacheMemoryContext │ 168820784 │ 31 │ 2363064 │ 33 │ 166457720 │
> │ PgStat Pending │ 27983872 │ 3419 │ 27846912 │ 112699 │ 136960 │
> │ smgr relation table │ 16777216 │ 12 │ 2797592 │ 50 │ 13979624 │
> │ MdSmgr │ 8388608 │ 11 │ 3475256 │ 1 │ 4913352 │
> │ PgStat Shared Ref │ 5767344 │ 88 │ 19712 │ 352 │ 5747632 │
> │ PgStat Shared Ref Hash │ 4195376 │ 2 │ 712 │ 0 │ 4194664 │
> │ Relcache by OID │ 4194304 │ 10 │ 57448 │ 24 │ 4136856 │
> │ TopMemoryContext │ 173264 │ 7 │ 32968 │ 79 │ 140296 │
> └────────────────────────┴─────────────┴───────────────┴────────────┴─────────────┴────────────┘
>
> When I did I was quite surprised to see "PgStat Pending" using so much, as it
> should all be freed after the stats have been submitted, a few seconds later
> at most.
>
> After a moment of worry about having introduced a leak, and adding the
> necessary columns to see more information, it's clear that all the memory was
> actually freed. It's just that we don't ever free aset blocks, even if all the
> constituent memory has been freed. So the overall context size doesn't shrink.
>
> That seems decidedly not great.
>
> I wonder how bad it would be to teach aset to recognize this situation.
>
> But I think for this use case we actually have a more fitting memory context
> type for this workload, i.e. GenerationContext. With that the size after the
> same vacuum is
>
> │ PgStat Pending │ 134144 │ 3 │ 133848 │ 0 │ 296 │
>
> Seems like a fairly obvious change.
>
>
> My local experimental changes attached.
>
>
> Greetings,
>
> Andres Freund
>
> [1] https://postgr.es/m/fcvnawwq32mamvf66q5i3sk73xudxz5corqlgqljtocepspjps%40ypvl6yjzy5xk
> [2] It's a big enough saving that I'm kinda wondering about whether we should
> try to sneak it into 19.
> <pgstat-memory.diff>

I tested this patch by creating a large number of tables and then running VACUUM.

Before the patch:
```
evantest=# select name, total_bytes, free_bytes, used_bytes, total_nblocks, free_chunks
from pg_backend_memory_contexts
where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref Hash')
order by name;
name | total_bytes | free_bytes | used_bytes | total_nblocks | free_chunks
------------------------+-------------+------------+------------+---------------+-------------
PgStat Pending | 16384 | 8368 | 8016 | 5 | 8
PgStat Shared Ref | 8192 | 3672 | 4520 | 4 | 2
PgStat Shared Ref Hash | 9280 | 704 | 8576 | 2 | 0
(3 rows)

evantest=# vacuum;
VACUUM
evantest=#
evantest=# select name, total_bytes, free_bytes, used_bytes, total_nblocks, free_chunks
from pg_backend_memory_contexts
where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref Hash’)
order by name;
name | total_bytes | free_bytes | used_bytes | total_nblocks | free_chunks
------------------------+-------------+------------+------------+---------------+-------------
PgStat Pending | 65536 | 64896 | 640 | 11 | 244
PgStat Shared Ref | 8142848 | 79328 | 8063520 | 997 | 1987
PgStat Shared Ref Hash | 4195392 | 704 | 4194688 | 2 | 0
(3 rows)
```

After the patch:
```
evantest=# select name, total_bytes, free_bytes, used_bytes, total_nblocks, free_chunks
from pg_backend_memory_contexts
where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref Hash’)
order by name;
name | total_bytes | free_bytes | used_bytes | total_nblocks | free_chunks
------------------------+-------------+------------+------------+---------------+-------------
PgStat Pending | 8192 | 3360 | 4832 | 4 | 0
PgStat Shared Ref | 16802 | 13536 | 3266 | 1 | 188
PgStat Shared Ref Hash | 9280 | 704 | 8576 | 2 | 0
(3 rows)

evantest=#
evantest=# vacuum;
VACUUM
evantest=#
evantest=#
evantest=#
evantest=# select name, total_bytes, free_bytes, used_bytes, total_nblocks, free_chunks
from pg_backend_memory_contexts
where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref Hash’)
order by name;
name | total_bytes | free_bytes | used_bytes | total_nblocks | free_chunks
------------------------+-------------+------------+------------+---------------+-------------
PgStat Pending | 37888 | 37592 | 296 | 3 | 0
PgStat Shared Ref | 7274914 | 3672 | 7271242 | 444 | 51
PgStat Shared Ref Hash | 4195392 | 704 | 4194688 | 2 | 0
(3 rows)
```

For PgStat Pending, total_bytes went from about 64kB down to about 36kB.

For PgStat Shared Ref, total_bytes went from about 8MB down to about 7MB.

As a comparison point, PgStat Shared Ref Hash showed no change before and after the patch.

So this does show improvement to me.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/