Posgresql Optimization technique: work_mem

#postgres #apacheage

What is `work_men`?

This parameter determines how much memory is allocated to in-memory sort operation. If high values is set, then the query will execute very fast compared with
using disk-based sort. By default it is set to be 1 MB. It is a parameter of per-sort rather than per-client.

In case a query containing more than one sort operations, the actual memory required will be work_mem times the number of sort operations. It is difficult to predict how many sort operations are needed per client.

One way to calculate suitable value is to see how much free RAM is around after shared_buffers is allocated, divide by max_connections, and then take a part of that figure and then divided by two. Sort operations include ORDER BY, DISTINCT, GROUP BY, UNION AND merge joins.

Assuming you have a postgresql Database and you want to perform an in-memory sort operation, you can set work-men to 2 MB.

SET work_mem = '2MB';

This allocates more memory for the sort operation, making the query execute faster.

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →