This article is half-done without your Comment! *** Please share your thoughts via Comment ***
If you don’t know about the MVCC, please visit the below article.
Because without knowing the MVCC, you should not start the learning of PostgreSQL.
Many times, I shared information on MVCC like ONLY INSERT concept where every update is a new version of data.
In today’s post, I am sharing a proof of MVCC.
Not in detail, but with the small and easy demonstration.
The XMIN is a pseudo column of PostgreSQL which used to track concurrent row access.
Using XMIN, you can get an exact number which internally uses by MVCC operations.
Please check the below demonstration:
Create a table with a sample record:
|
1 2 3 |
CREATE TABLE tbl_MVCCDemo (ID INT, CODE CHARACTER VARYING); INSERT INTO tbl_MVCCDemo VALUES (1,'XVK98'); |
Check the value of XMIN pseudo column:
|
1 2 3 4 5 6 |
SELECT XMIN,ID,CODE FROM tbl_MVCCDemo; xmin | id | code ---------------------- 622 | 1 | XVK98 |
Now, update a record using XMIN:
|
1 2 |
UPDATE tbl_MVCCDemo SET code = 'T2738' WHERE XMIN = 622; |
Check the value of XMIN pseudo column:
We updated the value base on XMIN, then also the value of XMIN changed from 622 TO 623.
So this is proof that MVCC architecture inserts the new row for each update.
|
1 2 3 4 5 6 |
SELECT XMIN,ID,CODE FROM tbl_MVCCDemo; xmin | id | code ---------------------- 623 | 1 | XVK98 |
