What is MVCC in PostgreSQL? How does it create row versions?

MVCC (Multi-Version Concurrency Control) in PostgreSQL is a mechanism for managing concurrent access to data without read locks.

How MVCC works:

• Each row in a table stores two system labels: xmin (the ID of the transaction that created or modified the row) and xmax (the ID of the transaction that deleted the row).
• When a row is updated, a new version of the row is actually created with a new xmin, and the old version gets an xmax indicating it is outdated.
• During reading, a transaction only sees the versions of rows that were "active" at the start of its operation, ignoring rows with xmin or xmax outside its visibility.

Thus, PostgreSQL stores multiple versions of the same row, ensuring transaction isolation and allowing data reading without locks.

Example:

• Transaction T1 reads a row with xmin=100, xmax=0.
• Transaction T2 updates this row, creating a new version with xmin=101, and sets xmax=101 on the old version.
• T1 continues to see the old version, while T2 and subsequent transactions see the new one.

This allows efficient implementation of isolation levels and maintaining high performance during parallel operations.