Re: Memory issues with PostgreSQL 15

> On 25 Jul 2024, at 12:58, Christian Schröder <christian(dot)schroeder(at)wsd(dot)com> wrote:
>
> Hi all,
> I started this discussion in May and was then dragged into other topics, so I could never follow up. Sorry for that!
> Since then, the problem has resurfaced from time to time. Right now, we seem to have issues again, which gives me the opportunity to follow up on your various suggestions.
>
> The current error messages are similar to what we have seen before:
>
> <2024-07-25 12:27:38 CEST - > LOG: could not fork autovacuum worker process: Cannot allocate memory
> <2024-07-25 12:27:38 CEST - mailprocessor> ERROR: could not resize shared memory segment "/PostgreSQL.1226901392" to 189280 bytes: No space left on device

We sometimes encounter a similar issue, but with disk space - on a 1TB virtual disk of which usually only about 1/4th is in use.
Our hypothesis is that sometimes some long-running transactions need to process a lot of data and put so much of it in temporary tables that they fill up the remaining space. We’ve seen the disk space climb and hit the ’No space left on device’ mark - at which point the transactions get aborted and rolled back, putting us back at the 1/4th of space in use situation.

Have you been able to catch your shared memory shortage in the act? I suspect that the stats you showed in your message were those after rollback.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.