Work_mem and effective_cache_size tuning in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding work_mem impact on query execution

Which of the following best describes the effect of increasing work_mem in PostgreSQL?

A It increases the memory available for each sort or hash operation, potentially reducing disk usage during query execution.

B It increases the total memory available for all queries combined, preventing out-of-memory errors.

C It controls the size of the shared buffer cache used for caching disk pages.

D It limits the maximum size of the query result set returned to the client.

💡 Hint

Think about what happens when PostgreSQL sorts or hashes data during a query.

work_mem sets the amount of memory used for internal sort operations and hash tables before writing to disk. Increasing it can reduce disk I/O for large operations.

🧠 Conceptual

intermediate

Role of effective_cache_size in query planning

What does the effective_cache_size setting in PostgreSQL represent?

A The maximum size of the shared buffer pool inside PostgreSQL.

B An estimate of the OS disk cache available for PostgreSQL to use, influencing planner cost estimates.

C The amount of memory PostgreSQL uses for sorting operations.

D The total memory allocated to all active connections.

💡 Hint

Consider how PostgreSQL guesses if data is likely cached in memory.

effective_cache_size tells the planner how much memory is available for caching data, helping it decide whether to use index scans or sequential scans.

❓ query_result

advanced

Effect of work_mem on sort operation

Given the following PostgreSQL configuration and query, what will be the output of EXPLAIN ANALYZE regarding sort method?

SET work_mem = '64kB';
EXPLAIN ANALYZE SELECT * FROM large_table ORDER BY column1;

PostgreSQL

SET work_mem = '64kB';
EXPLAIN ANALYZE SELECT * FROM large_table ORDER BY column1;

A Sort method: external merge Disk, indicating disk usage due to insufficient work_mem.

B Sort method: quicksort in-memory, indicating sort fits in work_mem.

C Sort method: no sort needed, data already ordered.

D Sort method: hash aggregate, unrelated to sorting.

💡 Hint

Think about what happens when work_mem is too small for sorting large data.

With a small work_mem, PostgreSQL cannot sort all data in memory and uses disk-based external merge sort, which is slower.

📝 Syntax

advanced

Correctly setting effective_cache_size

Which of the following commands correctly sets effective_cache_size to 4GB in PostgreSQL?

A SET effective_cache_size = '4000MB';

B SET effective_cache_size = 4GB;

C ALTER DATABASE SET effective_cache_size = 4GB;

D ALTER SYSTEM SET effective_cache_size = '4GB';

💡 Hint

Remember the syntax for persistent configuration changes in PostgreSQL.

ALTER SYSTEM SET is the correct way to persistently set configuration parameters like effective_cache_size. Values must be quoted with units.

❓ optimization

expert

Optimizing work_mem for concurrent queries

You have a PostgreSQL server with 32GB RAM and expect up to 20 concurrent queries, each performing multiple sorts. Which work_mem setting is most appropriate to avoid memory exhaustion while allowing efficient sorting?

A Set work_mem to 500MB to allow multiple sorts per query without exceeding RAM.

B Set work_mem to 1GB to maximize sort performance per query.

C Set work_mem to 100MB to balance memory usage and concurrency.

D Set work_mem to 2GB since total memory is large enough for all queries.

💡 Hint

Calculate memory usage as work_mem * sorts per query * concurrent queries.

Setting work_mem too high risks exhausting RAM when many queries run concurrently. 100MB balances memory use and performance for 20 concurrent queries with multiple sorts.

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