professional-cloud-data-engineer
Prepare and test your skills
Prepare and test your skills
Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.
Keep the momentum going with these hand-picked practice scenarios
Want more questions like this?
Get a free certification question every week.
Last updated
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?
Set a conservative global work_mem value at the instance level and allocate higher work_mem settings at the session level for heavy reporting workloads, while adjusting shared_buffers if additional workspace is required.
Scale down the instance compute size to clear cached buffer memory, then scale back up immediately during dashboard peak hours.
Increase shared_buffers to allocate all available instance RAM and disable the default huge_pages configuration.
Increase the global work_mem flag to maximum capacity across the entire instance to ensure all sort operations use in-memory quick sort instead of disk merge sort.
Set a conservative global work_mem value at the instance level and allocate higher work_mem settings at the session level for heavy reporting workloads, while adjusting shared_buffers if additional workspace is required.
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.
StudyCerts
StudyCertswork_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.
Scale down the instance compute size to clear cached buffer memory, then scale back up immediately during dashboard peak hours.
Increase shared_buffers to allocate all available instance RAM and disable the default huge_pages configuration.
Increase the global work_mem flag to maximum capacity across the entire instance to ensure all sort operations use in-memory quick sort instead of disk merge sort.