PostgreSQL: Increase the speed of UPDATE query using HOT UPDATE (Heap only tuple)

What is HOT UPDATE (Heap Only Tuple) and How It helps to increase the performance of UPDATE Query?
In this post, I am going to explain the answer of this question.

This is one of the internal of PostgreSQL, but I am sure that most of you guys don’t know about PostgreSQL HOT.

PostgreSQL requires HOT because of MVCC architecture.
Please read below articles, before start to knowing about the HOT.

What is MVCC?

What is Fillfactor in PostgreSQL?

What is Vacuum in PostgreSQL?

In the MVCC architecture, When we update any record, internally MVCC architecture inserts a new record with new values and marks old record as deleted or discarded.
When we vacuum, we can clean that deleted records and utilize that space.

What is HOT (Hot Only Tuple)?

Now imagine that, we have total 500 data pages and we are updating record of page number 3, but unfortunately new record insert in page number 500.

It looks like very big internal fragmentation because of MVCC architecture.
But actually this is not happening.

PostgreSQL has a concept of HOT, With a HOT dead tuple space can be automatically reclaimed at the time of INSERT or UPDATE if no changes are made to indexed columns. This allows for more consistent performance.

HOT means, creating a new update tuple if possible on the same page as the old tuple, and maintaining a chain of updated tuples linking a new version to the old tuple.

HOT updates can only reuse dead tuples from previous transactions directly, not from the same or concurrent ones.
If you have big updates, changing large portions of the table at once, you might want to split them up in a couple of chunks.

HOT updates only work when the changed columns are not involved in indexes in any way because the indexes pointing the the old tuples need to point to the new version of it as of transaction id.

You might be blocking HOT updates with indexes on updated columns. If those are expendable, you might get better overall performance without them.

Let’s understand PostgreSQL HOT practically.

Create a table with Sample data:

Delete one record:

Insert one New Record:

Please check If any HOT Update or not on table:

Please update the statestic of that table:

Now execute one more update command:

Please check If any HOT Update or not on table:

Again, do more update:

Please check If any change in number of HOT Update of a table:

Now Apply, Index on EmpName Column:

After Index, execute few more update:

After Index, Please check If any change in number of HOT Update of a table:
The result is same because If column has an Index, HOT Update does not work.

You can also check number of HOT update of current transaction:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz