Thread

Re: SQL-level pg_datum_image_equal

Matthias van de Meent <boekewurm+postgres@gmail.com> — 2025-12-21T19:26:18Z

On Sat, 20 Dec 2025, 17:07 Corey Huinker, <corey.huinker@gmail.com> wrote:
>
> On Wed, Dec 10, 2025 at 12:46 PM Matthias van de Meent <boekewurm+postgres@gmail.com> 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)