"The Internals of Postgresql" chapter #7

Heap Only Tuple

Heap Only Tuple (HOT) is a feature implemented in PostgreSQL version 8.3 to optimize the storage and update of rows in a table with an index.

• Without HOT: updating a row in a table requires inserting a new version of the row and a new index tuple in the index page, which consumes index page space and incurs high inserting and vacuuming costs for the index tuples.

• HOT allows PostgreSQL to store the updated row version in the same table page as the old row version, without inserting a new index tuple in the index page. This optimization effectively utilizes the pages of both the index and table and reduces the need for VACUUM processing.

Index-Only Scans

Index-only scans are a technique used by relational database management systems (RDBMS) to directly use the index key without accessing the corresponding table pages when all the target entries of the SELECT statement are included in the index key. This technique reduces the I/O cost. PostgreSQL has introduced this option since version 9.2.

When using an index scan, PostgreSQL has to check the visibility of the tuples in principle, and the index tuples do not have any information about transactions such as the t_xmin and t_xmax of the heap tuples. Therefore, PostgreSQL has to access the table data to check the visibility of the data in the index tuples.

To avoid this, PostgreSQL uses the visibility map of the target table. If all tuples stored in a page are visible, PostgreSQL uses the key of the index tuple and does not access the table page that is pointed at from the index tuple to check its visibility. Otherwise, PostgreSQL reads the table tuple that is pointed at from the index tuple and checks the visibility of the tuple, which is the ordinary process.