A brief overview on Heap Only Tuple and Index Only Scans

#database #postgres #postgressql

Here we are going to discuss two features of postgresSQL that are very useful in enhancing its performance i.e.

Heap Only Tuple (HOT)
Index Only Scan

Heap Only Tuple

So basically in postgresSQL, when a row/tuple is updated, it is added as the new version. That tuple will have two versions the old one that is marked as deleted and the updated one that is marked as updated. But there is a problem in this case i.e. we have to manage two versions of a row.

To solve this problem, we use HOT. In Heap only tuple, we check the space on the page where old version tuple is located and if there is enough space, we create the updated version on the same page where old version is present.

Index Only Scan

In psotgresSQL, when a database query needs the index and data of column, we have to go to the table to get that column. This process involves a lot of disk I/O and eventually this thing results in very slow execution of query.

To solve this problem, here comes the index only scans. To reduce that I/O cost, index-only scans directly use the index key without accessing the corresponding table page when all entries of the SELECT statement are included in the index key. But there is certain criteria to use index-only scans: