OrioleDB Multi-Version Concurrency Control
MVCC not only tracks row history but also records the search-key space. To better understand PostgreSQL MVCC, it's useful to look at some alternatives. One is zheap, which introduced out-of-place undo logging to rebuild historical row versions by storing old tuples in an undo log instead of the heap. This was done entirely at the table access method level, without modifying the index access…
OrioleDB is a PostgreSQL table access method that replaces the traditional heap structure with version-aware B-trees. This allows for multi-version concurrency control (MVCC) where historical row versions are preserved. The database does this by storing complete rows in the primary B-tree and native secondary indexes store the secondary key plus the primary key. This layout is similar to InnoDB's clustered index, but the key difference is how OrioleDB maintains old snapshots as rows, keys, and B-tree pages change.
Two types of history are kept in OrioleDB: row-level undo and page-level undo. Row-level undo reconstructs older tuple values, while page-level undo reconstructs older B-tree leaf contents and key ranges. The latter is particularly interesting as it allows OrioleDB to preserve where an old query would have found a row.
The problem arises when trying to determine if a snapshot should enumerate a row in a previous status. PostgreSQL handles this by retaining old heap tuples and index entries until they are safe to remove. InnoDB does this by keeping old secondary records as delete-marked entries. OrioleDB, on the other hand, uses page-level undo to reconstruct the historical searchable key space.
The primary B-tree stores complete rows, with the key being the primary key or an internal key if no primary key is defined. Native secondary leaves store the secondary key and primary key. This allows the secondary B-tree to route rows to the correct snapshot.
OrioleDB supports PostgreSQL's extensive ecosystem of index types by using a bridge index. An ordinary index stores a synthetic heap-shaped bridge_ctid, while an internal OrioleDB bridge tree maps this identifier to the primary key. This preserves PostgreSQL's extensibility but adds an extra lookup and reintroduces stale index entries and a VACUUM cleanup cycle.
In conclusion, OrioleDB's use of MVCC and its unique approach to preserving historical data within the B-tree structure provides an interesting perspective on database management systems.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.