Postgresql hard
The `work_mem` parameter is applied 'per operation' within a query. If a complex query involves 4 Hash Joins and 2 Sorts, and `work_mem = 64MB`, what is the risk?
- A The query will be restricted to a single CPU core to prevent memory fragmentation in the heap.
- B The query can allocate up to 384MB of RAM, potentially leading to an OOM (Out of Memory) crash.
- C The database will automatically switch all operations to disk-based temporary files to save memory.
- D The shared buffer pool will be locked for the duration of the query, blocking other client requests.
Answer: B. The query can allocate up to 384MB of RAM, potentially leading to an OOM (Out of Memory) crash.
This is a common configuration mistake. A single session can allocate many multiples of `work_mem`. If 100 users run that same query, the server can run out of memory very quickly even if the total RAM is quite large.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- In a PostgreSQL environment, what functional characteristic distinguishes a `MATERIALIZED VIEW` from a standard `VIEW`?
- Which standard PostgreSQL utility is designed to extract a single database into a script or archive file for backup purposes?
- Why would PostgreSQL reject a `DELETE` operation on a record despite the user having full administrative privileges?
- The 'Heap Only Tuples' (HOT) optimization improves UPDATE performance. Which specific set of conditions must be met for a HOT update to occur?
- Which native command-line utility is used to execute SQL queries and manage PostgreSQL databases from a terminal session?
- What is the specific purpose of 'Freezing' a row (modifying its `xmin` status) during a major VACUUM operation?