Postgresql hard
What is the specific purpose of 'Freezing' a row (modifying its `xmin` status) during a major VACUUM operation?
- A It marks the row as read-only to prevent concurrent transactions from modifying its physical offset.
- B It moves the row from the primary heap file to a compressed archival segment on slower disk storage.
- C It replaces the XID with a special value that makes the row visible to all future transactions.
- D It encrypts the row data using a session-specific key to protect against offline disk-access attacks.
Answer: C. It replaces the XID with a special value that makes the row visible to all future transactions.
Transaction IDs (XIDs) are 32-bit and eventually wrap around. If a row's creating XID is 'too old', it would become invisible. 'Freezing' replaces the XID with `FrozenTransactionId` (2), ensuring it remains visible indefinitely.
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?
- In the context of PostgreSQL storage management, what is the primary objective of the `VACUUM` command?