In Part 1 of our PostgreSQL MVCC Internals series, we explored one of Postgres’s fundamental architectural designs: rows are never updated in place. Instead, every UPDATE creates an entirely new row version (tuple), while hidden system columns like xmin, xmax, and ctid track those versions under the hood.
That explains how multiple versions of a row come to exist, but it raises a much more compelling question: If multiple versions of the same row exist simultaneously, how does PostgreSQL decide exactly which version your specific transaction is allowed to see?
This challenge comes up constantly when designing high-concurrency systems. Imagine an online banking application handling thousands of concurrent money transfers, where hundreds of users read and update the same account balances at the same moment.
In this article, we will use this banking scenario to dive deep into the PostgreSQL transaction manager. You will learn how Postgres assigns transaction IDs, constructs snapshots, applies isolation rules, and guarantees perfectly consistent reads without ever blocking concurrent users.
1. The Transaction Lifecycle
Before looking at IDs and snapshots, we have to look at the entity that creates row versions in the first place: The Transaction.
Every SQL statement you execute in Postgres runs inside a transaction. Whether you explicitly wrap your code in a BEGIN...COMMIT block or execute a standalone UPDATE, Postgres manages it via a strict transaction lifecycle.
While a transaction is running, its inserts, updates, or deletes are strictly isolated. Only after a successful COMMIT do those changes become visible to the rest of the database. If a ROLLBACK occurs, Postgres effectively ignores those modifications, leaving the data exactly as it was.
The Lifecycle in Action
Let’s look at how this isolation works with concurrent users:
- Session A begins a transaction and deducts ₹500 from an account. The transaction remains active (uncommitted).
- Session B concurrently queries that same account balance.
Instead of blocking and waiting for Session A to finish, Session B instantly returns the original balance. Postgres completely hides uncommitted data from other sessions, serving up the older, valid row version instead.
2. A Real-World Case: The Bank Transfer
To see why this rigid lifecycle matters, consider what happens when Alice transfers ₹500 to Bob. The application orchestrates three logical operations:
If the application server or database crashes right after deducting money from Alice, but before adding it to Bob, a system without transaction safeguards would lose Alice’s ₹500 permanently.
Because Postgres enforces strict atomicity, the entire uncommitted sequence is automatically rolled back upon recovery. The database behaves as if the transfer never even started. No customer ever sees a half-completed financial statement.
3. Proving MVCC Isolation (Hands-On Lab)
You can easily observe this version isolation yourself by opening two separate psql terminals connected to the same database.
Step 1: Modify data in Session A
Session A
BEGIN;
UPDATE bank_account
SET balance = balance - 500
WHERE account_no = 1001;---Check our current internal transaction ID
SELECT txid_current(); (Leave this session open and uncommitted.)
Step 2: Read data from Session B
-- Session B
SELECT txid_current(), balance
FROM bank_account
WHERE account_no = 1001;You will observe that Session B’s balance remains unchanged, completely unaffected by Session A’s ongoing write.
Step 3: Commit and Observe
Go back to Session A and type:
COMMIT;Now, re-run the SELECT query in Session B. The updated balance appears instantly.
Here is the youtube video :
4. How Does Postgres Identify Transactions? (XIDs)
This brings us to the next natural question: If thousands of transactions are running simultaneously, how does Postgres uniquely track them? How does it map a specific row version back to the transaction that created it?
To do this, the PostgreSQL engine assigns every mutating transaction (any transaction that performs an INSERT, UPDATE, or DELETE) a monotonically increasing, 32-bit integer called a Transaction ID (XID).
Read-only queries (SELECT) generally do not consume a permanent XID, preserving the 32-bit ID space for transactions that actually alter state.
When a row is created, its xmin hidden column is stamped with the XID of the creating transaction. When it’s deleted or updated, its xmax column is stamped with the modifying transaction’s XID. These stamps become the structural trail Postgres follows to evaluate visibility.
5. Capturing a Moment in Time: Snapshots
Knowing a row’s xmin and xmax is only half the battle. To determine if your query can see those XIDs, Postgres creates a Transaction Snapshot at the start of your statement or transaction (depending on your isolation level).
A snapshot is essentially an internal boundary line that splits the database’s transaction history into three distinct visual categories. It is represented textually in Postgres as XMIN:XMAX:xip_list.
| Snapshot Component | What It Represents |
| XMIN | The earliest transaction ID that is still currently running. Any transaction lower than this is guaranteed to be finished (committed or rolled back). |
| XMAX | The next unassigned XID. Any transaction equal to or higher than this hasn’t even started yet when our snapshot was taken, making its changes invisible to us. |
| xip_list | A list of the specific active XIDs that were running in between this snapshot’s XMIN and XMAX. |
6. The Core Visibility Rules
When your query scans a table, it looks at every tuple’s hidden headers and passes them through a deterministic checklist against your snapshot:
- Rule 1: If a tuple’s xmin belongs to a transaction that has been rolled back, the row is invisible.
- Rule 2: If xmin is greater than or equal to your snapshot’s XMAX, the row was created after your snapshot was captured. It is invisible.
- Rule 3: If xmin is less than your snapshot’s XMIN, the transaction committed before your snapshot was captured. It is visible (unless it was later deleted/updated).
- Rule 4: If xmin sits between XMIN and XMAX, Postgres checks the xip_list. If it’s in the list, it means the transaction was still running when you started; therefore, it is invisible. If it’s not in the list, it was committed before your query, making it visible.
Using these four foundational checks, Postgres filters through millions of concurrent changes instantly, providing a perfectly isolated view of your data without ever locking out readers.
Conclusion:
Checking the exact commit status of an XID dynamically during a massive table scan sounds incredibly expensive. Where does Postgres store this status, and how does it make these rules lightning-fast?
In Part 3, we will explore pg_xact Unmask the magic of Hint Bits, clarify the difference between CLOG and pg_xact, and tackle the ultimate database boogeyman: Transaction ID Wrap-around and Freezing. Stay tuned!