From the objective of OSS-DB Exam Gold
- Performance Tuning - Tuning database and queries
Sample Question
3.08
Overall processing of PostgreSQL has been slowing down periodically. Select two tuning measures that can be effective in this case.
-
Review slow-running SQL queries and optimize the code that is causing the performance issues.
-
Adjust the checkpoint_completion_target parameter to smooth out the load caused by checkpoints.
-
Decrease the autovacuum_vacuum_cost_limit or vacuum_cost_limit value to reduce the load caused by VACUUM processing.
-
Change to a streaming replication configuration to achieve load balancing.
-
This is a characteristic of PostgreSQL, and there are no countermeasure.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2025/06/02
Answer and Explanation
The most likely cause of temporary slowdowns in queries and overall processing is contention with checkpoint or VACUUM operations.
A. SQL tuning alone cannot resolve temporary periods of high load.
B. If there is contention with checkpointing, distributing the checkpoint workload can be effective.
C. If there is contention with VACUUM, adjusting VACUUM-related parameters to distribute the VACUUM workload is an effective mitigation strategy.
D. Switching to a replication configuration will not eliminate checkpoint or VACUUM contention.
E. Temporary periods of high processing load are expected and can be mitigated.
Therefore, the correct answers are B and C.