How are undo blocks implemented in PostgreSQL internally?

In PostgreSQL, undo blocks are implemented using a mechanism called Multi-Version Concurrency Control (MVCC), which allows multiple versions of a row to exist in the database at the same time. When a transaction updates or deletes a row, a new version of the row is created, and the old version is marked as “dead” and kept around for other transactions to access.

Undo blocks are created when a transaction updates or deletes a row, and they contain the old version of the row that was changed. The undo block is stored in a separate area of the database called the “undo log”, which is used to store all of the old versions of rows that have been changed by transactions.

When a transaction needs to access an old version of a row, it can do so by reading the corresponding undo block from the undo log. The undo block contains all of the data that was changed in the original row, as well as a pointer to the new version of the row that was created.

The undo log is periodically cleaned up by a process called the “vacuum” process, which removes any undo blocks that are no longer needed. This helps to keep the size of the undo log under control and prevent it from growing too large.

The MVCC mechanism and the undo log provide a powerful and flexible way to manage concurrency in PostgreSQL, allowing multiple transactions to access and modify the same data without interfering with each other.

How is PostgreSQL MVCC implemented internally? 

PostgreSQL’s MVCC (Multi-Version Concurrency Control) mechanism is a way to manage concurrent access to the database by allowing multiple versions of a row to exist at the same time. This means that transactions can read and modify data without locking the entire table or blocking other transactions.

Here’s an example to explain how MVCC works in PostgreSQL:

Suppose we have a table employees with the following data:

idnamesalary
1Alice5000
2Bob6000
3Charlie7000

Transaction A wants to update Alice’s salary to 5500, and Transaction B wants to update Bob’s salary to 6500. Both transactions read the data from the table, and each one creates a new version of the row that it wants to update.

Transaction A creates a new version of the row for Alice with a salary of 5500, and marks the old version as “dead”. Transaction B creates a new version of the row for Bob with a salary of 6500, and marks the old version as “dead”.

Now, suppose Transaction A wants to read the data for Bob. Since there are two versions of the row for Bob (the old version with a salary of 6000, and the new version with a salary of 6500), Transaction A sees the old version with a salary of 6000.

Similarly, if Transaction B wants to read the data for Alice, it sees the old version with a salary of 5000.

This is how MVCC allows concurrent access to the data without blocking or locking. Each transaction works with its own version of the data, and can read or modify the data without interfering with other transactions.

Now, regarding how PostgreSQL MVCC avoids deadlocks, it uses a “wait-for” graph to detect and resolve deadlocks. When a transaction is blocked by another transaction, it adds an edge to the graph that represents the dependency between the two transactions. If a cycle is detected in the graph, it means that there is a deadlock.

To resolve the deadlock, PostgreSQL chooses one of the transactions as the “victim”, and rolls it back. This allows the other transactions to continue without getting stuck in the deadlock.

Overall, PostgreSQL’s MVCC mechanism and deadlock resolution algorithm provide a robust and efficient way to manage concurrent access to the database, and ensure that transactions can run without interfering with each other.

About Shiv Iyer 437 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.