PostgreSQL and MVCC

PostgreSQL uses MVCC to provide different views of the database to different sessions, which are based on the isolation level setting. With MVCC, high levels of concurrency are achieved without sacrificing performance. The key rule is that readers should not block writers and writers should not block readers. MVCC is used by many other databases (for example, Oracle and Berkeley DB).

PostgreSQL uses the concept of transaction ID to achieve MVCC. In the previous section, we saw the transaction ID being retrieved using the txid_current() function. Each transaction will see the effect of all transactions that were started and committed before the current transaction started. This is the most basic rule followed to get consistent views of the data in a transaction.

In the following figure, P0 is a point in time. T1, T2, T3 up to T13 are transactions that begin one after the other. P1 is the point in time when transaction T10 started.

All the statements in T10 will see the effect...