Conference Schedule - PGConf.EU 2019
Instantaneous transaction rollback, and other advantages of versioned storage
Time: 11:50 - 12:40
PostgreSQL's approach to MVCC is based on versioned storage, and owes much to the time travel feature that appeared in the original Berkeley codebase. An asynchronous cleanup process (VACUUM) garbage collects obsolete row versions, with additional cleanup performed by user transactions opportunistically. This approach has significant differences to the approaches taken by other database systems, which generally either support two-phase locking, a limited form of MVCC with remnants of two-phase locking, or both.
The use of versioned storage is not purely an accident of history; it's a design trade-off with non-obvious practical advantages and disadvantages for DBAs and application developers. This talk will focus on the advantages.
Topics covered include:
"Instantaneous transaction rollback". This is the term used by SQL Server to describe a key advantage of the "Accelerated Database Recovery" feature that will appear in SQL Server 2019. PostgreSQL always takes this approach to transaction rollback.
Advantages of versioned storage for recovery and high availability, especially in cloud environments.
How versioned storage decouples concurrency control from recovery, greatly simplifying the implementation of index access methods.
Disadvantages of even limited use of 2PL style locks within index access methods, which PostgreSQL always avoids. There are significant locking issues with access methods that implement an inverted index or bitmap index. Coarse-grained concurrency control has to be added to support eager rollback.