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.