MVCC and Secondary Index Consistency

In this lesson, you will learn how secondary indexes maintain data integrity when rows are constantly shifting behind the scenes in an MVCC environment.

The Problem of Indirection

In a standard database, a secondary index (like an index on an email column) must point to the actual row containing the data. In an MVCC system, every update creates a new row version with a different physical location (a new Row ID or Tuple ID). If every update forced us to find and rewrite every secondary index entry to point to the new physical address, performance would collapse.

Physical vs. Logical Pointers

To avoid this "index update storm," many MVCC databases (like PostgreSQL) use a clever design choice:

1. Heap Pointers: The secondary index points to a fixed entry in an "Indirect" or "Version" map rather than the moving heap row itself.
2. Pointer Stability: By pointing to a stable internal identifier, the secondary index does not need to change even if the row version is updated or moved during a VACUUM process.

Consider this sequence:

• Version 1: User "Alice" exists at physical location Page 1: Slot A. The index points here.
• Update: Alice changes her name. The engine creates Version 2 at Page 5: Slot B.
• Consistency: Because of the version-tracking metadata, the system knows that Version 1 is now "dead" and Version 2 is "live." The secondary index continues to point to the address that manages these versions, rather than chasing the raw disk location.

The Visibility Check

Even though the index points to the correct "logical" row, the secondary index itself does not know about the transaction state of the database. When you perform an index lookup, the database performs a two-step verification:

-- Step 1: Find the candidate row via the secondary index
-- Step 2: Check the hidden visibility metadata (XMIN/XMAX)
SELECT * FROM users WHERE email = 'alice@example.com';

The database finds the index entry, follows it to the versioned row, and performs a visibility check based on your snapshot. If the version is not visible to your transaction, the engine traverses the version chain to find the correct version that is visible.

Key Takeaway: Secondary indexes in MVCC point to stable logical identifiers, relying on visibility checks at the row level to ensure that queries return the correct version for your specific transaction snapshot.