Prepare and test your skills

Prepare and test your skills

Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
A data engineering team maintains a Cloud SQL for PostgreSQL instance that serves data models for executive reporting dashboards. During morning peak hours, numerous concurrent dashboard sessions execute complex queries involving large ORDER BY, GROUP BY, and multi-table join operations.
Database logs reveal recurring Out of Memory: Killed process (postgres) errors and connection terminations (terminating connection due to administrator command). Monitoring shows high active session counts and severe RAM saturation.
Which resource configuration adjustment should the data engineer implement to balance query performance and prevent out-of-memory errors?
Modifying the compute size (vCPU and RAM) of a Cloud SQL instance adjusts the underlying hardware resources allocated to the database container.
work_mem configuration or uncontrolled memory allocation during concurrent queries.The instance was permanently over-provisioned or under-provisioned over an extended 30-day baseline period and required a permanent tier change during an authorized maintenance window.
In PostgreSQL databases, work_mem defines the amount of base memory allocated for internal sort operations (such as ORDER BY and DISTINCT) and hash tables (such as hash joins and hash-based aggregations) before writing temporary data to disk buffers. Unlike global shared memory structures, work_mem is allocated per operation, per backend process, meaning a single complex analytical query containing multiple joins or sorts can allocate multiple multiples of work_mem concurrently.
work_mem rapidly multiplies memory consumption (connections × operations × work_mem), triggering OS-level OOM killer events. Lowering the global default establishes a safe baseline for standard queries.SET work_mem = '...'; locally within their specific session or transaction, isolating high-memory allocations strictly to authorized batch jobs.shared_buffers frees additional unreserved RAM that can be utilized dynamically by backend processes and connection overhead.Setting a conservative baseline with session-level overrides represents standard database optimization. It protects database availability under high connection counts while giving resource-intensive reporting pipelines the flexibility to utilize larger memory allocations safely.
Increasing the global work_mem configuration parameter forces PostgreSQL to reserve larger chunks of RAM for every sorting and hashing operation across every active backend connection on the instance.
work_mem when the database is already experiencing high connection counts directly accelerates memory exhaustion. Because work_mem is not a shared pool but is allocated per sort/hash node within every running query plan, multiplying this value across peak concurrent dashboard users will cause immediate and frequent OOM process terminations.work_mem value simultaneously.The database ran only single-threaded, isolated batch transformations with strictly limited connections and dedicated, predictable memory ceilings.
shared_buffers sets the amount of dedicated shared memory PostgreSQL uses to cache data blocks read from disk. The huge_pages kernel feature reduces Translation Lookaside Buffer (TLB) overhead by allowing the operating system to manage memory allocations in chunks larger than standard 4KB pages.
shared_buffers to consume almost all available RAM leaves insufficient free memory for dynamic per-query operations like work_mem, connection process overhead, and operating system page caches. This severely exacerbates out-of-memory failures during query execution.huge_pages degrades performance for large memory allocations because it significantly increases memory management overhead and page table traversal latency.shared_buffers only caches shared data pages; it cannot be used by queries to perform sorting, grouping, or hash table joins.huge_pages is enabled by default to optimize virtual memory architecture and stability.The primary performance bottleneck was a low cache hit ratio on disk reads on an instance with low concurrency and no dynamic sorting memory requirements.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated