On Sat, 20 Dec 2025, 17:07 Corey Huinker, <[email protected]> wrote: > > On Wed, Dec 10, 2025 at 12:46 PM Matthias van de Meent > <[email protected]> wrote: >> >> Hi, >> >> One of our customers has this workload where every so often they >> update the whole table to make sure it's up-to-date. In general, you'd >> probably want to use MERGE for such a workload and ignore all rows >> that already have only matching data, but there's a catch: PostgreSQL >> doesn't have an efficient way to check if the provided data is >> actually equal in all senses of the word, so we can't easily and >> cheaply determine whether an update is needed; which is one reason why >> the full table was updated every time. > > Have you ruled out the suppress_redundant_updates_trigger?
Thank you for the reference, I wasn't aware of this trigger. Sadly, it does not work for our use case, as that only suppresses an update if the heap-formatted rows are binary identical, which is not guaranteed even if when all values are equivalent; as it doesn't take detoasting into account. It also doesn't minimize the pressure on the TOAST table, which is something else we're trying to do with the new function. The issue is that when you SET a column with a user-provided value, during trigger handling, HOT checking, and TOASTing, the binary representation of that user-provided value is the untoasted version (as it has not yet been inserted into any toast table and isn't represented as varatt_external), while the original row's value may be a toast pointer (represented as varatt_external). The checks in trigger handling, TOASTing, and HOT checking, the old tuple's value for that column (in its varatt_external representation) is compared against the new value (as normal varattrib_4b.va_4byte or varattrib_1b), and those will never be binary equal - their first byte is guaranteed to be different. Only if the value is pulled directly from the original column will the original column's TOAST pointer be used, and can a new toast table insertion be skipped (after which suppress_redundant_updates_trigger with its in-heap-row compare option might become useful). But, lacking a system that checks checks whether toasted values actually changed (and thus whether HOT applies, and whether an update has to happen), that trigger isn't up to the task at hand. Kind regards, Matthias van de Meent Databricks (https://www.databricks.com)
