Problem - Konfiguration oder Anwendung? (Endlich die Lösung)

Moin,

lange Zeit hatte ich mit dem Problem zu kämpfen Link
Damals wusste ich mir nicht anders zu helfen, als der Maschine glaube ich 2 TB SWAP Platte anzuschließen und so dafür sorgen, dass zumindest im meinem Fall alles ohne Abbrüche durchlief.

Ich war bei der Anwendung natürlich nicht der einzige der mit dem Problem zu kämpfen hatte und der Entwickler es immer wieder bestritten hat und auf Konfigurationsfehler hinweisen wollte wo es keine gab.
Dann fand sich jemand der die Anwendung nutzt und auch etwas von Postgres versteht. Der meinte in einem Post in dem Herstellerforum u.a

I had reason to be looking at SQL activity related to a separate issue but I think I noted what is the key issue with Photo Supreme and PostgreSQL specifically.
Note that you don’t have to do anything intensive to exhaust memory, you just have to do enough in a single session. If you do something so simple as leave a slideshow running, for example, memory usage creeps up and will unavoidably eventually exhaust the server’s memory, how long it takes is just a question of how much memory you have.
The issue is the use of prepared statements without any corresponding deallocation in most cases. Preparing a statement reserves memory related to that prepared statement which is not released until either:

  1. The session ends, or
  2. it is expressly deallocated
    This is clearly and unequivocally stated in PostgreSQL official documentation:
    www postgresql org/docs/current/sql-deallocate.html

… so thanks to the lack of deallocations, the number of operations you can do in any session is hard limited by how much memory PostgreSQL has access to because so much ends up tied up by no-longer-needed prepared statements.
In the very small sample of activity I looked at there were 1,309 prepared statements and only 157 of them (about 12%) deallocated despite only 77 of them (6%) actually using parameters, meaning the other 94% 1. had no potential reusability to go against immediately deallocating them and/or 2. if for some reason deallocation is undesirable, they could have been run as direct queries to avoid the need to deallocate them.

Adding in a deallocation for every prepared statement once it no longer has any potential use should resolve the PostgreSQL memory usage issue.

Wenige Tage nach seinem Post hat der Entwickler doch reagiert und schon läuft alles bestens und ohne unnötige 2TB an SWAP.