Table of Contents

1. The Core Concept (Why MVCC?)

MVCC stands for Multi-Version Concurrency Control.

Its primary goal is simple: Readers should never block Writers, and Writers should never block Readers.

Instead of locking a row when someone is updating it (which makes everyone else wait), PostgreSQL keeps multiple versions of the same row active at the same time. Different transactions see different versions depending on when they started.

2. Hidden Columns (xmin & xmax)

Every table you create in PostgreSQL has hidden system columns. The two most important ones for MVCC are:

• xmin: The Transaction ID that created/inserted this row version.
• xmax: The Transaction ID that deleted or updated this row version. (If it is 0, the row is still actively alive).

-- To view these columns by explicitly querying them:
SELECT xmin, xmax, * FROM table_name;

3. Create a Test Table

-- Create a simple products table and  insert our first record and see how Postgres assigns the transaction ID.

[postgres@pgdb01 ~]$ psql -c "CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,price INT);"
CREATE TABLE
[postgres@pgdb01 ~]$

-- Insert a row
[postgres@pgdb01 ~]$ psql -c "INSERT INTO products (name, price) VALUES ('Laptop', 1000);"
INSERT 0 1
[postgres@pgdb01 ~]$ 

-- Query the row with xmin and xmax
[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2156 |    0 |  1 | Laptop |  1000
(1 row)

[postgres@pgdb01 ~]$

• xmin = 2156: xmin represents the transaction ID that created the row version. xmin = 2156
• xmax = 0: xmax represents the transaction ID that deleted or updated. xmax = 0 means No one has deleted/updated it yet . It is actively visible to everyone.

4. UPDATE Creates a New Version

Now, let’s see MVCC in action. Open two separate database sessions/terminal windows.

Terminal 1 (The Writer): Start a transaction and update the price, but do not commit yet.

postgres=# BEGIN;
BEGIN
postgres=*# SELECT txid_current();
 txid_current
--------------
         2157
(1 row)

postgres=*# UPDATE products SET price = 1200 WHERE id = 1;
UPDATE 1
postgres=*#

Terminal 2 (The Reader): Check what Terminal 2 sees:

[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2156 | 2157 |  1 | Laptop |  1000
(1 row)

[postgres@pgdb01 ~]$

• The reader still sees the original price of 1000. They are not blocked!
• The row now has an xmax of 2157. This tells Postgres: “Transaction 2157 is currently changing or deleting this row. If you aren’t Transaction 2157, keep looking at the old data.”
• Under the hood, Postgres has already written a new row version with a price of 1200 and xmin = 2157, but it is completely invisible to Terminal 2 because transaction 2157 hasn’t committed yet.

5. After COMMIT

Let’s finalize the transaction and see the transition.

Terminal 1 (The Writer): Commit the change.

postgres=# BEGIN;
BEGIN
postgres=*# SELECT txid_current();
 txid_current
--------------
         2157
(1 row)

postgres=*# UPDATE products SET price = 1200 WHERE id = 1;
UPDATE 1
postgres=*# COMMIT;   <------------------ (only run COMMIT)
COMMIT
postgres=#

Terminal 2 (The Reader): Query the table again. What Terminal 2 sees now

[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2157 |    0 |  1 | Laptop |  1200
(1 row)

[postgres@pgdb01 ~]$

• The old row version (price 1000) is now marked as dead(invisible).
• The new row version (price 1200) is now alive(current row) with xmin = 2157 and xmax = 0.

6. DELETE Doesn’t Actually Delete

In Postgres, a DELETE statement does not physically erase data from the hard drive immediately. It just changes a flag.

postgres=# BEGIN;
BEGIN
postgres=*# DELETE FROM products WHERE id = 1;
DELETE 1
postgres=*# SELECT txid_current();
 txid_current
--------------
         2158
(1 row)

postgres=*# COMMIT;
COMMIT
postgres=#

Under the hood, Postgres simply updated the existing row’s xmax value to 2158. Because transaction 2158 is committed, Postgres now knows that this row is dead and shouldn’t be shown to any new queries. The actual space on the disk won’t be freed until we vacuum.

7. Dead tuples and VACUUM

Because UPDATE creates new rows and DELETE just marks them as dead, database will accumulate “dead tuples” (bloat) over time.
In simple way, Since UPDATE and DELETE don’t remove old rows, dead rows pile up. This is called bloat.

Postgres uses a process called VACUUM to find these dead rows, clear them out, and mark that space as reusable for future inserts.

-- 1. Check how many dead rows are lurking a table

[postgres@pgdb01 ~]$ psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'products';"
 relname  | n_live_tup | n_dead_tup
----------+------------+------------
 products |          0 |          2   <--------------------
(1 row)

[postgres@pgdb01 ~]$


-- 2. Run a manual vacuum to clean up

[postgres@pgdb01 ~]$ psql -c "VACUUM VERBOSE products;"
INFO:  vacuuming "postgres.public.products"
INFO:  table "products": truncated 1 to 0 pages
INFO:  finished vacuuming "postgres.public.products": index scans: 1
pages: 1 removed, 0 remain, 1 scanned (100.00% of total)
tuples: 2 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2159, which was 1 XIDs old when operation ended
new relfrozenxid: 2159, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 1 pages from table (100.00% of total) had 1 dead item identifiers removed
index "products_pkey": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 11.905 MB/s
buffer usage: 61 hits, 0 misses, 8 dirtied
WAL usage: 11 records, 8 full page images, 47529 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_65663"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_65663": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2160, which was 0 XIDs old when operation ended
new relfrozenxid: 2160, which is 5 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 22.978 MB/s, avg write rate: 22.978 MB/s
buffer usage: 27 hits, 1 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 8097 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
[postgres@pgdb01 ~]$


-- 3. Check again; dead rows should now be 0

[postgres@pgdb01 ~]$ psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'products';"
 relname  | n_live_tup | n_dead_tup
----------+------------+------------
 products |          0 |          0  
(1 row)

[postgres@pgdb01 ~]$

Note: PostgreSQL runs an Autovacuum background process automatically, so you rarely need to run manual vacuums in production.

8. Quick Summary Cheatsheet

To keep MVCC simple, remember these four rules: