Developers at Orioledb have conducted an analysis of the low-level APIs used to access tables and indexes in PostgreSQL, known as the Table/Index Access Method API. They have identified areas for improvement and have suggested ways to enhance the current state of these APIs. Despite the availability of the API and the limitations of the built-in storage mechanism, there is still a lack of fully functional transactional storage mechanisms implemented solely as extensions.
Some of the popular alternative implementations for PostgreSQL tables include MVCC and index-organized tables. These alternative methods offer different ways of storing and accessing data within the PostgreSQL database.
To support alternative implementations of MVCC, changes to the API Table/Index AM are being considered by the Orioledb expansion developed to address the deficiencies in the built-in storage mechanism of PostgreSQL. However, integrating Orioledb with PostgreSQL requires modifications to the PostgreSQL code, complicating the implementation process and highlighting the need to update the current API Table AM.
While the API Table AM does not specify a specific method for MVCC implementation, it operates under the assumption that each tuple/row identifier (TID) is indexed by all indices or none at all. This principle has faced criticism for causing “Write Amplification” when updating indexed attributes, as it requires updating each index in the table. To fully utilize alternative storage methods like the UNDO log or WARM (Write-Once-Readable-Many) method, this assumption may need to be violated.
 | |