Mastering MVCC in Postgres: The Ultimate Challenge Quiz Quiz

1. Conceptual Foundations of MVCC

   Which of the following best describes how Multi-Version Concurrency Control (MVCC) in Postgres allows reading and writing transactions to occur simultaneously without blocking each other?

   1. By maintaining multiple physical copies of the entire database for different transactions
   2. By locking every row for each reader so writers have to wait
   3. By storing multiple versions of each row and managing visibility based on transaction snapshots
   4. By implementing a centralized lock manager for all data modifications
   5. By using shadow paging for every transactional update

2. Visibility Rules and Snapshots

   When a SELECT query is executed in a Read Committed isolation level, how does Postgres determine which version of a row is visible to that query?

   1. It displays only the most recent version based on the database system time
   2. It shows the version committed just after the query began
   3. It shows the latest committed row visible to the transaction at the start of each statement, based on committed transactions up to that point
   4. It always exposes all uncommitted changes within the system to the reader
   5. It chooses a row version at random if multiple are available

3. Tuple Lifecycle

   Given a tuple (row) in Postgres, which system columns are used by MVCC to determine the validity of the row for a particular transaction’s snapshot?

   1. oid and ctid
   2. xmin and xmax
   3. tid and xid
   4. created_at and updated_at
   5. xmin and zmax

4. Handling Updates and Deletes

   What happens internally in Postgres when a row is updated as part of an UPDATE statement under MVCC?

   1. The row is locked in place and its values are modified in-place
   2. A new row version is inserted, and the old version is marked as deleted
   3. All other transactions are blocked until the update is completed and committed
   4. The database uses a pointer to redirect old queries to the new values
   5. The original row is overwritten and its xmin is reset

5. Transaction IDs (TXIDs) and Wraparound

   Why does Postgres perform an automatic 'freeze' of old tuples during VACUUM, and what could happen if this is not done?

   1. To reclaim disk space for new tables; otherwise, the database cannot expand
   2. To prevent transaction ID wraparound that could cause data loss or data corruption
   3. To remove old indexes automatically; unfreezed indexes will become inaccessible
   4. To refresh in-memory caches; otherwise, performance would drastically drop
   5. To update the statistics collector; stale stats could mislead the optimizer

6. Phantom Reads in MVCC

   Which isolation level in Postgres, in conjunction with MVCC, prevents phantom reads during concurrent transactions?

   1. Read Uncommitted
   2. Read Committed
   3. Repeatable Read
   4. Serializable
   5. Read Steady

7. HOT (Heap-Only Tuples) and MVCC

   In which scenario will Postgres take advantage of Heap-Only Tuple (HOT) updates, and how does it benefit MVCC performance?

   1. When updating a primary key; it always avoids index lookups
   2. When updating columns that are indexed; it eliminates index bloat
   3. When a tuple is updated and none of the indexed columns change, allowing in-place updates and reducing unnecessary index entries
   4. When deleting rows with large TOASTed values; it speeds up vacuum
   5. When updating foreign key references; it bypasses visibility checks

8. MVCC and Locking Granularity

   Which statement accurately reflects the locking mechanisms used by Postgres under MVCC during a typical SELECT query?

   1. Postgres always takes a global table lock for SELECT operations
   2. SELECT queries obtain a row-level exclusive lock preventing concurrent writes
   3. SELECT queries do not acquire any row-level locks, instead relying on MVCC snapshots for consistency
   4. SELECT queries take a shared lock on every row they scan
   5. SELECT operations escalate to a page lock if many rows are read

9. VACUUM and Dead Row Cleanup

   Under MVCC, why is a VACUUM operation sometimes necessary even if there are no active transactions?

   1. To reset user access controls
   2. To forcibly rewrite all tuples for freshness
   3. To reclaim storage by removing dead tuples that are no longer visible to any transaction
   4. To randomly shuffle the order of rows in the table for load balancing
   5. To force auto-analyze of index statistics

10. Serializable Snapshot Isolation (SSI)

    What is the primary difference between 'Serializable' isolation in Postgres and 'Repeatable Read', specifically related to MVCC behaviors in concurrent workloads?

    1. Serializable schedule always allows write skew, but Repeatable Read does not
    2. Repeatable Read uses a global lock, while Serializable does not
    3. Serializable performs additional checks to detect and abort transactions with serialization anomalies that Repeatable Read would allow
    4. Repeatable Read allows only dirty reads, whereas Serializable blocks all reads
    5. Serializable disables the creation of new tuple versions altogether