Re: Add the ability to limit the amount of memory that can be allocated to backends.

On Jan 2, 2025, at 9:21 PM, Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>
>> That said, I do think a workload manager would be more effective than
>> trying to limit total connections.
>
> The "workload management" concept is so abstract I find it very
> difficult to discuss without much more detail about how would it
> actually work / be implemented.
>
> I believe implementing some rudimentary "global" memory accounting would
> not be *that* hard (possibly along the lines of the patches early in
> this thread), and adding some sort of dynamic connection limit would not
> be much harder I think. But then comes the hard part of actually doing
> the "workload management" part, which seems pretty comparable to what a
> QoS / scheduler needs to do. With all the weird corner cases.

I’ve been saying “workload management” for lack of a better term, but my initial suggestion upthread was to simply stop allowing new transactions to start if global work_mem consumption exceeded some threshold. That’s simplistic enough that I wouldn’t really consider it “workload management”. Maybe “deferred execution” would be a better name. The only other thing it’d need is a timeout on how long a new transaction could sit in limbo.

I agree that no matter what everything being proposed would rely on having metrics on actual work_mem consumption. That would definitely be a good feature on its own. I’m thinking adding “work_mem_bytes” and “work_mem_operations” to pg_stat_activity (where “work_mem_operations” would tell you how many different things were using work_mem in the backend.

Incidentally, something related to this that I’ve seen is backend memory consumption slowly growing over time. Unbounded growth of relcache and friends was presumably the biggest contributor. There’s an argument to be made for a view dedicated to tracking per-backend memory stats, with additional info about things contributing to idle memory consumption.