Gabriele Svelto (gabriele.svelto.delete@this.gmail.com) on January 24, 2020 8:23 am wrote:
> A quick search for MySQL, MariaDB and PostgreSQL show that they all have tunables that the *user* has
> to set in order to prevent them from explicitly using too much mem. Can you point to an example of code
> or a paper describing this preallocation strategy? Oracle uses compressed storage in a lot of places
> nowadays so I doubt it's even possible to figure out how much memory a query will need beforehand.

So you started by looking first by the crapware I mentioned earlier (MySQL/MariaDB)... I won't even comment on this fake DBMS.

About others, it is true they all have several tunable options which the user MAY (not HAS) set to tune for their specific case, in the case of PostgreSQL, let them talk about themselves:
In Linux 2.4 and later, the default virtual memory behavior is not optimal for PostgreSQL. Because of the way that the kernel implements memory overcommit, the kernel might terminate the PostgreSQL postmaster (the master server process) if the memory demands of either PostgreSQL or another process cause the system to run out of virtual memory.
https://www.postgresql.org/docs/current/kernel-resources.html

Oracle recommends disabling memory overcommit as well, Microsoft set a configurable limit on SQL on Linux that default to 80%, on Windows the user may specify a limit, the default is to use as much as needed without causing a crash.

Also, it is common for databases to use compressed storage, not just Oracle, and what? Reading a header with the uncompressed size allow it to be predictable.

But there are queries where the memory usage is really unpredictable, yet, databases do their best to ESTIMATE memory usage, if the actual memory usage is higher than the estimate the engine may offload the work to disk, and of course, without a crash, after all, the dataset of a database is expected to not fit in memory.

BTW, they estimate by storing statistics about the data, for Oracle:
https://docs.oracle.com/database/121/CCAPP/GUID-E4F013A8-E83C-44AF-B9A4-CA1FBF717730.htm#CCAPP9634

> Visual Studio did the exact opposite the first time I tried to compile LLVM. It started using a ton
> of memory, then swapping heavily, and after minutes of pain died because of memory exhaustion.

"Visual Studio Code (or, at least one plugin I use for a specific language)"

> That's possible in a simple, self-contained application and absolutely
> impossible to apply to any kind of even moderately large software.

Every real DBMS does that.