Work_mem and effective_cache_size tuning in PostgreSQL - Deep Dive

Overview - Work_mem and effective_cache_size tuning

What is it?

Work_mem and effective_cache_size are two important settings in PostgreSQL that control how much memory the database uses for query operations and caching. Work_mem sets the amount of memory used for internal operations like sorting and hashing during query execution. Effective_cache_size estimates how much memory is available for caching data, helping the query planner decide the best way to run queries. Together, they help PostgreSQL run queries efficiently by balancing memory use and speed.

Why it matters

Without tuning work_mem and effective_cache_size, PostgreSQL might use too little memory, causing slow queries due to excessive disk access, or too much memory, leading to system crashes or swapping. Proper tuning improves query speed, reduces server load, and makes the database more responsive, which is crucial for applications that rely on fast data access.

Where it fits

Before tuning these settings, you should understand basic PostgreSQL configuration and how queries work. After learning this, you can explore advanced performance tuning topics like autovacuum tuning, indexing strategies, and parallel query execution.

Mental Model

Core Idea

Work_mem controls memory for individual query operations, while effective_cache_size guides the planner on available cache memory to optimize query plans.

Think of it like...

Imagine a kitchen where work_mem is the size of your cutting board for preparing each dish, and effective_cache_size is how much pantry space you have to keep ingredients handy. A bigger cutting board lets you prepare ingredients faster, and knowing your pantry size helps you plan meals efficiently.

┌───────────────────────────────┐
│        PostgreSQL Memory       │
├───────────────┬───────────────┤
│  work_mem     │ effective_cache_size │
│ (per operation│ (planner estimate of │
│  memory for   │  OS cache memory)    │
│  sorting, etc)│                      │
└───────────────┴───────────────┘

Query Execution:
[Query] → uses work_mem for sorting/hashing
Planner → uses effective_cache_size to choose plan

Build-Up - 7 Steps

1

Foundation

Understanding work_mem basics

Concept: Introduce work_mem as memory for query operations like sorting and hashing.

Work_mem is a setting that defines how much memory PostgreSQL can use for internal operations during a single query step. For example, when sorting data or creating hash tables, PostgreSQL uses work_mem to hold data in memory instead of writing to disk. If work_mem is too small, PostgreSQL will use temporary files on disk, which slows down queries.

Result

Queries that require sorting or hashing run faster when work_mem is set appropriately, reducing disk I/O.

Understanding that work_mem is per operation helps realize that complex queries with many operations can use a lot of memory if work_mem is set too high.

2

Foundation

Grasping effective_cache_size role

Concept: Explain effective_cache_size as an estimate of OS-level cache available for PostgreSQL.

Effective_cache_size tells PostgreSQL's query planner how much memory is available for caching data pages in the operating system's file system cache. It does not allocate memory but helps the planner guess if data will be in memory or need disk access. A higher effective_cache_size encourages the planner to use index scans and join methods that assume data is cached.

Result

The planner makes better decisions about query plans, improving performance by choosing faster methods when enough cache is available.

Knowing effective_cache_size is an estimate, not a memory limit, prevents confusion about its role in memory allocation.

3

Intermediate

Balancing work_mem for query complexity

🤔Before reading on: do you think setting work_mem very high always improves query speed? Commit to your answer.

Concept: Discuss how work_mem is used per operation and the risk of setting it too high.

Each query can have multiple operations that use work_mem separately. For example, a query with several sorts or joins can multiply the memory usage by the number of operations. Setting work_mem too high risks exhausting server memory if many queries run simultaneously. The goal is to find a balance where queries run efficiently without causing memory pressure.

Result

Properly balanced work_mem improves query speed without risking server stability.

Understanding that work_mem multiplies per operation and per concurrent query is key to avoiding out-of-memory errors.

4

Intermediate

Using effective_cache_size to guide planner

🤔Before reading on: does effective_cache_size allocate memory or just inform the planner? Commit to your answer.

Concept: Clarify that effective_cache_size is a planner hint, not a memory allocation.

