PostgreSQL Concurrency with MVCC
Last updated December 28, 2022
Table of Contents
One of the big selling points of Postgres is how it handles concurrency. The promise is simple: reads never block writes and vice versa. Postgres achieves this via a mechanism called Multi Version Concurrency Control. This technique isn’t unique to Postgres: there are several databases that implement some form of MVCC including Oracle, Berkeley DB, CouchDB and many more. Understanding how MVCC is implemented in Postgres is important when designing highly concurrent apps on PostgreSQL. It’s actually an elegant and simple solution to a hard problem.
How MVCC works
Every transaction in Postgres gets a transaction ID called XID. This includes single one statement transactions such as an insert, update, or delete, as well as explicitly wrapping a group of statements together via
COMMIT. When a transaction starts, Postgres increments an XID and assigns it to the current transaction. Postgres also stores transaction information on every row in the system, which is used to determine whether a row is visible to the transaction or not.
For example, when you insert a row, Postgres stores the XID in the row and calls it
xmin. Every row that has been committed and has an
xmin that is less than the current transaction’s XID is visible to the transaction. This means that you can start a transaction and insert a row, and until that transaction
COMMITs that row isn’t visible to other transactions. After it commits and other transactions get created, they’re able to view the new row because they satisfy the
xmin < XID condition – and the transaction that created the row has completed.
A similar mechanism occurs for
UPDATEs, only in these cases Postgres stores an
xmax value on each row in order to determine visibility. This diagram shows two concurrent transactions inserting and reading rows, and how MVCC comes into play in terms of transaction isolation.
For the following charts, assume the following DDL:
CREATE TABLE numbers (value int);
xmax values are hidden from daily operations, you can just ask for them and Postgres gives them to you:
SELECT *, xmin, xmax FROM numbers;
You can also get the XID for the current transaction easily:
I know what you’re thinking though: what about a two transactions updating the same row at the same time? This is where transaction isolation levels come in. Postgres basically supports two models that allow you to control how this situation is handled. The default,
READ COMMITTED, reads the row after the initial transaction has completed and then executes the statement. It basically starts over if the row changed while it was waiting. For instance, if you issue an
UPDATE with a
WHERE clause, the
WHERE clause will rerun after the initial transaction commits, and the
UPDATE takes place if the
WHERE clause is still satisfied. Here’s an example of two transactions modifying a row where the initial
UPDATE causes the
WHERE clause of the second transaction to return no rows. Therefore the second transaction doesn’t update any rows at all:
If you need finer control over this behavior, you can set the transaction isolation level to
SERIALIZABLE. With this strategy the above scenario fails because it says “If the row I’m modifying has been modified by another transaction, don’t even try,” and Postgres responds with the error message
ERROR: could not serialize access due to concurrent update. It’s up to your app to handle that error and try again, or to give up if that’s what makes sense.
Disadvantages of MVCC
Now that you know how MVCC and transaction isolation actually works, you’ve added another tool for solving the kinds of problems where a
SERIALIZABLE isolation level comes in handy. While the advantages of MVCC are clear it also has some disadvantages.
Because different transactions have visibility to a different set of rows, Postgres must maintain potentially obsolete records. This is why an
UPDATE actually creates a new row and why
DELETE doesn’t really remove the row: it merely marks it as deleted and sets the XID values appropriately. As transactions complete, there will be rows in the database that can’t possibly be visible to any future transactions. These are called dead rows. Another problem that comes from MVCC is that transaction IDs can only ever grow so much – they’re 32 bits and can “only” support around 4 billion transactions. When the XID reaches its max, it wraps around and starts back at zero. Suddenly all rows appear to be in future transactions, and no new transactions would have visibility into those rows.
Both dead rows and the transaction XID wraparound problem are solved with
VACUUM. This is routine maintenance, but thankfully Postgres comes with an auto_vacuum daemon that runs at a configurable frequency. It’s important to keep an eye on this because different deployments have different needs when it comes to vacuum frequency. You can read more about what
VACUUM actually does on the Postgres docs and how Heroku handles it.