Performance Issue with Hash Calculation in PostgreSQL Triggers

PostgreSQL
1

Hello,

I am facing a performance issue while calculating the hash of inserted or deleted rows in an INSERT/UPDATE trigger in PostgreSQL. My data contains various field types, including integers, timestamps, strings, booleans, etc.

I have experimented with the following methods to compute the hash:

  1. Concatenating fields and converting to text: Using col1::TEXT || col2::TEXT along with hashtextextended() results in a performance drop of around 200%.
  2. Using record_send(new) for MD5: Converting the row to a byte array and then calculating MD5 also yields similar performance degradation.
  3. Hashing the output of record_send(new): When using hashtextextended(record_send(new)::text), the performance remains unsatisfactory.

Unfortunately, all of these approaches are leading to a performance degradation between 150% and 600%, which is unacceptable for my use case.

Are there alternative methods for efficiently calculating the hash of a row in a trigger that maintain acceptable performance levels? Any suggestions or insights would be greatly appreciated.

Thank you!

2

Write or update the data first, then use the new column to store the calculated value, and finally reclaim the permission for the column of the original data.