Effective_cache_size helps the planner estimate how much data is likely cached by the OS. If set too low, the planner may avoid index scans and choose slower sequential scans. If set too high, it may overestimate cache, leading to plans that perform poorly if data is not cached. Setting it to about half to three-quarters of total RAM is common.

Result

The planner chooses query plans that better match actual cache availability, improving performance.

Knowing effective_cache_size is a hint prevents misconfigurations that cause inefficient query plans.

5

Advanced

Tuning work_mem for concurrent workloads

🤔Before reading on: do you think work_mem should be the same for all workloads? Commit to your answer.

Concept: Explain how workload concurrency affects work_mem tuning.

In systems with many simultaneous queries, total memory used by work_mem can be very large if set too high. For example, 100 concurrent queries each using 10MB work_mem can consume 1GB of RAM just for query operations. Monitoring typical concurrency and adjusting work_mem accordingly prevents memory exhaustion. Sometimes, setting work_mem lower and optimizing queries is better than increasing it blindly.

Result

Memory usage stays within safe limits while maintaining query performance under load.

Recognizing the impact of concurrency on memory usage helps avoid server crashes and slowdowns.

6

Advanced

Estimating effective_cache_size correctly

Concept: Teach how to estimate effective_cache_size based on system memory and OS cache behavior.

Effective_cache_size should reflect the amount of memory the OS dedicates to caching PostgreSQL data files. On dedicated database servers, this might be most of the RAM minus what the OS and other processes need. On shared servers, it should be lower. Tools like 'free' or 'vmstat' on Linux help estimate cache size. Setting this value too high or too low misguides the planner.

Result

Better query plans that match real cache availability, improving overall performance.

Knowing how to estimate effective_cache_size based on actual OS cache usage leads to more accurate planner decisions.

7

Expert

Surprising effects of work_mem and cache interplay

🤔Before reading on: do you think increasing work_mem always reduces disk I/O? Commit to your answer.

Concept: Reveal how work_mem and effective_cache_size interact in unexpected ways affecting query plans and disk usage.

Increasing work_mem reduces disk usage for sorting and hashing but can lead the planner to choose plans that assume more data fits in memory. If effective_cache_size is set too low, the planner may avoid index scans even if work_mem is large, causing more disk reads. Conversely, a high effective_cache_size with low work_mem can cause inefficient plans that spill to disk. Balancing both is crucial. Also, some operations like hash joins use work_mem differently, affecting memory pressure.

Result

Understanding this interplay helps avoid tuning traps that degrade performance despite increasing memory settings.

Knowing the subtle interaction between these settings prevents common tuning mistakes and leads to optimal query performance.

Under the Hood

Work_mem allocates memory for each sort or hash operation during query execution. If the data fits in this memory, operations happen in RAM; otherwise, PostgreSQL writes temporary files to disk. Effective_cache_size is a planner parameter that estimates how much data the OS cache can hold, influencing the planner's choice between sequential scans, index scans, and join methods. The planner uses this estimate to predict query costs and select the fastest plan.

Why designed this way?

PostgreSQL separates these settings to give fine control over memory use and planning. Work_mem controls actual memory allocation per operation to prevent overuse, while effective_cache_size is a heuristic to guide planning without reserving memory. This design balances flexibility, safety, and performance, allowing administrators to tune based on workload and hardware.

┌───────────────────────────────┐
│        Query Execution         │
├───────────────┬───────────────┤
│   work_mem    │  effective_cache_size  │
│ (memory used  │ (planner's estimate of │
│  per operation│  OS cache memory)      │
│  for sorting, │                       │
│  hashing)     │                       │
└───────┬───────┴───────────────┬───────┘
        │                       │
        ▼                       ▼
 ┌─────────────┐         ┌─────────────┐
 │ In-memory   │         │ Planner uses │
 │ operations  │         │ estimate to  │
 │ or temp     │         │ choose query │
 │ files on    │         │ plan         │
 │ disk       │         └─────────────┘
 └─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does increasing work_mem always improve query speed? Commit to yes or no.

Common Belief: Increasing work_mem always makes queries faster because more memory means less disk usage.

Reality: Increasing work_mem too much can cause excessive total memory use when many queries run concurrently, leading to swapping or crashes.

