Work_mem and effective_cache_size tuning in PostgreSQL - Step-by-Step Execution

Concept Flow - Work_mem and effective_cache_size tuning

Start Query Execution

↓

Check work_mem setting

↓

Sort/Hash operations use work_mem

↓

If work_mem too low -> Disk spill

↓

Check effective_cache_size

↓

Planner estimates cached data

↓

Choose query plan based on cache estimate

↓

Execute query with chosen plan

↓

End Query Execution

The database uses work_mem for sorting and hashing during query execution. It uses effective_cache_size to estimate how much data is cached in memory to choose the best query plan.

Execution Sample

PostgreSQL

SET work_mem = '4MB';
SET effective_cache_size = '128MB';
EXPLAIN SELECT * FROM sales ORDER BY date;

This sets work_mem and effective_cache_size, then explains the query plan for sorting sales by date.

Execution Table

Step	Action	Setting/Value	Effect on Query	Notes
1	Set work_mem	4MB	Limits memory for sort/hash	Small memory may cause disk spill
2	Set effective_cache_size	128MB	Planner estimates cache size	Helps planner choose index scan or seq scan
3	Planner analyzes query	Uses effective_cache_size	Chooses plan based on cache estimate	If cache large, prefers index scan
4	Execute sort operation	Uses work_mem	Sort fits in memory	Fast in-memory sort
5	Query completes	-	-	Result returned quickly
6	Increase work_mem to 64MB	64MB	More memory for sort	Less chance of disk spill
7	Re-execute query	-	Faster sort, less disk I/O	Improved performance
8	Increase effective_cache_size to 1GB	1GB	Planner assumes more cache	May choose index scan more often
9	Re-execute query	-	Potentially better plan	Depends on actual cache availability
10	End	-	-	Tuning complete

💡 Execution ends after query runs with tuned settings

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 6	After Step 8	Final
work_mem	default (e.g. 4MB)	4MB	4MB	64MB	64MB	64MB
effective_cache_size	default (e.g. 128MB)	128MB	128MB	128MB	1GB	1GB
query_plan	unknown	plan with 4MB work_mem	plan with 4MB work_mem and 128MB cache	plan with 64MB work_mem	plan with 64MB work_mem and 1GB cache	final chosen plan

Key Moments - 3 Insights

Why does increasing work_mem reduce disk usage during sorting?

Because work_mem sets the memory available for sorting. If it's too small (see step 1 and 4), sorting spills to disk which is slower. Increasing it (step 6) allows sorting to happen fully in memory.

How does effective_cache_size influence the query planner's decisions?

Effective_cache_size tells the planner how much data it expects to be cached in RAM. A larger value (step 8) makes the planner more likely to choose index scans assuming data is cached, improving performance.

Can setting work_mem too high cause problems?

Yes. If many queries run simultaneously, each using large work_mem, total memory usage can exceed system RAM causing swapping and slowdowns. So balance is needed.

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what happens when work_mem is set to 4MB?

A Sort operation spills to disk causing slow down

B Sort operation fits in memory and is fast

C Query planner ignores work_mem

D Effective_cache_size is increased automatically

💡 Hint

Check the 'Effect on Query' and 'Notes' columns at step 4 in execution_table

At which step does the planner start assuming a larger cache size?

A Step 8

B Step 6

C Step 2

D Step 10

💡 Hint

Look at the 'Setting/Value' and 'Effect on Query' columns in execution_table rows

If work_mem is set too high for many concurrent queries, what is a likely outcome?

A Queries run faster without issues

B Effective_cache_size automatically adjusts

C System may run out of memory and slow down

D Disk usage increases for sorting

💡 Hint

Refer to key_moments about work_mem and memory usage

Concept Snapshot

work_mem sets memory for sorting and hashing operations during query execution.
effective_cache_size tells the planner how much memory is available for caching data.
Too low work_mem causes disk spills; too high risks memory exhaustion.
Effective_cache_size influences planner's choice of index vs sequential scans.
Tune both for balanced performance based on workload and system RAM.

Full Transcript

This visual execution shows how PostgreSQL uses work_mem and effective_cache_size settings during query execution. First, work_mem limits memory for sorting and hashing. If too small, sorting spills to disk, slowing queries. Increasing work_mem allows faster in-memory operations but must be balanced to avoid memory exhaustion when many queries run concurrently. Effective_cache_size is a planner hint about how much data is cached in RAM. A larger value encourages the planner to choose index scans assuming data is cached, improving performance. The execution table traces setting these values, planner decisions, and query execution steps. Key moments clarify why tuning these parameters matters and the risks of improper settings. The quiz tests understanding of how these settings affect query plans and performance.

Practice

1. What does the work_mem setting control in PostgreSQL?

easy

A. The amount of memory used for sorting and joining operations during query execution

B. The total memory available for caching disk pages

C. The maximum size of a database connection pool

D. The memory allocated for background worker processes

Solution

1. Step 1: Understand the role of work_mem

   work_mem is the memory PostgreSQL uses for internal operations like sorting and joining during query execution.

