Scribd
Upload
22 views2 pages

Postgres MVCC

how does MVCC work for postgres

Uploaded by

namedspheres
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
22 views2 pages

Postgres MVCC

how does MVCC work for postgres

Uploaded by

namedspheres
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

PostgreSQL's Multi-Version Concurrency Control (MVCC) mechanism is designed to

allow concurrent access to the database without locking, ensuring data consistency
while minimizing conflicts. Here's a simplified breakdown of how MVCC works in
PostgreSQL:

1. Snapshot Isolation
PostgreSQL uses MVCC to provide snapshot isolation. Each transaction works with its
own snapshot of the database, which means that it only sees the data as it was at
the start of the transaction, even if other transactions are modifying the same
data simultaneously.

2. Tuples and Versions


Each row in PostgreSQL is called a tuple.
Every tuple has associated metadata: xmin and xmax.
xmin: The transaction ID of the transaction that inserted or last updated the
tuple.
xmax: The transaction ID of the transaction that deleted the tuple (if applicable).
If a tuple is still alive, xmax will be set to a special value indicating that it's
not deleted.
When a transaction modifies a row, a new version (tuple) is created rather than
overwriting the old one. This new tuple is assigned a new xmin value, and the old
tuple's xmax is updated to mark it as "expired" for future transactions.

3. Visibility Rules
A transaction can see a row if:

The row's xmin is less than or equal to the current transaction's ID (meaning the
row was committed before the transaction started).
The row's xmax is either not set (row is still active) or set to a value greater
than the current transaction's ID (meaning the row has not been deleted by any
transaction that is active or committed before the current transaction).
This allows different transactions to work with different versions of the same row,
depending on their snapshot.

4. Read and Write Behavior


Reads: A transaction reads the version of a tuple that was committed before the
transaction began, based on its snapshot. This avoids reading uncommitted data from
other concurrent transactions (avoiding "dirty reads").
Writes: When a transaction modifies a row, a new version of the row is created. The
existing version is not deleted immediately but remains visible to transactions
that started before the modification.
5. Vacuuming
Because old versions of rows remain in the database until they are no longer
visible to any active transaction, PostgreSQL performs a process called vacuuming
to clean up old, obsolete tuples. Vacuuming reclaims storage and ensures that the
system doesn't accumulate too many dead tuples.

Example:
Transaction A inserts a row with the value 1 into a table.
Transaction B begins and reads the value from that row. It sees the inserted value
1 since it was committed before Transaction B started.
Transaction A updates the row to 2. A new tuple is created with the value 2, and
its xmin is set to Transaction A's ID.
Transaction B reads the row again. It will still see the old version (with value 1)
because it started before Transaction A's update was committed.
If Transaction A commits, the old version of the row still exists but will be
marked as obsolete once all transactions that started before it have finished.
This process helps PostgreSQL provide concurrent access while maintaining
consistency and isolation, making sure that transactions don't interfere with each
other.

You might also like