What is work_mem in PostgreSQL: Explanation and Usage

work_mem in PostgreSQL is a configuration setting that controls the amount of memory used for internal sort operations and hash tables before writing to disk. It helps speed up queries by allowing more data to be processed in memory, reducing slow disk access.

How It Works

Imagine you are sorting a big pile of papers on your desk. If your desk is big enough, you can sort all papers at once quickly. But if your desk is small, you have to sort some papers, put them aside, and then sort the rest, which takes more time.

In PostgreSQL, work_mem is like the size of your desk. It sets how much memory the database can use to sort data or create hash tables during query execution. If the data fits within this memory, the operation is fast. If not, PostgreSQL writes temporary files to disk, which slows down the process.

Each query operation that needs sorting or hashing can use up to the work_mem amount. So, setting it too low causes slow disk writes, and setting it too high can use too much memory if many queries run at once.

Example

This example shows how to check and set work_mem in PostgreSQL, then run a query that benefits from it.

sql

SHOW work_mem;

SET work_mem = '64MB';

EXPLAIN ANALYZE SELECT * FROM large_table ORDER BY some_column;

Output

 work_mem 
----------
 4MB
(1 row)

QUERY PLAN
--------------------------------------------------------------------------
 Sort  (cost=12345..12500 rows=1000 width=50) (actual time=10.123..10.456 rows=1000 loops=1)
   Sort Key: some_column
   Sort Method: quicksort  Memory: 6000kB
   ->  Seq Scan on large_table  (cost=0.00..10000.00 rows=1000 width=50) (actual time=0.123..5.456 rows=1000 loops=1)
 Planning Time: 0.123 ms
 Execution Time: 10.789 ms
(7 rows)

When to Use

Adjust work_mem when you have queries that do a lot of sorting, hashing, or joining large datasets. Increasing it can make these queries faster by avoiding slow disk writes.

For example, if you run reports that sort millions of rows or use hash joins, raising work_mem helps. But be careful: setting it too high on a busy server can cause memory exhaustion because each query can use this much memory per operation.

Start with a moderate increase and monitor your server's memory usage and query performance to find the best balance.

Key Points

• work_mem controls memory for sorting and hashing in queries.
• Higher values speed up complex queries by reducing disk usage.
• Each query operation can use up to work_mem, so set carefully.
• Monitor memory usage to avoid server overload.
• Adjust work_mem based on workload and query needs.

Key Takeaways

work_mem sets memory for sorting and hashing during query execution.

Increasing work_mem can speed up queries that sort or join large data.

Each query operation uses up to work_mem, so avoid setting it too high on busy servers.

Monitor performance and memory to find the best work_mem value for your workload.

Use SET work_mem = 'value' to adjust memory for specific sessions or queries.