PostgreSQL for Data Architects

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 ...