Why it matters: Ignoring concurrency effects can cause server instability and worse performance despite higher work_mem.

Quick: Does effective_cache_size allocate memory for caching? Commit to yes or no.

Common Belief: Effective_cache_size reserves memory for caching data in PostgreSQL.

Reality: Effective_cache_size is only a planner hint; it does not allocate or reserve memory.

Why it matters: Misunderstanding this leads to setting it incorrectly, causing poor query plans and performance.

Quick: If effective_cache_size is set too high, will queries always be faster? Commit to yes or no.

Common Belief: Setting effective_cache_size very high always improves query plans and speeds up queries.

Reality: An overestimated effective_cache_size can cause the planner to choose plans that assume data is cached when it is not, leading to slow queries.

Why it matters: Overestimating cache size can degrade performance by causing inefficient query plans.

Quick: Does work_mem apply to the entire query or per operation? Commit to one.

Common Belief: Work_mem is a total memory limit for the whole query.

Reality: Work_mem applies per operation (sort, hash) within a query, so total memory used can be much higher.

Why it matters: Underestimating total memory use can cause unexpected memory exhaustion.

Expert Zone

1

Work_mem is allocated per operation and per parallel worker, so parallel queries multiply memory usage unexpectedly.

2

Effective_cache_size should consider not only PostgreSQL but also other processes and OS cache behavior, which can vary with workload.

3

Some query plans use work_mem differently, for example, hash joins vs. sorts, affecting how memory pressure manifests.

When NOT to use

Avoid setting very high work_mem on systems with many concurrent connections; instead, optimize queries or use connection pooling. Effective_cache_size is not useful on systems with unpredictable OS cache behavior, such as virtualized environments with limited control; in such cases, rely more on monitoring and adaptive query tuning.

Production Patterns

In production, DBAs monitor query plans and memory usage, adjusting work_mem per workload type (e.g., higher for reporting queries, lower for OLTP). Effective_cache_size is set based on server RAM and OS cache behavior, often around 50-75% of total RAM. Tools like pg_stat_statements help identify queries benefiting from work_mem tuning.

Connections

Operating System File Cache

effective_cache_size models OS file cache behavior

Understanding OS caching helps grasp why effective_cache_size is a planner hint, not a memory allocation.

Query Planner Cost Estimation

effective_cache_size influences cost estimates in the planner

Knowing how the planner estimates costs clarifies why tuning effective_cache_size changes query plans.

Resource Management in Operating Systems

work_mem tuning relates to managing limited memory resources among processes

Understanding OS resource management helps appreciate why per-operation memory limits prevent system overload.

Common Pitfalls

#1 Setting work_mem too high without considering concurrency

Wrong approach:

SET work_mem = '500MB'; -- for all sessions without concurrency check

Correct approach:

SET work_mem = '50MB'; -- balanced for expected concurrency

Root cause:

Misunderstanding that work_mem is per operation and per query, leading to excessive total memory use.

#2 Setting effective_cache_size too low causing poor plans

Wrong approach:

SET effective_cache_size = '128MB'; -- too low for server with 16GB RAM

Correct approach:

SET effective_cache_size = '12GB'; -- realistic estimate of OS cache

Root cause:

Not realizing effective_cache_size guides planner decisions, so too low values cause inefficient query plans.

#3 Confusing effective_cache_size with actual memory allocation

Wrong approach:

Expecting effective_cache_size to reserve memory and adjusting other settings accordingly

Correct approach:

Treat effective_cache_size as a planner hint only, not a memory reservation

Root cause:

Misunderstanding the role of effective_cache_size in PostgreSQL's memory management.

Key Takeaways

Work_mem controls memory used for individual query operations like sorting and hashing, and is allocated per operation and per query.

Effective_cache_size is a planner hint estimating how much OS cache is available, guiding query plan choices but not allocating memory.

Setting work_mem too high without considering concurrency risks exhausting server memory and causing crashes.

Setting effective_cache_size too low or too high misguides the planner, leading to inefficient query plans and slower queries.

Balancing work_mem and effective_cache_size based on workload and system memory is key to optimizing PostgreSQL performance.