Postgresql hard
How does PostgreSQL efficiently determine whether a transaction ID (XID) found in a data page represents a successfully committed change?
- A By performing a full physical scan of the active WAL segments on the disk to find a commit record.
- B By checking the 'Commit Log' (CLOG) bitmap, which stores 2 bits of status for every transaction.
- C By querying a specialized system-catalog table that tracks the lifecycle of every backend process.
- D By verifying the cryptographic signature of the row header against the master cluster key.
Answer: B. By checking the 'Commit Log' (CLOG) bitmap, which stores 2 bits of status for every transaction.
Reading the WAL for every row check would be impossible. CLOG (now `pg_xact`) is a compact bitmap in memory. It tells the engine if an XID is: In-Progress, Committed, Aborted, or Sub-Committed.
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?