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:
Concatenating fields and converting to text: Using col1::TEXT || col2::TEXT along with hashtextextended() results in a performance drop of around 200%.
Using record_send(new) for MD5: Converting the row to a byte array and then calculating MD5 also yields similar performance degradation.
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.
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.
Postgres has to serialize the whole row before it can even start hashing, whether you are doing col1::TEXT || col2::TEXT or record_send(new). That serialization step is probably your real bottleneck, not the hashing itself.
You can try few things mentioned below:
Hash only the columns you need, not the whole row which is less to serialize.
Use pgcrypto’s digest() on a plain text concat instead of record_send():
Best fix if you can swing it - compute the hash in your app before writing, and just store the result. Skips the trigger overhead entirely.
If it must live in the DB, try a GENERATED ALWAYS AS computed column instead of a trigger. Sometimes it optimizes better. But first you need to check for your PG version