Postgresql medium
What is the critical operational risk associated with a failure of the 'Autovacuum' process over a long period of time?
- A The database will automatically revert to an older logical backup from the previous week.
- B Transaction ID Wraparound: The 32-bit counter resets, causing old data to appear as new/invisible.
- C The database will swap all data to the local system page file, causing a 100x slowdown in writes.
- D The physical database files will be fragmented across more than 4,000 distinct filesystem nodes.
Answer: B. Transaction ID Wraparound: The 32-bit counter resets, causing old data to appear as new/invisible.
Transaction IDs are only 32-bit. If the counter hits 4 billion without a vacuum to 'freeze' old rows, new transactions might think old rows haven't happened yet. To prevent data corruption, Postgres will eventually stop accepting new writes entirely.
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?