Sorting in memory with work_mem
There are two important parameters used by PostgreSQL to allocate and use memory at session level. We can get them by executing the following command in the PostgreSQL data directory as follows:
grep work postgresql.conf #work_mem = 1MB # min 64kB #maintenance_work_mem = 16MB # min 1MB
The first one is work_mem
. The default value is set to 1 MB and minimum 64 KB. This is the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files. The last part is crucial (if enough memory is not allocated, it will result in physical I/O), which will certainly result in a spike in response time. So it sounds like setting this pretty high will be good. However, the issue is that this is not allocated at the server level, as is done for shared_buffers
. This is allocated for each user. Even in a query, if there are multiple sort operations in parallel, each one might run up to the maximum...