BUG #18675: Postgres is not realasing memory causing OOM - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18675
Logged by:          Maciej Jaros
Email address:      eccenux@gmail.com
PostgreSQL version: 16.4
Operating system:   Ubuntu 22.04
Description:

Hi. We have a DB server running on a VM hosted on Proxmox (ZFS). We recently
upgraded from PG10 to PG16 and noticed an increase in load and, most
importantly, **uncontrolled** RAM usage growth. In my calculations RAM usage
should stay under 20GB. In practice, we found that even 50GB wasn’t enough
for PostgreSQL, as OOM killer killed the service. What’s strange is that
once OOM kills PostgreSQL, the memory drops to zero, indicating that nothing
else was using that memory. After the OOM, PostgreSQL runs fine again, but
requires intervention. So as a workaround, we’re manually restarting it from
time to time.

This is a bug, right? I mean, surely PostgreSQL shouldn't exceed 20GB, maybe
30GB if I missed something. I've searched the web and found posts asking how
to set an absolute memory limit for PostgreSQL, only to learn that no such
setting exists. Perhaps there should be an absolute limit, or maybe
PostgreSQL should include garbage collection?

RAM for PG calculation (autovac is disabled, cron runs at night instead):
```
shared_buffers = 12544
temp_buffers = 8
work_mem = 6422/1024
max_connections = 500
RAMforPG = shared_buffers + (temp_buffers + work_mem) * max_connections;
console.log({RAMforPG}); -> // 19679.74
```

One of SQL causing large tables to be scanned:
```
SELECT
    this_.id as y0_,
    this_.pfx as y1_,
    this_.base as y2_,
    this_.type as y3_,
    lower(this_.pfx) as pfxLowerCol,
    lower(this_.base) as baseLowerCol
from
    "m6187".AuthorEntry this_
where
    this_.bibliographicDatabaseId = 566757
    and (
        exists (
            select
                daee_.AUTHORENTRY_ID as y0_
            from
                "m6187".DOCUMENT_AUTHOR daee_
                inner join "m6187".Document doc1_ on daee_.DOCUMENT_ID = doc1_.id
            where
                daee_.AUTHORENTRY_ID = this_.id
                and not doc1_.hidden = '2'
        )
        or exists (
            select
                daee_.AUTHORENTRY_ID as y0_
            from
                "m6187".DOCUMENT_AUTHOR daee_
                inner join "m6187".Document doc1_ on daee_.DOCUMENT_ID = doc1_.id
            where
                daee_.AUTHORENTRY_ID = this_.NORMALFORM_ID
                and not doc1_.hidden = '2'
        )
        or exists (
            select
                daee_.AUTHORENTRY_ID as y0_
            from
                "m6187".DOCUMENT_AUTHOR daee_
                inner join "m6187".AuthorEntry ae1_ on daee_.AUTHORENTRY_ID = ae1_.id
                inner join "m6187".Document doc2_ on daee_.DOCUMENT_ID = doc2_.id
            where
                ae1_.NORMALFORM_ID = this_.id
                and not doc2_.hidden = '2'
        )
        or exists (
            select
                daee_.AUTHORENTRY_ID as y0_
            from
                "m6187".DOCUMENT_AUTHOR daee_
                inner join "m6187".AuthorEntry ae1_ on daee_.AUTHORENTRY_ID = ae1_.id
                inner join "m6187".Document doc2_ on daee_.DOCUMENT_ID = doc2_.id
            where
                ae1_.NORMALFORM_ID = this_.NORMALFORM_ID
                and not doc2_.hidden = '2'
        )
    )
order by
    baseLowerCol asc,
    pfxLowerCol asc
limit 25
```