PostgreSQL Work

In PostgreSQL, the work_mem parameter controls the amount of memory that is allocated to each query to sort and hash the operations.

This parameter determines the maximum memory that a single operation (sorting and hashing) can use before using the temporary disk files to store the intermediate results. By configuring this parameter, we can optimize a PostgreSQL database’s performance for any queries involving a large data set or that requires significant sorting or hashing operations.

In this tutorial, we will try and understand the role of the work_mem parameter, its effects on the database engine and operations, and more.

What Is the Role of the Work_Mem Parameter?

Let us start at the basics and explore the role of the work_mem parameter in the PostgreSQL database engine.

The work_mem parameter allows us to configure the memory for internal PostgreSQL operations. These operations include sorting, hashing, and creating temporary tables during query execution.

By allocating an appropriate amount of memory to “work_mem”, you can avoid the disk I/O operations which can significantly impact the query performance.

Get the Current Work_Mem Value

To determine the current value of the work_mem parameter in your PostgreSQL database, run the following query:

SELECT name, setting FROM pg_settings WHERE name = 'work_mem';

This should return the name and the value as follows:

name | setting
----------+---------
work_mem | 4096
(1 row)

This query returns the current value of the work_mem parameter in bytes. By default, PostgreSQL sets the value of the work_mem parameter to 4 megabytes (4MB) or 4096 bytes.

PostgreSQL Alter Work_Mem Parameter

Sometimes, you may need to modify the work_mem parameter in your PostgreSQL. In such a case, you have a few available options.

Method 1: Temporary Modification

The first method is to modify the work_mem parameter in your current session. Then, you can use the SET command followed by the target parameter and the value that you wish to use.

SET work_mem = '16MB';

The given command changes the value of work_mem only for the current session. It’s useful to test the different values without making permanent changes.

Method 2: Modifying the Server Configuration

We can also set the work_mem parameter by editing the server configuration. Locate and edit the PostgreSQL server configuration with a text editor.

Locate the line that sets the work_mem parameter and modify the value.

For example:

work_mem = 16M

Save the changes and restart the PostgreSQL server for the changes to take effect.

Method 3: Dynamic Update

We can also modify the value dynamically using the PostgreSQL command-line interface. The command is as follows:

ALTER SYSTEM SET work_mem = '16MB';

The given command updates the value dynamically and persists the change across database restarts. This command does require the superuser permissions to execute on the target server.

Choosing the Optimum Work_Mem Parameter Value

The optimal value for work_mem depends on the specific workload and available system resources. It’s essential to strike a balance between allocating enough memory for efficient sorting and hashing operations without overcommitting the memory and causing a system-wide performance degradation.

Conclusion

Setting work_mem correctly will help with performance, and we hope this article helps you better understand how to go about planning for the correct setting.

PostgreSQL Work_Mem