What happens when maxing out Postgres' work_mem?

Question

How does the work_mem option in Postgres work? Here's the description from http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html:

Specifies the amount of memory to be used by internal
sort operations and hash tables before switching to
temporary disk files. The value defaults to one megabyte
(1MB). Note that for a complex query, several sort or
hash operations might be running in parallel; each one
will be allowed to use as much memory as this value
specifies before it starts to put data into temporary 
files. Also, several running sessions could be doing
such operations concurrently. So the total memory used
could be many times the value of work_mem; it is
necessary to keep this fact in mind when choosing the
value. Sort operations are used for ORDER BY, DISTINCT,
and merge joins. Hash tables are used in hash joins,
hash-based aggregation, and hash-based processing of IN
subqueries. 

I'm probably totally wrong here but..isn't "switching to temporary disk files" essentially the same thing as "virtual memory" in the operating system? Wouldn't the OS just create a swap file once the RAM is gone? Wouldn't it be better to set this to something like 100TB and let the OS figure it out? Before I potentially mess up my system, I want to check if anyone actually tried this approach.

Answer 1

The OS is generic in the terms it handles swap, besides, there's a finite amount of address space a process can use, which isn't that big on 32 bit systems(2Gb on a windows 32 bit platform, can be enhanced to 3Gb), but you're right, you could let the OS handle this through virtual memory.

PostgreSQL is not 'generic' it'll know much better than the OS how to structure data once disk access is involved, so letting the database switch over to explicit file handling once memory is exhausted will have benefits over letting the OS handle it.

Answer 2

PostgreSQL will for example switch to a sorting operation more suitable for on-disk sort than in-memory sort if it knows the sort will happen on disk - which it won't know if it happens in swap.

Also, PostgreSQL can switch to a completely different plan (for example, using a different JOIN method) if it figures out the data does not fit in RAM.

Setting work_mem too high will get you a very slow database as soon as you have enough data so that everything doesn't always fit in RAM anymore.

Answer 3

A database server that swaps is a dead database server.

In RAM postgres uses quicksort, on disk it uses another algorithm which is much more suited to harddisks. Using quicksort on swapped-out memory will be incredibly slow.

Answer 4

Keep in mind that work_mem is the maximum amount of RAM that can be used for every single sort operation. For a single query, multiple sort operations might run in parallel and there might be multiple connections querying the database at once. For that reason all sort operations may use x-times the amount of work_mem in RAM (that's the reason a conservative amount is recommended).

Now back to your question, if you choose a work_mem to a such high value, sort operations might use up most of your RAM, which leads to page in and out's from swap (keep in mind, there are lots of other processes and PostgreSQL parts that need some (or even lots of) RAM. Disk-based sort operations are by factors more efficient than page swaps done by the OS. As some of the other replies pointed out, a database server which has swap out and in constantly will perform extremely slow.

Another point is, that with such a high work_mem value, a single query (purposely or by accident) might more or less make the whole database server go unresponsive.