Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: pgstat vs aset
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: <rtskcyc2o6rt7yebeej4byb62fnv7mcbuiodyuduqmi4ek2mnn@y5pqlz5fbsbd>
Date: Thu, 9 Apr 2026 09:51:26 +0800
Cc: pgsql-hackers@postgresql.org,
 Nathan Bossart <nathandbossart@gmail.com>,
 Tom Lane <tgl@sss.pgh.pa.us>,
 Alexander Lakhin <exclusion@gmail.com>,
 Sami Imseih <samimseih@gmail.com>,
 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 Treat <rob@xzilla.net>,
 satyanarlapuram@gmail.com,
 tndrwang@gmail.com
Content-Transfer-Encoding: quoted-printable
Message-Id: <A79BDDB6-4594-4C24-97AF-D515E261DEDC@gmail.com>
References: <rtskcyc2o6rt7yebeej4byb62fnv7mcbuiodyuduqmi4ek2mnn@y5pqlz5fbsbd>
To: Andres Freund <andres@anarazel.de>
Archived-At: 
 <https://www.postgresql.org/message-id/A79BDDB6-4594-4C24-97AF-D515E261DEDC%40gmail.com>
Precedence: bulk


> On Apr 9, 2026, at 07:04, Andres Freund <andres@anarazel.de> wrote:
>=20
> Hi,
>=20
> 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].
>=20
> 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.
>=20
>=20
> I didn't immediately look at the next few entries:
> =
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=AC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=AC=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
> =E2=94=82          name          =E2=94=82 total_bytes =E2=94=82 =
total_nblocks =E2=94=82 free_bytes =E2=94=82 free_chunks =E2=94=82 =
used_bytes =E2=94=82
> =
=E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=A4
> =E2=94=82 CacheMemoryContext     =E2=94=82   168820784 =E2=94=82       =
     31 =E2=94=82    2363064 =E2=94=82          33 =E2=94=82  166457720 =
=E2=94=82
> =E2=94=82 PgStat Pending         =E2=94=82    27983872 =E2=94=82       =
   3419 =E2=94=82   27846912 =E2=94=82      112699 =E2=94=82     136960 =
=E2=94=82
> =E2=94=82 smgr relation table    =E2=94=82    16777216 =E2=94=82       =
     12 =E2=94=82    2797592 =E2=94=82          50 =E2=94=82   13979624 =
=E2=94=82
> =E2=94=82 MdSmgr                 =E2=94=82     8388608 =E2=94=82       =
     11 =E2=94=82    3475256 =E2=94=82           1 =E2=94=82    4913352 =
=E2=94=82
> =E2=94=82 PgStat Shared Ref      =E2=94=82     5767344 =E2=94=82       =
     88 =E2=94=82      19712 =E2=94=82         352 =E2=94=82    5747632 =
=E2=94=82
> =E2=94=82 PgStat Shared Ref Hash =E2=94=82     4195376 =E2=94=82       =
      2 =E2=94=82        712 =E2=94=82           0 =E2=94=82    4194664 =
=E2=94=82
> =E2=94=82 Relcache by OID        =E2=94=82     4194304 =E2=94=82       =
     10 =E2=94=82      57448 =E2=94=82          24 =E2=94=82    4136856 =
=E2=94=82
> =E2=94=82 TopMemoryContext       =E2=94=82      173264 =E2=94=82       =
      7 =E2=94=82      32968 =E2=94=82          79 =E2=94=82     140296 =
=E2=94=82
> =
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=B4=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=B4=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98
>=20
> 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.
>=20
> 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.
>=20
> That seems decidedly not great.
>=20
> I wonder how bad it would be to teach aset to recognize this =
situation.
>=20
> 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
>=20
> =E2=94=82 PgStat Pending         =E2=94=82      134144 =E2=94=82       =
      3 =E2=94=82     133848 =E2=94=82           0 =E2=94=82        296 =
=E2=94=82
>=20
> Seems like a fairly obvious change.
>=20
>=20
> My local experimental changes attached.
>=20
>=20
> Greetings,
>=20
> Andres Freund
>=20
> [1] =
https://postgr.es/m/fcvnawwq32mamvf66q5i3sk73xudxz5corqlgqljtocepspjps%40y=
pvl6yjzy5xk
> [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=3D# 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=3D# vacuum;
VACUUM
evantest=3D#
evantest=3D# 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=E2=80=99)                                                           =
                                          =20
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=3D# select name, total_bytes, free_bytes, used_bytes, =
total_nblocks, free_chunks
from pg_backend_memory_contexts=20
where name in ('PgStat Pending', 'PgStat Shared Ref', 'PgStat Shared Ref =
Hash=E2=80=99)                                                           =
                                          =20
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=3D#
evantest=3D# vacuum;
VACUUM
evantest=3D#
evantest=3D#
evantest=3D#
evantest=3D# 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=E2=80=99)
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/