Work_mem and effective_cache_size tuning in PostgreSQL

Introduction

These settings help PostgreSQL use memory efficiently to run queries faster without using too much system memory.

When you want to speed up sorting and joining operations in your database queries.

When your database server has enough RAM and you want to improve query performance.

When you notice queries are slow because they use disk for temporary data instead of memory.

When you want to balance memory usage between multiple users running queries at the same time.

Syntax

PostgreSQL

SET work_mem = 'value';
SHOW work_mem;

-- To set effective_cache_size, edit postgresql.conf:
-- effective_cache_size = 'value'
-- Requires server restart
SHOW effective_cache_size;

work_mem is memory used for internal operations like sorting and hashing per query operation.

effective_cache_size estimates how much memory is available for disk caching by the OS and PostgreSQL.

Examples

This sets the memory for sorting and hashing to 4 megabytes for the current session.

PostgreSQL

SET work_mem = '4MB';
SHOW work_mem;

This shows effective_cache_size after setting it to about 2 gigabytes in postgresql.conf (requires server restart) to tell PostgreSQL memory available for caching disk data.

PostgreSQL

-- Edit postgresql.conf:
-- effective_cache_size = 2GB
-- Requires server restart
SHOW effective_cache_size;

Permanent settings applied on server restart to allocate more memory for query operations and caching.

PostgreSQL

-- In postgresql.conf file:
work_mem = 8MB
effective_cache_size = 4GB

Sample Program

This example sets work_mem to 8MB for the current session, then shows both work_mem and effective_cache_size (configured in postgresql.conf).

PostgreSQL

SET work_mem = '8MB';
SHOW work_mem;
SHOW effective_cache_size;

Important Notes

Setting work_mem too high can cause your server to run out of memory if many queries run at once.

effective_cache_size does not allocate memory but helps PostgreSQL plan queries better.

Always test changes in a safe environment before applying to production.

Summary

work_mem controls memory for sorting and joining in queries.

effective_cache_size helps PostgreSQL estimate available cache memory.

Proper tuning improves query speed and server stability.