Postgresql hard
PostgreSQL manages its Shared Buffer pool using a 'Clock Sweep' algorithm. How does this provide resistance against cache-thrashing from one-time sequential scans?
- A It prioritizes index pages by assigning them a permanent 'pinned' status in the L2 cache.
- B It tracks a 'Usage Count' per page; pages with higher counts are protected from immediate eviction.
- C It randomly selects a subset of the least-recently-used pages to be compressed into a swap file.
- D It forces all sequential scans to bypass the shared buffers and read directly from the OS page cache.
Answer: B. It tracks a 'Usage Count' per page; pages with higher counts are protected from immediate eviction.
Standard LRU is easily wiped out by a single large scan. Clock Sweep uses a 'Usage Count' (up to 5). Every time a page is accessed, its count goes up. The sweep hand decrements counts, only evicting buffers with a count of 0.
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?