Postgresql hard
What is the technical justification for setting `checkpoint_completion_target` to a high value (like `0.9`) in a write-heavy PostgreSQL production cluster?
- A To ensure the internal WAL buffers are cleared as quickly as possible to prevent session timeouts.
- B To spread the disk I/O load over a longer period, preventing massive 'thundering herd' write spikes.
- C To allow the background vacuum workers more time to identify dead tuples before the logs are rotated.
- D To minimize the total number of WAL segments kept on disk, reducing the overall storage footprint.
Answer: B. To spread the disk I/O load over a longer period, preventing massive 'thundering herd' write spikes.
A low value forces the DB to flush all dirty pages to disk immediately. This creates an I/O bottleneck that can stall other queries. Spreading the flush over 90% of the checkpoint interval keeps the server responsive.
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?