Work_mem is a symptom of punting. It gives the DBA an imprecise tool, and then implicitly offloads responsibility to not allocate too many steps in their query plans using global knowledge of physical execution steps which are dependent on statistics, available concurrency and so on.
The database ought to be monitoring global resource use for the query and partitioning it into stages that free up memory or spill to disk as necessary.
This all fundamentally goes back to the Volcano iterator pull design. Switching to pulling batches instead of row should already improve performance; and it would leave open the option of using a supervisor to schedule execution (query stages using fibers or similar continuation compatible control flow instead of recursive calls), with potential restarts / dynamic replanning when things like global memory limits are approached. Using batches leaves more performance margin for heavier calling mechanisms, and also opens the door for more vectorized strategies for operators.
In TFA it's set to a measly 2MiB, yet tried to allocate 2TiB. Note that the PG default is double that, at 4MiB.
What the setting does is offload the responsibility of a "working" implementation onto you (or the DBA). If it were just using the 4MiB default as a hardcoded value, one could argue it's a bug and bikeshed forever on what a "good" value is. As there is no safe or good value, the approach would need to be reevaluated.
The core issue is that there is no overall memory management strategy in Postgres, just the implementation.
Which is fine for an initial version, just add a few settings for all the constants in the code and boom you have some knobs to turn. Unfortunately you can't set them correctly, it might still try to use an unbounded amount of memory.
While the documentation is very transparent about this, just from reading it you know they know it's a bad design or at least an unsolved design issue. It just describes the implementation accurately, yet offers nothing further in terms of actual useful guidance on what the value should be.
This is not a criticism of the docs btw, I love the technically accurate docs in Postgres. But it's not the only setting in Postgres which is basically just an exposed internal knob. Which I totally get as a software engineer.
However from a product point of view, internal knobs are rarely all that useful. At this point of maturity, Postgres should probably aim to do a bit better on this front.
Why not? That'd be useful. Feels like a software written in C should make that reasonably easy.
or more probably the c api setrlimit(2)
RLIMIT_RSS
This is a limit (in bytes) on the process's resident set (the number of virtual pages resident in RAM). This limit has effect only in Linux 2.4.x, x < 3 and there affects only calls to madvise(2) specifying MADV_WILLNEED.