MVCC Internals — Complete Guide
MVCC Internals — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of PostgreSQL Tutorial on Toolliyo Academy.
On this page
PostgreSQL Tutorial · Lesson 31 of 100
MVCC Internals
SQL → Advanced
SQL · 1 — Queries · ~6 min · PostgreSQL — Transactions & MVCC
What is this?
Multi-Version Concurrency Control keeps old row versions when you UPDATE — each row has xmin/xmax transaction ids. Readers do not block writers because they see a snapshot.
Why should you care?
Understanding MVCC explains why PostgresVerse long SELECT during bulk UPDATE does not freeze the app — and why bloat appears until vacuum.
See it live — copy this example
Run in pgAdmin or psql.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SELECT xmin, xmax, ctid, balance FROM accounts WHERE account_id = 1;
ROLLBACK;What happened?
- UPDATE creates new row version; old version marked dead for old snapshots.
- xmin/xmax expose transaction ids.
- ctid is physical location — changes on update.
Practice next
- SELECT xmin, xmax from accounts before update.
- UPDATE one row inside BEGIN without COMMIT.
- Open second psql session — see old balance until first commits.
- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ and repeat two-session test.
- Query pg_stat_activity for xact_start age during open transaction.
Remember
Snapshots isolate readers from in-flight writers. Dead versions linger until vacuum. xmin/xmax encode row visibility rules.
PostgresVerse support mystery
User sees old balance until refresh — open backend held snapshot during failed transfer retry.
Outcome: Team adds idle_in_transaction_session_timeout.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
