Understanding MVCC and Tuple Visibility

In this lesson, you will learn how PostgreSQL avoids "read-write contention" by allowing multiple versions of a single row to coexist simultaneously.

The Core Concept: MVCC

In many database systems, if one transaction is updating a row, other transactions must wait until that update is finished. PostgreSQL uses Multi-Version Concurrency Control (MVCC) to avoid this. Instead of overwriting data, PostgreSQL creates a new "version" (or tuple) of the row whenever an UPDATE occurs.

Because multiple versions of the same row can exist in the table at once, the database needs a set of rules to decide which version a specific transaction is allowed to see. This is called Tuple Visibility.

How PostgreSQL Tracks Visibility

Every row in a PostgreSQL table contains hidden metadata fields, specifically xmin and xmax.

• xmin: Stores the ID of the transaction that inserted (created) this version of the row.
• xmax: Stores the ID of the transaction that deleted or updated this version. (If it's 0, the row is still active).

When you run a SELECT query, the database looks at your transaction's ID and compares it against these hidden fields:

-- Imagine this scenario:
-- Transaction 100 inserts a row (xmin = 100, xmax = 0)
-- Transaction 101 updates that row:
--   - The old row is marked (xmin = 100, xmax = 101)
--   - A new row is inserted (xmin = 101, xmax = 0)

The Visibility Rules

When your transaction performs a read, it follows these basic logic gates:

1. Ignore rows where the xmin is in the future (uncommitted transactions).
2. Ignore rows where the xmax is in the past (already deleted).
3. Accept rows where the xmin was committed before your transaction started and the xmax is either empty or represents a transaction that hadn't committed yet when you started.

This architecture ensures that readers never block writers, and writers never block readers, providing a high-concurrency environment where every transaction sees a consistent "snapshot" of the data as it existed at the start of their operation.

Key Takeaway: MVCC enables high performance by creating new row versions rather than locking data, using xmin and xmax metadata to determine which version of a record is visible to a specific transaction.