2. Step 2: Differentiate from other memory settings

   Other settings like effective_cache_size relate to cache estimation, not sorting or joining memory.

3. Final Answer:

   The amount of memory used for sorting and joining operations during query execution -> Option A

4. Quick Check:

   work_mem = sorting/join memory [OK]

Hint: Remember: work_mem is per operation memory for sorting/joining [OK]

Common Mistakes:

• Confusing work_mem with effective_cache_size
• Thinking work_mem controls total server memory
• Assuming work_mem affects connection limits

2. Which of the following is the correct way to set effective_cache_size to 4GB in PostgreSQL's configuration file?

easy

A. effective_cache_size = 4000MB

B. effective_cache_size = '4GB'

C. effective_cache_size = 4g

D. effective_cache_size = 4GB

Solution

1. Step 1: Check PostgreSQL config syntax for memory sizes

   PostgreSQL accepts memory sizes with units like KB, MB, GB without quotes.

2. Step 2: Validate each option

   effective_cache_size = 4GB uses correct syntax: number + unit without quotes. effective_cache_size = '4GB' uses quotes (invalid). effective_cache_size = 4000MB uses MB but 4000MB is less than 4GB. effective_cache_size = 4g uses lowercase 'g' which is invalid; units must be uppercase.

3. Final Answer:

   effective_cache_size = 4GB -> Option D

4. Quick Check:

   Config memory size = number + uppercase unit [OK]

Hint: Use number + uppercase unit without quotes for memory sizes [OK]

Common Mistakes:

• Adding quotes around memory size values
• Using lowercase units like 'g' instead of 'GB'
• Confusing MB and GB values

3. Given work_mem = '2MB' and a query performing 3 sorts simultaneously, what is the total memory PostgreSQL may use for sorting?

medium

A. 6MB

B. 3MB

C. 2MB

D. 1MB

Solution

1. Step 1: Understand work_mem usage per operation

   Each sort operation can use up to work_mem memory independently.

2. Step 2: Calculate total memory for 3 sorts

   3 sorts x 2MB each = 6MB total memory used for sorting.

3. Final Answer:

   6MB -> Option A

4. Quick Check:

   work_mem x number of sorts = total memory [OK]

Hint: Multiply work_mem by number of simultaneous operations [OK]

Common Mistakes:

• Assuming work_mem is total for all operations
• Adding instead of multiplying memory sizes
• Ignoring simultaneous operation count

4. A PostgreSQL server has effective_cache_size set too low. What problem might this cause?

medium

A. PostgreSQL will allocate too much memory for sorting operations

B. The server will crash due to memory exhaustion

C. PostgreSQL may underestimate available cache and choose inefficient query plans

D. Connections will be refused due to low cache size

Solution

1. Step 1: Understand effective_cache_size role

   This setting helps PostgreSQL estimate how much OS cache is available for data pages.

2. Step 2: Consequence of low effective_cache_size

   If set too low, PostgreSQL thinks less cache is available, so it may avoid index scans or other efficient plans, choosing slower ones.

3. Final Answer:

   PostgreSQL may underestimate available cache and choose inefficient query plans -> Option C

4. Quick Check:

   Low effective_cache_size = conservative query plans [OK]

Hint: Low effective_cache_size causes conservative, slower plans [OK]

Common Mistakes:

• Confusing effective_cache_size with work_mem
• Assuming server crashes from low effective_cache_size
• Thinking it limits connection count

5. You have a PostgreSQL server with 32GB RAM. You want to optimize work_mem and effective_cache_size for a workload with many concurrent queries doing large sorts. Which is the best tuning approach?

hard

A. Set work_mem very high (e.g., 1GB) and effective_cache_size low (e.g., 4GB)

B. Set work_mem moderately (e.g., 16MB) and effective_cache_size high (e.g., 24GB)

C. Set both work_mem and effective_cache_size very low to save memory

D. Set work_mem low (e.g., 1MB) and effective_cache_size moderate (e.g., 8GB)

Solution

1. Step 1: Balance work_mem for concurrent large sorts

   Setting work_mem too high risks memory exhaustion with many queries; moderate value like 16MB balances performance and memory use.

2. Step 2: Set effective_cache_size to reflect OS cache

   With 32GB RAM, setting effective_cache_size high (e.g., 24GB) helps PostgreSQL plan queries assuming ample cache.

3. Step 3: Evaluate other options

   Set work_mem very high (e.g., 1GB) and effective_cache_size low (e.g., 4GB) risks memory overuse; Set both work_mem and effective_cache_size very low to save memory wastes performance; Set work_mem low (e.g., 1MB) and effective_cache_size moderate (e.g., 8GB) underestimates cache and limits sort memory.

4. Final Answer:

   Set work_mem moderately (e.g., 16MB) and effective_cache_size high (e.g., 24GB) -> Option B

5. Quick Check:

   Moderate work_mem + high effective_cache_size = balanced tuning [OK]

Hint: Balance work_mem and effective_cache_size for memory and cache [OK]

Common Mistakes:

• Setting work_mem too high causing memory exhaustion
• Setting effective_cache_size too low causing bad plans
• Ignoring concurrent query memory needs