PostgreSQL's Multi-Version Concurrency Control

Anyone who has worked in a large multi-user database environment can relate to the frustration that "locks" can cause by making users wait. Whether the database system is using table-level, page-level, column-level, or row-level locking, the same annoying problem persists: Readers (SELECTs) wait for writers (UPDATEs) to finish, and writers (UPDATEs) wait for readers (SELECTs) to finish. Wouldn't it be nice to use a database with a "no-locking" capability?

If you use PostgreSQL, you know that "no-locking" is already a reality. In PostgreSQL, readers never wait for writers, and writers never wait for readers. Before anyone objects to the claim that there is "no-locking" in PostgreSQL, let me explain PostgreSQL's advanced technique called Multi-Version Concurrency Control (MVCC).

While most other database systems use locks to maintain concurrency control and data consistency, PostgreSQL uses a multi-version model. Think of a version as a data snapshot at a distinct point in time. When users query a table, the current version of the data appears. If they run the same query again on the table, a new version appears if any data has changed. Data changes occur in a database through UPDATE, INSERT, or DELETE statements.

A simple example of selecting data from one table shows the difference between traditional row-level locking and PostgreSQL's MVCC.

SELECT headlines FROM news_items 

This statement reads data from a table called news_items and displays all the rows in the column called headlines. For data systems that use row-level locking, the SELECT statement will not succeed and will have to wait if another user is concurrently inserting (INSERT) or updating (UPDATE) data in the table news items. The transaction that modifies the data holds a lock on the row(s) and therefore all rows from the table cannot be displayed. Users who have encountered frequent locks when trying to read data know the frustration this locking scheme can cause, forcing users to wait until the lock releases.

In PostgreSQL, however, users can always view the news_items table. There is no need to wait for a lock to be released, even if multiple users are inserting and updating data in the table. When a user issues the SELECT query, PostgreSQL displays a snapshot, or version, of all the data that was committed before the query began. Any data updates or inserts that are part of open transactions or were committed after the query began will not be displayed. Doesn't that make complete sense?

How MVCC works in PostgreSQL

Let's look deeper into how MVCC works in PostgreSQL to allow "no-locking." Each row in PostgreSQL has two transaction IDs: a creation transaction ID for the transaction that created the row, and an expiration transaction ID for the transaction that expired the row. When an UPDATE is performed, PostgreSQL creates a new row and expires the old row. It's the same row -- just different versions. PostgreSQL creates a new version of the row and also retains the old or expired version. Database systems that use row-level locking do not retain old versions of the data, hence the need for locks to maintain data consistency.

So now that you know how PostgreSQL creates versions of the data, you might be wondering how it knows which version to display.

It's quite simple. At the start of a query, PostgreSQL records two things: 1) the current transaction ID, and 2) all in-process transaction IDs. When a query is issued, PostgreSQL displays all the row versions that match the following criteria:

• The row's creation transaction ID is a committed transaction and is less than the current transaction counter.
• The row lacks an expiration transaction ID or its expiration transaction ID was in process at query start.

The power of MVCC is in keeping track of transaction IDs to determine the version of the data, and thereby avoid having to issue any locks. It's very logical and efficient. New users to PostgreSQL will be pleased with the performance improvements of MVCC over row-level locking, especially those running in a large multi-user environment.

Additionally, MVCC offers another advantage: hot backups. MVCC allows PostgreSQL to make a full database backup while the database is live. For many database systems, users need to shutdown the database or lock all tables to get a consistent snapshot. Not so with PostgreSQL. It simply takes a snapshot of the entire database at a point in time and dumps the output even while data is being inserted, updated or deleted.

In conclusion, MVCC ensures that readers never wait for writers and writers never wait for readers. If you don't believe that PostgreSQL's multi-version model is better than row-level locking, I challenge you to try out PostgreSQL for yourself.

Joseph Mitchell is a knowledge engineer for Great Bridge LLC.

oreillynet.com Copyright © 2003 O'Reilly & Associates, Inc.