Postgresql hard
PostgreSQL sets `full_page_writes` to `on` by default. What specific type of data corruption does this mechanism protect against during a crash?
- A Silent bit rot caused by electromagnetic interference on high-density magnetic platter drives.
- B Torn Pages occurring when the OS crashes while writing an 8KB page in smaller atomic blocks.
- C Transaction ID overlap caused by a massive spike in concurrent write requests to the WAL.
- D Memory leaks in the shared buffer pool that lead to inconsistent snapshots for long-running reads.
Answer: B. Torn Pages occurring when the OS crashes while writing an 8KB page in smaller atomic blocks.
If the OS writes an 8KB page in two 4KB chunks and crashes in the middle, the page is corrupt ('torn'). `full_page_writes` ensures that a clean copy of the page is in the WAL, allowing it to be safely restored during recovery.
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?